TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
sys.tables t ON ind.object_id = t.object_id
Index Details April 22, 2014
Missing Primary Key and Other Useful Queries March 6, 2014
Dynamic SQL to be able to cut across from sys.databases in master.
Use sys views not the informationschema. For example, schemas. And object id is only available in sys views.
SQL Azure Incompatibilities February 26, 2014
Merge and Output Clause February 13, 2014
Change Data Capture Primer January 31, 2014
SQL Endpoint for Internet in Azure January 22, 2014
sql server 2008 – DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) Can someone explain me this – Stack Overflow January 16, 2014
this will give you the first of the month for a given date
select DATEDIFF(MONTH, 0, GETDATE()) will give the number of months from 1900-01-01
here it is 1350
this will be add to 1900-01-01 , but only the months
select DATEADD(MONTH,1350,0) will give 2012-07-01 00:00:00.000
which is the start of the current month.
I think this is the most efficient way to find the starting of a month for any given date.
SQL NOT IN and NULL January 13, 2014
Whenever you use NULL you are really dealing with a Three-Valued logic.
Your first query returns results as the WHERE clause evaluates to:
3 = 1 or 3 = 2 or 3 = 3 or 3 = null which is: FALSE or FALSE or TRUE or UNKNOWN which evaluates to TRUE
The second one:
3 <> 1 and 3 <> 2 and 3 <> null which evaluates to: TRUE and TRUE and UNKNOWN which evaluates to: UNKNOWN
The UNKNOWN is not the same as FALSE you can easily test it by calling:
select 'true' where 3 <> null select 'true' where not (3 <> null)
Interesting to show that to get NULL handled with LEFT JOIN for a particular Date partition then need a “snapshot” of derived table.
First of all,
LEFT JOIN / IS NULL and
NOT EXISTS are semantically equivalent, while
NOT IN is not. These method differ in how they
NULL values do not satisfy the equality conditions, so both
LEFT JOIN / IS NULL and
NOT EXISTS will always return rows from
t_left that have
value set to
NULL, even is there are rows with
value IS NULL in
NOT IN, however, behaves differently.
IN predicate (unlike
EXISTS) is trivalent, i. e. it can return
TRUEis returned when the non-
NULLvalue in question is found in the list
FALSEis returned when the non-
NULLvalue is not found in the list and the list does not contain
NULLis returned when the value is
NULL, or the non-
NULLvalue is not found in the list and the list contains at least one