Master.dbo.SysColumns

Question

Master.dbo.SysColumns

Nunnery

Ten Centuries

Points: 1293
More actions
December 29, 2009 at 3:01 pm

#218992

I was reading the article on the "Numbers" or "Tally" table (http://www.sqlservercentral.com/articles/T-SQL/62867/) and see this used in a query. I tried to do some research on what exactly this table is as I have seen it used elsewhere as well but I haven't been able to turn up much on it. If someone could direct me to some more information on this it would be greatly appreciated. Thank you.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's the syscolumns view in the master database. syscolumns used to be a table in SQL 2000 it's now a view and it's one of the meta-data views. There's a syscolumns in each database.

If you want to know more, look in Books Online under syscolumns. There's a full page on the view in there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

check this out for tally table uses.

http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/

"Keep Trying"

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

corey_nunnery (12/29/2009)
I was reading the article on the "Numbers" or "Tally" table (http://www.sqlservercentral.com/articles/T-SQL/62867/) and see this used in a query. I tried to do some research on what exactly this table is as I have seen it used elsewhere as well but I haven't been able to turn up much on it. If someone could direct me to some more information on this it would be greatly appreciated. Thank you.

As well as what the others have stated, it's simply being used as a source of rows to "iterate" over using the cross-join. Master.dbo.SysColumns in guaranteed to have at least 4,000 rows in it even on a brand new system in SLQ Server 2000. On SQL Server 2005, it's no longer a table... it's a "legacy view" and will have at least 11,000 rows in it even on a brand new system.

4k*4K = up to 16 million rows can be generated by the Tally table creation code because of the crossjoin.

11k*11k = up to 121 million rows can be generated by the Tally table creation code because of the crossjoin.

Many folks try to increase both of those by doing more than one cross-join. If you do that, it will cause some nasty growth on the database if you exceed the number of rows in one of the cross joins because it will temporarily materialize the rows in the log file. I did a multi-cross join experiment to create a billion row Tally table... the database size grew from 1GB to over 40GB.

Use Itzik's cascaded CTE method if you need more rows than what a single cross join will create. When I used that method to create the billion row table, the database grew only by what was necessary to hold the billion row table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff - 40GB? Wow.

Good info in this thread.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Yep... not a misprint.

What was worse was when I tried to add a clustered index. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nunnery Ten Centuries Points: 1293 More actions · Answer 6

Thank you all for the insight on this. You have been very helpful.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Thanks for the feedback, Corey.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

9423t8 Mr or Mrs. 500 Points: 507 More actions · Answer 8

Jeff Moden (12/30/2009)
As well as what the others have stated, it's simply being used as a source of rows to "iterate" over using the cross-join.

OK, I admit I'm probably being very very dumb here, but I don't see the word JOIN anywhere in Jeff's code, and I don't understand where the heck this CROSS JOIN is allegedly coming from, or why it works at all without the word JOIN in it?

I don't recall ever having written any T-SQL query which joins tables without typing the word JOIN in it (e.g. JOIN or LEFT OUTER JOIN), so Jeff's SELECT statement looks like smoke and mirrors from where I'm sitting.

Can anyone explain, please?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

It's the old-style join syntax. These two are equivalent:

...

FROM TableA, TableB

...

FROM TableA CROSS JOIN TableB

... as are these two:

...

FROM TableA, TableB

WHERE TableA.ID = TableB.ID

...

FROM TableA INNER JOIN TableB

ON TableA.ID = TableB.ID

John

9423t8 Mr or Mrs. 500 Points: 507 More actions · Answer 10

Well I never! I genuinely didn't know that.

John: you're a star, and thanks.

Clearly my SQL education has been so Prim and Proper that I have never ever been introduced to that "method" of joining tables!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

John Mitchell-245523 (4/12/2016)
It's the old-style join syntax. These two are equivalent:
...
FROM TableA, TableB
...
FROM TableA CROSS JOIN TableB
... as are these two:
...
FROM TableA, TableB
WHERE TableA.ID = TableB.ID
...
FROM TableA INNER JOIN TableB
ON TableA.ID = TableB.ID
John

Those latter two in that aren't equivalent to the first. The latter two create a 1:1 join between the table and itself and you'd end up with only the number of rows present in the table. An equivalent to the CROSS JOIN would be...

FROM TableA a

JOIN TableB b

ON 1 = 1

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Jeff Moden (4/12/2016)
John Mitchell-245523 (4/12/2016)
It's the old-style join syntax. These two are equivalent:
...
FROM TableA, TableB
...
FROM TableA CROSS JOIN TableB
... as are these two:
...
FROM TableA, TableB
WHERE TableA.ID = TableB.ID
...
FROM TableA INNER JOIN TableB
ON TableA.ID = TableB.ID
John
Those latter two in that aren't equivalent to the first.

Which, I don't think is what Michael is saying. He's saying that 1 and 2 are equivalent, and that 3 and 4 are equivalent. Two different examples of 'old' and new style joins.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

cad.delworth (4/12/2016)
Well I never! I genuinely didn't know that.
John: you're a star, and thanks.
Clearly my SQL education has been so Prim and Proper that I have never ever been introduced to that "method" of joining tables!

If you're interested in learning more on that subject, there are other types of joins similar to a CROSS JOIN (also known as a "Square Join") that can get folks into a bit of trouble simple due to missing criteria or the wrong criteria ("Triangular Joins" are almost as frequent as "Square Joins" as problems that cause TempDB to blow up). Some of the examples use the old style non-Ansi method and are also done using the Ansi method because they're actually pretty easy to miss in both styles. Here's the link.

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

GilaMonster (4/12/2016)
Jeff Moden (4/12/2016)
John Mitchell-245523 (4/12/2016)
It's the old-style join syntax. These two are equivalent:
...
FROM TableA, TableB
...
FROM TableA CROSS JOIN TableB
... as are these two:
...
FROM TableA, TableB
WHERE TableA.ID = TableB.ID
...
FROM TableA INNER JOIN TableB
ON TableA.ID = TableB.ID
John
Those latter two in that aren't equivalent to the first.
Which, I don't think is what Michael is saying. He's saying that 1 and 2 are equivalent, and that 3 and 4 are equivalent. Two different examples of 'old' and new style joins.

Ah. Got it. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)