I needed to generate a range of data about dates for a lookup table. There’s an elegant solution using a recursive cte that does the job; ?View Code TSQLWITH daysCte ( d ) AS ( SELECT CONVERT(DATETIME, ’1 January 2011′) AS d — starting date UNION ALL SELECT DATEADD(D, 1, d) FROM daysCte WHERE DATEPART(yyyy, d) [...]

I’ve been reading a bit about VLFs (Virtual Log Files) this week. I’ve found quite a few interesting links, especially this one, informing us that there’s such a thing as too few or too many VLFs. We can view details about VLFs using the DBCC LOGINFO TSQL command. This only works against the current database [...]

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. [...]