The important part is the IDENTITY(x,x) and PRIMARY KEY. CREATE TABLE Persons ( ID int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); More information found here: https://www.w3schools.com/sql/sql_autoincrement.asp
Add Trigger
I have 2 tables: CREATE TABLE [dbo].[capalogin]( [ID] [INT] IDENTITY(1,1) NOT NULL, [Username] [VARCHAR](50) NULL, [PcName] [VARCHAR](50) NULL, [lastlogin] [DATETIME] NOT NULL, CONSTRAINT [PK_capalogin_history] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE […]
Get latest order on customer
http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column/123481#123481 I see many people use subqueries or else vendor-specific features to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it […]
Job template on Always On Cluster
Jobs on Always On cluster, the important part is the check if it is primary USE [msdb] GO /****** Object: Job [_MULTISERVER_JOB_TEMPLATE] Script Date: 14-03-2017 14:15:33 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = […]
Reseed
The command to reset the identity property is DBCC CHECKIDENT (tablename, RESEED, new_reseed_value) When you want to set the column identity to 12345 you run this DBCC CHECKIDENT (beer, RESEED, 12345) When you […]
Convert to Unix Timestamp
Converting a datetime to unix timestamp is easy, but involves error prone typing the following: @timestamp=DATEDIFF(second,{d ‘1970-01-01’},@datetime) Where @datetime is the datetime value you want to convert. The {d ‘yyyy-mm-dd’} notation is an ODBC escape […]
Convert from datetime to varchar
CONVERT(VARCHAR(12),DATEADD(S, getdate(), ‘1970-01-01’),105) Specification according to https://msdn.microsoft.com/library/ms187928
Convert from Unix timestamp
Select dateadd(S, [unixtime], ‘1970-01-01’) From [Table] Source http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server
