MSSQL CREATE TABLE – SQL For Programmers

ka_vol1_120 Once you have your database created, you’ll want to get some tables set up into it.  To do this, you’ll need the CREATE TABLE statement that can be issued from the connection object once you’ve established the connection.

The first thing you’ll need to do before executing this command is to establish a connection to your SQL database.

Once you’ve done that, you’ll want to create a SqlCommand object passing in the CREATE TABLE command string and the connection object.

Finally, you’ll open the connection, issue the ExecuteNonQuery() method and close the connection.

The CSharp code for this looks something like:

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(createTable, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Pretty standard code that I hope you are already familiar with.

But what you probably want to know is what to put in the createTable string.  Here you go:

string createTable = @"CREATE TABLE tableName
(
ColumnName ColumnType(size)
    [IDENTITY(seed,increment)]
    [NULL|NOT NULL
    [DEFAULT defaultData]]
    [PrimaryKey]
[comma delimited column statements go here]
)";

I like to use the @ string statement so that I can create my CREATE TABLE statements in SQL Enterprise Manager and copy and paste the results into my code without having to worry about carriage return/linefeed pairs causing me to do extra editing once I paste the code into my CSharp editor.

A column statement is in the form of:

ColumnName ColumnType(size)
    [NULL|NOT NULL
    [DEFAULT defaultData]]
    [Primary Key]

Where ColumnType is one of the various data types available in T-SQL

The datatypes you are most likely to use are:

  • Int
  • varchar(size) or nvarchar(size)
  • datetime
  • decimal(precision,scale)
  • money

If you want the field to hold NULL values, you can leave the NULL or NOT NULL qualifier out, but if you want to make sure the field always holds some sort of valid data, you’ll want to include the NOT NULL statement after your type and size information.

And if the field is the primary key for the table, include the Primary Key statement at the end of the column description.

If you want the field to auto-increment, you’ll want to include the identity information.  Assuming you want to start at 1 and increment by 1, your SQL will look like:

    IDENTITY(1,1)

To put this all together in a typical CREATE TABLE string, here is what a typical CREATE TABLE string would look like for a Users table:

string createTable = @"CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Username] [nvarchar](100) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](256) NULL,
    [DisplayName] [nvarchar](128) NOT NULL DEFAULT (''),
)";

There is more to the CREATE TABLE syntax, but this should get most programmers as far as they need to go.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • SQL For Programmers – Stored Procedure BasicsSQL For Programmers – Stored Procedure Basics Now that we've established a reason for using them, let's continue with the basic syntax of the stored procedure. I think you'll find that it is not that dissimilar to writing a procedure […]
  • SQL IF/WHILE Blocks – SQL For ProgrammersSQL IF/WHILE Blocks – SQL For Programmers OK, all you programmer types.  Here's where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, […]
  • SQL For Programmers – Finding a StringSQL For Programmers – Finding a String Many times in our queries, we aren't looking for an exact match.  We are looking for one string that exists in another.  There are a couple statements available to us that will allow us […]
  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]

About Dave Bush

Dave Bush is a .NET programmer and Certified ScrumMaster who is passionate about managing risk as it relates to developing software. When he is not writing or speaking about topics related to Application Lifecycle Risk Management (ALRM), he is an example to his peers as he develops web sites in the ASP.NET environment using industry best practices.

Awards & Certs