| ABS | |||||
| ATAN | |||||
| ATN2 | |||||
| AVG | |||||
| CAST | |||||
| CEILING | |||||
| CERTPROPERTY | |||||
| CHARINDEX | |||||
| CHECKSUM | |||||
| CHECKSUM_AGG | |||||
| COALESCE | |||||
| COLUMNS_UPDATED | |||||
| CONVERT | |||||
| COS | |||||
| COT | |||||
| COUNT | |||||
| COUNT_BIG | |||||
| DATEADD | SELECT DATEADD(month, 1, '08/31/2006') | ||||
| DATEDIFF | |||||
| DATENAME | SELECT DATENAME(year,'1995-10-30
12:15:32.123'); SELECT DATENAME(yy,'1995-10-30 12:15:32.123'); |
||||
| DATEPART | |||||
| DAY | SELECT DAY('03/12/1998') AS 'Day Number'; | ||||
| DB_ID | SELECT DB_ID(N'AdventureWorks')
AS [Database ID]; GO |
||||
| DECRYPTBYASYMKEY | SELECT
CONVERT(nvarchar(max), DecryptByAsymKey( AsymKey_Id('JanainaAsymKey02'), ProtectedData, N'pGFD4bb925DGvbd2439587y' )) AS DecryptedData FROM [AdventureWorks].[Sales].[ProtectedData04] WHERE Description = N'encrypted by asym key''JanainaAsymKey02'''; GO |
||||
| DECRYPTBYCERT | |||||
| DECRYPTBYKEY | |||||
| DECRYPTBYKEYAUTOASYMKEY | |||||
| DECRYPTBYKEYAUTOCERT | |||||
| DECRYPTBYPASSPHRASE | |||||
| DEGREES | SELECT 'The number of degrees in
PI/2 radians is: ' + CONVERT(varchar, DEGREES((PI()/2))); GO |
||||
| DENSE_RANK | USE AdventureWorks; GO SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity) AS DENSE_RANK FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID ORDER BY Name; GO |
||||
| DIFFERENCE | USE AdventureWorks; GO -- Returns a DIFFERENCE value of 4, the least possible difference. SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene'); |
||||
| ELSE (IF … ELSE) | |||||
| ENCRYPTBYASYMKEY | |||||
| ENCRYPTBYCERT | |||||
| ENCRYPTBYKEY | |||||
| ENCRYPTBYPASSPHRASE | |||||
| END (BEGIN … END) | |||||
| ERROR_LINE | |||||
| ERROR_MESSAGE | |||||
| ERROR_NUMBER | |||||
| ERROR_PROCEDURE | |||||
| ERROR_SEVERITY | |||||
| ERROR_STATE | |||||
| EVENTDATA | Returns information about server
or database events. EVENTDATA is called when an event notification fires, and
the results are returned to the specified service broker. EVENTDATA can also
be used inside the body of a DDL trigger. |
USE AdventureWorks; GO CREATE TRIGGER safety ON DATABASE FOR CREATE_TABLE AS PRINT 'CREATE TABLE Issued.' SELECT EVENTDATA().value ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') RAISERROR ('New tables cannot be created in this database.', 16, 1) ROLLBACK ; GO --Test the trigger. CREATE TABLE NewTable (Column1 int); GO --Drop the trigger. DROP TRIGGER safety ON DATABASE GO |
<EVENT_INSTANCE> <EventType>type</EventType> <PostTime>date-time</PostTime> <SPID>spid</SPID> <ServerName>name</ServerName> <LoginName>name</LoginName> <UserName>name</UserName> <DatabaseName>name</DatabaseName> <SchemaName>name</SchemaName> <ObjectName>name</ObjectName> <ObjectType>type</ObjectType> <TSQLCommand>command</TSQLCommand> </EVENT_INSTANCE> |
||
| FLOOR | |||||
| fn_get_sql | |||||
| fn_helpcollations | |||||
| fn_listextendedproperty | Returns extended property values of database objects. | ||||
| fn_my_permissions | fn_my_permissions | ||||
| fn_servershareddrives | |||||
| fn_trace_geteventinfo | Returns information about an event being traced. | ||||
| fn_trace_getfilterinfo | |||||
| fn_trace_getinfo | |||||
| fn_trace_gettable | |||||
| fn_virtualfilestats | |||||
| fn_virtualservernodes | |||||
| GET_TRANSMISSION_STATUS | SELECT Status = GET_TRANSMISSION_STATUS('58ef1d2d-c405-42eb-a762-23ff320bddf0') ; |
||||
| GETANSINULL | Returns the default nullability
for the database for this session. |
USE AdventureWorks; GO SELECT GETANSINULL('AdventureWorks') GO |
|||
| GETDATE | |||||
| GETUTCDATE | |||||
| GO | Signals the end of a batch of
Transact-SQL statements to the SQL Server utilities. |
||||
| GOTO | Alters the flow of execution to
a label. The Transact-SQL statement or statements that follow GOTO are
skipped and processing continues at the label. GOTO statements and labels can
be used anywhere within a procedure, batch, or statement block. GOTO statements
can be nested. |
Define the label: label : Alter the execution: GOTO label |
|||
| HAS_DBACCESS | |||||
| HAS_PERMS_BY_NAME | |||||
| HASBYTES | |||||
| HOST_ID | |||||
| HOST_NAME | |||||
| IDENT_CURRENT | |||||
| IDENT_INCR | |||||
| IDENT_SEED | |||||
| IDENTITY | |||||
| INDEX_COL | |||||
| IS_MEMBER | |||||
| IS_SRVROLEMEMBER | |||||
| ISDATE | |||||
| ISNULL | |||||
| ISNUMERIC | |||||
| KEY_GUID | |||||
| KEY_ID | |||||
| LEFT | |||||
| LEN | |||||
| LIKE | |||||
| LOAD | |||||
| LOG | |||||
| LOG10 | |||||
| LOGINPROPERTYT | |||||
| LOWER | |||||
| LTRIM | |||||
| MAX | |||||
| MIN | |||||
| MONTH | |||||
| NCHAR | |||||
| NEWID | |||||
| NEWSEQUENTIALID | |||||
| NOT | |||||
| NTILE | |||||
| NULLIF | |||||
| OBJECT_DEFINITION | |||||
| OBJECT_ID | |||||
| OBJECT_NAME | |||||
| OBJECTPROPERTY | USE AdventureWorks; GO IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 1 PRINT 'UnitMeasure is a table.' ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 0 PRINT 'UnitMeasure is not a table.' ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') IS NULL PRINT 'ERROR: UnitMeasure is not a valid object.'; GO |
||||
| OPEN | |||||
| OPENDATASOURCE | |||||
| OPENDATASOURCE | |||||
| OPENQUERY | Executes the specified
pass-through query on the specified linked server. This server is an OLE DB
data source. OPENQUERY can be referenced in the FROM clause of a query as if
it were a table name. OPENQUERY can also be referenced as the target table of
an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities
of the OLE DB provider. Although the query may return multiple result sets,
OPENQUERY returns only the first one. |
EXEC sp_addlinkedserver
'OracleSvr', 'Oracle 7.3', 'MSDAORA', 'ORCLDB' GO SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') GO |
|||
| OPENROWSET | Includes all connection
information that is required to access remote data from an OLE DB data
source. This method is an alternative to accessing tables in a linked server
and is a one-time, ad hoc method of connecting and accessing remote data by
using OLE DB. For more frequent references to OLE DB data sources, use linked
servers instead. For more information, see Linking Servers. The OPENROWSET
function can be referenced in the FROM clause of a query as if it were a
table name. The OPENROWSET function can also be referenced as the target
table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities
of the OLE DB provider. Although the query might return multiple result sets,
OPENROWSET returns only the first one. |
USE Northwind GO SELECT c.*, o.* FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders) AS o ON c.CustomerID = o.CustomerID GO |
|||
| OPENXML | |||||
| ORIGINAL_LOGIN | |||||
| PI | |||||
| POWER | |||||
| PUBLISHINGSERVERNAME | |||||
| QUOTENAME | |||||
| RADIANS | |||||
| RAISERROR | |||||
| RAND | |||||
| RANK | |||||
| READTEXT | |||||
| RECEIVE | |||||
| REPLACE | |||||
| REPLICATE | |||||
| REVERSE | |||||
| REVERT | |||||
| RIGHT | |||||
| ROUND | |||||
| ROW_NUMBER | |||||
| ROWCOUNT_BIG | |||||
| RTRIM | |||||
| SCHEMA_ID | |||||
| SCHEMA_NAME | |||||
| SCOPE_IDENTITY | |||||
| SELECT | |||||
| SEND | |||||
| SERVERPROPERTY | |||||
| SESSION_USER | |||||
| SESSIONPROPERTY | |||||
| SETUSER | |||||
| SIGN | |||||
| SIGNBYASYMKEY | |||||
| SIGNBYCERT | |||||
| SIN | |||||
| SOUND | |||||
| SPACE | |||||
| SQL_VARIANT | |||||
| SQRT | |||||
| SQUART | |||||
| STATS_DATE | |||||
| STDEV | |||||
| STR | |||||
| STUFF | |||||
| SUBSTRING | |||||
| SUM | |||||
| SUSER_ID | |||||
| SUSER_NAME | |||||
| SUSER_SID | |||||
| SUSER_SNAME | |||||
| SYSTEM_USER | |||||
| TAN | |||||
| TERTIARY_WEIGHTS | |||||
| TEXTPTR | |||||
| TEXTVALID | |||||
| TRIGGER_NESTLEVEL | Returns the number of triggers
executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used
in DML and DDL triggers to determine the current level of nesting. |
IF ( ( SELECT TRIGGER_NESTLEVEL
( ( SELECT object_id FROM sys.triggers WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 ) RAISERROR ('Trigger abc nested more than 5 levels.',16,-1) |
|||
| TYPE_ID | |||||
| TYPE_NAME | |||||
| TYPEPROPERTY | |||||
| UNICODE | |||||
| UPDATE | Returns a Boolean value that
indicates whether an INSERT or UPDATE attempt was made on a specified column
of a table or view. UPDATE() is used anywhere inside the body of a
Transact-SQL INSERT or UPDATE trigger to test whether the trigger should
execute certain actions. |
USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER Person.reminder; GO CREATE TRIGGER reminder ON Person.Address AFTER UPDATE AS IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) ) BEGIN RAISERROR (50009, 16, 10) END; GO -- Test the trigger. UPDATE Person.Address SET PostalCode = 99999 WHERE PostalCode = '12345'; GO |
|||
| UPPER | |||||
| USE | |||||
| USER_ID | |||||
| USER_NAME | |||||
| VAR | SQL Server 2005 Books
Online VAR (Transact-SQL) Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause. Transact-SQL Syntax Conventions Syntax VAR ( [ ALL | DISTINCT ] expression ) |
USE AdventureWorks; GO SELECT VAR(Bonus) FROM Sales.SalesPerson; GO |
|||
| VARP | |||||
| VERIFYSIGNEDBYASYMKEY | |||||
| VERIFYSIGNEDBYCERT | |||||
| WAITFOR | |||||
| WHILE | |||||
| WITH | Specifies a temporary named
result set, known as a common table expression (CTE). This is derived from a
simple query and defined within the execution scope of a single SELECT,
INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE
VIEW statement as part of its defining SELECT statement. A common table
expression can include references to itself. This is referred to as a
recursive common table expression. |
USE AdventureWorks; GO WITH DirReps(ManagerID, DirectReports) AS ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID; GO |
WITH DirReps (Manager,
DirectReports) AS ( SELECT ManagerID, COUNT(*) AS DirectReports FROM HumanResources.Employee GROUP BY ManagerID ) SELECT AVG(DirectReports) AS [Average Number of Direct Reports] FROM DirReps WHERE DirectReports>= 2 ; GO |
USE AdventureWorks; GO WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate) AS ( SELECT SalesPersonID, COUNT(*), MAX(OrderDate) FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ) SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate, E.ManagerID, OM.NumberOfOrders, OM.MaxDate FROM HumanResources.Employee AS E JOIN Sales_CTE AS OS ON E.EmployeeID = OS.SalesPersonID LEFT OUTER JOIN Sales_CTE AS OM ON E.ManagerID = OM.SalesPersonID ORDER BY E.EmployeeID; GO |
|
| WITH XMLNAMESPACES | Declares one or more XML
namespaces. |
||||
| WRITETEXT | |||||
| XACT_STATE | Is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. | USE AdventureWorks; GO -- SET XACT_ABORT ON will render the transaction uncommittable -- when the constraint violation occurs. SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- A FOREIGN KEY constraint exists on this table. This -- statement will generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980; -- If the delete operation succeeds, commit the transaction. The CATCH -- block will not execute. COMMIT TRANSACTION; END TRY BEGIN CATCH -- Test XACT_STATE for 0, 1, or -1. -- If 1, the transaction is committable. -- If -1, the transaction is uncommittable and should -- be rolled back. -- XACT_STATE = 0 means there is no transaction and -- a commit or rollback operation would generate an error. -- Test whether the transaction is uncommittable. IF (XACT_STATE()) = -1 BEGIN PRINT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test whether the transaction is active and valid. IF (XACT_STATE()) = 1 BEGIN PRINT 'The transaction is committable.' + ' Committing transaction.' COMMIT TRANSACTION; END; END CATCH; GO |
|||
| XML | |||||
| XML_SCHEMA_NAMESPACE | |||||
| YEAR |