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).
15
votes
2answers
833 views
As a DBA, how would I go about transitioning from Oracle to SQL Server?
I'm an Oracle DBA that also has Sybase experience.
What are the major architectural and conceptual differences between the two RDBMS platforms?
An answer similar to the SQl Server->Oracle question ...
41
votes
7answers
1k views
Does SQL Server read all of a COALESCE function even if the first argument is not NULL?
I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:
...
29
votes
9answers
2k views
How can I track database dependencies?
As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods ...
17
votes
5answers
27k views
How to the get current date without the time part
In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
13
votes
2answers
658 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
569 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 ...
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 ...
3
votes
1answer
197 views
sql server database sharding - what to do with common data / non sharded data
We have a very large scale enterprise level database. As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very ...
16
votes
3answers
4k views
When should I use a unique constraint instead of a unique index?
When I want a column to have distinct values, I can either use a constraint
create table t1(
id int primary key,
code varchar(10) unique NULL
);
go
or I can use a unique index
create table t2(
id ...
13
votes
1answer
2k views
Why is using a table variable more than twice as fast as a #temp table in this specific case?
I was looking at the article here
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
8
votes
1answer
295 views
Behavior of data in indexes based on fill factor
Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?
From my understanding, when the data is inserted there ...
8
votes
4answers
674 views
What tools are there to generate test data for SQL Server?
As you can see from another question of mine, generating test data is my theme right now.
At this point, I'm still generating my test data by hand. However, this process always generates small ...
6
votes
3answers
150 views
Nonclustered Index Insert
Say I have a table like this:
create table SomeTable
(
id int identity(1, 1) not null primary key clustered,
SomeString1 varchar(50) not null,
SomeString2 varchar(50) not null
)
go
...
6
votes
2answers
1k views
SQL Server Join/where processing order
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
5
votes
1answer
2k views
SQL Server Deadlock Graph - Table, Page or Row Lock?
Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
17
votes
4answers
1k views
What are the consequences of setting varchar(8000)?
Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server?
On create table, if ...
11
votes
3answers
654 views
Is there any benefit to defragmenting SQL indexes in a SAN environment?
Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.
We have been running Ola Hallengren's index maintenance scripts weekly, and ...
9
votes
3answers
3k views
What can cause a mirroring session to timeout then failover? SQL Server 2005
We have two production SQL Servers running SQL Server 2005 SP4 with cumulative update 3. Both servers run on physical machines that are identical. DELL PowerEdge R815 with 4 x 12 core CPUs and 512GB ...
9
votes
6answers
1k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
9
votes
4answers
5k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
8
votes
1answer
245 views
DELETE vs TRUNCATE
I am trying to get a greater understanding on the differences between the DELETE and TRUNCATE commands. My understanding of the internals goes something along the lines of:
DELETE -> the database ...
8
votes
2answers
490 views
What is the meaning of DOP in the context of SQL Server?
What is the meaning of DOP in the context of sql server?
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
7
votes
2answers
3k views
Difference Between Row Level and Page Level Locking and Consequences
When attempting to run my Maintenance Plan, I receive the following error:
Executing the query "" failed with the following error: "The
index "" (partition 1) on table "" cannot be reorganized
...
7
votes
1answer
380 views
Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?
Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered ...
6
votes
5answers
551 views
Inclusion of ORDER BY on query that returns no rows drastically affects performance
Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant ...
6
votes
2answers
303 views
Code creates different plan when ran ad-hoc vs. in a stored procedure
I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.
I have rebuilt all the indexes for the tables used by the ...
6
votes
4answers
610 views
SQL Server: what should be done when a process is blocking another process?
I'm an accidental DBA and still learning.
When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?
Should I simply kill those processes or is ...
6
votes
3answers
2k views
Why is a .bak so much smaller than the database it's a backup of?
I just took a backup of a SQL Server database. The MDF and LDF files together total around 29 GB, but the .bak file was only 23 GB, about 20% smaller.
My first guess when one version of a set of ...
5
votes
2answers
580 views
SQL Server gets slow over time until we have to restart it
We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
5
votes
3answers
670 views
Passing in the table into a stored procedure
Is it possible to pass in the name of a table into a stored procedure?
For example, suppose you have several views of the same table. They all have the exact same structure.
You want a store ...
5
votes
1answer
554 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
121 views
Transaction Log maintenance on Mirror database
SQL Server Version: 2008 R2 Enterprise SP2
I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
4
votes
1answer
178 views
How to Optimise Query
I have a database structure similar to this,
CREATE TABLE [dbo].[Dispatch](
[DispatchId] [int] NOT NULL,
[ContractId] [int] NOT NULL,
[DispatchDescription] [nvarchar](50) NOT NULL,
...
4
votes
5answers
2k views
Pros/Cons Using multiple databases vs using single database
Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented.
While I don't agree I'm having trouble collecting good ...
3
votes
1answer
101 views
Analysing A Query Plan
Why is the following query slow?
select count(*)
from [dbo].[mt_dispatch_link]
, [dbo].[_mt_dispatch] [_mt_dispatch]
where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
...
3
votes
3answers
890 views
Test if any fields are NULL
I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.
I need something like
SELECT * FROM ...
3
votes
5answers
1k views
Recommendations for MS SQL Server monitoring software or service?
I have a client with MS SQL Server instances (2000, 2005) scattered across 20-30 servers. I've been engaged to get things organized and will be periodically reviewing the state of the database ...
2
votes
0answers
81 views
How to Audit for Access with Cross Database Ownership Chaining
I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
2
votes
4answers
914 views
How to move data from big table
There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?
2
votes
2answers
1k views
Problem Reaching VM SQL Server Instance
Here's what I'm working with right now. I have a host OS which includes a SQL Server 2008 Enterprise instance. I have a virtual machine running on this host which has a SQL Server 2008 R2 Express ...
0
votes
1answer
43 views
Migration to databases Domain Users do not have access
I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
0
votes
1answer
2k views
Set permissions on all objects for SQL users
I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
-1
votes
2answers
440 views
help in multi-language medical database?
My project is to design a medical database for many countries.
This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language.
I started my ...
17
votes
3answers
2k views
Performance difference for COALESCE versus ISNULL?
I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
13
votes
2answers
328 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 ...
9
votes
4answers
793 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
8
votes
2answers
469 views
INT or CHAR for a Type Field
What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema:
create table Car
(
Name varchar(100) not null,
Description varchar(100) not ...
8
votes
2answers
2k views
Unique Constraints on Nullable Columns in SQL Server 2005
In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered ...
7
votes
1answer
202 views
Hard limitation for merge replication article count?
A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
