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

learn more… | top users | synonyms (2)

2
votes
1answer
374 views

Mirroring multiple source databases to a single destination in SQL Server 2008

How can we maintain multiple source databases data in a single destination server database in SQL Server 2008? They all are at different networks, if that makes any difference.
1
vote
1answer
890 views

Create Index with a WHERE clause

I've read the documentation on the MSDN, but it doesn't answer what I'm after. If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs ...
0
votes
1answer
7k views

Remove an instance of “SQL Server 2008 R2” after reinstalling “SQL Server 2008”

I have installed "SQL Server 2008 R2" with an instance name "SQLEXPRESS". Then I found out it cannot restore from my older "SQL server 2008" backup files . So I uninstalled SQL Server 2008 R2 and ...
4
votes
2answers
8k views

How to set full text catalog location SQL Server 2008/2012

I want to upgrade my SQL Server 2005 database to SQL Server 2012. In SQL Server 2005 you can set the location of the full text catalog when you create it. But in SQL Server 2012 (and in SQL Server ...
5
votes
1answer
228 views

Does the transaction isolation level affect indices as well?

Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages? E.g. using ISOLATION LEVEL READ UNCOMMITTED, ...
2
votes
1answer
2k views

Save Transaction: Is the name local to the stored procedure or not?

Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused. There is a subset of stored procedures that preserve state on failure. That is, if the ...
-2
votes
1answer
1k views

Query database by date, month, year

How can I query order by date. Like this: And the same for week, month, year...?
5
votes
1answer
4k views

On-Demand Fuzzy Lookup

We've got a customers table (Who doesn't?), containing many records that are, from a business perspective, duplicates. I've been able to create an SSIS package to perform fuzzy grouping, and report on ...
6
votes
2answers
494 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 ...
4
votes
1answer
1k views

Alter failed for schema 'dbo' when trying to change schema owner

I have some MS Access code that uses ODBCDirect to execute some sql statements in a SQL 2008 R2 DB. The sql statements do not specify the schema name so subsequently are executed using the windows ...
0
votes
1answer
272 views

Thwarting some awful parameter sniffing

I've got this fairly simple reporting procedure that searches through inventory transaction data (stuff being received, moving around the warehouse, etc). The whole thing is listed below - I've only ...
1
vote
1answer
1k views

Send SSRS Report Many Times Based on Sql Query

I have a user who is requesting that a legacy report (email subscription) that runs with multiple categories be modified to run based on which categories exist in the given date range. Thus instead of ...
9
votes
3answers
755 views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
11
votes
3answers
3k views

Insert if not exists, concurrently

I am having concurrency issues with my inserts in a stored procedure. The relevant part of the procedure is this: select @_id = Id from table1 where othervalue = @_othervalue IF( @_id IS NULL) BEGIN ...
3
votes
1answer
64 views

Do SQL Server disabled databases cause any usage concerns?

I have a SaaS e-commerce app. When clients stop using the service I disable their SQL Server Database and User Account rather than delete them. Do these disabled databases cause any concerns related ...
7
votes
1answer
2k views

Is there a more helpful way to detect lock acquire/release stats in SQL Profiler?

Got a few deadlocks happening on our live server, i know, it sucks right. Anyway, i'm trying to figure out how to see what SQL code is causing the locks (or holding it for too long). I've added all ...
3
votes
3answers
2k views

Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system?

We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set ...
3
votes
2answers
6k views

How to monitor SQL Server data and log file growth?

I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
2
votes
2answers
3k views

Why the query scan all the partitions?

I have the following partitioned table create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month) The partition function is CREATE PARTITION FUNCTION [pfMonth](char(6)) ...
11
votes
3answers
9k 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 ...
10
votes
2answers
3k views

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
2
votes
2answers
898 views

What effect does serializable isolation level have on DDL-statements?

I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works ...
1
vote
1answer
100 views

Trying to figure out when a table was dropped? [duplicate]

Possible Duplicate: Determining how a schema change occurred? Using SQLServer 2008, I see in my schema a particular table was dropped. I am trying to figure out when / why etc? Is there ...
6
votes
2answers
7k views

How to check when statistics was last executed?

We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
1
vote
1answer
106 views

backing up selected tables in a database

We need to transport database backups over a lower bandwidth connection. We only want the data of specific tables, not the whole database. In SQL Server 2008 R2, how can this be done? I am attempting ...
4
votes
1answer
136 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
0
votes
1answer
284 views

FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and ...
2
votes
1answer
8k views

YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how do I switch back?

I have moved the domain of my SQL Server and while everything is mainly working the date format for queries seems to have changed. So if I run this: select convert(datetime,'2012-02-13') I get ...
6
votes
2answers
4k views

SQL Server Profiler is available for SQL Server 2008 Dev Edition?

I just upgrade from SQL Server 2008 Express with advance services to Dev Edition. I wonder whether SQL Profiler is available for the Dev Edition? Please let me know how to access that if it is ...
1
vote
1answer
95 views

SQL Server many active create procedure processes

While troubleshooting performance issues on a SQL 2008 instance, I've noticed there are several SPIDs which appear to be executingcreate procedurestatements for already existing procedures. Is this ...
6
votes
2answers
611 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 ...
3
votes
1answer
398 views

Checking lock granularity

I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB. Forgetting about lock escalation, I just want to check the default level of lock ...
2
votes
1answer
383 views

Effect on performance on retrieval of image column from a table with half a billion records on remote server

I require to loop through different servers to collect data in a temporary table. The table accessed on these servers have about half a billion records. To retrieve the data from these tables, I ...
2
votes
1answer
3k views

Database restoring taking too long

I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible. ...
1
vote
1answer
714 views

Using View filters for tenants in SQL-Server 2008

I'm currently creating a Multi-tenant data architecture in a SaaS BI-product. The product are using shared database and shared schema, but with tenant view filters and tenant data encryption ...
1
vote
1answer
410 views

Query optimization: Which is better in my case - CURSORs or a Set-based approach?

First of all, I can describe the schema of the table, but my table itself is around 80GB so I am hoping some expert eyes can look at any obvious optimizations that can be performed. I am carrying out ...
6
votes
3answers
501 views

How are my SQL Server constraints being bypassed?

We have found a handful of rows in our DB that violate an active constraint. How is this possible? The constraint is active, as we can't just manually add a row that bypasses this constraint. ...
2
votes
2answers
232 views

How to use READ_COMMITTED_SNAPSHOT

I'm having a database which is used heavily. A lot of writing/reading is done simultanosly. Therefore I've decided to use snapshots to reduce/avoid deadlocks. I'v turned on READ_COMMITTED_SNAPSHOT, ...
5
votes
1answer
144 views

Find out which columns must be specified in insert-statement

Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail. I wrote this query to find out which ...
18
votes
1answer
11k views

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number ...
2
votes
3answers
549 views

SQL Server 2008r2 replication from multiple sources

I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven ...
3
votes
2answers
538 views

Performance differences between executing via Linked Server vs. SSMS

We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in ...
2
votes
2answers
477 views

Looking for a way of moving millions of record without impacting the transaction log

I need to move around 10 million records out of a table into a new "optimized" table, but I need to keep the database online. What would be the best method in doing this without impacting the ...
2
votes
1answer
633 views

Is there a way to view the body / contents of a system function in sql server 2008

For the sake of leaning and exploring, I'd like to look at how some of the system functions are built. Is there a way to do this in sql server 2008 or R2? I've tried sp_helptext 'Sum' but I get an ...
1
vote
3answers
97 views

Need to figure out what FKs point to a specific table

In SQLServer 2008 r2. I need to figure for a specific table what FK's in other tables point to it. I try: SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS But this does not give me what I ...
5
votes
1answer
1k views

How does objectproperty() figure out if a function is deterministic in sql server?

How does sql server determine if a function is deterministic or not? Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 ...
3
votes
1answer
770 views

sys.sysprocesses versus DMVs

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and ...
3
votes
2answers
2k views

Keeping “SQL Trace was started” and “SQL Trace stopped” from filling up my SQL logs

I have a SQL2008 server that runs a custom application that starts and then stops a SQL trace every 60 seconds (it does some reporting and summarization on the trace data). The problem is that my SQL ...
1
vote
0answers
89 views

Managing SQL 2008 database [closed]

We have a vendor application that creates a new database monthly for only few tables. Vendor doesn't have solution. Is there any other way besides partitioning as partitioning require enterprise ...
2
votes
3answers
164 views

Document versioning

I want to store in SQL Server 2008 XML files, but in a way that versioning is supported. For example, I have a XML document and I save it in the DB(in the XML datatype column). After that, I edit it. ...