Create a new GUID in SQL Server

To create a new uniqueidentifier or GUID (Globally Unique Identifier) in SQL Server use the newid() function:

select newid()

This function can be useful for creating test data but there is another use. It's also possible to select a random record using the script below:

select top 1 *
from dbo.Customer
order by newid()

I can't really think of any situation where this randomisation technique would have been helpful but, you never know !

Remove time from datetime in SQL Server

This was going to be a one liner but as ever, there's always something new to learn. Whenever I've needed to remove the time portion from a datetime in SQL Server I've generally used this SQL:

select convert(datetime, convert(char, getdate(), 106))

However, I found a faster method where no string conversion is necessary here. The performance improvement is only slight, 10k conversions are 0.21 seconds quicker, but it's interesting nonetheless.

Internally SQL Server stores datetime values as two 4-byte integers. The first one represents the days since 1st Jan. 1900 and the second one the number of milliseconds since midnight. Therefore another solution is to convert the datetime value to float, strip the digits behind the decimal point and convert it back to datetime again:

select cast(floor(cast(getdate() as float)) as datetime)

Captain Sybase ;)

SQL Server Reports

I wrote a SQL query the other day to determine how many rows there were in each table in my database. I now realise this information is available directly using a Management Studio Report. From SQL Server Management Studio, select the database you would like to work with from the Object Explorer. From the Database Summary page click the report button in the toolbar. There are several reports available from this drop down button but Disk Usage Report is the one we want. Then expand the Disk Space Used by Tables section and hey presto, there are the record counts and plenty more besides !