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)

14
votes
1answer
9k views

How do I read Query Cost, and is it always a percentage?

I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric. According to any documentation I could find via ...
1
vote
1answer
179 views

Use SSMS to Create Database Diagrams but not Database Objects

I'm looking to be able to create a database diagram without actually creating database objects. It seems like the best way to do this would be directly through SSMS. My only question is, is there a ...
1
vote
1answer
167 views

How should row-specific metadata be created handled for an outer join view?

I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when ...
2
votes
4answers
495 views

Data Change Audit Plan

My company is changing from a distributed Access Database model to using a centralized SQL Database. The datatables were designed such that all of the tables have a modified date. In discussion it ...
1
vote
3answers
537 views

Upgrading to SQL Server 2008 R2 but downgrading the edition - will this cause problems?

I am looking at upgrading our SQL Servers from 2005 Enterprise edition to SQL Server 2008 R2 Standard edition. Obviously, the upgrade to 2008 R2 gives us more features even in Standard edition, but I ...
1
vote
3answers
613 views

why locks a table will improve performance during transaction?

I just started to read about table lock in SQL (from this page) It saids: table lock may reduce the overhead of acquiring a large number of row or page locks and save overall locking time ...
3
votes
3answers
3k views

Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?

At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields. I have a web interface that interacts with the sql scheduling system. ...
1
vote
1answer
11k views

In which folder / directory does SQL 2008 store files?

Where can I set the folder to store a database in when I create a new Database in the Microsoft SQL 2008 Server?
1
vote
3answers
3k views

BACKUP failed to complete - with spaces, for no reason

Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
2
votes
2answers
1k views

Tuning SQL Server Performance

While profiling my application against SQL Server 2008 running against a JDBC Application most of the time consumed were by following COMMIT TRAN FETCH API_CURSOR & exec sp_cursorfetch I ...
1
vote
2answers
213 views

Table value functions vs UDF SQL Server

I am currently watching this video on Entity Framework and about 15 minutes in, the speaker says that table value functions occasionally out perform UDF's. My question is in what scenario do TVF's ...
5
votes
1answer
294 views

bcp database migration

I want one server to migrate the database to another server. On another server database already exists, then ist all the tables, but they are empty and I want to fill. For the filling, I want to use ...
20
votes
2answers
3k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...
6
votes
4answers
519 views

Find the first gap in an aggregation of integers in SQL Server

Let's say I have a table called dbo.GroupAssignment. GroupID | Rank ------------------ 1 1 1 2 1 3 2 1 2 3 2 4 3 2 3 4 3 5 The PK is GroupID, Rank. Normally, the ranks ...
4
votes
3answers
3k views

NULLs in a composite primary key - SQL Server

I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
0
votes
1answer
459 views

Extracting Extended Entity Relationship Diagram from a Relational Database

I am researching how to extract an Extended Entity Relationship (EER) diagram from a Relational Database. I'm using Visual Studio 2010 and SQL Server 2008. I've found tools for Entity Relationship ...
1
vote
3answers
1k views

How do I upload the .mdf file and associated .ldf file from visual studio to a host using WebsitePanel?

I have created an ASP.NET MVC 3 project in Visual Studio and the database is a SQL Server Express database BeerRecommenderContext.mdf with a log file BeerRecommenderContext.ldf. I can upload these to ...
3
votes
2answers
1k views

Temporarily disable checking of constraints

I'm bulk inserting rows into a database and I'm finding (through the query analyser) that the foreign key constraints are taking about 20% of the insertion time. I've tried creating a separate index ...
0
votes
1answer
2k views

Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should ...
4
votes
1answer
930 views

Not all members in a Windows group have permissions

I ran into a bit of an odd situation. Let's say I have a domain group [DMN\Group] which has been created as a login on my server and a user in the database. One of the members in that group is not ...
2
votes
1answer
170 views

Migrate Data and/or Schema from DBv1 to DBv2

I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null ...
1
vote
1answer
1k views

Partial restore to a different database

I have a database with 3 filegroups: PRIMARY, AP, and GL. I did a full database backup. I did a partial restore of just the PRIMARY and AP files successfully using this command: -- Partial Backup ...
1
vote
1answer
1k views

Partial Backup / Restore Sql Server Simple Recovery

I have a scenario where we are using SQL Server 2008 R2 with FileStream. I have partitioned the table where our files live into multiple filegroups. What I want to be able to do is backup our ...
3
votes
1answer
1k views

Restoring SQL Server Database Command Line Error

I have a restore database script in a SQL file, and I'm trying to restore a backup generated from one DB to a new DB (that exists). That SQL file is being executed by SQLCMD on the command line. The ...
1
vote
1answer
566 views

Partial backup and restore using filegroups

I have a single database that I put all tables into with multiple schemas for the different products I have. I know I can't backup and restore just one schema, but if I move all tables for a schema ...
0
votes
1answer
242 views

Remote RESTORE of database through SSMS

How can I RESTORE a database through SQL Server Management Studio by connecting "remotely" to it. If I know the "IP address" of the target machine what else do I need to know? If the port number is ...
0
votes
1answer
88 views

Remote update/restore of database through SSMS

How can I update or restore a database through SQL Server Management Studio by connecting "remotely" to it? If I know the "IP address" of machine, what else do I need to do? If a port number is ...
3
votes
2answers
909 views

SQL Server Deadlock Log Entries

We're analysing a deadlock situation on a SQL Server 2005 database and have set the trace flags 1204 and 1222. In the resulting logs we're seeing the following line repeatedly: "Log Viewer could not ...
1
vote
1answer
1k views

Isn't a tab (char(9) ) whitespace in the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3)?

If there is any sane reason to ignore trailing blanks in varchar comparisons, why doesn't that apply to tab-characters too ? On SQL Server I get SELECT case when '' = ' ' then 'true' else ...
18
votes
2answers
4k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
3
votes
2answers
2k views

How to restore SQL Server database just by dbName and physical path

I have several database backups and I want to restore all of them to a server. Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and ...
-2
votes
1answer
193 views

SQL Server reports for management

I have been placed in charged of overseeing our DB and web servers (SQL Server 2005/2008.) Not being a technical person, what reports should I request on a weekly/monthly basis to asses the health of ...
0
votes
2answers
6k views

Joining two tables on multiple criteria

I have two tables products and positions. products: id BIGINT PRIMARY KEY, product VARCHAR(100), criterium1 VARCHAR(100), criterium2 VARCHAR(100) positions: id BIGINT PRIMARY KEY, position ...
4
votes
1answer
3k views

How to write a check constraint to disallow empty varchar fields?

On SQL Server not nullable varchar columns can hold empty strings. Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL. If you design a database schema suitable for both RDBMS ...
3
votes
2answers
10k views

How to install SQL Server Management Console in 2008 Express Version?

I started a dozen attempts to install the SQL Server Management Studio for the 2008 Express Version of SQL Server. What i use: Windows Vista 2007 SP 2 What i did: a) I downloaded the install file ...
4
votes
2answers
3k views

SQL Server 2012 RC installation failing

I'm trying to test out the new SS 2012 RC, but I'm having problems with the installation. My installation stops a ways in saying that the resource needed cannot be found. I'm using the ISO from MSDN ...
2
votes
1answer
552 views

Will Entity Framework and/or Linq-To-SQL queries show up as “Adhoc Queries” in SSMS' “Server Dashboard” Report?

Its pretty much all in the title. I have a SQL Server which is showing that 90%+ of CPU Usage and Logical IO Performed is due to "Adhoc Queries" The rest rest of the usage shows up under the actual DB ...
2
votes
0answers
2k views

Could not open File Control Bank (FCB) for invalid file ID X when restoring from snapshot

We have a scenario in which we, during integration tests, make a backup of an existing database and restore it under a new name. We then create a snapshot of our "new" database. After each test we ...
1
vote
2answers
1k views

SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.) Both databases are identical with many foreign-key constraints on tables. ...
1
vote
1answer
2k views

Can't restore database: A transport-level error has occurred when receiving results from the server

This is not a good day for me. I have a backup (master.bak) for SQL Server 2005 Express SP4 (v.9.0.500) that I want to restore to my master db instance. I am running SQL Server in single-user mode ...
3
votes
2answers
4k views

Shared and IX locks causing deadlock (Sql server)

In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table). Now, we insert values into these 2 tables using the below 2 queries: INSERT ...
15
votes
2answers
1k views

Why did creating this new index improve performance so much when existing index included all columns in new index?

I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems The query in question is complicated so I'm ...
7
votes
4answers
5k views

Do SQL Server stored procedures cache data results?

I have heard this from friends, but I never investigated whether this is true. Is it true that the data results of an executed query are stored in cache? I mean, if I have a stored procedure like : ...
1
vote
2answers
227 views

SQL Server RandomNumbers Stored Procedure Error

I tried to create a stored procedure for generating random numbers but it doesn't work; the random numbers are identical in every record. CREATE PROCEDURE Update_Driver_Position @TempName ...
6
votes
1answer
259 views

32-bit or 64-bit for development and deployment?

I'm creating a Windows desktop application and want to include a database. I've chosen SQL Server Compact 4 but when I download it there are two versions (32-bit and 64-bit). My development machine is ...
4
votes
1answer
1k views

How are bit fields sorted for an index?

I am building a compound index, and I'm not 100% sure which way to include a bit field that is part of the table. The index will be for something similar to a list of forum topics, where I want the ...
6
votes
1answer
170 views

On-the-fly query/command modification

I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly ...
3
votes
5answers
2k views

Track deleted rows/records

A project has the requirement that it shall not be possible to lose data from certain tables. That is, if I "delete" a row, that row's information should somehow be preserved. Additional information ...
3
votes
3answers
5k views

Why so many logical reads?

I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server. SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK) JOIN ...
2
votes
1answer
3k views

Disable SQL Server Log Shipping to nonexistent destination

I am administering a SQL Server 2008 instance in a dev environment. At some point before I became an administrator, someone enabled log shipping from one of the databases on this dev instance to ...