DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

8
votes
2answers
423 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
816 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
1answer
1k 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
2answers
14k views

SQL Server backup failing. Error: 3041, Severity: 16, State: 1

First of all, I'm very new at stackexchange, so please bear with me. I'm running a SQL Server 9.0.4060. My problem is: my SQL Server backup keeps failing on a lot of the databases. The Maintenance ...
8
votes
3answers
4k 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
2k 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
2answers
1k 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
1answer
933 views

Friends relations in MySQL

I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which ...
8
votes
1answer
2k 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
2answers
3k 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
3answers
51k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
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 : ...
7
votes
3answers
3k views

CHECKPOINT or COMMIT writes to disk?

Let's say, for SQLServer2008R2 and higher, with full recovery mode databases. I always thought : When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM. ...
7
votes
2answers
881 views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
7
votes
6answers
3k views

attach/detach v.s. backup/restore

I am using SQL Server 2008 Enterprise. I need to transfer the database (as a whole) to another server (to make a duplicate database to setup another test environment). I have two choices, (1) making ...
7
votes
2answers
5k 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
4answers
56k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
7
votes
2answers
32k views

Returning a result set with multiple rows based on max date

I have a child table that is something like this: [Cust Date Table] | Customer ID | Some Date | Balance | +-------------+------------+---------+ | 1 | 2012-04-30 | 20.00 | | 1 ...
7
votes
3answers
699 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, ...
7
votes
2answers
18k 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
2answers
1k views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
7
votes
2answers
486 views

What is the purpose of the USING parameter in SQL?

Working with some code on a standard MySQL database, and I came across this statement: SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 What's throwing me is: what is USING for, ...
7
votes
4answers
20k views

Update existing rows with squence number/char or any unique data

I added a new column in the Table X This column "cn" has to be unique and mandatory, but old data don't have any value. How to update the existing records with sequecely or random unique data? ...
7
votes
3answers
7k views

SQL Calculated Field in both SELECT and GROUP BY clause

Often in querying my MS SQL Server databases, I need to create a calculated field, such as this (CASE WHEN A.type = 'Workover' THEN 'Workover' ELSE (CASE WHEN substring(C.category, 2, 1) = 'D' ...
7
votes
2answers
436 views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
7
votes
4answers
2k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
7
votes
2answers
1k views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field. Consider the following table: lap_id (Serial), lap_no (int), car_type ...
7
votes
1answer
509 views

selecting where two columns are in a set

This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following: SELECT whatever WHERE col1,col2 IN ...
7
votes
3answers
6k views

Why are NULLs sorted first?

Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first? select 1 as test union all select 2 union all select NULL union all select 3 ...
7
votes
1answer
155 views

IN and NOT IN for XML column

I have a table with a xml column. Xml is similar to <Root> <Row> <user>abc</user> <Rowid>1</Rowid> </Row> <Row> ...
7
votes
1answer
2k views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
7
votes
2answers
366 views

Why do we have SHRINKDATABASE command in SQL Server

I have read across the internet where Finest Developer says shrinkdatabse should be avoided as it causes increase in fragmentation and decrease in performance . So i would like to know from you guys ...
7
votes
1answer
490 views

Adding SPARSE made table much bigger

I have a generic log table, about 5m rows. There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of ...
7
votes
4answers
3k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
7
votes
2answers
2k views

How to effectively query and aggregate a normalized SQL db?

In relation to Most efficient way to return multiple aggregates in a single stored proc? I have an email type application and want to select all the messages (inbox) for a user. The problem is that I ...
7
votes
1answer
236 views

Is it possible to limit the maximum disk usage for a user

Is it possible to set a limit on the amount of physical disk space a user (or group of users) can use within a single database? For example is there some method of associating a schema with a ...
7
votes
3answers
3k views

SQL Server 2008 R2 High CPU

We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3. Our database works very hard (we run around 600,000 queries in 1 minute) and ...
7
votes
3answers
2k views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
7
votes
2answers
6k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
7
votes
3answers
465 views

How to express this constraint in a database schema?

I have the following functional dependencies which are in BCNF: a,b -> c a -> d b -> d With the additional constraint, that no a and b should be combined with a c, where a and b have ...
7
votes
4answers
374 views

Mathematical equality of two SQL statements

Is there a way to get check the mathematical equality of two SQL statements? I have two SQL statements: SQL_STATEMENT_1 SQL_STATEMENT_2 Running both statements on data and comparing the output ...
7
votes
2answers
3k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
7
votes
1answer
13k views

CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?

Just came across a weird issue, whereby depending on how I create an index, an index name is required. http://dev.mysql.com/doc/refman/5.5/en/create-index.html ...
7
votes
2answers
6k views

Oracle 11g: performance improvements of inserts

I have a table of 500 millions of rows (and growing) I did the following to improve performance of inserts: On database side: dropped all indexes and constraints disabled logging On application ...
7
votes
2answers
2k views

PAGE compression in SQL Server effect on running queries

I am planning to apply PAGE compression to some of the big tables in my database (DW to be precise). These tables are fairly big with over 15 Billion rows. When I applied the compression in test ...
7
votes
4answers
6k views

Select statement takes long remotely due to long “client processing time”, but short locally

While connected to our production server (SQL2008. very powerful machine), this select statement takes 2 seconds, spitting back all fields (4 MB of data in total). select top 30000 * from person ...
7
votes
1answer
203 views

T-SQL - OPTION (FAST x) and trace flag 8722

I have searched a long time and still haven't found the answer to my problem. Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to ...
7
votes
1answer
261 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
7
votes
5answers
6k views

Use of HAVING without GROUP BY in SQL queries

In order to use HAVING in SQL queries , must there be a GROUP BY to aggregate the column names? Are there any special cases where it is possible to use HAVING without a GROUP BY in SQL queries? ...