Hot answers tagged

8

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level. In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and ...


5

Summary Add the index: CREATE INDEX index_name ON TableName (companyid DESC, id DESC) INCLUDE (active); Try this rewrite: SELECT COUNT_BIG(*) FROM ( SELECT TOP (9223372036854775807) TN1.active FROM TableName AS TN1 WHERE TN1.id = ( SELECT MAX(TN2.id) FROM TableName AS TN2 ...


2

will this script delete all tables with the prefix of Test_ on the database I execute it on? Yes, it will DROP tables. DELETE is normally used with deletion of data. Your script will physically drop the tables. Also, please dont use INFORMATION_SCHEMA and there is no need for a cursor. Your script can easily be written using dynamic sql. EDIT : In ...


2

Aaron Bertrand's comments copied as an answer: SQL Server just uses the actual number of bytes for storage, but take a look at your data size estimates - they'll be (half the declared byte size x number of rows) rather than (the average size x number of rows), and this can have a big impact on memory grants. Right-sizing is a good thing. These values can be ...


2

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages. This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to ...


1

If I understood your query correctly, the following query should produce the same result, but it may be more efficient, if you create an index on (companyId, id). Columns should be included in the index in this order. select count(*) from TableName tbl1 where tbl1.id = ( select TOP(1) tbl2.id from TableName tbl2 where ...


1

Your comment confirms that the original long script was using variables and these have become parameters. Probably you are benefitting from parameter sniffing here and better execution plans. The values for variables are not sniffed unless you use the ’option recompile' query hint so you will get the same plan regardless of what the runtime values are. ...



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