This tag is for SQL Server 2008, major build version 10.00.xxxx.
27
votes
4answers
8k 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 ...
27
votes
5answers
16k views
What are the main causes of deadlocks and can they be prevented?
Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that ...
26
votes
6answers
1k views
The smallest backup possible … with SQL Server
Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever.
We don't mind if our backup process takes a bit longer; as it stands ...
25
votes
5answers
2k views
Downgrading from SQL Server 2008 to 2005
Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
22
votes
4answers
813 views
Is there a “best-practices” type process for developers to follow for database changes?
What is a good way to migrate DB changes from Development to QA to Production environments? Currently we:
Script the change in a sql file and attach that to a TFS work item.
The work is ...
21
votes
9answers
20k views
Passing array parameters to a stored procedure
I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
21
votes
2answers
2k views
Find Queries that Are Causing Waits
Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server?
In this particular case I am wondering about async_network_io.
19
votes
7answers
4k views
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
I am writing the schema for a simple bank database. Here are the basic specifications:
The database will store transactions against a user and currency.
Every user has one balance per currency, so ...
18
votes
5answers
3k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
18
votes
4answers
8k views
How do I shrink all files quickly for all databases?
In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, ...
17
votes
4answers
3k views
SQL Server “empty table” is slow after deleting all (12 million) records?
I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
17
votes
3answers
2k views
Efficency of stored procedures vs raw queries
I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
17
votes
7answers
5k views
How do long columns impact performance and disk usage?
In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.
In reality, how much does it really matter? How ...
16
votes
3answers
12k views
How do I get back some deleted records?
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.
Is there any way to get back these ...
16
votes
2answers
1k views
Using EXCEPT in a recursive common table expression
Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion..
with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) ...
16
votes
3answers
23k views
How to change SQL Server collation
How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database?
Is there a way to do it using visual interface of SQL Server Management Studio? In ...
16
votes
4answers
857 views
Does the order of columns in a table's definition matter?
When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
15
votes
4answers
7k views
Task scheduler for SQL Server Express
I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database.
Unfortunately the ...
15
votes
2answers
19k views
How do I list or search all the column names in my database?
I want to search for a string in the names of the columns present in a database.
I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
14
votes
5answers
2k views
Possible attack on my SQL server?
Checking my SQL Server log I see several entries like this:
Date: 08-11-2011 11:40:42
Source: Logon
Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. ...
14
votes
3answers
1k views
Painless way to create a clustered index on a huge table?
So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
14
votes
3answers
888 views
Is it OK to blindly add missing indexes?
I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
14
votes
3answers
496 views
How to store 'n' days of web server logs in Sql Server?
For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time.
We have a daemon ...
14
votes
3answers
991 views
Dynamically Define a Range in a Dimension
I have an issue that i face every time i decide to build a cube, and i haven't found a way to over come it yet.
the issue is how to allow the user to define Range of things automatically without ...
14
votes
4answers
2k views
SQL large table design
I have a general question about SQL Server 2008 table(s) design. We currently have a table that is over 600GB and grows at about 3GB a day. This table has the appropriate indecies but is becoming a ...
14
votes
4answers
5k views
What is the best query to use to monitor a SQL Server database's status?
I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state.
This is the ...
14
votes
3answers
19k views
Recommended page file size for SQL 2008R2 on Windows 2008R2
This Microsoft article - How to determine the appropriate page file size for 64-bit versions of Windows Server 2008 and or Windows 2008 R2 provides guidance for calculating the page file size for 64 ...
13
votes
6answers
572 views
Why should an application not use the sa account
My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc.
I have an absolute belief that ...
13
votes
7answers
25k views
Need to migrate SQL Server to MySQL
I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the ...
13
votes
3answers
200 views
How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first?
Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute.
Your expert advice: How can I write the C# insert code for SQL ...
13
votes
2answers
334 views
Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?
I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
13
votes
5answers
2k views
SQL: What is slowing down INSERTs if not CPU or IO?
We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower ...
12
votes
4answers
522 views
The order of columns in a table
From what I've seen so far, when we define a table, normally we put the primary key as the first column. If there are miscellaneous columns that are common in all tables, they come at the bottom of ...
12
votes
3answers
4k views
How does shrinking a SQL Server log file affect performance?
I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
12
votes
1answer
202 views
ISO Week vs SQL Server Week
Okay so I have a report that does a this week vs last week comparison and our customer noticed that their data was "funky". Upon further investigation we found it was not doing weeks correctly ...
12
votes
1answer
728 views
What types of corruption can DBCC CheckDB miss?
This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following:
BACKUP 'BrokenDatabase' detected an error on page (1:123456) ...
12
votes
1answer
299 views
Best practices for connecting DBs that are in different geographical regions
We are about to set up SQL Servers in different countries. We need to link them, but we don't have to have a direct link (as in linked server). In other words, they can be loosely coupled.
Is it ...
12
votes
2answers
343 views
SQL Server 2008 Performance issue with very strange resolution
I'm a long time DBA (12 years in SQL). I'm running SQL Server 2008 Enterprise in a Win 2008 clustered environment. 128gb memory and 32cpu in each cluster node. Not wimpy.
I've got the weirdest ...
11
votes
2answers
1k views
Why do wildcards in GROUP BY statements not work?
I am trying to make the following SQL statement work, but I get a syntax error:
SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*
Here, A is a wide table ...
11
votes
4answers
2k views
Is ROLLBACK a fast operation?
Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?
11
votes
3answers
2k views
SQL Server 2008 R2 “Ghost Memory”?
We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit ...
11
votes
3answers
2k views
Schema design - best practices?
I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, ...
11
votes
4answers
524 views
Creating centralized DBA database
We have 200+ servers and I am thinking about creating a centralized DBA database where it logs backup, errors, space jobs and generate reports on SSRS. I am not sure where to start and I would highly ...
11
votes
1answer
1k views
Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?
I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks ...
11
votes
2answers
929 views
Deadlocks From Locks on Same Temporary Tables In Different Processes
I have found a deadlock that appears to show something I thought was impossible. There are two processes involved in the deadlock:
1. process8cf948 SPID 63
Performing an ALTER TABLE on temporary ...
11
votes
2answers
8k views
How to change the instance name of SQL Server Express 2008?
On my development machine I've installed SQL Server Express 2008, but it has the instance name .\SQLEXPRESS. I want it to be the default instance. I'm pretty sure that I selected the "Default ...
11
votes
3answers
1k views
Anybody using HierarchyId in production? Is it reliable?
Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
11
votes
3answers
847 views
Does the partition key also have to be part of the primary key?
I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
11
votes
3answers
580 views
Maximum table capacity in SQL Server 2008
I have an application which inserts more than 1 billion rows annually into a table. This table contains some varchar and bigint columns and one blob column as well.
The 1 billion rows consist of ...
11
votes
3answers
278 views
Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key
We have a large table [MyTable] which currently has both a PK, and a UNIQUE Index on [KeyColumn] (which has additional covering columns), which seems redundant. The table is clustered by another ...
