Friday, November 26, 2010

Sleep Command in T-SQL?

Is there to way write a T-SQL command to just make it sleep for a period of time?
WAITFOR command is the answer.

-- wait for 1 minute
WAITFOR DELAY '00:01:00'

-- wait for 1 second
WAITFOR DELAY '00:00:01'

This command allows you a high degree of precision but is only accurate within 10ms - 16ms for example, the call WAITFOR DELAY '00:00:00:001' is likely to result in no wait at all.

Tuesday, November 16, 2010

What is uniqueidentifier in SQL Server?

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
  1. By using the NEWID() function.
  2. By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.
Note: The only operations that can be performed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.

Reference: MSDN

Wednesday, November 10, 2010

Which TCP/IP port does SQL Server run on? How can it be changed?

By default (unless changed) the default TCP Port for SQL Server is 1433.

There are a few options when trying to find out this information… one way would be to go right to the registry and look at the TCP settings:
SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.\MSSQLServer\SuperSocketNetLib\TCP\

SQL 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\MSSQLServer\SuperSocketNetLib\TCP\

How to change TCP/IP port:
Open SQL Server Configuration Manager and expand the SQL Server Network Configuration, then select Protocols for MSSQLSERVER (default instance) in the right hand pane, you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA).


If you double click on TCP/IP, the TCP/IP Properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.


If you need to change this port number, make sure you restart the SQL Server service as this change will not take affect until you do.

Sunday, November 7, 2010

Fun With TRANSACTION

What will be the output of the SELECT statement in the following query?

DECLARE @Table TABLE ([ID] INT IDENTITY(1,1), [Name] VARCHAR(10))
INSERT @Table ([Name]) VALUES ('Hari')
BEGIN TRANSACTION Test
INSERT  @Table ([Name]) VALUES ('Jon')
INSERT @Table ([Name]) VALUES ('Peter')
ROLLBACK TRANSACTION Test
INSERT @Table ([Name]) VALUES ('Max')
SELECT [ID],[Name] FROM @Table

Option1:
Option2:
Option3:
Option4:
None of the above.