This function is used to determine the current nest level or number of triggers that fired the current one. This could be used to prevent triggers from firing when fired by others. Here’s an example that does that; we have two tables with triggers, that fire AFTER INSERT, and insert into the other table. The [...]

The CONNECTIONPROPERTY function can be used to obtain information about the current connection. The information available is similar to the sys.dm_exec_connections system view. ?View Code TSQLSELECT ‘Net Transport’ AS Property, CONNECTIONPROPERTY(’net_transport’) AS Value UNION ALL SELECT ‘Protocol type’, CONNECTIONPROPERTY(’protocol_type’) UNION ALL SELECT ‘Auth scheme’, CONNECTIONPROPERTY(’auth_scheme’) UNION ALL SELECT ‘Local net address’, CONNECTIONPROPERTY(’local_net_address’) UNION ALL SELECT [...]

SQL Server has a bunch of encryption functionality at its disposal. The EncryptByPassphrase allows us to quickly encrypt data using a password. This function uses the Triple DES algorithm to protect data from prying eyes. To encrypt a section of text we supply a password and the text to the function; ?View Code TSQLSELECT ENCRYPTBYPASSPHRASE(’secret’, [...]

The PWDCOMPARE function is really handy for further securing your SQL Servers by checking for a range of blank or common passwords. If you google for common password list you’ll probably recognise several if you’ve been working in IT for any reasonable amount of time. Fortunately you can use this function, in conjunction with the [...]

TSQL has a bunch of statistical system functions that can be used to return information about the system. This includes details about the number of connection attempts, cpu time, and total reads and writes and more. Many of these functions will be useful for performance monitoring. All of these functions return values that indicate cumulative [...]

The NTILE is used to assigned records into the desired number of groups. NTILE assigns a number to each record indicating the group it belongs to. The number of records in each group will be the same if possible, otherwise some groups will have less than the others. This function may be useful for assigning [...]

The GROUPING_ID function computes the level of grouping in a resultset. It can be used in the SELECT, HAVING or ORDER BY clauses when used along with GROUP BY. The expression used in this function must match what has been used in the GROUP BY clause. This function can be usefully deployed to order a [...]

You can use the GROUPING function to indicate if a column in a resultset has been aggregated or not. A value of 1 will be returned if the result is aggregated, otherwise 0 is returned. It is best used to identify the additional rows returned when a query uses the ROLLUP, CUBE or GROUPING_SETS clauses. [...]

The APP_NAME function returns the name of the application for the current database connection if the application has set it. Run the following in SSMS; ?View Code TSQLSELECT APP_NAME(); This will return; Microsoft SQL Server Management Studio – Query There’s no method of setting this value within TSQL. To set this value you need to [...]

We’re currently busy reviewing some of the historical database design decisions taken in our organisation. We’ve noticed quite a lot of columns, that are specified as nullable, but do not actually contain any nulls. Obviously this fact makes the column a possible candidate for changing to NOT NULL. I wanted to make this task a [...]