I must admit most (if not all) my SQL Server interaction takes place in SQL Server Management Studio, but I wanted to create a new database project using the Visual Studio database tools, so I thought I’d give this a go…

Getting Started

I always like to start such posts off with a set of steps for getting the basics up and running, so let’s continue with that way of doing things.

• Create a new SQL Server | SQL Server Database Project (mine’s called cddb)
• Select the project in solution explorer and if need be, open the project properties/settings and set the Target platform etc.
• Right mouse click on the project and select Add | Table…
• Name the table artist
• Repeat the last two steps but name the table album

So at this point we have a database project and two tables/sql scripts with nothing much in them.

We’re going to create some very basic tables, as this post isn’t mean’t to be too focused on data but more using these tools.

So for artist.sql we should have

CREATE TABLE [dbo].[artist]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
[Name] NVARCHAR(50) NOT NULL
)

and for album.sql we should have

CREATE TABLE [dbo].[album]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
[Title] NVARCHAR(50) NOT NULL, 
[ArtistId] INT NOT NULL, 
CONSTRAINT [FK_album_Toartist] 
  FOREIGN KEY (ArtistId) 
  REFERENCES [artist]([Id])
)

Deploy/Publish your database

At this point, let’s actually publish our database to an instance of SQL Server or SQL Server Express.

Right mouse click on the project and select Publish, you should have the Database name supplied as cddb and the script as cddb.sql. Click the Edit button and enter the connect details for the instance of SQL Server. Finally click on the generate script button if you wish to create DB script and then run this yourself or click the Publish button to automatically publish your tables to the SQL Sever instance.

In the Data Tools Operations view you’ll see the process of publishing and creating the database scripts. Once successfully completed you should now have the cddb database running in SQL Server.

Let’s add some data

In a continuous integration and/or continuous deployment scenario, it’s useful to recreate our database from scripts, so generating the script instead of publishing to the database obviously helps in this, but it’s also useful to generate some data. Ofcourse it could be we’re populating the data from another instance of the DB but for this example we’re going to add some data via an SQL script.

Right mouse click on the database project and select Add | Script… We’re going to create a post-deployment script. As the name suggests this should be run after the DB is generated. I’ve named my script populate.sql, you’ll notice in the Visual Studio properties window the Advanced | Build Action will show PostDeploy.

We’re going to use the T-SQL Merge statement to create our test data, this script is as follows

SET IDENTITY_INSERT artist ON
GO

MERGE artist AS target
USING (VALUES
   (1, N'Alice Cooper'),
   (2, N'Van Halen'),
   (3, N'Deep Purple')
)
AS source (Id, Name)
ON target.Id = source.Id
WHEN MATCHED THEN
   UPDATE SET Name = source.Name
WHEN NOT MATCHED BY target THEN
   INSERT (Id, Name)
   VALUES (Id, Name)
WHEN NOT MATCHED BY source THEN
   DELETE;
GO

SET IDENTITY_INSERT artist OFF
GO

SET IDENTITY_INSERT album ON
GO

MERGE album AS target
USING (VALUES
   (1, N'Lace and Whiskey', 1),
   (2, N'I', 1),
   (3, N'III', 1),
   (4, N'Burn', 2)
)
AS source (Id, Title, ArtistId)
ON target.Id = source.Id
WHEN MATCHED THEN
   UPDATE SET Title = source.Title, ArtistId = source.ArtistId
WHEN NOT MATCHED BY target THEN
   INSERT (Id, Title, ArtistId)
   VALUES (Id, Title, ArtistId)
WHEN NOT MATCHED BY source THEN
   DELETE;
GO

SET IDENTITY_INSERT album OFF
GO

Ofcourse the above would be somewhat unwieldy if we’re populating hundreds of hundreds or MB of data entries.

Populating data from CSV

One possible solution for populating a larger number of records might be to use one or more CSV files to contain our seed data. So let’s assume we have the following files

artists.csv

1,Alice Cooper
2,Van Halen
3,Deep Purple

and albums.csv

1,Lace and Whiskey,1
2,I,1
3,III,1
4,Burn,2

we could now replace our post deployment code with the following

BULK INSERT artist
   FROM 'artists.csv'
   WITH
   (
   FIRSTROW=1,
   FIELDTERMINATOR=',',
   ROWTERMINATOR='\n',
   TABLOCK
   )

GO

BULK INSERT album
   FROM 'albums.csv'
   WITH
   (
   FIRSTROW=1,
   FIELDTERMINATOR=',',
   ROWTERMINATOR='\n',
   TABLOCK
   )

GO

Importing data using SQL Server Management Studio

Whilst this doesn’t fit in with the context of this post, i.e. it’s not automated. You could ofcourse create the database and use SQL Server Management Studio’s Import task to import data into your database.

Simply select the database you want to import data into, right mouse click on this and select Tasks | Import Data and work through the wizard to import your data from a variety of sources.

At some point we may need to look at adding indexes to our tables. Indexes can help with the performance when retrieving rows from our database.

An index allows us to help the SQL query engine to better search for commonly searched for data by giving it “hints” about the searchable columns. An index helps the query engine so that it doesn’t have to search all rows in a database by default but instead can use a more efficient looking up on indexed columns. So for example if we have an Employee database we might have a primary key on an employee number (which will usually be indexed) and we might add an index to the surname column, in the knowledge that this is often uses in queries.

Sounds great, right, but there is a downside to creating indexes and it is that they can have a detrimental affect on performance when inserting data. Because indexes need updating when new rows are added to the database. Hence when we add a new item of data the database engine may need to regenerate indexes each time.

An index is usually represented as a B-Tree structure (a balanced tree), see B-tree for examples of implementations.

Before we get started

The following examples work on SQL Server. In some cases they’ll work exactly “as is” on other databases, in some you may need to tweak things. I’ve simply not tried them out on anything other than SQL Server at this time.

Creating an index

As a naming convention, we will prefix our index name with IX_, so for example

CREATE INDEX IX_TITLE
ON FlashCard (Title)

this creates an index, named IX_TITLE on the table “FlashCard” and the column “Title”. By default we’ll have created a NOCCLUSTERED index. If want to created a CLUSTERED index we need to change the SQL to

CREATE CLUSTERED INDEX IX_TITLE
ON FlashCard (Title)

See CREATE INDEX (Transact-SQL) for the full syntax for creating an index using SQL Server.

According to the post Maximum Capacity Specifications for SQL Server you can create 999 nonclusted indexes per table. However you can only create one clustered index.

If we can create an index we must be able to drop one, so here goes

DROP INDEX IX_TITLE 
ON FlashCard

Viewing indexes

Using a UI tool such as SQL Server Management Studio we simply open the database in the Object Explorer and look at the Indexes section. This lists all the indexes on the table. In SQL we query the sys.indexes table, so for example we can use the following against SQL Server

select * from sys.indexes

or to view our recently added index

select * from sys.indexes where name = 'IX_TITLE'

CLUSTERED, NONCLUSTERED and UNIQUE indexes

In their simplest form, a NONCLUTERED index is an index which may have duplicates whereas (probably obvious) a UNIQUE index may not. UNIQUE indexes have a unique constraint on them whereas a CLUSTERED index is again unique but has the addition that data is ordered on disk to match the index.

By default your primary key will be created as a clustered index. For example this is the result of getting SQL Server Management Studio to create a CREATE script (abridged) for a simple FlashCard database

CREATE TABLE [dbo].[FlashCard](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nchar](20) NOT NULL,
	[Front] [ntext] NULL,
	[Back] [ntext] NULL,
 CONSTRAINT [PK_Card] PRIMARY KEY CLUSTERED 

And ofcourse, as only one clustered index may exist per table, if you attempt to create a clustered index on a table with a primary key set as clustered (in it’s default state) you’ll get the error

Cannot create more than one clustered index on table ‘FlashCard’. Drop the existing clustered index ‘PK_Card’ before creating another.

in which case you’ll need to decide whether your primary key is best suited to be clustered, if not then you’ll need to drop the primary key constraint and then re-add it, for example

ALTER TABLE FlashCard 
DROP CONSTRAINT PK_Card
GO
ALTER TABLE FlashCard 
ADD CONSTRAINT PK_Card PRIMARY KEY NONCLUSTERED (Id)

or ofcourse create the constraint in the first place as nonclustered.

References

For an excellent and far more comprehensive look at indexes, check out What every developer should know about SQL performance.

SQL Server Index Design Guide

I created a database in SQL Server (previously created in the post on Entity Framework – Code First). I decided I wanted to limit the access to the db to a readonly user.

So in SQL Server Management Studio I carried out the following

• In the Server/Security/Logins right mouse click and create a new login. Login name “readonly” and set as SQL Server authentication, assign the password “readonly” for this example. Press OK.
• Under the Databases section select the database we want to apply this user to, so in this case StorePlanDatabase
• In the Security/Users section, right mouse click and select New User…
• Click the ellipse (…) button next tot he Login name textbox
• Check the [readonly] user
• Next select the Securables item on the left of the dialog and press the Search button
• Select specific objects…, press OK
• Select Object Types…
• Check the Tables option
• Select Browse.. and check the tables you want to alter permissions for (in my case the three tables I added)
• Now Grant Select permission to each table and press OK

Now to test this, I’m going to change the connection string in the client application from the Code First post to something this

Server=MyServer;Database=StorePlanDatabase;User Id=readonly;Password=readonly

and with the following simply code to test this

using(StorePlanDatabase context = new StorePlanDatabase())
{
   foreach(var p in context.Products)
   {
      Console.WriteLine(p.Name);
   }
}

This should now list the names of all our products stored in the database, but it doesn’t prove the permissioning worked, so if we replace the foreach loop above with

Product peas = new Product
{
   Name = "Peas",
   UPC = "9999"
};

context.Products.Add(peas);
context.SaveChanges();

We’ll get an exception due to not having valid INSERT permissions.