SQL Server Work Tips

1, isnull (); function

In SQL Server, you often encounter fields that are null, and you can use the IsNull () function to make judgments; eg:

Select IsNull (table_column, ' Test ') as column from Table

This statement means that the field table_column is taken from the table, and if the field value is empty, the "Test" character is substituted;

2, Sysobject

There is a System object table in SQL Server sysobject This list stores all objects in the database, such as constraints, defaults, logs, rules, stored procedures, table names, and so on, and can see the Help manual in detail;

Select name from Sysobject where xtype= ' U '

This statement is to say that all the table names in the database are detected;

Xtype can take the following values:

C = CHECK Constraint

D = defaults or DEFAULT constraint

F = FOREIGN KEY constraint

FN = Scalar function

IF = Inline Table function

K = PRIMARY KEY or UNIQUE constraint

L = Log

P = Stored Procedure

R = Rule

RF = copy Filter stored procedure

S = System table

TF = Table function

TR = Trigger

U = User Table

V = view

X = Extended Stored Procedure

AF = aggregate function (CLR)

FS = assembly (CLR) scalar functions

FT = assembly (CLR) table-valued function

IF = Inline Table function

IT = Internal table

PC = assembly (CLR) stored procedures

PK = PRIMARY KEY constraint (type K)

SN = synonym

SQ = Service queue

TA = Assembly (CLR) DML triggers

TT = Table Type

UQ = UNIQUE constraint (type K)

3, SQL Server 2008 tools to use;

When you create a new database table, when you are done, but you have to modify it, sometimes db blocks changes to the table structure, and you need to set the following:

SQL Server----"tool"----"option"----"designers"----"prevent saving changes that require table recreating" remove this "block modification" option ;

4, nvarchar (Max)

Max is the maximum value in SQL Server, and its definition is 4000,nvarchar (max) is actually nvarchar (4000);

5. Select * FROM table

In a database, the select * from table of the general small amount of data does not find anything slow, but once it is in a large data database, the query is the slowest and most time-consuming! The cost of the database will be huge!

So it's not easy to execute a select cout (*) from table on a large datasheet that is not only time-consuming, but also locks the database table, and any insert update delete on and that table will be blocked during cout (*)!!!

6, GETDATE ()

The GETDATE () function is used to obtain the system time of the current system this is accurate to milliseconds: eg: "2013-12-12 12:23:33:321"

7, NEWID ()

This needless to say, the key ID is most commonly used, this function can generate a unique guid,32 bit length, this strength is large, and by the database system generation, with uniqueness!