SQL Server Practical Skills

1. Start with the primary key
We create the example Table bingot1 and insert several data entries. The primary key is an auto-increment field.

Create Table bingot1

(

Id int identity (1, 1 ),

Name nvarchar (10), -- name

Identityno varchar (18), -- ID card number

Logdate datetime default (getdate (), -- input date

Primary Key (ID) -- primary key constraint

)

Go

Insert into bingot1 (name, identityno)

Values ('Lee bin', '123 ')

Insert into bingot1 (name, identityno)

Values ('zhang Linqing ', '123 ')

Insert into bingot1 (name, identityno)

Values ('Li fe', '12345678901234500x ')

Insert into bingot1 (name, identityno)

Values ('ge xiaojuan ', '123 ')

Go

Select * From bingot1

Go

 

Question 1: How can I fix a deleted record by mistake?
The ID field cannot be modified in Enterprise Manager (SQL 2000) or SQL Server Management studio,

 

Solution:

Set identity_insert bingot1 on

Insert into bingot1 (ID, name, identityno)

Values (1, 'libbin', '123 ')

Set identity_insert bingot1 off -- disable it when you use it.

 

After execution, view the data as follows:

 

 

From the time of logdate, we can see that the data with ID = 1 is last inserted.

Question 2: After deleting all the data, why does the id not start from 1?

Delete from bingot1goinsert into bingot1 (name, identityno) values ('Lee bin', '000000') insert into bingot1 (name, identityno) values ('zhang Linqing ', '20140901 ') insert into bingot1 (name, identityno) values ('Li fe', '12345678901234500x') insert into bingot1 (name, identityno) values ('ge xiaojuan ', '12341 ') goselect * From bingot1go

 

After execution, view the data as follows:

 

Solution:
Truncate table bingot1

Go

 

If the table contains an ID column, the counter of this column is reset to the seed value defined in this column. If no seed is defined, the default value 1 is used. To retain the ID counter, use Delete.

Truncate table deletes all rows in the table, but does not record the deletion of a single row. The truncate table function is the same as the delete statement without a where clause. However, the truncate table is faster and uses fewer system resources and transaction log resources.

 

Ii. Central Nervous System of SQL Server-system table

• Sysobjects

Stores each object (such as constraints, default values, logs, rules, and stored procedures) created in the database ).

Select * From sysobjects where xtype = 'U'

Select * From sysobjects where xtype = 'D'

Select * From sysobjects where xtype = 'pk'

After execution, we can see the newly created table bingot1. The default table constraint DF _ bingot1 _ logdate _ 07020f21 (starting with DF), the primary key of the table primary key _ bingot1 _ 060deae8 (starting with PK ).
Xtype identifies the object type. It can be one of the following object types:

C = check constraint d = default setting or default Constraint

F = foreign key constraint L = Log fn = scalar function if = inline Table Function

P = Stored Procedure PK = primary key constraint (type: K) Rf = copy filter Stored Procedure

S = system table TF = table function TR = trigger u = User table uq = unique constraint (type: K)

V = view X = Extended Stored Procedure

You can use the following statement to view the table-related resources:

-- View various table-related resources

Select *

From sysobjects

Where parent_obj in (select ID

From sysobjects

Where name = 'bingot1 ')

• Syscolumns

Store columns in tables and views, and save each parameter of the stored procedure in the database. You can use the following statement to view information about all columns in Table bingot1.
-- View information about all columns in a table

Select *

From syscolumns

Where ID in (select ID

From sysobjects

Where name = 'bingot1 ')

 

• SYS. Types

Storage type. The content is as follows:
Select * From SYS. Types

 

Q: I rarely use system tables. Is there any practical application?
Below we will use these three system tables to write a simple code generator!
Application 1: Generate object class attributes:

Select 'public virtual'

+ Case T. Name When 'int' then' int 'else' string 'end + ''+ C. Name + '{Get; set ;}'

From DBO. sysobjects as O

Inner join DBO. syscolumns as C on C. ID = O. ID

Inner join DBO. policypes as t on C. xusertype = T. xusertype

Where o. type = 'U'

And O. Name = 'bingot1'

The output result is as follows:

Public Virtual int ID {Get; set ;}

Public Virtual string name {Get; set ;}

Public Virtual string identityno {Get; set ;}

Public Virtual string logdate {Get; set ;}

 

Application 2: generate the list page and add page:

First, you must add an extension attribute for each field. The @ value parameter is assigned with a comment, the @ level1name parameter is assigned with a table name, And the @ level2name parameter is assigned with a field name.

Exec SYS. sp_addextendedproperty @ name = n 'Ms _ description ',

@ Value = n' primary key ', @ level0type = n' scheme', @ level0name = n' dbo', @ level1type = n' table', @ level1name = n' bingot1 ', @ level2type = n'column', @ level2name = n'id'

Exec SYS. sp_addextendedproperty @ name = n 'Ms _ description ',

@ Value = n' name', @ level0type = n' scheme', @ level0name = n' dbo', @ level1type = n' table ', @ level1name = n' bingot1 ', @ level2type = n'column', @ level2name = n'name'

Exec SYS. sp_addextendedproperty @ name = n 'Ms _ description ',

@ Value = n' ID number ', @ level0type = n' scheme', @ level0name = n' dbo', @ level1type = n' table ', @ level1name = n' bingot1', @ level2type = n' column', @ level2name = n' identityno'

Exec SYS. sp_addextendedproperty @ name = n 'Ms _ description ',

@ Value = n'input date', @ level0type = n'scheme', @ level0name = n'dbo', @ level1type = n'table ', @ level1name = n' bingot1', @ level2type = n' column ', @ level2name = n' logdate'

Then you can use the system function to generate the code on the list page.

Select '<asp: boundcolumn datafield = "' + objname + '" headertext = "'

+ Cast (value as nvarchar) + '"'

+ Case cast (value as nvarchar)

When'primary key 'then' visible = "false "'

Else ''end + '> </ASP: boundcolumn>'

From fn_listextendedproperty (null, 'user', 'dbo', 'table', 'bingot1 ',

'Column ', default)

The results are as follows (the results are displayed in text format in the query analyzer, with the shortcut key Ctrl + T ):
<Asp: boundcolumn datafield = "ID" headertext = "primary key" visible = "false">

</ASP: boundcolumn>

<Asp: boundcolumn datafield = "name" headertext = "name">

</ASP: boundcolumn>

<Asp: boundcolumn datafield = "identityno" headertext = "ID card number">

</ASP: boundcolumn>

<Asp: boundcolumn datafield = "logdate" headertext = "input date">

</ASP: boundcolumn>

Then generate the add page code, where Char (13) represents the carriage return.

Select '<tr>' + char (13)

+ '<TD class = "add_tdr" style = "width: 30%;">' + Cast (value as nvarchar) + ': </TD>' + char (13)

+ '<TD class = "add_tdl" style = "width: 70%;">'

+ '<Asp: textbox id = "TXT' + objname + '" runat = "server" style = "width: 220px"> </ASP: textbox> <TD> '+ char (13)

+ '<Tr>'

From fn_listextendedproperty (null, 'user', 'dbo', 'table', 'bingot1 ',

'Column ', default)

The results are as follows (the results are displayed in text format in the query analyzer, with the shortcut key Ctrl + T ):

<Tr>

<TD class = "add_tdr" style = "width: 30%;"> primary key: </TD>

<TD class = "add_tdl" style = "width: 70%;"> <asp: textbox id = "txtid" runat = "server" style = "width: 220px "> </ASP: textbox> <TD>

<Tr>

<Tr>

<TD class = "add_tdr" style = "width: 30%;"> name: </TD>

<TD class = "add_tdl" style = "width: 70%;"> <asp: textbox id = "txtname" runat = "server" style = "width: 220px "> </ASP: textbox> <TD>

<Tr>

<Tr>

<TD class = "add_tdr" style = "width: 30%;"> ID card number: </TD>

<TD class = "add_tdl" style = "width: 70%;"> <asp: textbox id = "txtidentityno" runat = "server" style = "width: 220px "> </ASP: textbox> <TD>

<Tr>

<Tr>

<TD class = "add_tdr" style = "width: 30%;"> input date: </TD>

<TD class = "add_tdl" style = "width: 70%;"> <asp: textbox id = "txtlogdate" runat = "server" style = "width: 220px "> </ASP: textbox> <TD>

<Tr>

The page is as follows: