SQL Server inline table-valued functions

August 29, 2019 by Esat Erkec

In this article series, we will find basics and common usage scenarios about the inline table-valued functions and we will also be consolidating your learnings with practical examples.

Naming Convention

August 29, 2019 by SQLShack

APX1088 – Parameters names not specified explicitly Description: This rule evaluates the T-SQL script for executing a stored procedure without referencing parameters explicitly by name. Although it is possible, it is considered a best practice to provide the names of the parameters. For more information visit https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure?view=sql-server-2017 Example script:

EXEC proc_ListAllParam

12345,

54321,

32145;

EXEC proc_ListAllParam

@FirstNumeber = 12345,

@SecondNumeber = 54321,

@ThirdNumeber = 32145;

Message: Explicitly named parameters not […]

Query

August 29, 2019 by SQLShack

APX1090 – Avoid Select(*) Description: This rule evaluates the T-SQL script for using “SELECT *”. Avoid using SELECT * in your script unless it is required. Always explicitly list the columns in a query to create more maintainable and predictable code. For more information visit https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select Example script:

SELECT * FROM FirstTable;

SELECT a, b, c FROM SecondTable;

UPDATE ThirdTable SET a = 0;

SELECT * FROM FourthTable;

Message: Statement contains SELECT * APX1091 […]

Performance

August 28, 2019 by SQLShack

APX1092 – HAVING with non-aggregate functions Description: This rule evaluates the T-SQL script for using HAVING with non-aggregate functions. HAVING should be used only with aggregate functions. For more information visit https://www.techonthenet.com/sql/having.php Example script:

SELECT DataVal,

MAX(price)

FROM FirstFailedTable

GROUP BY DataVal

HAVING DataVal LIKE '%abc%';

SELECT DataVal,

MAX(price)

FROM SecondFailedTable

GROUP BY DataVal

HAVING DataVal LIKE '%def%';

SELECT DataVal,

MAX(price)

FROM ThirdFailedTable

GROUP BY DataVal

HAVING DataVal LIKE '%111%';

SELECT DataVal,

MAX(price)

FROM FirstGoodTable

GROUP BY DataVal

HAVING SUM(ID) < 20;

SELECT DataVal,

MAX(price)

FROM SecondGoodTable

GROUP BY DataVal

HAVING SUM(ID) < 200;

Message: HAVING clause is used in a non aggregate function APX1096 – Comparison with NULL Description: This rule evaluates the […]

Design

August 28, 2019 by SQLShack

APX1129 – Index type not specified explicitly Description: This rule evaluates the T-SQL script for NOT specifying the Index types, CLUSTERED or NONCLUSTERED explicitly. If not specified, the CREATE INDEX statement will be used to create non-clustered index. It is recommended to specify the type of the index as Clustered or Non-clustered in the CREATE […]

SQL Server ALTER TABLE ADD Column overview

August 27, 2019 by Dinesh Asanka

In this article, we will explore SQL Server ALTER TABLE ADD Column statements to add column(s) to an existing table. We will also understand the impact of adding a column with a default value and adding and updating the column with a value later on larger tables.

Deprecated features

August 27, 2019 by SQLShack

APX1094 – Non-ANSI outer join Description: This rule evaluates the T-SQL script for using Non-ANSI outer join syntax e.g. *=, =* that has been deprecated. Use ANSI OUTER JOIN statement instead. For more information visit https://docs.microsoft.com/en-us/sql/sql-server/install/outer-join-operators-and-are-not-supported-in-90-or-later-compatibility-modes?view=sql-server-2014 Example script:

SELECT * FROM Employee, Department WHERE Employee.DepartmentID *= Department.DepartmentID;

SELECT * FROM Employee, Department WHERE Employee.DepartmentID =* Department.DepartmentID;

SELECT * FROM Employee, Department, Person WHERE Employee.DepartmentID =* Department.DepartmentID AND Employee.PersonID *= Person.ID;

SELECT * FROM Employee LEFT OUTER JOIN Departmen ON Employee.DepartmentID = Department.DepartmentID;

SELECT * FROM Employee RIGHT OUTER JOIN Departmen ON Employee.DepartmentID = Department.DepartmentID;

SELECT * FROM Employee FULL OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

Message: Statement uses Non-ANSI outer join APX1119 – String = expression_alias Description: This rule evaluates the […]

Code

August 27, 2019 by SQLShack

APX1086 – Check syntax errors Description: This rule evaluates the T-SQL script for any syntax error, by parsing the T-SQL statement and show any errors. It is recommended to check the documentation for each T-SQL statement in order to use it in the query properly. For more information visit https://www.tsql.info/ Example script:

SELECT * from FirstTable;

SELECT * frm SecondTable;

SELECT * form ThirdTable;

DELETE from FourthTable wher id > 0;

SELECT * from FifthTable;

SELECT q from SixthTable;

Message: Incorrect […]

SQL code smells rules

August 27, 2019 by SQLShack

Code APX1086 – Check syntax errors APX1095 – GOTO Usage APX1112 – Semi-colons statement terminator APX1113 – Uninitialized variable APX1114 – Unused parameter APX1123 – SET NOCOUNT OFF Usage APX1131 – Un-closed cursor APX1132 – Cursor not deallocated APX1133 – RAISERROR with severity above 18 missing WITH LOG clause APX1136 – Direct use of Date-Time […]

SET NOCOUNT ON statement usage and performance benefits in SQL Server

August 26, 2019 by Rajendra Gupta

Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it.

SQL Server CHECKPOINT, Lazy Writer, Eager Writer and Dirty Pages in SQL Server

August 22, 2019 by Rajendra Gupta

Database administrators should be aware of the internal SQL Server processes such as the dirty pages, SQL Server CHECKPOINT, Lazy writer process. This is a very common question that you might come across in SQL DBA technical interviews as well on all levels such as beginner, intermediate and expert level.

KILL SPID command in SQL Server

August 21, 2019 by Rajendra Gupta

This article gives an overview of the KILL SPID command and how to monitor the rollback progress.

sp_updatestats overview and usage

August 20, 2019 by Esat Erkec

In this article, we will learn usage details of the sp_updatestats built-in store procedure which helps to update all statistics in a SQL Server database. First of all, we will take a glance at the statistics concept in SQL Server.

Performance Monitoring via SQL Server Query Store

August 19, 2019 by Ben Richardson

SQL Server Query Store is a performance monitoring tool that helps us evaluate the performance of a SQL query in terms of several different performance metrics such as CPU and Memory Consumption, execution time and the I/O cycles consumed by the query. Query store is similar to the windows “Task Manager”. A task manager provides […]

Understanding Graph Databases in SQL Server

August 16, 2019 by Ben Richardson

A graph database is a type of NoSQL database that is based on graph theory. Graph databases are ideal for storing data that has complex many to many relationships. In this article, we will study the very basics of graph databases with the help of a simple example.

SSIS Conditional Split Transformation overview

August 16, 2019 by Rajendra Gupta

This article explores the SSIS Conditional Split Transform task to split data into multiple destinations based on the specified conditions.

SQL Server Merge Replication on Linux

August 15, 2019 by Jignesh Raiyani

Replication is a process to manage multiple copies of the same data at a different node. Microsoft SQL Server supports Merge Replication, Transaction Replication, Peer to Peer Replication and Snapshot Replication.

Dynamic SQL in SQL Server

August 15, 2019 by Ranga Babu

In this article, we will review how to construct and execute dynamic SQL statements in SQL Server with different examples.

Overview of the Shrink TempDB database in SQL Server

August 15, 2019 by Rajendra Gupta

This article explores the usage of TempDB and different ways to shrink the TempDB database in SQL Server

SQL Server Statistics and how to perform Update Statistics in SQL

August 13, 2019 by Rajendra Gupta

This article gives a walk-through of SQL Server Statistics and different methods to perform SQL Server Update Statistics.

Monitoring SQL Server deadlocks using the system_health extended event

August 12, 2019 by Rajendra Gupta

Performance monitoring is a must to do the task for a DBA. You should ensure that the database performance is optimal all the time without any impact on the databases. Performance issues act like an open stage, and you need to look at every aspect such as CPU, RAM, server performance, database performance, indexes, blocking, […]

Snapshot Isolation in SQL Server

August 5, 2019 by Gerald Britton

Introduction In this article, I’ll explore the use of snapshot isolation using row versioning as an alternative to the classic ANSI SQL transaction levels.

FOR XML PATH clause in SQL Server

July 30, 2019 by Rajendra Gupta

As SQL professionals, we often have to deal with XML data in our databases. This article will help you walk through several examples of using ‘FOR XML PATH’ clause in SQL Server.

A complete guide to T-SQL Metadata Functions in SQL Server

July 30, 2019 by Gauri Mahajan

In this article, we will demonstrate T-SQL metadata functions available in the SQL Server.

SQL Server stored procedures for beginners

July 29, 2019 by Ranga Babu

In this article, we will learn how to create stored procedures in SQL Server with different examples.