About the T-SQL of small 9 9 (1)

I suddenly remembered what a friend recently said to me: Turning around may mean a lifetime of life ....... The passage of time is always heartless and unintentional, so that you have no chance to appeal. In a twinkling of an eye, I wrote it last time. It's nearly March years. What happened and what was busy, it seems that it's no longer important.

Not so lyrical, let's talk about the theme you want to describe today, some topics about SQL Server and T-SQL.

Let's talk about some basic topics first. Maybe it's too basic that we never want to go deeper, so there are many problems that are easily overlooked by us:

1. Strong comparison between T-SQL and ANSI SQL

If the database environment in your work is SQL Server2000, And the select top cannot meet the variable requirement of the number of query records, you will certainly find help from multiple parties, find set rowcount to meet your requirements. E. g:

Use orderformif exists (select [name] From sysobjects where [name] = 'proc _ getnewssourceofsummary 'and type = 'P') Drop procedure proc_getnewssourceofsummarygocreate procedure [DBO]. [proc_getnewssourceofsummary] (@ count int, -- used to specify the number of records returned for query @ datalen int, -- used to specify the number of bytes returned by the datalength function (Note: I convert it to the varchar data type, 2 English letters occupy one byte) @ length int, -- used to specify the number of characters or bytes returned by the substring function @ ptcode varchar (30 ), -- used to specify the product encoding string @ newclscode varchar (30) -- used to specify the information classification number (obtained from the infoclass table) asbegin set rowcount @ count
Select. it_id, summary = case when datalength (cast (. other as varchar (120) <= @ datalen then. other when datalength (cast (. other as varchar (120)> @ datalen then substring (. other, 1, @ Length + (@ datalen-
Datalength (substring (cast (A. Other as varchar (120), 1, @ length)/2) + '... 'End from reglogin. DBO. infotable as a where. newclscode like @ newclscode + '%' and. ptcode like @ ptcode + '%' and. ismake> 0 and. isdel = 0 and. ischeck = 1 and. ifpage = 1 -- whether to recommend the news to the corresponding channel page order by. it_id DESC
Set rowcount 0 -- execution of set rowcount 0 will disable the influence of the Set rowcount option endgo -- test the Stored Procedure exec orderform. DBO. proc_getnewssourceofsummary 1, 90, 45, '002', '006'

The above example is a stored procedure written in my actual work. Whether you use select top or set rowcount, these are not problems, the key question is whether you know they are not part of the ansi SQL standard, but are unique to the T-SQL.

How to Implement the implementation of ansi SQL, which is a key part of the above stored procedure, in order to explain the problem in a simple and straightforward manner, and to run the SQL statement smoothly in a real database environment, I just abstracted some SQL statements in the above stored procedure to illustrate the problem.

-- Ansi SQL method use orderformif exists (select [name] From sysobjects where [name] = 'proc _ getnewssourceofid 'and type = 'P') Drop procedure proc_getnewssourceofidgocreate procedure [DBO]. [proc_getnewssourceofid] (@ count int, -- used to specify the number of returned records @ newclscode varchar (50) -- used to specify the news category number) asbeginselect. it_id from reglogin. DBO. infotable as a where (select count (B. it_id) from reglogin. DBO. infotable as B where B. it_id>. it_id and B. newclscode = '20140901' and B. ismake> 0 and B. isdel = 0 and B. ischeck = 1) <@ count and. newclscode like @ newclscode + '%' and. ismake> 0 and. isdel = 0 and. ischeck = 1 order by. it_id descend
-- Test the Stored Procedure exec orderform. DBO. proc_getnewssourceofid 5, '123'

You may not be interested in testing the performance of this stored procedure. I think it is necessary to show the execution plan generated after it is run to you:

So what if we change the stored procedure that complies with ansi SQL to use the set rowcount unique to the T-SQL? It may make you feel a little stunned, isn't it?

Such a strong comparison may not explain anything, ansi SQL is the cornerstone of the T-SQL, a more accurate statement should be T-SQL is just a specific implementation of the ansi SQL standard. And top and set rowcount is nothing more than a unique implementation of T-SQL, everything is to improve performance for, we use it, not to taste. Please remember that in other database products or databasesProgramming LanguageBut not necessarily the top and set rowcount implementations.

2. A absurd T-SQL statement

You may have written a T-SQL statement like this, just like me:

Select * from (select Top 100 it_id, title from reglogin. DBO. infotable order by it_id DESC) as newtable;
 

The SQL statement above is so absurd that I use the description of Ben-Gan, A T-SQL master, to explain the origin of "absurd.

Do you know that a standard query using the order by clause, for example, select it_id, title from reglogin. DBO. infotable order by it_id DESC returns a cursor instead of a table as expected. Let's see what a cursor is? SQL is based on the set theory. The set does not sort its rows in advance. It is only a logical set of members. The order of the members is irrelevant. An object can be returned for queries of table sorting, contains rows organized in a specific physical order. ANSI calls this object a cursor.

Because the query using the order by clause returns a cursor, it cannot be used as a table expression (I .e., an attempt, an inline Table value function, a subquery, or a derived table ), an error occurs when you run the following derived Table query in SQL Server:

Select * from (select it_id, title from reglogin. DBO. infotable order by it_id DESC) as newtable;
 

Whether you have guessed that comparing the differences between two T-SQL statements should lead to the conclusion that SQL Server allows you to use top queries in Table expressions. In fact, you can use a query with an order by clause in the "Table expression" only when the top option is specified. In other words, if both the top clause and the order by clause are specified, a relational result is returned instead of a cursor. It is ironic that after the top option of non-standard non-relational category is used, the query that should have returned the cursor returns a relational result. Because of this, support for these non-standard non-relational features is allowedProgramMembers use them in a very absurd way, and in fact they should not be supported at all.

Are you touched? We have done this before, but it does not matter, but is it true? Maybe you have the answer.

3. Some things about null

I still like to manually write all SQL-related scripts in the query analyzer. Of course, the SQL statements used to create tables are no exception. For example, the following SQL statement:

Use d_dowpolif exists (Select name from sysobjects where name = 'curementplan' and type = 'U') Drop table procurementplangocreate table [DBO]. [procurementplan] -- purchase schedule (autoid int identity (200) primary key, -- auto-increment planid nvarchar () Unique default cast (getdate () as nvarchar (20 )) not null, -- purchase plan number (dp_cg_20090520152412) createdate datetime not null, -- Date of the selected purchase plan (optional? The date on which the record was created ?) Procurementitem nvarchar (500) not null, -- purchase project (my understanding is the procurement name) supplierid int not null, -- supplier number plannedtype bit not null, -- Plan Type 0: indicates planned; 1: Indicates unplanned plannedfund decimal (0.00) default (0.00) not null, -- Plan fund (all amounts are kept in two decimal places, must be filled in) dollar decimal () default) not null, -- corresponding to the dollar amount (all amounts are kept in two decimal places and must be filled in) remark nvarchar (2000) default 'none' not null, -- Special Case remarks enteringemp nvarchar (20) not null, -- enter employee (enter record Think about whether to do foreign key association ?) Assessor nvarchar (20) not null, -- reviewer ispassed bit default 0 not null, -- whether the audit is passed (0: not approved; 1: approved) isdeleted bit default 1 not null -- whether it has been logically deleted (0: logically deleted; 1: logically valid data) goalter table [DBO]. [procurementplan] add constraint fk_procurementplan_supplierid foreign key (supplierid) References supplier (supplierid) goalter table [DBO]. [procurementplan] add constraint fk_procurementplan_enteringemp foreign key (enteringemp) References t_employee (nickname) goalter table [DBO]. [procurementplan] add constraint fk_procurementplan_assessor foreign key (Assessor) References t_employee (nickname) Go

Are you aware of anything? For some details about null, yes. When I create a table, it shows that not null is defined. I have always been used to displaying not null or null declared fields when creating a table under any circumstances. This is a good habit. I have always thought so, but why is it a good habit? I recently learned it. Please let me explain it. In fact, if you do not display whether the declared field is null when creating a table, SQL Server assumes that the field is not null.

You will say that my SQL Server allows the field to be null by default. That's right. Your illusion is that many tools or interfaces that work with SQL Server have enabled a session setting, make the default value null. Of course, we can also use a session setting or database option to enable SQL Server to allow null by default. If the column definition does not display the Declaration null or not null when creating a table, you can place the scripts on a server that is previously used with you, execution on the database server with different default settings will result in the opposite result.

Do you still find that when I create a procurementplan table, I declare all its columns as not null, and logically allow columns with null values to define default values, why?To put it simply, processing null values adds complexity to the storage engine, because SQL Server sets a special bitmap in each record to show which columns that allow null are really null values. If null is allowed, SQL Server must decode the bitmap when accessing each row. Furthermore, the allowed null value also increases the complexity of the application, because it is always necessary to write some special logic to handle null values.

Due to some of the nuances of the null value, I followed the advice given by the instructor Kalen Delaney :?If possible, declare all columns in the table as not null and define the default value for lost or unknown items.To be honest, I think this suggestion is very good. In the practical application process, it brings me many unexpected ideas, such as the last topic I will describe below.

4. How to Use T-SQL programming to meet a demanding demand

One day, the boss said to you: I need the numbers of each record to be generated according to the specified format, and it is unique, not just a simple continuous 1, 2, 4 ...... Do you understand these numbers?

Do you still remember the table above? Yes, there is a planid column, this field is used to describe the procurement plan number, the boss told me, the most ideal situation is generated according to the DP-P-YYMMXXXXX format, the specific point is: for example, now is June if you fill in the record of this table, should generate DP-P-090600001, DP-P-090600002, DP-P-090600003 ...... Such a record must meet the requirements of multi-person operations, that is, it can meet the needs of good concurrent operations.

Let's take a look at how my initial solution meets these needs:

------------------------ Stored Procedure for the synchronization mechanism to generate sequence values ---------------------- use d_dowpol; goif object_id ('[DBO]. [syncseq] ') is not nulldrop table [DBO]. [syncseq]; gocreate table [DBO]. [syncseq] (Val INT); insert into [DBO]. [syncseq] values (0 );
 

 
If object_id ('[DBO]. [usp_syncseq] ') is not null drop proc [DBO]. [usp_syncseq]; gocreate proc [DBO]. [usp_syncseq] (@ Val as int output) as update [DBO]. [syncseq] Set @ val = Val + 1; -- T-SQL proprietary syntax, equivalent to set val = Val + 1, @ val = Val + 1
-- SQL server will first lock the row and increment the Val value to obtain the value. After the transaction is completed, the lock go will be released.
 

Use d_dowpolif exists (select [name] From sysobjectswhere [name] = 'proc _ addprocurementplandat' and type = 'P') Drop procedure proc_addprocurementplan1_ocreate procedure [DBO]. [proc_addprocurementplandata] (@ createdate datetime, @ procurementitem nvarchar (500), @ supplierid int, @ plannedtype bit, @ plannedfund decimal (), @ dollar decimal ), @ remark nvarchar (2000), @ enteringemp nvarchar (50), @ assessor nvarchar (50) asbegin set nocount on
Declare @ curday as datetime, @ upd_rowcount int, @ ins_error int, @ identity as int, @ key as int, @ ID nvarchar (50), @ count as int, ---- count the number of data inserted on the first day of each month @ planid nvarchar (100) -- prevent entering the database with only a bunch of spaces in the remarks section. Select @ remark = Replace (@ remark, '',''), @ planid = 'dp _ p _ '+ convert (nvarchar (4), getdate (), 12 ), @ curday = Day (getdate ())

Begin Tran addplantran ---- start of transaction ----
Select @ COUNT = (select count (autoid) from d_dowpol.dbo.procurementplan where planid like @ planid + '% ')
If @ remark is null or (datalength (@ remark) = 0) Begin insert into values (createdate, procurementitem, supplierid, plannedtype, plannedfund, dollar, enteringemp, assessor) values (@ createdate, @ procurementitem, @ supplierid, @ plannedtype, @ plannedfund, @ dollar, @ enteringemp, @ Assessor) end else begin insert into values (createdate, values, supplierid, plannedtype, plannedfund, dollar, remark, enteringemp, assessor) values (@ createdate, @ override, @ supplierid, @ plannedtype, @ plannedfund, @ dollar, @ remark, @ enteringemp, @ Assessor) End
Select @ ins_error = @ Error
If @ ins_error = 0 begin
Select @ identity = ident_current ('procurementplan')/* still has a defect, that is, if the correct time on the server is January August, then some exceptions suddenly occur, someone changed the time on the server to July, And the DP-P-09070018 in July, and then in fact the Sequence Value in the sequence generator has survived to 9 in August, apparently there would be an error if someone was inserting data into the database at this time, because the planid generated at this moment is: DP-P-09070009, which was generated when Someone inserted the data in July, therefore, an error is reported for the planid field with a unique constraint. How can this problem be solved? My initial idea is that before each incoming request, the @ Count value and the value in the d_dowpol.dbo.syncseq table will be used to determine if the value does not match, modify the value of d_dowpol.dbo.syncseq to @ count and then execute other logic. */If @ curday = 1 and @ COUNT = 0 -- after the first day of each month, the sequence generator is first reset to zero before any new data is inserted, only begin update d_dowpol.dbo.syncseq set val = 0 end once a month
Exec d_dowpol.dbo.usp_syncseq @ key output -- obtains the sequence value generated by the synchronous sequence generator.
If @ key between 0 and 9 select @ ID = '000' + Cast (@ key as nvarchar (10 )) else if @ key between 10 and 99 select @ ID = '00' + Cast (@ key as nvarchar (10 )) else if @ key between 100 and 999 select @ ID = '0' + Cast (@ key as nvarchar (10 )) else select @ ID = cast (@ key as nvarchar (10 ))
Update d_dowpol.dbo.procurementplan set planid = @ planid + @ ID where autoid = @ identity
Select @ upd_rowcount = @ rowcount
If @ upd_rowcount> 0 begin select 1 as result -- return 1 indicates a unique success ID? Commit Tran addplantran end else begin select 2 as result -- returns 2 indicating that an error occurs when modifying the planid field of the d_dowpol.dbo.procurementplan table and rolls back the rollback Tran addplantran end else begin select 3 as result -- Return 3 indicates that an error occurs when inserting data into the d_dowpol.dbo.procurementplan table and rollback Tran addplantran endendgo is rolled back.

If you have carefully read a large segment of T-SQL aboveCodeRemove useless parts, and you will find the key code you are interested in. Yes, some colleagues will find that the above T-SQL code is written in SQL Server 2000, from the way to get @ error processing error mechanism, it seems that there is a very strong SQL2000 style taste, it is not as flexible and elegant as sql2005's error handling mechanism. At the same time, I use a large number of non-ANSI select assignments, especially when assigning values to multiple variables, the set assignment method is not used. In fact, when obtaining the values of @ error and @ rowcount correctly, if only two variables @ error and @ rowcount are declared, and then the global variables are assigned to local variables by using set, you cannot obtain the correct @ rowcount value because set @ error = @ error generates a record that affects the number of rows. Therefore, it does not seem as easy to get the correct @ rowcount value under the ansi SQL standard. However, if you use the T-SQL syntax, simply using select to assign values to multiple variables can solve this problem in one sentence: Select @ rowcount = @ rowcount, @ error = @ error ;.

When I wrote the long stored procedure above, I kept telling myself that if I just wanted to insert data, I would have to survive and meet the business rule number, we will put ourselves in an embarrassing ban. Sometimes we really need to take a step back and let ourselves pursue the feeling of the sky. This is not a good thing, I just found the feeling of entering in a retreat. Of course, when we solve the problem of generating serial numbers according to the specified rules, the solution we provide is not necessarily the best solution. I hope you can get a positive response and discussion.

In fact, there are too many topics about SQL Server and T-SQL, their ability is limited, want to express something is always written, only 1.1 point squeeze toothpaste like a record, with everyone to discuss and study, stop the pen, it is already at, no intention, the heart has been brewing next article about T-SQL 9.