Transact-SQL 概述 Transact-SQL 语法规则 Transact-SQL 中的新特性和增强的功能 !> （不大于） !< （不小于） !=（不等于） % （通配符 -- 需匹配的字符） %（模） &（按位 AND） >（大于） >=（大于或等于） <>（不等于） <（小于） <=（小于或等于） -（负） -（减） --（注释） *（乘） /（除） /*...*/（注释） @@CONNECTIONS @@CPU_BUSY @@CURSOR_ROWS @@DATEFIRST @@DBTS @@ERROR @@FETCH_STATUS @@IDENTITY @@IDLE @@IO_BUSY @@LANGID @@LANGUAGE @@LOCK_TIMEOUT @@MAX_CONNECTIONS @@MAX_PRECISION @@NESTLEVEL @@OPTIONS @@PACK_RECEIVED @@PACK_SENT @@PACKET_ERRORS @@PROCID @@REMSERVER @@ROWCOUNT @@SERVERNAME @@SERVICENAME @@SPID @@TEXTSIZE @@TIMETICKS @@TOTAL_ERRORS @@TOTAL_READ @@TOTAL_WRITE @@TRANCOUNT @@VERSION [ ]（通配符 -- 需匹配的字符） [^]（通配符字符 -- 无需匹配的字符） ^（按位互斥 OR） _（通配符 -- 匹配一个字符） |（按位 OR） ~（按位 NOT） +（加） +（正） +（字符串串联） =（等于） ABS ACOS ALL ALTER DATABASE ALTER FUNCTION ALTER PROCEDURE ALTER TABLE ALTER TRIGGER ALTER VIEW AND ANY APP_NAME ASCII ASIN ATAN ATN2 AVG BACKUP BEGIN DISTRIBUTED TRANSACTION BEGIN TRANSACTION BEGIN...END BETWEEN binary 和 varbinary BINARY_CHECKSUM bit BREAK BULK INSERT CASE CAST 和 CONVERT CEILING CHAR char 和 varchar CHARINDEX CHECKPOINT CHECKSUM CHECKSUM_AGG CLOSE COALESCE COL_LENGTH COL_NAME COLLATE
SQL 排序规则名称
Windows 排序规则名称
COLLATIONPROPERTY COLUMNPROPERTY COMMIT TRANSACTION COMMIT WORK COMPUTE 子句 CONTAINS CONTAINSTABLE CONTINUE COS COT COUNT COUNT_BIG CREATE DATABASE CREATE DEFAULT CREATE FUNCTION CREATE INDEX CREATE PROCEDURE CREATE RULE CREATE SCHEMA CREATE STATISTICS CREATE TABLE CREATE TRIGGER CREATE VIEW CURRENT_TIMESTAMP CURRENT_USER cursor CURSOR_STATUS DATABASEPROPERTY DATABASEPROPERTYEX DATALENGTH DATEADD DATEDIFF DATENAME DATEPART datetime 和 smalldatetime DAY DB_ID DB_NAME DBCC
DBCC CHECKALLOC
DBCC CHECKCATALOG
DBCC CHECKCONSTRAINTS
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKIDENT
DBCC CHECKTABLE
DBCC CLEANTABLE
DBCC CONCURRENCYVIOLATION
DBCC DBREINDEX
DBCC DBREPAIR
DBCC dllname (FREE)
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC HELP
DBCC INDEXDEFRAG
DBCC INPUTBUFFER
DBCC NEWALLOC
DBCC OPENTRAN
DBCC OUTPUTBUFFER
DBCC PINTABLE
DBCC PROCCACHE
DBCC ROWLOCK
DBCC SHOW_STATISTICS
DBCC SHOWCONTIG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
DBCC SQLPERF
DBCC TRACEOFF
DBCC TRACEON
DBCC TRACESTATUS
DBCC UNPINTABLE
DBCC UPDATEUSAGE
DBCC USEROPTIONS
DEALLOCATE decimal 和 numeric DECLARE @local_variable DECLARE CURSOR DEGREES DELETE DENY DIFFERENCE DROP DATABASE DROP DEFAULT DROP FUNCTION DROP INDEX DROP PROCEDURE DROP RULE DROP STATISTICS DROP TABLE DROP TRIGGER DROP VIEW DUMP ELSE (IF...ELSE) END (BEGIN...END) EXECUTE EXISTS EXP FETCH FILE_ID FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY float 和 real FLOOR fn_helpcollations fn_listextendedproperty fn_servershareddrives fn_trace_geteventinfo fn_trace_getfilterinfo fn_trace_getinfo fn_trace_gettable fn_virtualfilestats fn_virtualservernodes FOR 子句 FORMATMESSAGE FREETEXT FREETEXTTABLE FROM FROM 子句 FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY GETANSINULL GETDATE GETUTCDATE GO GOTO GRANT GROUP BY GROUPING HAS_DBACCESS HAVING HAVING 子句 HOST_ID HOST_NAME IDENT_CURRENT IDENT_INCR IDENT_SEED IDENTITY（函数） IDENTITY（属性） IF...ELSE image IN INDEX_COL INDEXKEY_PROPERTY INDEXPROPERTY INSERT int、bigint、smallint 和 tinyint IS [NOT] NULL IS_MEMBER IS_SRVROLEMEMBER ISDATE ISNULL ISNUMERIC KILL LEFT LEN LIKE LOAD LOG LOG10 LOWER LTRIM MAX MIN money 和 smallmoney MONTH NCHAR nchar 和 nvarchar NEWID Northwind 示例数据库
Categories
CustomerCustomerDemo
CustomerDemographics
Customers
Employees
EmployeeTerritories
Order Details
Orders
Products
Region
Shippers
Suppliers
Territories
NOT ntext、text 和 image NULLIF numeric OBJECT_ID OBJECT_NAME OBJECTPROPERTY OPEN OPENDATASOURCE OPENQUERY OPENROWSET OPENXML OPTION 子句 OR ORDER BY ORDER BY 子句 PARSENAME PATINDEX PERMISSIONS PI POWER Predicate PRINT pubs 示例数据库
authors
discounts
employee
jobs
pub_info
publishers
roysched
sales
stores
titleauthor
titles
QUOTENAME RADIANS RAISERROR RAND READTEXT real RECONFIGURE REPLACE REPLICATE RESTORE RESTORE FILELISTONLY RESTORE HEADERONLY RESTORE LABELONLY RESTORE VERIFYONLY RETURN REVERSE REVOKE RIGHT ROLLBACK TRANSACTION ROLLBACK WORK ROUND ROWCOUNT_BIG RTRIM SAVE TRANSACTION SCOPE_IDENTITY SELECT
SELECT 示例
SELECT @local_variable SELECT 子句 SERVERPROPERTY SESSION_USER SESSIONPROPERTY SET
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET ARITHABORT
SET ARITHIGNORE
SET CONCAT_NULL_YIELDS_NULL
SET CONTEXT_INFO
SET CURSOR_CLOSE_ON_COMMIT
SET DATEFIRST
SET DATEFORMAT
SET DEADLOCK_PRIORITY
SET DISABLE_DEF_CNST_CHK
SET FIPS_FLAGGER
SET FMTONLY
SET FORCEPLAN
SET IDENTITY_INSERT
SET IMPLICIT_TRANSACTIONS
SET LANGUAGE
SET LOCK_TIMEOUT
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET OFFSETS
SET PARSEONLY
SET QUERY_GOVERNOR_COST_LIMIT
SET QUOTED_IDENTIFIER
SET REMOTE_PROC_TRANSACTIONS
SET ROWCOUNT
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET STATISTICS IO
SET STATISTICS PROFILE
SET STATISTICS TIME
SET TEXTSIZE
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT
SET @local_variable SETUSER SHUTDOWN SIGN SIN smalldatetime smallint smallmoney SOME | ANY SOUNDEX SPACE sql_variant SQL_VARIANT_PROPERTY SQRT SQUARE STATS_DATE STDEV STDEVP STR STUFF SUBSTRING SUM SUSER_ID SUSER_NAME SUSER_SID SUSER_SNAME SYSTEM_USER table TAN text TEXTPTR TEXTVALID timestamp tinyint TRIGGER_NESTLEVEL TRUNCATE TABLE TYPEPROPERTY UNICODE UNION UNION 运算符 uniqueidentifier UPDATE UPDATE STATISTICS UPDATETEXT UPPER USE USER USER_ID USER_NAME VAR varbinary varchar VARP WAITFOR WHERE WHERE 子句 WHILE WRITETEXT YEAR 保留关键字 表达式 常量 跟踪标记 函数
安全函数
聚合函数
配置函数
日期和时间函数
数学函数
文本和图像函数
系统函数
系统统计函数
行集函数
游标函数
元数据函数
字符串函数
控制流语言 事务 数据类型
精度、小数位数和长度
排序规则的优先顺序
数据类型的优先顺序
数据类型同义词
搜索条件 系统表
backupfile
backupmediafamily
backupmediaset
backupset
log_shipping_databases
log_shipping_monitor
log_shipping_plan_databases
log_shipping_plan_history
log_shipping_plans
log_shipping_primaries
log_shipping_secondaries
logmarkhistory
MSagent_parameters
MSagent_profiles
MSarticles
MSdistpublishers
MSdistribution_agents
MSdistribution_history
MSdistributiondbs
MSdistributor
MSdynamicsnapshotjobs
MSdynamicsnapshotviews
MSlogreader_agents
MSlogreader_history
MSmerge_agents
MSmerge_altsyncpartners
MSmerge_contents
MSmerge_delete_conflicts
MSmerge_errorlineage
MSmerge_genhistory
MSmerge_history
MSmerge_replinfo
MSmerge_subscriptions
MSmerge_tombstone
MSpub_identity_range
MSpublication_access
MSpublications
MSpublisher_databases
MSqreader_agents
MSqreader_history
MSrepl_backup_lsns
MSrepl_commands
MSrepl_errors
MSrepl_identity_range
MSrepl_originators
MSrepl_transactions
MSrepl_version
MSreplication_objects
MSreplication_options
MSreplication_queue
MSreplication_subscriptions
MSsnapshot_agents
MSsnapshot_history
MSsub_identity_range
MSsubscriber_info
MSsubscriber_schedule
MSsubscription_agents
MSsubscription_articles
MSsubscription_properties
MSsubscriptions
MSsync_states
restorefile
restorefilegroup
restorehistory
sysalerts
sysaltfiles
sysarticles
sysarticleupdates
syscacheobjects
syscategories
syscharsets
syscolumns
syscomments
sysconfigures
sysconstraints
syscurconfigs
sysdatabases
sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
sysdbmaintplans
sysdepends
sysdevices
sysdownloadlist
sysfilegroups
sysfiles
sysforeignkeys
sysfulltextcatalogs
sysindexes
sysindexkeys
sysjobhistory
sysjobs
sysjobschedules
sysjobservers
sysjobsteps
syslanguages
syslockinfo
syslogins
sysmembers
sysmergearticles
sysmergepublications
sysmergeschemaarticles
sysmergeschemachange
sysmergesubscriptions
sysmergesubsetfilters
sysmessages
sysnotifications
sysobjects
sysoledbusers
sysopentapes
sysoperators
sysperfinfo
syspermissions
sysprocesses
sysprotects
syspublications
sysreferences
sysremotelogins
sysreplicationalerts
sysschemaarticles
syssubscriptions
systargetservergroupmembers
systargetservergroups
systargetservers
systaskids
systypes
sysusers
系统存储过程
sp_ActiveDirectory_Obj
sp_ActiveDirectory_SCP
sp_add_alert
sp_add_category
sp_add_data_file_recover_suspect_db
sp_add_job
sp_add_jobschedule
sp_add_jobserver
sp_add_jobstep
sp_add_log_file_recover_suspect_db
sp_add_log_shipping_database
sp_add_log_shipping_plan
sp_add_log_shipping_plan_database
sp_add_log_shipping_primary
sp_add_log_shipping_secondary
sp_add_maintenance_plan
sp_add_maintenance_plan_db
sp_add_maintenance_plan_job
sp_add_notification
sp_add_operator
sp_add_targetservergroup
sp_add_targetsvrgrp_member
sp_addalias
sp_addapprole
sp_addextendedproc
sp_addextendedproperty
sp_addgroup
sp_addlinkedserver
sp_addlinkedsrvlogin
sp_addlogin
sp_addmessage
sp_addremotelogin
sp_addrole
sp_addrolemember
sp_addserver
sp_addsrvrolemember
sp_addtask
sp_addtype
sp_addumpdevice
sp_adduser
sp_altermessage
sp_apply_job_to_targets
sp_approlepassword
sp_attach_db
sp_attach_single_file_db
sp_autostats
sp_bindefault
sp_bindrule
sp_bindsession
sp_can_tlog_be_applied
sp_catalogs
sp_certify_removable
sp_change_monitor_role
sp_change_primary_role
sp_change_secondary_role
sp_change_users_login
sp_changedbowner
sp_changegroup
sp_changeobjectowner
sp_column_privileges
sp_column_privileges_ex
sp_columns
sp_columns_ex
sp_configure
sp_create_log_shipping_monitor_account
sp_create_removable
sp_createstats
sp_cursor_list
sp_cycle_errorlog
sp_databases
sp_datatype_info
sp_dbcmptlevel
sp_dbfixedrolepermission
sp_dboption
sp_dbremove
sp_defaultdb
sp_defaultlanguage
sp_define_log_shipping_monitor
sp_delete_alert
sp_delete_backuphistory
sp_delete_category
sp_delete_database_backuphistory
sp_delete_job
sp_delete_jobschedule
sp_delete_jobserver
sp_delete_jobstep
sp_delete_log_shipping_database
sp_delete_log_shipping_monitor_info
sp_delete_log_shipping_plan
sp_delete_log_shipping_plan_database
sp_delete_log_shipping_primary
sp_delete_log_shipping_secondary
sp_delete_maintenance_plan
sp_delete_maintenance_plan_db
sp_delete_maintenance_plan_job
sp_delete_notification
sp_delete_operator
sp_delete_targetserver
sp_delete_targetservergroup
sp_delete_targetsvrgrp_member
sp_denylogin
sp_depends
sp_describe_cursor
sp_describe_cursor_columns
sp_describe_cursor_tables
sp_detach_db
sp_dropalias
sp_dropapprole
sp_dropdevice
sp_dropextendedproc
sp_dropextendedproperty
sp_dropgroup
sp_droplinkedsrvlogin
sp_droplogin
sp_dropmessage
sp_dropremotelogin
sp_droprole
sp_droprolemember
sp_dropserver
sp_dropsrvrolemember
sp_droptask
sp_droptype
sp_dropuser
sp_dropwebtask
sp_enumcodepages
sp_executesql
sp_fkeys
sp_foreignkeys
sp_fulltext_catalog
sp_fulltext_column
sp_fulltext_database
sp_fulltext_service
sp_fulltext_table
sp_get_log_shipping_monitor_info
sp_getapplock
sp_getbindtoken
sp_grantdbaccess
sp_grantlogin
sp_help
sp_help_alert
sp_help_category
sp_help_downloadlist
sp_help_fulltext_catalogs
sp_help_fulltext_catalogs_cursor
sp_help_fulltext_columns
sp_help_fulltext_columns_cursor
sp_help_fulltext_tables
sp_help_fulltext_tables_cursor
sp_help_job
sp_help_jobhistory
sp_help_jobschedule
sp_help_jobserver
sp_help_jobstep
sp_help_maintenance_plan
sp_help_notification
sp_help_operator
sp_help_targetserver
sp_help_targetservergroup
sp_helpconstraint
sp_helpdb
sp_helpdbfixedrole
sp_helpdevice
sp_helpextendedproc
sp_helpfile
sp_helpfilegroup
sp_helpgroup
sp_helphistory
sp_helpindex
sp_helplanguage
sp_helplinkedsrvlogin
sp_helplogins
sp_helpntgroup
sp_helpremotelogin
sp_helprole
sp_helprolemember
sp_helprotect
sp_helpserver
sp_helpsort
sp_helpsrvrole
sp_helpsrvrolemember
sp_helpstats
sp_helptask
sp_helptext
sp_helptrigger
sp_helpuser
sp_indexes
sp_indexoption
sp_invalidate_textptr
sp_linkedservers
sp_lock
sp_makewebtask
sp_manage_jobs_by_login
sp_monitor
sp_MShasdbaccess
sp_msx_defect
sp_msx_enlist
sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OAMethod
sp_OASetProperty
sp_OAStop
sp_password
sp_pkeys
sp_post_msx_operation
sp_primarykeys
sp_processmail
sp_procoption
sp_purge_jobhistory
sp_purgehistory
sp_reassigntask
sp_recompile
sp_refreshview
sp_releaseapplock
sp_remoteoption
sp_remove_job_from_targets
sp_remove_log_shipping_monitor
sp_rename
sp_renamedb
sp_resetstatus
sp_resolve_logins
sp_resync_targetserver
sp_revokedbaccess
sp_revokelogin
sp_runwebtask
sp_scriptsubconflicttable
sp_server_info
sp_serveroption
sp_setapprole
sp_setnetname
sp_settriggerorder
sp_spaceused
sp_special_columns
sp_sproc_columns
sp_srvrolepermission
sp_start_job
sp_statistics
sp_stop_job
sp_stored_procedures
sp_table_privileges
sp_table_privileges_ex
sp_tableoption
sp_tables
sp_tables_ex
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
sp_unbindefault
sp_unbindrule
sp_update_alert
sp_update_category
sp_update_job
sp_update_jobschedule
sp_update_jobstep
sp_update_log_shipping_monitor_info
sp_update_log_shipping_plan
sp_update_log_shipping_plan_database
sp_update_notification
sp_update_operator
sp_update_targetservergroup
sp_updateextendedproperty
sp_updatestats
sp_updatetask
sp_validatelogins
sp_validname
sp_who
sp_xml_preparedocument
sp_xml_removedocument
xp_cmdshell
xp_deletemail
xp_enumgroups
xp_findnextmsg
xp_grantlogin
xp_logevent
xp_loginconfig
xp_logininfo
xp_msver
xp_readmail
xp_revokelogin
xp_sendmail
xp_sprintf
xp_sqlagent_proxy_account
xp_sqlmaint
xp_sscanf
xp_startmail
xp_stopmail
对象层次结构语法
复制存储过程
sp_add_agent_parameter
sp_add_agent_profile
sp_addarticle
sp_adddistpublisher
sp_adddistributiondb
sp_adddistributor
sp_addmergealternatepublisher
sp_addmergearticle
sp_addmergefilter
sp_addmergepublication
sp_addmergepullsubscription
sp_addmergepullsubscription_agent
sp_addmergesubscription
sp_addpublication
sp_addpublication_snapshot
sp_addpublisher70
sp_addpullsubscription
sp_addpullsubscription_agent
sp_addscriptexec
sp_addsubscriber
sp_addsubscriber_schedule
sp_addsubscription
sp_addsynctriggers
sp_addtabletocontents
sp_adjustpublisheridentityrange
sp_article_validation
sp_articlecolumn
sp_articlefilter
sp_articlesynctranprocs
sp_articleview
sp_attachsubscription
sp_browsemergesnapshotfolder
sp_browsereplcmds
sp_browsesnapshotfolder
sp_change_agent_parameter
sp_change_agent_profile
sp_change_subscription_properties
sp_changearticle
sp_changedistpublisher
sp_changedistributiondb
sp_changedistributor_password
sp_changedistributor_property
sp_changemergearticle
sp_changemergefilter
sp_changemergepublication
sp_changemergepullsubscription
sp_changemergesubscription
sp_changepublication
sp_changesubscriber
sp_changesubscriber_schedule
sp_changesubscriptiondtsinfo
sp_changesubstatus
sp_check_for_sync_trigger
sp_copymergesnapshot
sp_copysnapshot
sp_copysubscription
sp_deletemergeconflictrow
sp_disableagentoffload
sp_drop_agent_parameter
sp_drop_agent_profile
sp_dropanonymouseagent
sp_droparticle
sp_dropdistpublisher
sp_dropdistributiondb
sp_dropdistributor
sp_dropmergealternatepublisher
sp_dropmergearticle
sp_dropmergefilter
sp_dropmergepublication
sp_dropmergepullsubscription
sp_dropmergesubscription
sp_droppublication
sp_droppullsubscription
sp_dropsubscriber
sp_dropsubscription
sp_dsninfo
sp_dumpparamcmd
sp_enableagentoffload
sp_enumcustomresolvers
sp_enumdsn
sp_enumfullsubscribers
sp_expired_subscription_cleanup
sp_generatefilters
sp_get_distributor
sp_getagentoffloadinfo
sp_getmergedeletetype
sp_getqueuedrows
sp_getsubscriptiondtspackagename
sp_grant_publication_access
sp_help_agent_default
sp_help_agent_parameter
sp_help_agent_profile
sp_help_publication_access
sp_helparticle
sp_helparticlecolumns
sp_helparticledts
sp_helpdistpublisher
sp_helpdistributiondb
sp_helpdistributor
sp_helpmergealternatepublisher
sp_helpmergearticle
sp_helpmergearticlecolumn
sp_helpmergearticleconflicts
sp_helpmergeconflictrows
sp_helpmergedeleteconflictrows
sp_helpmergefilter
sp_helpmergepublication
sp_helpmergepullsubscription
sp_helpmergesubscription
sp_helppublication
sp_helppullsubscription
sp_helpreplfailovermode
sp_helpreplicationdboption
sp_helpreplicationoption
sp_helpsubscriberinfo
sp_helpsubscription
sp_helpsubscription_properties
sp_ivindexhasnullcols
sp_link_publication
sp_marksubscriptionvalidation
sp_mergearticlecolumn
sp_mergecleanupmetadata
sp_mergedummyupdate
sp_mergesubscription_cleanup
sp_publication_validation
sp_refreshsubscriptions
sp_reinitmergepullsubscription
sp_reinitmergesubscription
sp_reinitpullsubscription
sp_reinitsubscription
sp_removedbreplication
sp_repladdcolumn
sp_replcmds
sp_replcounters
sp_repldone
sp_repldropcolumn
sp_replflush
sp_replication_agent_checkup
sp_replicationdboption
sp_replsetoriginator
sp_replshowcmds
sp_repltrans
sp_restoredbreplication
sp_resyncmergesubscription
sp_revoke_publication_access
sp_script_synctran_commands
sp_setreplfailovermode
sp_showrowreplicainfo
sp_subscription_cleanup
sp_table_validation
sp_update_agent_profile
sp_validatemergepublication
sp_validatemergesubscription
sp_vupgrade_replication
sql_replqueuemonitor
信息架构视图
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINE_COLUMNS
ROUTINES
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
游标 运算符