Sql Query titbits

Generate Dates and hour between Date Range

 DECLARE 

  @start DateTime = getdate() -1, 

  @end   DateTime = getdate();

;

WITH Dates_CTE

     AS (SELECT @start AS Dates

         UNION ALL

         SELECT Dateadd(hh, 1, Dates)

         FROM   Dates_CTE

         WHERE  Dates < @end)

SELECT DAY(dates),DATEPART(hour,Dates)

FROM   Dates_CTE

OPTION (MAXRECURSION 0) 

Got better queries? .. Add in comment .. Thanks

SQL Hacks

1) make self admin sql express script. Just download and run the below script
https://gist.github.com/wadewegner/1677788


2)SSRS Deployment Script
http://www.sqlblogspot.com/2014/03/ssrs-deploymentcomplete-automation2012.html

Combine multiple results in a subquery into a single comma-separated value

1. Create the UDF:

CREATE FUNCTION CombineValues(
@F_ID INT --The foreign key from TableA which is used to fetch corresponding records
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SomeColumnList VARCHAR(8000);
SELECT @SomeColumnList = COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) FROM TableB CWHERE C.FK_ID = @FK_ID;
RETURN (
SELECT @SomeColumnList)
END

2. Use in subquery:

SELECT ID, Name, dbo.CombineValues(FK_ID) FROM TableA


ref :http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value

Split CSV String into Table in SQL Server

ref :http://www.saqib-ansari.com/2010/04/split-csv-string-into-table-in-sql-server.html

Split CSV numbers into Table in SQL Server

--- From string to table




CREATE FUNCTION [dbo].[ConvertCsvToNumbers]

(

  @String AS VARCHAR(8000)

)

RETURNS

  @Numbers TABLE (Number INT)

AS

BEGIN

  SELECT @String =

    LTRIM(

      RTRIM(

        REPLACE(

          ISNULL(@String, ''), '  ' /* tab */, ' ')))

  IF (LEN(@String) = 0)

    RETURN

  DECLARE @StartIdx       INT

  DECLARE @NextIdx        INT

  DECLARE @TokenLength    INT

  DECLARE @Token          VARCHAR(16)

  SELECT  @StartIdx       = 0

  SELECT  @NextIdx        = 1

  WHILE @NextIdx > 0

  BEGIN

    SELECT @NextIdx = CHARINDEX(',', @String, @StartIdx + 1)

    SELECT @TokenLength =

      CASE WHEN @NextIdx > 0 THEN @NextIdx

      ELSE LEN(@String) + 1

    END - @StartIdx - 1

    SELECT @Token =

      LTRIM(

        RTRIM(

          SUBSTRING(@String, @StartIdx + 1, @TokenLength)))

    IF LEN(@Token) > 0

      INSERT

        @Numbers(Number)

      VALUES

        (CAST(@Token AS INT))

    SELECT @StartIdx = @NextIdx

  END

  RETURN

END

----From Table values to string

declare @RoleIds varchar(max)
select @RoleIds = coalesce(@RoleIds + ',','') + convert(varchar, nRoleID)
       from    EmpRole
       WHERE  sEmployeeID =10

ref:http://alekdavis.blogspot.com/2009/04/convert-string-to-table-in-sql.html

Joining Three or More Tables

Example query :
SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;

What's the best practice for primary keys in tables?

1. Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats. This is because most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache you will use.
When dealing with "small" databases this stuff doesn't matter so much. But when you deal with large db's all of the little things matter. Just imagine if you have 1 billion rows with int or long pk's compared to using text or guid's. There's a huge difference!

Reference:
1 .http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables

Database Design Guidelines

References :

1.Best practices SQL Server naming conventions
http://www.sqlservercentral.com/articles/Naming+Standards/2895/

2.Best practices SQL Server naming conventions
http://vyaskn.tripod.com/object_naming.htm

3)10+ common questions about SQL Server data types
http://www.techrepublic.com/blog/10things/10-common-questions-about-sql-server-data-types/355

4)Composite Primary Keys

Date time vs small datetime in sql server 2005

DATETIME column must fall within the range of January 1, 1753, through December 31,
9999.

SMALLDATETIME column must fall within the range of January 1,
1900, through June 6, 2079.

Hence we can use Smalldatetime for most of our apps.

Ref:http://suryan72.blogspot.com/2008/08/date-time-vs-small-datetime-in-sql.html

TRY...CATCH + BEGIN TRANSACTION in SQL Server a good way for error handling

TRY...CATCH + BEGIN TRANSACTION in SQL Server a good way for error handling than just @@error since the later gives only if last statement made error unlike the former which tracks if error occurs in any statement in that block.

BEGIN TRANSACTION
BEGIN TRY
Try Statement 1
Try Statement 2
...
Try Statement M
END TRY
BEGIN CATCH
ROLLBACK
Catch Statement 1
Catch Statement 2
...
Catch Statement N
END CATCH

COMMIT

Ref: http://www.4guysfromrolla.com/webtech/041906-1.shtml

SQL SERVER – Query to Find Column From All Tables of Database

1. To get Simply the table names for a column name

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%your_column_name%' )

or else by schema and more detail

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%your_column_name%'
ORDER BY schema_name, table_name;

References:
1)http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/

2.Retrieve columnnames of all tables in a SQL Server database

a)In SQL-Server 2005+ you can do it using system views sys.columns and sys.tables

SELECT t.name TableName, c.name ColumnName
FROM sys.tables t
     JOIN sys.columns c ON t.object_id=c.object_id

b)Before SQL SERVER 2005 you can use below

SELECT   SysObjects.[Name] as TableName,   
    SysColumns.[Name] as ColumnName,   
    SysTypes.[Name] As DataType,   
    SysColumns.[Length] As Length   
FROM   
    SysObjects INNER JOIN SysColumns   
ON SysObjects.[Id] = SysColumns.[Id]   
    INNER JOIN SysTypes  
ON SysTypes.[xtype] = SysColumns.[xtype]  
WHERE  SysObjects.[type] = 'U'  
ORDER BY  SysObjects.[Name]