Let's pretend you've got the white pages of the phone book - remember, that thing Grandpa kept by the fridge so he could call his friends from the war. It's organized by last name, first name.
If I asked you to get that phone book and read out the names:
SELECT FirstName, LastName FROM dbo.PhoneBook
You would usually read them out to me in last name order....
If you need order in your query results, put in an ORDER BY. It’s that simple.
Check out this article from SQL Server architect Conor Cunningham which pretty much sums this topic up:
No Seatbelt – Expecting Order without ORDER BY
One possible scenario that very much amuses me:
The rows were originally written when the database didn't have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled
RCSI or SI was enabled, or the database was added into an Availability Group
During the deletions, a 14-byte timestamp was added to the deleted rows to ...
In SQL Server the clustered index key column(s) are always added in to the non clustered index to act as a row locator (Ref: More About Nonclustered Index Keys).
For an NCI declared as unique they are added as an included column otherwise they are added to the end of the key.
You might want to add the columns in explicitly if the default placement is not ...
If I let the server decide which index to use, it picks IX_MachineryId, and it takes up to a minute.
That index is not partitioned, so the optimizer recognizes it can be used to provide the ordering specified in the query without sorting. As a non-unique nonclustered index, it also has the keys of the clustered index as subkeys, so the index can be used to ...
The cost model used by the optimizer is exactly that: a model. It produces generally good results over a wide range of workloads, on a wide range of database designs, on a wide range of hardware.
You should generally not assume that individual cost estimates will strongly correlate with runtime performance on a particular hardware configuration. The point ...
Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free to present data to you in any order it finds most convenient at the time, which can mean a different order for the same query you ran earlier.
If there is a ...
If the subquery in that update consistently uses those two predicate values, a filtered index should help a lot. Something like this (which Erik Darling kindly provided as a comment):
CREATE INDEX IX_ntID_ResponseWindow_Includes ON dbo.Notifications (ntID, ResponseWindow)
INCLUDE (NotificationType, Status)
WHERE (Status = 'Done' AND NotificationType = '...
Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion:
WHERE [serialNumber] = 137802
According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of ...
You need to be very careful what you read on the interwebs ;-) (of course, that also goes for this answer or pretty much anything anywhere, but still). Just as there is a lot of good information out there, there is also a lot of misinformation (and sadly, this is not confined to technical info). And people copy and paste / repost / share both. So, it is good ...
how to stored the actual IP address - in text or bytes format. Which is going to be better?
Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither.
Given the following information (from Wikipedia article on IPv6):
The 128 bits of an IPv6 address are represented in 8 groups of 16 bits ...
Yes it could, when SQL Server decides that the statistics from that index is more accurate/useful and uses that stats to do the estimates and come up with a plan.
I have come across situations when SQL Server has decided to use stats from one index and scan/seek another index.
Edit - This might not be applicable cause I just realized that you have ...
Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does.
Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online ...
As an alternative to @AaronBertrand's solution (if you can't or don't want to create an indexed view), I would recommend you to create an index on (Enroll_Date, UserID). If this type of question is very common on your table, this should probably even be your clustered index.
I would not generally recommend high-selectivity indexes as a general "best ...
With a good index present, the time taken to locate a matching row should scale roughly logarithmically, as long as you have room for the index in memory.
I'd make the index UNIQUE since the basename must be unique otherwise your workflow is invalid, and it makes the index more efficient.
CREATE UNIQUE INDEX IX_raw_records_basename
ON dbo.raw_records (...
Nonclustered indexes contain a row locator back to the base table.
This is a clustered index key for rowstore tables with a clustered index or a physical RID (file/page/slot) for heaps.
So the parts in your question assuming that it uses the primary key (in the event that these are different) are moot.
The row locator is added to the key for non unique ...
Sounds like an ideal scenario for an indexed view, which allows you to pay for calculations and aggregates at write time instead of query time.
CREATE VIEW dbo.MyIndexedView
SELECT Enroll_Date, UserID, RawCount = COUNT_BIG(*)
GROUP BY Enroll_Date, UserID;
CREATE UNIQUE CLUSTERED INDEX CIX_miv ON dbo....
Aarons answer is a great solution. I'll answer the question assuming you don't want to take that approach.
The query that you posted will usually be executed by first grouping on (Enroll_Date, UserID), then again on (Enroll_Date). This optimization is new to SQL Server 2012. It takes effect in case of a single COUNT DISTINCT.
An index on those two columns ...
I wrote a post about this kind of situation here, which goes into more depth than this answer.
is there any way to say in index definition to include only first 800 characters from the column?
This can be done by creating a computed column as LEFT(MyColumn, 800), and then indexing that column. Note that the column doesn't have to be materialized in the ...
The reason for this is that the "fixed" physical location of your row - the RID (or row identifier) might (and will!) change over time - think page splits that occur when a row needs to be inserted into a table on a page that's already full.
Updating those RIDs in all the nonclustered indices that exist on a given table is quickly becoming both a hassle, ...
They don't tend to be shown in ERDs. An ERD focuses on the Entities and their Relationships, but an index is a copy of the data from one (or potentially more) of the entities, created to assist in the execution of queries. While it's possible that an index could be unique and therefore contribute to the database design, they are not typically shown on ERDs.
You see different plans because from SQL Server 2014 there is a new cardinality estimator in SQL Server. And then they added some new features to SQL Server 2016 for the the new CE.
First some test data to reproduce what you see.
create table dbo.T(C1 char(10) default '', C2 varchar(11));
insert into dbo.T(C2)
select top(800000) row_number() over(...
If you were to compare the number of reads required in 100,000 lookups with what’s involved in doing a sort, you might quickly get an idea about why the Query Optimizer figures that the CIX+Sort would be the best choice.
The Lookup execution ends up being quicker because the pages being read are in memory (even if you clear the cache, you have a lot of rows ...
Why does SQL Server use the clustered index plus a sort algorithm instead of using a non-clustered index even if the execution time is 38% faster in the latter case?
Because SQL Server uses a cost-based optimizer based on statistics, not runtime info.
During the cost estimation process for this query, it does actually evaluate the lookup plan, but ...
This is the name of the filegroup or partition scheme that the index is created on. This can be specified when creating an index with a second ON clause.
The sp_help procedure calls sp_helpindex which retrieves the name from sys.data_spaces
The primary filegroup contains the primary data file and any other
files not specifically assigned to another ...
I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values.
The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid the ...
A non-clustered index scan may be chosen in this scenario:
the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans
the non-clustered index is "skinnier" than the clustered index
the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...
SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree.
In a DML heavy workload there can be many small tree adjustments called page splits. These ...