Useful T-SQL Knowledge

Useful T-SQL Knowledge

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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

–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];
ALTER LOGIN “LOGIN_NAME” WITH DEFAULT_LANGUAGE = us_english;

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.


— B: Basic string functions

–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

–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 …


— D: 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];
DBCC SHRINKFILE ([Data_Log_LogicalName],[TargetMBSize]);

–4: Truncating a data/log file
USE [DBName];
DBCC SHRINKFILE ([Data_Log_LogicalName],TRUNCATEONLY);

–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];
EXEC sp_change_users_login ‘Auto_Fix’, ‘[UserName]’, NULL, ‘[Password]’;

–8: Changing the Database Owner in a SQL Server Database (SQL Login)
USE [DBName];
EXEC sp_changedbowner ‘[SQL_Login_Name]’;

–9: Changing the Database Owner in a SQL Server Database (Windows Login)
USE [DBName];
EXEC sp_changedbowner ‘[DomainNameUserName]’;

–10: Backing up a Database in a Network Folder (creating the destination media)
USE [master];
EXEC sp_addumpdevice ‘disk’, ‘NetworkDeviceName’,’\serverNamebackupFolderBackupFileName.bak’;
–Now you can backup databases onto the network device created above


— E: Miscellaneous

–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!

I hope you found this article useful!

Cheers!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.