SQL Server

Source: http://www.jb51.net/article/19936.htmCREATE PROCEDUREcommonpagination@columns varchar( -),--column names to display, separated by commas@tableName varchar( -),--the name of the table to query@orderColumnName varchar( -),--sorted column

IntroductionToday and test communication a percentage calculation method encountered a problem, I used in the stored procedure strong to cast (32.678 as Decimal (5,1)) I think this method will only retain a decimal place, I give the test reply is I

--Handling gender randomizationSelect (Case if round (rand () *10,0) >5 Then ' male ' else ' end '),--random within the processing time frameSelect DATEADD (Dd,round (DateDiff (DD, ' 1992-01-01 ', ' 1995-01-01 ') *rand (), 0), ' 1992-01-01 ')--Add

Each of us knows it's a bad idea, but sometimes we do it: we execute a SELECT * statement. There are many drawbacks to this approach: You return each column from your table, even the post-add column. Think about what will happen if you add

=========================== creating a stored procedure with no parameter and no return value ===========================Create proc Pro_nameAs--The SQL statement to execute--SELECT * FROM tablename--Execute stored procedure--EXEC pro_name===========

Because of the data security considerations in SQL Server, you need to back up the database regularly, and this article describes using jobs in the SQL Server database agent to automatically back up the database.1. Start the SQL Server Agent service,

SELECTrow_number () Over(ORDER byActual payment amount) asA--Serial NumberRank () Over(ORDER byActual payment amount) asB--the same count one, skip the new calculationDense_rank () Over(ORDER byActual payment amount) asC--the same count one, do

1.EXEC execution of SQL statement truncated problem:SQL statements:1 SET @sqlSel='SELECT'+@sqlField+',2 SUM (ISNULL (b.customstariff_sup,0)) as Customstariff_sup,sum (ISNULL (addedvaluetax_sup,0)) as Addedvaluetax_sup , SUM (ISNULL

1 CREATE PROCEDURE Delete_date2 (3 @applycode nvarchar4 )5 as6--declaring variable definition variables separated by commas7declare @applycode_var nvarchar ( -),8@instanceid_var nvarchar ( -),9@t_ywsloid_var nvarchar ( -),Ten@areaPart_oid nvarchar (

一，sqlserver分页：SELECTTOP(每页显示的数据条数) *FROM表名 WHERE(ID NOTIN(SELECTTOP(（当前页码 -1）*每页显示的数据条数 -1) ID FROM表名 AS表名1_1  ORDERBYID)) ORDERBYID;二：mysql分页    select* from 表名 whereid limit(（当前页码 -1）*每页显示的数据条数,当前页码 * 每页显示的数据条数);三：orcale分页SELECT* FROM(  SELECTA.*, 

Why do we need transaction logs to delete or not add log files? The answer is no, if there is no transaction log, your database will not work at all!The transaction log supports the following operations:Transaction rollbackIf the user or program

PackageSQL

First build the table statement: ifExists (Select* fromsysobjectswhereid = object_id ('[Test]') and OBJECTPROPERTY (ID,'isusertable') =1DROP table [test] CREATE table [test] ([ID] [int] IDENTITY (1,1) not NULL, [name] [nvarchar] ( -) NULL, [Votenum]

Write in frontStarting with SQL Server 2005, provided the Row_number () function, using the function generated by the index to handle paging, according to normal logical thinking is to pass pageindex and pagesize to complete the paging, the front

GrammarCONVERT (data_type (length),data_to_be_converted,style)data_type (length) Specifies the target data type (with optional lengths). The data_to_be_converted contains values that need to be converted. style Specifies the date/time output

SELECT CONVERT (VARCHAR), GETDATE (), 1)---09/14/15SELECT CONVERT (VARCHAR), GETDATE (), 2)---15.09.14SELECT CONVERT (VARCHAR), GETDATE (), 3)---14/09/15SELECT CONVERT (VARCHAR), GETDATE (), 4)---14.09.15SELECT CONVERT (VARCHAR), GETDATE (), 5)----14

--Paging Statements--Max ID PagingSelect Top 4 * fromDistrictwhereId>(Select ISNULL(Max(ID),0) from(Select Top 2Id fromDistrictOrder byId) a)--Not ID pagingSelect Top 4 * fromDistrictwhereId not inch(Select Top 2Id fromDistrict)--row_number

-----Trigger------------------- ---Trigger: is a special kind of stored procedure that is an action that occurs automatically when an operation is performedSelect * fromQ_user--------------the Insert trigger-------------- if(object_id('Tr_

Methods for how to open xp_cmdshell:exec sp_configure ' show advanced option ', 1ReconfigureGoexec sp_configure ' xp_cmdshell ', 1ReconfigureGo About Intercepting table field names:Select Newname=left (Trim (name), 1) from student SQL Temp Table

1 UseQzone2 3 --select * from sysobjects where name= ' Q_user '4 if object_id('Q_user','u') is not NULL --obejct_id a method based on table sysobjects (object name, object type)5 Drop TableQ_user--Delete a table6 Create TableQ_user7