sql server common criteria compliance

with a join relationship, the field must indicate which table the field belongs to, or the program compatibility error of ambiguous column name may occur after a future table structure changeArchitecture Design Read/write separation Schema decoupling Data life cycle Read/write separation At the beginning of the design consider read and write separation, even read and write the same library, facilitate rapid expansion Read into real-time reading and deferred re

Like Oracle databases, SQL The Server database also has transaction logs. Transaction logs are mainly used to record all transactions and the changes made to the database by each transaction. Transaction logs are one of the most important data files in the database. When the system fails, data logs can be used to restore data to solve database faults. As SQLServer database administrators are very important

Tags: role inpu plink character Inner har spi deadlock textRed for common 0, Row_number () over and data combination sale/cnt Select *,row_number () over (order by ProductName) as RowNumberFrom ProductsSelect Row_number () over (order by sale/cnt Desc) as sort, sale/cntFrom (Select-60 as sale,3 as CNT from dual unionSelect sale,6 as CNT from dual unionSelect sale,5 as CNT from dual unionSelect-20 as sale,2 as CNT from dual unionSelect sale,8 as CNT fr

lock caused blocking) (this is mainly considering the transformation 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 full table scan operation may be fas

【Recommendation]SQL Server One of the uncommon applications Obtains the basic information, Field List, and stored procedure parameter list of a table. -- Establish a personal brand through knowledge sharing. 1. obtain basic table information Select [tablename] = [Tables]. name, [tableowner] = [schemas]. name, [tablecreatedate] = [Tables]. create_date, [tablemodifydate] = [Tables]. m

I sorted out the date format conversion methods that may be frequently used in SQL Server: Example: Select convert (varchar, getdate (), 120) 11:06:08 Select Replace (replace (convert (varchar, getdate (), 120 ),'-',''),'',''),':','')20040912110608 Select Replace (convert (varchar, getdate (), 111 ),'/','-')2004-09-12 Select convert (varchar (12), getdate (), 111)2004/09/12 Select convert (varchar

1. Left returns the number of characters in the string that are specified in the beginning.SELECT left (' ABCDEFG ', 2);----AB2. Right returns the specified number of characters in the string starting from the left.SELECT Right (' ABCDEFG ', 2);----FG3.LEN return string lengthSELECT LEN (' ABCDEFG ');----7SELECT LEN (' ABCDEFG ');----8SELECT LEN (' a bcdefg ');----8SELECT LEN (' ABCDEFG ');----74.LOWER returns the lowercase form of an uppercase stringSELECT LOWER (' ABCDEFG ');----ABCDEFG5.UPPER

-- The above two phrases will intercept the transaction records, as long as they are executed, the transaction record is not successful until the next complete preparation!-- subtract log the size of the file (LOG file name reduced by up to DBCC Shrinkfile (beedb_log,+)/******** The position of the DB ********/-- Check the location of DBSelectname,Physical_name fromSYS.master_fileswheredatabase_id=db_id(' Salepony ')-- swap out the location of the DBAlter Database Salemodify file

proc proc_avg_nopass_output @noPassNum int output, @wPass int=60, @lPass int=60 as Select AVG (writtenexam) as ' Test Average ', AVG (Labexam) as ' pilot average ' from Stumarks Select A.stuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam,b.labexam from Stuinfo a,stuMarks b where a.stuNo = B.stuno and (B.writtenexam Select @noPassNum = count (c.stuname) from (select A.S Tuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam,b.labexam from Stuinfo a,stumarks b where a.stuNo = B.st

more columns of a non-primary key, and a table can define multiple unique constraints.Right-click the column you want to set to select the index / key. And then click the Add button.Select the columns you want to set, either a column or a combination of multiple columns.Close and save the settings.4. Default constraintIf a default value constraint is defined in a table, when a user inserts a new data row, if the row does not have the specified data, the system assigns the default value to the c

) = ' * ',--the column that needs to be returned: ' Xname,xdemo ' @pkName nvarchar = ' ID ',--primary Key name @strOrder varchar (255) = ',--sorted field name such as: ' ORDER by id DESC ' @strWhere varchar (1500) = ',--Query criteria (note: Do not add where) such as: ' XName like '%222name% ' @pageIndex int = 1,--page number: 2 @pageSize int = 20, number of records per page: 20 @recordCount int=0 out,--Total records @doCount bit=0-not 0 statis

SQL Server common table expressions (CTE) Implement recursion, cte Recursion Introduction to common table expressions: A common table expression (CTE) can be considered as a temporary result set defined within the execution range of a single SELECT, INSERT, UPDATE, DELETE, o

Tags: Windows Server console knowledge startYou are generally in the company of Oracle, rarely used SQL Server, then today to tell you about SQL Server commonly used to start the way:The SQL S