July 24, 2022

Sql Trainning - 13

What is a schema in SQL Server

A schema is a collection of database objects including tables, views, triggers, stored procedures, indexes, etc. A schema is associated with a username which is known as the schema owner, who is the owner of the logically related database objects.

A schema always belongs to one database. On the other hand, a database may have one or multiple schemas. For example, in our BikeStores sample database, we have two schemas: sales and production. An object within a schema is qualified using the schema_name.object_name format like sales.orders. Two tables in two schemas can share the same name so you may have hr.employees and sales.employees.

Built-in schemas in SQL Server

SQL Server provides us with some pre-defined schemas which have the same names as the built-in database users and roles, for example: dbo, guest, sys, and INFORMATION_SCHEMA.

Note that SQL Server reserves the sys and INFORMATION_SCHEMA schemas for system objects, therefore, you cannot create or drop any objects in these schemas.

The default schema for a newly created database is dbo, which is owned by the dbo user account. By default, when you create a new user with the CREATE USER command, the user will take dbo as its default schema.

SQL Server `CREATE SCHEMA` statement overview

The CREATE SCHEMA statement allows you to create a new schema in the current database.

The following illustrates the simplified version of the CREATE SCHEMA statement:

CREATE SCHEMA schema_name
    [AUTHORIZATION owner_name]
Code language: SQL (Structured Query Language) (sql)

In this syntax,

First, specify the name of the schema that you want to create in the CREATE SCHEMA clause.
Second, specify the owner of the schema after the AUTHORIZATION keyword.

SQL Server `CREATE SCHEMA` statement example

The following example shows how to use the CREATE SCHEMA statement to create the customer_services schema:

CREATE SCHEMA customer_services;
GO
Code language: SQL (Structured Query Language) (sql)

Note that GO command instructs the SQL Server Management Studio to send the SQL statements up to the GO statement to the server to be executed.

Once you execute the statement, you can find the newly created schema under the Security > Schemas of the database name.

If you want to list all schemas in the current database, you can query schemas from the sys.schemas as shown in the following query:

SELECT 
    s.name AS schema_name, 
    u.name AS schema_owner
FROM 
    sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY 
    s.name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

After having the customer_services schema, you can create objects for the schema. For example, the following statement creates a new table named jobs in the customer_services schema:

CREATE TABLE customer_services.jobs(
    job_id INT PRIMARY KEY IDENTITY,
    customer_id INT NOT NULL,
    description VARCHAR(200),
    created_at DATETIME2 NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server CREATE SCHEMA statement to create a new schema in the current database.

Dynamic SQL in SQL Server

Dynamic SQL is a programming technique where you build SQL query as a string and execute it dynamically at runtime. It lets you build the general-purpose query on the fly using variables, based on the requirements of the application. This makes a dynamic SQL more flexible as it is not hardcoded.

DECLARE @sql nvarchar(max) --declare variable

DECLARE @empId nvarchar(max) --declare variable for parameter

set @empId = '4' --assign value to parameter variable

set @sql = 'SELECT * FROM employees WHERE EmployeeID =' + @empId --build query string with parameter

exec(@sql) --execute sql query

------------------------------------------

Sequence in SQL Server

In SQL Server, the sequence is a schema-bound object that generates a sequence of numbers either in ascending or descending order in a defined interval. It can be configured to restart when the numbers get exhausted.

A Sequence is not associated with any table.
You can refer a sequence to generate values with specific increment and interval on each execution by using NEXT VALUE FOR. You don't need to insert a row in a table (like identity column) to generate the sequence.

Use the CREATE SEQUENCE statement to create a sequence.

Syntax:
CREATE SEQUENCE [schema_name.] sequence_name  
    [ AS [ integer_type ] ]  
    [ START WITH start_value ]  
    [ INCREMENT BY increment_value ]  
    [ { MINVALUE [ minvalue } | { NO MINVALUE } ]  
    [ { MAXVALUE [ maxvalue ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ size ] } | { NO CACHE } ]; 

In the above syntax:

schema_name: SCHEMA associated with the Sequence.

sequence_name: A unique name given to the sequence in a database.

integer_type: A sequence is defined with any of the integer types as tinyint, smallint, int, bigint, numeric, decimal, or a user - defined data type.

start_value: The first value in the sequence.

increment_value: This is the interval between two consecutive sequence values. If the increment value is negative, then the sequence is a decreasing sequence else it is ascending. The default increment value is 1. The Increment cannot be 0.

minvalue | NO MINVALUE: This specifies the lower bound for a sequence. If not specified, it defaults to the minimum value of the data type of the sequence.

maxvalue | NO MAXVALUE : specifies the upper bound for the sequence. It defaults to the maximum value of the data type of the sequence.

CYCLE | NO CYCLE: Specifies whether the sequence object should restart from the minimum value (maximum value for descending sequence) or raise an exception when the minimum (or maximum) value is reached. NO CYCLE is the default value.

Note: Cycling will restart the sequence from the minimum or maximum value and not from the start value.

CACHE [ size ] | NO CACHE: Improves performance for applications using sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

Note: SQL Serve pre-allocates the number of sequence numbers specified by the CACHE.

Let's create a simple Sequence that starts from 5 with an increment of 2.

Example: Create Sequence
CREATE SEQUENCE SequenceCounter
    AS INT
    START WITH 5
    INCREMENT BY 2;

The new sequence is created under the Programmability -> Sequence folder, as shown below.

You can now execute the above sequence SequenceCounter using NEXT VALUE FOR <sequence-name>. The SequenceCounter will return 5 when you execute it for the first time.

Example: Use Sequence
SELECT NEXT VALUE FOR SequenceCounter AS Counter;

Now, execute the same sequence again. The counter is incremented by 2 as specified in the CREATE SEQUENCE statement.

Example: USe Sequence
SELECT NEXT VALUE FOR SequenceCounter AS Counter;

Every time you execute the SequenceCounter, the counter is incremented by 2.

Create a Sequence with Min, Max, Cycle

In the following example, a sequence is created with data type as Decimal(3,0). It starts with 10 and every time the sequence is executed, it is incremented by 5. The maximum value is 500. It stops after reaching 500 and since CYCLE is specified, the counter restarts from 10 again.

Example: Decimal Sequence
CREATE SEQUENCE dbo.MyDecSequence  
    AS decimal (3,0)   
    START WITH 10  
    INCREMENT BY 5  
    MINVALUE 10  
    MAXVALUE 500  
    CYCLE  
    CACHE 5 ;

Use SEQUENCE with a Table

You can use a sequence with the table while inserting or updating records. For example, consider the following table.

Example: Create a Table
CREATE TABLE Training(
    TrainingId int PRIMARY KEY,
    TrainingName nvarchar(50) NOT NULL,
    TrainingDate date NOT NULL)
);

Now, use the SequenceCounter sequence in the INSERT statement to insert TrainingId values, as shown below.

Example: Use Sequence in Insert Statement
INSERT INTO Training(TrainingId, TrainingName, TrainingDate)
VALUES(NEXT VALUE FOR SequenceCounter, 'SEO' , '11/23/2022');

INSERT INTO Training(TrainingId, TrainingName, TrainingDate)
VALUES(NEXT VALUE FOR SequenceCounter, 'SQL Server', '11/24/2022');

Now, let's check the inserted values using the select query.

Example:
SELECT * FROM dbo.Training

------------------

You can see that the TrainingId column of the Training table is populated with numbers generated by using the sequence.

Though, the IDENTITY column also generates sequence numbers, there are a few instances where Sequence is used instead of identity column:

You want to share the sequence number across multiple tables.
You need to generate a sequence number in the application before inserting it into a table.
You need the counter to restart after a certain number is reached.
To get several sequence numbers at the same time. You can use the stored procedure sp_sequence_get_range to retrieve several numbers at the same time

-----------------------------------------------------------------

Synonyms in SQL Server

In SQL Server, the synonym is the database object that provides alternate name (alias) to another database objects such as table, view, stored procedure, etc. in the local server or a remote server. It provides a layer of abstraction and protects the client application in case of a name change or location change made to the base object.

For example, the local MyDBServer contains the HR database that includes the Employee table. Now, the remote client application has to refer to this table with full name like MyDBServer.HR.dbo.Employee. In this scenario, you can create a Synonym for the Employee table which can be used in the client application.

Create Synonym

A few points to consider while creating a synonym:

A synonym must have a unique name just like other database objects in a schema.
A synonym cannot be a base object for another synonym.
A synonym cannot reference a user -defined aggregate function.

Use CREATE SYNONYM in T-SQL to create a new synonym in SQL Server.

Syntax:
CREATE SYNONYM [schema_name.] synonym_name FOR object

To create a new synonym in the HR database, open and login to the SQL Server Management Studio and select New Query. Copy the following query to the query window and execute.

Example: Create a Synonym
CREATE SYNONYM Emp FOR dbo.Employee;  

Here Emp is the synonym name and Employee is the database table for which a synonym is created.

The new synonym is created under the Synonym folder of the HR database, as shown below.

You can now reference the Employee table using the synonym Emp. Let's insert data using Emp synonym. The following shows the data in the Employee table.

Now, use the synonym Emp to insert values into the Employee table, as shown below.

Example: Insert Data using Synonym
INSERT INTO Emp
values('Amit', 'Saha', 'AH23@abc.com', '22545678',  '10/03/2017',  2, 67000, 20);

Now, let's check if the new row is inserted or not, as shown below.

To view all the synonyms, use the following SELECT statement:

Example: Get All Synonyms
SELECT name, base_object_name, type
FROM sys.synonyms;

Alter Synonyms

You cannot alter a synonym. To make changes, you first have to drop the synonym and re-create it.

Delete Synonyms

Use the DROP SYNONYM statement to delete a synonym.

The following deletes the Emp synonym.

Example: Delete a Synonym
DROP SYNONYM Emp;

----------------------------------------

Control User Access

Differentiate system privileges from object privileges Create Users

Grant System Privileges

Create and Grant Privileges to a Role

Change Your Password

Grant Object Privileges

How to pass on privileges?

Revoke Object Privileges

Regular Expression Support

Use the Regular Expressions Functions and Conditions in SQL Use

SQL Regex

Below I have listed down all the Regular Expressions that can be used in SQL.

Pattern	Description
*	Matches zero or more instances of the preceding String
+	Matches one or more instances of the preceding String
.	Matches any single character
?	Matches zero or one instance of the preceding Strings
^	^ matches the beginning of a String
$	$ matches the ending of a String
[abc]	Matches any character listed in between the square brackets
[^abc]	Matches any character not listed in between the square brackets
[A-Z]	Matches any letter in uppercase
[a-z]	Matches any letter in lowercase
[0-9]	Matches any digit between 0-9
[[:<:]]	Matches the beginning of words
[[:>:]]	Matches the end of words
[:class:]	Matches any character class
p1\|p2\|p3	Mathes any of the specified pattern
{n}	Matches n instances of the preceding element
{m,n}	Matches m through n instances of the preceding element

Meta Characters with Regular Expressions

Perform a Basic Search using the REGEXP_LIKE function Find patterns using the REGEXP_INSTR function Extract Substrings using the REGEXP_SUBSTR function Replace Patterns Using the REGEXP_REPLACE function Usage of Sub-Expressions with Regular Expression Support

Implement the REGEXP_COUNT function

Manage Objects with Data Dictionary Views

Explain the data dictionary

Use the Dictionary Views

USER_OBJECTS and ALL_OBJECTS Views

Table and Column Information

Query the dictionary views for constraint information

Query the dictionary views for view, sequence, index, and synonym information Add a comment to a table

Query the dictionary views for comment information

Manipulate Large Data Sets

Use Subqueries to Manipulate Data

Retrieve Data Using a Subquery as Source

Insert Using a Subquery as a Target

Usage of the WITH CHECK OPTION Keyword on DML Statements List the types of Multitable INSERT Statements

Use Multitable INSERT Statements

Merge rows in a table

Track Changes in Data over a period of time

---------

Create, maintain, and use sequences

Create private and public synonyms

------------

Data Management in Different Time Zones

Time Zones

CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP þÿCompare Date and Time in a Session s Time Zone DBTIMEZONE and SESSIONTIMEZONE

Difference between DATE and TIMESTAMP

INTERVAL Data Types

Use EXTRACT, TZ_OFFSET, and FROM_TZ

Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries

Multiple-Column Subqueries

Pairwise and Nonpairwise Comparison

Scalar Subquery Expressions

Solve problems with Correlated Subqueries

Update and Delete Rows Using Correlated Subqueries The EXISTS and NOT EXISTS operators

Invoke the WITH clause

The Recursive WITH clause

Search This Blog

Tech Conscious

Sql Trainning - 13

What is a schema in SQL Server

Built-in schemas in SQL Server

SQL Server `CREATE SCHEMA` statement overview

SQL Server `CREATE SCHEMA` statement example

Dynamic SQL in SQL Server

Sequence in SQL Server

Create a Sequence with Min, Max, Cycle

Use SEQUENCE with a Table

Synonyms in SQL Server

Create Synonym

Alter Synonyms

Delete Synonyms

SQL Regex

Comments

Post a Comment

Popular Posts

How to use PostgreSql With .Net

SQL Interview Questions

Sql Trainning - 13

What is a schema in SQL Server

Built-in schemas in SQL Server

SQL Server CREATE SCHEMA statement overview

SQL Server CREATE SCHEMA statement example

Dynamic SQL in SQL Server

Sequence in SQL Server

Create a Sequence with Min, Max, Cycle

Use SEQUENCE with a Table

Synonyms in SQL Server

Create Synonym

Alter Synonyms

Delete Synonyms

SQL Regex

Comments

Post a Comment

Popular Posts

How to use PostgreSql With .Net

SQL Interview Questions

SQL Server `CREATE SCHEMA` statement overview

SQL Server `CREATE SCHEMA` statement example