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