Tables in SQL Server contain data arranged in rows and columns. Each column can contain data with a certain data type and size

Advanced data types

In addition to the basic data types such as int,float nvarchar… SQL Server also supports some advanced data types as follows:

Create, edit, delete tables

Create table (Create table)

Syntax to create table:

Docs: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

 -- Simple CREATE TABLE Syntax (common if not using options) CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } ( { <column_definition> } [ ,...n ] ) [ ; ]

Explain:

• database_name: the name of the database you want to create a table
• table_name: the name of the table you want to create, up to 128 characters
• column_name: the name of the column in the table. Up to 128 characters. Note: column name does not need to be named when the column's data type is timestamp. The default column name of the column with timestamp data type is 'timestamp'.
• data_type: defines the data type (data type) for column

For example:

 USE AdventureWorks2019 CREATE TABLE dbo.CustomerInfo( CustomerID int NOT NULL, CustomerName nvarchar (40) NOT NULL ) GO

Short syntax:

 USE AdventureWorks2019 CREATE TABLE CusInfo( CusID int identity NOT NULL, CusName varchar(40) NOT NULL )

Note, if the default is initialized, without defining the schema (schema), SQL Server will implicitly interpret the schema as dbo.

Modify table (modify table)

Syntax:

Docs: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15

 ALTER TABLE [[dabase_name.[schema_name].|schema_name.]table_name ALTER COLUMN ([<column_name>] [data_type] NULL/ NOT NULLm); | ADD ([<column_name>] [data_type] NULL/NOT NULL); | DROP COLUMN ([<column_name>]);

Explain:

• ALTER TABLE: edit table
• ALTER COLUMN: defines the column to be edited
• ADD: Add one or more columns to the table
• DROP COLUMN (<column_name>): defines the column name named <column_name> to be removed from the table

Example statement to add columns:

 ALTER TABLE [dbo].[CustomerInfo] ADD [ContactNumber] [numberic](12,0) NOT NULL;

Example column edit statement:

 ALTER TABLE [dbo].[CustomerInfo] ALTER COLUMN [CustomerID] numberic[12,0] NOTNULL

Example statement to delete columns:

 ALTER TABLE [dbo].[CustomerInfo] DROP COLUMN [ContactNumber];

Note: before deleting columns, need to make sure that columns have no constraints and can be deleted. In some constraints, columns cannot be deleted, for example, if the column uses the constraints CHECK,FOREIGN KEY, UNIQUE, PRIMARY KEY, or the column is defined by default (DEFAULT) (which will be covered in the next section). the next part).

Drop table (Drop table)

The DROP TABLe statement will delete the entire table definition, its data, and all objects associated with the table such as indexes, triggers, contrainst, permission specifications for the table.

Syntax:

 DROP TABLE <table_name>

Example delete table:

 DROP TABLE dbo.CustomerInfo

Commands to modify data

To modify the data in the table, there are INSERT,UPDATE, DELETE statements that do this job.

See the article about INSERT in SQL Server: https://hocvietcode.com/lenh-insert-trong-sql-server/

See the article about UPDATE in SQL Server: https://hocvietcode.com/lenh-update-trong-sql-server/

See the article about DELETE in SQL Server: https://hocvietcode.com/lenh-delete-trong-sql-server/

DEFAULT Definition

Consider a situation where product details must be stored in a SQL Server 2019 table, but all values for product details may not be known even at the time of data insertion. However, according to data integrity and consistency rules, columns in a record should usually contain a value. Storing null values into columns such that the exact value of the data is not known can cause some undesirable problems.

In such cases, a DEFAULT definition can be provided for the column to assign it as the default value if no value is given at the time of creation. For example, it is common to specify zero as the default for the numeric column.

The DEFAULT definition for a column can be created at table creation time or added at a later stage to the current table. When a DEFAULT definition is added to an existing column in a table, SQL Server applies the new defaults only to those new rows of data that are added to the table.

For example, the CREATE TABLE statement uses the DEFAULT keyword to define a default value for the Price . column

 CREATE TABLE StoreProduct (ProductID int NOT NULL, Name varchar(40) NOT NULL, Price money NOT NULL DEFAULT(100))

When the row is added using the command below, the Price column will have a default value of 100 in case no value is entered.

 INSERT INTO dbo.StoreProduct (ProductID,Name) VALUES (111,'Laptop Dell 5450') SELECT * FROM dbo.StoreProduct GO

Some column types cannot define DEFAULT definitions:

• A timestamp data type
• Column with IDENTITY or ROWGUIDCOL . attribute

IDENTITY Property (IDENTITY property)

The SQL Server IDENTITY property is used to create identity columns that can contain auto-generated sequential values that uniquely identify each row in a table. For example, an identity column could be created to automatically generate unique student registration numbers whenever new rows are inserted into the Students table. The identifier for the first row inserted into the table is called the seed value. The increment, also known as the Identity Increment property, is added to the seed to create an additional identifier that is automatically generated by SQL Server by adding the increment to the seed. An identity column is commonly used for primary key values.

Columns that use the IDENTITY attribute must be columns with integer data:

• decimal
• int
• numeric
• smallint
• bigint
• tinyint

The column with the IDENTITY attribute does not need to be set to the SEED (seed) value and specify the increment value ( increment ), if not specified, the SEED and INCREMENT values are equal to 1 by default.

Syntax:

 CREATE TABLE <table_name> (column_name data_type IDENTITY(seed_value,increment_value) NOT NULL)

Explain:

• seed_value: seed value starts to initialize the value for identity
• increment_value: the value incremented every time the database has a new record

For example:

 CREATE TABLE HRContactPhone (Person_ID int IDENTITY(500,1) NOT NULL, MobileNumber bigint NOT NULL)

When inserting data into the table and selecting out:

 INSERT INTO HRContactPhone(MobileNumber) VALUES (0911222334) INSERT INTO HRContactPhone(MobileNumber) VALUES (0934333123) SELECT * FROM HRContactPhone GO

Globally Unique Identifier

In addition to the identity property, SQL Server also supports globally unique identifiers. Typically, in a networked environment, multiple tables may require a column that includes a globally common unique value. Consider a situation where data from multiple database systems such as a bank database must be consolidated in a single location. When data from around the world is collated at a central authority for aggregation and reporting, using globally unique values prevents customers in different countries from having the same account number bank or customer ID. To work around this, SQL server provides globally unique identifier columns. They can be created for each table containing unique values across all computers in the network. Only on the identity column and a globally unique identifier column can be created for each table. To generate and work with globally unique identifiers, a combination of ROWGUIDCOL , uniqueidentifier , and the NEWID() functions are used to generate the identifier.

For example:

 CREATE TABLE Emp_CellularPhone(Person_ID uniqueidentifier DEFAULT NEWID() NOT NULL, PersonName varchar(60) NOT NULL)

Then put the data in the table:

 INSERT INTO Emp_CellularPhone(PersonName) VALUES ('William Smith') SELECT * FROM Emp_CellularPhone GO