SQL Server skills

1. isnull (); Function

In SQL Server, some fields are often null. Here, you can use the isnull () function to judge; eg:

Select isnull (Table_column, 'test') as column from Table

This statement indicates that the Table_column field is obtained from the Table. If the field value is null, it is replaced by the "Test" character;

2. sysobject

In SQL Server, a System Object table sysobject stores all objects in the database, such as constraints, default values, logs, rules, stored procedures, and table names. For details, see the help manual;

Select name from sysobject where Xtype = 'U'

This statement is used to check all the table names in the database;

Xtype can be set to the following values:

C = CHECK Constraints

D = DEFAULT value or DEFAULT Constraint

F = foreign key constraint

FN = scalar function

IF = embedded table functions

K = primary key or UNIQUE constraint

L = Log

P = Stored Procedure

R = rule

RF = copy and filter the Stored Procedure

S = system table

TF = table functions

TR = trigger

U = User table

V = View

X = Extended Stored Procedure

AF = aggregate function (CLR)

FS = assembly (CLR) scalar function

FT = assembly (CLR) Table Value Function

IF = inline Table Function

IT = internal table

PC = assembly (CLR) Stored Procedure

PK = primary key constraint (type is K)

SN = Synonym

SQ = Service Queue

TA = assembly (CLR) DML trigger

TT = table Type

UQ = UNIQUE constraint (type is K)

3. Use the SQL Server 2008 tool;

When creating a new database table, when it is created but needs to be modified, sometimes the database will prevent the modification of the table structure, you need to set the following:

SQLServer ---- "tool" ---- "option" ---- "Designers" ---- "prevent saving changes that require table recreating" remove this "Stop modification" option;

4. nvarchar (Max)

In SQL Server, Max is the maximum value. Its definition is 4000. nvarchar (Max) is actually nvarchar (4000 );

5. select * from table

In a database, select * from table with a small amount of data will not find any slowness, but once it reaches the database with a large amount of data, this query is the slowest and most time-consuming! Database overhead will be huge!

Therefore, it is not easy to execute select cout (*) from table without conditions on a large data table. It not only takes a large amount of time, but also locks S the database table in cout (*) during this period, any insert update delete related to the table will be blocked !!!

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 ()

Needless to say, this is the most commonly used primary key ID. This function can generate a unique guid with 32-bit length. This function is powerful and is generated by the database system and is unique!