Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

4
votes
1answer
122 views

Is there a non-DDL way to preserve back-references when staging data into production?

Let's say I've got this hypothetical schema: Source (OLTP) DB: Table Orders ------------ OrderID int IDENTITY (PK), CustomerID int NOT NULL, OrderAmount decimal NOT NULL Destination (DSS) DB: ...
1
vote
1answer
1k views

What is the purpose of sqlbulkcopy's useinternaltransaction property?

I'm just testing my PowerShellScript to do bulkcopy of multiple tables between sql-server and oracle Databases. When the destination is Oracle, than I can monitor the progress by by executing select ...
5
votes
1answer
384 views

How to Index a JOIN result of two or more tables in order to improve the performance in SQL server?

I am new to indexing and gone through the basics of indexing. I can find a default clustered index for the primary key constraint within the indexes part of the corresponding table, but after creating ...
2
votes
1answer
2k views

“Procedure: XXXX has an unresolved reference to object XXXX” errors

After using for some time MS SQL Server Management Studio for creating and editing databases I tried to use Visual Studio 2010 "SQL Server 2008 Database Project" for this. I used the SQL 2008 Wizard ...
9
votes
5answers
2k views

How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
8
votes
3answers
723 views

SQL Server: filegroup for system tables only?

One of our corporate standards to is have a separate filegroup/file for user tables/indexes. This is set as the default so no need to qualify CREATE TABLE statements. So it looks like this fileid ...
5
votes
3answers
380 views

SELECT * WHERE VarCharColumn IN (…) Optimization

I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution. The column is ...
4
votes
2answers
11k views

SQL Server shows database in recovery

I'm a junior DBA and I have a very strange problem with one database. Today, after a power failure, one database (with Recovery: full) show (In recovery) in SSMS ie: myDatabase (In recovery) ...
8
votes
5answers
198 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 ...
5
votes
1answer
1k views

Is it safe to change the Recovery Model of SSRS database ReportServer to SIMPLE?

I'm changing the recovery model of ReportServer database from FULL to SIMPLE. Is there any reason why i would not want to do this? Besides the obvious answer of losing point-in-time recovery? (i am ...
4
votes
1answer
745 views

SQL 2005 Full Recovery Mode Point in Time Restore w/out Backup

We have a database that is in full recovery mode. The database is not in production yet so does not have a backup plan in place and is thus not being backed up. In other words, there is no existing ...
2
votes
3answers
773 views

Migrating from ntext to NVARCHAR(MAX)

I have table with the columns of type ntext.And for some peculiar reasons, client want to migrate the column type to NVARCHAR(MAX). While doing so , what all potential threat should i be careful ...
3
votes
4answers
321 views

Regnerating an execution plan - is this a valid approach?

If I am optimizing a query, in order to ensure that this query does NOT uses cached execution plan I use OPTION (RECOMPILE) with the query. Is it a good approach?
4
votes
1answer
421 views

What can be done, if anything, to tune a SharePoint database?

SharePoint uses a single table (i.e. dbo.AllUserData) with columns like nvarchar1, nvarchar2, ..., nvarchar64 or int1, int2, ..., int16 (193 columns altogether) to store all items from all lists in ...
5
votes
3answers
934 views

Is the ADO.NET Entity Framework known for generating poorly performing queries?

This question is inspired by the comment posted to the latest ServerFault blog post: Are you guys still using LINQ to SQL? I know I can use SQL Server Profiler and/or the ToTraceString method to ...
0
votes
1answer
677 views

detaching database results in error “SQL Server detected a logical consistency-based I/O error”

I'm trying to detach a database and I'm getting this error : SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:234; actual 0:0). It occurred during a read ...
3
votes
3answers
138 views

Is it possible to install Developer Edition of SQL Server as the primary development server?

Is it possible to install Developer edition of SQL Server on a Windows server environment as development server? Each developer will have a local copies of same SQL Server edition and will connect to ...
2
votes
1answer
413 views

truncate table for all db in sql server 2000

i need a script that will go to every db in server and if exsistes a table named "table1" will do truncate table table1
4
votes
4answers
2k views

Examples of SQL transaction procedures for sales tracking or a financial database

I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions: salesman creates new product ...
8
votes
3answers
3k views

Is it okay to put dots in SQL Server database names?

If I use a database name with a dot in it in SQL Server (2005 or 2008), something like "MyApp.Sales", will this cause any problems?
1
vote
1answer
915 views

automating a database backup, restore and stored proc call

How do I create a process that will: backup Database A restore it as Database X (needs to be definable) then run a stored procedure against Database X. shrink and truncate its log file. Anybody ...
9
votes
5answers
1k views

SQL Server command line usage? Is it possible?

I've been trying to use the SQL Server Management Studio 2008's built in "Solution Explorer" to manage a project and after 2 weeks of struggling with it, I've decided I don't really care for it. It's ...
3
votes
3answers
119 views

Is there any trick that allows to use Management Studio's (ver. 2008) IntelliSense feature with earlier versions of SQL Server?

Latest version of Management Studio (i.e. the one that ships with SQL Server 2008) finally has a Transact-SQL IntelliSense feature. However, out-of-the-box it only works with SQL Server 2008 ...
7
votes
1answer
321 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 ...
3
votes
3answers
237 views

Does separating VARCHAR values based on size have a performance gain?

I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ...
3
votes
2answers
4k views

Easy path to REST on SQL Server?

At work, one of our primary databases is running on MS SQL Server. I think it's 2008. I'm currently hitting it via UnixODBC and FreeTDS from a Linux host. Unfortunately, the UnixODBC/FreeTDS are not ...
5
votes
2answers
413 views

Impact of Redgate SQL Search on Server

I'm a huge fan of a lot of the Redgate tools, however at my current workplace they are very paranoid over anything interacting with live servers (in particular affecting performance). So my question ...
10
votes
7answers
20k views

Moving tables to another SQL2008 database (including indexes, triggers, etc.)

I need to move a whole bunch (100+) of large (millions of rows) tables from one SQL2008 database to another. I originally just used the Import/Export Wizard, but all the destination tables were ...
1
vote
1answer
1k views

Can't stop replication sp_dropdistpublisher responds “there are subscribers defined”

I am running into errors: exec sp_dropdistpublisher @publisher='servername' response: Msg 21047, Level 16, State 1, Procedure sp_dropdistpublisher, Line 145 Cannot drop the local distribution ...
5
votes
3answers
8k views

How to restore multiple backups

I'm using ExpressMaint and Windows Scheduled Tasks to create weekly full and daily differential backups of some MS SQL Server 2008 R2 Express databases Weekly fulls expressmaint -S ...
13
votes
1answer
2k views

How to get the MAX row

In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability. ...
18
votes
3answers
18k views

How do I get back some deleted records?

I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side. Is there any way to get back these ...
31
votes
11answers
3k views

How can I track database dependencies?

As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods ...
14
votes
3answers
3k views

What is the best way to get a random ordering?

I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How ...
10
votes
1answer
9k views

Difference between Hash, Merge and Loop join?

In SQL Server you can specify the join hints: HASH JOIN MERGE JOIN LOOP JOIN What is the definition of those three join hints, and when should each be used?
2
votes
2answers
993 views

What is the best way to filter or otherwise manipulate the results returned by a stored procedure?

Often I find myself wanting to filter or order the results returned by execing a stored procedure - for example to order the output of sp_who2 by login or CPU Time, or filter it by host name. What is ...
7
votes
6answers
3k views

Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios

From what I can tell, there are three possible ways of backing up your SQL Server database Full backup Differential backup Log Shipping What are the pros and cons of each strategy and in what ...
14
votes
5answers
4k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
23
votes
3answers
5k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
3
votes
2answers
322 views

What's the best way to track Updated By, Updated On

In MS SQL Server, what's the best way to update an Updated By, Updated On set of fields on a table? I've seen it done it triggers, in the code. etc. The upside I've seen to triggers is that it all ...
10
votes
2answers
7k views

What is a Clustered Index?

I need a short explaining of clustered index. What is a clustered index? What are best practices for using the clustered index?
2
votes
6answers
766 views

Optimize this SQL query for performance

I'm trying to optimize the following statement: 'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR) +'/00000' +CAST(@number+1 AS VARCHAR) The statement produces a value ...
11
votes
3answers
10k views

Rebuilding the transaction log

We have a very large database (~6TB), whose transaction log file was deleted. We have tried: Detaching and reattaching the database; and Undeleting the transaction log file but nothing has worked ...
8
votes
2answers
359 views

Shouldn't SQL Server Support RANGE?

As someone who develops websites and does a bit of SQL Server, it seems a no brainer to me that SQL Server should support a simple clause that indicates the results should only include a specific ...
15
votes
4answers
4k views

Using SQL Profiler on a database that's in production

As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems. But I've always used it on my development ...
4
votes
3answers
1k views

Building Dynamic SQL-Server Where Clause

Let us review this dba.exchange Oracle question for SQL-Server. This is SaUce's code, after a little formatting: CREATE PROCEDURE GetCustomer @FirstN nvarchar(20) = NULL, @LastN ...
12
votes
3answers
3k views

Find out what queries are causing the biggest amount of network traffic

On a production SQL server I am seeing intermittent enormous spikes in data traffic. Up to 200Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what ...
6
votes
2answers
768 views

Get records between time span

I would like to retrieve records between time spans,All i have is this snippet of string indicating the time span 9:00 AM - 10:00 AM below is the table schema CREATE TABLE [dbo].[Samples]( [RID] ...
9
votes
2answers
2k views

How large is “too large” for SQL 2008 Web Edition?

For our soon-to-be-hosted application I need to choose between SQL Server 2008 Web edition and SQL Server 2008 Enterprise edition. There isn't any "must-have" functionality: the application currently ...
8
votes
2answers
878 views

Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due ...