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)

1
vote
2answers
813 views

I run sp_who on the database, and there is a “TASK MANAGER” process. What is it?

I have a sporadic problem, when upgrading SQL Server in a single-user mode, some other application somehow logs into the database and kicks my process out. Last time when that happened, I ran sp_who, ...
2
votes
1answer
724 views

Do you set an index on JOIN clauses or where clauses, or both?

Say my query looks like: SELECT t1, t2 FROM t1 LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid) WHERE t1.enabled = 1 AND t1.startDate <= ??? AND (t1.counter = -1 OR ...
0
votes
2answers
837 views

Date and time parameter In msrs report

Hi I am looking to set up my report so the start date will always be say '14-09-2011 00:00:00' And the end date will always be '14-09-2011 23:59:59' Or in other words Start date will be 00:00 and end ...
7
votes
2answers
3k views

Can Windows Authentication logins be disabled on SQL Server 2008 (or R2)?

So we can do Windows Login or Mixed Mode, but can we configure SQL Server to only use internal logins and to block all Windows Logins? Is the only solution to add all potential Windows logins and set ...
2
votes
0answers
279 views

How to do automated archiving for a SQL Server database based on interpretation of database metadata?

I am looking for a solution giving me automated archiving of SQL Server databases based on a smart interpretation of table relationships that is able to archive records from related tables minimizing ...
3
votes
2answers
582 views

SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help

I have a SQL Server table with about 2 million rows in, this table stores short textual documents. Here's my schema: CREATE TABLE Documents ( documentId bigint IDENTITY NOT NULL, content ...
3
votes
1answer
323 views

Why am I getting Table scan on a partitioned table query?

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table. When I run the following: SELECT * FROM dbo.INVENTORY_BREAK WHERE IR_ID ...
3
votes
1answer
11k views

CASE statement with IS NULL and NOT NULL

Is there any better way to write the lines below in SQL Server 2005? CASE WHEN (ID IS NULL) THEN 'YES' WHEN (ID IS NOT NULL) THEN 'NO' END AS ID_Value,
3
votes
2answers
84 views

can access to a view be controlled by access to a referenced table?

I have a table Table_A and a view View_A that is a select of the contents of Table_A. If I deny SELECT on Table_A to a user, the user can still use the view View_A. Is there a way that I can force ...
3
votes
0answers
318 views

SQL Server 2008 replication scenario

First of all sorry about my english, and most importantly, my database ignorance, I'll do my best ;) Until now, the scenario that I have is as follows: In a server under our LAN, we have a database ...
10
votes
1answer
611 views

Are there reasons why I should not set my db owner to [sa]?

Yesterday I asked this question regarding changing the dbo of multiple databases that I have. The change makes sense, but I want to be clear. Is there any, good reason or circumstance why I should ...
2
votes
2answers
868 views

sql bulk delete is much slower?

I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete DELETE TOP (n) syntax, then ...
2
votes
0answers
175 views

Upgrading Remote Distributor in SQL Server Replication

I'm upgrading my remote distributor from 2005 to 2008r2, it is performing transactional replication only. this is a prerequisite to completing an upgrade of the Publisher to 2008r2. Script ...
6
votes
3answers
5k views

Improve performance of sys.dm_db_index_physical_stats

During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on ...
4
votes
1answer
81 views

How to remove a database server from Utility Explorer

I use the Utility Explorer to monitor some of my databases but I forgot to remove one before retiring the server. Normally, you could just right click and select "Remove Managed Instance" but I can't ...
12
votes
3answers
2k views

Does the partition key also have to be part of the primary key?

I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
11
votes
1answer
278 views

How does SQL Server know predicates are correlated?

While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article: ...
5
votes
2answers
142 views

Mechanics of partitioning

Imagine the following hypothetical database structure. Basically it's a top down structure (Country has Orders, Order has OrderLines, OrderLine has ShipLines, etc...). I want to partition my SQL ...
7
votes
5answers
23k views

Restore SQL Server 2012 backup to a SQL Server 2008 database?

Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008? I tried to attach file, it does not work.
2
votes
1answer
201 views

Active Directory domain being shutdown— Do I have to update my DBO accounts?

I manage a SQL Server machine (with SQL Server 2000 and 2005 installed) that was first created on an Active Directory domain that we are transitioning away from and are about to shut down. All of my ...
0
votes
2answers
2k views

Conversion failed when converting date and/or time from character string [closed]

I have a problem with this query: SELECT @dateforaftersixmonth=U_PYMON FROM [@CCS_PYRUNPERIODS] WHERE @STRTEMP_DATE between U_BEGDA and U_ENDDA @STRTEMP_DATE is a datetime variable and ...
0
votes
3answers
248 views

How can I connect to SQL server by just using local ip address

Currently I am connecting to SQL server using 192.168.1.157\MyInstance. I would like to connect to SQL server by just using 192.168.1.157 i.e if I type IP addressin SSMS it should connect properly ...
0
votes
3answers
3k views

Database is not accessible after 'taking it offline' process failed

I was trying to detach the DB when it gave me an error that it is currently in use. Hence, I tried to take my DB offline but it failed saying 'an exception occured while executing a transact SQL ...
4
votes
1answer
101 views

Is there a way to start a script on the publisher when synchronization starts?

I am using merge replication with pull subscriptions and Web Sync. What is the easiest way to start a SQL script on the publication database when a subscriber starts the synchronization process? ...
10
votes
4answers
1k views

Rebuilding Indexes, DB now 10x the size

I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very ...
2
votes
2answers
633 views

Remove Database from SQL Management Studio Database List

I'm working with MS SQL Express 2008 and Visual Studio 2010. If you are attached to a local MDF file how do you remove it from the Databases list without deleting the file? The reason is to purge the ...
3
votes
2answers
116 views

When did a specific database come online?

How can I determine when the state of a specific database switched to online? Background: I want to determine how long the database is in use to find out how reliable sys.dm_db_index_usage_stats ...
9
votes
5answers
881 views

UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL)

I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID ...
11
votes
3answers
1k views

Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?

SQL server 2008 R2 query optimizer puzzle We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same. This is fast, 13 seconds, select * from bigtable1 ...
2
votes
0answers
7k views

SQL Server named instance - can connect remotely, but not locally [closed]

I have an instance of SQL Server on my machine. Machine: Bert01 SQL Server instance: Bert2008 I connect to it using SSMS putting Bert01\Bert2008 in the Server name box, using sa and the . When I ...
1
vote
1answer
120 views

Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instances

I'm developing an inventory system for RDBMS deployments. I'm starting with SQL Server, and later will be pulling in info for Oracle & other RDBMS deployments. When you query [dm_os_windows_info] ...
1
vote
1answer
662 views

Indexed View Slows Replication

I have setup SQL Server transactional replication, and then added indexed views at the subscriber, but the indexed views appear to really slow down the subscriber and almost bringing it to a halt. I ...
4
votes
2answers
82 views

what replication option should I choose?

Just looking for some feedback here, there are lots of replication options out there, and I'd like to try something that those more expert than I have found to be successful. Here's my scenario: 1 ...
3
votes
1answer
110 views

How can I find out which statistics are evaluated by the optimizer?

My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. ...
4
votes
3answers
681 views

Write to Error Log

Using SQL Server 2008 R2: How can I write to the SQL Server Error Log? I have a ROLLBACK statement that I'd like to couple with a statement written to the error log for external monitoring. ...
3
votes
1answer
964 views

How to check connection string in SSMS2012?

I'm connected to database. I use db by Management Studio 2012 Express. Can I check connection string by click something in Management Studio?
5
votes
2answers
303 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 ...
6
votes
2answers
944 views

Do indexes consume memory?

I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is ...
4
votes
3answers
435 views

Restore a SQL Server database to an offline copy

I have SQL Server Management Studio. I need to access a backup file (*.bak). How do I create a new database engine that is OFFLINE (running from my PC not the server) and then restore the database ...
3
votes
1answer
93 views

Directly acessing the NULL bitmap of a row in SQL Server

I was reading the question Checking a wide table for nulls and I'm wondering if it's possible to somehow directly access the NULL bitmap of a row to fast check if a row contains NULL values. Would ...
5
votes
3answers
636 views

How to transfer files at remote location using sql server job?

I have a job configured in which it transfers the file at remote location. My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on ...
7
votes
2answers
3k views

FIFO queue table for multiple workers in SQL Server

I was attempting to answer the following stackoverflow question: What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances? ...
4
votes
2answers
159 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
8
votes
3answers
314 views

SQL Server query slow when paginated

I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012: select Id FROM dbo.Person WHERE CONTAINS(Name, '"John" AND "Smith"') order by Name Executing this query alone ...
0
votes
6answers
6k views

How to recover deleted/updated record in sql server or mysql?

I want to know that is there any way that I can can recover the deleted or updated record in the sqlserver and mysql as well?
2
votes
2answers
2k views

All pull subscriptions cannot connect to Distributor

recently all my PULL subscriptions have this message in the synchronization status. :- The process could not connect to Distributor 'DISTRIBUTIONDB' This is only happening with the PULL ...
6
votes
1answer
189 views

Large SQL server advice

Copied my question from StackOverflow after advice from a fellow user: I'm dealing with a quite large SQL Server database and encountering a few problems. The database is quite new and I've been ...
3
votes
2answers
570 views

Parallel Statistics Update

In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I ...
3
votes
1answer
387 views

Wrong behavior in Backups

Checking my Native backup this morning something wierd happened. I will describe the problem here. 1) The msdb table backup set shows me the backup was done successfully. 2) The errorlog shows me ...
3
votes
1answer
142 views

Creating User for a single contained database

I am pretty new working with MSSQL and have 2012 version at the moment. What I'm trying to do is create a user for a contained database, and give that user access only to that database. I will invoke ...