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)

2
votes
1answer
6k 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
358 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
354 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
684 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
608 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 ...
33
votes
4answers
23k 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
18k 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
236 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
193 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 ...
51
votes
5answers
18k 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
843 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
698 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
147 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
463 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
24k 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
5k 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
316 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
380 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 ...
12
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
799 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
166 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
1k 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 ...
2
votes
2answers
204 views

Extract specific backupfiles from SQL Server 2008 backup

I have a big .bak file containing a full backup and some transaction log backups. Is it possible to extract only the transaction log backups so I can copy them to another PC? (the full backup has ...
10
votes
2answers
6k views

What is blocking and how does it happen?

I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
8
votes
1answer
697 views

Generating large strings for test data

I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
11
votes
2answers
1k views

SQL Server: Has anyone used the large-page allocations tuning option?

Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any ...
6
votes
2answers
1k views

powershell to get sql server memory counters and show value

I am writing a powershell script to capture the following SQL server counters: SQL Server: Memory Manager: Total Server Memory (KB) SQL Server: Memory Manager: Target Server Memory (KB) My ...
5
votes
3answers
723 views

Why is Row Overflow not working on SQL Server 2005

I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an ...
9
votes
2answers
4k views

How can I have SQL Server email me the error details when a job fails?

SQL Server lets you configure a job to send email alerts when it fails. This is a simple and effective way to monitor your jobs. However, these alerts do not include any detail--just a success or ...
14
votes
7answers
351 views

Which of these table designs is better for performance?

I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this. Here's what I know Company ...
4
votes
2answers
130 views

Performance of SQL Server indexing

I want to see the performance differences between an indexed vs non-indexed column when running the same query. The table I've created (FileCollection) is very simple and has the following 3 columns ...
3
votes
1answer
716 views

SQL Server Embedded Edition vs SQLite

Can anybody shed some light on how these SQL products compare? No need for essays, only highlights such as "X supports this, while Y doesn't". How is efficiency affected as the database grows ...
3
votes
1answer
1k views

Getting error on SQL Server

I am getting these error messages one after the other every week on Win 2008 and SQL 2008 R2. MAX DOP is set at 1. Any idea how I can solve this? Thanks. DESCRIPTION: A fatal error occurred ...
6
votes
2answers
293 views

How can I secure my database code?

We have a lot of proprietary database code that is simply encrypted using the "WITH ENCRYPTION" option. Unfortunately, this is a very weak method of securing database code. Is there any better way ...
16
votes
3answers
5k views

Why is `SELECT @@IDENTITY` returning a decimal?

I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application. INSERT INTO Customers ( Type, Name, Address, ...
4
votes
1answer
155 views

How big of an impact does NUMA really have?

I was reading up on Non-Uniform Memory Access (NUMA) and I was wondering how much of a difference it can really make? Another way to word that, what level of throughput is required before this ...
6
votes
1answer
3k views

Database Users and Server Logins and the Mapping between them

I have an instance of SQL Server 2008 R2 Express Edition at home and another on a server at a clients premises which I am using to develop an application using Access as the front end. This ...
20
votes
1answer
541 views

Reporting Service and Application Role

First poster, long time lurker here. What is the best way to activate application role in a report ? I've tried different things and so far the only method that works is to embed the call to the ...
7
votes
2answers
1k views

DTA Recommends to CREATE STATISTICS

I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file. My question is, how exactly ...
5
votes
2answers
140 views

In SQL Server 2005 how would I determine the minimal permissions a user requires?

I'm the accidental DBA of my group and I've inherited a database that follows a number of bad practices. Currently every login is a member of the sysadmin group. What is the easiest way to determine ...
10
votes
3answers
407 views

Are heaps considered an index structure or are they strictly a table structure without index?

Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505 Heaps: Are they considered an index structure or are they strictly a table structure without index?
7
votes
1answer
1k views

Understanding SQL Server permissions

I am rooting around in SQL Server Management Studio against my instance of SQL Server 2008 R2 Express Edition. I am trying to understand how the permissions work. What I can see is (via the ...
7
votes
3answers
365 views

When are procedural queries absolutely necessary?

I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not ...
1
vote
2answers
3k views

Getting current date as part of an Stored Procedure

I have the following SP: SELECT moncallAdd.FirstListing, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart, ...