SQL Introduction Continued

Following on from my previous article on an introduction SQL this post covers some scripts I had to write to alter a table structure.

Being able to script these changes obviously makes it easier to move changes between development, Q/A and production.

As I was in a development environment I was able to clear the table prior to performing the column removal. All this is possible through the SQL Management studio, but using T-SQL is a powerful skill to have in a developers toolkit.
Let’s say we have a table with relationships to other tables and we want to remove column_5 from the table. In this case column_5 is part of the primary key.

sql-relationships

Use these scripts to create the tables needed.

CREATE TABLE [dbo].[table3]( [column_3] [int] NOT NULL, CONSTRAINT [PK_table3] PRIMARY KEY CLUSTERED ( [column_3] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table2]( [column_2] [int] NOT NULL, CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ( [column_2] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON 
GO
CREATE TABLE [dbo].[table1]( [column_1] [int] NOT NULL, [column_2] [int] NOT NULL, [column_3] [int] NOT NULL, [column_4] [int] NOT NULL, [column_5] [int] NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ( [column_1] ASC, [column_2] ASC, [column_3] ASC, [column_4] ASC, [column_5] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO

 

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table2] FOREIGN KEY([column_2])REFERENCES [dbo].[table2] ([column_2])

GO
ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table2]

GO


ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table3] FOREIGN KEY([column_3])REFERENCES [dbo].[table3] ([column_3])

GO


ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table3]

GO

 

Clear the data from the table

DELETE FROM dbo.table1

Let’s try and drop the column straight away.

ALTER TABLE dbo.table1 DROP COLUMN column_5

The object ‘PK_table1’ is dependent on column ‘column_5’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN column_5 failed because one or more objects access this column.

This is because column_5 is currently part of the primary key.

Note: If column_5 was part of a foreign key then you’d need to remove that relationship by using the following statement. In our case we can move straight onto removing the primary key which column_5 is part of.

Remove foreign key constraints (if any exist)

ALTER TABLE dbo.table1 DROP CONSTRAINT FK_table1_table2;
ALTER TABLE dbo.table1 DROP CONSTRAINT FK_table1_table3;

Remove primary key constraint

ALTER TABLE dbo.table1 DROP CONSTRAINT PK_table1;

Drop column_5 from the table

ALTER TABLE dbo.table1 DROP COLUMN column_5

Re-create the foreign key constraints

Now we can add back in our foreign key constraints

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table2] FOREIGN KEY([column_2])REFERENCES [dbo].[table2] ([column_2])

GO
ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table2]

GO
ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table3] FOREIGN KEY([column_3])REFERENCES [dbo].[table3] ([column_3])

GO
ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table3]

GO

 

Now let’s add the primary key back onto the table.


If you refresh the table in SQL Management studio you will notice that column_5 has been removed, the foreign key constraints have been applied again and the primary key is in place (excluding column_5).

This is again simple example using T-SQL, but it shows the control and power being able to write these provides.

SQL Introduction

Here is an introduction to writing SQL statements rather than using the user interfaces to build objects for you, insert, update, delete and query data within them.
It’s targeted at SQL Server, but with a little modification will work for My SQL.
Creating a database
First let’s create a database. Open SQL Server Management Studio and click ‘New Query’ and paste the following statement into the query windows and click ‘Execute’.
CREATE DATABASE tracks;
Then let’s switch to that database so we can work in it.
Use tracks
Command(s) completed successfully is always a good sign.
Creating tables
CREATE TABLE dbo.tracks (
track_id int IDENTITY(1,1) PRIMARY KEY,
track_name varchar(255) NULL,
track_release_date DateTime NULL,
label varchar(255) NULL
);
Inserting Data
Add some data to the newly created table. Notice on the insert we don’t pass in track_id, as this was setup as a IDENTITY (identity seed) that will be automatically generated for us. Also I’m using GetDate() here to fill in the release date with the current date and time.
INSERT INTO dbo.tracks
(track_name, track_release_date, label)
VALUES ('my song', GetDate() ,'my label')
(1 row(s) affected) so let’s add another track.
INSERT INTO dbo.tracks
(track_name, track_release_date, label)
VALUES ('your song', GetDate() ,'another label')
Selecting Data
In the simpliest form we can do the following query to extract data from the newly created tracks table.
SELECT * FROM dbo.tracks
We can be specific about the information we want to return.
SELECT * FROM dbo.tracks WHERE track_id = 1
We can return multiple rows
SELECT * FROM dbo.tracks WHERE track_id IN (1,2)
Ordering Data
SELECT * FROM dbo.tracks ORDER BY track_name
The following statement will order the track names in descending order.
SELECT * FROM dbo.tracks ORDER BY track_name DESC
Updating Data
Now we know the track_id on the newly created entries we can write some SQL to update the data in one of the columns.
UPDATE dbo.tracks SET track_name = 'my song updated' WHERE track_id = 1
If you run the following SELECT statement you’ll see the updated changes.
SELECT * FROM dbo.tracks
Let’s remove the data we’ve added to the tracks table and add another table to show us how to create a relationship between two tables.
The following statement will delete all tracks from the table.
DELETE FROM dbo.tracks
(2 row(s) affected)
Altering Tables
Lets run the following two statements to create a relationship between our tracks table and newly created table called artists. First we’ll add a artist_id field to our tracks field. This will be the column for us to associate a track with an artist.
ALTER TABLE dbo.tracks
ADD artist_id int NOT NULL;
Let’s create an artists table to hold our artists
CREATE TABLE dbo.artists (
artist_id  int IDENTITY(1,1) PRIMARY KEY,
artist_name varchar(255) NULL,
artist_web_site varchar(500) NULL
);
INSERT INTO dbo.artists 
(artist_name, artist_web_site)
VALUES ('an artist', 'http://www.somewebpage.com')
(1 row(s) affected)
We can now create a relationship between tracks and artists to ensure when we release an artist to a track the artist has to exist in the artists table.
ALTER TABLE dbo.tracks
ADD FOREIGN KEY (artist_id) REFERENCES artists(artist_id);

ALTER TABLE dbo.tracks
ADD CONSTRAINT FK_ArtistTrack
FOREIGN KEY (artist_id) REFERENCES artists(artist_id);
If we were to look at the a diagram of the database right now it would look like this.
track-artist-diagram
Now, let’s insert our track again into the tracks table.
INSERT INTO dbo.tracks
(track_name, track_release_date, label)
VALUES ('my song', GetDate() ,'my label')
Well that failed!
Cannot insert the value NULL into column ‘artist_id’, table ‘tracks.dbo.tracks’; column does not allow nulls. INSERT fails.
This is because we’ve said artist_id cannot be null so we must provide a value.
INSERT INTO dbo.tracks
(track_name, track_release_date, label, artist_id)
VALUES ('my song', GetDate() ,'my label', 1)
Run the following against and see the artist id has been set to 1
SELECT * FROM dbo.tracks
Try and run this we will just set the artist id to 1 again and this will be allowed.
UPDATE dbo.tracks SET artist_id = 1 WHERE track_id = 4
If we try and run this the insert will fail as artist_id 2 doesn’t exist.
UPDATE dbo.tracks SET artist_id = 2 WHERE track_id = 4
The UPDATE statement conflicted with the FOREIGN KEY constraint “FK__tracks__artist_i__398D8EEE”. The conflict occurred in database “tracks”, table “dbo.artists”, column ‘artist_id’.
Deleting Related Data
Let’s try and delete an artist now
DELETE FROM dbo.artists WHERE artist_id = 1
The DELETE statement conflicted with the REFERENCE constraint “FK__tracks__artist_i__25869641”. The conflict occurred in database “tracks”, table “dbo.tracks”, column ‘artist_id’.
This fails because artist_id 1 is in use against a track in the tracks table. So we need to clear out any tracks related to that artist.
DELETE FROM dbo.tracks WHERE artist_id = 1
and then execute the following for that to work.
DELETE FROM dbo.artists WHERE artist_id = 1
SQL to determine duplicate values
Let’s insert another couple of songs, but the same title as the original one we created.
Add the artist first
INSERT INTO dbo.artists 
(artist_name, artist_web_site)
VALUES ('an artist', 'http://www.somewebpage.com')
Then the tracks
INSERT INTO dbo.tracks
(track_name, track_release_date, label, artist_id)
VALUES ('my song', GetDate() ,'mylabel', 2)

INSERT INTO dbo.tracks
(track_name, track_release_date, label, artist_id)
VALUES ('my song', GetDate() ,'another label', 2)
Here is a useful snippet of sql that I use on a regular basis. Find duplicate values in a specific column in a table. Just replace <columnname> with the column name you are looking for the duplicate values.
SELECT     track_name, COUNT(*) AS 'Duplicate Count'
FROM         dbo.tracks 
GROUP BY track_name 
HAVING      (COUNT(*) > 1)
This will show me I have two tracks named ‘my song’ in the table.
Simply SQL Join
How about if we want to know what artist artist_id? Well because artist_id cannot be null, we can always assume artist_id is filled. We can do an inner join onto the artists table to see the artist name.
SELECT tracks.artist_id, track_name, artists.artist_name FROM dbo.tracks
INNER JOIN dbo.artists ON dbo.tracks.artist_id = dbo.artists.artist_id
Run this and to prove it is working we’ll update the artist on the 2nd track.
INSERT INTO dbo.artists 
(artist_name, artist_web_site)
VALUES ('another artist', 'http://www.someotherwebpage.com')
Let’s run this query to get the artist_id of the newly created artist.
SELECT * FROM dbo.artists
So we can update track 2 with artist_id 3. Let’s run an update statement
SELECT * FROM dbo.tracks
We can see the 2nd track has track_id 2. So if we execute…
UPDATE dbo.tracks SET artist_id = 3 WHERE track_id = 6

Running the INNER JOIN query again you can see we have two tracks from two different artists.

SELECT tracks.artist_id, track_name, artists.artist_name FROM dbo.tracks
INNER JOIN dbo.artists ON dbo.tracks.artist_id = dbo.artists.artist_id
Clean Up
Let’s clean up our test data and tables.
DELETE FROM dbo.tracks
DELETE FROM dbo.artists

DROP TABLE dbo.tracks
DROP TABLE dbo.artists

Calling a Powershell script with multiple parameters

If you are working with a PowerShell​ script that takes parameters and some of those parameters contain spaces you can call the script as follows.

powershell “& {&’C:\Users\<user folder>\Documents\BackupFiles.ps1′ ‘\\server\share\a folder with spaces’ ‘\\server\share\another folder with a space’}”

The first value passed after the -command is the ps1 script and then the parameters the script takes are passed, but they are enclosed in single quotes.

The initial “&{& and the closing } are the important points to note.

Log Parser – Querying IIS Logs

Recently ​I needed to get statistics about a web site running on the production server. I found the following Microsoft software easy to use and it gave me the results I needed really quickly.

Background

Log Parser 2.2 is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, IIS logs and Active Directory. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart. Most software is designed to accomplish a limited number of specific tasks. Log Parser is different… the number of ways it can be used is limited only by the needs and imagination of the user. The world is your database with Log Parser.

How To Use It

I copied the date range of logs I needed from production to my laptop in order to run the query against them as I was querying 1 years worth of data. You can run the UI against the live logs as it doesn’t lock the file, but I wanted to do this in isolation from the production server.

I was able to run the following query against the logs for 2017 to give me stats around how many people had accessed the application on a daily basis for 2017.

  • Open log parser
  • Point it at the log files from the relevant environment
  • Type your sql syntax and press execute

Example SQL syntax

select cs-uri-stem, cs-username , count(cs-uri-stem), TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalDate] from '[LogFilePath]' where cs-uri-stem = '/CMS/MainFrameset.aspx' group by cs-uri-stem, cs-username, TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) order by TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))), count(cs-uri-stem) desc

 

Download Log Parser 2.2 (command line)

http://www.microsoft.com/en-gb/download/details.aspx?id=24659

Download Log Parser Studio (UI)

https://gallery.technet.microsoft.com/office/Log-Parser-Studio-cd458765

50 Examples to get you going

https://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/

SQL Server 2016 Temporal Tables

A temporal table is a new type of user table in SQL Server 2016, it’s designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

– Auditing all data changes and performing data forensics when necessary
– Reconstructing state of the data as of any time in the past
– Calculating trends over time
– Recovering from accidental data changes and application errors

getting-started-with-temporal

This post is when you already have tables in place in SQL Server. See the Microsoft article at the bottom of the article that explains how to create them from Scratch. If your like me you’ve been using SQL Server through the versions.

Create the history schema

CREATE SCHEMA history; 
GO

Converting an existing table

ALTER TABLE dbo.components
ADD ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ValidFrom DEFAULT SYSUTCDATETIME(), 
 ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ValidTo DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'), 
 PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); 
GO
ALTER TABLE dbo.components
 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.components))

Querying the data

You now easily query the audit history of the components table by using FOR SYSTEM_TIME BETWEEN and then specify the dates.

SELECT * FROM dbo.components 
 FOR SYSTEM_TIME 
 BETWEEN '2017-05-19 00:00:00.0000000' AND '2017-05-19 00:00:00.0000000'

Converting back to a normal table

If you wish to remove system versioning apply the following:-

ALTER TABLE dbo.components SET (SYSTEM_VERSIONING = OFF)

Then turn off period definition – without turning this off you won’t be able to remove the ValidFrom and ValidTo columns/constraints.

ALTER TABLE dbo.components  DROP PERIOD FOR SYSTEM_TIME;

Then we can delete the DF_ValidFrom and DF_ValidTo constraints

ALTER TABLE dbo.components DROP CONSTRAINT DF_ValidFrom;
ALTER TABLE dbo.components DROP CONSTRAINT DF_ValidTo;

We can then remove the two ValidFrom and ValidTo columns

ALTER TABLE dbo.components DROP COLUMN ValidFrom
ALTER TABLE dbo.components DROP COLUMN ValidTo

You can then drop the history table from the history schema. Warning – this will remove all history – be careful!)

DROP TABLE history.components

Continue reading “SQL Server 2016 Temporal Tables”