|
|
-
|
|
CHARINDEX()
can only be used to search a literal string in the specified expression. In other
words you cannot use wildcards. PATINDEX() provides this capability. It takes
two arguments, the pattern to be searched and the expression.
– © 2011 – Vishal (http://S......
|
|
-
|
|
CHARINDEX() returns the starting position of an expression in another expression.
It takes below arguments:
CHARINDEX ( exp1, exp2, position )
Where, exp1 = expression to be searched,
exp2 = the main expression which contains exp1,
position = this is optional, it spe......
|
|
-
|
|
ROW_NUMBER() can be used to generate a sequential number for each row in
the result set. Unlike
RANK() and
DENSE_RANK(), ROW_NUMBER() in case of ties it does not
generate same number, it simply ignores the tie and generates sequential numbers
for each of ......
|
|
-
|
|
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......
|
|