Useful T-SQL Knowledge for SQL Developers and DBAs

In this article, I present useful T-SQL knowledge for SQL Developers and DBAs.

A few days ago I started writing an article that it would provide a set of useful tips on SQL Server topics that are met in the everyday life of DBA/Database Developer.

The article is now completed and you can find it below! It is organized in the following categories:

• Solutions to common issues
• Basic string functions
• Performance-related tips
• Maintenance
• Miscellaneous

A: Solutions to common issues

Below, you can find T-SQL scripts, which can be solutions to common SQL Server development technical challenges.

1: Resolving the “Divide by zero” error (by example)

DECLARE @denominator INT
SET @denominator = 0
SELECT 1 / ISNULL(NULLIF(@denominator, 0), 1)

2: Handling NULL and empty values

3: Handling the error “A transport-level error has occurred when sending the request to the server”

If the problem occurs in a SSMS Query Window, just open a new one and run the T-SQL statements again. In a different case, re-initiate the request to the SQL Server instance.

4: Handling the error “The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”

Change the default language to “us_english” for the given SQL Server login:

USE [master];
GO
ALTER LOGIN "LOGIN_NAME" WITH DEFAULT_LANGUAGE = us_english;
GO

Best practice: Always use the ISO date format in your data applications/T-SQL scripts: YYYY-MM-DD

5: Handling the error “The multi-part identifier … could not be bound”

Be careful with the use of subqueries and table aliases. Don’t forget to reference the correct table aliases in your T-SQL code.

Also, keep in mind that subqueries can only provide their results to their outer queries and not references to the subqueries’ tables.

6: Handling the error “String or binary data would be truncated”

Either use an adequate size for the table columns in which the data is inserted or cast the data by removing redundant characters. I suggest the first approach.

7: Handling the error “Error converting data type varchar to float”

8: Handling the error “Database [Database_Name] cannot be upgraded because it is read-only or has read-only files”

Make sure that the user account on which the SQL Server instance database engine is running has full access to the database files.

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Learn More

B: Basic string functions

Below, you can find useful T-SQL examples that use basic string functions in SQL Server.

1: Returns @length characters from @expression starting from @start_index

SELECT SUBSTRING(@expression, @start_index, @length)

2: Finds the given @pattern in the @string and replaces it with the @replacement_string

SELECT REPLACE(@string, @pattern, @replacement_string)

3: Returns the size of @string in terms of number of characters

SELECT LEN(@string)

4: Returns the first @num_chars characters of the @string counting from the left

SELECT LEFT(@string, @num_chars)

5: Returns the first @num_chars characters of the @string counting from the right

SELECT RIGHT(@string, @num_chars)

6: Removes the leading blank spaces

SELECT LTRIM(@expression)

7: Removes the trailing blank spaces

SELECT RTRIM(@expression)

C: Performance-related tips

Below, you can find useful performance-related T-SQL tips.

1: Avoiding locking when reading data (however, dirty reads are allowed)

SELECT [columnName]
FROM [tableName] WITH (NOLOCK)

2: Rebuilding indexes in SQL Server 2005 or later

3: Rebuilding all the indexes in a database

4: Updating database tables without causing blocking

D: Maintenance

Below, you can find useful T-SQL scripts, related to SQL Server maintenance.

1: Shrinking an entire database

DBCC SHRINKDATABASE([DBName],[PercentageOfFreeSpace]);

2: Truncating an entire database

DBCC SHRINKDATABASE([DBName],TRUNCATEONLY);

3: Shrinking a data/log file

4: Truncating a data/log file

USE [DBName];
GO
DBCC SHRINKFILE ([Data_Log_LogicalName],TRUNCATEONLY);
GO

5: Renaming a Windows login

6: Renaming a SQL Server login

ALTER LOGIN "[SQL Server Login Name]"
WITH NAME="[New SQL Server Login Name]";

7: Creating Logins for orphaned SQL Server users

USE [DBName];
GO
EXEC sp_change_users_login 'Auto_Fix', '[UserName]', NULL, '[Password]';
GO

8: Changing the Database Owner in a SQL Server Database (SQL Login)

USE [DBName];
GO
EXEC sp_changedbowner '[SQL_Login_Name]';
GO

9: Changing the Database Owner in a SQL Server Database (Windows Login)

USE [DBName];
GO
EXEC sp_changedbowner '[DomainNameUserName]';
GO

10: Backing up a Database in a Network Folder (creating the destination media)

1: Gets basic information on the current SQL Server instance

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY ('ProductLevel') AS ProductLevel,
SERVERPROPERTY ('Edition') AS Edition,
SERVERPROPERTY('MachineName') AS ServerName,
SERVERPROPERTY('ServerName') AS Server_and_Instance_Names

2: Gets basic table index information

EXEC sp_helpindex 'schema.table_name'

3: Connecting to Windows Internal Database (SSEE)

From within SSMS (Express Edition works as well):

• • Server Type: Database Engine
  • Server Name: \.pipeMSSQL$MICROSOFT##SSEEsqlquery
  • Authentication: Windows Authentication

* Make sure that the Named Pipes protocol is enabled!