Sladescross's Blog

Blogging about Sharepoint related stuff

Index Details April 22, 2014

Filed under: DESC,Details,Index,Sort Order,SQL — sladescross @ 1:54 pm

http://www.toadworld.com/platforms/sql-server/w/wiki/9631.find-information-about-indexes.aspx

SELECT
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
ind.*,
ic.*,
col.*
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
t.name=’stgTest1′

 

Missing Primary Key and Other Useful Queries March 6, 2014

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

http://www.sqldbpros.com/2010/09/find-all-tables-without-a-primary-key/

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/

Dynamic SQL to be able to cut across from sys.databases in master.

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx

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

http://www.databasejournal.com/features/mssql/migrating-sql-server-database-to-windows-azure-resolving-incompatibility-issues.html

 

Merge and Output Clause February 13, 2014

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

http://www.made2mentor.com/2013/06/using-the-output-clause-with-t-sql-merge/

 

Change Data Capture Primer January 31, 2014

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

http://www.databasejournal.com/features/mssql/getting-starting-with-change-data-capture-cdc-in-sql-server-part-1.html

 

SQL Endpoint for Internet in Azure January 22, 2014

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

http://blog.nimbo.com/how-to-provision-sql-server-2012-in-windows-azure-iaas/

http://msdn.microsoft.com/en-us/library/windowsazure/dn133152.aspx

 

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

http://stackoverflow.com/questions/11665119/dateaddmonth-datediffmonth-0-getdate-0-can-someone-explain-me-this

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

http://stackoverflow.com/questions/129077/not-in-constraint-and-null-values

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)
 

SubQuery and Derived Table and NULL

http://www.sqlservercentral.com/articles/DerivedTables/61388/

Interesting to show that to get NULL handled with LEFT JOIN for a particular Date partition then need a “snapshot” of derived table.

http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

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

http://technet.microsoft.com/en-US/library/aa964135(v=sql.90).aspx

 

 

 
Follow

Get every new post delivered to your Inbox.

Join 63 other followers