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), ' ')


In the past when I was creating sql statements there was many times I would need to have more than one subquery that was just slightly different than the first. However, now there is a better way to reuse a subquery in T-SQL and that is using a common table expression (CTE).

A common table expression (CTE) is defined by:


WITH cte_name_here AS
(
cte_query_here
)
outer_sql_query_that_uses_cte_name_here


You can provide aliases for the results of your common table expression by putting the aliases in the CTE query or in the CTE definition.


WITH mycte (CustomerNumber, InvoiceYear) AS
(
SELECT CustomerId, YEAR(invoicedatepaid)
FROM Invoice
)
SELECT CustomerNumber, InvoiceYear
FROM mycte ;


OR


WITH mycte AS
(
SELECT CustomerId AS CustomerNumber, YEAR(invoicedatepaid) AS InvoiceYear
FROM Invoice
)
SELECT CustomerNumber, InvoiceYear
FROM mycte;


You can use variables with common table expressions just like you can with sub queries. The example below will filter invoices just for the month of March.


DECLARE @StartDate AS DATETIME = '3/1/2014',
@EndDate AS DATETIME = '4/1/2014';

WITH mycte (CustomerNumber, InvoiceYear) AS
(
SELECT CustomerId, YEAR(invoicedatepaid)
FROM Invoice
WHERE invoicedatepaid >= @StartDate
AND invoicedatepaid < @EndDate ) SELECT CustomerNumber, InvoiceYear FROM mycte ;

Unlike subqueries, you cannot nest common table expressions directly, but you can build upon a previous one in sequence by referring to the previous CTE.


WITH mycte1 (CustomerNumber, InvoiceYear, RetailAmount) AS
(
SELECT CustomerId, YEAR(invoicedatepaid), RetailAmount
FROM Invoice
),
mycte2 (InvoiceYear, YearlyAmount) AS
(
SELECT InvoiceYear, SUM(RetailAmount)
FROM mycte1
),
SELECT InvoiceYear, YearlyAmount
FROM mycte2 ;


The best feature of a CTE is that you can refer to the same one more than once.


WITH mycte (CustomerNumber, InvoiceYear, RetailAmount) AS
(
SELECT CustomerId, YEAR(invoicedatepaid), RetailAmount
FROM Invoice
)
SELECT tab1.InvoiceYear, tab2.RetailAmount
FROM mycte tab1
inner join mycte tab2
where tab1.CustomerId = tab2.CustomerId
and tab1.InvoiceYear = tab2.InvoiceYear;


The benefit is that you can define the recordset once and not need to copy and paste it. This will help with maintenance later.