Temp Tables in MSSQL Database

Different types of temp Tables in Exists in MSSQL Database:

1) # TABLE (Local Temporary Tables)

This types of temp table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name.

CREATE TABLE #TABLE
(
rowid INT,
fname VARCHAR(200)
)

2) ##TABLES  (Global Temporary Tables)

This types of Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions)

If the session is running and other session try to create the Global Temp Table than second session will get a error.
“There is already an object named ##TABLE in database. “

CREATE TABLE ##TABLE
(
rowid INT,
fname VARCHAR(200)
)

3) @TABLES (Table Variables )

This types of table variable is created in memory, and so performs slightly better than #temp tables ( less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

DECLARE @table TABLE
(
rowid INT,
fname VARCHAR(200)
)


How to pass parameters dynamically

How to pass parameters dynamically?
MS-SQL has many features to call the procedure from other application and from SQL itself, See the example.
Some time you need optional parameter in Procedure then assign null as default so that it will not ask you compulsory.

CREATE PROCEDURE dbo.TESTPROCEDURE
@param1 VARCHAR(32) = NULL,
@param2 INT = NULL
AS
BEGIN
SET NOCOUNT ON

SELECT Param1 = COALESCE
(
@param1,
'@param1 was Blank'
)

SELECT Param2 = COALESCE
(
RTRIM(@param2),
'@param2 was Blank'
)
END
GO

EXEC dbo.TESTPROCEDURE @param1='HELLO', @param2=1
EXEC dbo.TESTPROCEDURE @param1='HELLO'
EXEC dbo.TESTPROCEDURE @param2=1
EXEC dbo.TESTPROCEDURE 'HELLO',1
EXEC dbo.TESTPROCEDURE 'HELLO

There are so many way to get the result from procedure, Choose any of them which you like.

Table Row in String

How to create the row data as string.

If you have table row data and you need to show all the records in a single string, use the following query.

DATA:
--------------------
Name
RAM
SHYAM
HARI
RITA

RESULT:
---------------------
Name
RAM,SHYAM,HARI,RITA

Use the following query
------------------------------------
DECLARE @strList varchar(100)
SELECT @strList = COALESCE(@strList + ', ', '') +
 CAST(Name AS varchar(5))
FROM TABLE1

SELECT @strList

There are complex example in internet but you can use this simple query to get this solutions. No need to use complex cursor to get such result.

How to parametrize OPENQUERY in MSSQL

How to parametrize OPENQUERY in MSSQL:

MSSQL has good feature of linked server between two different Database in same location or in remote location.
OPENQUERY helps you to execute your query in Linked server (remote) .

In linked server you can directly execute your query without OPENQUERY but the performance will be a issue.

As per my experience OPENQUERY is best way to access the linked servers.

If you want to pass your variable in OPENQUERY than it is so simple. Use the following way to pass your variable in OPENQUERY.

DECLARE @strDate VARCHAR(10)
DECLARE @strSql VARCHAR(8000)
DECLARE @strLinked VARCHAR(8000)

SET @strDate= '2009-01-01'

SET @strSsql= 'SELECT * FROM TABLE1 WHERE TRN_DATE =' + '''' + '''' + @strDate+ '''' + ''''

SET @strLinked= 'SELECT * FROM OPENQUERY(MYLINKEDSERVER,' + '''' + @strSql+ '''' + ')'

--############ Print and see your script
PRINT @strLinked

--############ Final Execution
EXEC(@strLinked)

As per MSDN:

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

How to find date name in MSSQL

How to find Date Name in MSSQL:

if you want to return a weekday or date name of particular date then it is so simple in MSSQL.

See the example:

select DATENAME(dw , getDate())

This query returns today’s date name like Sunday, Monday etc.

You can pass “dw” or “weekday” as a first parameter to date name which specifies the part of date returned.

How to delete the MSSQL transaction log.

How to delete the MSSQL transaction log.

If your transaction log is big or too much large. it will utilize your space and other issue might be there, so you need to clean up the transaction log time to time. Follow the following steps and clean your LOG size.

1)  first take log backup

BACKUP LOG IMEKL TO DISK='d:\TEMP.bak' with init

2) Shrink your Database.


USE TESTDB
GO
DBCC SHRINKFILE ('TESTDB_log', EMPTYFILE);
GO


From GUI mode: Rclick  DB ==> Task ==> Shirinks ==> Database ==> specify size and press OK

3) Now see your log and file size.


SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

MSDN:
You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.

Display Line Numbers In SQL Queries

Display Line Numbers In SQL Queries

By default in SQL Query analyzer line numbers are not shown.

Follow the steps below:

GOTO:  Tools => Text Editor =>All Languages => General =>Line Number (Select this check box).

Now when you open a “New Query” line numbers will appear.

Saving change is not permitted

Saving change is not permitted

Some time if your setting changed by some one then you can not update table in MSSQL 2008.
you will see the below error:

“A table that can not be re-created or enabled the options prevent saving changes.
Sql 2008 Management Tools: Can’t save changes that require Recreation of Database”

That time you need to update your setting.

The fix is:

Go to Tools => Options => Designers =>Tables and Designers and uncheck the Prevent Saving Changes that require table re-creation option:

As it is, reverting back to the ‘old’ behavior now doesn’t let you know that a table recreate is required either, so the behavior now is the same as was with the old tools. Here Microsoft added some useful functionality and then UI fails to expose it intelligently.

MERGE (Transact-SQL)

MERGE (Transact-SQL 2008)

MERGE is new feature , it Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Examples

CREATE TABLE TABLE1
(
Monthid INTEGER,
Month_Name VARCHAR(15)
)
GO
INSERT INTO TABLE1
VALUES(1,'JAN')
INSERT INTO TABLE1
VALUES(2,'FEB')
INSERT INTO TABLE1
VALUES(3,'MAR')
INSERT INTO TABLE1
VALUES(4,'APR')
INSERT INTO TABLE1
VALUES(5,'MAY')
GO

CREATE TABLE TABLE2
(
Monthid INTEGER,
Month_Name VARCHAR(15)
)
GO
INSERT INTO TABLE2
VALUES(1,'JAN')
INSERT INTO TABLE2
VALUES(6,'JUNE')
INSERT INTO TABLE2
VALUES(7,'JULY')

select * from TABLE2
select * from TABLE1

MERGE TABLE1 AS t1
USING (SELECT Monthid,Month_Name FROM TABLE2) AS t2
ON t1.Monthid = t2.Monthid
WHEN MATCHED AND t1.Monthid > 8 THEN DELETE
WHEN MATCHED THEN UPDATE SET t1.Monthid = t2.Monthid
WHEN NOT MATCHED THEN
INSERT(Monthid,Month_Name)
VALUES(Monthid,Month_Name);
GO

MSDN:
Using MERGE to perform INSERT and UPDATE operations on a table in a single statement

A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. This is usually done by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. With the MERGE statement, you can perform both tasks in a single statement. The following example shows a stored procedure that contains both an INSERT statement and an UPDATE statement. The procedure is then modified to perform the equivalent operations by using a single MERGE statement.

CREATE TYPE TABLE (Transact-SQL 2008)

CREATE TYPE TABLE (Transact-SQL 2008)

Create TYPE  Table is new opotion in SQL2008. It is very easy to handle and good to use in TSQ.
This is good as compare to TEMP Table.

Here is the Example:
Create Type MyTable as TABLE
(
ID INT IDENTITY (1,1),
NAME varchar(20),
ADRES VARCHAR(200)
)

DECLARE @T AS MyTable;

INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )
INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )
INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )
INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )


SELECT * FROM @T ;
DROP TYPE MyTable;


Many a times, we need to utilize a single Stored Procedure to update multiple database tables with one-to-many relationships. In such occasions, we end up concatenating large strings, and inside the procedure, end up parsing the string to get records. With SQL Server 2008, it is very simple, and we no more need to write tons of lines of code to implement such requirements.

CREATE PROC dbo.sp_GetOrders
(@T AS dbo.tbl_Order READONLY)
AS
SELECT O.OrderID, O.OrderDate, O.CustomerID
FROM dbo.tbl_Order AS O JOIN @T AS T ON
O.OrderID = T. OrderID
ORDER BY T. RecordID;
GO

DECLARE @MyOrderIDs AS dbo.tbl_Order;
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(1, 10248)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(2, 10250)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(3, 10249);


EXEC dbo.sp_GetOrders @T = @MyOrderIDs;


This example shows  how to use the Table UDT as a parameter in SQL Stored Procedure.
Hope you enjoyed this feature. Happy Programming.

Find All SQL Server Instance Running in Local Network

How to list all SQL Server Instance Running in Local Network:
Some time you need to see the List of other running SQL server on your network. It is so simple and easy to call from other application also.

See the example:

-- ######### Query
EXEC master..xp_cmdshell 'osql -L'

-- ############ Result

NULL
Servers:
    (local)
    MAIL_SERVER
    RAGHU
    RAGHU\SQLEXPRESS
    SERVER1
NULL

If you want to get the same list from Command prompt then:

C:\> osql -L

it will show you the same list.

MSSQL Server cursors

How to use MSSQL Server cursors?
SQL Server cursors are database objects used to manipulate data in a set on a row-by-row basis. You can fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Before you can use a cursor, you need to declare it.

Example:

declare @row as varchar(20)
DECLARE cur1 CURSOR FOR

SELECT rowid FROM Table1

OPEN cur1

FETCH NEXT FROM cur1 INTO @row
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT * FROM Table1 WHERE rowid = @row

    FETCH NEXT FROM cur1 INTO @row

END
CLOSE cur1
DEALLOCATE cur1

DECLARE CURSOR

The DECLARE CURSOR command defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

Password Encryption and Decrypt

How to Encrypt Password and compare?

MS SQL has its own built in function to Encrypt password or secure text and compare it. This is very simple see the Example:

—1 , Create a Table to insert.

Create table tempPassTable
(
id int,
pwd varbinary(200)
)

—2, Insert encrypted value to a table.

insert into tempPassTable(id,pwd)
select 1, pwdencrypt('abcd')

—3, Compare encrypted value and user input value.

if (select pwdcompare('abcd',pwd,0) from tempPassTable)=0
select 'Invalid Password'
else
select 'Valid Password'

TSQL Delete on join

Delete statement on join:

Some time you need to delete a table on join.
This example will help you to run delete statement on sql join.

Example:

DELETE table1
FROM table1 t1
JOIN table2 t2 ON t1.IDCOL=t2.IDCOL

This T-SQL is checking TABLE2, If records exists then delete TABLE1. Weather IDCOL is already there in TABLE2 or not?

How to find hard disk serial number ?

How to find hard disk serial number ?

If you want to get the hard drive serial number or computer unique number then use this function.

‘Computer Unique No or hard drive serial number From Visual Basic

Private Declare Function GetVolumeInformation _
Lib "kernel32.dll" _
Alias "GetVolumeInformationA" _
(ByVal lpRootPathName As String, _
ByVal lpVolumeNameBuffer As String, _
ByVal nVolumeNameSize As Integer, _
lpVolumeSerialNumber As Long, _
lpMaximumComponentLength As Long, _
lpFileSystemFlags As Long, _
ByVal lpFileSystemNameBuffer As String, _
ByVal nFileSystemNameSize As Long) As Long

This Function will return serial number of a Drive

Public Function GetSerialNumber(DriveLetter As String) As String
Dim SerialNum As Long
Dim VolNameBuf As String
Dim FileSysNameBuf As String

Select Case Len(DriveLetter)
Case 1
If DriveLetter Like "[a-z]" Then
DriveLetter = Left$(DriveLetter, 1) & ":\"
Else
GetSerialNumber = "Error - Bad drive designation"
End If
Case 2
If LCase(DriveLetter) Like "[a-z]:" Then
DriveLetter = DriveLetter & "\"
Else
GetSerialNumber = "Error - Bad drive designation"
End If
Case 3
If LCase(DriveLetter) Like "[!a-z]:\" Then
GetSerialNumber = "Error - Bad drive designation"
End If
Case Else
GetSerialNumber = "Error - Bad drive designation"
End Select
If Len(GetSerialNumber) = 0 Then
VolNameBuf = String$(255, Chr$(0))
FileSysNameBuf = String$(255, Chr$(0))
GetVolumeInformation DriveLetter, VolNameBuf, _
Len(VolNameBuf), SerialNum, 0, 0, _
FileSysNameBuf, Len(FileSysNameBuf)
GetSerialNumber = Right$("00000000" & Hex$(SerialNum), 
End If
End Function

This is good for security purpose. some time if you want to run your EXE file on some computer only then this is the best way.

Send SMS using AT command

Send SMS using GSM modem from your computer. This Example shows you to use AT command to handle the GSM modem.
Try this to send SMS using this example.

1- Insert a form in VB project
2- Insert textbox (txtMsg,txtDestinationNumber)
3- Insert command button (Command1)
4- Insert a MSComm1
5- Connect your mobile phone in computer with datacable

Private Sub Command1_Click()
Call SENDSMS
End Sub

Sub SENDSMS()
On Error GoTo Errr:
If MsgBox("Are you sure to send SMS Message", vbYesNo) = vbNo Then
    Exit Sub
End If

' Set up the communications port
MSComm1.CommPort = 5 ' Com Port 5

' Set for 9600 baud, no parity, 8 data, and 1 stop bit.
MSComm1.Settings = "9600,N,8,1"
MSComm1.InputLen = 0
MSComm1.PortOpen = True
MSComm1.Output = "AT" & Chr$(13)

' Set up the phone for a text message
MSComm1.Output = "AT+CMGF=1" & Chr$(13)
MSComm1.Output = "AT+CMGS= " & Chr(34) & txtDestinationNumber.Text & Chr(34) & Chr$(13) & Chr(10)
MSComm1.Output = txtMsg.Text & Chr$(26)
MSComm1.PortOpen = False
MsgBox "Message Sent successfully"
Exit Sub

Errr:

MsgBox Err.Description & Err.Number, vbExclamation

End Sub

NULLIF (Transact-SQL)

How to use NULLIF (Transact-SQL)

NULLIF will Return a null value if the two specified expressions are equal. If you want to compare to fields and then this will work for you like :

Run these examples and you can see how to use it on your way.
Select NULLIF('aa','aa')
Select isnull( NULLIF('aa','aa'),'right')

MSDN is comparing NULLIF and CASE

To show the similarity between NULLIF and CASE, the following queries evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same. The first query uses NULLIF. The second query uses the CASE expression.


USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO

So NULLIF and ISNULL functions can help you on different way.

Format money/decimal with commas

SQL format money/decimal with commas:

Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57
You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that

Below is an example:

DECLARE @v MONEY
SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0)  --1322323.67
Rounded but no formatting

SELECT CONVERT(VARCHAR,@v,1)    --1,322,323.67
Formatted with commas

SELECT CONVERT(VARCHAR,@v,2)    --1322323.6666
No formatting

If you have a decimal field it doesn’t work with the convert function
The work around is to convert it to money:

DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656

SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57
Formatted with commas

Difference with (nolock) vs (nolock) as a SQL Table Hint

Difference with (nolock) vs (nolock) as a SQL Table Hint:

select sum(amount) from reward_expired (nolock)

difference was between using the table hint (nolock) and the table hint with (nolock).
Look at MSDN under SQL 2005,

“In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone.”

Micorosft plan to remove the ability to use just (nolock), and to future proof all SQL, users should write all table hints using the with keyword.

Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Table Hints (Transact-SQL)

Table Hints (Transact-SQL)

Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options.

WITH ( <table_hint> [ [ , ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW
  | FORCESEEK
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY
  | KEEPDEFAULTS
  | FASTFIRSTROW
  | HOLDLOCK
  | IGNORE_CONSTRAINTS
  | IGNORE_TRIGGERS
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

The following table hints are allowed with and without the WITH
keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD,
SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK,
ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints
are specified without the WITH keyword, the hints should be specified
alone.

For example:

Select * FROM table1 (TABLOCK)

Select * FROM table1 WITH (TABLOCK, INDEX(myindex))