SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

9
votes
4answers
622 views

How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
9
votes
3answers
2k views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
9
votes
1answer
12k views

Handling exceptions in stored procedures called using insert-exec blocks

I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The ...
9
votes
3answers
26k views

SQL Server NTFS allocation unit size

On Windows 2008 R2 running SQL Server 2008 R2 how imporatant is the NTFS allocation unit size on DISK IO performance. It appears to me that server admin who built the few servers for a mission ...
9
votes
1answer
7k views

How and when does SQL Agent update next_run_date/next_run_time values?

I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific ...
9
votes
2answers
37k views

How to create Sql Server login for a domain account?

All, I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I ...
9
votes
1answer
5k views

SSIS 2008 Package Configurations being ignored

With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
9
votes
1answer
3k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
9
votes
3answers
2k views

How recording of every change of a row in a database is generaly stored?

In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
9
votes
2answers
856 views

Should I add page level compression before adding the primary key, or after?

Situation Data warehouse on Sql Server 2008 Enterprise 36+ million row heap (don't ask), with 60+ columns 750k added monthly No primary key defined (I have now identified one) No compression What ...
9
votes
4answers
2k 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
12k views

How to apply a theme to SQL Server Management Studio 2008?

Has anyone had any success with, for example, applying a 'dark' them to the Text Editor component of of SSMS 2008? There was an old trick for 2005 which is no longer operational and has not been ...
9
votes
3answers
5k views

SQL Server 2008 datetime index performance bug

We are using SQL Server 2008 R2, and have a very large (100M+ rows) table with a primary id index, and a datetime column with a nonclustered index. We are seeing some highly unusual client/server ...
9
votes
2answers
4k views

Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?

I want to access data in an Oracle 11g database from SQL-Server 2008 I set up a linked server and when I execute select * from [Link_server_name]..Oracle_schema.Oracle_table and Oracle_table ...
9
votes
2answers
2k 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 ...
9
votes
2answers
2k 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%. ...
9
votes
1answer
119 views

Why would suser_name() not reflect an AD account name change?

One of our users' names was legally changed, so we changed their Active Directory username to match - from domain\oldname to domain\newname. However, when suser_sname() is called by this user in a ...
9
votes
2answers
887 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 ...
9
votes
0answers
4k views

Fast SQL query with * but extremely slow with specific columns? [closed]

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER ...
8
votes
3answers
10k views

Installation of SQL Server RAID 10 vs RAID 5

I am new to setting up SQL Servers and Vms. My predecessor used Raid 5 for C Drive where the SQL installation resided and RAID 10 for both database files and database logs. I was wondering whether ...
8
votes
2answers
6k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
8
votes
3answers
6k views

Why do linked servers have a limitation of 10 branches in a CASE expression?

Why does this CASE expression: SELECT CASE column WHEN 'a' THEN '1' WHEN 'b' THEN '2' ... c -> i WHEN 'j' THEN '10' WHEN 'k' THEN '11' END [col] ...
8
votes
3answers
30k views

Query to report disk space allocation and used space

We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage). I'd like to write a query (report) for a single database ...
8
votes
2answers
31k views

Return a column per date in a range

Let's say I have Table A: BookingsPerPerson Person_Id ArrivalDate DepartureDate 123456 2012-01-01 2012-01-04 213415 2012-01-02 2012-01-07 What I need to achieve with a ...
8
votes
3answers
715 views

Accidentally change database name (SQL Server 2008)

It didn't happen to me YET. But I was thinking about this. I was messing around with my training environment, and by mistake I clicked in the database name, and then I touched the letter A. Let's ...
8
votes
2answers
16k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
8
votes
5answers
4k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
8
votes
2answers
20k views

How do I determine if a table exists in a SQL Server database in SQL Server 2008?

How do I determine if a table exists in a SQL Server database in SQL Server 2008?
8
votes
2answers
689 views

Can't find process that is creating backups

We have an instance of SQL Server 2008 installed on a customer server. The customer's IT department is in charge of the various backups of the machine. The SQL Server log shows there is a full backup ...
8
votes
3answers
908 views

Worse performance on a new server

We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
8
votes
4answers
41k views

Location of the mdf file of the database

I have a database Project. My problem is where can I find the .mdf and _log.ldf of my database so that I can transfer my database to another user. I am using Microsoft SQL Server 2008 Management ...
8
votes
2answers
782 views

Index fragmentation and SSD disks

I'm new to performance problems, but I saw some discussion that SSD disks solve performance problems caused by index fragmentation. Has anyone experienced this? I'm thinking of changing my hardware ...
8
votes
4answers
4k views

Are there tools to perform data comparison between two different schemas?

We are migrating our database to a new schema but want to validate the data has been moved correctly. Traditional data comparison tools can compare two databases for differences if the schemas are ...
8
votes
2answers
3k views

Do indexes consume memory?

I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is ...
8
votes
5answers
7k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
8
votes
3answers
622 views

Identify why a user can drop a database

I have a sql server user that has the ability to drop any database. I've been running the code below to check the rights that the user has in SQL Server but have not been able to identify how the user ...
8
votes
2answers
3k views

How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following: INSERT INTO [TableName] (...Columns...) SELECT ...
8
votes
2answers
305 views

What is the function of rebuilding indexes?

I'm young in the IT world, and I'm learning daily. I just started working with SQL databases, and I have lots of basic questions. One in particular, What is the function of rebuilding indexes? I have ...
8
votes
2answers
203 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
2answers
741 views

Are SQL Server Database writes slower with Snapshot Isolation?

I have a lot of deadlocks going on in my system. I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it. One of his concerns is that Snapshot Isolation slows down ...
8
votes
3answers
1k views

SQL Server Sandbox

I'm attempting to set up a sandbox for our report developers to their work in. My current plan is to "reset" the database every evening but I'm not sure how to go about doing so. What I mean by reset ...
8
votes
2answers
11k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
8
votes
3answers
2k 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 ...
8
votes
2answers
2k 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 ...
8
votes
3answers
857 views

Speed impact of using varchar in SQL Server 2008

In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
8
votes
2answers
508 views

Simple DELETE, but complicated execution plan

When I run this delete: DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870 ...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and ...
8
votes
6answers
9k views

SQL Server in a state of confusion: does the database exist or doesn't it?

Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted. First, we noticed that the database we created yesterday was ...
8
votes
2answers
30k views

How to add a user with access to a single view?

I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a ...
8
votes
5answers
216 views

What is the best way to find out how many records do not have a proper phone number in SQL Server?

I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them. I've been looking around in SSIS ...
8
votes
2answers
1k views

How to schedule jobs in Sql server 2008 for less than 10 sec?

I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds. The job is used to insert/update visitor information, and segmentation ...