meta user
network profile
| bio | website | |
|---|---|---|
| location | United Kingdom | |
| age | ||
| visits | member for | 1 year, 7 months |
| seen | 49 mins ago | |
| stats | profile views | 1,398 |
Email: stackoverflowmartinsmith@gmail.com
|
1d |
comment |
Filter on a window function without writing an outer SELECT statement What is the motivation for the question? |
|
2d |
comment |
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? Don't know why the question was migrated until the requested info was supplied. |
|
2d |
comment |
Is there a way to generate an execution plan for a stored procedure before executing it? @WileCau - Yes it is cached. Though if you generate the plan from SSMS it still may never be used by your application anyway as various settings need to consistent across both connections for plans to be shared. The execution plan tells you what the CompileTime is. What does it say in your case? |
|
2d |
comment |
Is there a way to generate an execution plan for a stored procedure before executing it? In 2008 ThomasStringer's suggestion would work but in 2012 it won't cache the plan generated that way. @Wile I don't see how generating the plan in advance would help anyway even if it actually does take significant time to compile. It would still be the same total time just split into two separate operations (rather than having SQL Server automatically compile the plan then execute it immediately afterwards as one operation) |
|
May 13 |
comment |
Change SQL Server settings without restarting services Which specific settings? |
|
May 9 |
comment |
Table Constraint causing deadlock This constraint is broken anyway. eg 1 or eg 2. You can achieve the same more efficiently and reliably with CREATE UNIQUE INDEX iX on [grid]([row_id],[column_id]) WHERE [is_valid] = 1 if you are on 2008+ or using an indexed view for previous versions. |
|
May 7 |
comment |
Is there a difference in performance between @date and getdate()?GETDATE is a runtime constant and will not in fact be called many times. In the case of the OP as they are using the expression intermingled with a column reference I'm not sure it will actually effect the cardinality estimates. |
|
May 7 |
comment |
What is the meaning of this anomaly warning message? Specifically looks as though it takes that as an indicator the table may not be in first normal form then. |
|
May 6 |
comment |
Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2? If you've never taken a backup, and your recovery model is BULK LOGGED or FULL then actually your recovery model is to all intents and purposes the same as simple. See When is FULL Recovery not Really FULL Recovery |
|
May 4 |
comment |
Why disabling a clustered index makes the table inaccessible? I'm not really sure why SQL Server even allows you to do this. CREATE TABLE T(X INT CONSTRAINT PK PRIMARY KEY CLUSTERED, Y INT CONSTRAINT UQ UNIQUE NONCLUSTERED);ALTER INDEX PK ON T DISABLE also disables the NCI so even SELECT queries that would be covered by that are disabled. I can't think of any use case for this. |
|
May 4 |
comment |
Why disabling a clustered index makes the table inaccessible? The leaf level of the B+ tree is the table. What are you hoping to achieve by disabling the CI? Just don't do this if you don't want the data to be inaccessible. |
|
Apr 12 |
comment |
What could cause the wrong ID to be inserted? Only if the table is newly created or has been truncated. If you insert a row and delete it you get a different result CREATE TABLE dbo.[table] (id int identity(1,1));INSERT INTO dbo.[table] DEFAULT VALUES;DELETE FROM dbo.[table];dbcc checkident('dbo.table',reseed,0);INSERT INTO dbo.[table] OUTPUT inserted.* DEFAULT VALUES;DROP TABLE dbo.[table] |
|
Apr 11 |
comment |
Query requires 'Update statistics' very often in SQL Server This is a known issue with ascending date columns. For a 5 million row table you would need to insert 1 million rows (20%) before the statistics would be auto updated. See the answers here. The Microsoft response on this Connect Item looks intriguing though. |
|
Apr 11 |
comment |
Concatenation Physical Operation: Does it guarantee order of execution? I was on 2008, Just tested on 2012 and see same as you. On 2008 SELECT 1 WHERE EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)<> 1 UNION ALL SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)= 1) gives the same plan as the original query despite the order of the conditions being reversed |
|
Apr 9 |
comment |
Concatenation Physical Operation: Does it guarantee order of execution?SELECT 1 WHERE EXISTS(SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)= 1 UNION ALL SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)<>1) does process the inputs in opposite order from written (presumably cheapest one first?) but does not seem to use UNIAReorderInputs |
|
Apr 6 |
comment |
I need help in Nested Cursors in SQL server Better to just show us the table structures, example data and desired results so we don't have to reverse engineer your requirement. |
|
Apr 3 |
comment |
Concatenation Physical Operation: Does it guarantee order of execution? The results in the test here indicate that it might run in parallel. It would be possible to trace page locks taken to test this hypothesis but I haven't got the time to do that myself. |
|
Apr 3 |
comment |
Unexpected Table Scan with Parameterized LIKE That is a variable not a parameter. The values of variables are not sniffed except if you use OPTION (RECOMPILE). Both of these queries use a seek. sqlfiddle.com/#!6/d41d8/3298 |
|
Apr 1 |
comment |
Estimated Execution Plan SQL Server Sort? Can you let us know the result of the following query? pastebin.com/1RWAHFZK Interested to see how many rows are going into the final join. That is a triangular join on a 70,000 row table with a residual predicate. If thousands of rows are going into the join this could easily be very expensive. |
|
Apr 1 |
comment |
Estimated Execution Plan SQL Server Sort? @RichardC - That is the estimated plan. Can you upload the actual plan? |
