This tag is for SQL Server 2008, major build version 10.00.xxxx.
11
votes
3answers
535 views
Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?
SQL server 2008 R2 query optimizer puzzle
We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same.
This is fast, 13 seconds,
select * from bigtable1
...
10
votes
2answers
1k views
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
10
votes
4answers
4k views
Should dbo schema be avoided?
When it comes to the dbo schema:
Is it a best practice to avoid using the dbo schema when creating database objects?
Why should the dbo schema be avoided or should it?
Which database user should own ...
10
votes
2answers
354 views
@table_variable or #temp_table
I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get ...
10
votes
3answers
1k views
Why might a table's data space take up 4x the size of the raw data?
I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row. The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT. The average length of the text in ...
10
votes
4answers
3k views
When to use XML data type
I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the ...
10
votes
3answers
455 views
Executing same request from C# VS SSMS give different execution time
I have a request like this one
SELECT
[EstimateId],
[CreationUserId],
[EstimateStatusValueId],
[LanguageId],
[LocationId],
[EstimatorUserId],
[FilterUnitSystemTypeId],
[EstimateNumber],
...
10
votes
3answers
397 views
Is there a way to determine if SQL Server queries are running in memory or going to disk?
I came across a set of stored procedures in an application today that are called repeatedly within a long running process. Within each procedure I found multiple different select statements, some ...
10
votes
2answers
1k views
Can I move rows between partitions by updating the partition key?
I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.
The question:
Can you move rows of data within a partitioned table from ...
10
votes
2answers
504 views
How to avoid using variables in WHERE clause
Given a (simplified) stored procedure such as this:
CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
SELECT
-- Stuff
FROM Sale
...
10
votes
1answer
482 views
What exactly does “No Join Predicate” mean in SQL Server?
MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".
But unfortunately it does not seem to be as easy as that.
For example, very ...
10
votes
1answer
529 views
Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?
The three SELECT statements in this code
USE [tempdb];
GO
SET NOCOUNT ON;
CREATE TABLE dbo.persist_test (
id INT NOT NULL
, id5 AS (id * 5)
, id5p AS (id ...
10
votes
4answers
5k views
SQL Server statements intermittently slow on SQL Server 2008 R2
On one of our customers, we've been having some performance issues on our application. It's a .NET 3.5 web app that is consuming and updating data on a SQL Server database. Currently our production ...
10
votes
3answers
544 views
What's the most effective way to duplicate an instance on SQL 2008?
We've got a simple database instance with just enough data in the databases to keep it useful, and we can use it for demonstration purposes, and I want to put a copy of it on my laptop so I can use it ...
10
votes
1answer
995 views
What is the impact of changing the index creation memory setting in SQL Server?
I am trying to create some indexes a SQL Server 2008 database but am getting the following error message.
Msg 8606, Level 17, State 1, Line 1
This index operation requires 1024 KB
of memory ...
9
votes
7answers
16k views
Moving tables to another SQL2008 database (including indexes, triggers, etc.)
I need to move a whole bunch (100+) of large (millions of rows) tables from one SQL2008 database to another.
I originally just used the Import/Export Wizard, but all the destination tables were ...
9
votes
5answers
416 views
SQL Server Backups - A couple of questions
We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at ...
9
votes
2answers
472 views
Why does SELECT 1/2 return 0?
As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0.
I am using SQL Server 2008.
9
votes
5answers
5k views
Save Results as CSV *with* headers in SSMS
With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in ...
9
votes
5answers
1k views
SQL Server command line usage? Is it possible?
I've been trying to use the SQL Server Management Studio 2008's built in "Solution Explorer" to manage a project and after 2 weeks of struggling with it, I've decided I don't really care for it. It's ...
9
votes
3answers
1k views
How to change the firing order of Triggers?
Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every ...
9
votes
3answers
834 views
SQL Server 2008/R2 recovery model
Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the ...
9
votes
5answers
613 views
UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL)
I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID ...
9
votes
3answers
1k views
SQL Profiler tutorials for newbie
The MSDN documentation tough to understand.
Are there any online tutorials available to learn SQL Profiler? If possible, videos or simple blog posts would be preferred.
9
votes
2answers
460 views
A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?
We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.
I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
9
votes
4answers
689 views
Rebuilding Indexes, DB now 10x the size
I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very ...
9
votes
2answers
283 views
Decision criteria on when to use a non-dbo schema vs a new Database
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
9
votes
1answer
1k views
Empty Strings: Why or when is '' equal to ' '?
Who can explain why
select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');
yields
----------- ----------- ----------- ----------- -----------
1 ...
9
votes
2answers
7k views
How to find the SQL statements which caused the tempdb grew huge?
The tempdb of an server (SQL Server 2008) increases to 500+G several times every month. Is it possilbe to find out which SQL statements cause the problems? (The problem is usually not caused by create ...
9
votes
2answers
231 views
How many Sproc Parameters is too many?
I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
9
votes
3answers
838 views
Updating a table with millions of records, its been 4 days
I am currently updating a table with millions of records, its been 4 days and query is still executing.
I checked the activity monitor its shows that query is running.
In event log there is no ...
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 ...
9
votes
3answers
12k views
How do I assign an entire Active Directory group security access in SQL Server 2008?
I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
9
votes
1answer
3k views
Varchar(Max) field cutting off data after 8000 characters SQL Server 2008
I have a field to store some data, the field is declared as varchar(Max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
9
votes
1answer
2k views
How to find corrupt page in SQL Server 2008
I know I can execute a DBCC CHECKDB and get a status of my database.
Questions
How can I find if there is some corrupt data page into my database?
If an error is throwndue to page corruption, where ...
9
votes
2answers
1k views
How large is “too large” for SQL 2008 Web Edition?
For our soon-to-be-hosted application I need to choose between SQL Server 2008 Web edition and SQL Server 2008 Enterprise edition. There isn't any "must-have" functionality: the application currently ...
9
votes
2answers
158 views
SQL SERVER Storage of TinyInt
In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask.
USE tempdb ;
GO
CREATE TABLE tbl
...
9
votes
1answer
1k views
How do you ORDER BY a parameter?
Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!).
Basically I have ...
9
votes
2answers
1k views
Restore bak file to smaller mdf and ldf database files
I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have:
MyDatabase.mdf: 24.8GB
MyDatabase.ldf: 114.6GB
This ...
9
votes
2answers
188 views
Is there a set based way to load/read a tree branch with HierarchyId
I am playing around with HierarchyId, and I have not figured out a set based way to do the following:
insert all subtree at once
retrieve all subtree at once
This question is related to my ...
9
votes
1answer
236 views
Does SQL Server 2008 store the creation date of execution plans?
We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to ...
9
votes
4answers
880 views
What is the best way to monitor long running queries in SQL Server?
I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database. Is there an alternative to running the query in SQL Server Management Studio and ...
9
votes
2answers
319 views
Is data retrieved from SQL Server compressed for transmission?
Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it?
I'm examining analysis ...
9
votes
1answer
2k views
Are there any risks to granting users SQL Server SHOWPLAN permission?
I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
9
votes
1answer
806 views
When should you specify PAD_INDEX?
So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it ...
9
votes
1answer
319 views
Slot Array and Total Page Size
I continue to read in many forums and on many blogs that a page is comprised as shown below:
Page Size: 16 x 512B = 8192B
Page Header: = 96B
Maximum In_Row Row: = 8060B
This leaves ...
9
votes
1answer
449 views
SQL Server 2008 R2 Dirty reads - how non-atomic?
I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but:
Can a row appear as ...
9
votes
1answer
395 views
query optimization: time intervals
In the main, I've got two kinds of time intervals:
presence time and absence time
absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
9
votes
1answer
489 views
Scalar function used in calculated column, what's the cleanest way to update the function?
I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the ...
8
votes
5answers
933 views
Do empty columns take up space in a table?
I have table that holds from very basic info. Just a title and a few date fields.
There's one field called comments which is varchar(4000)
Most of the time we leave it blank, but some times will enter ...
