Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

1
vote
1answer
2k 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 ...
0
votes
2answers
101 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 ...
2
votes
1answer
6k views

Average of a row or record across three columns

The avg command in SQL works particular column data. But here, we want to calculate the average of three such columns for each record. In math, we would do avg=(col1 + col2 + col3)/3 . ...
0
votes
0answers
81 views

MySQL server vs client isolation levels

What is the difference between setting the transaction isolation level in the client configuration and the server configuration? If I set the client isolation level to be serializable while the server'...
1
vote
0answers
361 views

Postgres binary replication: Standby takes a long time to start

I am using the Postgres binary replication tutorial, and have rsynced all the data files from my master to standby rsync -av --exclude pg_xlog --exclude postgresql.conf data/* 192.168.0.2:/var/lib/...
5
votes
1answer
21k views

Split/explode comma delimited string field into SQL query

I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216' and I want to use it to search IN this field: SELECT * FROM table1 WHERE id IN (id_list) id is integer id_list is varchar/text ...
15
votes
1answer
610 views

Is using DISTINCT as a hint in subqueries useful?

Does adding DISTINCT in the following example have any impact on the query running time? Is it wise to use it as a hint sometimes? SELECT * FROM A WHERE A.SomeColumn IN (SELECT DISTINCT B....
-1
votes
1answer
83 views

COUNT WITH JOIN [closed]

I've the following tables: fss_post(post_id,text) fss_comment(idComment, post_id, text) Is possible to write a query that give me as result a row for each post_id with total comment count for ...
1
vote
1answer
3k views

Override/kill when Code 3/SQLSTATE=57016 Occurs

I have encountered issues a few times when a process of mine performs a bulk load to a temp table we use for pre-processing purposes. The Bulk load fails for one reason or another (this we are looking ...
1
vote
0answers
265 views

SQL Server 2008 100% CPU lock/hang [closed]

Our application is a SAAS web application and uses Microsoft ASP.NET MVC application with LINQ to SQL and SQL Server 2008. It is hosted on Amazon’s cloud services (EC2). The SQL Server and IIS are ...
2
votes
2answers
9k views

Should I join datetime to a date using cast or range?

This question is a take-off from the excellent one posed here: Cast to date is sargable but is it a good idea? In my case, I am not concerned with the WHERE clause but in joining to an events table ...
4
votes
3answers
1k views

Inserting two rows from single row

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
1
vote
1answer
595 views

SSIS Package Execution From Visual Studio Unit Test

Hello I am attempting to run a test which contains the following code: string pkgLocation; Package pkg; Application app; DTSExecResult pkgResults; ...
1
vote
2answers
975 views

Returning Right of a string with no specific known length

I want to return the data from a column from the right. This would mean using the RIGHT() function, however this means entering a length of what to return. For example, I have the following data: ...
6
votes
1answer
658 views

calculate days and equally distribute remaining

I'm having difficulty with calculating remaining days from hours being distributed over a date range within a given time period. For this example, the time period would be 2013-11-01 through 2013-05-...
1
vote
1answer
3k views

Is there a way to determine automatic storage table spaces?

First off, we are on both Windows and AIX and are using DB2 ESE 9.7 FP 4. I am trying to determine dynamically whether or not table spaces are set up via automatic storage or not (so we can ...
2
votes
3answers
635 views

Many to many link tables with history? [closed]

While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to: enable customers to subscribe to multiple services each service can have multiple domain names per ...
0
votes
1answer
4k 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 ...
5
votes
7answers
3k views

How to store schema-less data efficiently in a relational database?

I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods. Option 1: Serialize the array as JSON/PHP serialized array and store it in a 'meta' column. ...
-1
votes
1answer
461 views

Inserting dates before year 1950 in Oracle

How can I insert date which is before 1950? update up_sif.tb_person set dateB =to_date('18.10.1949','DD.MM.YYYY') where ID = '123345'; It keeps update with date 18.19.2049. Any ideas?
1
vote
1answer
176 views

Which string variables in mysql support utf-8?

I have a table that stores string from a user and is displayed in a web form. But as I see when the user inputs e.g. in a language of cyrilic alphabet (I assume is UTF-8) garbage are displayed back. I ...
0
votes
1answer
182 views

Review my simple database tables

Hey trying to create a simple database table for a small beer review project, it been awhile since i have created db's so could anyone just tell me if i am totally wrong here? Especially my use of ...
16
votes
2answers
70k views

Max Connection Pool capped at 100

I'm running SQL Server 2008 R2 SP1, on a Windows Server 2008 box. I have a .NET script running from Visual Studio 2010 that does the following: Reaches into the database Makes a change Iterates ...
0
votes
1answer
74 views

How to treat a row as a table

I have two tables with the following structures: CREATE TABLE USERS ( USERID VARCHAR2, USERNAME VARCHAR2 ); CREATE TABLE CONFIRMATIONS ( PERSONELID NUMBER, ACCOUNTING VARCHAR2, FINANCE ...
3
votes
4answers
6k views

How can I recover my tables in sql server database [duplicate]

I deleted 3 tables from my database with data and I have no backup. How can I restore tables with data in my database ? How can I restore tables and data with database log file?(Database recovery ...
2
votes
1answer
1k views

How to optimize a query that includes counts of rows in multiple tables?

My query looks like this: EXPLAIN SELECT ... FROM USER u LEFT OUTER JOIN (SELECT COUNT(*) notified_count, user_id FROM user_email_tracking ON u.id = email_tracking WHERE email_type_id = 4 ...
0
votes
1answer
43 views

How to determine which `Foos` have no associated `Bars` [duplicate]

I want to find all Foos that do not have any associated Bars. Here's the query I'm using. It's returning no records: select * from foos f where f.id not in(select b.foo_id from bars b); However, ...
0
votes
0answers
135 views

unexpected null returned from calculated columns

I'm working on a program that will calculate journey times based on bluetooth MAC addresses and i'm trying to nail down the mySQL commands I need before I get cracking on the software part of it. i ...
1
vote
2answers
746 views

Get a number nth bit value in oracle

How can I get nth bit value in oracle database? I am currently using this query: WITH a AS (SELECT 7 val FROM DUAL) SELECT CASE BITAND (a.val, POWER (2, n-1)) WHEN 0 ...
3
votes
1answer
400 views

find total money of parent node of tree according to a particular pattern

actually i am working on a project in which members are added in a tree pattern, and get the payment accordingly. My tbltestingtree structure is ass follow: Id ParentId IsLeft ...
1
vote
2answers
389 views

Cross Join with Filter?

i need to make Sp to distribute students to their sections the procedure take 2 string parameters StuID and SecID in case I've send '1,2,3,4,5' as StuID and 'a,b' as SecID i'm using spliting function ...
3
votes
2answers
596 views

How do I use window functions (“over”) together with crosstab in Postgres?

Table: application_app applied_date | applied_class | id -------------------------------+---------------+----- 2013-03-06 15:59:49.22774-05 | BOSFAL13 | 13 2013-05-12 16:56:...
2
votes
1answer
926 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
2
votes
3answers
125 views

Are these indexes redundant?

I have two indexes on a table that will contain roughly 10-20 million records at a given moment. Index 1 CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC] ON [dbo].[LogWindow] ( [AccountNumber]...
0
votes
1answer
73 views

Extract data for all groups in a single SELECT statement

MySQL 5. Table: schedule Columns: id int(10) UN PK AI startdate date groupid smallint(5) UN The following statement extracts the Schedule ID for a given group (the schedule with the latest date ...
0
votes
2answers
269 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
709 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. ...
0
votes
1answer
101 views

Performance: order of tables in joined statement

I have the following SQL statement, running on a SQLite database on a Windows mobile device. SELECT table1.uniqueidentifier1, table1.int1, table1.varchar1, table1.decimal1, table1.decimal2 ...
0
votes
1answer
54 views

Append columns with values from has many reletionship

I have two tables orders and items. Every Order has many Items. orders - id - created_at - paid_at items - id - title - amount - quantity - order_id Let say that there are 3 kinds of items. Chair, ...
2
votes
1answer
197 views

Joining on a field with different values

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually ...
0
votes
2answers
5k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
2
votes
2answers
4k 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 #...
1
vote
1answer
479 views

Merge Fields into one Row with a Query

I am working with Microsoft Office Professional Plus 2010. What I am trying to figure out is how to merge all characters for each DOMAIN field into one row, for example: Q01 ...
2
votes
1answer
2k views

select row data as column list

In my database I have a set of custom field values that can be setup by the customer. They can create labels for these fields and then assign values which are linked back to particular tables in the ...
-2
votes
1answer
143 views

put number inside database sqle server in arabic format I do not want it in english format I need it in arabic [closed]

I do not want the number in English format I need it in Arabic I am sorry my problem I inserted number in my table in English(e.g 5),but I want to replace it in Arabic format. because I need it in ...
0
votes
0answers
307 views

Getting Request Timeout from Store Procedure?

Only sometimes, I get Request Time-out(means my SP is not responding within a specfied time-period which is 90 seconds). Here is my SP. ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog] ...
0
votes
0answers
2k views

How to concatenate multi row sql output

Version Information: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production HP Server Automation v9.14 I am creating a report of all my HP-SA managed servers. The report includes data from ...
2
votes
1answer
8k views

MySQL query to evaluate ranges in date and time elements of a datetime column separately

TABLE EventID, EventTime (DATETIME) 1 2013-09-29 23:55:00.0 2 2013-10-01 00:05:00.0 3 2013-09-29 23:55:00.0 4 2013-09-29 23:45:00.0 5 2013-10-02 23:05:00.0 6 ...
0
votes
1answer
86 views

Handling empty fields

SELECT Name, SUM(Switch([Date] = DateAdd('d', - 7, DATE ()), NetAmount) )AS DAY1 FROM Customer GROUP BY Name My question is how to return the value 0 when the date is not present (an empty field). ...
1
vote
1answer
551 views

MySQL query optimization with aggregate ORDER BY

I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items." The schema is as follows: ...