Generating a date table on the fly is nice to have for many reasons. The most recent of my adventures required determining when employees did not enter their time in their timesheets. I know I had a tattle tale job, the ironic part is that I ended up getting in trouble the next day for not inputting my hours. So, as for determining the dates when no one entered their time, the only way to determine this, was to cross join against a table of complete sequential dates. Yes it is an expensive query, however it’s the only way I think it can be done. In this exercise, I needed to create a date table containing all dates for the last month. I needed to do this in order to cross join against all the days the employee had logged in order to retrieve a summary of hours. If an employee did not enter any hours for a particular day, then the day itself would not be represented on the report in order to show zero hours. [cc lang=”sql”] — Create our date table using a — Common Table Expression (CTE) DECLARE @EndDate datetime DECLARE @StartDate datetime — 30 days ago SET @StartDate = GETDATE() – 30 — today SET @EndDate = GETDATE() ;WITH Dates(DATEPARAM) AS ( SELECT @StartDate AS datetime UNION ALL SELECT DATEADD(DAY, 1, DATEPARAM) FROM Dates WHERE DATEPARAM < @EndDate ) SELECT * FROM Dates OPTION (MAXRECURSION 10000) [/cc] Now we join this table on the actual […]
Continue reading ...
Adding a primary key can be done either after a table is created, or at the same a table is created. It is important to note, that by default a primary key is clustered. This may or may not be the preferred method of creation. For more information on clustered vs non clustered indexes, please see this article. Either way, you can specify the clustered / non clustered option when creating a primary key. To create a clustered primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID); [/cc] To create a non clustered primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonID); [/cc] To create a composite primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID, DOB); [/cc] For an existing table, there are two places that can be used to specify the primary key. The first is inline to the column. Using this method, you cannot create a composite primary key: [cc lang=”sql”] CREATE TABLE [dbo].[Person]( PersonID [int] IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED NOT NULL, FirstName VARCHAR(50) NULL ) [/cc] The second way is to specify the primary key after the creation of the table, this will enable you to create a composite primary key. Keep in mind that in order to use a column in the primary key, the field must not be nullable. [cc lang=”sql”] CREATE TABLE [dbo].[Person]( PersonID […]
Continue reading ...
The SQL Server Select statement is the first statement used when returning data. It is the most used and most important statement in the T-SQL language. The Select statement has many different clauses. We will step through each clause further in the tutorial, however now, we will look at Select itself. The following [cc lang=”sql”] SELECT ‘Mike’ AS FirstName [/cc] As we can see from our results, the code returns one row, one column. The data of the column returned is the value: ‘Mike’. The column’s name is ‘FirstName’. The ‘AS’ allows you to specify a column alias. This alias could be anything, in this case we made it ‘FirstName’. We can also Select multiple columns of data by separating the columns using a comma: [cc lang=”sql”] SELECT ‘Mike’ AS FirstName, ‘Nichols’ AS LastName [/cc] These examples so far, have only used the Select statement by itself. Now we will explore using Select in addition with the ‘From’ clause. The From Clause allows a source to be specified for the Select Statement. For the purposes of this example, the source is always a table. Let’s look at an example. [cc lang=”sql”] SELECT * FROM Person.Person [/cc] Phonetically the above query is pronounced: Select star from Person dot Person. The star (*) or asterisks is notation for saying “all columns”. Basically, the query is saying give me all columns from the Person table contained within the Person Schema. The person schema is basically a class, or a way to better organize […]
Continue reading ...
This error occurs when trying to insert into a column containing an identity. An Identity column is not able to be inserted into without the use of a special command mentioned below. Identity columns are columns that automatically increment when a value is inserted into a row. They are commonly used as primary keys because they guarantee uniqueness. In order to insert into a table containing an identity column SET IDENTITY INSERT ‘tablename’ ON IDENTITY INSERT ON can only be executed by a user having dbo privilidges The following example illustrates the error and shows how to successfully insert. [cc lang=”sql”] — Create MyNames Table with Identity Column CREATE TABLE dbo.MyNames ( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, FName varchar(50) NULL ) INSERT INTO dbo.MyNames ( FName ) SELECT ‘Abe’ UNION SELECT ‘Henry’ UNION SELECT ‘Phil’ — Create YourNames Table with Identity Column CREATE TABLE dbo.YourNames ( ID int IDENTITY(4,1) NOT NULL PRIMARY KEY, –Start Incrementing at 4 FName varchar(50) NULL ) INSERT INTO dbo.YourNames ( FName ) SELECT ‘Bill’ UNION SELECT ‘Candy’ UNION SELECT ‘Sara’ — Attempt to Insert MyNames Identity Into YourNames INSERT INTO dbo.YourNames ( ID, FName ) SELECT ID, Fname FROM dbo.MyNames — we get the error message: Cannot insert explicit value for identity column in table ‘YourNames’ when IDENTITY_INSERT is set to OFF. — Attempt to Insert after turning on IDENTITY_INSERT ON SET IDENTITY_INSERT dbo.YourNames ON INSERT INTO dbo.YourNames ( ID, FName ) SELECT ID, Fname FROM dbo.MyNames SET IDENTITY_INSERT dbo.YourNames OFF [/cc]
Continue reading ...
The kill command is used against a SPID. (Server Process ID). This command is typically used because something is misbehaving. In order to use the Kill command, simply type the word “kill” followed by a space and then the number of the Server Process ID to kill. [cc lang=”sql”] — Kill Server process ID 98 kill 98 [/cc] The kill command has three parameters. Two of which I have no idea what they mean: KILL { session ID | UOW } [ WITH STATUSONLY ]
Continue reading ...
