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

----Step 1: Create the IsEmpty user-defined function
CREATE FUNCTION IsEmpty
(
@input AS VARCHAR(250),
@newValue VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
BEGIN
-- First handle the case where the input value is a NULL
DECLARE @inputFiltered AS VARCHAR(250)
SET @inputFiltered = ISNULL(@input, '')


-- The main logic goes here
RETURN (CASE RTRIM(LTRIM(@inputFiltered)) WHEN '' THEN RTRIM(LTRIM(@newValue)) ELSE RTRIM(LTRIM(@inputFiltered)) END)


END
GO

----Step 2: Usage
SELECT dbo.IsEmpty(@column_to_check, @new_value);

 

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”

----Step 1: Create the Varchar2Float user-defined function
CREATE FUNCTION [dbo].[Varchar2Float]
( @inputString VARCHAR(50) )
RETURNS FLOAT
AS
BEGIN
--Prepare the string for casting/conversion
SET @inputString = REPLACE(@inputString, '.', '')
SET @inputString = REPLACE(@inputString, ',', '.')


--Perform the conversion and return the result
RETURN CAST(@inputString AS FLOAT)
END;

----Step 2: Usage
SELECT dbo.Varchar2Float(@value)

 

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.

 


Learn Useful SQL Server Administration Tips – Enroll to the Course!

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!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

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

----Rebuild a specific index with using parameters
USE [DATABASE_NAME];
ALTER INDEX [INDEX_NAME] ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);


----Rebuild all indexes in a table with using parameters
USE [DATABASE_NAME];
ALTER INDEX ALL ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);

 

3: Rebuilding all the indexes in a database

----Rebuild all indexes online with keeping the default fill factor for each index
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)";


----Rebuild all indexes offline with keeping the default fill factor for each index
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)";


----Rebuild all indexes online with specifying the fill factor
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=ON)";


----Rebuild all indexes offline with specifying the fill factor
USE [DATABASE_NAME];
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=OFF)";

 

4: Updating database tables without causing blocking

UPDATE [TABLE_NAME] WITH (READPAST)
SET ...
WHERE ...

 


Learn Useful SQL Server Development Tips – Enroll to the Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More


 

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

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

 

4: Truncating a data/log file

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

 

5: Renaming a Windows login

ALTER LOGIN "[Domain or Server Name][Windows Username]"
WITH NAME="[New Domain or New Server Name][Windows Username]";

 

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)

USE [master];
GO
EXEC sp_addumpdevice 'disk', 'NetworkDeviceName','\\serverName\backupFolder\BackupFileName.bak';
GO

--Now you can backup databases onto the network device created above

 

E: Miscellaneous

Below, you can find other useful T-SQL tips.

 

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!

 

 

Upgrade your Tech Skills – Learn all about Azure SQL Database!

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub