Wednesday, December 14, 2011

Useful T-SQL Knowledge

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 '[DomainName\UserName]';

--10: Backing up a Database in a Network Folder (creating the destination media)
USE [master];
EXEC sp_addumpdevice 'disk', 'NetworkDeviceName','\\serverName\backupFolder\BackupFileName.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: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
  • Authentication: Windows Authentication

* Make sure that the Named Pipes protocol is enabled!

I hope you found this article useful!

Cheers!
[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: