Creative developer based in the UK

Find all SQL Stored Procedures with QUOTED_IDENTIFIER set to OFF

SELECT
    SCHEMA_NAME(s.schema_id)  + '.' + s.name AS name,
    s.create_date,
    s.modify_date,
    OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects s
WHERE
    s.type IN ('P','TR','V','IF','FN','TF')
    AND OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') = 0
ORDER BY SCHEMA_NAME(s.schema_id)  + '.' + s.name DESC

Taken from zvolkov.com.

Saturday May 19th

sql

Sql Server: Check if a column allows null values

This is not a check for existing data, this is to check if the actual properties of the column allows null values when inserting and updating. Could be used when upgrading tables and you want to check if you have applied the update already.

IF( SELECT COUNT(*)
      FROM    INFORMATION_SCHEMA.COLUMNS
      WHERE   TABLE_NAME = ‘TABLE’
              AND
              COLUMN_NAME = ‘nullableColumn’
              AND
              IS_NULLABLE = ‘YES’) > 0
BEGIN
      PRINT ‘TABLE.nullableColumn is nullable’
      — alter here.
END

Monday Apr 4th

sql

Sql: Get the current datetime at midnight

GETDATE() will return the current date and whatever time it’s called at.

There may be times when you need just the date and a time of midnight, this will do the trick nicely:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

Wednesday Dec 15th

sql

An Introduction to Clustered and Non-Clustered Index Data Structures

Knowledge Monday.

http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

Monday Oct 25th

SQL Server averages and rounding

Due to the nature of sql server and integer division, use this to get a more accurate average which rounds up to the nearest whole number.

ceiling(cast(sum(VoteValue) as float) / cast(count(VoteId) as float))

To get more precision we cast the result of the sum and count functions to perform a calculation with more precision.

In this case, vote value is the value held for each vote - in a five star rating system, this would be 1-5. VoteId is simply used for column count, in this case, the vote’s unique id.

Tuesday Aug 10th