Home > Articles > Transact SQL Programming Articles

SQL Server Help: Creating tables using the Create Table statement

As well as creating tables in the SQL Server Enterprise Manager/Management Studio, it is also possible to create them programmatically using SQL statements.

The basic syntax for creating a database table is the following:

CREATE TABLE [dbo].[MyTable](
[MyIdentityColumn] [int] IDENTITY(1,1) NOT NULL,
[MyVarcharColumn] [varchar](100) NOT NULL,
[MyIntColumnWithNullsAllowed] [int] NULL,
[MyDateTimeColumn] [datetime] NOT NULL
)

This SQL Statement creates a table called MyTable with the owner dbo. The table is created with four columns. The first column, MyIdentityColumn is the identity column and is set up to autonumber new rows (starting at 1 for the first row, and incrementing the row number by 1 each time a row is inserted into the table). The second column, MyVarcharColumn is a varchar field of length 100 and does not accept null values. The third column, MyIntColumnWithNullsAllowed is configured to accept integer values and also nulls. Finally, the last column, MyDateTimeColumn, is a datetime field.

Creating temporary tables

The create table statement can also be used to create temporary tables. There are two types of temporary tables: local, which are accessible to the current SQL connection, and global, which are accessible to all current SQL connections. Local temporary tables have names that are prefixed with a single hash symbol (#), global temporary tables have names that are prefixed with a two hash symbols. The following SQL creates a local temporary table called #MyTempTable:

CREATE TABLE [dbo].[#MyTempTable](
[MySalesPersonID] [int] NOT NULL,
[MySalesPersonName] [varchar](100) NOT NULL,
[MySalesInLastQuarter] [int] NOT NULL,
)

Temporary tables are particularly useful if data needs to be stored temporarily by a SQL query or stored procedure and the data is only required by that query or stored procedure. Using temporary tables also means that the database is kept free of temporary data that could otherwise clutter up the database.

Useful Links

• How to calculate the difference between two dates in Transact SQL.

• The SQL Documentation Tool automatically builds technical documentation for Microsoft SQL Server databases, saving you time and money. A trial version is available for download.

• The VB Documentation Tool can automatically document your Visual Basic 6.0 applications and associated SQL Server/Microsoft Access databases. A .NET Documentation Tool for the .NET Framework is also available.

Useful Development Tools

SQL Documentation Tool

The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0 and 2000 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers, Table Relationships and Dependencies.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (10.3Mb ZIP file).

ASP Documentation Tool™

Automatically creates developer documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5.2Mb ZIP file).

.NET Documentation Tool

Automatically creates technical documentation for .NET Framework Windows and ASP.NET applications written in C# or VB.NET and SQL Server 7/2000/2005 or Microsoft Access databases associated with the application. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5Mb ZIP file).

VB Documentation Tool

The VB Documentation Tool creates technical documentation for Microsoft Visual Basic 6.0 projects. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1Mb ZIP file).

Indexing Service Companion

The Indexing Service Companion is a Windows application that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from remote websites and also from ODBC databases. As such it can be used as a low cost alternative to Sharepoint Portal Search Services.

Try Sample Search Facility.     Download Trial Version (1.7Mb ZIP file).

The Website Utility

The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is able to create both client-side JavaScript Search Engines and server-side ASP Search Engines for a website.

View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

PHP Documentation Tool™

Automatically creates developer documentation for PHP web applications. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1.0Mb ZIP file).