sql server common criteria compliance

--Examine and analyze blocking information while 1=1 BEGIN EXEC MASTER.DBO.SP_BLOCKER_PSS80-or S P_BLOCKER_PSS08--or for fast mode--exec master.dbo.sp_blocker_pss80 @fast =1--or for latch mode--exec MASTER.DBO.SP_BLOCKER_PSS80 @latch =1 WAITFOR DELAY ' 00:00:15 ' END GO cmd execute command: OSQL-E-sserver-icheckblk.sql-ocheck blk.out-w2000 Reference: http://support.microsoft.com/zh-cn/kb/271509 uncover the veil of hidden data and optimize application performance https://msdn.microsoft.com/ Zh-

, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a stringDatePart Date Time output format codeUse: Select DATEADD (yyyy, ' 2012-05-06 ', ' 2013-05-06 ') DATEDIFFSubtract two dates in a specific time format to get a new datetime type of dataGrammatical structure: DATEDIFF (datepart,startdate,enddate)Parameter description:StartDate is an expression of time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined v

Tags: name common allow select Details SEL SDA System--1. Querying the user tables in the current databaseSelect *from sysobjects where xtype= ' U ';2. Get the maximum number of simultaneous user connections allowed by SQL ServerSELECT @ @MAX_CONNECTIONS;3. Get connection information for the currently specified databaseSELECT * from master.dbo.sysprocesses WHERE dbid in(SELECT dbid from master.dbo.sysdataba

with a data type of sql_variant, which is a variant. Common Property attribute values Property value Description return value Tablehastextimage Whether the table contains text, image columns Int 1 represents true 0 for false Tablehasprimarykey Whether the table contains a primary key Int 1 represents true 0 for false Tablehasindex Whether the table contai

22:56:32 ') returns a value of 514,592 secondsDateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7)DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25thDatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the YearDatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005Examples of ap

' Favorite Movies ' [Email protected] Effect: 6.RAISERROR The RAISERROR command is used to return the user-specified information when an error message is returned in the SQL Server system 7.READTEXT The READTEXT command reads the value in the text, ntext, or image column, starting at the specified position to read the specified number of characters. The format is as follows: READTEXT {table. column [HOL

Label:A data table is made up of multiple columns, and you must specify the data type of each column when you create the table. The following is a list of usage rules for common SQL Server data types . Integer type Int The storage range is an integer between 2,147,483,648 and 2,147,483,647, and this type is often set by the p

Tags: Determine required property insert EFI SEL object_id RoCE spanFirst, SQL SERVER Monthly, quarterly, annual statistical query--This daySELECT *from dbo. TableName WHERE DATEDIFF (day,timefield,getdate ()) = 0;--This weekSELECT *from dbo. TableName WHERE DATEDIFF (week,timefield,getdate ()) = 0;--This monthSELECT *from dbo. TableName WHERE DATEDIFF (month,timefield,getdate ()) = 0;--This quarterSELECT *

full table data Update, long-term lock cause blocking)(This is mainly considering the improvement of the previous table) index design Guidelines• You should create indexes on columns that are used frequently in the WHERE clause • You should create indexes on columns that are frequently used for joining tables • You should create an index on the columns that are used frequently in the ORDER by clause • You should not create indexes on small tables (tables that use only a few pages) because a ful

Suppose there is a table article, Field ID, year ... (other omitted), Data 53,210 (customer real data, amount of small), page 30 per page, query page 1500th (that is, 第45001-45030条 data), field ID clustered index, Year no index, SQL Server version: 2008R2The first scenario, the simplest and the most common method:SELECT TOP - * from [article] WHEREId not inch(

Original: Common cursor looping usages in SQL Server stored proceduresWith cursors, and while you can traverse every record in your query and pass the required fields to variables for appropriate processingDECLARE @A1 VARCHAR(Ten),@A2 VARCHAR(Ten),@A3 INTDECLAREYoucurnameCURSOR for SELECTA1,a2,a3 fromYoutablenameOPENYoucurnameFetch Next fromYoucurname into @a1,

Label:In SQL Server:1. The following query statements can be used to query to system tables (system_table), table aliases (synonym), user tables (user_table), Views (view), primary foreign key names, triggers, stored procedures, and so on, based on the different queries of ' Type ':　　　　SELECT * from SYS. OBJECTS;2. The table alias (synonym) can be queried by the following query statement:　　　　SELECT * from S

=drivermanager. getconnection (URL,user,password);String sqlstring= "Update bookphone set ph_name= '" +name+ "', ph_sex= '" +sex+ "', ph_age= '" +age+ "' Where Ph_name= '" +oldname + "'";Statement stmt=conn.createstatement ();Stmt.executeupdate (sqlString);Stmt.close ();Conn.close ();}catch(ClassNotFoundException e) {E.printstacktrace ();}catch(SQLException e) {E.printstacktrace ();}}Static void Delete (String name) {Try {Class. forname ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");Connection

directly after saving it into the release package. Be aware that stored procedures that are opened directly can only be modified, not created, but can be Save the statement that created the stored procedure. At the same time, the general mode of the stored procedure is SETAnsi_nulls on GO SETQuoted_identifier on GO -- ============================================= --Author: --Create Date: --Description: -- ============================================= CREATE PROCEDURETable name@I_INPUT_VALUE

1. Check the database space usage to see which tables are consuming larger disk spaceExecute the following statement:SelectO.name, SUM (p.reserved_page_count) asReserved_page_count, SUM (p.used_page_count) asUsed_page_count, SUM ( CaseWhen (p.index_id2) Then (p.in_row_data_page_count+P.lob_used_page_count+p.row_overflow_used_page_count)Elsep.lob_used_page_count+P.row_overflow_used_page_count End) asDataPages, SUM ( CaseWhen (p.index_id2) then Row_countElse 0end) asrowcounts fromsys.dm_db_partiti

finished Duplicate records, which are duplicates of all fields, and duplicate records for some key fields, such as the name field repeating, while the other fields are not necessarily duplicated or can be omitted. For the first repetition, it is easier to solve, using selectdistinct*fromtablename second repetition (subsequent supplement) 10, viewing triggers or stored procedures EXECNBSP;SP_ helptext trigger name/stored Procedure name 11, determine if a table already exists in the database, or

) intercept stringCHARINDEX (substring, parent string), to find whether to include. Returns the first occurrence of the position, no return 0Patindex ('%pattern% ', expression) function as above, but use wildcard charactersReplicate (' char ', rep_time), repeating stringReverse (char), invert stringReplace (str, strold, strnew) Replacement stringSpace (n), producing n empty rowsStuff (), SELECT stuff (' abcdef ', 2, 3, ' ijklmn ') = ' Aijklmnef ', 2 is the start position, 3 is the length of the

soldier ') --Output 5 Select datalength (' ABCDE ') --Output 5 Select Len (' ABCDE ') --Output 5　　22, SOUNDEXReturns a four-character code (SOUNDEX) that evaluates the similarity of two strings.Select SOUNDEX (' ABCDE ') --a120 Select SOUNDEX (' Abcdr ')--a121　　23. REPLACEReplaces all occurrences of the specified string value with another string value.The syntax is as follows:REPLACE (String_expression1, String_expression2, String_expression3)ParametersString_expression1: The string

1. View the version of the database SELECT @ @version 2. View the machine operating system parameters of the database EXEC master.. xp_msver 3. View Database Startup Parameters sp_configure 4. View Database Startup time Select CONVERT (varchar, login_time,120) from master. sysprocesses where spid=1 To view the database server name and instance name print ' Server Name ......... : ' + CONVERT (varchar, @ @SE

of digits that can be stored (left and right of the decimal point). P must be a value between 1 and 38. The default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value between 0 and P. The default is 0. 5-17 bytessmallmoney The currency data between -214,748.3648 and 214,748.3647. 4 bytesMoney data between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. 8 bytesfloat (n) floating precision digital data from -1.79e + 30