T-SQL Create table syntax

if exists (select * from sysobjects where id = object_id('dbo.tblMetricEvaluationMethod') and sysstat & 0xf = 3)

    drop table dbo.tblMetricEvaluationMethod

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE dbo.tblMetricEvaluationMethod (

     memID int IDENTITY (1, 1) NOT NULL ,

     memText varchar(200) ,

     memTaskID int ,

     memCreateDate datetime ,

     memCreatedBy int ,

     memModifiedDate datetime ,

     memModifiedByec_EventDescription varchar(1000)

CONSTRAINT PK_tblMetricEvaluationMethod PRIMARY KEY CLUSTERED ( memID )

CONSTRAINT FK_tblMetricEvaluationMethod FOREIGN KEY (memCreatedBy) REFERENCES tblUsers(UserID)

)

GO

SET ANSI_PADDING OFF
GO



===========================================================

Important to remember that ORDER BY works below when TOP is used.
--------------------------------------------------------------------

USE [database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].vw_name
as
SELECT TOP 10
[col1], [col2], ...
FROM dbo.[tblName]
GROUP BY [col1], [col2], ...
ORDER BY [col1], [col2], ...
GO

ALTER TABLE multiple columns

ALTER TABLE Countries
ADD  
HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit,
 HasText  bit;

Clustered Index syntax

USE [DWHCBI]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

GO

/*** Object: Index [ndx_unique] Script Date: 4/17/2014 9:40:23 AM ****/
CREATE UNIQUE CLUSTERED INDEX [ndx_unique] ON [dbo].[Transform_Rolling_12_Months]
(
[Month_ID] ASC,
[Rolling12Month] ASC
)WITH (PAD_INDEX = OFF

    , STATISTICS_NORECOMPUTE = OFF

    , SORT_IN_TEMPDB = OFF

    , IGNORE_DUP_KEY = OFF

    , DROP_EXISTING = OFF

    , ONLINE = OFF

    , ALLOW_ROW_LOCKS = ON

    , ALLOW_PAGE_LOCKS = ON

    , FILLFACTOR = 95) 
ON [Primary]
GO

T-SQL Error Handling

BEGIN Transaction

BEGIN TRY


PRINT 'STEP 1 COMPLETE'

PRINT 'STEP 2 COMPLETE'

PRINT 'STEP 3 COMPLETE'
--delete from vendorcompanyvendor where vendorcompanyid > @MaxVendorCompanyID
--truncate table vendorcompanyfyenddate

--select @MaxCompanyID = @MaxCompanyID + 1
--select @MaxVendorCompanyID = @MaxVendorCompanyID + 1
--select @MaxVendorCompanyVendorID = @MaxVendorCompanyVendorID + 1
--DBCC CHECKIDENT ('company', RESEED, @MaxCompanyID)
--DBCC CHECKIDENT ('vendorcompany', RESEED, @MaxVendorCompanyID)
--DBCC CHECKIDENT ('vendorcompanyvendor', RESEED, @MaxVendorCompanyVendorID)

-- Log this rollback so the Audit log makes sense afterwards
EXEC StatusUpdate @MaxDataPeriodID, 3, '[RollBackDataPeriod]', '...completed', null


END TRY

BEGIN CATCH
 SELECT
          ERROR_NUMBER() AS ErrorNumber
        , ERROR_SEVERITY() AS ErrorSeverity
        , ERROR_STATE() AS ErrorState
        , ERROR_PROCEDURE() AS ErrorProcedure
        , ERROR_LINE() AS ErrorLine
        , ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

List all Database Constraints

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

where Constraint_Name like '%Service%'
GO

ALTER Table Primary Key

If a primary key already doesn't already exists. then you want to do this

ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);

If a primary key already exists then you want to do this

drop primary key
ALTER TABLE Table1DROP CONSTRAINT PK_Table1_Col1GO


ALTER TABLE [dbo].[tbl_Excel_Tier1Tier2IDs]
   drop constraint [PK_tbl_Excel_Tier1Tier2IDs]
GO


 ALTER TABLE [dbo].[tbl_Excel_Tier1Tier2IDs]
   ADD PRIMARY KEY(Agent_Name, PwC_GUID, Effective_Date);


ALTER TABLE dbo.tblComment_Review
ADD FOREIGN KEY (Record_ID)
REFERENCES [dbo].[tblSurvey_Response_2](Record_ID)

RowVersion

Create Table Orders (

    OrderID int not null identity(1,1)

        Constraint PK_Orders Primary Key

    , ProductName varchar(50) not null

    , Quantity int not null

    , RowRevision rowversion

)

Insert Into Orders(ProductName, Quantity)

Values ('Widget-Dongles', 13)

Insert Into Orders(ProductName, Quantity)

Values ('test 2', 15)

select OrderID, ProductName, Quantity, RowRevision, cast(RowRevision as int)

from orders order by RowRevision desc

Find is_ansi_padded status of columns

SELECT b.name as 'schema_name', a.name as 'table_name', a.type_desc

       , c.name, c.column_id

       , d.name as [type_name], c.max_length, c.precision

       , c.scale, c.is_nullable

       , c.is_ansi_padded, is_identity

FROM sys.types d INNER JOIN (sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b 

    ON a.schema_id = b.schema_id)

              ON c.object_id = a.object_id)

              ON d.user_type_id = c.user_type_id

WHERE b.name like '%snapshot%'

       and is_ansi_padded = 0 

        and d.name NOT IN ('int', 'datetime', 'date', 'numeric', 'float', 'bigint', 'smallint')

ORDER BY a.[type], b.name, a.name, c.column_id

To fix

TrimTrailingBlanks "no" is the same as ANSI_PADDING ON.  So it looks like those columns were (correctly) created with SET ANSI_PADDING ON.

If you have only a few columns with TrimTrailingBlanks "yes", you can create a new column, migrate data and rename like the example below, keeping in mind that you will need do drop constraints and indexes on the column(s) and recreate afterward.  You might find it easier to recreate the table if you have many columns.

SET ANSI_PADDING ON;
GO
ALTER TABLE dbo.MyTable
    ADD MyColumn_New varchar(100);
GO
UPDATE dbo.MyTable
    SET MyColumn_New = MyColumn;
GO
ALTER TABLE dbo.MyTable
    DROP COLUMN MyColumn;
GO
EXEC sp_rename 'dbo.MyTable.MyColumn_New', 'MyColumn', 'COLUMN';
GO

T-SQL no leading zeroes

DECLARE @Sku NVARCHAR(500)

    SET @Sku = '000000000000000000000000000000000000000000000000000000012335670000'

 SELECT @Sku AS Original,

        SUBSTRING(@Sku,PATINDEX('%[^0]%',@Sku),DATALENGTH(@Sku)) AS NoLeadingZeros

Declare @MyStr char(20)

Set @MyStr = '00004567890'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

What this does is to find the first non-zero character in the field and then substring from there.  It also works if you have a dash imbeded in the field.

Declare @MyStr char(20)

Set @MyStr = '0000-45670'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

Make comma separated list from database table

select distinct se.guid, spt.respid
into #tempSUBLOS
from dbo.Static_Pivot_Table spt inner join dbo.tblSaratogaExtract se on spt.respid=se.respid
where spt.sublos is null


DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + se.[GUID]
from #tempSUBLOS se
SELECT @listStr

drop table #tempSUBLOS
GO

GRANT EXECUTE

exec [dbo].[GSPrimeExcel]

GRANT EXECUTE ON [dbo].[GSPrimeExcel] TO GSPrimeExcelUser;

------------------

USE DB1;
GRANT SELECT ON OBJECT:: View1 TO JohnDoe;
GO

DateTime into Date

, CONVERT(char(10), DateOfHire,126)


turns DateTime in to Date

Create List of Calendar Dates

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Holidays_Weekends](
CalendarDate datetime NOT NULL,
descr [varchar](20) NULL,
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


WITH sample AS (
 SELECT CAST('2015-04-01' AS DATETIME) AS dt
 UNION ALL
 SELECT DATEADD(dd, 1, dt)
FROM sample s
  WHERE DATEADD(dd, 1, dt) <= CAST('2015-06-30' AS DATETIME))

INSERT INTO [dbo].[Holidays_Weekends]
SELECT *, '' FROM sample

SQL function DataLength

SELECT CompanyName
, len(CompanyName) as 'length'
, datalength(CompanyName) as 'RealLength'
FROM TempfinanceCompanyList 

WHERE right(Companyname,1) = ' '
ORDER by CompanyName

You cannot count on LEN function to count trailing spaces, at least not in SQL 2012.

Compare two SQL Server tables

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.

UNION allows you to compare all columns very quickly, and also handles comparing NULL values to other NULLs successfully, which a join clause or a WHERE condition doesn't normally do.  It also allows you to very quickly see which rows are missing in either table, which only a FULL OUTER JOIN will do, but of course we all know to avoid those at all costs (right?) -- a full outer join is about as “unrelational” as you can get.  (every column returned is potentially Null and must be wrapped in a COALESCE function).  Best of all, the UNION is quick and easy and short.

The basic idea is: if we GROUP the union of two tables on all columns, then if the two tables are identical all groups will result in a COUNT(*) of 2.  But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want.  We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.

So, here's an example, assuming we are comparing tables A and B, and the primary key of both tables is ID:

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
  SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
  FROM A
  UNION ALL
  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
  FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

CASE statement in UPDATE query

UPDATE a
SET a.Date_Added = (CASE 
                    WHEN a.Date_Requested > b.ADmodifyTimeStamp THEN getdate() 
                    ELSE b.ADmodifyTimeStamp END)
FROM [dbo].[tableA] a INNER JOIN [dbo].[tableA] b 
     ON a.[col1] = b.[col1] AND a.[col2] = b.[col2]
WHERE a.[col3] IS NULL

Configure SQL Agent Operator role

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

Using SQL Server Management Studio

To add a SQL login or msdb role to a SQL Server Agent fixed database role

1. In Object Explorer, expand a server.
2. Expand Security, and then expand Logins.
3. Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties.
4. On the User Mapping page of the Login Properties dialog box, select the row containing msdb.
5. Under Database role membership for: msdb, check the appropriate SQL Server Agent fixed database role.

[Top]

To configure a proxy account to create and manage SQL Server Agent job steps

1. In Object Explorer, expand a server.
2. Expand SQL Server Agent.
3. Right-click Proxies and select New Proxy.
4. On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. Note that you must create a credential first before creating a SQL Server Agent proxy. For more information about creating a credential, see Create a Credential and CREATE CREDENTIAL (Transact-SQL).
5. Check the appropriate subsystems for this proxy.
6. On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.

MAX updated row per User record

use AssessmentApp

declare @LatestDateTime as datetime

SET @LatestDateTime = '09/12/2014 04:03:15.000'

-- this will capture EVERY record.

/*

SELECT ASSESSMENT_ID, AUDIT_DATE_TIME, AUDIT_BY_PPID, AUDIT_BY_NAME, NEW_ASSESSMENT_STATUS, ASSESSMENT_STATUS, USER_AGENT_TYPE, USER_AGENT_VERSION

FROM [dbo].[table]

WHERE AUDIT_DATE_TIME <= @LatestDateTime

ORDER BY AUDIT_DATE_TIME DESC

*/

-- this will capture the LATEST record for each Assessment_ID.

SELECT a.ASSESSMENT_ID, a.AUDIT_DATE_TIME, AUDIT_BY_PPID, AUDIT_BY_NAME, NEW_ASSESSMENT_STATUS, ASSESSMENT_STATUS, USER_AGENT_TYPE, USER_AGENT_VERSION

FROM [dbo].[table] a

WHERE AUDIT_DATE_TIME IN (

                          SELECT MAX(aa.AUDIT_DATE_TIME)

                          FROM [dbo].[ASSESSMENT_AUDIT] aa

                          WHERE aa.AUDIT_DATE_TIME <= @LatestDateTime

                             and aa.Assessment_ID = a.ASSESSMENT_ID                                                    

                            GROUP BY aa.ASSESSMENT_ID

                                         )

       AND AUDIT_DATE_TIME <= @LatestDateTime

--     AND assessment_id = 88205

ORDER BY a.Audit_Date_Time

ALTER SCHEMA Ownership

Syntax ::

USE [dbname]
GO
ALTER AUTHORIZATION ON SCHEMA::[schema_name] TO [login]

USE [sandbox]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [dbo]



SELECT s.name, u.name AS owner
   FROM sys.schemas s, sys.database_principals u
   WHERE s.principal_id = u.principal_id
and u.name like '%chao%';

All objects in a schema

SELECT b.name as 'schema_name', a.name, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
FROM sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
WHERE object_id IN (select object_ID from sys.columns where name like 'snapshot%')
ORDER BY a.[type], b.name, a.name


--1760985600
SELECT b.name as 'schema_name', a.name, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
FROM sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
WHERE object_id IN (select object_ID from sys.columns where name like 'snapshot%')
and b.name = 'snapshot'
and a.name = 'T2_Flatfile_snapshot_level'
ORDER BY a.[type], b.name, a.name

--823934257
SELECT b.name as 'schema_name', a.name, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
FROM sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
WHERE object_id IN (select object_ID from sys.columns where name like 'snapshot%')
and b.name = 'dbo'
and a.name = 'Flatfile_snapshot'
ORDER BY a.[type], b.name, a.name


SELECT name, column_id FROM sys.columns where object_ID = 109503719
order by 2
union all
SELECT object_id, name, column_id FROM sys.columns where object_ID = 823934257
order by 2

ORA-00907 Missing Paranthesis error

PROBLEM:  SSIS package fails  with ORA-00907 error even though the query can be successfully parsed in the data flow task.

POSSIBLE SOLUTION:  Remove all of the comments from the query.

----------------------------------------------------------------------------------------

Sound stupid, I know, but try it anyway.

Transaction Count

http://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm

Custom Sorting in Power Pivot (Tabular Models)

http://dhanumjay999.blogspot.com/2012/06/custom-sorting-slicers-in-power-pivot.html

Determine SQL Server Table Create Date

SELECT col.* from sys.objects obj 
inner join sys.columns col 
on obj.object_Id=col.object_Id 
and obj.Name=@tableName


SELECT * FROM sys.objects WHERE Name='tblHeadcount'

SELECT sys.objects.name, sys.schemas.name AS schema_name, create_date, modify_date
FROM    sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.name = 'tblHeadCount'



SELECT sys.objects.name, sys.schemas.name AS schema_name, create_date, modify_date
FROM    sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.name like '%sp_T2_Daily%'

ORDER BY create_date

Disable double-click on Pivot Table

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range

    '~~> This is your pivot table range. Change as applicable
    Set rng = Range("B43:E52")

    '~~> Check if the double click happend in the Pivot
    If Not Intersect(Target, rng) Is Nothing Then
        '~~> Cancel Double click
        Cancel = True
    End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range
    Dim wks As Worksheet
    Dim pt As PivotTable

    Set wks = Target.Worksheet
    For Each pt In wks.PivotTables()
        Set rng = Range(pt.TableRange1.Address)
        If Not Intersect(Target, rng) Is Nothing Then
            Cancel = True
        End If
    Next
End Sub