[LALIT KUMAR]

Avoid using NULL values in your SQL Server databases.
Pertains to SQL Server 6.5 and higher

Here's a short list of some of the problems you can expect to encounter if you use NULLs in your database.

1) Client applications require extra programming logic to handle NULL values.

2) Calculation, sorting, comparison and grouping operations can handle NULL values in unexpected and counterintuitive ways.

3) Aggregates and join operations also exhibit unexpected or counterintuitive results (e.g., COUNT(*) and COUNT(MyField) produce different results).

4) NULL values can lead to undesirable results for WITH CUBE, WITH ROLLUP, and other statistical operations.

5) Nullable columns that contain NULL values cause a small but measurable sacrifice in performance since SQL Server has to perform an additional check to determine whether the column allows NULL values.

Instead of allowing NULL values, you should set default values on your columns, such as 0 for numeric columns and an empty string ('') for text columns.
-----------------------------------------------------------

Return SQL Server resultsets in random order
Pertains to SQL Server 2000 and higher

There may be times when you want to return the records of a query in random order, such as return card values in a poker game application.
A quick way to do this is to use the NewID() function.

Example:

Code:

SELECT *FROM CardsORDER BY NewID()

-----------------------------------------------------------

Examine details about your SQL Server programmatically
Pertains to SQL Server 2000


Code:

SELECT SERVERPROPERTY('Edition')SELECT SERVERPROPERTY('IsSingleUser')SELECT SERVERPROPERTY('MachineName')SELECT SERVERPROPERTY('ProcessId')SELECT SERVERPROPERTY('ProductVersion')SELECT SERVERPROPERTY('ProductLevel')SELECT SERVERPROPERTY('ServerName')

-----------------------------------------------------------

Use DATEPART to display individual protions of dates and times.
Pertains to SQL Server 7.0 and higher

Example:

Code:

PRINT DATEPART(Mm, GETDATE())


Constants that can be used

Quote:

Milliseconds = Ms
Second = Ss
Minute = Mi
Hour = Hh
Day of the Week = Dw
Day of the Month = Dd
Day of the Year = Dy
Week = Wk
Month = Mm
Quarter of the Year = Qq
Year = Yy

If you know of any other tips or tricks pertaining to Microsoft SQL Server, feel free to post them here so that others may learn from them.