Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

2
votes
1answer
585 views

DB Integrity Check Lock Database

Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of ...
2
votes
1answer
13k views

SQL query to convert columns into rows

I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005. my input Data ONAY_ID sip_RECno KULLANICI ONAY 1 1 ...
5
votes
1answer
14k views

How to get the foreign and primary key details for a table in SQL Server 2008

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys. Is ...
0
votes
1answer
193 views

Grouping stock in 6 month ranges

I have been pulling the total stock within the first 6 months. How can I pull the total stock within the last 6 months in the same query? Also I want the result of the query as shown below: Stock ...
5
votes
1answer
3k views

Which workarounds exists to missing relative path feature of SQLCMD :r?

I'm not the first to notice the lack of this feature. But I wonder if there are some useful workarounds. I want to deploy a main script and some depended scripts in the same folder. BTW Is there a ...
6
votes
2answers
446 views

What is an advantage or disadvantage for not upgrading database server?

I have a legacy database server. The person who wrote it is long gone. There is no documentation at all just some comments in the code. Luckily, it is not that complicate but if I want to fix it (...
6
votes
1answer
849 views

What makes an OLTP database an OLTP database?

This is a pretty broad question. I know the definition of OLTP. But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that ...
54
votes
7answers
49k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
2
votes
2answers
405 views

Indexing of Data Warehouse tables?

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
2
votes
3answers
9k views

ON DUPLICATE KEY UPDATE question

I'm trying to compare data from two systems. The basic idea is: Create Temp Table. Insert Table1 Data. Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected. Filter where first ...
0
votes
1answer
146 views

What would cause the speed difference between servers?

I have this query on my dev server: select snStudyNumber as ProjectNumber from CDB.cdb.Quotes q inner join CDB.cdb.LineItems l on q.PK_quID=l.FK_quID inner join CDB.cdb....
3
votes
3answers
5k views

Backup Compression in SQL Server 2005

I noticed that simply zipping my backup files reduces the space used by 70% My idea was to, initially, backup the databases to a local server; then, using Bacula or Cobian (I don't have a budget), ...
0
votes
2answers
1k views

Upgrading Instances with Mirroring

If you want to upgrade or install a patch on two separate instances that house both the principal and mirrored database, how can you go about that? If the database that is being mirrored needs to be ...
5
votes
2answers
5k views

Changing Domain on SQL Server

I currently have some SQL Servers that sit on a sub-domain. We are eliminating that sub-domain and moving everything to the regular domain. As far as SQL Server configuration goes, is there anything ...
5
votes
2answers
13k views

Minimum requirements to run Sql Server BCP.exe utility

I am working with a client who needs to do a data import to a remote SQL Server (2008) instance. The machine that the import will be done from cannot have SQL Server installed on it. Can the BCP.exe ...
2
votes
2answers
247 views

What is an industry standard method for verifying openquery connection is open prior to running queries?

I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community. It could very well cross over into stackoverflow as well. I'm seeking an ...
15
votes
6answers
65k views

How to drop multiple tables with common prefix in one query?

I'm using Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query? something like that table names: LG_001_01_STLINE, LG_001_02_STFICHE
24
votes
2answers
17k views

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added ...
8
votes
1answer
2k views

How does table compression affect SQL Server's buffer pool?

I'm curious as to how SQL Server's buffer pool is affected by compressed data tables. Is the data in memory compressed, just as it is on disk, or is it fully decompressed? If the data is stored ...
7
votes
2answers
12k views

How can I backup & restore a single FILEGROUP in Sql Server 2008

Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup. Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:- ...
4
votes
2answers
421 views

Data Model For Summarizing Student Info

I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame. I could certainly go retrieve the count from the attendance ...
2
votes
1answer
519 views

Resource Waits: Mathematical link between Wait Time and Average Waiter Count

Just curious, per BOL Wait Time (ms/sec) is the wait time in milliseconds for all tasks waiting for one or more resources in the category since last update. Average Waiter Count is the number of ...
40
votes
4answers
26k views

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
4
votes
1answer
211 views

How can a key lookup with zero executions and zero actual rows take time?

I have a query that is taking about 10 seconds to run and I am trying to optimize it. All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the ...
16
votes
2answers
22k views

Can I enable SQLCMD mode from inside a script?

Is there something like a directive which I can use in a script to force SSMS to enable/disable SQLCMD mode?
4
votes
2answers
3k views

Has SELECT an INSERT clause?

I know the form Select * into new_table from old_table statement. Bur recently I found 2 posts here and here which use it like SELECT * FROM NhlPlayer INSERT INTO PlayerBackups I find nothing in ...
2
votes
1answer
240 views

What are hypothetical indexes?

They seem to be created by tools like DTA. For practical purposes they could be deleted, ignored or chosen to be created, but until then, they have no direct influence on the database.
5
votes
5answers
3k views

log shipping to multiple-read servers

The SQL Server 2008 R2 Web Edition product marketing site has a benefit listed on its High Availability section: Benefit from log shipping, which helps automatically back up data on separate ...
7
votes
2answers
942 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
2k 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 (1,'...
3
votes
1answer
111 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 ...
33
votes
5answers
7k 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 ...
30
votes
4answers
107k 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
220 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
186 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 ...
3
votes
1answer
2k 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
2k 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
354 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
524 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 ...
4
votes
2answers
3k 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 fil....
57
votes
3answers
34k 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
429 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 ...
7
votes
2answers
242 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
276 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, T....
5
votes
3answers
3k 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 ...
37
votes
2answers
68k 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 ...
1
vote
3answers
6k views

Figuring out database timezone

Does anybody know of a way to alter a time zone on a SQL database? Is there any tool that could find the objects using the date and time?
5
votes
2answers
13k 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
274 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.
12
votes
1answer
1k 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, ...