An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

8
votes
6answers
2k views

How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Possible Duplicate: Passing array parameters to a stored procedure Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read: WHERE ...
8
votes
3answers
639 views

Worse performance on a new server

We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
8
votes
3answers
4k views

Storing date as integer (numeric), what are the advantages

Question 1 I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example ...
8
votes
4answers
170 views

SQL Server 2005/8 Query Optimisation Hints

I'm looking at educating a team on writing better SQL Server queries and was wondering what people's best hints were for improving performance. For instance I once had a DBA who insisted that ...
8
votes
4answers
1k views

How to check if a subquery has exactly one distinct result and a specified value concisely?

I found myself writing the following: select 'yes' where exists(select * from foo where val=1) and not exists(select * from foo where val<>1); and wondering if there is a more concise way ...
8
votes
3answers
2k views

SQL Server - Multiple running totals

I've got a base table with transactions and I need to create a table with running totals. I need them to be per account and and also have a few running totals for each account (depending on the ...
8
votes
2answers
9k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
8
votes
1answer
3k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
8
votes
2answers
4k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
8
votes
2answers
188 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
3answers
788 views

overstating field size in database design

I have some fields for my tables that are strings and at the moment, most of the field size have pretty high character limits. For instance, 100 char for the street name. Is there a penalty for using ...
8
votes
3answers
2k views

What are the alternatives for an ORDER BY clause in a View?

This question just had to be in this site :) ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view. I know that there are ...
8
votes
3answers
2k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...
8
votes
4answers
1k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
5answers
205 views

What is the best way to find out how many records do not have a proper phone number in SQL Server?

I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them. I've been looking around in SSIS ...
8
votes
2answers
499 views

function hangs with null case operation

I created a function that accepts a start and end date, with the end date being optional. I then wrote a CASE in the filter to use the start date if no end date is passed. CASE WHEN @dateEnd IS NULL ...
8
votes
2answers
285 views

Where can I find the SQL standard document?

Where can I find a legal copy of the ISO SQL 2008 standard?
8
votes
3answers
4k views

How to boost performance of virgin queries in MS SQL Server?

I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
8
votes
2answers
4k views

Create View in specified database with dynamic sql?

I am writting a dynamic sql to drop and create view in different database. So I wrote: set @CreateViewStatement = ' USE ['+ @DB +']; CREATE VIEW ...
8
votes
3answers
1k views

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find ...
8
votes
2answers
6k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
8
votes
5answers
686 views

SQL Server 2008 and extended properties [closed]

In SQL Server, what do you guys use extended properties for? Some articles and blogs are suggesting they be used for self-documentation of the database and its objects but I can't see this being used ...
8
votes
3answers
11k views

How export a sql server 2008 diagram to PDF filetype?

I want to have a export from my database diagram to PDF or image types.How can I do this? I worked with sql server 2008 R2.
8
votes
2answers
2k views

How to delete a procedure which name is ambigous?

I am using informix... I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a DROP PROCEDURE myProc; then i get a error message ...
8
votes
1answer
764 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
8
votes
3answers
607 views

SQL Server query slow when paginated

I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012: select Id FROM dbo.Person WHERE CONTAINS(Name, '"John" AND "Smith"') order by Name Executing this query alone ...
8
votes
3answers
4k views

When should indexes be dropped and recreated?

We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month. For certain tables we are doing daily ETL processes and others we are doing weekly/monthly. ...
8
votes
2answers
6k views

Table not getting created under dbo schema

When creating tables in SSMS, I've realized that if you execute the following statement: CREATE TABLE [tableName]; the table will be created under your own schema (and not dbo). So in order to ...
8
votes
3answers
37k views

SELECT DISTINCT on one column, while returning the other columns?

I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it. My SQL ...
8
votes
1answer
1k views

Is there an execution difference between a JOIN condition and a WHERE condition?

Is there a performance difference between these two example queries? Query 1: select count(*) from table1 a join table2 b on b.key_col=a.key_col where b.tag = 'Y' Query 2; select ...
8
votes
4answers
120 views

What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs?

In a lot of relational database designs there are fields that are referenced in other tables. For example consider a user table with a unique username and a second table storing address data. One ...
8
votes
2answers
383 views

Mirroring the Distributor

Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
8
votes
2answers
681 views

LNNVL Justification

LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of ...
8
votes
2answers
327 views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
8
votes
1answer
925 views

What are the security implications of sp_executesql?

By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql. However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql. As ...
8
votes
3answers
3k views

SQL Server 2008 datetime index performance bug

We are using SQL Server 2008 R2, and have a very large (100M+ rows) table with a primary id index, and a datetime column with a nonclustered index. We are seeing some highly unusual client/server ...
8
votes
2answers
715 views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
8
votes
2answers
1k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
8
votes
1answer
1k views

SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT

I was researching the differences between SQL Server's READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource: Choosing Row Versioning-based Isolation Levels ...
7
votes
5answers
1k views

Database design: Dividing multiple identical tables, good or bad?

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them. I have seats at a theater, the seats are divided into 4 main areas ...
7
votes
3answers
1k views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
7
votes
2answers
2k views

A good SQL sniffer

Can anyone point me to a good SQL sniffer where I can watch the SQL commands that run against my database in real time? If it's free, that would be great.
7
votes
4answers
4k 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 : ...
7
votes
3answers
2k views

Does disconnecting the network stop a query?

I recently executed an update query against 100,000 records. I realized I'd made a mistake while the query was running and quickly unplugged the network cable. Does the update query stop ...
7
votes
3answers
5k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? ...
7
votes
2answers
4k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
7
votes
2answers
14k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
7
votes
3answers
8k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
7
votes
1answer
9k views

Performance implications of using OPENQUERY in a view

Please see this http://stackoverflow.com/questions/7753380/performance-implications-for-using-openquery-in-a-view question on stackoverflow, it's a cross over subject and would love a dba's POV
7
votes
3answers
634 views

More Databases Per Sql Server 2005 Instance

Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate. Database size ranges from 50mb to 40-60gb in the extreme, ...