Without going into a dissertation on data modeling(itself a subject of many full-length treatises), tables should follow basic rules of normalization:
Avoid duplicate data, avoid repeating groups in tables, and only put data in tables where the information is directly …
CREATE TABLE Loans (
loan_nbr INT NOT NULL ,
customer_nbr INT NOT NULL,
loan_date DATETIME NOT NULL,�
loan_amount DECIMAL(15, 2) NOT NULL,
loan_type CHAR(1) NOT NULL,
CONSTRAINT ck_loan_type
CHECK (loan_type IN(‘P’,’B’)), — P = Personal; –B=Business
CONSTRAINT pk_loans
PRIMARY KEY(loan_nbr));…
PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法：
table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(<column_list>)
)
UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别
在数据库属性->选项->兼容级别改为 90
典型实例
一、行转列…
It is not easy to change SQL Server 2008/2005 object schema (tables, stored procedures and views) to “dbo”. Here is a solution.
How to format datetime & date in Sql Server 2005
June 11, 2009 — Anubhav Goyal
Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data …
One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. Here’s a summary of the different date formats that come standard in SQL Server as …
SET IDENTITY_INSERT [Table] ON|OFF
注意一次只能有一个Table能在On的状态！要及时关闭！…
sqlserver删除数据库中所有的表
如果由于外键约束删除table失败，则先删除所有约束：
–/第1步**********删除所有表的外键约束*************************/
DECLARE c1 cursor for
select ‘alter table [‘+ object_name(parent_obj) + ‘] drop constraint [‘+name+’]; ‘
from sysobjects
where xtype = ‘F’
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next …
…