Tagged Questions

25
votes
2answers
2k views

Are SQL Server in-place upgrades as ill advised as they used to be?

I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade. I'm currently upgrading my 2008 R2 DEV and TEST ...
24
votes
4answers
7k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
1
vote
3answers
1k views

Cannot start SqlLocalDB instance with my Windows account

I'm the administrator and simply run the command: sqllocaldb start v11.0 Result: Start of LocalDB instance "v11.0" failed because of the following error: Error occurred during LocalDB instance ...
13
votes
2answers
633 views

Why does DELETE leave a lingering effect on performance?

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the ...
13
votes
2answers
535 views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ...
5
votes
1answer
547 views

Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here. I always thought that I could trust that identity values ...
4
votes
1answer
60 views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
4
votes
1answer
218 views

Mirroring and Availability Groups in SQL2012

I've been looking at the new features of SQL 2012, and how it can help my current situation. Currently we're on SQL 2008R2, and replicate two OLTP servers' databases to a single reporting server via ...
0
votes
2answers
254 views

SQL Server 2012 Data Warehousing Overall Flow [closed]

I am studying Data Warehousing in SQL Server 2012 I am attempting to learn the various tools included by practicing on the different tools separately. I have practiced with: AdventureWorks ...
9
votes
1answer
685 views

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
6
votes
3answers
333 views

What is a good, repeatable way to calculate MAXDOP on SQL Server?

When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's ...
6
votes
2answers
382 views

SQL Server 2012 Availability Group is it “AlwaysON”?

In a traditional SQL Server clustering when failover occurs, all client that are connected to SQL Server failover instance looses the connection and each client must reestablish a new connection to ...
6
votes
3answers
273 views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
2
votes
4answers
520 views

limiting sql server backups inside .bak file

I have a job scheduled to run a few times everyday. The job backups my databases, like: BACKUP DATABASE [databasename] TO DISK = N'd:\sqlserver\backup\databasename.bak' I can track the backups in ...
0
votes
0answers
72 views

How to create table set from several XSD in SQL Server 2012 or with VS 2012

Is there any solution in T-SQL or in C# how to create XML schema collection in T-SQL or C#? Problem is I cannot use: USE Database_test GO CREATE XML SCHEMA COLLECTION Collection_from_XSD AS N' ...
0
votes
1answer
74 views

How can I install a provider for linked server?

I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database. I would like to use this same ...