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)

5
votes
7answers
2k views

Recommended tools for SQL Server Developers & DBA [closed]

I was thinking what tools are recommended for SQL Server Developer & DBAs? The list can comprise of free & paid tools which could be helpful to any SQL Server Guy (Developer/DBA). The tool ...
4
votes
3answers
5k views

How does one scale SQL Server 2008 or 2012?

How does one scale SQL Server 2008 (or 2012)? At its basic, I understand there are two options: Scale up: If CPU bound, I can clearly see going from 1 CPU core to 2 to 4. Or if RAM usage rockets, ...
2
votes
4answers
952 views

SQL Server Indexing Performance in Unique/Duplicate column

It is said that if we create index on a column with more uniqueness than performance of that index will be more. But I believe that whether it is unique or not it will occupy same no. of blocks than ...
1
vote
0answers
439 views

time interval period in sql [closed]

How to display the time period of the most widely in use in ms sql server I have purchase data, such as: hours the number of purchases 10:00 2 12:00 4 13:00 5 14:00 1 ...
3
votes
3answers
2k views

How do I temporarily disable sp_send_dbmail?

I have just taken over responsibility for a SQL 2000 system which made heavy use of xp_sendmail. We are in the process of upgrading to SQL Server 2008 R2 and I have implemented sp_send_dbmail. ...
1
vote
2answers
85 views

Add more memory to Server?

I have a Dell 2900 Server with 16 GB of memory running SQL Server 2005. All 3 of the databases I have configured on the Server all have the maximum memory allocated to each Database. The primary ...
15
votes
3answers
4k views

Determining how a schema change occurred?

Something bad happened yesterday. A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this ...
3
votes
2answers
294 views

SQL Server Configuration Manager changes effected via Central Management Server

When using Central Management Server (CMS), under Central Management Server Actions, SQL Server Configuration Manager (SSCM) is an option. When launched, the GUI reflects the information of the ...
22
votes
4answers
2k views

Is NOLOCK always bad?

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
4
votes
1answer
2k views

How do you create a relationship to a non-primary key in SQL Server?

I have a Users table which has two columns a primary key called UserID and another column called UserName. UserID (int) PK UserName (varchar(256) They are both unique but I decided for reasons to ...
2
votes
1answer
2k views

Copying the database from SQL Server to PostgreSQL

I currently have SQL Server 2005 and I want to make a copy of the entire database into PostgreSQL. I don't want to migrate to new PostgreSQL but just a entire copy. I almost have more than 1 TB of ...
1
vote
2answers
263 views

What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table?

I have a table p with a column points. There are a couple of million of records in the table and i need to regularly calculate and update the points for each row. This process takes a couple of ...
1
vote
1answer
3k views

Why are breakpoints not being hit in SQL Server Management Studio?

SQL Server Management Studio allows debugging of SQL (breakpoints and stepping through the SQL). It is possible to step in to a stored proc being called and to see a call stack. However I cannot ...
2
votes
1answer
523 views

SQL Server 2008 r2: What events/queries hit tempdb

We had a slowdown on a db server this morning. Checking the logs, the only thing that happened during the 2-minute slowdown worth noting was an object being created and deleted in tempdb over the span ...
2
votes
2answers
448 views

SQL Server 2008 R2 Query For Failed Email Notifications

Is there a SQL query I can run to see if there has been any failed attempts to send database mail in the last X Minutes?
4
votes
2answers
219 views

Backup VLDB replicated database in SQL Server?

I have a problem with my backup strategy. Currently I'm doing a Full Backup every night and transaction log backup every 2 hours to TAPE. This used to work without problems, but the size of the DB ...
4
votes
3answers
881 views

Query the Owner of SQL Server 2008 R2 Jobs

Is there a way to query the owner of all jobs in SQL Server 2008 R2? I discovered when maintenance plans are edited the owner gets changed, so I want to make sure they are all owned by sa.
7
votes
1answer
7k views

How can I recover data from the LDF file?

We are using SQL 2005 Express Edition. We want to read the data from the LDF file in order to restore some deleted records. We tried using a trial version of ApexSQL which helped us a lot. Instead of ...
6
votes
4answers
221 views

Query Performance depending on parameter 21seconds vs. > 14hours

I have the following query: SELECT DISTINCT DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))), [DATE], DATEPART(YY,[DATE]), ...
0
votes
2answers
625 views

Mirroring after restoring the database on a new machine

We've had alot of problems with one of our database servers and two days ago the call was made to change a disk and reinstall the OS. Now this server functioned as part of a mirroring setup. Basically ...
4
votes
1answer
1k views

Alternatives to LIKE and FTS for mid name search

I have a question about what are the alternatives for LIKE '%%' and Full-Text Search when it comes to searching organisation names, firstnames, surnames, etc. The LIKE '%%' causes table scan, however ...
1
vote
1answer
107 views

Log all user error messages for retrospective troubleshooting

Suppose I want to log all severity 16 (and higher) user error messages to a database table in order to allow some retrospective application troubleshooting (since the answer to "Can you send me the ...
1
vote
2answers
135 views

why doesn't time for a simple update scale linearly with the number of records?

I am running some performance tests and I started with a dataset of 900.000 records the update took 10 seconds or about 90000 records per second. Then I increased the number of records to 9.000.000 ...
6
votes
4answers
2k views

How does SQL Server reduce index fragmentation?

If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes? MSDN suggests that if an index is over 30% fragmented, it is recommended to run ...
3
votes
1answer
565 views

Index fragmentation increase as more row being deleted

I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday. Index fragmentation was low. This table has high transaction throughput. ...
1
vote
1answer
297 views

Buffer Pool and Multiple instances of SQL Server

If you have multiple instances of SQL Server running on the same windows server system, a) Does buffer pool is shared amount multiple instances? b) What is the effect of running DBCC ...
2
votes
1answer
2k views

Behavior of Varchar with spaces at the end

When I use a Varchar with spaces it ignores the spaces at the end. ex: declare @X varchar(50) This... set @X= 'John' ...is the same as... set @X= 'John ' It considers these to be ...
-1
votes
1answer
271 views

Does SQL Server full restore set the differential base?

Differential backups are taken as the difference between the current state, and the last full backup called the "differential base". Does restoring a full backup reset the differential base? If so, ...
4
votes
1answer
2k views

Converting Microsoft SQL Server collation to UTF 8

I've a Rails-Application, which reads data from a Microsoft SQL Server 2008 R2 Database. All umlauts are represented by a '?', so I checked the encoding of the database. The encoding is 'Latin 1'. ...
2
votes
2answers
407 views

Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008? The case here is to set the row and page locks to false on the table. The lock is in effect during a series ...
1
vote
2answers
544 views

SQL Server 2008 R2 - user disk quota

Is it possible to apply restrictions on how much data a user / login may store in a database? I found no way to apply such restrictions to schemas, for instance. If there is no built-in mechanism ...
2
votes
2answers
481 views

Unable to locate database.mdf in production server

1) I want to attach a database from my development server to my production server. The database ESpiceHerbs from my development server is located at: C:\Program Files\Microsoft SQL ...
4
votes
1answer
441 views

database design issue

This is my first time on this beautiful content rich site. I have a sql server 2008 database, but am not too experienced with good relational database design. My project is this: I have a school ...
11
votes
1answer
1k views

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
9
votes
1answer
3k views

Are there any risks to granting users SQL Server SHOWPLAN permission?

I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
3
votes
1answer
250 views

What do I need to sync a local site office SQL Server with the Head Office SQL Server

The company has a desktop application powered by SQL Server from the head office that is dependent on an internet connection to our remote sites. Usually when we move to a new site there is a good ...
3
votes
3answers
1k views

Does ORDER BY on a clustered key effect performance?

Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey so I additionally always ORDER BY PrimaryKey in all queries. Does this ORDER BY affect ...
3
votes
2answers
1k views

Max number of tables (objects) in SQL Server editions (compact, express)

What are the maximum number of tables that you can have in: SQL CE4 SQL 2008 R2 Express Numbers here http://msdn.microsoft.com/en-us/library/ms143432.aspx I guess is for SQL Server 2008, but are ...
0
votes
3answers
449 views

SQL Server : primary keys advice to my whitepaper needed

I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should ...
1
vote
4answers
2k views

SQL Server error log monitoring

In SQL server instance(s) I manage; I see a lot of activity and messages in SQL server error log for e.g. backup or transaction log backup completed etc.. We have quite a few databases in one instance ...
1
vote
1answer
1k views

SQL Server 2012 Create index with Sort In TempDb On - Gains False?

If I create an index e.g Create nonclustered index(Path, Value, Id) with(sort_in_tempdb=on); It doesn't fail but when looking at the index in SMMS, it says False. Any ideas why? EDIT: Can not find ...
3
votes
4answers
150 views

What is the optimal set of database maintenance tasks for high volume of transactions

I have a database that has a high volume of data being inserted and deleted each week. Every week there are approximately one million rows of data being deleted and inserted. Each month the base ...
78
votes
17answers
14k views

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the ...
8
votes
2answers
3k views

How to create an Event Notification that runs a Job/procedure when mirroring state changes

I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL? Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to ...
1
vote
4answers
442 views

Update column of a large set of records with a calculated value

I am trying to figure out what a good way would be to write the pseudo query below in TSQL taking into account that there will be about 50 actualResult records and 100.000 prediction records per ...
6
votes
1answer
158 views

Was I right with locking?

Regarding the this question I want to ask Comparing two queries: (1) DELETE dbA.dbo.tableA FROM dbA.dbo.tableA a WITH(NOLOCK) JOIN dbB.dbo.tableB b WITH(NOLOCK) ON b.colA = a.colA AND ...
1
vote
0answers
322 views

How to manage SQL Server Log file? [closed]

I have an application build on SharePoint 2007 platform, and currently I encounter a common issue where the Database Log file growth rapidly and fill up the hard disk space. In my situation, I wanted ...
0
votes
1answer
388 views

which DBMS should i go for? [closed]

I am interested in learning a DBMS. As a beginner which DBMS should i go for ? I know few DBMS by name like Oracle,Sql Server,My Sql . How are these DBMS different from each other ? As a beginner ...
2
votes
2answers
899 views

Best practices for creating partition schemes and functions

I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning. My main question is: Should I use the same Partition Scheme and Function for ...
4
votes
1answer
235 views

Is there a way to make SQL Server's Database Tuning Advisor use more than one core?

I'm using SQL Server's Database Engine Tuning Advisor to generate recommendations for performance improvements on my database server. For the "consuming workload" step, it appears to use all ...