Friday, May 20, 2016

Create Table in SQL Server 2014

These are the steps to create a table in a SQL Server 2014 database using SQL Server Management Studio (SSMS).

Ensuring you have the correct database expanded (in our case, the TaskTracker database), right click on the Tables icon and select Table... from the contextual menu:

A new table will open in Design view. While you have this screen open, do the following:
Using the values in the screenshot, complete the details in the Column Name column, the Data Type column, and Allow Nulls column.
Make the TaskId column a Primary Key field by right-clicking the button next to TaskId (i.e. the same area where the key appears in the following screenshot) and selecting Set Primary Key.
Make the TaskId column an identity column by setting Is Identity to Yes (you can find this option under theIdentity Specification section in the bottom pane). Note that to set values in the bottom pane, you need to select the column name in the top pane first. We're setting this column to be an auto-number column - it will automatically generate a new number for each record that is created.
Set the Default Value of the DateCreated column to (getdate()). (This will automatically insert the current date into that field for each new record).

What we are doing is creating the column names, specifying the type of data that can be entered into them, and setting default values. Restricting the data type for each column is very important and helps maintain data integrity. For example, it can prevent us from accidentally entering a task name into a field for storing the current date. SQL Server will prevent data from entering tables where the data doesn't adhere to the rules that we've set for each column.
Save the table by selecting File > Save Table_1 or by right-clicking on the Table's tab and selecting Save Table_1 from the contextual menu:

When prompted, name your table (we'll call it Tasks):

Your New Table
Now that you've created a new table, it will appear under your database in the Tables section. If you don't see it immediately, try refreshing the Object Explorer. You can do this by right-clicking on Tables and clicking Refresh from the contextual menu:

Here's a screenshot of the table with the Columns folder expanded:

2 comments: