SQL Server / T-SQL Tutorial

SQL Server / T-SQL Tutorial

1.Query

1.1.Query Select ( 18 ) 1.17.ANY ( 6 )

1.2.where ( 10 ) 1.18.Some ( 2 )

1.3.Order by ( 16 ) 1.19.AND ( 2 )

1.4.GROUP BY ( 8 ) 1.20.OR ( 4 )

1.5.Having ( 7 ) 1.21.Not ( 6 )

1.6.Null ( 2 ) 1.22.AS ( 2 )

1.7.IS NOT NULL ( 1 ) 1.23.Case ( 19 )

1.8.IS NULL ( 3 ) 1.24.arithmetic operator ( 1 )

1.9.Between ( 8 ) 1.25.Math Calculation ( 4 )

1.10.Exists ( 6 ) 1.26.Logical operator ( 11 )

1.11.In ( 7 ) 1.27.Comparison operators ( 7 )

1.12.DISTINCT ( 7 ) 1.28.Query Hints ( 2 )

1.13.LIKE ( 16 ) 1.29.Select Into ( 6 )

1.14.Column Alias ( 5 ) 1.30.TABLESAMPLE ( 1 )

1.15.Top ( 10 ) 1.31.ROWGUIDCOL ( 1 )

1.16.ALL ( 8 ) 1.32.rowversion ( 1 )

2.Insert Delete Update

2.1.Insert ( 12 ) 2.4.Delete ( 9 )

2.2.Insert into select ( 5 ) 2.5.Update ( 16 )

2.3.BULK INSERT ( 1 ) 2.6.Output ( 1 )

3.Table

3.1.Create Table ( 6 ) 3.5.DROP TABLE ( 2 )

3.2.ALTER TABLE ( 16 ) 3.6.Table alias ( 5 )

3.3.Column ( 7 ) 3.7.Temporary Table ( 9 )

3.4.Computed Columns ( 1 ) 3.8.TRUNCATE table ( 2 )

4.Table Join

4.1.Table Join ( 14 ) 4.7.Self join ( 3 )

4.2.Cross Join ( 4 ) 4.8.MERGE ( 1 )

4.3.Inner join ( 8 ) 4.9.One to many ( 2 )

4.4.Left Join ( 1 ) 4.10.many to many ( 2 )

4.5.LEFT OUTER JOIN ( 5 ) 4.11.APPLY ( 1 )

4.6.RIGHT OUTER JOIN ( 2 )

5.Data Types

5.1.bigint ( 1 ) 5.16.nchar ( 1 )

5.2.bit ( 4 ) 5.17.nVarChar ( 2 )

5.3.bitwise operators ( 13 ) 5.18.OPENROWSET ( 2 )

5.4.Char ( 4 ) 5.19.READTEXT ( 1 )

5.5.collate ( 2 ) 5.20.smalldatetime ( 2 )

5.6.Create Type ( 5 ) 5.21.Smallint ( 1 )

5.7.Data type ( 1 ) 5.22.SQL_VARIANT ( 6 )

5.8.Date Type ( 1 ) 5.23.text ( 2 )

5.9.datetime ( 18 ) 5.24.TEXTPTR ( 1 )

5.10.decimal ( 4 ) 5.25.timestamp ( 2 )

5.11.Float ( 1 ) 5.26.VARBINARY ( 4 )

5.12.FULLTEXT ( 8 ) 5.27.VARCHAR ( 13 )

5.13.integer ( 9 ) 5.28.WRITETEXT ( 1 )

5.14.Large Text ( 3 ) 5.29.Unicode ( 1 )

5.15.money ( 1 )

6.Set Operations

6.1.EXCEPT ( 2 ) 6.3.Union ( 6 )

6.2.INTERSECT ( 2 )

7.Constraints

7.1.Constraints ( 5 ) 7.7.Create Default ( 2 )

7.2.Primary Key ( 7 ) 7.8.Create Rule ( 2 )

7.3.Foreign Key ( 9 ) 7.9.NewID ( 1 )

7.4.REFERENCES ( 5 ) 7.10.Unique ( 7 )

7.5.Check ( 13 ) 7.11.uniqueidentifier ( 1 )

7.6.Default ( 8 ) 7.12.Constriant violation ( 4 )

8.Subquery

8.1.Subquery ( 16 ) 8.3.Inline view ( 10 )

8.2.Correlated subquery ( 4 )

9.Aggregate Functions

9.1.Aggregate function ( 5 ) 9.5.Min ( 6 )

9.2.AVG ( 6 ) 9.6.RANK ( 3 )

9.3.Count ( 8 ) 9.7.SUM ( 6 )

9.4.Max ( 6 )

10.Date Functions

10.1.CURRENT_TIMESTAMP ( 4 ) 10.8.DATEPART ( 25 )

10.2.Date calculation ( 1 ) 10.9.Day ( 3 )

10.3.DATEADD ( 16 ) 10.10.GETDATE ( 7 )

10.4.DATEDIFF ( 16 ) 10.11.GETUTCDATE ( 1 )

10.5.DATEFIRST ( 3 ) 10.12.ISDATE ( 4 )

10.6.DATEFORMAT ( 1 ) 10.13.MONTH ( 2 )

10.7.DATENAME ( 15 ) 10.14.Year ( 2 )

11.Math Functions

11.1.ABS ( 2 ) 11.13.LOG10 ( 1 )

11.2.ACOS ( 1 ) 11.14.PI ( 1 )

11.3.ASIN ( 1 ) 11.15.Power ( 2 )

11.4.ATAN ( 1 ) 11.16.RADIANS ( 1 )

11.5.CEILING ( 4 ) 11.17.RAND ( 4 )

11.6.COS ( 1 ) 11.18.ROUND ( 9 )

11.7.COT ( 1 ) 11.19.SIGN ( 1 )

11.8.DEGREES ( 1 ) 11.20.Sin ( 2 )

11.9.EXP ( 1 ) 11.21.SQRT ( 3 )

11.10.FLOOR ( 7 ) 11.22.SQUARE ( 2 )

11.11.ISNUMERIC ( 5 ) 11.23.TAN ( 1 )

11.12.LOG ( 1 )

12.String Functions

12.1.ASCII ( 3 ) 12.13.REPLACE ( 1 )

12.2.Char function ( 4 ) 12.14.REPLICATE ( 5 )

12.3.CHARINDEX ( 6 ) 12.15.REVERSE ( 2 )

12.4.DIFFERENCE ( 7 ) 12.16.Right ( 4 )

12.5.FORMATMESSAGE ( 1 ) 12.17.RTRIM ( 6 )

12.6.LEFT ( 7 ) 12.18.SOUNDEX ( 4 )

12.7.LEN ( 4 ) 12.19.SPACE ( 2 )

12.8.LOWER ( 4 ) 12.20.STR ( 6 )

12.9.LTRIM ( 4 ) 12.21.STUFF ( 3 )

12.10.nchar function ( 3 ) 12.22.SUBSTRING ( 8 )

12.11.PATINDEX ( 5 ) 12.23.UNICODE function ( 2 )

12.12.QUOTENAME ( 2 ) 12.24.UPPER ( 4 )

13.Data Convert Functions

13.1.CAST ( 7 ) 13.4.DROP DATABASE ( 2 )

13.2.COALESCE ( 5 ) 13.5.ISNULL ( 7 )

13.3.CONVERT ( 26 ) 13.6.NULLIF ( 1 )

14.Analytical Functions

14.1.COMPUTE ( 5 ) 14.8.ROLLUP ( 8 )

14.2.CUBE ( 7 ) 14.9.ROW_NUMBER ( 2 )

14.3.DENSE_RANK ( 1 ) 14.10.STDEV ( 3 )

14.4.GROUPING ( 4 ) 14.11.STDEVP ( 2 )

14.5.NTILE ( 1 ) 14.12.VAR ( 2 )

14.6.PARTITION ( 3 ) 14.13.VARP ( 1 )

14.7.PIVOT ( 1 )

15.Sequence Indentity

15.1.Identity ( 22 ) 15.5.IDENT_INCR ( 2 )

15.2.IDENTITYCOL ( 2 ) 15.6.IDENT_SEED ( 2 )

15.3.IDENTITY_INSERT ( 2 ) 15.7.SCOPE_IDENTITY ( 3 )

15.4.IDENT_CURRENT ( 2 )

16.View

16.1.View ( 8 ) 16.5.DROP VIEW ( 3 )

16.2.Create View ( 10 ) 16.6.Filter view ( 5 )

16.3.Query view ( 1 ) 16.7.Updatable View ( 6 )

16.4.ALTER VIEW ( 2 )

17.Index

17.1.CREATE INDEX ( 18 ) 17.2.DROP INDEX ( 2 )

18.Cursor

18.1.Cursor ( 3 ) 18.5.FETCH_STATUS ( 1 )

18.2.Declare Cursor ( 12 ) 18.6.Close cursor ( 1 )

18.3.Open Cursor ( 1 ) 18.7.DEALLOCATE cursor ( 2 )

18.4.Fetch cursor ( 3 ) 18.8.cursor_status ( 1 )

19.Database

19.1.Create Database ( 9 ) 19.4.BACKUP ( 3 )

19.2.Use ( 1 ) 19.5.RESTORE DATABASE ( 2 )

19.3.Alter Database ( 8 )

20.Transact SQL

20.1.Declare ( 7 ) 20.14.Delete Statement ( 5 )

20.2.Variable ( 15 ) 20.15.Insert Statement ( 5 )

20.3.Variable Select ( 8 ) 20.16.Select statement ( 4 )

20.4.Set ( 7 ) 20.17.Update Statement ( 2 )

20.5.Table Variable ( 5 ) 20.18.Dynamic SQL ( 8 )

20.6.Print ( 5 ) 20.19.EXEC ( 12 )

20.7.IF ( 14 ) 20.20.Error Handler ( 1 )

20.8.while ( 10 ) 20.21.RAISERROR ( 9 )

20.9.Goto ( 3 ) 20.22.Return ( 7 )

20.10.Break ( 1 ) 20.23.Try catch ( 7 )

20.11.Comments ( 2 ) 20.24.WAITFOR ( 4 )

20.12.Continue ( 1 ) 20.25.With ( 4 )

20.13.Code Block ( 2 )

21.Procedure Function

21.1.Create function ( 6 ) 21.9.Procedure call ( 2 )

21.2.function ( 19 ) 21.10.Alter procedure ( 4 )

21.3.Recursive function ( 5 ) 21.11.Utility Procedure ( 5 )

21.4.Alter function ( 1 ) 21.12.Drop procedure ( 2 )

21.5.Drop function ( 2 ) 21.13.Parameter ( 20 )

21.6.Utility function ( 6 ) 21.14.Output parameter ( 3 )

21.7.Create Procedure ( 6 ) 21.15.Returning value ( 4 )

21.8.Procedure ( 10 )

22.Trigger

22.1.Trigger ( 14 ) 22.10.Trigger order ( 2 )

22.2.Alter Trigger ( 4 ) 22.11.Drop trigger ( 2 )

22.3.Trigger for after ( 4 ) 22.12.COLUMNS_UPDATED ( 1 )

22.4.Trigger for Delete ( 4 ) 22.13.Update function ( 3 )

22.5.Trigger for insert ( 1 ) 22.14.Deleted table ( 2 )

22.6.Trigger for update ( 4 ) 22.15.Inserted table ( 5 )

22.7.Trigger on database ( 2 ) 22.16.RECURSIVE_TRIGGERS ( 1 )

22.8.Trigger on server ( 1 ) 22.17.Utility trigger ( 4 )

22.9.Trigger on view ( 3 )

23.Transaction

23.1.TRANSACTION ( 14 ) 23.4.Save points ( 2 )

23.2.TRANSACTION ISOLATION LEVEL ( 3 ) 23.5.TRANCOUNT ( 2 )

23.3.COMMIT ( 1 )

24.XML

24.1.XML ( 7 ) 24.4.XML query ( 5 )

24.2.For XML ( 4 ) 24.5.XML Schema ( 1 )

24.3.XML Index ( 1 )

25.System Functions

25.1.APP_NAME ( 1 ) 25.31.sp_depends ( 1 )

25.2.COL_LENGTH ( 2 ) 25.32.sp_describe_cursor ( 1 )

25.3.COL_NAME ( 1 ) 25.33.sp_dropdevice ( 1 )

25.4.context_info ( 1 ) 25.34.sp_DropLogin ( 1 )

25.5.DATABASEPROPERTY ( 1 ) 25.35.sp_dropmessage ( 1 )

25.6.DATABASEPROPERTYEX ( 1 ) 25.36.sp_DropRoleMember ( 3 )

25.7.DATALENGTH ( 4 ) 25.37.sp_DropSrvRoleMember ( 2 )

25.8.db_name ( 2 ) 25.38.sp_droptype ( 1 )

25.9.DecryptByPassphrase ( 1 ) 25.39.sp_GrantDbAccess ( 2 )

25.10.EncryptByPassPhrase ( 1 ) 25.40.sp_GrantLogin ( 1 )

25.11.fn_builtin_permissions ( 1 ) 25.41.sp_help ( 3 )

25.12.fn_helpcollations ( 1 ) 25.42.sp_helpconstraint ( 2 )

25.13.fn_varbintohexstr ( 1 ) 25.43.sp_helpdb ( 2 )

25.14.fn_varbintohexsubstring ( 1 ) 25.44.sp_helpindex ( 1 )

25.15.fn_virtualfilestats ( 1 ) 25.45.sp_HelpRole ( 2 )

25.16.sp_addlinkedserver ( 1 ) 25.46.sp_helptext ( 4 )

25.17.sp_addlogin ( 2 ) 25.47.sp_helpuser ( 1 )

25.18.sp_addmessage ( 3 ) 25.48.sp_makestartup ( 1 )

25.19.sp_AddRole ( 2 ) 25.49.sp_monitor ( 1 )

25.20.sp_addrolemember ( 5 ) 25.50.sp_procoption ( 1 )

25.21.sp_AddSrvRoleMember ( 2 ) 25.51.sp_recompile ( 2 )

25.22.sp_addtype ( 3 ) 25.52.sp_refreshview ( 1 )

25.23.sp_altermessage ( 1 ) 25.53.sp_rename ( 2 )

25.24.sp_bindefault ( 2 ) 25.54.sp_RevokeDbAccess ( 2 )

25.25.sp_bindrule ( 1 ) 25.55.sp_serveroption ( 1 )

25.26.sp_change_users_login ( 1 ) 25.56.sp_spaceused ( 2 )

25.27.sp_configure ( 4 ) 25.57.sp_unbindefault ( 1 )

25.28.sp_create_removable ( 1 ) 25.58.sp_unmakestartup ( 1 )

25.29.sp_dboption ( 9 ) 25.59.SYSTEM_USER ( 3 )

25.30.sp_DenyLogin ( 1 )

26.System Settings

26.1.ANSI_NULLS ( 1 ) 26.24.PACK_RECEIVED ( 1 )

26.2.CONNECTIONS ( 1 ) 26.25.PACK_SENT ( 1 )

26.3.CPU_BUSY ( 1 ) 26.26.QUOTED_IDENTIFIER ( 1 )

26.4.DBCC ( 10 ) 26.27.rowcount ( 7 )

26.5.DB_ID ( 1 ) 26.28.ROWCOUNT_BIG ( 1 )

26.6.ERROR ( 11 ) 26.29.SERVERNAME ( 1 )

26.7.ERROR_NUMBER ( 1 ) 26.30.SERVERPROPERTY ( 2 )

26.8.ERROR_SEVERITY ( 1 ) 26.31.SERVICENAME ( 1 )

26.9.FORCEPLAN ( 1 ) 26.32.SESSIONPROPERTY ( 7 )

26.10.HashBytes ( 1 ) 26.33.SESSION_USER ( 1 )

26.11.HOST_ID ( 1 ) 26.34.SHOWPLAN_TEXT ( 2 )

26.12.HOST_NAME ( 1 ) 26.35.SPID ( 1 )

26.13.IDLE ( 1 ) 26.36.SUSER_ID ( 1 )

26.14.Information_schema ( 10 ) 26.37.SUSER_NAME ( 1 )

26.15.IO_BUSY ( 1 ) 26.38.TIMETICKS ( 1 )

26.16.LANGID ( 1 ) 26.39.TOTAL_ERRORS ( 1 )

26.17.LOCK_TIMEOUT ( 2 ) 26.40.TOTAL_READ ( 1 )

26.18.NESTLEVEL ( 1 ) 26.41.TOTAL_WRITE ( 1 )

26.19.NOCOUNT ( 1 ) 26.42.VERSION ( 2 )

26.20.OBJECTPROPERTY ( 4 ) 26.43.XACT_ABORT ( 3 )

26.21.OBJECT_ID ( 5 ) 26.44.xp_cmdshell ( 2 )

26.22.OBJECT_NAME ( 1 ) 26.45.xp_msver ( 2 )

26.23.PACKET_ERRORS ( 1 ) 26.46.xp_sendmail ( 1 )

27.System Tables Views

27.1.sys.columns ( 1 ) 27.19.sys.triggers ( 3 )

27.2.sys.databases ( 3 ) 27.20.sys.types ( 1 )

27.3.sys.database_principals ( 2 ) 27.21.sys.views ( 3 )

27.4.sys.fn_varbintohexstr ( 1 ) 27.22.sys.xml_schema_collections ( 1 )

27.5.sys.fulltext_catalogs ( 1 ) 27.23.sys.xml_schema_namespaces ( 1 )

27.6.sys.fulltext_indexes ( 1 ) 27.24.syscolumns ( 2 )

27.7.sys.fulltext_index_columns ( 1 ) 27.25.syscomments ( 3 )

27.8.sys.indexes ( 1 ) 27.26.sysconstraints ( 2 )

27.9.sys.messages ( 1 ) 27.27.sysdatabases ( 2 )

27.10.sys.objects ( 4 ) 27.28.sysdepends ( 2 )

27.11.sys.schemas ( 3 ) 27.29.sysfiles ( 1 )

27.12.sys.server_principals ( 1 ) 27.30.sysindexes ( 3 )

27.13.sys.server_sql_modules ( 1 ) 27.31.syslogins ( 1 )

27.14.sys.server_triggers ( 1 ) 27.32.sysmessages ( 1 )

27.15.sys.sql_dependencies ( 1 ) 27.33.sysobjects ( 5 )

27.16.sys.sql_modules ( 2 ) 27.34.systypes ( 1 )

27.17.sys.syscacheobjects ( 1 ) 27.35.SYSUSERS ( 2 )

27.18.sys.syslogins ( 1 )

28.User Role

28.1.CREATE CERTIFICATE ( 2 ) 28.8.REVOKE ( 6 )

28.2.CREATE SCHEMA ( 1 ) 28.9.Role ( 11 )

28.3.CURRENT_USER ( 1 ) 28.10.schema ( 3 )

28.4.ALTER AUTHORIZATION ( 1 ) 28.11.USER ( 8 )

28.5.DENY ( 2 ) 28.12.USER_ID ( 1 )

28.6.GRANT ( 14 ) 28.13.USER_NAME ( 1 )

28.7.LOGIN ( 13 )

29.CLR

29.1.ASSEMBLY ( 4 ) 29.2.CLR ( 6 )