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)

4
votes
3answers
592 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
4
votes
3answers
1k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
4
votes
2answers
350 views

Running different instances of SQL Server from different networks on one box

I want to be able to run SQLSMS and select an instance to connect to but the instances are on different networks. I remotely log on to our server and from there, I want to be able to access our ...
4
votes
3answers
4k views

Stored procedure executes too long

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
3
votes
1answer
1k views

PostgreSQL list of integers separated by comma or integer array for performance?

As per subject, I have a field which is varchar in which i store some numbers separated by commas. The list grows and shrinks based on clients choices. I then use it in queries to check if a ...
3
votes
2answers
727 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
3
votes
1answer
433 views

How to Update same table on deletion in MYSQL?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. Following is query I used to create table: CREATE TABLE IF NOT EXISTS `Employee` ...
2
votes
2answers
63 views

Call UPSERT function multiple times to different inputs

I have a function: merge_vehicles(vid, cid, vname, reg_no, name, name_1st) Can i call it multiple times on inputs like (2335, 55, '246BDH', '246BDH', '811', 1), (2336, 55, '038THX', '038THX', ...
2
votes
1answer
143 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
2
votes
2answers
453 views

Speed up bulkinsert on sql server

We're doing some testing and this involves adding 10.000 rows every 10seconds in a table that has 4 int columns and one of them is clustered index. When we run the app the first bulkcopy takes around ...
2
votes
2answers
393 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
3answers
2k views

In Microsoft SQL Server 2008, syntax generates the error “The Parallel Data Warehouse (PDW) features are not enabled.”

I have the following virtual column generated from an aggregate over a sorted partition, MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC ) However, when I execute that, I get ...
2
votes
0answers
320 views

Transform XPath map into XML document using relational data

Background Most modern databases have XML functions that can be used to extract data in an XML format. I want to avoid the task of manually calling XML functions to extract the data. This problem ...
2
votes
2answers
2k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
2
votes
1answer
7k views

Multiple “GROUP_CONCAT”s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
2
votes
1answer
343 views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself). mysql> SELECT * FROM Employee; ...
2
votes
2answers
2k views

Querying a database efficiently for a huge chunk of data

Here is my query SELECT * FROM ( SELECT a.*, rownum rnum FROM ( SELECT id, data FROM t ORDER BY id ) a ...
2
votes
2answers
2k views

CREATE INDEX for long columns

I am looking for alternative to create index on long column: create table line ( field_key integer not null, value varchar(4000), ... ); create index key_value_idx on line ...
2
votes
1answer
358 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
2
votes
1answer
406 views

SQL Server 08: Union over while

I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out! I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the ...
2
votes
2answers
394 views

Check Index Fragementation Query

I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea. I have a new production SQL Server (Windows Server ...
1
vote
1answer
64 views

optimize the query with big and low cardinality

I have big enough dataset and few joins to get them all with help next query: SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER ...
1
vote
1answer
176 views

type recognition with MonetDB, *create* tables from CSVs

I am confused whether you can copy records into a new table in MonetDB. The official documentation seems to talk only about copying into existing tables. I have quite a large universe of billions of ...
1
vote
1answer
2k views

Is it possible to query value of SQLNET.EXPIRE_TIME parameter?

Can somebody advice whether it is possible to query for current value of SQLNET.EXPIRE_TIME parameter in Oracle 11 DB? Should select * from v$parameter return value of SQLNET.EXPIRE_TIME (if it ...
1
vote
1answer
1k 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 ...
1
vote
1answer
203 views

SQL from/where or join?

I was wondering performance wise what would be faster a join or a from and where query. I am trying merge and update query but performance is terrible?
1
vote
3answers
1k views

sql server 2008 - controlling size of log file

I am getting this error. Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc ...
0
votes
3answers
34 views

Allowing users to compile / run PLSQL and SQL

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data ...
0
votes
1answer
93 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | ...
0
votes
1answer
107 views

Embedded select with rank

I am having the hardest time following the tutorials on this site regarding ranking with MySQL. I have a larger problem than others seem to show. My table is constantly updating with single answers ...
10
votes
1answer
11k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
10
votes
1answer
6k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
9
votes
2answers
3k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
8
votes
3answers
12k 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
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
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 ...
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
347 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
248 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
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
4answers
4k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
6
votes
4answers
524 views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 ...
6
votes
2answers
1k views

Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
6
votes
2answers
996 views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
5
votes
1answer
2k views

Difference between count(*) and count(1) in mysql?

In MySql we can count the total number of records by using count(1) or count(*). Is there any technical difference between them?
5
votes
4answers
2k views

Checking if 2 tables ( on different servers ) have the same exact data

So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been ...
5
votes
2answers
545 views

SQL Server configuration / specification recommendations and advice!

I am about to deploy a new server at work as follows: Dell T610 2 x Xeon 2.53 Quad core 24 GB RAM 1 x Perc H700 RAID controller with 1GB Non Volatile memory 8 x 300GB 15k SAS Hard drives I am looking ...
4
votes
2answers
529 views

Migrate all databases from one SQL Server 2005 to another

We have a production SQL Server 2005 server on which we are hosting 200 databases. We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready. I want ...
4
votes
1answer
242 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
4
votes
1answer
188 views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...