Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
12
votes
2answers
4k views
Why Does the Transaction Log Keep Growing or Run Out of Space?
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
What are some reasons the ...
16
votes
3answers
11k 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 ...
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, ...
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
9
votes
1answer
1k views
How to get the MAX row
In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability.
...
21
votes
9answers
18k 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 ...
12
votes
1answer
1k views
How do I attach a database in SQL Server?
Some common questions relating to attaching databases in SQL Server:
What does it mean to attach or detach a database?
How do I detach a database?
How do I attach a database?
What ...
12
votes
3answers
3k views
Using SQL Profiler on a database that's in production
As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems.
But I've always used it on my development ...
8
votes
1answer
879 views
Optimising BCP performance for BLOB data
I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
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 ...
15
votes
4answers
1k views
Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.
The database development started in ...
2
votes
3answers
786 views
Why is SSMS inserting new rows at the top of a table not the bottom?
Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
82
votes
14answers
8k views
How do you document your databases?
I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice I would like to know what tools/process you are using.
How ...
45
votes
4answers
4k views
Why use both TRUNCATE and DROP?
In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them.
Many of my colleagues (almost ...
25
votes
5answers
15k 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 ...
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 ...
10
votes
3answers
1k views
Determining how a schema change occurred?
Something bad happened yesterday.
A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this ...
6
votes
5answers
375 views
Over use/correct use of schemas?
Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise.
Basically, every object that I create is going into a schema with the schema name reflecting a ...
5
votes
1answer
312 views
The use of NOT logic in relation to indexes
According to MSFT's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development:
Neither leading wildcard characters not NOT logic allow the query optimizer to use ...
16
votes
3answers
2k views
Justify NOT using (nolock) hint in every query
Have you ever had to justify NOT using a query hint?
I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on ...
3
votes
1answer
174 views
Limiting database security
A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which ...
9
votes
3answers
2k views
Is it possible to restore sql-server bak and shrink the log at the same time?
We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
3
votes
5answers
2k views
Convert varchar column to datetime
In SQL Server, I have a varchar column with value such as:
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
QUESTIONS
How do I convert ...
14
votes
2answers
1k views
How to make a transition from SQL Server DBA to Oracle?
As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do?
I would assume the concepts are the same and the ...
42
votes
4answers
3k views
Why are numbers tables “invaluable”?
Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table:
USE Model
GO
CREATE TABLE Numbers
(
Number INT NOT NULL,
...
26
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 ...
12
votes
5answers
2k views
Why use an int as a lookup table's primary key?
I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).
I understand that using a ...
9
votes
2answers
5k views
What is a Clustered Index?
I need a short explaining of clustered index.
What is a clustered index?
What are best practices for using the clustered index?
7
votes
4answers
1k views
Help me choose a RAID level combination for a SQL Server 2008 instance
I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.
I am going to make new RAID configuration. I have 6 SCSI ...
6
votes
3answers
808 views
Testing stored procedure scalability
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
11
votes
3answers
2k views
Find out what queries are causing the biggest amount of network traffic
On a production SQL server I am seeing intermittent enormous spikes in data traffic. Up to 200Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what ...
6
votes
2answers
1k views
How to create an Event Notification that runs a Job/procedure when mirroring state changes
I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL?
Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to ...
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 ...
9
votes
1answer
611 views
SQL query for combinations without repetition
I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n.
Extended sample input and result so input ...
8
votes
2answers
1k views
What is blocking and how does it happen?
I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
5
votes
1answer
738 views
Trouble With Database Permissions for sp_send_mail
I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows:
SELECT SUSER_NAME(), ...
5
votes
2answers
874 views
'Conversion failed' error with ORDER BY CASE expression
I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'.
I get the following error:
Conversion failed when converting the nvarchar value 'SK11270' to ...
4
votes
1answer
2k views
SQL Server: hierarchy of permissions for schema?
We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is ...
3
votes
3answers
780 views
What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?
Many times I need to write something like the following when dealing with SQL Server.
create table #table_name
(
column1 int,
column2 varchar(200)
...
)
insert into #table_name
execute ...
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
6
votes
3answers
8k views
Restore SQL Server 2012 backup to a SQL Server 2008 database?
Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008?
I tried to attach file, it does not work.
6
votes
3answers
228 views
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
5
votes
4answers
10k views
Restoring a backup to an older version of SQL Server
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:
Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
...
3
votes
1answer
371 views
Running queries with SQLCMD vs. Running queries with SSMS
I've noticed something which i'm not sure about. can it be that running queries directly with SSMS is much faster than running queries with SQLCMD? I've noticed that the results of queries that ran ...
2
votes
1answer
221 views
Shrinking log file
Some of the sql log files (idf) are hugh like 5gb. Found a article (http://extended64.com/blogs/rhoffman/archive/2009/06/11/shrink-sql-server-2008-database-log-file-script.aspx) but not sure why he ...
2
votes
1answer
979 views
Is SQL Server Backup single or multi-threaded?
If you perform a...
BACKUP DATABASE [DB_Name] TO DISK=N'c:\backups\db_name.bak' WITH STATS=10
Will that perform a single or multi-threaded write?
Our Server support company advised us that if we ...
0
votes
1answer
100 views
Restoring a differential backup in SQL server 2005
I have created a full back up of my database by executing the line below in my application:
Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & ...
0
votes
3answers
7k views
SQL Server 2008 R2 High memory usage
I have SQL Server 2008 R2 that runs multiple jobs over night, the problem is SQL Server will eat up all RAM and CPU, then it will not release the RAM resources after the job finishes, as a workaround ...
-3
votes
1answer
210 views
Why does a VIEW with a CLUSTERED index not use the index?
I have a view that has a clustered index along with other non-clustered indexes.
A simple SELECT query does not use any of the indexes even when I select an indexed column.
What could be the ...
27
votes
5answers
2k views
Which database could handle storage of billions/trillions of records?
We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in ...
