This tag is specifically for the 2008 version of Microsoft SQL Server.
6
votes
3answers
792 views
Is it better to populate variables using SET or SELECT?
What is the better way (with regards to performance) to set a value to variable?
By SET command:
DECLARE @VarString nvarchar(max);
SET @VarString = 'john doe';
SELECT @VarString;
By SELECT ...
6
votes
1answer
9k views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
6
votes
1answer
1k views
Deadlock error isn't returning the deadlock SQL
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am getting this error randomly when one of my ...
6
votes
1answer
202 views
SQL Server 2000 to 2008 to 2012
We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to ...
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
872 views
SQL Server 2008 R2 High CPU
We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3.
Our database works very hard (we run around 600,000 queries in 1 minute) and ...
6
votes
3answers
1k views
Why is this Full Outer Join not working?
I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join.
I have 2 tables (which ...
6
votes
2answers
168 views
Meeting with DBAs regarding server upgrade project - Common concerns
I'm more of a network/windows admin and I've been tasked with overseeing a SQL server upgrade project. I need to meet with the DBAs and discuss their needs / wants regarding the upgrade. I don't want ...
6
votes
2answers
4k views
SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller?
Ok, to start with, I screwed up when I created the databases, using a create script roughly like so: (artificial linebreaks and names/paths for wrapping purposes)
CREATE DATABASE [EXAMPLE] ON ...
6
votes
1answer
182 views
Why does sql server need to convert count(*) result into int before comparing it with an int variable?
I have many queries in my application where in the having clause, I have comparison of count aggregate function with int variable.
In the query plans, I can see an implicit_convert before the ...
6
votes
1answer
266 views
CATCH block is triggered when it should not
On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
6
votes
2answers
875 views
how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?
I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than ...
6
votes
2answers
2k views
SQL Disk Setup Advice - TempDB, Log DB, Data file placement question
We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all ...
6
votes
1answer
139 views
How to shred .docx XML?
I am trying to import a xml (actually a docx file) to a sql server 2008 database. I am almost a novice in XML programming. I googled a lot but almost all the examples there are with simple xml file. ...
6
votes
1answer
275 views
Attempting to tune a Sharepoint site using SQL Server Waits and Queues
I'm pretty new to this performance tuning with Waits and Queues thing - fascinating, but also not always all that intuitive...
Right now, a customer of mine has a SQL Server 2008 64-bit Enterprise ...
6
votes
3answers
331 views
What happens to non clustered index when included column is updated using update statement?
Question on non clustered index with included columns (DB - MS SQL Server).
I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is ...
6
votes
1answer
493 views
Are there any performance benefits to using a hash table with no clustered index?
I have this table:
CREATE TABLE [dbo].[relatea] (
[mid] [varchar](16) NOT NULL,
[sid] [varchar](16) NOT NULL
)
It stores hash matches. Is there any benefit to having this as a heap? It has ...
6
votes
1answer
10k views
SQL Server 2005/2008 UTF-8 Collation/Charset
I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin ...
6
votes
3answers
678 views
non-clustered unique indexes on sql server partitions
SQL Server 2008 requires unique non-clustered indexes created on a partition to include the partition field in the index definition.
I'm wondering why.
6
votes
2answers
183 views
SQL Server Internal Inconsistency on GROUP BY
I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS.
Now I can select records from a table-valued function to retrieve sales records, but using a ...
6
votes
1answer
1k views
Add DEFAULT to existing column
In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?
6
votes
2answers
203 views
Why would a SELECT INTO cause an arithmetic overflow?
I am doing this
SELECT * INTO table1 FROM Table0
I get an arithmetic overflow on a datetime column (error converting to smalldatetime), however the destinations schema that got created has a ...
6
votes
2answers
377 views
Using 100's of schemas in a database SQL2008R2?
We are in SSAS type of environment with 100's of clients.
Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being ...
6
votes
1answer
1k views
SQL Server nonclustered index with INCLUDED columns and their order
I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order.
If I run the following query:
select
col1,col2
from ...
6
votes
1answer
95 views
What happens behind the scenes when adding a nullable column in SQL Server?
This question came up today. When you add a column that accepts null values to the end of a table what exactly happens? Does the table get fully rebuilt similar to what happens when you change column ...
6
votes
2answers
343 views
SQL Server database : moving from 2008 to 2005 - encrypted procedure issue
I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.
Neither do I have older ...
6
votes
1answer
146 views
Is it recommended to clear the query plan cache
Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation.
After making the changes to the code, ...
6
votes
3answers
1k views
Group by minute and two minute intervals
I have a datetime column and I can easily run queries with a group by on my datetime column. However, I would like to run group queries for
1-minute intervals
2-minute intervals
5-minute intervals
1 ...
6
votes
2answers
233 views
Why select max partitioned column is much slower than indexed one?
I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity.
However the following ...
6
votes
1answer
352 views
Mirroring problems after removing domain
I successfully created a mirror with certificates between two servers. One of those servers was a domain controller. After removing the domain the mirror was interrupted.After hours of searching i ...
6
votes
1answer
2k views
Database Mirroring with TDE
I have the requirement of mirroring a few databases and also using transparent data encryption (TDE) on them as our data must be encrypted while 'at rest'.
I have setup TDE on both the principle and ...
6
votes
1answer
177 views
How do I optimize large table so queries that target only recent data perform optimaly?
So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved.
I am open to ...
6
votes
2answers
400 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
1answer
230 views
Can I “Execute T-SQL Statement Task” against all user databases?
I'm setting up a maintenance plan on Sql Server.
I want to add an Execute T-SQL Statement Task to the plan, however I do not see any way to set this task to run in every [user|system|all] database as ...
6
votes
1answer
904 views
OPTION FORCE ORDER improves performance until rows are deleted
I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds.
...
6
votes
2answers
739 views
sp_executesql with user defined table type not behaving correctly
Problem
Is there a known issue with user defined table types as parameters to sp_executesql? Answer - no, I'm an idiot.
Set up script
This script creates one each of table, procedure and user ...
6
votes
2answers
483 views
Database locking issues?
I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
6
votes
2answers
2k views
Listing the existing SQL Server Logins and Users
I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script.
I ran the query below but it shows Principal_id which ...
6
votes
2answers
156 views
Workaround to importing data
I am trying to import data into a SQL Server. I can import through the Import and Export Data wizard. I cannot import from my machine using BULK IMPORT or OPENROWSET since the file is not on the ...
6
votes
1answer
256 views
Is there a way to DENY CRUD operations on connections from linked server?
I have a SQL Server instance, RPT1 (2008 R2), that was set up for "power users" to run their own queries. This server is linked to our production reporting server, PRD1 (SQL Server 2008 instance), ...
6
votes
1answer
395 views
Dependant T-SQL comparison collation
Is there any way to set the collation for a string comparison based on an independent variable or column value? Something akin to
SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
...
6
votes
3answers
412 views
Inserting and updating and selecting at thousands of times per second
I have a table that gets inserted, updated and selected from thousands of times within one second. I am having deadlock issues though.
The database has 2-5 simultaneous 1000+ row inserts using Linq ...
6
votes
1answer
200 views
How to trace SQL queries which are crashing SQL Server
We have a SQL Server 2008 database server (it happens to be running under MS Failover Clustering, but I don't think that is relevant here).
Our application runs Hibernate for DB access, and since we ...
6
votes
1answer
280 views
computed columns, index, clustered index and covering index?
We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time.
...
6
votes
1answer
489 views
How to prevent daily index fragmentation of 99%
I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
6
votes
3answers
761 views
Performance issue with xpath in SQL Server 2008
I have a table with lots of large xml-documents.
When I run xpath expressions to select data from those documents I run into a peculiar performance issue.
My query is
SELECT
p.n.value('.', 'int') ...
6
votes
1answer
275 views
How long can the log reader agent run for before a restart will do it some good?
As a developer who has for the first time successfully implemented a replication topology for our production database, I'm monitoring it very closely and fretting over potential problems.
I'm pleased ...
6
votes
1answer
180 views
Landed as BI, but databases are a big WTF, what to do?
Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
6
votes
1answer
553 views
SQL Server, remove all users' access right to a table object
As a clean up job, I am trying to remove all users' (including non public users) access right to a single table object (apart from superusers) and will subsequently grant new access rights. Is there a ...
6
votes
2answers
645 views
How does using separate schemas affect SQL Server 2008 performance?
I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
