DDL commands in SQL Server with examples

DDL (Data Definition Language): 

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

Examples of DDL commands:


CREATE - is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

Syntax of table creation:

CREATE TABLE [SCHEMA].TABLE_NAME(COLUMN_NAME COLUMN_DATATYPE)

Example of table creation:

CREATE TABLE dbo.Students(Id INT IDENTITY(1,1) PRIMARY KEY, age INT, gender CHAR(1))
GO

Syntax of view creation:

CREATE VIEW [SCHEMA].VIEW_NAME
AS
SELECT_STATEMENT

Example of view creation:

CREATE VIEW dbo.Students_VW
AS
SELECT
  Id,
  age,
  gender
FROM
  Students
GO


DROP - is used to delete objects from the database.

Syntax of dropping a table:

DROP TABLE [SCHEMA].TABLE_NAME

Example of dropping a table:

DROP TABLE dbo.Students


ALTER - is used to alter the structure of the database.

Syntax of altering size of a column in table:

ALTER TABLE [SCHEMA].TABLE_NAME
ALTER COLUMN COLUMN_NAME DATA_TYPE(DATA_LENGTH)

Example of altering a column size of a table:

ALTER TABLE dbo.Students
ALTER COLUMN gender VARCHAR(20)

Syntax of adding a new column in a table:

ALTER TABLE [SCHEMA].TABLE_NAME
ADD COLUMN_NAME DATA_TYPE(DATA_LENGTH)

Example of adding a new column in a table:

ALTER TABLE dbo.Students
ADD salary float


TRUNCATE - is used to remove all records from a table, including all spaces allocated for the records are removed.

Syntax of truncating a table:

TRUNCATE TABLE [SCHEMA].TABLE_NAME

Example of truncating a table:

TRUNCATE TABLE dbo.Students


Conclusion: 

So today we saw the SQL DDL Commands that we use very frequently while writing queries in SQL Server.




10 Ways to Improve SQL Query Performance

Overview

SQL query performance improvement is a very thought-provoking topic between developers and the user community. Users always wants a fast response on their data retrieval action and developers put forth their best efforts to provide the data in the shortest time, however, there is no straightforward way to define what is the best performance. Sometime it’s debatable what is good and what is bad performance of a query but overall if you follow best practices during development, you can provide the best query response to users and avoid such discussions.

You can choose multiple ways to improve SQL query performance, which falls under various categories like re-writing the SQL query, creation and use of Indexes, proper management of statistics, etc.

In this post we will see 10 different methods to improve SQL query performance.

10 Ways to Improve SQL Query Performance

1. Avoid Multiple Joins in a Single Query

Try to avoid writing a SQL query using multiple joins that includes outer joins, cross apply, outer apply and other complex sub queries. It reduces the choices for Optimizer to decide the join order and join type. Sometime, Optimizer is forced to use nested loop joins, irrespective of the performance consequences for queries with excessively complex cross apply or sub queries.


10 Ways to Improve SQL Query Performance

2. Eliminate Cursors from the Query

Try to remove cursors from the query and use set-based query; set-based query is more efficient than cursor-based. If there is a need to use cursor than avoid dynamic cursors as it tends to limit the choice of plans available to the query optimizer. For example, dynamic cursor limits the optimizer to using nested loop joins.

10 Ways to Improve SQL Query Performance

3. Avoid Use of Non-correlated Scalar Sub Query

You can re-write your query to remove non-correlated scalar sub query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch. This will give better options to Optimizer, which may help to return accurate cardinality estimates along with a better plan.

10 Ways to Improve SQL Query Performance

4. Avoid Multi-statement Table Valued Functions (TVFs)

Multi-statement TVFs are more costly than inline TFVs. SQL Server expands inline TFVs into the main query like it expands views but evaluates multi-statement TVFs in a separate context from the main query and materializes the results of multi-statement into temporary work tables. The separate context and work table make multi-statement TVFs costly.

10 Ways to Improve SQL Query Performance

5. Creation and Use of Indexes

We are aware of the fact that Index can magically reduce the data retrieval time but have a reverse effect on DML operations, which may degrade query performance. With this fact, Indexing is a challenging task, but could help to improve SQL query performance and give you best query response time.

10 Ways to Improve SQL Query Performance

6. Understand the Data

Understand the data, its type and how queries are being performed to retrieve the data before making any decision to create an index. If you understand the behavior of data thoroughly, it will help you to decide which column should have either a clustered index or non-clustered index. If a clustered index is not on a unique column then SQL Server will maintain uniqueness by adding a unique identifier to every duplicate key, which leads to overhead. To avoid this type of overhead choose the column correctly or make the appropriate changes.


10 Ways to Improve SQL Query Performance

7. Create a Highly Selective Index

Selectivity define the percentage of qualifying rows in the table (qualifying number of rows/total number of rows). If the ratio of the qualifying number of rows to the total number of rows is low, the index is highly selective and is most useful. A non-clustered index is most useful if the ratio is around 5% or less, which means if the index can eliminate 95% of the rows from consideration. If index is returning more than 5% of the rows in a table, it probably will not be used; either a different index will be chosen or created or the table will be scanned.


10 Ways to Improve SQL Query Performance

8. Position a Column in an Index

Order or position of a column in an index also plays a vital role to improve SQL query performance. An index can help to improve the SQL query performance if the criteria of the query matches the columns that are left most in the index key. As a best practice, most selective columns should be placed leftmost in the key of a non-clustered index.

10 Ways to Improve SQL Query Performance

9. Drop Unused Indexes

Dropping unused indexes can help to speed up data modifications without affecting data retrieval. Also, you need to define a strategy for batch processes that run infrequently and use certain indexes. In such cases, creating indexes in advance of batch processes and then dropping them when the batch processes are done helps to reduce the overhead on the database.

10 Ways to Improve SQL Query Performance

10. Statistic Creation and Updates

You need to take care of statistic creation and regular updates for computed columns and multi-columns referred in the query; the query optimizer uses information about the distribution of values in one or more columns of a table statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.

Conclusion:

We discussed how SQL query performance can be improved by re-writing a SQL query, creation and use of Indexes, proper management of statistics and we revisited schema definitions. There are many more areas that can be looked at to improve the SQL query performance like using query hints, table hints and plan hints, etc.

Overall we explored 10 different ways to improve SQL query performance, which isn't much for this subject. You can come across many other ways with your experience and deeper knowledge of your SQL query execution plan to get better performance. You can also refer previous articles to understand SQL query execution plan and its operator.

How to send mail from SQL Server?

Below are the steps to send email from a database in Microsoft Sql Server:

Step 1: Get the database default mail profile using the below query, if you don't have a database mail profile create one.

DECLARE @defaultProfileName NVARCHAR(100)=''
SELECT TOP 1 @defaultProfileName=name
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1

Step 2: Use the below query to send email with the appropriate arguments such as toEmailAddress, ccEmailAddress, bccEmailAddress, subject, mailBody etc.

DECLARE @toEmailAddress NVARCHAR(100), @ccEmailAddress NVARCHAR(100), @bccEmailAddress NVARCHAR(100), @subject NVARCHAR(200), @mailBody NVARCHAR(MAX),@filePath NVARCHAR(MAX)
SET @toEmailAddress = 'abc@gmail.com'
SET @ccEmailAddress = 'def@gmail.com'
SET @bccEmailAddress = 'ghi@gmail.com'
SET @subject = 'test subject'
SET @mailBody = 'body of the email'
SET @filePath = 'C:\Test\abc.pdf'
IF(ISNULL(@defaultProfileName,'')<>'')
BEGIN
     EXEC msdb.dbo.sp_send_dbmail @profile_name=@defaultProfileName,
    @recipients=@toEmailAddress,
    @copy_recipients=@ccEmailAddress,
    @blind_copy_recipients=@bccEmailAddress,
    @subject=@subject,
    @body=@mailBody,
    @body_format = 'HTML',
    @file_attachments=@filePath
END

Query to check Default Database Mail profile in SQL Server?

Query to check default Database Mail profile in SQL Server?

SELECT TOP 1 name
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1

Suppose if you want to take the value in a variable then you can use the below query:

DECLARE @defaultProfileName NVARCHAR(100)=''
SELECT TOP 1 @defaultProfileName=name
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1


Followers

Popular Posts