In SQL Server, data is stored in a B-Tree structure. If you are not familiar with a B-Tree you may want to take a look at Wikipedia for more details. As you can see in the example of the image below (Copied from the Wikipedia entry referenced above), not every node is completely filled in with values. The less dense […]

Today I wanted to talk about writing queries so that they are maintainable in the long run. There are two things that I don’t like to see when looking at a select statement. The first thing you have heard and read over and over, don’t use SELECT *. When you do, * is a variable that has to be resolved […]

I write about many different topics for www.SSWUG.org. Every once in a while I bring up topics that are of interest to our readers, and the next thing I know, companies I may have referenced are now following me on Twitter, Facebook, or LinkedIn. Notifications of the company following me happen very quickly after the editorials are posted. This weekend […]

Today I am closing out the week with a comment from Tim: Just to add an alternative spin on this argument… Firstly, we work quite often in an agile development methodology, which practically means that the customer’s requirements are "a bit fluid". That often means that we cannot establish a definitive schema or data model in either the code or […]

Today we conclude with some final thoughts regarding Ravi’s question about Clustered Indexes. As most of our readers already know, each table in SQL Server may have up to one clustered index. This restriction is due to the fact that a clustered index is a b-tree structure, and that the data of a table is physically stored within the structure […]

Today we continue to interact with a question on Clustered Index posted by Ravi. He writes: I would want to know your thoughts on the clustered indexes. I would create my clustered index a) on a transactional table column/columns which is heavily queried upon, I would prefer it to be a numeric and sequential most of the time and does […]

While considering the different options for primary keys, and the validation of Composite Keys Ravi asks the question regarding how indexing may work. He writes: I would want to know your thoughts on the clustered indexes. I would create my clustered index a) on a transactional table column/columns which is heavily queried upon, I would prefer it to be a […]

Today Tai adds his insight to our composite key example. He brings up 4 points which I interact with inline. My intention is not to discount the thoughts of others, but to add some counter point and stimulate thought and discussion. 1) The article doesn’t take the amount of data in the table as a factor. Surely, we don’t want […]

Aaron and I have ended up with a bit of a conversation regarding composite keys. With his permission I am including more of that conversation today as a followup from the editorial yesterday. Aaron: Thanks for considering my opinion yesterday. I think one of the other issues I’ve found with composite keys is the composite key needs to be included […]

Aaron has a different perspective on the value of always having an ID column as primary or surrogate key on every table. He writes: I do take issue with having only a composite key in place instead of an identity column. Why? Because writing queries against tables that have composite keys of 4 columns, is a total pain in the […]