New answers tagged

3

Unfortunately I don't think there is a proper StackExchange for theoretical questions, but hopefully you gather good enough information here Typically the "Phonebook" makes for a good analogy of how indexes work, not only because of the pages sorted by Names (i.e. the nodes of the B-Tree) but the sorting of the LastName, FirstName of the individual ...


1

The WHERE clauses refer to t, so it is very likely that the Optimizer will start with t in each SELECT. You have the optimal indexes for them. Then it needs to reach into the other two tables (merchants and customers) and get 1 (or 0) row from them. Those tables have the optimal index for the JOIN, namely PRIMARY KEY(id) in each case. (The FKs do not play ...


1

Do you need all 20 columns from transaction_events? If not, then getting rid of * and specifying only the columns you need not only reduces the amount of data you're pulling back at one time but also reduces the chances of a sub-optimal query plan. It's possible the query plan generated will vary based on the columns in your SELECT clause. You could test ...


0

I have used the built-in function DBCC CHECKIDENT to reset the identity value (surrogate key which used to create unique index) on the table and the DW is working fine now. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15


0

Looks like you are trying to insert a row in your table with value for the column : TraceBackFinishedProductSK. In the definition of your table you have defined it to be unique and identity as well as an auto incrementing integer starting with lowest value of -2147483648 and max value 1. That means you do not need to insert data with this column, it will get ...


3

RID Lookups occur on a heap data structure in SQL Server (as opposed to a B-Tree). This occurs when a non-covering nonclustered index is used to fetch the data and it needs to lookup the remaining fields it's missing. Your table data is stored in a heap when there is no clustered index on that table (as the clustered index defines the ordering the records ...


6

It seems you are too much concerned about fragmentation, As long as you keep updating statistics regularly, fragmentation shouldn't bother you much for performance. You may read more details about this on a video shared by Mr. Brent Ozar and also another page here. Let me try answering your question one by one: Doesn't an Index with included columns have ...


7

Your questions: Doesn't an Index with included columns have the exact same problem? Yes. Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems? Yes Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id? I would, yes. How to prevent fragmentation? There are a couple of ...


Top 50 recent answers are included