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)

3
votes
1answer
203 views

Bayesian ranking

I have the following table in my database: ╔════════╦═════════════╦═══════════════╗ ║ Name ║ total_stars ║ total_reviews ║ ╠════════╬═════════════╬═══════════════╣ ║ Item A ║ 27 ║ ...
4
votes
1answer
251 views

Specifying the node to connect in db2

I have 2 nodes in my db2 catalog. How would I include the node on which I want to connect inside the db2 connect paramters? Node 1 entry: Node name = DB2HOST1 Comment ...
7
votes
2answers
1k 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 ...
3
votes
2answers
787 views

Restore a Database into a Current Database and merge

I want to know how, or if it is possible, to backup a database then should the user wish, restore that database at a later date and merge it with the one they are currently using. Both Databases will ...
3
votes
2answers
957 views

Shrinking SQL Server 2000 database

I apologize for the long question. I am trying to learn a lot on the fly as I have become the defacto SQL Admin. I have been tasked with moving a production database to a new server. My goal is to ...
0
votes
0answers
30 views

Shrinking SQL Server 2000 database [duplicate]

Possible Duplicate: Shrinking SQL Server 2000 database I have become the defacto SQL Server Admin and I have been tasked with moving a production database to a new server. I am trying to ...
3
votes
3answers
979 views

How to delete large amount of data from a sql table?

We ran out of disk space and we have a table with about 10million records. We'd like to delete the old records which are about 2million. What are the impact of just deleting these using a simple ...
1
vote
1answer
191 views

MapReduce performance on a single PC [closed]

I have heard that Hadoop has better performance than MySQL. Until now, I have used relational databases so this is really new technology for me. I have a 6 core PC. Suppose I have a table with 20 ...
3
votes
1answer
382 views

How to categorize rows by SUM of a column in mysql?

I have a table as id num_items folder 1 4 2 33 3 74 4 41 5 24 6 34 7 46 8 55 9 11 I want to assign folder number to rows but limiting the number of items in each folder (e.g. ...
4
votes
1answer
391 views

Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?

I kick off the following command in my app: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE Note: ...
1
vote
0answers
300 views

big mysql database optimization

My Joomla mysql database has about 3.4 million rows in content table. It is just static article. Very slow and often use out of resources. To speed up it, I segmented it into 6 sub-tables and added ...
2
votes
2answers
419 views

Combining two similar SQL queries

I'm using the following SQL query twice with different values of limit. In my case, 40 and 100, though it doesn't really matter. My question is - can I combine these two queries into one query? It ...
0
votes
2answers
405 views

Merging multiple databases as separate schemas in PostgreSQL

I have to combine data from 4 different databases. Each having its own design. What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database Then ...
1
vote
1answer
94 views

How to aggregate datapoints in a table?

Suppose I have following table - CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER); I want to aggregate the data by calculating average of every 10 points in the table. i.e. If ...
0
votes
1answer
2k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
0
votes
1answer
197 views

avoid insert xml duplicate data

how can to prevent duplicate inserts for every Execute? insert into IQ (XMLDT)values('<row>sdsdf</row>')
2
votes
2answers
467 views

Reordering data in a table

I have a table that stores sensor data (in SQLite) and looks like this; TABLE Timestream: idTimestream PK autoincrementing integer, time int not null, value float not null, idSensor integer not ...
3
votes
1answer
810 views

Profiler TextData output is null

I am doing a trace on a database and dumping the trace realtime into a table. The reason I'm doing a trace is I want to see all the queries that are being executed against the database. ...
2
votes
2answers
824 views

How to GROUP_CONCAT between given rows?

I have a table as id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night I want ...
1
vote
1answer
748 views

Splitting a column to normalized table

I have a de-normalized table as Original De-Normalized Table id text 23 first,second|third,fourth,fifth|sixth I want to normalize the database to create the following tables Table 1: (Split by ...
4
votes
1answer
754 views

ANSI/ISO plans for LIMIT standardization?

Are there currently plans to standardize one best way of limiting the number of results returned by a query? The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT ...
5
votes
2answers
171 views

New Cluster Testing - Best Practice

We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS. We are looking for suggestions on a set of tests we could perform to test failover of the ...
3
votes
2answers
768 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
2answers
286 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
2
votes
2answers
3k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
12
votes
2answers
13k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
0
votes
1answer
223 views

How can I use “select distinct” and have a range for my rows? [closed]

I wrote this code in PL/SQL developer and was wondering how I could have one row per range of t.ttt in the select distinct columns. Here's the SQL: select count (t.aaa), sum (t.bbb) from xxx t where ...
1
vote
1answer
118 views

Does Microsoft Security Bulletin MS11-049 affect SQL Server 2008 SP3?

The security bulletin does not list SQL Server 2008 SP3 under affected software but it came out prior to that service pack. We are running SP3 but this issue is still showing up on a vulnerability ...
5
votes
2answers
1k views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
5
votes
1answer
872 views

SQL Server 2008 R2 DMV Question

I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV. SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE ...
1
vote
3answers
485 views

Usefulness of a multi-attribute index

If an index has more than one attribute in it, is there any speed gained in a select statement whose where clause uses one of the attributes in the index? For example, take a table T with an index on ...
1
vote
1answer
226 views

SQL Server - Ignoring object dependencies to copy a DB (despite some column referenced in a sproc no longer existing)

First of all apologies for the novel but there's a lot to explain... We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be ...
3
votes
0answers
124 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
4
votes
3answers
8k views

Need to Check if a Table Exists Before Deleting a Record

I have the following SQL query in my Ruby app: sql = "DELETE FROM #{database}.table1 WHERE same_id = #{some_id};" The problem is that in rare circumstances 'table1' might not exist. I need to ...
1
vote
2answers
627 views

list tables that are missing primary/foreign key in postgresql database

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing primary and foreign keys? PostgreSQL 8.4 and 9.x versions
2
votes
1answer
4k views

What are SQL Server Securables?

I migrated two applications to the new server. One of the them works without any issues but the other ran into back end sql permission problems like The EXECUTE permission was denied on the ...
2
votes
2answers
119 views

Joining 4 tables and 2 counts

I have four tables. UserMaster ---------- UserKey - PK UserName UserID - UNIQUE UserLogTransaction ------------------ UserID - FK:UserMaster.UserID LoginTime LogoutTime CallCenter ---------- ...
2
votes
1answer
758 views

SQL Server Maintenance Plan multiple database backups in a single folder

I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day. For ex. I need to backup db1, db2, db3 and dump all these 3 ...
3
votes
3answers
150 views

Is it possible to install Developer Edition of SQL Server as the primary development server?

Is it possible to install Developer edition of SQL Server on a Windows server environment as development server? Each developer will have a local copies of same SQL Server edition and will connect to ...
2
votes
1answer
508 views

SQL Server 2008 - Return records in chronological order, given a separation factor

This question was originally posted here http://stackoverflow.com/questions/8492149/return-records-in-chronological-order-given-a-separation-factor and re-posted on this forum given a user's advice. ...
1
vote
1answer
891 views

Set statistics time showing different elapsed_time than dm_exec_query_stats view

When I exec a SQL statement after setting set statistics time on and running dbcc freeproccache and dbcc dropcleanbuffers I get the following output: DBCC execution completed. If DBCC printed error ...
2
votes
1answer
370 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. ...
13
votes
3answers
8k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
4
votes
1answer
689 views

How to restore a SQL Server 2008 R2 database

Here's my issue. I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the ...
2
votes
2answers
394 views

Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file ...
2
votes
2answers
2k views

SQL set allowed values for a column

I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only ...
3
votes
2answers
632 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
4
votes
2answers
1k views

SQL Server - How to know when a database has been restored

I'm using sql server 2008, and I would like to know if a specific database has been restored from a device and when did it happened. Is it possible?
1
vote
2answers
991 views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
0
votes
0answers
949 views

Dynamically Converted rows into columns (Pivoting)

Query SELECT Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total, COUNT(studfeedback.studfeedback_Rating) AS totastudents, ...