4

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code: DECLARE @cmd nvarchar(max) ; SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL ' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS , db = ''' + d....


4

The best thing I have found to help in big index creation is to (1) have enough RAM, (2) have a slow time before the index creation starts, and (3) perform a SELECT...INTO of the index fields into a temp table with an ORDER BY of the desired index order right before creating the index. This can speed the process by up to 75% in some cases (which I have not ...


3

The only idea I can think of is if I maintained a copy of the database on a separate server where I can make DDL changes, then re-point my applications to that server. Instead, consider just building a new table, and loading it incrementally from your existing table (things like Change Tracking or even Triggers can help here). Then during your short ...


3

It's explained very well in the documentation. A useful property of WITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query ...


2

You can see that your second query has Rows Removed by Filter: 18562, while at an average, the first query has Rows Removed by Filter: 1875532. If you run 50 individual queries, the optimizer will optimize each of them individually, and in the cases where only few rows satisfy the filter condition, it will probably choose a different and better execution ...


2

The type of statistics are cardinalities i.e. a count of the number of rows that have a certain value for a particular attribute. To take a simple example, say I have a table with one column that is one character long: create table T(c1 char(1)); This table contains the values A, A, A, B, C, F, K, X, X, Z. The cardinality will then be a count of the ...


1

Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light. You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.


1

This index will take care of 3 of your queries: INDEX(title, date, -- in either order category) -- last Then you will need two more indexes to handle the other two queries. Assuming you use the order above, then these could be the other two. INDEX(category) INDEX(date) If you have a dozen columns and users can filter on virtually any ...


Only top voted, non community-wiki answers of a minimum length are eligible