Sladescross's Blog

Blogging about Sharepoint related stuff

Missing Primary Key and Other Useful Queries March 6, 2014

Filed under: Missing,Primary Key,Query,Row Count,SQL,Sys — sladescross @ 12:41 pm

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

Filed under: Azure,Incompatible,SQL,SQL Azure — sladescross @ 4:17 pm


Merge and Output Clause February 13, 2014

Filed under: Merge,Output,SQL — sladescross @ 8:00 pm


Change Data Capture Primer January 31, 2014

Filed under: Change Data Capture,Incremental,SQL — sladescross @ 3:50 pm


SQL Endpoint for Internet in Azure January 22, 2014

Filed under: Azure,Endpoint,SQL — sladescross @ 6:52 pm


sql server 2008 – DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) Can someone explain me this – Stack Overflow January 16, 2014

Filed under: Date,DATEDIFF,SQL,Zero — sladescross @ 9:26 pm

this will give you the first of the month for a given date

inner select  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

Filed under: NOT IN,Null,SQL,SubQuery,Three-Valued Logic,Unknown — sladescross @ 4:15 pm

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:
which evaluates to 

The second one:

    3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
which evaluates to:

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)

SubQuery and Derived Table and 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

Note that 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 t_right.

NOT IN, however, behaves differently.

IN predicate (unlike EXISTS) is trivalent, i. e. it can return TRUE, FALSE or NULL:

  • TRUE is returned when the non-NULL value in question is found in the list
  • FALSE is returned when the non-NULL value is not found in the list and the list does not contain NULL values
  • NULL is returned when the value is NULL, or the non-NULL value is not found in the list and the list contains at least one NULL value




SQL Rules Engine Implementation January 10, 2014

Filed under: Engine,Formulae,Rules,Rules Engine,SQL,Well Formed Formulae — sladescross @ 8:21 pm



Resource Governor May 6, 2013

Filed under: Resource Governor,Session,SQL — sladescross @ 3:52 pm

Each time SQL Server creates a new session, Resource Governor runs a classifier function to assign it to a workload group based upon characteristics defined by the administrator (e.g. name of the application creating the session). The sessions in that workload group are then granted access to a specific shared resource pool that limits the workload group’s use of system resources.



Get every new post delivered to your Inbox.

Join 63 other followers