SQL Server Table Name Limitations

Table Name in SQL Server can be a maximum of 128 characters (except local temporary table names that cannot exceed 116 characters) and must follow identifier rules undermentioned:

·         The first character must be one of the following

o   Character from a-z or A-Z

o   Underscore (_), at sign (@), or number sign (#)

    If a table name starts with number sign (#) then this table will be created as temporary table in TempDB and will be access only through current connection.

    If a table name starts with double number sign (##) then this table will be created as global temporary table in TempDB and will be access only through current connection.

·         Subsequent characters can include the following:

o   Character a-z or A-Z

o   Digit 0-9

o   Underscore (_), at sign (@), number sign (#) or dollar sign ($)

·         The identifier must not be a T-SQL reserved word.

·         Special characters are not allowed.

CREATE a Database

What is a database
A database in SQL Server is made up of a collection of tables that stores a specific set of structured data. A table contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes. Each column in the table is designed to store a certain type of information, for example, dates, names, dollar amounts, and numbers.

How to create a Database in SQL Server?

We can create databas in SQL Server by following ways:

1. Through SQL Server Management Studio
2. Through T-SQL command.

Create a database SQL Server Management Studio (SSMS):

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Right-click Databases, and then click New Database.
3. In New Database, enter a database name.
4. To create the database by accepting all default values, click OK; otherwise, continue with the following optional steps.
5. To change the owner name, click (…) to select another owner.
6. To change the default values of the primary data and transaction log files, in the Database files grid, click the appropriate cell and enter the new value.
7. To change the collation of the database, select the Options page, and then select a collation from the list.
8. To change the recovery model, select the Options page and select a recovery model from the list.
9. To change database options, select the Options page, and then modify the database options.
10. To add a new filegroup, click the Filegroups page. Click Add and then enter the values for the filegroup.
11. To add an extended property to the database, select the Extended Properties page.
    1. In the Name column, enter a name for the extended property.
    2. In the Value column, enter the extended property text. For example, enter one or more statements that describe the database.
12. To create the database, click OK.

Create a database using T-SQL:

USE [master]

GO

CREATE DATABASE [DatabaseName] ON PRIMARY

(

      NAME = N'DatabaseName'        --Logical data-file name

    , FILENAME = N'D:\Microsoft SQL Server\MSSQL\DATA\DatabaseName.mdf' --Data file

    , SIZE = 2048KB  --You can specify the size as per your requirement

    , MAXSIZE = UNLIMITED

    , FILEGROWTH = 1024KB

)

LOG ON

(

      NAME = N'DatabaseName_log'    --Logical log-file name

    , FILENAME = N'D:\Microsoft SQL Server\MSSQL\LOG\DatabaseName_log.ldf' --Log file

    , SIZE = 1024KB

    , MAXSIZE = 2048GB

    , FILEGROWTH = 1024KB

)

GO

Arguments:

• DatabaseName is the name of new database.
• NAME is the logical file name. Name is required when FILENAME is specified.
• FILENAME is the physical (operating system) file name. This is the path and file name used by the operating system when you create the file.
• SIZE is the initial size of the file. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB
• MAXSIZE is the max size to which the file can grow.
• FILEGROWTH is the automatic growth increment of the file. This is the amount of space added to the file every time new space is required

Once database is created, we can create other objects in the databases like Table, View, Stored Procedure, Functions, Triggers etc.