August 19, 2016 at 1:14 pm
I came across a partitioned table that only contains non-clustered index where the partitioned column is on the index. I had the impression that partitioned tables require a primary key and that the partitioned column must be in the pk. I guess that's not the case, or the tables and procedures wouldn't have compiled, but I hope someone can confirm it.
Thanks
August 20, 2016 at 12:00 pm
BY DEFINITION, a table has to have at least one key. Your narrative is for a deck of punch cards written in SQL. Exactly what is this data?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 20, 2016 at 2:10 pm
The type of data and the quality of the queries was not on my question. Apparently if it is BY DEFINITION, then SQL Server has a bug, because it does allow for partitioned tables without primary key.
August 20, 2016 at 3:15 pm
In relational theory, something isn't a table until it has a key. In SQL Server, tables without keys are perfectly valid, and you can partition a heap with no indexes without a problem.
If a partitioned table has a primary key, iirc the primary key must have the partition column in it, but not having a pk on a partitioned table is perfectly legit.
Don't mind Joe, he yells at people with the intention of making them learn the stuff so they don't get yelled at as much. 😉
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
August 20, 2016 at 7:16 pm
Back in the 1980'2, when I was on ANSI X3H2, the question of whether or not to require a primary key in the table came up in the committee. Back in those days, SQL systems were based on existing file packages, which meant punch cards and magnetic tapes with a little indexing. There was no hashing to speak of, nor columnar databases nor parallel processing nor any the other things that you sort of take for granted these days (like indoor plumbing).
Did you ever wonder why the primary key is special? That at Hawaii actually has a separate syntax? That is because punch cards and tapes have to be in sorted order to work on those old filesystems. Later, Dr. Codd would allow us to have have many keys and all keys are created equal (they are all made up of not null columns and their combination is unique for each row of their table). No key is "more equal" than another key.
What we were worried about in the early days of SQL was migrating old files to the new technology. We thought about having a CREATE FILE or CREATE SOMETHING syntax in the language but decided to just keep the CREATE TABLE. This way we could put constraints on a table and fix it up after the fact without re-writing code.
Partitioning is not part of the ANSI/ISO standards, nor should it be. This is a physical storage method, and can vary from vendor to vendor. If you want to have some fun create a table with hundreds of thousands of identical rows, and then partition it. Kinda useless ain't it? Let your product partition your table by state code; one partition for California one partition for Guam, etc. I have to have some way of not putting the same customer in California and Guam at the same time. This is where key on a customer identifier is useful.
To be frank about it, when this issue came up on the committee, we never thought anybody would be sloppy enough to have a table without a key. But we thought they might inherit legacy (a.k.a. family curse) code. But surely nobody would keep it around longer than a week or two before cleaning it up!
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply