Common Transact-SQL

Transact-SQL Syntax Conventions-MSDN

Conventions	Role
Capital	The Transact-SQL keyword.
Italic body	The parameters for the Transact-SQL syntax provided by the user.
Bold body	The database name, table name, column name, index name, stored procedure, utility, data type name, and the text that must be typed as it appears.
Underline	Indicates that the default value applied when a clause with an underlined value is omitted from the statement.
| (vertical bar)	Separates the syntax items in parentheses or curly braces. only one of these items can be used.
[] (square brackets)	Optional syntax item. do not type square brackets.
{} ( Curly braces)	required syntax entries.   do not type curly braces.
n] ">[, ... n]	n number of times." > indicates that the preceding item can be repeated  n  times.   The items are separated by commas.
n] ">[... n]	n number of times." > indicates that the preceding item can be repeated  n  times.   each item is separated by a space.
;	Transact-SQL statement terminator. Although most of the statements in this version of SQL Server do not require semicolons, future versions require semicolons.
<label>:: =	The name of the syntax block.   each position of the syntax block can be indicated by a label enclosed in angle brackets indicating the:< label >. Sets are collections of expressions, such as < grouping sets >; lists are collections of sets, such as < group elements list >.

Multi-part name

All database object names are made up of 4 parts, in the format:

1 server_name. [database_name]. [scheme_name]. object_name 2 [database_name]. [scheme_name]. object_name 3 scheme_name. object_name 4 object_name

Create a table

1  Use [database_name]2 GO3 SETAnsi_nulls on4 GO5 SETQuoted_identifier on6 GO7 CREATE TABLE [Scheme_name].[table_name](8     [field_name1] [int]  not NULL,9     [field_name2] [uniqueidentifier] NULLTen) on [PRIMARY] One GO

Existing table column operations

1 --new Column2 ALTER TABLEtable_name3 ADDcolumn_name datatype4 --Delete Column5 ALTER TABLEtable_name6 DROP COLUMNcolumn_name7 --Modifying the data type of a column8 ALTER TABLEtable_name9 ALTER COLUMNCOLUMN_NAME datatype

Index

1 -- Create a simple index 2 CREATE INDEX index_name 3  on table_name (column_name) 4 -- Create a unique index 5 CREATE UNIQUE INDEX index_name 6  on table_name (column_name)

Change and delete

1 INSERT  intoTABLE_NAME (FIELD_NAME1,FIELD_NAME2)VALUES(Value1,'value2')2 DELETE  fromtable_name3 UPDATEtable_nameSETField_name1=Value1,field_name2='value2'4 SELECTId,name fromtable_namewhereId>Ten ORDER  byIdDESC

Copy data from one table to another

1 --no Join2 INSERT  intotable_name3 (4 field_name1,field_name25 )6 SELECT7 value1,value28  fromtable_name29 --there is a joinTen INSERT  intotable_name One ( A field_name1,field_name2 - ) - SELECT the value1,value2 -  fromtable_name2 -  Left JOINTable_name3 onField_name=Table_name2.field_name

Common Transact-SQL