Microsoft SQL Server Tips

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:

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

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


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

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

Example:


Constants that can be used


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.

What is the default value for a Date field that hasn't been filled?

TIA,

Brad Z.

1/1/1900

what will happen when I querry this from any dataset->tableadapter. Will this not force constraint error, whenever I will not use all non-null fields in my querry ....?

MySQL does not allow setting the default value of a column to an expression. TIMESTAMP columns are a limited exception. A simple trigger is something like this:

CREATE TRIGGER triggerName
BEFORE INSERT ON yourTable
FOR EACH ROW
SET NEW.date = curdate();