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.

What is Trace Flag 610

What is Trace Flag 610? When should we use it?

Trace Flag 610 (TF610) can be used to have minimal logging into a b tree, i.e. clustered table or an index on a heap, that already has data. TF610 can be used to get minimal logging in a non-empty B-Tree. When you insert a large amount of data, you don't want to create a lot of transaction log. So, initially the idea was to automatically do this in the engine, but because of bunch of issues this was put under a TraceFlag.


There are a few things one shoud be aware of:

1. When a Transaction COMMITs, it writes all pages that were minimally logged to disk. But there is possibilty that not all the pages can be written in time in case of a slow I/O subsystem. This can make the operation slower than full logging because for full logging, SQL only needs to write the commit log record and it's done. 
2. There is an issue in SQL 2008 where the transaction log grows very big due to log reservation - even though the log is never used. This is due to how rollback works for minimally logged pages. However, this bug was fixed in SQL 2008 R2.
3. If you'hv a large buffer-pool and the I/O subsystem cannot keep up, the commit could take very long time.
4. Don't make transactions too big. Even though the features is build to be used when inserting a large number of rows, it does not work very well if you make the transactions too big. Inserting data around 1TB in a single transaction may caused some issues.
5. The feature is NOT designed to make inserts go faster. It is mainly written to reduce the size of the transaction log.

I would recommendation to test this feature in a test environment - very similar I/O characteristics than the production system - and see if this really helps.

I would only use it if I have fast enough I/O to keep up with creation of the minimally logged pages.

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

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.

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.