I'd change the collation first, since I think that will affect the compression. I believe the overall compression overhead, including I/O, would outweigh any advantages gained in I/O.

But those conditions within the WHERE clause could convolute the query plan and thus the performance.

Much better to set variables and have a simple and consistent WHERE clause, allowing SQL...

DECLARE @start_date datetime

DECLARE @end_date datetime

IF @parameter = 'GT30'

BEGIN

SET @start_date = '19000101'

SET @end_date = GETDATE() - 30

END --IF

ELSE

IF @parameter = 'LT30'

BEGIN

...

I'm virtually certain you'll get an error if the linked server isn't there.

You could use dynamic SQL, which will only be compiled if/when it's run.

As to recompiles, SQL will not...

If there's always at least one decimal place:

SELECT

value AS original_value,

SUBSTRING(value, 1, CHARINDEX('.', value) + 1) + '%' AS new_value

FROM (

...

The more disorganized the index, the more work space will be needed. Hopefully once you get the tables back in shape, the future runs won't be as bad.

Also, look...

PhilipC (9/16/2014)

---

ScottPletcher (9/16/2014)

---

I first look at SQL's usage stats (incl. row counts), operational stats and missing index info. Be aware they get reset when SQL stops and restarts, so...

CREATE NONCLUSTERED INDEX sampletable__IX_id ON sampletable ( id ) INCLUDE ( serialnumber...

Another option is to rename the column(s) then use "SELECT *" for output:

--CREATE TABLER #temp ...

--INSERT INTO #temp ... ...

DECLARE @sql varchar(8000)

SET @sql = 'EXEC tempdb.sys.sp_rename ''#temp.sale'', ''Sales' + CAST(YEAR(GETDATE())...

I first look at SQL's usage stats (incl. row counts), operational stats and missing index info. Be aware they get reset when SQL stops and restarts, so you get...

You could start with a url summary table and add a details table later if you need to.

I'd still strongly urge you to use an int to represent the url,...

Create an index::

keyed on : id

INCLUDE : serialnumber

Use quotes in the WHERE:

WHERE id IN ('1', '2', '3')

You can really get around that error directly, as SQL has control of master.

Back up the master db and restore it to a different name.

RESTORE DATABASE master_dbcc_checkdb

FROM DISK = 'x:\full\path\to\master.BAK'

Then...

You can have odd errors occur in that situation.

You're better off having a fixed, permanent owner for dbs and granting permissions as needed to other users, up to and including...

The second style would at least have a chance of being fully optimized, particularly if you force a recompile of the query. Stick with it.

I'd avoid the first style...