This mean your begain transaction is completed and insert/update/delete is runing but Comit transacion is yet be run

OR you can create the situation by running the below query:


BEGIN TRANSACTION
DELETE * FROM Table1


Below script will allow you to find which session is Locking your table:

SELECT
OBJECT_NAME(P.object_id) AS TableName,
Resource_type,
request_session_id
FROM
sys.dm_tran_locks L
join sys.partitions P
ON L.resource_associated_entity_id = p.hobt_id
WHERE   OBJECT_NAME(P.object_id) = 'Table1'


To release the Loking you can kill that session using below script:

KILL 54

If you want to know in detail why, when and which session is locking then below script is useful:


SELECT
blocking_session_id AS BlockingSessionID,
session_id AS VictimSessionID,
(SELECT [text] FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE spid = blocking_session_id) AS BlockingQuery,
[text] AS VictimQuery,
wait_time/1000 AS WaitDurationSecond,
wait_type AS WaitType,
percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0


Create a Test Table :
————————————–

CREATE TABLE sample(
id int IDENTITY(1,1)
,name VARCHAR(100)
,address VARCHAR(100)
,phoneNo VARCHAR(15)
)

In One Session, I am going to Allow the Identity Insert
————————————————————————–

SET IDENTITY_INSERT dbo.sample ON;

INSERT INTO sample(id,name,address,phoneNo)
SELECT 10,'user-2','address-2','014002555-2'

SELECT * FROM sample

In another session i am going to insert normally without ID value:
——————————————————————-
INSERT INTO dbo.sample(name,address,phoneNo)
SELECT 'user','address','014002555'


SELECT * FROM sample

—————————-

So the Conclusion is IDENTITY_INSERT will be Enabled on Session Level only. You can insert with identity value from one session and another session will be working normally with auto identity value.

UPDATE top (1) TABLE1
SET status ='ready'
where status ='hold'

At the same time if you want to return the Row IDs of updated table:

UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid
where status ='hold'

—————— result—————-
rowid
1

Same thing if you want to store in variable

DECLARE @UpdatedIds table (id int)
DECLARE @rowId int


UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid INTO @UpdatedIds
where status ='hold'

SELECT @rowId=id FROM @UpdateIds
SELECT @rowId

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text


END

ALTER function [dbo].[POSTHttp]
(
@url varchar(8000),
@data varchar(2000)
)
returns varchar(8000)
as

BEGIN
DECLARE @win int
DECLARE @hr  int
DECLARE @text varchar(8000)

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'POST',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'setRequestHeader',NULL, 'Content-type'
, 'application/x-www-form-urlencoded'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send',null,@data
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text

END

IF OBJECT_ID('tempdb..#dataList') IS NOT NULL
DROP TABLE #dataList


CREATE TABLE #dataList(
id            INT
,name        VARCHAR(50)
,parent        INT
,ip            INT
)


INSERT #dataList
SELECT 1    id, 'Group1'        Name, 0 Parent, 0 ip UNION ALL
SELECT 2    id, 'SubGroup1'        Name, 1 Parent, 0 ip UNION ALL
SELECT 3    id, 'Item1'            Name, 2 Parent, 1 ip UNION ALL
SELECT 4    id, 'Item2'            Name, 2 Parent, 1 ip UNION ALL
SELECT 5    id, 'Item3'            Name, 2 Parent, 1 ip UNION ALL
SELECT 6    id, 'Item4'            Name, 2 Parent, 1 ip UNION ALL
SELECT 7    id, 'SubGroup2'        Name, 1 Parent, 0 ip UNION ALL
SELECT 8    id, 'Item5'            Name, 7 Parent, 1 ip UNION ALL
SELECT 9    id, 'Item6'            Name, 7 Parent, 1 ip UNION ALL
SELECT 10    id, 'Item7'            Name, 7 Parent, 1 ip UNION ALL
SELECT 11    id, 'Item8'            Name, 7 Parent, 1 ip


--SELECT * FROM #dataList



DECLARE @id INT = 1
;WITH CTE(id, name, parent, ip)
AS
(
SELECT * FROM #dataList WHERE id = @id
UNION ALL
SELECT d.* FROM #dataList D
INNER JOIN CTE c ON d.parent = c.id
)

SELECT * FROM CTE WHERE ip = 1 ORDER BY id ASC;

Some time we need complex cross tab result for report, that time we can use the PIVOT Query, for example we have 3 Tables and we have data like this :


IF OBJECT_ID('tempdb..#customer') IS NOT NULL
DROP TABLE #customer

CREATE TABLE #customer (
CustomerId INT,
CustomerName VARCHAR(50)
)


INSERT #customer
SELECT 1, 'Ram'
INSERT #customer
SELECT 2, 'Hari'
INSERT #customer
SELECT 3, 'Depesh'
INSERT #customer
SELECT 4, 'Sailesh'

--Select * from #customer

————————————————————————–

IF OBJECT_ID('tempdb..#LoanType') IS NOT NULL
DROP TABLE #LoanType

CREATE TABLE #LoanType (
LoanId INT,
LoanName VARCHAR(50)
)

INSERT #LoanType
SELECT 1, 'Loan-1'
INSERT #LoanType
SELECT 2, 'Loan-2'
INSERT #LoanType
SELECT 3, 'Loan-3'
INSERT #LoanType
SELECT 4, 'Loan-4'
INSERT #LoanType
SELECT 5, 'Loan-5'
INSERT #LoanType
SELECT 6, 'Loan-6'

--Select * from #LoanType

——————————————————————————

IF OBJECT_ID('tempdb..#LoanCustomer') IS NOT NULL
DROP TABLE #LoanCustomer


CREATE TABLE #LoanCustomer (
CustomerId INT,
LoanId INT,
Amount MONEY
)


INSERT #LoanCustomer
SELECT 1, 1, 500
INSERT #LoanCustomer
SELECT 2, 1, 5300
INSERT #LoanCustomer
SELECT 3, 3, 1500
INSERT #LoanCustomer
SELECT 2, 1, 2500
INSERT #LoanCustomer
SELECT 1, 6, 5500
INSERT #LoanCustomer
SELECT 2, 4, 1100
INSERT #LoanCustomer
SELECT 2, 3, 3200
INSERT #LoanCustomer
SELECT 2, 3, 3200


--Select * from #LoanCustomer

——————————————————————————–

Combine Result of three table from PIVOT Query:


DECLARE
@sql VARCHAR(MAX)
,@LoanType VARCHAR(MAX)

SELECT
@LoanType = ISNULL(@LoanType + ', ', '') + '[' + LoanName + ']'
FROM (
SELECT
DISTINCT
l.LoanName
FROM #LoanCustomer lc
INNER JOIN #LoanType l ON lc.LoanId = l.LoanId
) x



SET @sql = '
SELECT
CustomerName
,' + @LoanType +
Sample for Dynamic PIVOT


FROM (
SELECT
c.CustomerName,
l.LoanName,
lc.Amount
FROM #LoanCustomer lc
INNER JOIN #customer c ON lc.CustomerId = c.CustomerId
INNER JOIN #LoanType l ON l.LoanId = lc.LoanId
) dataList
PIVOT
(
SUM(Amount)
FOR LoanName IN(' + @LoanType+ ')
) X '

EXEC (@sql)


This Script is Prepared by Netra Prasad Acharya (MSSQL Developer)

There are two forms of encryption algorithms: one-way and two-way. Two-way algorithms allow you to encrypt and decrypt data. One-way algorithms will only encrypt data without any ability to decrypt.

A hash algorithm is the simplest way to encrypt data, however, once encrypted, you can’t decrypt the data. Your data remains secure, ensuring that in order to “decrypt” the contents of a column you would first have to know the original data value.

--Different hash algorithms produce different hash values
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('MD5', @TEST_VAL)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('SHA1', @TEST_VAL)
GO

--Hash values are case sensitive
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'sql'
SELECT HashBytes('SHA1', @TEST_VAL)
SELECT @TEST_VAL = 'SQL'
SELECT HashBytes('SHA1', @TEST_VAL)
GO


SELECT
'3/6/2010' as OLD_FORMAT,
charindex('/','3/6/2010',1) [FIRST/POSITION],
charindex('/','3/6/2010',3) [2nd/POSITION],
replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','') as MM,
replace(left('3/6/2010',2),'/','') as DD,
right('3/6/2010',4) as YYYY,

replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','')+'/'+
replace(left('3/6/2010',2),'/','')+'/'+
right('3/6/2010',4) as NEW_FORMAT

Example to convert date  dd/mm/yyyy to mm/dd/yyyy

CTRL + f1 = sp_help
CTRL + 1 = sp_who
CTRL + 2 = sp_lock

CTRL + R = Show/Hide Result Window
CTRL + E = Execute the SQL statement
CTRL + L = Execute with Execuation plan
CTRL + G = GOTO line number
CTRL + F = find
CTRL + H = find and replace

CTRL + SHIFT + U = Convert to Upper Case
CTRL + SHIFT + L = Convert to Lower Case

If you want to change the style of your query text editor in MSSQL Management studio:

Go to the following menu:

TOOLS  ==> OPTIONS == > ENVIRONMENT == > FONTS AND COLORS

if you want to backup a SQL Server database to a mapped drive use the following script
1) First it will create Map Drive in Local PC
2) Backup the Database.
3) Delete the Map Drive, so that next time the same script can use again.

EXEC master..xp_cmdshell 'net use z: "\\192.168.1.93\share" password /user:user1'
GO

BACKUP DATABASE DB1
to disk='z:\db1.bak' with init
GO

EXEC master..xp_cmdshell 'net use z: /delete'
GO

See the example to find Max value:

-- select dbo.FuncGetMax(123,5,null,null,null)

ALTER FUNCTION [dbo].[FuncGetMax] (@str float, @str1 float, @str2 float, @str3 float,@str4 float)
RETURNS float
BEGIN

Declare @result int

-- First compare
if @str > @str1
set @result=@str
else
set @result=@str1

-- 2nd compare
if @str2 is null OR @result > @str2
set @result=@result
else
set @result=@str2

-- 3rd compare
if @str3 is null OR @result > @str3
set @result=@result
else
set @result=@str3

-- 4th compare
if @str4 is null OR @result > @str4
set @result=@result
else
set @result=@str4

return @result

end

See the example to find MIN value:


-- select dbo.FuncGetMIN(123,5,null,null,null)

Alter FUNCTION [dbo].FuncGetMIN(@str float, @str1 float, @str2 float, @str3 float,@str4 float)
RETURNS float
BEGIN

Declare @result int

-- First compare
if @str < @str1
set @result=@str
else
set @result=@str1

-- 2nd compare
if @str2 is null OR @result < @str2
set @result=@result
else
set @result=@str2

-- 3rd compare
if @str3 is null OR @result < @str3
set @result=@result
else
set @result=@str3

-- 4th compare
if @str4 is null OR @result < @str4
set @result=@result
else
set @result=@str4

return @result

end

Create table #Table
(
yearofJoining int,
EmpId int,
Deptid int
)
GO


insert into #Table select 1990,1,1
insert into #Table select 1991,2,2
insert into #Table select 1990,3,4
insert into #Table select 1991,4,2
insert into #Table select 1990,5,1
insert into #Table select 1990,6,3
insert into #Table select 1992,7,3
insert into #Table select 1990,8,4
insert into #Table select 1993,9,1
insert into #Table select 1994,10,2
insert into #Table select 1990,11,3
insert into #Table select 1995,12,3
insert into #Table select 1995,14,3
insert into #Table select 1995,15,3
insert into #Table select 1995,16,6
GO

--Original Cross Tab query
select YearofJoining,
count(case [DeptId] when 1 then 1 else null end) as [Department-1],
count(case [DeptId] when 2 then 1 else null end) as [Department-2],
count(case [DeptId] when 3 then 1 else null end) as [Department-3]
from #Table where deptid in(1,2,3)
group by Yearofjoining

Out put:

YearofJoining Department-1 Department-2 Department-3
------------- ------------ ------------ ------------
1990 2 0 2
1991 0 2 0
1992 0 0 1
1993 1 0 0
1994 0 1 0
1995 0 0 3

PIVOT Example:


SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #Table) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining


USE AdventureWorks;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL
AND ListPrice != 0.00
AND Name LIKE 'Mountain%'
GROUP BY Color
ORDER BY Color;
GO


Alternate way.

USE AdventureWorks ;
GO
SELECT Color, ListPrice, StandardCost
FROM Production.Product
WHERE Color IS NOT NULL
AND ListPrice != 0.00
AND Name LIKE 'Mountain%'
ORDER BY Color
COMPUTE SUM(ListPrice), SUM(StandardCost) BY Color;
GO

Other Example:

USE AdventureWorks;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
GROUP BY Color
ORDER BY Color;
GO


USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Other way:

USE AdventureWorks;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO

USE AdventureWorks ;
GO
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;


Comparing queries by using EXISTS and IN

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO

Usage of IN :

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO

Comparing queries by using EXISTS and = ANY


USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store s
WHERE EXISTS
(SELECT *
FROM Purchasing.Vendor v
WHERE s.Name = v.Name) ;
GO


Usage of ANY:

USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store s
WHERE s.Name = ANY
(SELECT v.Name
FROM Purchasing.Vendor v ) ;
GO


Comparing queries by using EXISTS and IN

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EXISTS
(SELECT *
FROM HumanResources.Department d
WHERE e.DepartmentID = d.DepartmentID
AND d.Name LIKE 'P%');
GO


Usage of IN :

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE DepartmentID IN
(SELECT DepartmentID
FROM HumanResources.Department
WHERE Name LIKE 'P%');
GO

Using NOT EXISTS

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE NOT EXISTS
(SELECT *
FROM HumanResources.Department d
WHERE e.DepartmentID = d.DepartmentID
AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName
GO

Advance Usage of Exists

If exists(
SELECT id FROM Table1 where price > = 1000
)
begin
SELECT '>= than 10000' as MSG
print '> than 10000' -- OR you can print as out put
return;

end


--The basic JOIN statement
-- JOIN:
SELECT Customer.CustomerID, TransID, TransAmt
FROM Customer JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

-- similar:
SELECT Customer.CustomerID, TransID, TransAmt
FROM Customer, Transaction;
where Customer.CustomerID = Transaction.CustomerID;

-- INNER JOIN
SELECT CustomerName, TransDate
FROM Customer INNER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

-- LEFT OUTER JOIN
SELECT CustomerName, TransDate, TransAmt
FROM Customer LEFT OUTER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

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)
)


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.

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.

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.

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.

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 on Drive
BACKUP LOG TESTDB TO DISK='d:\TEMP.bak' with init


-- Backup and Delete file
BACKUP LOG TESTDB TO DISK='NUL'

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.

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.

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 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 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.

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.

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.

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'

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?

If you want to get the hard drive serial number or computer unique number then use this function.
This is Visual Basic 6.0 Code to Get the Computer Hard Drive Serial Number.

‘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), 8)
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.

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 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

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

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

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 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))

Deadlocking refers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first. This is different from being blocked, or having to wait for a resource. Using the locks above, if a transaction had a shared lock, then you issued a delete on those same records held by the first lock, you would not be deadlocked. Instead, you would be blocked. When the shared lock was released, your delete statement would complete. Blocking implies some performance hit, but the transaction will complete. It simply has to wait for something else to finish first. A deadlock on the other hand, means there is no way to finish. Your transaction is stuck in a loop with some other transaction. At this point, the database system will usually pick one transaction to be killed so the other can complete.

Here are some tips on how to avoid deadlocking on your SQL Server:

• Esure the database design is properly normalized.
• Have the application access server objects in the same order each time.
• During transactions, don’t allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
• Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
• Consider using bound connections
• Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.
• Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.
• Use Column name in ORDER BY clause instead of numbers.
• Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).
• Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.

Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. This extraction can be done in any of the following ways:

• At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select the Deadlock graph event on the Events Selection tab.
• Using the Extract SQL Server Events option on the File menu.
• Individual events can also be extracted and saved by right-clicking a specific event and choosing Extract Event Data.

The CUBE operator generates a result set that is a multidimensional cube. A multidimensional cube is an expansion of fact data, or data that records individual events. The expansion is based on columns that the user wants to analyze. These columns are called dimensions. The cube is a result set containing a cross tabulation of all the possible combinations of the dimensions.

The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns and the keywords WITH CUBE. The result set contains all possible combinations of the values in the dimension columns, along with the aggregate values from the underlying rows that match that combination of dimension values.

For example, a simple table Inventory contains:

Item                 Color                Quantity
-------------------- -------------------- --------------------------
Table                Blue                 124
Table                Red                  223
Chair                Blue                 101
Chair                Red                  210

This query returns a result set that contains the Quantity subtotal for all possible combinations of Item and Color:

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

Here is the result set:

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                Blue                 101.00
Chair                Red                  210.00
Chair                (null)               311.00
Table                Blue                 124.00
Table                Red                  223.00
Table                (null)               347.00
(null)               (null)               658.00
(null)               Blue                 225.00
(null)               Red                  433.00

The following rows from the result set are of special interest:

Chair (null) 311.00

This row reports a subtotal for all rows having the value Chair in the Item dimension. The value NULL is returned for the Color dimension to show that aggregate reported by the row includes rows with any value of the Color dimension.

Table (null) 347.00

This row is similar, but reports the subtotal for all rows having Table in the Item dimension.

(null) (null) 658.00

This row reports the grand total for the cube. Both the Item and Color dimensions have the value NULL showing that all values of both dimensions are summarized in the row.

(null) Blue 225.00
(null) Red 433.00

These two rows report the subtotals for the Color dimension. Both have NULL in the Item dimension to show that the aggregate data came from rows having any value for the Item dimension.

Using GROUPING to Distinguish Null Values
The null values generated by the CUBE operation present a problem: How can a NULL generated by the CUBE operation be distinguished from a NULL returned in the actual data? This is achieved using the GROUPING function. The GROUPING function returns 0, if the column value came from the fact data, and 1 if the column value is a NULL generated by the CUBE operation. In a CUBE operation, a generated NULL represents all values. The SELECT statement can be written to use the GROUPING function to substitute the string ALL in place of any generated NULL. Because a NULL from the fact data indicates the data value is unknown, the SELECT can also be coded to return the string UNKNOWN in place of any NULL from the fact data. For example:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

Multidimensional Cubes
The CUBE operator can be used to generate n-dimensional cubes, or cubes with any number of dimensions. A single dimension cube can be used to generate a total, for example:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO

This SELECT statement returns a result set showing both the subtotals for each value of Item and the grand total for all values of Item:

Item                 QtySum
-------------------- --------------------------
Chair                311.00
Table                347.00
ALL                  658.00

SELECT statements that contain a CUBE with many dimensions can generate large result sets, because these statements generate rows for all combinations of the values in all the dimensions. These large result sets may contain too much data to be easily read and understood. One solution to this problem is to put the SELECT statement into a view:

CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

The view can then be used to query only the dimension values of interest:

SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                ALL                  311.00

HOW TO USE  ROLLUP:

The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. For more information, see Summarizing Data Using CUBE.

The differences between CUBE and ROLLUP are:

CUBE generates a result set showing aggregates for all combinations of values in the selected columns.

ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.
For example, a simple table Inventory contains:

Item                 Color                Quantity
-------------------- -------------------- --------------------------
Table                Blue                 124
Table                Red                  223
Chair                Blue                 101
Chair                Red                  210

This query generates a subtotal report:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                Blue                 101.00
Chair                Red                  210.00
Chair                ALL                  311.00
Table                Blue                 124.00
Table                Red                  223.00
Table                ALL                  347.00
ALL                  ALL                  658.00

(7 row(s) affected)

If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end:

ALL Blue 225.00
ALL Red 433.00

The CUBE operation generated rows for possible combinations of values from both Item and Color. For example, not only does CUBE report all possible combinations of Color values combined with the Item value Chair (Red, Blue, and Red + Blue), it also reports all possible combinations of Item values combined with the Color value Red (Chair, Table, and Chair + Table).

For each value in the columns on the right in the GROUP BY clause, the ROLLUP operation does not report all possible combinations of values from the column (or columns) on the left. For example, ROLLUP does not report all the possible combinations of Item values for each Color value.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:

ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.

ROLLUP can be used in a server cursor; COMPUTE BY cannot.

The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

Traditionally developers and Database administrators used temporary tables and co-related sub-queries to generate calculated row numbers in a query. Now SQL Server 2005 provides a function, which replaces all of the additional resources we used to generate row numbers.

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example:

select ROW_NUMBER() OVER(ORDER BY CLM1),* from Table1

Remark:

Error Description ::

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.Note Click OK when you receive the following message:
   Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

Create exceptions in Windows Firewall:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe executable program, click Open, and then click OK.Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe executable program, click Open, and then click OK.

Some time we need to compare some data in binary see the examples:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE convert(varbinary(255), PASS) = convert(varbinary(255), 'RAGHU')

Normal comparison:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE convert(varbinary(255), PASS) = convert(varbinary(255), 'Raghu') AND PASS= 'TEST'

Uuse the BINARY_CHECKSUM function:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE BINARY_CHECKSUM(answer) = BINARY_CHECKSUM('TEST')

Use SQL Server case insensitive collation:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE PASSWORD COLLATE SQL_Latin1_General_CP1_CS_AS = 'RAGHU' COLLATE SQL_Latin1_General_CP1_CS_AS

UPDATE Table1
SET Price = Price * 2
WHERE ProductID IN
    (SELECT ID
     FROM Table2
     WHERE ID= 51);
GO

Here is an equivalent UPDATE statement using a join:

USE AdventureWorks;
GO
UPDATE Table1
SET Price = Price * 2
FROM Table1 AS t1
INNER JOIN Table2 AS t2
    ON t1.ProductID = t2.ProductID AND t2.ID= 51;
GO

Syntax:

BEGIN { TRAN | TRANSACTION }
    [ { transaction_name | @tran_name_variable }
      [ WITH MARK [ 'description' ] ]
    ]
[ ; ]

Example:

You can catch your error in T-SQL as per my experience

-- Exec spa_begintrnExample 'a'

Create Proc spa_begintrnExample
@flag char(1)

AS
if @flag='a'
begin

BEGIN TRANSACTION

Update table set clm1='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

Update table2 set clm12='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

Delete from table3 where clm1='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

Delete from table4 set clm1='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

COMMIT TRANSACTION

GOTO  EndSave

QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: 

end

Remarks:

The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:

• An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.
• A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.

The SQL RAND() function is used to generate some random numbers at run time. Here is the syntax:

Syntax

RAND ( [ seed ] )

Repetitive calls of RAND() with the same seed value return the same results.

For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.

SELECT RAND(100), RAND(), RAND()

Examples:

The following example produces four different random numbers that are generated by the RAND function.

DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND() Random_Number
SET @counter = @counter + 1
END
GO

The following example returns random float numbers based on three different seed values.

CREATE TABLE Random (Seed1 float, Seed5 float, Seed10 float)
INSERT INTO Random Values (RAND(1), RAND(5), RAND(10))
SELECT * FROM Random

The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function. If no seed is provided, the system generates its own variable seed numbers. If you call RAND with a seed value, you must use variable seed values to generate random numbers. If you call RAND multiple times with the same seed value, it returns the same generated value. The following script returns the same value for the calls to RAND because they all use the same seed value:

SELECT RAND(159784)
SELECT RAND(159784)
SELECT RAND(159784)

A common way to generate random numbers from RAND is to include something relatively variable as the seed value, such as adding several parts of a GETDATE:

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

When you use an algorithm based on GETDATE to generate seed values, RAND can still generate duplicate values if the calls to RAND are made within the interval of the smallest datepart used in the algorithm. This is especially likely when the calls to RAND are included in a single batch. Multiple calls to RAND in a single batch can be executed within the same millisecond. This is the smallest increment of DATEPART. In this case, incorporate a value based on something other than time to generate the seed values.

To ensure high-speed data copying, transformations cannot be performed on the data while it is moving from the source file to the table or view.

You can configure the Bulk Insert task in the following ways:

• Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted.
• Specify the File or Flat File connection manager to access the source file and provide information about the source data file, such as the code page and file type.
• Define the format used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file.
• Specify actions to perform on the destination table or view when inserting the data. The options include whether to check constraints, enable identity inserts, keep nulls, fire triggers, or lock the table.
• Provide information about the batch of data to insert, such as the batch size, the first and last row from the file to insert, the number of insert errors that can occur before the task stops inserting rows, and the names of the columns that will be sorted.

If the Bulk Insert task uses a Flat File connection manager to access the source file, the task does not use the format specified in the Flat File connection manager. Instead, the Bulk Insert task uses either the format specified in a format file, or the values of the RowDelimiter and ColumnDelimiter properties of the task.

BULK INSERT (Transact-SQL)

BULK INSERT
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE  =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FIRSTROW  =first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]
   [ [ , ] ERRORFILE = 'file_name' ]
    )]

Examples

A. Using pipes to import data from a file:

This example imports order detail information into the AdventureWorks.Sales.SalesOrderDetail table from the specified data file by using a pipe (|) as the field terminator and |n as the row terminator.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail     
FROM 'f:orderslineitem.txt'     
WITH         
(           
FIELDTERMINATOR =' |',           
ROWTERMINATOR =' |n'        
)

B. Using the FIRE_TRIGGERS argument

This example specifies the FIRE_TRIGGERS argument.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail     
FROM 'f:orderslineitem.txt'     
WITH       
(          
FIELDTERMINATOR =' |',          
ROWTERMINATOR = ':n',          
FIRE_TRIGGERS        )

C. Using line feed as a row terminator

This example loads a file that uses the line feed as a row terminator such as a UNIX output:

DECLARE @bulk_cmd varchar(1000)  
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail  
FROM ''<drive>:<path><filename>''   
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'  
EXEC(@bulk_cmd)

The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

Syntax:

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-Sserver_name[instance_name]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

Examples:

Copying Table Rows into a Data File (with a Trusted Connection)

The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At a command prompt, enter the following command:

bcp AdventureWorks.Sales.Currency out Currency.dat -T -c

Copying Table Rows into a Data File (with Mixed-Mode Authentication)

The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format.

he example assumes that you are using mixed-mode authentication, you must use the -U switch to specify your login ID. Also, unless you are connecting to the default instance of SQL Server on the local computer, use the -S switch to specify the system name and, optionally, an instance name.

bcp AdventureWorks.Sales.Currency out 
Currency.dat -c -U<login_id> -S<server_nameinstance_name>

The system will prompt you for your password.

Copying Data from a File to a Table

The following example illustrates the in option by using the file created in the preceding example (Currency.dat). First, however, this example creates an empty copy of the AdventureWorks Sales.Currency table, Sales.Currency2, into which the data is copied. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

To create the empty table, in Query Editor, enter the following command:

USE AdventureWorks;
GO
SELECT * INTO AdventureWorks.Sales.Currency2
FROM AdventureWorks.Sales.Currency WHERE 1=2

To bulk copy the character data into the new table–that is, to import the data–enter the following command at a command prompt:

bcp AdventureWorks.Sales.Currency2 
in Currency.dat -T -c

To verify that the command succeeded, display the contents of the table in Query Editor, and enter

USE AdventureWorks;
GO
SELECT * FROM Sales.Currency2

Copying a Specific Column into a Data File

To copy a specific column, you can use the queryout option. The following example copies only the Name column of the Sales.Currency table into a data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" 
queryout Currency.Name.dat -T -c

Copying Data From a Query to a Data File

To copy the result set from a Transact-SQL statement to a data file, use the queryout option. The following example copies the names from the AdventureWorks.Person.Contact table, ordered by last name then first name, into the Contacts.txt data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp "SELECT FirstName, LastName FROM 
AdventureWorks.Person.Contact 
ORDER BY LastName, Firstname" 
queryout Contacts.txt -c -T

Creating a Non-XML Format File

a non-XML format file, Currency.fmt, for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency 
format nul -T -c  -f Currency.fmt

Creating an XML Format File

The following example creates an XML format file named Currency.xml for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency 
format nul -T -c -x -f Currency.xml

Using a Format File to Bulk Import with bcp

To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the in option. For example, the following command bulk copies the contents of a data file, Currency.dat, into a copy of the Sales.Currency table (Sales.Currency2) by using the previously created format file (Currency.xml). The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency2 
in Currency.dat -T -f Currency.xml

1. Use “TRUNCATE TABLE” statement instead of “DELETE” clause if you want to delete all rows from a table. It is much faster then “DELETE” statement without any conditions. “TRUNCATE TABLE” frees all the space occupied by that table’s data and indexes, without logging the individual row deletes.
2. Always use owner prefix in T-SQL  queries:


   SELECT mycolumn FROM dbo.mytable

   In this case query optimizer does not have to decide whether to retrieve from dbo.mytable or other owner’s table and avoids recompilation.  Recompilation results in no performance advantages of stored procedures usage.

3. Don’t use “sp_“ as your prefix for stored procedures – it is a reserved prefix in MS SQL server! MS SQL server searches for a stored procedure with “sp_” prefix in the system procedures first, and only after that looks for them in client procedures.
4. If you are unable to install MSDE at home because of unknown error – check that you did not stop “Server” system service on you PC…
5. There are thousands of examples, when developers use “SELECT COUNT(*)” statement. But there is another, much faster way to accomplish the task:

   SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Table_Name') AND indid < 2

6. Include “SET NOCOUNT ON” statement in your stored procedures to greatly reduce network traffic.
7. Use the “BETWEEN” clause instead of “IN” for greater performance:
SELECT productId FROM customer
WHERE productId BETWEEN 1 AND 9

   Instead of:

   SELECT productId
   FROM customer
   WHERE productId IN (1, 2, 3, 4,5,6,7,8,9)

8. Use Table variables – new feature of MS SQL 2000 instead of temp tables. Table variables are created in memory, not written to the tempdb database, and therefore they are much faster. However, be careful to use them only with not very huge amount of data that you want to allocate in temp tables, otherwise you can easily get the server down.

• You can turn off the ‘auto create statistics’ database option.When this database option is set to true, statistics are automatically created on columns used in a predicate. By default, this database option is set to true. Because auto creation statistics results in some performance degradation, you can turn off this database option and create statistics manually during off-peak times by using the CREATE STATISTICS statement. By the way, in most cases, it will not provide some performance benefits.
• You can turn off the ‘auto update statistics’ database option.When this database option is set to true, existing statistics are automatically updated when the statistics become out-of-date. By default, this database option is set to true. Because auto update statistics results in some performance degradation, you can turn off this database option and update statistics manually during off-peak times by using the UPDATE STATISTICS statement. By the way, in most cases, it will not provide some performance benefits.
• Turn off the ‘autoclose’ database option.When this option is turned on, the database’s resources are freed after the last user exits. When the new user will connect to database, the database should be reopened, which takes some time. So, do hot set this database option to true on your production server. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.
• Turn off the ‘autoshrink’ database option.When this database option is set to true, the database files will be periodically shrink. Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to on. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.
• You can turn on the ‘read-only’ database option to prevent users to modify the database’s data.By default, this database option is set to false. If you have data that should not be modified, you can place it into another database and set for this database the ‘read-only’ option to true. It can increase the speed of your queries. If you need to allow permissions management (for example, prevent some users to select data from some tables), you should create another filegroup and make only this filegroup read-only, because when the ‘read-only’ database option is set to true, the database’s system tables will be also read-only and this will prevent the permissions management.
• You can turn on the ‘select into/bulkcopy’ database option to allow SELECT INTO statements and nonlogged bulk copies.The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:1. The database option ‘select into/bulkcopy’ is set to true.
  

  2. The target table is not being replicated.
  3. The TABLOCK hint is specified.
  4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.
  By default, this database option is set to false.

• You can turn off the ‘trunc. log on chkpt.’ database option to prevent the transaction log from truncating on checkpoint.This option can be set if the transaction log grows very quickly to prevent the transaction log from filling rapidly and running out of disk space. If you set the ‘trunc. log on chkpt.’ database option to true, the transaction log cannot be backed up, so you cannot restore your data to the point of failure (only to the time when the last full backup was made). So, the general recommendation about this option is allow it to be turned off, and make the transaction log backup periodically to truncate the log. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.

Note. You can set the above database options by using the sp_dboption system stored procedure or Enterprise Manager. If you want to set the above database options for the newly created database, you should set these options for the model database.

1. You can increase the ‘min memory per query’ option to improve the performance of queries that use hashing or sorting operations, if your SQL Server has a lot of memory available and there are many queries running concurrently on the server.
   The SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default ‘min memory per query’ option is equal to 1024 Kb.
2. You can increase the ‘max async IO’ option if your SQL Server works on a high performance server with high-speed intelligent disk subsystem (such as hardware-based RAID with more than 10 disks).
   This option specifies the maximum number of outstanding asynchronous disk I/O requests that the entire server can issue against a file. By the way, the ‘max async IO’ SQL Server option is no longer supported in SQL Server 2000.
3. You can change the ‘network packet size’ option to the appropriate value.
   This option can improve performance on networks whose base topology supports larger packets than TCP/IP’s default of 4096 bytes. For example, if client sends or receives large amounts of data, a larger packet size can improve performance, because it results in fewer network reads and writes. The default value for the ‘network packet size’ option is 4096 bytes. Microsoft does not recommend changing this option, because for most applications, the default packet size of 4096 bytes is best.
4. You can change the ‘fill factor’ option to the appropriate value.
   

   The ‘fill factor’ option specifies how full SQL Server will make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed), you can set the ‘fill factor’ option to 100. When the table’s data modified very often, you can decrease the ‘fill factor’ option to 70 percent, for example.

5. You can increase the ‘recovery interval’ value.
   The ‘recovery interval’ option specifies the maximum number of minutes per database that SQL Server needs to complete its recovery procedures. The default value of this option is 0. It means that SQL Server will automatically configure this option. SQL Server issues a checkpoint using the ‘recovery interval’ option. Microsoft does not recommend changing this option in general case, but sometimes you can improve performance by changing this option. You can monitor disk-write activity on the data files, and if you see periodic spikes that send disk utilization to 100 percent, you can increase the recovery interval. In this case, Microsoft suggests setting the ‘recovery interval’ option to 5 and continuing monitoring.
   
6. You can set the ‘priority boost’ SQL Server options to 1.
   You can set this option to 1, if you want from SQL Server to work with a higher priority than other processes on the same computer. The default value is 0. Setting ‘priority boost’ to 1 can degrade the performance of other applications running on the same computer with SQL Server. So, you should set the ‘priority boost’ SQL Server options to 1 only if you have dedicated server to SQL Server. In other case, do not change this option.
7. Set the ‘max worker threads’ options to the maximum number of the user connections to your SQL Server box.
   The default setting for the ‘max worker threads’ option is 255. If the number of user connections will be less than the ‘max worker threads’ value, a separate operating system thread will be created for each client connection, but if the number of user connections will exceed this value the thread pooling will be used. For example, if the maximum number of the user connections to your SQL Server box is equal to 50, you can set the ‘max worker threads’ options to 50, this frees up resources for SQL Server to use elsewhere. If the maximum number of the user connections to your SQL Server box is equal to 500, you can set the ‘max worker threads’ options to 500, this can improve SQL Server performance because thread pooling will not be used.
8. You can specify the ‘min server memory’ and ‘max server memory’ options.
   These options can be used to specify the fixed amount of memory to allocate to SQL Server. In this case, you should set the ‘min server memory’ and ‘max server memory’ to the same value (equal to the maximum amount of physical memory that SQL Server will use), and set the ‘set working set size’ SQL Server option to 1. This can improve performance because SQL Server will not dynamically allocate memory. You can also change these options when SQL Server works on the same computer with other applications. In this case, the ‘min server memory’ options is used to allow SQL Server works when other applications pretend to use all available memory, and the ‘max server memory’ options is used to allow other applications work when SQL Server tried to use all available resources.
9. You can specify the ‘set working set size’ SQL Server option to reserve the amount of physical memory space for SQL Server.
   Unlike SQL Server 6.5, SQL Server 7.0/2000 can automatically allocate memory (can take more memory if SQL Server need it, and can give memory back to operation system). This is one of the main advantages in comparison with previous versions, but dynamic memory allocation takes some time. If you know the maximum amount of physical memory that SQL Server will use, you can specify this amount by setting ‘min server memory’ and ‘max server memory’ to the same value (equal to the maximum amount of physical memory that SQL Server will use) and set the ‘set working set size’