All Questions
Tagged with sql-server index
953
questions
0
votes
0
answers
46
views
Not able to drop non-clustered index
I was executing a query to create a non clustered index in one of the table and that has around 43M of rows. It runs more than 1 hour, then I felt something is wrong and have cancelled the query ...
- 581
1
vote
1
answer
86
views
What is the impact on an index when I am widening a key varchar column
One of the central tables in my database has a varchar(25) column named EmployeeID.
This column is used within a compound index and is likely the most used index in our system.
We got an enhancement ...
- 163
9
votes
1
answer
574
views
Does Ordering of Columns in INCLUDE Statement of Index Matter?
I was reading through the comments of this answer on another question, and it got me wondering if the ordering of columns in the INCLUDE clause of an index (on SQL Server specifically but also any ...
elahren
3
votes
3
answers
466
views
Enforce non-unique combination of columns, where combination is required
I have the following (and more non-related) columns in my MSSQL database table: Id, ImageUrl and ImageId.
The combination of ImageUrl and ImageId can occur multiple times. ImageUrl belongs to ImageId ...
- 141
-1
votes
1
answer
109
views
Identify which index's rebuild operation is causing the log file to grow
We have a database, in full recovery model, which goes under index rebuild maintenance once a week. There is an index which is causing the log file to grow. I have log file growth event alerts enabled,...
- 1,550
1
vote
5
answers
239
views
Why do Databases use HEAP to store table data while other structure like B-tree exists?
Database Engines like MS SQL Server , PostgreSQL etc uses HEAP to store table data while using B-tree to store index data. Time complexity for most operations in B-tree is O(log n) while for HEAP it ...
- 168
4
votes
1
answer
233
views
Unique index on computed column when contributing columns are already unique
I have a table like this:
CREATE TABLE
dbo.DiscountBarcodeList
(
ID int NOT NULL IDENTITY
CONSTRAINT PK_DiscountBarcodeList
PRIMARY KEY CLUSTERED
, Discount int NOT NULL
CONSTRAINT ...
- 23.1k
0
votes
2
answers
189
views
Does rebuilding/reorganize an index in SQL Server free up space in the DB file due to compacting data pages?
If I have an index with a default fill factor of 100%, and modifications result in a large amount of internal fragmentation (page splits, free space, etc.) so that many pages end up around 60% full ...
- 69
1
vote
2
answers
560
views
How can I tell if a full-text index is being used?
It's possible to tell how much a rowstore index is used, but what about full-text?
I could find nothing in any GUI or DMV. Even dbatools doesn't support it.
I've inherited a server that has a few full-...
- 545
0
votes
1
answer
46
views
Is there ever a reason to create a non-clustered index on a column that is already the clustered primary key?
Suppose that I have a table. Its primary key is clustered and consists of exactly one column. Is there ever a reason to also make a non-clustered index on that same column with no other columns in any ...
- 545
0
votes
1
answer
93
views
Is there any advantage in using IdentityColum+AnotherColumn vs just IdentityColum as the Clustered Index?
The database where I work has Table A with column A_id (an autoincrement field, which in practice is always unique, never null), and column Date (not unique), and other columns.
For some reason, the ...
- 121
1
vote
1
answer
58
views
What indexes should I put on this table, given this illustration of its contents and the query I will be running on it?
I am working with a table (which I control) that is being populated by another application (which I don't control), each time a record of a particular type in an external system is created or updated. ...
2
votes
2
answers
713
views
Why are these columns specified in the include clause of an index rather than as key columns
For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with ...
- 69
1
vote
1
answer
288
views
Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?
I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I ...
0
votes
2
answers
121
views
Does the logical query processing change when indexes are involved?
I highly recommend this book.
This book explains in a great way the behavior of a sql query, but my doubt is,
How is a sql query processed when there are indexes, that is, would this behavior change?
...
- 1