In a relational database, tables are at the focal point of activity. Tables store all of your data and serve as the essential interface to any applications or client interactions. In this article, you will get good ideas about how to make your own tables. After you decide how you need your tables to be organized, SQL Server offers numerous settings that you can use to change the conduct of these tables and their columns.
SQL Server does an extraordinary job of securing your data. But you can go the additional mile by exploiting a database idea known as “requirements.” In this level, we quickly demonstrate to you industry standards that offer imperatives to expand the security of your information. Perspectives are another helpful database capacity, so we will look at how to make them by utilizing the SQL Server Management Studio.
Conclusively, in light of the fact that nobody likes to enter code by hand, SQL Server offers supportive scripting capacities that permit you to mechanize normal database support assignments, for example, making new tables. You’ll see how to create scripts rapidly at whatever point you have to make or keep up a table.
How to Build New Tables Using SQL Server Management Studio.
To start, you will need to launch the SQL Server Management Studio by following these steps:\
- Open the SQL Server Management Studio.
- Connect to the suitable SQL server instance.
- Expand the connection’s entry in the Object Explorer view.
- Expand the Databases folder.
- Right-click the Tables folder and choose New Table.
See the diagram below:
After right-clicking the table as indicated in the diagram, create your table as described in the following steps:
Enter a unique name for each column in your table.
After you’ve done this naming, the base portion of the dialog box contains numerous configurable settings for this section, as shown in the figure below. Let us look extensively at each of these settings.
In the drop-down box, pick one of the data type shown.
Pick from the full list of data types found in Microsoft SQL Server: Bigint, Binary, Bit, Char, Datetime, Decimal, Float, Int, etc.
Allow the column to permit NULL values (optional).
You can mark the Allow Nulls check box.
Set properties for these columns.
Properties can be set either alphabetically or categorically. Below is a list of what these properties are and how they can be used.
Allow Nulls: This decides whether a section can store NULL (that is, non-existent) values. Specific types of columns, for example, primary keys, are not allowed to hold NULL values.
Collation: SQL Server stores information from several languages, settings can be set as default for the server, database, and column (for example, English, Dutch, etc). SQL Server can use a specialized set of rules for these languages. This setting can be enabled at the database or server level.
Computed Column Specification:
In SQL server, you can specify computational rules that can be executed at runtime, as shown in the figure below.
The above diagram shows a table name dbo.Accounts where only Column with Name Allows Null value as ticked. Note that other tables were blocked for security reasons.
Formulas can be provided when defining columns and we can also enable
Is Persisted by setting it from “No” to “Yes.” This instructs the SQL server to store it in the database.
When you are finished entering your columns, make sure you save your work by clicking on the save icon at the top left corner of your workspace, as highlighted in yellow below.
The above illustration can also be created using standard T-SQL. The basic syntax for creating a table in MSSQL 2008 is stated below:
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
Using the example above with dbo.Accounts table already created, we have:
CREATE TABLE [dbo].[Accounts]( [AccountId] [dbo].[vtmKey] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [Name] [dbo].[vtmName] NULL, [AccountTypeId] [dbo].[vtmKey] NOT NULL, [AccountStatusId] [dbo].[vtmKey] NOT NULL, [PartnerId] [dbo].[vtmKey] NOT NULL, [Balance] [dbo].[vtmMoney] NOT NULL, [BalanceSuspended] [dbo].[vtmMoney] NOT NULL, [LastBatchBalance] [dbo].[vtmMoney] NOT NULL, [DateAmmended] [dbo].[vtmDate] NOT NULL, [bonusCurrent] [dbo].[vtmMoney] NOT NULL, [bonusPrevious] [dbo].[vtmMoney] NOT NULL, [BonusCumulative] [dbo].[vtmMoney] NOT NULL, [PPASQuota] [dbo].[vtmMoney] NOT NULL, [Threshold] [dbo].[vtmMoney] NOT NULL, CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED ( [AccountId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]