MS SQL 2008笔记之万事开头难（2）
以下没有什么技术含量，只是列出一些基本的要点提示。掌握这些基础知识是必要的：
————————————————————————————-
For the following, I am just listing some points of SQL with some notes. This may actually be easier for review purpose:
1. Tables: works as the element in RDMS (relational database management system). Each table contains one or more columns. We could build relationships between different columns within one table or between different through foreign key constraint.
2. Data type: as in every language, MS SQL contains several data types:
* char, nchar, varchar, nvarchar. The prefix n- corresponds to unicode, takes double spaces for each characters ( 2 bytes comparing to 1 bytes). Each type here takes size as its parameter, like char(10) (default char() is one character). var- means the length can change with actual content.
* int, bigint, smallint, tinyint, decimal/numeric, float, real, money, smallmoney: int (-2^31 ~ 2^31-1), bigint(-2^63~2^63-1), smallint( -2^15, 2^15-1), tinyint(0~255), decimal(precision(-38, 38), scale), float()/real-float with given number of digits. money (-922 tri – 922 tri) without currency info. smallmoney(-200000, 200000)
* date, datetime, datetime2, smalldatetime, datetimeoffset, time: datetime2 (to 0.1 ms) has higher precision than datetime
* geometry, geography:
* uniqueidentifier, rowversion:
* binary, varbinary, bit, image (binary info.): var- variable size of binary data.
* xml:
3. Use Microsoft Visual Studio / DBMS to create database (?? what’s the use of identity specification ??)
4. Add foreign key relationship through table designer (the parent column needs to be primary key) — the relationship can be viewed graphically from database diagrams
5. calculated column: we can add calculated column by:
alter table xxx add “calculated column” as [formula]
drop a column:
alter table xxx drop column “column”
we can create the calculated column as persisted, in which case this column actually takes one space on hard drive and when queried, the data is grabbed from there directly without real calculation. This is useful when the data is deterministic and saves you time:
alter table xxx add “persisted column” as [formula] persisted
