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

learn more… | top users | synonyms (1)

23
votes
3answers
15k views

Why is my database still fragmented after I rebuilt and reindexed everything?

I have a database which I tried to defragment all the tables at once by running this T-SQL: SELECT 'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) + 'ALTER INDEX all ON ' + ...
12
votes
1answer
6k 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 ...
12
votes
1answer
1k views

What types of corruption can DBCC CheckDB miss?

This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following: BACKUP 'BrokenDatabase' detected an error on page (1:123456) ...
3
votes
1answer
173 views

What are some efficient ways in using COUNT(*) with WHERE

Instead of calling COUNT(*) with a WHERE clause, is there anything one can do to make this work more efficiently on the server? I would like to get this count on complicated joins, just wondering the ...
1
vote
1answer
331 views

Grant access for any logged in user for common database

In SQL server 2008, I have one database that is a common database that is accessed thru stored procedures in all other databases. Each of the other databases have their own login. I need a way that ...
1
vote
1answer
1k views

Conversion of varchar data to datetime failing

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering As part of this effort, we are taking data from the base Entity table and ...
0
votes
3answers
311 views

Does running a COUNT() statement using NOCOUNT ON; consume much

On millions of rows.. Is it worth caching the total amount or turning NOCOUNT OFF; instead of requesting it each time on that much data? Sql Server 2008 R2 Also, is running a COUNT() statement on ...
3
votes
2answers
2k views

sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups

These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing? Here is how I proved it is the same data: select ...
2
votes
1answer
171 views

Change tables from one user to another

I created some objects in MS SQL Server 2008 using SubSonic 3. It created them based on the user specified in connection string like: test_user.table1 but for some reason I am not able to test_user ...
2
votes
1answer
236 views

Where can I find information on shredding xml with .nodes?

I recently saw this answer on SO which uses the "local-name" value while shredding xml using .nodes(). I've never seen this before: select T.N.value('local-name(.)', 'varchar(128)') as Name, ...
6
votes
3answers
299 views

Things to consider with a bigger table

I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently ...
12
votes
3answers
1k 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 ...
2
votes
1answer
433 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
1
vote
1answer
597 views

Issue with SQL Server 2008 replication

I'm doing merge replication between two SQL Server 2008 machine.But,when I attempt to do any modification on system,I got the following error. The Merge Agent failed because the schema of the article ...
3
votes
1answer
993 views

View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL ...
3
votes
3answers
4k views

SQL Server: does ddladmin rights contain datareader and datawriter rights to the database?

Does db_ddladmin fixed database role include db_datareader and db_datawriter rights, so that you don't have to grant those rights explicitly?
7
votes
1answer
354 views

Why would running ALTER TABLE on a column cause it to go from NOT NULL to NULL?

I am mass updating a SQL Server database. I am changing all our numeric(38,0) columns to int (yes, SQL Server scripts were created from Oracle scripts). Using SMO and C# (I am a sw engineer), I ...
11
votes
6answers
3k views

SQL Server Checklists

Following up from my other question, I would like to start thinking about what I should take a look at on daily/weekly/monthly bases in terms of alerts. I am hoping to be able to see problems coming ...
2
votes
1answer
283 views

Health checking via Database Mail in SQL Server

I have a few Sql Scripts that I would like to run every day on my Sql Server(s). How can I query multiple times and have the output similar to the following: Health check for <server> Query ...
2
votes
3answers
310 views

Non-deterministic aggregated result

Why is my query non-deterministic? I have query that do the following: select sum(float1*float2*coalesce(float3,1)) from table When I run this query I get a deterministic result, but it seems not ...
5
votes
2answers
165 views

Database placement on abstract storage structure

Often times we all hear the same thing. The recommendations on where to put OLAP/OLTP databases, where to put the tempdb, where to put transaction logs...etc. But say you are in an environment where ...
5
votes
1answer
1k views

SQL Server 2008 Containstable generate negative rank with weighted_term

I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank value. The query is as follow SELECT ...
2
votes
2answers
1k views

SQL 2008, how does it link Full Text Catalog to a filegroup

While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?
7
votes
3answers
4k views

Chaining SQL Server Agent Jobs

I have several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that ...
1
vote
2answers
2k views

Statement could not be prepared

I've got a SQL Server 2008 server set up with a linked server to a 2005 server. On the 08 server i've got a view set up that's pulling data from the '05 server. This scenario works from my dev '08 ...
2
votes
1answer
1k views

Moving Tables Indexes to Separate file / file-groups, What Happens?

I'm trying to get a grasp on whats going on when you move objects to a separate filegroup / .ndf file. *This is for a 3rd party app that sucks that I have no access to source, and is entirely ...
7
votes
2answers
4k views

Why does this query cause a deadlock?

Why does this query cause a deadlock? UPDATE TOP(1) system_Queue SET [StatusID] = 2, @ID = InternalID WHERE InternalID IN ( SELECT TOP 1 InternalID FROM system_Queue WHERE ...
7
votes
2answers
1k views

DTA Recommends to CREATE STATISTICS

I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file. My question is, how exactly ...
11
votes
1answer
2k 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 ...
3
votes
4answers
199 views

how to interpret log files with high transactions

I'm having this issues with too many logs files being generated. Over 60gb in the last 24 hours. It's not a massively high transactional website. What is the best way to read the log files and work ...
3
votes
1answer
536 views

Does T-SQL MOVE actually move the database, or just copy?

I'm a bit confused over the T-SQL command MOVE. I have a statement saying: MOVE N'Prod_Database_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf' ...
3
votes
1answer
206 views

Minimum rowcount for filtered index?

Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known. But with the advent of ...
5
votes
3answers
740 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. ...
4
votes
2answers
2k views

SQL Server database forum design sample

I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find ...
3
votes
2answers
283 views

How to track how many times the data is read?

I want to track how many times records are read in a specific table. Let's say that we have this table ID Name 1 Cat 2 Dog 3 Fish I want to know how many times the record ...
1
vote
1answer
1k views

Install SQL server 2008 R2 with VS 2010

Ok, I've installed SQL Server 2008 R2. I'm trying to add a "SQL Server Database" to an ASP MVC project. But it keeps telling me I need SQL Server for it to work. What do I need to do to setup the ...
4
votes
2answers
1k views

Best way to evenly distribute data over partitioned tables

I am working with a database with roughly around 500MM records. We currently use a static partitioning method that partitions the data by an int identifier such at < 150MM goes on partition 1, > ...
7
votes
1answer
4k views

Find all ancestor nodes of a HierarchyId using a single SQL statement?

I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
1
vote
1answer
430 views

SQL Server sp_configure options?

Does anybody know where I can find a list of all the parameter options for sp_configure? It isn't really jumping out at me when I search online for possible variations and the capabilities of ...
3
votes
2answers
3k 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 ...
10
votes
1answer
4k views

How to use transactions with SQL Server DDL?

I have a login table into which all inserts are done by a single stored procedure. CREATE TABLE dbo.LogTable( LogRefnr int IDENTITY(1, 1) NOT NULL, LogQuery varchar(255) NOT NULL, LogTime ...
1
vote
1answer
425 views

Ms Access queries equivalent in Sql Server

Queries in MS Access can act like views do in oracle in that you can query against them. Example: if you have a query in access called allProducts, you can also do SELECT * from allProducts; My ...
4
votes
2answers
2k views

Role db_ddladmin not working properly on SQL Server 2008

I want to allow certain user to create/modify objects on a database(tables, views, sp's, functions, schemas etc) and don't want to give db_owner. I thought I could do it with the db_ddladmin role, ...
6
votes
1answer
917 views

Can someone explain the magic of Opendatasource/Openrowset?

When pulling information from an excel file (or ms-access DB) I commonly use something like this: SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\test.xls;Extended ...
2
votes
2answers
1k views

Is it possible with SQL Server 2008 to have a Partioned Tables with an Identity Column?

I'm not thinking of using it for the partition function. I just want to know, if it is supported or not. Don't tell me why it is not a good idea to use such columns on partitioned tables.
3
votes
3answers
434 views

Database Schema with a shared identity Field

I'm a developer treading into the database world so bear with me please. I'm creating a Contract database with separate tables for various types of contracts. I have 5 tables but would like to ...
5
votes
1answer
420 views

how to improve performance by using or not using table variables

I have a table variable: DECLARE @to_process TABLE ( [Id] [bigint] NOT NULL, [SequenceId] [bigint] NOT NULL, ... ) INSERT INTO @to_process ( Id , SequenceId ... ) SELECT ...
3
votes
3answers
1k views

Managing multiple SQL Server databases

My company has many branch offices and each branch has a DB server. Some have SQL Server 2005 and some others have SQL Server 2008. The schema is the same for all these servers. Whenever I want to do ...
2
votes
1answer
735 views

Change Tracking Retention from a backup?

I have a backup of a SQL Server 2008 database with change tracking enabled on it. The retention period is 2 days. AutoCleanUp is set to true. The database backup is 5 days old and has 2 days worth of ...
3
votes
1answer
638 views

Query Parallelism

I have a long running query that behaves differently between two different machines. I have an 8 core machine running Microsoft SQL Server Developer Edition (64-bit) that does not use parallelism and ...