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)

1
vote
3answers
170 views

How to create implicit select statements

I need to create an implicit select statement for each table with all of its coluns - i.e. SELECT COL1, COL2, COL3 FROM TABLE1 instead of SELECT * FROM TABLE1. There are too many tables to use the ...
3
votes
1answer
101 views

Restoring with replace going incredibly quickly [duplicate]

So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database ...
1
vote
2answers
111 views

SQL Server 2000 optimzation job hasn't run for a while

Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database. We turned it ...
0
votes
1answer
852 views

Calculating time spent on query cache lock

How can I calculate the total time my MySQL database is spending Waiting for query cache lock ? I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the ...
2
votes
1answer
2k views

Return ResultSet with column names from postgresql Stored procedures

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
3
votes
2answers
168 views

Restore database to a instance in network

We are creating a application which requires sql database as backend to store data. So when user install my application he chooses a sql instance and its details. There we plan to restore the database ...
1
vote
3answers
562 views

How to determine if SQL Server will not execute parts of the script in parallel?

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"? USE UserDB; GO DBCC SHRINKFILE (DataFile1, 5); GO DBCC SHRINKFILE (DataFile2, ...
19
votes
3answers
2k views

Index on Persisted Computed column needs key lookup to get columns in the computed expression

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A ...
2
votes
2answers
572 views

Sql server - Rebuilding Indexes - Does it affect foreign keys?

i am looking at speeding up the performance of my database, and thought that Rebuilding some indexes might help. My question is, will this invalidate Foreign Keys from other tables pointing to the ...
1
vote
1answer
467 views

PostgreSQL 9.2 number of days in a tstzrange?

How can the number of days contained within a range be found? For example, with these timestamp ranges, get these (integer) number of days: tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
1
vote
1answer
5k views

Query Multiple tables in MS Access

I have three tables: Table City has Two columns: City_ID, CityName <City_ID is Primary Key> Table Accommodation has Two Columns: Accommodation_ID, CategoryType <Accommodaton_ID is ...
4
votes
1answer
2k views

Can column name be “Group” in Postgresql or in any databases

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax ...
1
vote
1answer
413 views

Using coalesce on an index column

Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way? I think that it ...
0
votes
0answers
155 views

Expanding the database in SQL Server 2008 R2

I have a database server DBA with .mdf and .ldf files on a SAN. Often one of my database will not list out the tables (unable to extend the db), Every time I have to take it offline and bring back ...
0
votes
1answer
1k views

SQL Linked Server Passing Date Parameters

I'm using SQL Server 2008 R2 and have a linked SQL 2012 Express database. On the 2008 box I am creating a stored procedure to be used in a report. I am wanting to pass a date variable so the user ...
1
vote
0answers
76 views

How to manage primary key while updating [closed]

In the following table primaryKeyColumn is primary key. To maintain the data history I always uses the values with WHERE condition(WHERE StatusColumn=1) And will set the StatusColumn to 0 if the data ...
3
votes
3answers
744 views

Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?

(First time poster, very very long time lurker. I cannot find an answer to this question anywhere, so I now come out from the shadows to ask the experts). Is a junction table with say 4 foreign keys ...
7
votes
4answers
63k 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 ...
0
votes
1answer
73 views

Optimize query with similar fields

I have the below schema, fields owner_id,billing_id,tech_id,admin_id on carts table are pointing to contacts table. As you can see from the query result I have to JOIN for each field .Is there a way ...
0
votes
1answer
1k views

Create table from stored procedure result

I am new to this field if you can please tell me if it is possible to do this it will be very help full. I have SQL Server 2008 R2. What I want to do is from my table I want to take first row and ...
12
votes
2answers
2k views

Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
4
votes
2answers
4k views

How to toggle a boolean field by sql in postgresql?

There is a field show which is a boolean type in postgesql. I want to write a sql to update that table, to toggle the value of show. If it's true, it becomes false, if it's false, it becomes true. ...
3
votes
2answers
2k views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself ...
1
vote
1answer
245 views

Should I extract / normalize the mailing address despite requirements limiting number / type of addresses?

I currently am working on a contacts application (similar to Outlook contacts combined with eVites and LinkedIn). The requirements for the application are to limit the addresses (and phone / email ...
0
votes
0answers
221 views

In mySQL possible to change auto-increment ids that have huge gaps and if so how?

I am importing a 50 table relational database (I'm not a dba) with 250,000+ records in most of the tables, yet the auto increment is off in most tables, some for example showing the last ID at 5 ...
2
votes
2answers
467 views

Slow query responce on different mysql versions, percona, MariaDB

LEFT JOIN is supposed to be faster than a NOT IN query. This is what I've read online. But practically, this seems to be opposite. I have the following query here, INSERT IGNORE INTO callrequests ...
0
votes
1answer
80 views

Not Equal Operation is not working

I have two tables 1st Table : center (2 columns) +-----------------------------------+ |id | company_name | +-----------------------------------+ |2 | Skytel Outsourcing Pvt. Ltd. ...
0
votes
0answers
30 views

SQL Server transaction log file size and auto grow [duplicate]

A SQL Server database that is trying do the auto-grow but seems that there is no free space on the disk to complete the operation. The SQL Server log error is: Autogrow of file 'DB_log' in ...
1
vote
1answer
7k views

What is the purpose of setting serveroutput in oracle? [closed]

Could someone let me know what is the purpose of using set serveroutput on/off in oracle? I ran the below query to see what it is doing. However I need to know much about serveroutput. Kindly help me ...
6
votes
2answers
307 views

How to optimize NOT IN statement

Why does NOT IN performs INDEX SCAN instead of INDEX SEEK? Here's my current index on the table, CREATE NONCLUSTERED INDEX [idx_dmcasarms_CourseList_cDesc] ON [dbo].[courselist] ( [c_desc] ...
1
vote
2answers
52 views

Convert to JOIN [duplicate]

I have a correlated sub query, which is taking 16 Minutes to execute how can i convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE SSF.SM_StockCode = ...
0
votes
2answers
1k views

How to retrieve comma separated values from database column with complete word

I have a table in a database with these columns id name facility_Type 1 Taluka y,n,n,y,n Now I want to retrieve the comma separated values like this id name Ambulance ...
9
votes
4answers
2k 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 ...
0
votes
2answers
201 views

Query Takes more time in SQL Server 2008

I have a table in SQL Server 2008 with 553,386 rows and every day it is increasing by about 400 rows. Now when I full scan of the table (Select * from) it is taking 23 sec. Is there any solution to ...
3
votes
2answers
458 views

continuously extending a table in SQL Server

We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table) This cause a lot of page splits. ...
1
vote
2answers
2k views

Optimizing removal of duplicate rows from recursive CTE query

I need to write a recursive SQL query to fetch multiple projects together with their lineage from the following table: CREATE TABLE #Project ( Name VARCHAR(10), ParentName VARCHAR(10) ) INSERT INTO ...
5
votes
3answers
7k views

What does NVL stand for?

What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
1
vote
1answer
307 views

Get maximum from aggregated values in a query

I have a table details it has a column kills and a column player_slot and a column match_id. I want to find the maximum difference between the SUM of kills for player_slot 0-4 and 128-132. Can someone ...
0
votes
0answers
499 views

Using an enum as part of an index definition

When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that. So my question is what are the subtle issues when an ENUM is part of an index ...
5
votes
1answer
381 views

Does SQL manage foreign key constraints in terms of Insertion?

Say I have a table tbl1 with data A1 and A2, where A1 is the primary key. Then I have another table tbl2 with data A3 and A1, both together being the primary key, referencing A1 as a foreign key from ...
1
vote
1answer
149 views

How to write this query in SQL Server Compact in more elegant way?

The query is : Select Case AStatus When 1 then 'Success' When 0 then 'Faliure' END, Case AStatus When 1 then 'AStatus' ...
0
votes
2answers
85 views

SORT, ORDER by Number

I have the following table: ID Name Items ------------------------------------- 1 John 7 2 Peter 533 3 Chang 13 4 Mike 9100 I want to order it by Items. I used ...
0
votes
1answer
55 views

Create table reference foreign key

I'm trying to create a table referencing a primary key from the table person; CREATE TABLE Membership (foreign key (USERNAME) references Person(USERNAME),MembershipNo INT Primary Key,DatePaid date ...
2
votes
1answer
260 views

Attribute and Value tables on SQL

I'm not a SQL expert and I'd like to ask for the best practices on the following schema. On my current project, we're using Attribute and AttributeValue tables quite often. I'm thinking this might ...
10
votes
4answers
3k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing ...
1
vote
1answer
1k views

Query an intermediate table and join to child table

Here's my database structure: Product Market ProductMarket (linked to Product and Market with "product_id" and "market_id" Price (linked to ProductMarket with "productmarket_id") I want to query ...
1
vote
1answer
2k views

Cascading Inserts in MySql

I have a users table that has a one to one relationship to a user_preferences table (primary foreign key user_id). When a new row is added to the users table (new id added), is there a way to setup ...
0
votes
1answer
2k views

How to get performance benefits from a view vs subquery?

I'd like to know how/if anyone has gained significant performance benefits from using views instead of subqueries. I would prefer not to define a specific case, since I'm trying to establish good ...
13
votes
1answer
1k views

Sql Anywhere 11: Restoring incremental backup failure

We want to create remote incremental backups after a full backup. This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible ...
1
vote
1answer
1k views

SQL Server 2008 database restore

I have database Registered and I backup that database from right click on database and backup and it is store registred.bak file. After I format the system once again I want to restore that database ...