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
131 views

Aggregating statistics from child rows

I have a parent row and then many child rows, and I must aggregate statistics from these child rows. For a more concrete example let's imagine I have one round of golf (parent row) and then child ...
2
votes
1answer
47 views

Best Practices. Do you append to the existing backup setup when doing a Transaction Log backup?

When doing transaction log backups, is it best practice to just keep appending to the same file or creating a new .trn file for each backup? Thanks.
1
vote
1answer
289 views

Struggling with complicated date query

So I have been tasked to write a query involving dates for a contract management system we have. The database stores the date the contract's primary terms expired, the frequency that the contract ...
2
votes
2answers
7k views

Local database vs Amazon RDS

I am doing large INSERT into a local MySQL database I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable I expect this database to ...
0
votes
1answer
455 views

Get ticket sales and reservations grouped by time slot

Ok, I am definitely no guru when it comes to SQL, I will preface this by saying that. Also, hopefully the info I post will be enough for those out there to figure out what I need to do. That being ...
0
votes
3answers
419 views

Transaction in SQL Server

Consider a scenario, when 2 Stored Procedure called inside a Stored procedure like follows. Note: All 3 SP’s are implemented with TRANSACTION. CREATE PROC [dbo].[SP3] AS BEGIN BEGIN TRAN EXEC ...
2
votes
3answers
90 views

SQL Update Efficiency and Blocking Concern

Say I have some SQL similar to the below, which is an update with many joins. The number of rows actually being updated may be 1000's or potentially only a few. The actual join takes a long time as ...
0
votes
1answer
203 views

Any issue in referencing a unique index from a foreign key table?

Is there any concern in referencing a unique index from a foreign key table? Using SQL Server 2008 R2 Please advise thanks
4
votes
2answers
228 views

MySQL | Complex (for me) SQL Statement, how to?

I have a table that contains posts and another table that contains meta options for each post in first table. The meta options table is a key value pare table. Lets say I have the posts tables that ...
1
vote
1answer
241 views

Efficient query plan for selecting all data duplicated on several columns

The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well. The purpose of the query is to show all ...
0
votes
1answer
152 views

sql 2000 trace : sp_releaseschemalock

For a SQL 2000 server, is it bad to see too many exec sp_releaseschemalock 1 events in a SQL trace file?
2
votes
2answers
406 views

Advantages of Database Snapshot

Can someone please highlight the advantages of Database Snapshot in SQL2008. I read some where that "When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent to the ...
0
votes
1answer
2k views

How to return different statements in where clause ? (oracle)

I mean: CREATE OR REPLACE FUNCTION FUNCTION1(ID IN NUMBER) RETURN NUMBER BEGIN SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1 WHERE COLUMN1<= Y AND (CASE WHEN ID < X THEN COLUMN1<COLUMN2 ELSE ...
4
votes
1answer
15k views

temporary table inside procedure oracle

I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g. Mysql procedures allow me to: 1. Create temporary tables 2. Insert data into temporary tables / Query these ...
2
votes
1answer
287 views

Optimize simple query in SQL Server

This should be a quite easy query, but I honestly think its execution time can be improved. select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) ...
0
votes
1answer
3k views

how to return table on sql function

I am very new to sql create function i wanted to check data column value at table the purpose is to return full table where data is null , <0 , >1 the problem is i am trying make data is ...
2
votes
6answers
2k views

MySQL join two table if second table is not empty

I have 2 tables like below table 1: users id name 1 John 2 Jane 3 Mery 4 Alpha table 2 : notes id note owner created 1 alpha alpha 1 2013-02-03 10:30 2 yeah ...
7
votes
1answer
379 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 ...
3
votes
1answer
781 views

Different Results From sys.databases As Different Users

We have a SQL Agent job that is configured to run as user "dbo". This job gets a list of databases defined in sys.databases that match a naming scheme. The job then performs some dynamic SQL to ...
0
votes
2answers
41 views

SQLQueryTableData-Important

In SQL Server Management Studio, when I run the query select * from tablename it shows me the data. But, that table is not physically present in that database. Is it possible?
2
votes
2answers
200 views

Using data from other database to populate a new database

I have a SQL Server database DB1 and want to move data from DB1 to DB2. But only want some of the data to be moved. I want all the rows from DB1 that have a catParent that is the same as I Id from ...
0
votes
0answers
368 views

MySQL Left Join Select all rows

Hi guys I have a query which is pulling data from database : SELECT sandwich_table.id, `sandwich_name`, salad, COUNT(salad), salad_type.type, ...
0
votes
0answers
235 views

Try to find a match using SUBSTRING(SHA1(value)), 0, 6)

I have a tbl where all entries for companies that participate in a online project are saved. This table is named "business". Those companies can use their employess to access the online platform i 've ...
1
vote
1answer
121 views

Change SQL Server 2008 name

I want to change my SQL Server's name. Currently I am connecting to SERVER1\SQLSERVER. SERVER1 is the machine name. I want to change it so I could connect to SERVER2\SQLSERVER and keep SERVER1 as the ...
0
votes
1answer
99 views

sql job cancel triggers failure notification

When I cancel a sql agent job running an ssis package, I get a failure email because I set up the notifications to email when the Job Fails. Does anyone know why this is considered a 'failure'?
4
votes
1answer
117 views

comma delimited column fix

At my current place of employment someone made a decision to put IDs into a comma delimited column. I'm looking to break these out into a proper many to many relationship. However; it is not in the ...
1
vote
1answer
201 views

Change tracking SQL Server 2008

My requirement is to track old rows and modified rows from some particular table and the time when changes are made. Using "Change Tracking" and with help of any system table. Due to some specific ...
-1
votes
1answer
8k views

how to sum column values with same id [closed]

with this query : SELECT a.nid, a.stock, b.qty FROM uc_product_stock a INNER JOIN uc_order_products b ON a.nid = b.nid I have the following result: nid stock qty 11 256 4 11 256 ...
17
votes
3answers
2k views

Why would someone put `where 1=1` in a query?

I came across a view in our database today where the first statement in the where clause was where 1 = 1. Shouldn't this return true for every record? Why would someone write this if it isn't ...
2
votes
3answers
8k views

Get counts grouped by month and year

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year. SELECT YEAR(crdate) AS y, MONTH(crdate) AS m, COUNT(*) AS tally FROM ...
2
votes
2answers
130 views

SQL Server: reset all database tables

I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to ...
3
votes
1answer
108 views

Dissecting Joins

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
1
vote
2answers
254 views

Select Into a table on server1 from server2 without using 'Linked Servers'

I am trying to get all the records from one table into another table but these tables (which are identical) are on separate servers. Any help would be appreciated. Thanks
4
votes
1answer
312 views

Does the order of fields in SELECT query matter when using composite indexing?

I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index? For example, if I ...
4
votes
1answer
198 views

What is the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this?

I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following ...
0
votes
1answer
81 views

SQL Server 2008 Mirroring

How would one start the secondary mirror instance if the principal SQL server instance went off line?
2
votes
2answers
64 views

2 databases in sql server 2005 “Production Server”

Can 2 databases decrease the performance in a sql 2005 production server? Server specs: Dell Power edge 2950, 2 Quad core Xeon, 16 GB of RAM, 3x73 GB sata hard drive hot plug, windows server 2003 R2 ...
4
votes
1answer
419 views

SQL Server 2008 upgrade advisor tool

We upgraded to SQL Server 2008 from SQL Server 2000. But due to some legacy scripts/queries in our health care application, that run only in 2000, we had to run 2008 version on backwards compatibility ...
3
votes
4answers
891 views

How to query a Vertica server in C language?

I'm investigating column oriented databases and came across Vertica. My need is to feed the Vertica database from C code. I don't succeed in grabbing this information from Vertica: I'm told to use ...
13
votes
3answers
354 views

What is the correct result for this query?

I came across this puzzle in the comments here CREATE TABLE r (b INT); SELECT 1 FROM r HAVING 1=1; SQL Server and PostgreSQL return 1 row. MySQL and Oracle return zero rows. Which is correct? ...
3
votes
1answer
732 views

Best Approach for Mirroring Data from SQL Server to MySQL

I need help in regards to replicating data from a third party point-of-sale system with a SQL Server backend to a MySQL DB. Specifically, can anyone suggest a best way to mirror data from SQL ...
1
vote
2answers
103 views

How to Perform Two Simple Queries using Select Value from First Query

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select ...
3
votes
1answer
645 views

Index Key Column Order

I have created a joining table for many-to-many relationship. The table only has 2 cols in it, ticketid and groupid typical data would be groupid ticketid 20 56 20 87 20 ...
0
votes
1answer
431 views

inconsistent Data Type -ORA-00932

I know there is question related to inconsistent datatypes but that comes from a query. while I am getting this error on database side when we try to access user defined datatype on our schema that ...
1
vote
2answers
347 views

Count events on “user based” sliding window

So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be ...
-1
votes
1answer
78 views

Select query from two tables which only specified rows [closed]

I want to try querying two tables, like this: Table A user_id primary_indicator ------- ----------------- 9012445 Y 9012445 N 9012442 N 9012332 Y 9012332 N 9012445 9012332 Table B ...
1
vote
1answer
578 views

Possible to query all SQL Server Indexes which have page locks enabled?

Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?
9
votes
2answers
5k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
0
votes
2answers
721 views

Database design of Ledger Account and Balance sheet generation

I am building an application which has deposit and loan accounts management, for which i have to develop ledger accounts and then generate balance sheet. Being new to database design, i am having a ...
6
votes
4answers
969 views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...