This article covers a few, more advanced topics about the usage of clustered indexes in SQL Server. Not only will I try to convince you of the absolute necessity of using clustered indexes, I'll also give you some tips on how to use them in a not so obvious context.
2006-01-04
5,922 reads
This SQL script returns a recordset with all the index information for all tables for all databases in a SQL server(instance)
2006-10-30 (first published: 2005-10-07)
2,678 reads
This SQL script returns a recordset with all the index information for all tables within a database
2005-10-18 (first published: 2005-10-07)
871 reads
Through this script we can fetch on regular index information with whole database. Here i am use temporary tebale concept show no procedure so its run quickly and also fetch information from production database in uptime. No sytem generate index information not send me.
2005-08-09 (first published: 2005-07-27)
208 reads
This is a simple script which would list sp_helpindex information for all the tables in the database. You dont have to provide any table name or anything. Just run it on the database from which you want to retrieve the information.This is helpful when you have several servers running the same form of DB, and […]
2005-08-10 (first published: 2005-07-26)
194 reads
SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.
2005-05-19
24,457 reads
This is a stored proc that will move your indexes to two new file groups. A Cindex file group is for the clustered indexes and NCindex file group is for non clustered indexes. The parameters for this procedure are @CPATH - the path to the clustered index datafiles @NCPATH - the path to the nonclustered […]
2005-04-29 (first published: 2005-04-15)
395 reads
We’ve all seen reindex procedures that rebuild all indexes in a database or all indexes in a database which have less than a specified ScanDensity. This one goes a few steps further. First, the DBA can specify a ScanDensity_Threshold (a min value for ScanDensity), plus a Limit (qty or %). If you specify a ScanDensity_Threshold […]
2005-06-14 (first published: 2005-03-31)
1,852 reads
When it comes to auditing index use in SQL Server databases, I sometimes get overwhelmed. For example, how to do you go about auditing indexes in a database with over 1,500 tables? While auditing a single index is relatively straight-forward, auditing thousands of them in multiple databases is not an easy task. Whether the task is easy or not, it is an important task if you want to optimize the performance of your SQL Server databases.
2005-01-18
3,860 reads
There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.
2004-12-23
3,412 reads
By HeyMo0sh
Working in DevOps, I’ve seen FinOps do amazing things for cloud cost control, but...
Every organization I talk to has the same problem dressed up in different clothes....
By DataOnWheels
I am delighted to host this month’s T-SQL Tuesday invitation. If you are new...
Comments posted to this topic are about the item The day-to-day pressures of a...
Hello all, I’m looking for advice on how to derive a daily snapshot table...
We need to replace our Windows server running SQL 2017. Any reason not to...
I have some data in a table that looks like this:
BeerID BeerName brewer beerdescription 1 Becks Interbrew Beck's is a German-style pilsner beer 2 Fat Tire New Belgium Toasty malt, gentle sweetness, flash of fresh hop bitterness. 3 Mac n Jacks Mac & Jack's Brewery This beer erupts with a floral, hoppy taste 4 Alaskan Amber Alaskan Brewing Alaskan Brewing Amber Ale is an "alt" style beer 8 Kirin Kirin Brewing Kirin Ichiban is a Lager-type beerIf I run this, what is returned?
select t1.[key]
from openjson((select t.* FROM Beer AS t for json path)) t1 See possible answers