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)

3
votes
2answers
1k views

Recent Queries in SQL Server 2005

I'm using a PHP web application to interact with a database and need to catch an erroneous query. It's dynamically generated SQL and I don't know 100% of the code nor did I write it so I need to see ...
1
vote
1answer
140 views

Using the name of a table contained in a field of another table?

I have a table (MyTable) with varchar columns named SourceID and ReferenceID where SourceID contains a table name and ReferenceID contains a key value for the source table. Is it possible in a query ...
2
votes
1answer
101 views

Full Text Search in SQL 2008

When I attempt to search in the full-text catalog which is my created earlier, I haven't been finding any word within database. For example: My word: Where CONTAINSTABLE(TABLO,*,' "*Where*" ') ...
9
votes
3answers
2k views

Testing stored procedure scalability

I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
2
votes
1answer
328 views

What system information is stored in the primary file in SQL Server?

Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of ...
5
votes
5answers
3k views

How much memory is needed to keep a table cached? (SQL Server 2008)

I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for ...
5
votes
3answers
3k views

Why is SSMS inserting new rows at the top of a table not the bottom?

Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
1
vote
2answers
186 views

Workload analysis before migration

Does anybody have a recommendation on which performance counters to collect and/or templates to use on the profiler before migrating SQL databases? The purpose is to evaluate the workload and make ...
1
vote
1answer
5k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
2
votes
1answer
407 views

Best Practice for Folder strucure?

I don't like my data files in C:\Program Files... , especially with a new system that has a smallish SSD as C: which holds mainly the operating system. So on a recent install I put my data files into ...
1
vote
4answers
302 views

Database backups

I have 10gb RDBMS (SQL Server). I would like to back it up as often as possible, but it seems that I will need an awful lot of space to make this happen on the order of several times per day. How ...
2
votes
4answers
3k views

how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio

We are a software engineering firm. We use SQL Server Management Studio (SSMS) to generate the few scripts we need to build and upgrade our databases. While it does an OK job, it bugs me that it ...
3
votes
3answers
3k views

What's the difference between Linked Server solution and SSIS solution?

When I want to move data between two databases (e.g source: Oracle, destination: SQL Server), I think I have two options: Linked Server and SQL Server Integration Services. But is there any benefit ...
0
votes
4answers
669 views

Why Clustered Index property is enabled on few columns and not on others?

I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere. In one of my SQL Server 2005 table, I have following columns: SerialNum ------- BigInt -------- ...
0
votes
1answer
235 views

Why SQL Server replication related jobs do not show up under the jobs folder in SSMS?

I found some SQL Server replication-related jobs that do not show up under the jobs folder in SSMS. Can anybody explain why this is, please? Thanks.
6
votes
3answers
569 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
1
vote
1answer
54 views

Replication : Which subscription changed a row

Is it possible to query the replication metadata to see which subscription caused a change to a specific row in a table?
1
vote
1answer
256 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
5k 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
8k 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 ...
4
votes
1answer
2k 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 ...
5
votes
2answers
278 views

What is an advantage or disadvantage for not upgreading 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
698 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 ...
31
votes
7answers
14k 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
325 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 ...
1
vote
3answers
4k 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 ...
0
votes
1answer
101 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 ...
2
votes
3answers
3k 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
615 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
2k 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 ...
4
votes
2answers
6k 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
239 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 ...
13
votes
2answers
6k 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 ...
5
votes
1answer
628 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 ...
5
votes
2answers
9k 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:- ...
3
votes
2answers
281 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 ...
14
votes
3answers
6k 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 ...
3
votes
2answers
167 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 ...
10
votes
2answers
8k 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
2k 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
169 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
1k 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 ...
6
votes
2answers
906 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
107 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 ...
29
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 ...
16
votes
4answers
30k 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
206 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 ...
2
votes
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 ...