Tally table in SQL

I had a situation where I had to write out to a file where the  column3 ( string)  should be split and write to the next line if the length was greater than 50 . I used Tally table to do that.

WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
)

,cte AS
(
SELECT
  v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50  + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Explanation ---

select * from cteTally
V
-----
1
1
1
1
1
1
1
1
1
1
Select * from cte
v
----
1
51
101
151
201
.
.
.
.
4951
(100 rows because of cross join)
finally use the place holder row value in the sub string

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Another easy to understand example that I found on http://blogs.inkeysolutions.com/2011/05/creating-tally-tables-using-cte-in-sql.html
Generating a sequence of numbers from 1 to 20
DECLARE @Max AS INT = 20


;WITH CTE AS (
  SELECT 1 Num
  UNION ALL
  SELECT Num + 1 FROM CTE WHERE Num < @Max
)


SELECT * FROM CTE
 



----Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

---Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

Changing Default Database File Locations in SQL

I missed to specify the default location for the data file and the log file while installing SQL SERVER . So when I created a database and did not specify the default location for the files it will be created on the location defined in the server settings. So if we want  always the data files and log files to be created on a particular location we can
change the server properties through SSMS - > Database Settings -> Database default locations .

However this requires the sql instance to be restarted for the changes to take effect.