You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
3. Copy and paste the following example into the query window and click **Execute**. The example creates a table and defines a primary keyon the column `CustomerID`and a clustered index on`TransactionID`.
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 or logon trigger.
35
+
This function returns information about server or database events. When an event notification fires, and the specified service broker receives the results, `EVENTDATA` is called. A DDL or logon trigger also support internal use of `EVENTDATA`.
36
36
37
37
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif"Topic link icon")[Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
38
38
@@ -44,12 +44,17 @@ EVENTDATA( )
44
44
```
45
45
46
46
## Remarks
47
-
EVENTDATA returns data only when referenced directly inside of a DDL or logon trigger. EVENTDATA returns null if it is called by other routines, even if those routines are called by a DDL or logon trigger.
47
+
`EVENTDATA` returns data only when referenced directly inside of a DDL or logon trigger. `EVENTDATA` returns null if other routines call it, even if a DDL or logon trigger calls those routines.
48
48
49
-
Data returned by EVENTDATA is not valid after a transaction that called EVENTDATA, either implicitly or explicitly, commits or is rolled back.
49
+
Data returned by `EVENTDATA` is invalid after a transaction that
50
+
51
+
+ called `EVENTDATA` explicitly
52
+
+ called `EVENTDATA` implicitly
53
+
+ commits
54
+
+ is rolled back
50
55
51
56
> [!CAUTION]
52
-
> EVENTDATA returns XML data. This data is sent to the client as Unicode that uses 2 bytes for each character. The following Unicode code points can be represented in the XML that is returned by EVENTDATA:
57
+
> `EVENTDATA` returns XML data, sent to the client as Unicode that uses 2 bytes for each character. `EVENTDATA` returns XML that can represent these Unicode code points:
53
58
>
54
59
> `0x0009`
55
60
>
@@ -61,24 +66,24 @@ EVENTDATA( )
61
66
>
62
67
> `>= 0xE000 && <= 0xFFFD`
63
68
>
64
-
> Some characters that can appear in [!INCLUDE[tsql](../../includes/tsql-md.md)] identifiers and data are not expressible or permissible in XML. Characters or data that have code points not shown in the previous list are mapped to a question mark (?).
69
+
> XML cannot express, and will not permit, some characters that can appear in [!INCLUDE[tsql](../../includes/tsql-md.md)] identifiers and data. Characters or data that have code points not shown in the previous list are mapped to a question mark (?).
65
70
66
-
To protect the security of logins, when CREATE LOGIN or ALTER LOGIN statements are executed, passwords are not displayed.
71
+
Passwords do not display when `CREATE LOGIN` or `ALTER LOGIN` statements execute. This protects login security.
67
72
68
73
## Schemas Returned
69
-
EVENTDATA returns a value of type **xml**. By default, the schema definition for all events is installed in the following directory: [!INCLUDE[ssInstallPath](../../includes/ssinstallpath-md.md)]Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.
74
+
EVENTDATA returns a value of data type **xml**. By default, the schema definition for all events installs in this directory: [!INCLUDE[ssInstallPath](../../includes/ssinstallpath-md.md)]Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.
70
75
71
-
Alternatively, the event schema is published at the [Microsoft SQL Server XML Schemas](http://go.microsoft.com/fwlink/?LinkID=31850)Web page.
76
+
The [Microsoft SQL Server XML Schemas](http://go.microsoft.com/fwlink/?LinkID=31850)web page also has the event schema.
72
77
73
-
To extract the schema for any particular event, search the schema for the Complex Type `EVENT_INSTANCE_\<event_type>`. For example, to extract the schema for the DROP_TABLE event, search the schema for `EVENT_INSTANCE_DROP_TABLE`.
78
+
To extract the schema for any particular event, search the schema for the Complex Type `EVENT_INSTANCE_<event_type>`. For example, to extract the schema for the `DROP_TABLE` event, search the schema for `EVENT_INSTANCE_DROP_TABLE`.
74
79
75
80
## Examples
76
81
77
82
### A. Querying event data in a DDL trigger
78
-
The following example creates a DDL trigger to prevent new tables from being created in the database. The [!INCLUDE[tsql](../../includes/tsql-md.md)] statement that fires the trigger is captured by using XQuery against the XML data that is generated by EVENTDATA. For more information, see [XQuery Language Reference (SQL Server)](../../xquery/xquery-language-reference-sql-server.md).
83
+
This example creates a DDL trigger that prevents creation of new database tables. Use of XQuery against the XML data generated by `EVENTDATA` captures the [!INCLUDE[tsql](../../includes/tsql-md.md)] statement that fires the trigger. See [XQuery Language Reference (SQL Server)](../../xquery/xquery-language-reference-sql-server.md) for more information.
79
84
80
85
> [!NOTE]
81
-
> When you query the `\<TSQLCommand>` element by using **Results to Grid** in [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)], line breaks in the command text do not appear. Use **Results to Text** instead.
86
+
> When using **Results to Grid** in [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] to query the `<TSQLCommand>` element, line breaks in the command text do not appear. Use **Results to Text** instead.
82
87
83
88
```
84
89
USE AdventureWorks2012;
@@ -104,10 +109,10 @@ GO
104
109
```
105
110
106
111
> [!NOTE]
107
-
> When you want to return event data, we recommend that you use the XQuery **value()** method instead of the **query()** method. The **query()** method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the **value()** method renders CR/LF instances invisible in the output.
112
+
> To return event data, use the XQuery **value()** method instead of the **query()** method. The **query()** method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the **value()** method renders CR/LF instances invisible in the output.
108
113
109
114
### B. Creating a log table with event data in a DDL trigger
110
-
The following example creates a table to store information about all database level events, and populates the table with a DDL trigger. The event type and [!INCLUDE[tsql](../../includes/tsql-md.md)] statement are captured by using XQuery against the XML data generated by `EVENTDATA`.
115
+
This example creates a table for information storage about all database level events, and populates that table with a DDL trigger. Use of XQuery against the XML data generated by `EVENTDATA` captures the event type and the [!INCLUDE[tsql](../../includes/tsql-md.md)] statement.
Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
30
+
This function returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
31
31
32
32
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif"Topic link icon")[Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
33
33
34
34
## Syntax
35
35
36
36
```
37
-
38
37
@@FETCH_STATUS
39
38
```
40
39
@@ -45,20 +44,20 @@ manager: craigg
45
44
46
45
|Return value|Description|
47
46
|------------------|-----------------|
48
-
|0|The FETCH statement was successful.|
47
+
| 0|The FETCH statement was successful.|
49
48
|-1|The FETCH statement failed or the row was beyond the result set.|
50
49
|-2|The row fetched is missing.|
51
50
|-9|The cursor is not performing a fetch operation.|
52
51
53
52
## Remarks
54
-
Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.
53
+
Because `@@FETCH_STATUS` is global to all cursors on a connection, use it carefully. After a FETCH statement executes, the test for `@@FETCH_STATUS` must occur before any other FETCH statement executes against another cursor. `@@FETCH_STATUS` is undefined before any fetches have occurred on the connection.
55
54
56
-
For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes the results from another cursor. When control is returned from the called stored procedure, @@FETCH_STATUS reflects the last FETCH executed in the stored procedure, not the FETCH statement executed before the stored procedure is called.
55
+
For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes results from another cursor. When control returns from that called stored procedure, `@@FETCH_STATUS` reflects the last FETCH executed inside that stored procedure, not the FETCH statement executed before the call to the stored procedure.
57
56
58
-
To retrieve the last fetch status of a specific cursor, query the **fetch_status** column of the **sys.dm_exec_cursors** dynamic management function.
57
+
To retrieve the last fetch status of a specific cursor, query the **fetch_status** column of the **sys.dm_exec_cursors** dynamic management function.
59
58
60
59
## Examples
61
-
The following example uses `@@FETCH_STATUS` to control cursor activities in a `WHILE` loop.
60
+
This example uses `@@FETCH_STATUS` to control cursor activities in a `WHILE` loop.
Returns the file identification (ID) number for the given logical file name in the current database.
33
+
For the given logical name for a component file of the current database, this function returns the file identification (ID) number.
34
34
35
35
> [!IMPORTANT]
36
36
> [!INCLUDE[ssNoteDepFutureAvoid](../../includes/ssnotedepfutureavoid-md.md)] Use [FILE_IDEX](../../t-sql/functions/file-idex-transact-sql.md) instead.
@@ -40,25 +40,26 @@ manager: craigg
40
40
## Syntax
41
41
42
42
```
43
-
44
43
FILE_ID ( file_name )
45
44
```
46
45
47
46
## Arguments
48
-
*file_name*
49
-
Is an expression of type **sysname** that represents the name of the file for which to return the file ID.
47
+
*file_name*
48
+
An expression of type **sysname**, representing the logical name of the file whose file ID value `FILE_ID` will return.
50
49
51
50
## Return Types
52
-
**smallint**
51
+
**smallint**
53
52
54
53
## Remarks
55
-
*file_name* corresponds to the logical file name displayed in the name column in the sys.master_files or sys.database_files catalog views.
54
+
*file_name* corresponds to the logical file name displayed in the name column of the sys.master_files or sys.database_files catalog views.
55
+
56
+
`FILE_ID` returns `NULL` if *file_name* does not correspond to the logical name of a component file of the current database.
56
57
57
-
In [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], the file identification number assigned to full-text catalogs is greater than 32767. Because the return type of the FILE_ID function is **smallint**, this function cannot be used for full-text files. Use [FILE_IDEX](../../t-sql/functions/file-idex-transact-sql.md) instead.
58
+
In [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], the file identification number assigned to full-text catalogs exceeds 32767. Because the `FILE_ID` function has a **smallint** return type, `FILE_ID` will not support full-text files. Use [FILE_IDEX](../../t-sql/functions/file-idex-transact-sql.md) instead.
58
59
59
60
## Examples
60
-
The following example returns the file ID for the `AdventureWorks_Data` file.
61
-
61
+
This example returns the file ID value for the `AdventureWorks_Data` file, a component file of the `ADVENTUREWORKS2012` database.
Returns the logical file name for the given file identification (ID) number.
36
+
This function returns the logical file name for a given file identification (ID) number.
37
37
38
38
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif"Topic link icon")[Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
39
39
@@ -44,17 +44,17 @@ FILE_NAME ( file_id )
44
44
```
45
45
46
46
## Arguments
47
-
*file_id*
48
-
Is the file identification number for which to return the file name. *file_id*is **int**.
47
+
*file_id*
48
+
The file identification number whose file name `FILE_NAME` will return. *file_id*has an **int** data type.
49
49
50
50
## Return Types
51
-
**nvarchar(128)**
51
+
**nvarchar(128)**
52
52
53
53
## Remarks
54
-
*file_ID* corresponds to the file_id column in the sys.master_files or sys.database_files catalog views.
54
+
*file_ID* corresponds to the file_id column in the sys.master_files catalog view or the sys.database_files catalog view.
55
55
56
56
## Examples
57
-
The following example returns the file names for `file_ID 1` and `file_ID` in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database.
57
+
This example returns the file names for `file_ID 1` and `file_ID` in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database.
58
58
59
59
```sql
60
60
SELECT FILE_NAME(1) AS'File Name 1', FILE_NAME(2) AS'File Name 2';
Returns the filegroup identification (ID) number for a specified filegroup name.
32
+
This function returns the filegroup identification (ID) number for a specified filegroup name.
33
33
34
34
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif"Topic link icon")[Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
35
35
36
36
## Syntax
37
37
38
38
```
39
-
40
39
FILEGROUP_ID ( 'filegroup_name' )
41
40
```
42
41
43
42
## Arguments
44
-
**'***filegroup_name***'**
45
-
Is an expression of type **sysname** that represents the filegroup name for which to return the filegroup ID.
43
+
*filegroup_name*
44
+
An expression of type **sysname**, representing the filegroup name whose filegroup ID `FILEGROUP_ID` will return.
46
45
47
46
## Return Types
48
-
**int**
47
+
**int**
49
48
50
49
## Remarks
51
-
*filegroup_name* corresponds to the **name** column in the **sys.filegroups** catalog view.
50
+
*filegroup_name* corresponds to the **name** column in the **sys.filegroups** catalog view.
52
51
53
52
## Examples
54
-
The following example returns the filegroup ID for the filegroup named `PRIMARY` in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database.
53
+
This example returns the filegroup ID for the filegroup named `PRIMARY` in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database.
Returns the filegroup name for the specified filegroup identification (ID) number.
35
+
This function returns the filegroup name for the specified filegroup identification (ID) number.
36
36
37
37
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif"Topic link icon")[Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
38
38
39
39
## Syntax
40
40
41
41
```
42
-
43
42
FILEGROUP_NAME ( filegroup_id )
44
43
```
45
44
46
45
## Arguments
47
46
*filegroup_id*
48
-
Is the filegroup ID number for which to return the filegroup name. *filegroup_id* is **smallint**.
47
+
48
+
The filegroup ID number whose filegroup name `FILEGROUP_NAME` will return. *filegroup_id* has a **smallint** data type.
49
49
50
50
## Return Types
51
-
**nvarchar(128)**
51
+
**nvarchar(128)**
52
52
53
53
## Remarks
54
-
*filegroup_id* corresponds to the **data_space_id** column in the **sys.filegroups** catalog view.
54
+
*filegroup_id* corresponds to the **data_space_id** column of the **sys.filegroups** catalog view.
55
55
56
56
## Examples
57
-
The following example returns the filegroup name for the filegroup ID `1` in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database.
57
+
This example returns the filegroup name for filegroup ID `1` in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database.
Is a positive integer constant expression that specifies the number of groups into which each partition must be divided. *integer_expression* can be of type **int**, or **bigint**.
44
+
Is a positive integer expression that specifies the number of groups into which each partition must be divided. *integer_expression* can be of type **int**, or **bigint**.
45
45
46
46
\<partition_by_clause>
47
47
Divides the result set produced by the [FROM](../../t-sql/queries/from-transact-sql.md) clause into partitions to which the function is applied. For the PARTITION BY syntax, see [OVER Clause (Transact-SQL)](../../t-sql/queries/select-over-clause-transact-sql.md).
0 commit comments