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)

6
votes
2answers
901 views

Sql Server Management Studio for 2008 Express?

I just installed Win7 on a fresh laptop and want to put a lightweight MS Sql Sever on it. VS2010 already installed SS2008 Express. So now I just need a management studio for it. What would you ...
2
votes
2answers
1k views

Insert Into MySQL over linked server when a medium text is the last column breaks Int fields

I have a table with ID INT(16) StateName VARCHAR(50) Details MEDIUMTEXT When I do an insert over the linked server connection: Insert Into MySQL...tblState (ID, StateName, Details) Values ...
3
votes
1answer
105 views

Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view?

Sorry for the long question, but let me give you my scenario. I have a DB that houses data exported from another DB. The containing tables are copies from the other (non-SQL) database. Each column ...
28
votes
5answers
5k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem) After working with ...
15
votes
4answers
24k views

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
4
votes
2answers
204 views

Best way(s) to time ad hoc queries and stored procedures

What are the most accepted ways to time your queries, whether they are ad hoc or stored procedures? Is it more accurate to use SQL Profiler than SET STATISTICS TIME ON?
3
votes
1answer
173 views

What are some efficient ways in using COUNT(*) with WHERE

Instead of calling COUNT(*) with a WHERE clause, is there anything one can do to make this work more efficiently on the server? I would like to get this count on complicated joins, just wondering the ...
1
vote
1answer
1k views

Conversion of varchar data to datetime failing

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering As part of this effort, we are taking data from the base Entity table and ...
4
votes
2answers
816 views

Will creating partitions reduce locking and how do we implement this in sql-server?

A client wants our application to process more data faster so arranged a meeting with their dba to discuss options. This application generates quite a lot of data that is used for reporting. Before ...
0
votes
3answers
309 views

Does running a COUNT() statement using NOCOUNT ON; consume much

On millions of rows.. Is it worth caching the total amount or turning NOCOUNT OFF; instead of requesting it each time on that much data? Sql Server 2008 R2 Also, is running a COUNT() statement on ...
5
votes
3answers
312 views

Can this method for unit testing a stored procedure be improved?

When I have written unit tests for my stored procedures, they usually look something like this: /* Unit test for stored procedure which runs a query */ /* Define #temp table with structure of query ...
3
votes
2answers
2k views

sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups

These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing? Here is how I proved it is the same data: select ...
13
votes
2answers
7k views

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other ...
6
votes
2answers
276 views

Row padding to get more performance

I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance: Because rows are small (many fit a page) multiple locks may compete for one ...
6
votes
2answers
219 views

SQL Server Internal Inconsistency on GROUP BY

I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS. Now I can select records from a table-valued function to retrieve sales records, but using a ...
2
votes
1answer
232 views

Where can I find information on shredding xml with .nodes?

I recently saw this answer on SO which uses the "local-name" value while shredding xml using .nodes(). I've never seen this before: select T.N.value('local-name(.)', 'varchar(128)') as Name, ...
5
votes
3answers
1k views

Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my ...
15
votes
2answers
17k views

Changing column width

Will increasing a column (nvarchar) width necessarily drop the table? In other words, can the width be changed in a production environment with active users? I figured that if the size is increasing ...
5
votes
2answers
7k views

can I resolve a deadlock with the rowlock hint?

I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything. It looks like the part of the stored proc that hit a deadlock was ...
3
votes
3answers
269 views

How do I load just the data from SQL Server 2005 backup into a 2008 R2 database?

I created a 2008 native database and I have the structures in place but need to load data from a 2005 backup.
10
votes
1answer
645 views

Strange behaviour with Computed Columns in SQL-Server

Whilst reading through my 70-433 exam book, I thought of something that I can see not working, yet I believe it does. The passage read something like: The column must also be marked as PERSISTED, ...
4
votes
2answers
4k views

What is the meaning of Microsoft Compatibility Level?

Setting cmptlevel to 80 on a SQL-Server 2005 inhibits the use of PIVOT and APPLY, but doesn't affect the use of Analytical Functions. Is there any rational behind this?
2
votes
1answer
5k views

Running an SSIS package without the SSIS service installed

On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service. If I have an SSIS package on that server, can I still schedule to run it without SSIS service? I tried ...
2
votes
1answer
3k views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...
2
votes
3answers
353 views

VLDB - SQL SERVER versus VFoxPro

I must create an DBF for an application which will collect data from 5 millions sensors on every minute, so I will have 5 millions records every minute. I was thinking to use SQL server because I ...
6
votes
4answers
344 views

What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.] Question 1 What ...
2
votes
1answer
654 views

Trusted Constraints and NOT FOR REPLICATION

Version and Build: SQL Server 2005 SP4 (9.0.5000) As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic. I don't know if there is really a ...
4
votes
1answer
579 views

T-sql: Select most specific match from table

Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem. Here's the scenario: Users can set up service instructions to be attached to a work order ...
8
votes
2answers
4k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
10
votes
1answer
2k views

Optimising BCP performance for BLOB data

I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
6
votes
2answers
3k views

SQL Server: how to avoid sysadmin rights but give adequate rights

I have a collegue who want to get an access to one SQL Server 2008 instance. I'll have to give him rights to this instance. He should have rights to do is e.g. Add and modify server logins Add and ...
29
votes
4answers
21k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
2
votes
3answers
16k views

SQL Server How to Set up Maintenance plan to shrink log

So I have a log file which grows very large after I run an SSIS job daily, sometimes up to 1220MB. In my database options I have set my recovery model to "FULL". When it is to FULL and I perfrom ...
3
votes
1answer
230 views

Is SQL Server Replication transactionally 'safe' across all published tables?

We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 ...
6
votes
4answers
192 views

SQL Server: what is the reason to favor customized database roles over fixed ones?

This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over ...
50
votes
5answers
16k views

When to use TINYINT over INT?

In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data. For example, it's ...
3
votes
3answers
4k views

SQL Server: does ddladmin rights contain datareader and datawriter rights to the database?

Does db_ddladmin fixed database role include db_datareader and db_datawriter rights, so that you don't have to grant those rights explicitly?
5
votes
3answers
804 views

How do I row count into groups?

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
1
vote
1answer
663 views

SQL Server Service Broker and Deadlocks

I am reading through SQL server books online for the first team whilst learning how to use the service broker and I came across this statement: The application that implements the service reads ...
3
votes
4answers
132 views

How implement a cancel attribute in a table?

Which is the best way to implement a canceled attribute in a database table, provided that less than say 3 % of the rows are canceled. For canceled data I want to store the additional information ...
2
votes
1answer
412 views

Can't create Windows Login's on a case sensitive SQL Server 2005 Instance - MSG 15401

I need to create 3 logins on a SQL Server 2005 instance: DOMAIN\aaa DOMAIN\bbb DOMAIN\ccc However two of them (aaa and bbb) fail with the following error: Msg 15401, Level 16, State 1, Line 5 ...
8
votes
5answers
22k views

Restoring a backup to an older version of SQL Server

When trying to restore a backup to a SQL Server Express 2008 database I got the following error: Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) ...
10
votes
4answers
4k views

Is Oracle more demanding environment for a DBA than SQL Server? [closed]

I don't know Oracle at all but I have heard Oracle DBA's saying that working with Oracle (as a DBA) is "harder" and more difficult and more demanding than working with SQL Server. Does this statement ...
9
votes
3answers
312 views

Query Performance Tuning

When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters? Do you run the query and view the actual execution plan? If so, what ...
7
votes
2answers
353 views

Is there any way to efficiently perform the equivalent of DENSE_RANK in MongoDB?

SQL Server and Oracle both have DENSE_RANK functions. Is there a way to do something similar in MongoDB without having to resort to MapReduce? In other words, suppose you have a T-SQL select clause ...
7
votes
4answers
1k views

SQL Server: what should be done when a process is blocking another process?

I'm an accidental DBA and still learning. When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done? Should I simply kill those processes or is ...
10
votes
4answers
3k views

What tools are there to generate test data for SQL Server?

As you can see from another question of mine, generating test data is my theme right now. At this point, I'm still generating my test data by hand. However, this process always generates small ...
4
votes
1answer
792 views

SELECT INTO creates a new table?

I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three ...
5
votes
2answers
165 views

Database placement on abstract storage structure

Often times we all hear the same thing. The recommendations on where to put OLAP/OLTP databases, where to put the tempdb, where to put transaction logs...etc. But say you are in an environment where ...
2
votes
1answer
2k views

Does msmdpump.dll pass the authentication info to SSAS?

I'm using msmdpump.dll with basic authentication to access a SSAS OLAP cube from the internet. Does this method pass the auth info to SSAS so if I put permissions on the cube they get applied ...