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)

7
votes
1answer
7k views

OPTION FORCE ORDER improves performance until rows are deleted

I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds. ...
2
votes
1answer
1k views

Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate?

We run a background task on one of our servers which polls sys.dm_exec_requests for long-running queries, gathering diagnostic information when it finds them, including wait type. I'm seeing a number ...
9
votes
1answer
2k views

Best way to re-import large amount of data with minimal downtime

I need to import about 500,000 records containing IP lookup (read-only reference) data about once a week (only three int/bigint cols). I don't really want to worry about merging the data with the ...
4
votes
3answers
160 views

Can anyone describe the step by step process of recovery , backup and restore in sql server

Can anyone describe the step by step process of recovery via backup and restore in SQL Server 2008. If possible please include some examples.
0
votes
1answer
424 views

Escaping a single quote in SQL Server 2005 [closed]

I am using SQL server 2005 and using the northwind database. I need the supplier id of Grandma Kelly's Homestead and I used this query: select supplierid from suppliers where companyname ='Grandma ...
1
vote
1answer
96 views

Where can I find guidelines for building a SQL Server machine specifically for DBCC CHECKDB work?

I'm working on defining specs for a new server to run DBCC CHECKDB scans on all databases in our portfolio (85 instances, 1300 DBs, 15 TB allocated). I have a custom application that copies dump files ...
5
votes
3answers
222 views

Difficulty in moving a SQL Server database

I'm trying to upgrade from SQL Server 2008 R2 Express to Developer. My plan is to remove the Express edition, then install the Developer edition. I have one database I need to bring over to the new ...
4
votes
4answers
32k views

SQL Server CREATE FILE encountered operating system error 5 (Access is denied.)

I'm trying to execute the following script in SQL Server Management Studio but I'm getting the error: Msg 5123, Level 16, State 1, Line 2 CREATE FILE encountered operating system error 5 ...
1
vote
3answers
91 views

Generally speaking, what pecking order should be used when tuning SQL Server?

When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to ...
3
votes
1answer
74 views

In SQL Server Replication, at what point is it recommended that one separate the distributor?

Is there a general rule of thumb for when it becomes good practice to assign distribution responsibilities to a separate server that is not also a subscriber? e.g. "At n publishers/publications ...
2
votes
2answers
534 views

Make sure two columns are sorted in the same order

I have a table like this: CREATE TABLE T ( ID INT NOT NULL IDENTITY PRIMARY KEY, CreateDateTime datetime2 NOT NULL, --other columns ) How can I make sure that ID and CreateDateTime are both ...
15
votes
2answers
3k views

SQL Server does not optimize parallel merge join on two equivalently partitioned tables

Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
2
votes
1answer
168 views

Can I move data and full text search files to another drive and change the drive letter?

My server's G: drive is rapidly running out of space. It mainly contains data files (*.mdf) for both system and user databases. The FullText Catalogs also exist on that drive. IT added a much lager ...
4
votes
1answer
126 views

unique constraint on a set

With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005? To illustrate the problem, I make an example: A tag_bundle consists ...
4
votes
1answer
961 views

Where are queries against a linked server actually processed?

How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering: Scenario ...
0
votes
6answers
822 views

Equivalent of CHARINDEX OR IN for ints

I want the equivalent of this for ints. --Valid SQL DECLARE @users AS nvarchar(50) SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23 select * FROM Person AS p WHERE CHARINDEX(p.sFullName,@users) > 0 ...
7
votes
1answer
189 views

Dynamic Dimension Security

I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company. The Company dimension contains a list of companies, of which there are several hundred in the cube. Our security model ...
1
vote
4answers
724 views

Backup and restore of RDBMS(Oracle, DB2, SQL Server) from Perl

What could be the best approach ? Till now could figure out the following (Oracle) Calling exp.exe and imp.exe utilities in Oracle client using Java and inturn call it from Perl. Calling exp.exe ...
3
votes
5answers
737 views

Single query to return counts over different IDs in a single record

select count(title_id)as algodata from titles where pub_id =1389 select count(title_id)as binnet from titles where pub_id =0877 select count(title_id)as newmoon from titles where pub_id =0736 ...
0
votes
2answers
2k views

Doing a comparison on two csv files with no Primary Key

I'm wondering if in SSIS there is a way to compare two csv files(with the same structure) with no reference key to make a record unique. To give you a better picture from what I mean; In initial load ...
3
votes
2answers
6k views

Copying View in Management Studio

I want to copy a VIEW from one server to another. I could copy tables but I can't copy views. Is there a way to do so? Thanks
6
votes
1answer
11k views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)

I have a clustered environment which has many instances in it. I have modified the xml file to allow several instances to use ssis. Everything works great when the instances are on the same node as ...
7
votes
2answers
1k views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
2
votes
1answer
696 views

Export SQL Server varbinary field to string using Visual Basic script in SSIS?

I'm using SSIS to export data from a set of tables to flat files. The export is running great except for one table with a varbinary field. The export is running a Visual Basic script task to load the ...
2
votes
1answer
894 views

Implementing “ANY” aggregate operator in T-SQL

I have a database that contains department and employee tables, and I need to return the number of employees for each department. I write the following query: SELECT d.ID, d.Name, COUNT(*) ...
3
votes
3answers
8k views

Mapped drive for Microsoft SQL Server 2008

I've noticed strange issue after this weekend. Maybe someone could explain this situation. I've got SQL Server 2008 placed on Windows Server 2008 with read/write permission to catalog ( let say ...
4
votes
1answer
145 views

Shrinking Reluctant Log Files

I have several SQL Server 2008 databases with transaction logs which have gotten out of hand and filled the drive. One of these logs was 68gb yikes. I know better than to kill off my backup chain ...
5
votes
4answers
1k views

Composing the “ultimate database maintenance script”

I'm trying to compose T-SQL script that will: A) Shrink database size to the minimum possible size; B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a ...
3
votes
1answer
211 views

I have Fragmented Index on a empty table!

I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be ...
6
votes
1answer
2k views

How to create a SQL Server 2008 backup without logs with mirroring

How do I create a backup of a SQL Server 2008 in a mirrored setup without any logs? We need to do this so that when we restore our local development databases, we would have the most up to date ...
6
votes
3answers
2k views

Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance

We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like PK BIGINT ID VARCHAR(50) Col1 Col2 etc So we need to insert a row in the ...
2
votes
4answers
1k views

SQl Server Password Change after installation

I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in Windows authentication mode, and I want to access the database in ...
1
vote
2answers
205 views

Sql Server Linked Server

I have two sql server S1(2008) and S2(2005). S2 is added to S1 as linked server. S2 have a table Product. When I try to run the code select ProductID from Product I get the error: Location: ...
3
votes
2answers
3k views

Suppress SQLCMD messages

Would it be possible to suppress all sqlcmd messages output to the cmd window? I am running a large script and it seems to be slowed down a lot with all the Processed XXXX total records messages. ...
0
votes
1answer
1k views

Fast Recovery in SQL Enterprise Edition

Starting with SQL Server 2005 and all later versions, Enterprise Edition includes a feature called "Fast Recovery". a) Does fast recovery use multiple OS threads to perform "REDO" operation? b) Does ...
4
votes
1answer
2k views

Running queries with SQLCMD vs. Running queries with SSMS

I've noticed something which i'm not sure about. can it be that running queries directly with SSMS is much faster than running queries with SQLCMD? I've noticed that the results of queries that ran ...
4
votes
1answer
8k views

Import/export wizard code page error [closed]

Using the Import/Export Wizard, I ended up with an annoying problem: Error 0xc02020f4: Data Flow Task 1: The column "pinNumber" cannot be processed because more than one code page (874 and 1252) ...
3
votes
2answers
12k views

Cannot access any database in SQL Server 2008 R2 Express after moving workstation from domain to workgroup

My workstation that has SQL Server 2008 R2 Express for development purposes installed has been moved out of a domain into a Windows workgroup. While being part of the domain I used the account ...
2
votes
1answer
538 views

How can I keep two tables in two different databases with different schemas the same in real-time?

I have two databases A, and B, which will reside on the same server. A and B have different schemas. Database B will contain different numbers of tables, and sometimes tables with the same name will ...
13
votes
3answers
1k views

Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query. The MSDN docs say that the general logical ...
9
votes
3answers
2k views

How to control the order of servers listed in the SSMS connection dialog?

My current project involves a lot of database shuffling as we upgrade from SQL 2008 -> 2012, re-task hardware, repeat. One minor annoyance is the connection dialog in SSMS re-shuffling the server ...
4
votes
2answers
2k views

Sql Server Agent notification email with custom message

I have set email notification on the completition of a scheduled job. Is it possible to customize the email content?
7
votes
1answer
93 views

sysschedules datatype choices

I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
3
votes
2answers
185 views

How SQL server database reorganize memory

I am using [sys].[master_files] table to determine the size of the database, however i didn't get a sensible value yet. If i am inserting 100 rows into a table ideally db size should increase while i ...
3
votes
1answer
477 views

SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]

I have the following table structure (simplified, pseudocode): TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY) TABLE dbo.Transfers ( Id int NOT NULL IDENTITY PRIMARY KEY, ...
4
votes
1answer
179 views

I was interrupted while transferring a very large table. How can I pick it up from where it stopped?

I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the ...
3
votes
1answer
718 views

Send data from SQL to a serial port or IP address

I've been asked to integrate SQL transaction data with a video monitoring system in real-time. The video system's application is only capable of receiving data by listening on a serial port or a ...
2
votes
2answers
73 views

What happen when changing column type for a table with clustered index?

I have a very large table (200M+ row), and it is clustered indexed. I would like to change one of the foreign key column from a bigint to smallint. My questions are: Does SQL need to rebuild the ...
11
votes
3answers
2k views

Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?

I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it. I am not a real DBA, I'm a software developer who is ...
5
votes
3answers
2k views

On SQL Server Backup: Msg 3271, Level 16, State 1, Server Line 1 A nonrecoverable I/O error occurred on file

I am trying to back up a pretty big database and I keep getting this error: Msg 3271, Level 16, State 1, Server {Server Name}, Line 1 A nonrecoverable I/O error occurred on file "{Backup File ...