Posts

Showing posts with the label T-SQL

Why table can have only one clustered index? No it is not what you think.

Image
Recently, I have been on an interview spree to get a DBA on board in our team. And this was one of the question I asked to nearly everyone. No, not only because this is my favorite question but also because it is a one of the litmus test that help me estimate how clear a DBA is about the core concepts of RDBMS. Problem How many clustered indexes can table have and why? Or. Why a table can have only one clustered index? Below is the most common answer I got for these question: “Because we can have only one primary key on the table”. Well, that answer is a big NO. Yes, NO THAT IS NOT THE CORRECT ANSWER. Surprised, well I was too when I use to hear that answer. Yes, it is correct we can’t have more than one primary key on a table but that is due to its definition and functional requirement. And that’s why by default SQL Server provides functionality of creating a clustered index along with it. My obvious next question to them was “Can we create a primary key without a cluster...

SQL Server Statistics and smart way to update it

Anybody who has worked with SQL Server databases whether be it administrator or developer, knows the significance of having “ statistics ” on a table and how it greatly impacts SQL query performance. Below are some excerpts about “ statistics ” from SQL Server documentation . “ Statistics for query optimization are binary large objects (BLOBs) that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The Query Optimizer uses these statistics to estimate the  cardinality , or number of rows, in the query result. These  cardinality estimates  enable the Query Optimizer to create a high-quality query plan. ” “ Statistics ” is a complex amalgamation of histograms and densities that helps SQL Server Query optimizer correctly estimate number of rows it may fetch and process for each operator in order to generate an optimal or near optimal execution plan . Estimation plays huge role in how much time eac...