SQL Server includes built-in functions that let you perform a variety of tasks, such as converting data, working with string values and performing mathematical calculations. Some SQL Server functions are categorized as system functions, which let you view information about update and insert operations, retrieve server property settings and access details about the current session and its users. You can also use system functions to perform a number of language-related tasks.
This article will introduce you to many of the system functions included in SQL Server. Although I won’t cover every function, I’ll describe as many as possible and look at several different types in order to give you a broad foundation. For a complete list of system functions and details about each one see the topic “System Functions (Transact-SQL)” in SQL Server Books Online.
Note:There are examples in this article that I ran within the context of the AdventureWorks2008 sample database installed in a local instance of SQL Server 2008. You can also run the examples with the AdventureWorks database in SQL Server 2005, but be sure to change the database name as necessary. Also, if you run the examples on SQL Server 2005, some of your results will be slightly different from what is shown here.
Viewing information about data modifications
Some of the system functions supported by SQL Server let you return details about your update and insert operations. For example, you can retrieve the last identity value inserted into a table, or the number of rows updated or added to a table. To demonstrate how these functions work I defined the following table and populated it with several rows:
USE AdventureWorks2008;
IF OBJECT_ID('TableA', 'U') IS NOT NULL
DROP TABLE dbo.TableA;
CREATE TABLE dbo.TableA
(
ColA INT IDENTITY(101, 100) NOT NULL,
ColB VARCHAR(20) NOT NULL
);
INSERT INTO TableA (ColB) VALUES('red');
INSERT INTO TableA (ColB) VALUES('blue');
INSERT INTO TableA (ColB) VALUES('green');
INSERT INTO TableA (ColB) VALUES('yellow');
INSERT INTO TableA (ColB) VALUES('orange');
SELECT * FROM TableA;
Below is a very simple table that’s been populated with five rows. Notice that the ColA column is defined as an identity column with a seed value of 101 and an increment value of 100. The final SELECT statement produces the following results:
| ColA | ColB |
| 101 | red |
| 201 | blue |
| 301 | green |
| 401 | yellow |
| 501 | orange |
Now let’s look at an example with several system functions that retrieve information about the data just inserted into the table. The following SELECT statement includes five functions, three related to the identity value and two related to row counts:
SELECT
IDENT_CURRENT('TableA') AS LastValue,
IDENT_SEED('TableA') AS SeedValue,
IDENT_INCR('TableA') AS IncrValue,
@@ROWCOUNT AS RowsAffected,
ROWCOUNT_BIG() AS BigRowsAffected;
The first function, IDENT_CURRENT, retrieves the last identity value inserted into TableA. As you can see, TableA is specified as an argument to the function. The function returns the information without regard to the session it was added to the table or the scope of the statement in which it was added. This is important because SQL Server supports other identity-related functions that are specific to the current session (@@IDENTITY) and the current scope and session (SCOPE_IDENTITY).
The next two functions in the example above return the setting information for the identity column of the specified table. The IDENT_SEED function returns the seed value and the IDENT_INCR function returns the increments value. The following result set includes the values returned by the three identity-related functions:
| LastValue | SeedValue | IncrValue | RowsAffected | BigBigRowsAffected |
| 501 | 101 | 100 | 5 | 5 |
As you would expect, the last identity value inserted into TableA is 501, the seed value is 101 and the increment value is 100.
Now let’s look at the next two functions in the example. The @@ROWCOUNT function returns the number of rows affected by the last statement. The ROWCOUNT_BIG function does the same thing, except the returned type is bigint. You should use this function if you anticipate that the number of affected rows will be greater than 2 billion.
Notice that neither function is associated with a table because the two functions return data based on the last statement executed. That means if 200 rows have been inserted into a table, a value of 200 will be returned by the functions. If a SELECT statement returns 30 rows, the functions will return a value of 30. In the example above, both functions return a value of 5 because the last statement to run was a SELECT statement that returned five rows.
Viewing server properties
At times, you might want to retrieve the value of a particular server property in which case you can use the SERVERPROPERTY function. The function takes one argument – the name of the property – as shown in the following example:
SELECT
SERVERPROPERTY('Edition') AS SrvEdition,
SERVERPROPERTY('InstanceName') AS SrvInstance,
SERVERPROPERTY('Collation') AS SrvCollation;
In this SELECT statement, I retrieved the settings for the Edition, InstanceName and Collation properties, which are passed in as arguments to the functions. The SELECT statement returns the following results:
| SrvEdition | SrvInstance | SrvCollation |
| Developer Edition | SQLSRV2008 | SQL_Latin1_General_CP1_CI_AS |
Your results might be different, particularly for the first two properties, but you get the point. The function lets you simply retrieve these settings.
Note:When working with a function where you must pass a specific property as an argument, refer to that function’s topic in SQL Server Books Online for a list of properties.
Another type of property setting you can retrieve is associated with collation properties. The function you use in this case is COLLATIONPROPERTY, as shown in the following example:
SELECT
COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'Version')
AS CollVersion,
COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage')
AS CollPage;
As you can see, the COLLATIONPROPERTY function takes two arguments -- the name of the collation and the specific property you want to view. In this case, the name of the collation is SQL_Latin1_General_CP1_CI_AS and the properties are Version and CodePage. The settings for both of these properties are shown in the following results:
| CollVersion | CollPage |
| 0 | 1252 |
Notice that the Version value is 0 and the CodePage value is 1252. If I were to plug in a different collation, my results would be specific to that collation.
Viewing user information
SQL Server supports several functions that are specific to the database user and that user’s identification numbers. The following example uses several of these functions to retrieve user information:
SELECT
USER_NAME() AS UserName,
USER_ID() AS UserId,
SUSER_SID() AS UserSid,
SYSTEM_USER AS SystemUser;
The functions are very straightforward; the USER_NAME function returns the name of the current database user and the USER_ID function returns the database ID for the current user. Additionally, the SUSER_SID function returns the security database ID for the current user, while the SYSTEM_USER function returns the current login. The following results show the data returned by the SELECT statement:
| UserName | UserId | UserSid | SystemUser |
| dbo | 1 | 0x010500000000000515000000FA4F0 C2FDBEB0C5007E53B2BF4010000 | WINSRV/Administrator |
The results themselves are self-explanatory, but note that you can pass in arguments to each of the first three functions in the example. For instance, you can pass in the database user ID to the USER_NAME function to return that user name. You can also pass in the user name as an argument to the USER_ID function to get that user’s ID. Similarly, you can pass a user’s login into the SUSER_SID function to retrieve that user name.
The following example demonstrates how to use these functions in this way:
SELECT
USER_NAME(1) AS UserName,
USER_ID('dbo') AS UserId,
SUSER_SID('WINSRV/Administrator') AS UserSid;
The statement returns the following results:
| UserName | UserId | UserSid |
| dbo | 1 | 0x010500000000000515000000FA4F0C2 FDBEB0C5007E53B2BF4010000 |
As you can see, the functions are versatile enough to work in different situations, depending on your needs.
Continue to part two – Digging deeper with system functions and language tools
Digging deeper with T-SQL system functions and language tools
Part one of this series examined how to use system functions to retrieve information about data modifications, server properties and users. This article examines other system functions uses, including how to view session information and use SQL Server language tools to enhance your T-SQL statements.
Viewing session information
In addition to viewing user information with system functions, you can also view information about the current session (which can include the session user). In the following example I use several session-specific functions to retrieve data:
SELECT
SESSION_USER AS SessionUser,
APP_NAME() AS AppName,
SESSIONPROPERTY('ANSI_NULLS') AS AnsiNulls,
SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QuotedIDs;
The SESSION_USER function returns the current user of the current database within the scope of the current session, while the APP_NAME function returns the name of the application that initiated the current session. The SESSIONPROPERTY function returns the specified SET option value for the current session.
In this case, the SELECT statement includes two SESSIONPROPERTY functions. The first retrieves the value for the ANSI_NULLS setting and the second retrieves the value for the QUOTED_IDENTIFIER setting. The following results show the information returned by the SELECT statement:
| SessionUser | AppName | AnsiNulls | QuotedIDs |
| dbo | Microsoft SQL Server Management Studio - Query | 1 | 1 |
Notice that the current user is dbo and the initiating application is SQL Server Management Studio. For the SET options, a value of 1 indicates that the setting is turned on. If it is turned off the SESSIONPROPERTY function returns a value of 0.
Viewing general information
SQL Server supports a number of system functions that let you retrieve general information about the system. The following SELECT statement demonstrates several of these functions:
SELECT
CURRENT_TIMESTAMP AS CurrentTimestamp,
GETANSINULL() AS DefaulNullability,
HOST_NAME() AS HostName,
HOST_ID() AS HostId;
As you would expect, the CURRENT_TIMESTAMP function returns a timestamp that is derived from the operating system and GETANSINULL returns the default nullability for the current database in the current session. Likewise, the HOST_NAME function returns the workstation name and the HOST_ID function returns the process ID (PID) of the application on the client computer that is connecting to SQL Server.
The SELECT statement returns the following results:
| CurrentTimestamp | DefaultNullability | HostName | HostId |
| 2010-08-04 17:33:45.590 | 1 | WINSRV | 3896 |
Some system functions can return multiple results. For example, the fn_helpcollations function returns a list of collations supported by an instance of SQL Server, as shown in the following example:
SELECT name AS CollName
FROM fn_helpcollations()
WHERE name LIKE 'greek_ci_ai%';
Notice that I treat the function as I would a table. I return the name column and limit the data returned to certain Greek collations, as shown in the follow results:
| CollName |
| Greek_CI_AI |
| Greek_CI_AI_WS |
| Greek_CI_AI_KS |
| Greek_CI_AI_KS_WS |
As you can see, only four collations fit the search condition as it’s specified in the WHERE clause. Notice, however, how simple it is to retrieve this information by using the fn_helpcollations function.
Using language tools
To wrap up, let’s look at the functions you can use as tools in your T-SQL statements. These types of functions often help to generate specific kinds of data. For example, the following SELECT statement uses the T-SQL NEWID function to create a unique value called uniqueidentifier:
SELECT NEWID() AS NewGuid;
As you can see from the following results, the statement returns a new value displayed as the uniqueidentifier:
| NewGuid |
| 91C81A03-8B5F-4133-BADD-901F5528C8CC |
Another function you can use is PARSENAME, which lets you parse the components of a fully qualified object name. The function takes two arguments -- the fully qualified object name and the name component that you want to identify. For example, in the following SELECT statement I use the PARSENAME function to parse the individual components of the Address table name:
SELECT
PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 4)
AS ServerName,
PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 3)
AS DbName,
PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 2)
AS SchemaName,
PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 1)
AS ObjectName;
As the following results show, the function parses each component of the table’s fully qualified name:
| ServerName | DbName | SchemaName | ObjectName |
| WinSrv | AdventureWorks2008 | Person | Address |
In the next example, I use the ISDATE and ISNUMERIC functions to verify the data type of specific values:
SELECT
ISDATE('2010-08-04 17:33:45.590') AS IsDatetime,
ISNUMERIC('12ec3') AS IsNumber;
The ISDATE function verifies whether the value passed in as an argument is a legitimate datetime value, and the ISNUMERIC function verifies whether the value passed in as an argument is a legitimate numeric value. If the value is the correct type, the function returns a value of 1, but if the value is not the right type the function returns a value of 0. For the example above, the statement returns the following results:
| IsDatetime | IsNumber |
| 1 | 0 |
As you can see, the first value is a legitimate datetime value, but the second value is not a legitimate numeric value.
Another useful function is the ISNULL function, which returns a specified value if the source value is null. For example, in the following SELECT statement I use ISNULL to return a value of 0 if the SalesQuota value is null:
SELECT
FirstName + ' ' + LastName AS FullName,
ISNULL(SalesQuota, 0) AS SalesQuota
FROM
Sales.vSalesPerson
WHERE
CountryRegionName = 'United States';
In the source data, three rows have null SalesQuota values – Stephen Jiang, Amy Alberts and Syed Abbas. For each of those rows the ISNULL function returns a SalesQuota value of 0, as shown in the following results:
| FullName | SalesQuota |
| Stephen Jiang | 0.00 |
| Michael Blythe | 300000.00 |
| Linda Mitchell | 250000.00 |
| Jillian Carson | 250000.00 |
| Pamela Ansman-Wolfe | 250000.00 |
| David Campbell | 250000.00 |
| Tete Mensa-Annan | 300000.00 |
| Amy Alberts | 0.00 |
| Shu Ito | 250000.00 |
| Syed Abbas | 0.00 |
| Tsvi Reiter | 300000.00 |
As the examples above and my previous article demonstrate, system functions are very useful when working with SQL Server data. I have not covered all the functions available to each of these categories, however. System functions also include error-handling functions that are useful when you include TRY…CATCH blocks in your T-SQL code. Again, refer to SQL Server Books Online for a complete list of functions and a description of those functions.
扫一扫
3024
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
