|
|
-
|
|
NTILE() distributes the result set into specified number of ordered partitions.
For each row in result set NTILE() will returns a group number to which the
row is associated.
This is very useful while distributing the result set into multiple groups in case
you need to di......
|
|
-
|
|
The RANK() functions ranks each row of a result set. It can also be used
to partition the data for ranking. It takes two arguments, PARTITION BY clause
and ORDER BY clause. As the name suggests PARTITION BY clause is used
to partition the result set into chunks, and ORDER BY def......
|
|
-
|
|
SESSION_USER returns the current user name, SUSER_SNAME() returns the login name
of the current user. Both these functions are context sensitive. On the other hand
ORIGINAL_LOGIN() returns the 'Original' login which was used to connect to SQL Server.
For example, when a statement is......
|
|
-
|
|
The LOGINPROPERTY() SQL function can be used to retrieve policy settings related to logins. It takes two arguments, Login Name and Property Name as below:
LOGINPROPERTY('Login Name','Property Name')
Where, Login Name = SQL Login Name, and
Property Name can be:
1. BadPasswordCount – Number ......
|
|
-
|
|
sys.server_role_members contains the membership of each server principal
with server roles. It returns a pair of role_principal_id and member_principal_id
for each membership as below:
-- © 2011 – Vishal (http://SqlAndMe.com)
SELECT role_principal_id, member_principal......
|
|
-
|
|
While sys.server_principals contains all logins/roles created on the server. sys.database_principals contains all users/roles in the current database. Some of the columns returned by sys.database_principals are as below:
1. Name – name of the principal – user/role name,
2. Principal_id......
|
|
-
|
|
sys.identity_columns contains a list of all identity columns in the database, some of the columns returned by sys.identity_columns are as below:
1. Object_id – object id of the parent table,
2. Name – column name for the identity column,
3. Column_id – id of the columns in the t......
|
|
-
|
|
sys.computed_columns returns all columns that are computed in the database, below
are some of the columns returned by sys.computed_columns:
1. Object_id – object id of the table to which the column belongs,
2. Name – name of the computed column,
3. Column_......
|
|
-
|
|
While
sys.check_constraints returns all CHECK constraints in the database,
sys.key_constraints and sys.default_constraints returns primary/unique key constraints
and DEFAULT constraints respectively.
some of the columns returned by sys.key_constraints are:
1. ......
|
|
-
|
|
sys.sql_modules returns a row for each function, procedure, trigger, view and rules defined in the database. Below are some of the columns returned by sys.sql_modules:
1. Object_ID – ID of the object in the database,
2. Definition – module text,
3. Uses_ansi_null – 1 if module w......
|
|