SQL Summary

 

show primary key info

sp_pkeys EMPLOYEE


set a explicit pk name during creating table

CREATE TABLE EMPLOYEE
(
  ID int Not Null CONSTRAINT pk_EMPLOYEE_ID PRIMARY KEY,
  FirstName nvarchar(20),
  LastName nvarchar(20)
)

or

CREATE TABLE EMPLOYEE
(
  ID int Not Null,
  FirstName nvarchar(20),
  LastName nvarchar(20),
  DEPARTMENT_ID int,
  CONSTRAINT pk_EMPLOYEE_ID PRIMARY KEY (ID)
)

set a explicit pk name to an existing table
ALTER TABLE EMPLOYEE ADD CONSTRAINT pk_ID PRIMARY KEY (ID)

set a explicit fk name to an existing table
ALTER TABLE EMPLOYEE ADD CONSTRAINT fk_FirstName FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENT(DEPARTMENT_ID)

show foreign key name
SELECT name FROM sysobjects WHERE parent_obj=OBJECT_ID('Account') AND xtype='F'

show primary key name
SELECT name FROM sysobjects WHERE parent_obj=OBJECT_ID('Account') AND xtype='PK'

 

 

alter table add column

ALTER TABLE Account ADD ClientRef nvarchar(128) null;

 

alter table drop column
ALTER TABLE Account DROP COLUMN ClientRef;

 

随机取得表中任意N条记录的方法

很简单，就用newid()：
select top N *  from table_name order by newid() ----N是一个你指定的整数，表是取得记录的条数

 

随机数rand()的用法
select  floor(rand()*N)  ---生成的数是这样的：12.0
select cast( floor(rand()*N) as int)  ---生成的数是这样的：12
select ceiling(rand() * N)  ---生成的数是这样的：12.0
select cast(ceiling(rand() * N) as int)  ---生成的数是这样的：12

update Account set ClientRef = 'ClientRef' + (CAST(CAST(RAND()*1000 as int) as nvarchar))