In SQL, what are the differences between primary, foreign, and unique keys?

Both primary and unique keys can span multiple columns within a table. Two differences exist: 1. A table may have more than one unique key. This depends on the design of the table. But, a table can have only one primary key. 2. The values in the unique key columns may or may not be NULL. The values in primary key columns, however, can not be NULL. While unique and primary keys both enforce uniqueness on the columns of one table, foreign keys define a relationship between 2 tables. A foreign key identifies a column or group of columns in one (referencing) table that refers to a column or group of columns in another (referenced) table. Both unique and primary keys can be referenced by foreign keys.

What is the difference between SMALLDATETIME and DATETIME?

"What's that", you ask? You never heard of this SmallDateTime data type?

That does not change the fact that it does indeed exist. And yes, even your SQL Server 2000 has it!

Here are the main differences you should know about these two datetime types:

1. Range of Dates

A DateTime can range from January 1, 1753 to December 31, 9999.
A SmallDateTime can range from January 1, 1900 to June 6, 2079.

2. Accuracy

DateTime is accurate to three-hundredths of a second.
SmallDateTime is accurate to one minute.

3. Size

DateTime takes up 8 bytes of storage space.
SmallDateTime takes up 4 bytes of storage space.


Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.


That's it for now... be sure to check back soon for more information on using datetime in SQL Server.