stored procedure performance tuning in sql server

SystemserialnoWith (Rowlock)SET Maxserialno=maxserialno+1WHERE TableName=@tableNameand Prefix=@Prefixand dataversion=@dataVersionENDELSEBEGIN--If on the same day, the maximum encoding plus one, otherwise update the date and reset the maximum encodingIF@defaultDateTime=CONVERT (VARCHAR (12),GETDATE (),112)BEGINUPDATE SystemserialnoWith (Rowlock)SET Maxserialno=maxserialno+1WHERE TableName=@tableNameand Prefix=@Prefixand dataversion=@dataVersionENDELSEBEGINUPDATE SystemserialnoWith (Rowlock)SET l

Label:1. Stored Procedure creation1 CREATE PROCEDUREsys.sp_student2 @id int, 3 @name varchar( -), 4 @age int 5 as 6 BEGIN 7 SELECT * fromStudentWHEREId=@id 8 END 9 GO View Code 2. Parameter Definition @id int, @name varchar (20) @age int output--output is represented as an output parameter DECLARE @where varchar (--declare) Global variables3, the first method of multi-criteria query:SET @

--Open Transaction - Insert intoStudents (Name,classid,age)Values(@stuName,@stuClassId,@stuAge) - Commit Tran --Commit a transaction - - EndTry in - beginCatch to ifXact_state ()=-1 + rollback Tran;--rolling back a transaction - SelectError_number () aserrornumber; the SelectError_message () aserrormsg; * EndCatch $ SetXact_abortoff; Panax Notoginseng End View Code Where students table: 1 CREATE TABLE [dbo].[Students]( 2 [ID] [int] IDENTITY(1,1) not NULL Primary Key, 3 [Name] [nva

from syscolumns C where order by C. colidOpen syscolumns_cursorSet @ column =''Set @ columndata =''Fetch next from syscolumns_cursor into @ name, @ xtypeWhile @ fetch_status BeginIf @ fetch_status Begin-- If @ xtype not in (189,34, 35,99, 98) -- timestamp does not need to be processed. Image, text, ntext, SQL _variant will not be processed for the moment.BeginSet @ column = @ column + case when Len (@ column) = 0 then 'else', 'end + @ nameSet @ colum

Cs page call code: Copy codeThe Code is as follows: public int TotalPage = 0; Public int PageCurrent = 1; Public int PageSize = 25; Public int RowsCount = 0; String userid, username; Public DataTable dt = new DataTable (); Public string path, userwelcome; Public string opt, cid; Protected void Page_Load (object sender, EventArgs e) { If (! IsPostBack) { If (Request. Params ["page"] = null | Request. Params ["page"]. ToString (). Equals ("")) PageCurrent = 1; Else PageCurrent = int. Parse (Reques

Copy CodeThe code is as follows: DECLARE @TotalCount int DECLARE @TotalPageCount int exec p_viewpage_a ' type1 ', ' * ', ' id ', ', ' ID ASC ', 1,0,4,3, @TotalCount output, @TotalPageCount output SELECT * FROM Type1 Create PROC p_viewpage_a /* Nzperfect [No_miss] Efficient common paging stored procedure (bidirectional retrieval) 2007.5.7 qq:34813284 Warning: Tables or views that apply to a single primary

Create procedure [DBO]. [sp_get_tree_relation] (@ Table_name nvarchar (50), @ ID nvarchar (50), @ name nvarchar (50), @ parent_id nvarchar (50), @ startid nvarchar (20 )) As Declare @ v_id int Declare @ v_level int Declare @ SQL nvarchar (500) Begin Create Table # temp (ID nvarchar (20), name nvarchar (50), parent_id nvarchar (20 )) Create Table # T1 (ID nvarchar (20), name nvarchar (50), parent_id nvarchar

One: Stored procedure implementation Paging Example: Query the property list ALTER procedure [dbo]. [Bm_getblocklist] @pageIndexint,-------number of pages @pagesizeint,---------number of bars displayed @cityidint asdeclare @sql nvarchar (max), @sql2 nvarchar (max)Set@sql ='F

Keytablename (:D Elibase:delord-Delord) Set @table Namestartpos=patindex ('%base:% ', @KeyTableName) if @tableNameStartPos >0set @KeyTableName =substring (@ Keytablename, @TableNameStartPos +5,len (@KeyTableName) [emailprotected]) Set @HeadStr = @PrefixSet @preFixStr = " If @CodeType = ' 1 ' beginif @FirstLoop = ' 1 ' Set @tmpstr = Cast ((@CurrentCode + 1) as Varchar) Else beginset @tmpstr = Ca St ((@CurrentCode + 1) as Varchar) Set @CCode = @CurrentCode + 1Update keycodeSet Currentcode =cast (

table, view, or table value function ', @ tbname)ReturnEnd -- Paging field checkIf isnull (@ fieldkey, n'') =''BeginRaiserror (n' primary key (or unique key) required for paging processing)ReturnEnd -- Other parameter checks and specificationsIf isnull (@ pagecurrent, 0) If isnull (@ pagesize, 0) If isnull (@ fieldshow, n') = n' set @ fieldshow = n '*'If isnull (@ fieldorder, n'') = n''Set @ fieldorder = n''ElseSet @ fieldorder = n 'ORDER BY' + ltrim (@ fieldorder)If isnull (@ where, n'') = n''

] [nvarchar] (a) not null,[detailage] [i NT] not null,[createtime] [datetime] is not NULL);Dbo. F_rtnstrbysplitindex is a custom scalar-valued function that returns the string corresponding to the first number of delimiters, such as dbo. F_rtnstrbysplitindex (' Jack|lilei|tom|nike ', 3) returns TomHere is the creation of the functionCreate function [dbo]. [F_rtnstrbysplitindex] (@procStr nvarchar (max), @splitStrIdx int) returns nvarchar (+) asbegindeclare @rtnStr nvarchar (+) DECLARE @ Currents

testtableWhere (id not in(Select top page size * Page IDFrom tableOrder by ID ))Order by ID ------------------------------------- Paging solution 2: (use the ID greater than the number and select top pages)Statement format:Select top 10 *From testtableWhere (ID>(Select max (ID)From (select top 20 IDFrom testtableOrder by ID) as t ))Order by ID Select top page size *From testtableWhere (ID>(Select max (ID)From (select top page size * Page IDFrom tableOrder by ID) as t ))Order by ID ---------

Usemaster -- note that this stored procedure should be created in the master database goifexists (select * fromdbo. sysobjectswhereidobject_id (N [dbo]. [p_compdb]) andOBJECTPROPERTY (id, NIsProcedure) 1) dropprocedure [dbo]. [p_compdb] GOcreateprocp_compdb @ dbna Use master -- note that this stored procedure should be

1 Use [Database name]2 GO3 4 SETAnsi_nulls on5 GO6 7 SETQuoted_identifier on8 GO9 CREATE PROCEDURE [dbo].[Stored Procedure name]Ten @pageIndex INT, One @pageSize INT, A @totalCount INTOUTPUT - as - SETNOCOUNT on the - DECLARE @PageStart INT - DECLARE @PageEnd INT - + SET @pageStart=(@pageIndex-1)*@pageSize - SET @pageEnd=@pageIndex*@pageSiz

ifExistsSelect* fromsysobjectswhereid = object_id (N'Dbo.test_cursor') and type ='P') Drop PROCEDURE dbo.test_cursorgoset ansi_nulls ongoset quoted_identifier ongocreate PROCEDURE test_ Cursorasdeclare @acctNbr varchar ( -);D eclare @acctName nvarchar ( -);D eclare mycursor Cursor forSelect Acctnbr,acctname fromBase. _member Order by Idopen Mycursorfetch next from MyCursor into @acctNbr, @acctName while(@ @

net share test = e: est in the program code (or in the CMD window).Or use netmask to add this API.Brief description:Net share: a WINDOWS Internal Network command.Purpose: create a local shared resource to display the shared resource information of the current computer.Syntax: see net share /?Step 2: establish a shared credit relationshipMaster.. Xp_mongoshell 'net use davidest 123/user: domainzf'Brief description:1: xp_mongoshell: an extended stored