sql server cheat sheet

Label:1. OverviewADO provides a wealth of database operations that can be divided into three steps: First, connect the database with the SqlConnection object; Second, establish the SqlCommand object, which is responsible for the execution of SQL statements and the invocation of stored procedures; Third, operate on the results returned after SQL or stored procedure execution. There ar

' currently has ' +convert (VARCHAR (3), @ @CURSOR_ROWS) + ' bar data rows. ' 2 FETCH NEXT from[cur_t_yg]3 while (@ @FETCH_STATUS =0) 4 BEGIN5 Update[t_yuangongxinxi] 6 set[work number] =[work number]+1007 WHERE current O F Cur_t_yg8 FETCH NEXT From[cur_t_yg] 9 ENDSELECT:To close the release:1 CLOSE Global cur_t_yg2 deallocate global CUR_T_YGThree: PROCEDURE, a stored procedure is a program written in the Transact-SQL language that is provided by the

following prompt:Server: Msg 3604, Level 16, State 1, line 1The duplicate key has been ignored.It indicates that no duplicate rows appear in the Product information temporary table products_temp. 　　 Fourth Axe--import new data into the original tableEmpty the original product information sheet products, import the data from the temporary table products_temp, and finally delete the temporary table products_temp.Delete Productsinsert into products sele

pointer to the last row of the data tablers.absoluteposition=n Move the record pointer to the nth row of the data tablers.absolutepage=n Move the record pointer to the first row of page Nrs.pagesize=n set to N records per pageRs.pagecount Returns the total number of pages based on pagesize settingsRs.recordcount Returns the total number of recordsRs.bof Returns whether the record pointer is above the first end of the data table, True indicates Yes, False nors.eof Returns whether the record poin

Original: A brief introduction to snapshots in SQL ServerBrief introductionA database snapshot, as its name implies, is a view of the database at a point in time. is an attribute introduced by SQL Server in the version after 2005. Snapshot scenarios are much more, but the snapshot design was initially designed for reporting Services. For example, I need a 2011 ba

SQL server lock mechanism (I)-Overview (lock types and ranges) SQL SERVER lock mechanism (II)-Overview (Lock compatibility and lockable resources) This article introduces the locking mechanism of SQL SERVER (iii) -- Overview (loc

This article is from the CSDN blog: blog. csdn. netjinjazzarchive200807142650506.aspx inserts the excel data read by oledb into sqlserver quickly. Many people concatenate SQL statements cyclically. This is not only error-prone but inefficient, but the best way is to use bcp, that is, System. data. sqlClient. sqlBulkCo This article from the CSDN blog: http://blog.csdn.net/jinjazz/archive/2008/07/14/2650506.aspx will oledb read the excel data quickly in

In SQL Server, many people concatenate SQL statements by repeating the excel data read by oledb. This is not only error-prone but also inefficient. The best way is to use bcp, that is, System. data. sqlClient. sqlBulkCopy class. Not only is the speed fast, but the code is simple. The following test code imports a sheet

Server CountersThe Access Methods object in SQL Server provides a counter that monitors access to logical data in the database, and the Buffer Manager counter monitors physical access to the database on disk. Access Methods access Mode Access methods monitors the methods used to access data stored in the database to help determine whether you can improve query p

Original address: http://www.cnblogs.com/CareySon/archive/2012/03/30/2424880.htmlIntroductionA database snapshot, as its name implies, is a view of the database at a point in time. is an attribute introduced by SQL Server in the version after 2005. Snapshot scenarios are much more, but the snapshot design was initially designed for reporting Services. For example, I need a 2011 balance

Three: Principles of index establishmentIn general, indexing depends on the scenario in which data is used, and in other words, the SQL statements that access the data are commonly used, and whether the statements are inefficient because of missing indexes (and possibly too many indexes). But not all SQL statements are indexed, and if all the SQL statements are i

),@pdname nvarchar(Ten),@salemoney decimal( -,2) --extracts the first record and assigns the extracted variable to the corresponding variable Fetch Next fromC_ Sales Table _ Date into @pdno,@pdname,@salemoney --use the global variable @ @fetch_status whether the value is 0 as a loop to determine whether to extract records, such as extracted to the output extracted in the loop body value, --then continue to extract the data using the FETCH command until the @ @FETCH_STATUS value is not 0 w