Often when I am working with large amounts of content stored in a database, there will be more than 2000 characters. Many SQL Server databases have varchar(max) columns which will store large amounts of content, but SQL Server Management Studio (SSMS) will only return the first 2000. In order to get all the data from a VARCHAR(MAX) or Text Column, you can’t use the default grid result pane of SSMS. In order to return all the data from a varchar(max) column, the best thing to do it to use XML Path.


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
DECLARE @S varchar(max)
SELECT @S = ''
SELECT @S = @S + LargeTextColumnStoredAsVarchar FROM dbo.MyTableNameHere
SELECT @S AS [processing-instruction(x)] FOR XML PATH('')
ROLLBACK


The result will be the full content from LargeTextColumnStoredAsVarchar.

I have been personally impacted by this as I copied the content of a result set from SQL Server Management Studio (SSMS) that I assumed was all the data, but in fact, it was only a small part. Next time, remember to use XML Path to get all the content.

One of the most common queries I need to write is to remove rows with spaces only usually caused by bad historical data or trim out the spaces.

A simple view is to show the difference in length when leaving all spaces (first example), removing outer spaces (second example) and removing all spaces (third example).

Please note that I used # around the variables. This is a great way to see spaces which may not look very obvious, especially if it is only one trailing space.


BEGIN TRANSACTION
DECLARE @MyName AS VARCHAR(50)
SET @MyName = ' David Middle Name Hall '

SELECT '#' + @MyName + '#' AS ColumnWithDataAndSpaces,
LEN(@MyName) AS ColumnWithDataAndSpacesLength

SELECT '#' + LTRIM(RTRIM(@MyName)) + '#' AS ColumnWithDataNoOuterSpace,
LEN(LTRIM(RTRIM(@MyName))) AS ColumnWithDataNoOuterSpaceLength

SELECT '#' + REPLACE(@MyName,' ','') + '#' AS ColumnWithData,
LEN(REPLACE(@MyName,' ','')) AS ColumnWithDataLength

ROLLBACK


Another solution is to update the value to NULL if the record is only spaces. This can be done by triming the value and checking the length is less than 1.


BEGIN TRANSACTION

DECLARE @MyName AS VARCHAR(50)

SET @MyName = ' '


SELECT LEN(LTRIM(RTRIM(@MyName))) AS CleanRecordLength,
CASE WHEN LEN(LTRIM(RTRIM(@MyName))) < 1 THEN NULL ELSE LTRIM(RTRIM(@MyName)) END AS CleanRecord

SET @MyName = ' Dave Hall '


SELECT LEN(LTRIM(RTRIM(@MyName))) AS CleanRecordLength,
CASE WHEN LEN(LTRIM(RTRIM(@MyName))) < 1 THEN NULL ELSE LTRIM(RTRIM(@MyName)) END AS CleanRecord

ROLLBACK


I recently came across the problem of how to get the identity value of records INSERTED. I found out the solution is using OUTPUT, which is really powerful.

My issue was that I was trying to insert data into MyTable1 from MyTable2, where I didn’t know what the value was after the insert into MyTable1.

The solution was the following code:

INSERT INTO MyTable1( Column2, Column3, Column4)
OUTPUT INSERTED.Column1
SELECT Column2, Column3, Column4
FROM MyTable2 WHERE Column2 = 'Unique-Value-Here';


Another option is that you can create a table variable and hold the content to be used later.


DECLARE @MyOutputTable TABLE (Column1 INTEGER);

INSERT INTO MyTable1( Column2, Column3, Column4)
OUTPUT INSERTED.Column1 INTO @MyOutputTable(Column1)
SELECT Column2, Column3, Column4
FROM MyTable2 WHERE Column2 = 'Unique-Value-Here';


This way you can look up the identity values stored in the table variable for more than 1 record at a time.

When I have to generate a report to output to a CSV file or text file, I sometimes have a problem with the carriage return. The following is my tip to replace the carriage return in SQL with a space.

Just keep in mind that when replacing the new line in a SQL statement or SQL script, you need to handle CR, LF and CR+LF. To replace the new line command with space, use the following:

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:


select REPLACE(REPLACE(@mystringhere, CHAR(13), ' '), CHAR(10), ' ')