This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

2
votes
2answers
4k views

Reducing Key Lookups

I am using SQL server, and I have been looking closely at the concept of key lookups, ...
1
vote
1answer
2k views

Simple Implementation of SQL Server 2008 Encryption

I am looking at using some of SQL Server 2008's encryption. I have read the following article on MSDN: http://msdn.microsoft.com/en-us/library/ms179331.aspx Q1a: Is the master key password ...
16
votes
4answers
11k views

What is the best query to use to monitor a SQL Server database's status?

I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state. This is the ...
15
votes
3answers
3k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
10
votes
2answers
2k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
10
votes
1answer
3k 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 ...
10
votes
3answers
736 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], ...
9
votes
2answers
3k 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
807 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
2answers
3k 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 ...
8
votes
1answer
43k views

ODBC Data Source SQL Server Connection - Login Failed For User

I have moved my database from an SQL 2005 to a server with SQL 2008. I am now trying to create an ODBC Data Source. I am using "With SQL Server authentication using a login ID and password ...
8
votes
4answers
7k views

Can I export a maintenance plan without using Integration Services?

I'm trying to export a simple maintenance plan from an SQL Server instance. I want to check the export into source control, and apply the exported maintenance plan to functionally identical ...
8
votes
3answers
7k views

Emulate a TSQL sequence via a stored procedure

I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in ...
8
votes
5answers
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
3answers
836 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 ...
7
votes
2answers
1k views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
7
votes
1answer
8k views

Automatic decimal rounding issue

The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
7
votes
2answers
1k 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 ...
7
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 ...
6
votes
2answers
1k views

problem running xp_cmdshell when Application Name set in connection string

I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL ...
6
votes
4answers
12k views

Could not find the Server Agent in SSMS

I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8 8. Now to schedule your newly created script. ...
6
votes
1answer
199 views

Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | ...
6
votes
2answers
4k views

Data type for storing an array of flags (a bitmap/bit array)

I need to store a bit array for each record of a table, supporting the following operations: Testing if a bit is set, and setting a bit (using SQL) Querying and setting the value using ADO 2.8 (not ...
6
votes
2answers
3k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
6
votes
2answers
220 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
3k 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 principal and ...
5
votes
2answers
4k views

SQL Server EXECUTE AS trouble

I'm missing something while trying to make my stored procedure use EXECUTE AS. The stored procedure is reading data from source_db, aggregates it and stores result in target_db. The sp itself is in ...
5
votes
1answer
958 views

SQL Server 2012 Resource Governor Enhancement

In SQL Server 2012 what specific enhacement was made regarding controlling memory usage by using Resource Governor. Up until SQL Server 2008 R2 resource governor was only able to control memory ...
5
votes
2answers
430 views

Can I prove my database does not have memory issues?

(This is a continuation of do indexes consume memory) A fellow database developer believes we are having memory issues. He has seen increased run times in some standard queries - going from under 10 ...
5
votes
3answers
769 views

Using SQL Server on separate networks

I have a bunch of SQL Servers installed on Windows2008R2. Currently, each windows server has two NIC's, each connecting to separate networks. In other words, each server appears on each network. ...
5
votes
2answers
5k views

What is the best practice for storing image varbinary(max) data in a database table?

I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
4
votes
3answers
153 views

How do you see which database on a Server uses most resources

I have a database server with a couple of databases on it. How can I see where any resource pressure may come from? I would like to get a table of: Database Name Batch Requests per second CPU ...
4
votes
1answer
4k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
4
votes
1answer
565 views

Indexes file damaged, was on ramdisk

I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
4
votes
2answers
7k views

Automatically restart SQL Server 2008

Is there a "best" way to automatically restart SQL Server on a regular basis? I read that I could create a batch file to net start, net stop the agent and service in a batch file and schedule that, ...
4
votes
1answer
1k views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
4
votes
1answer
2k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
4
votes
2answers
776 views

SQL Server Dynamic Memory management

I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's ...
4
votes
3answers
1k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
4
votes
3answers
5k views

Stored procedure executes too long

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
3
votes
1answer
492 views

Shared locks taken under Read Committed Snapshot Isolation (RCSI)

I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI. How can I be seeing ...
2
votes
1answer
1k views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
2
votes
2answers
2k views

Shrinking a SQL Server Log File Multiple Times

Recently ran into a situation where after a log backup and shrinkfile, the LDF file remained the same size. DBCC LOGINFO clearly showed only one active VLF and SSMS said that 99% of the space in the ...
2
votes
3answers
448 views

How to merge data sets without including redundant rows?

I think this must be a fairly common thing to do, but I haven't been able to find an example. It involves merging data that involves dates/times (or more generally any sequential values) without ...
2
votes
1answer
563 views

Prevent users from using power pivot excel connections to a database

A couple of weeks ago my boss connected to our production database via excel and was able to query the database. Because of how we're doing security to the database (AD groups) he should have access ...
2
votes
1answer
21k views

IF NOT NULL then UPDATE else keep the value of the field

I think I am somehow close to get it work, but for some reason I still get errors. I have the following UPDATE query: UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN ...
2
votes
2answers
2k views

how to use database mail from SSIS

I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks
2
votes
1answer
405 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
2
votes
1answer
861 views

How to Backup and Restore Filestream Data

I am trying to backup and restore my database which includes a filestream filegroup. I backed up the database without the filestream filegroup and was interested to know what happens when you restore ...
2
votes
1answer
456 views

SQL Server 08: Union over while

I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out! I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the ...