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

learn more… | top users | synonyms (1)

6
votes
1answer
2k views

Changing ANSI_NULLS setting on a table

Can we use the following query to update ANSI_NULLS option. sp_configure 'allow updates', 1 reconfigure with override update sysobjects set status = status | 0x20000000 where name = 'tblClient' ...
2
votes
1answer
92 views

Change-set SQL optimization

I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results. select ID, ...
6
votes
2answers
163 views

Optimization: One time vs Staging

I ran into a situation with two options: Use a single query with a cost of ~0.07 INSERT INTO @vals SELECT ... FROM ... JOIN ... JOIN ... WHERE a.col = 1 OR b.col IS NOT NULL Use two ...
2
votes
2answers
1k views

Trying to optimize query for performance (MySQL)

So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
2
votes
3answers
3k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
5
votes
2answers
11k views

How Does Oracle Handle Multiple Concurrent INSERTs Against One Table

I am trying to understand Oracle 11g a little more closely. My question is simple: how does Oracle handle two sessions that are attempting to insert records into a single table at the same time. For ...
2
votes
1answer
1k views

How to create a train schedule in SQLite3?

I am interested in what a SQLite3 database schema would look like for a train timetable. Generally this is presented in table format as the final result. Sample Schedule for M to F STATION A ...
4
votes
2answers
6k views

SQL Server 2008 R2 - Set tempDB size on full disk

One of my SQL Server 2008 R2 instances had a job that went mad last night, ended up with the job auto-growing tempDB until it ran out of disk space. To compound the issue I noticed that tempDB is ...
5
votes
4answers
3k views

CTE Running in Infinite Loop

My CTE runs in an infinite loop for a specific customer and I am not able to find out why. Here is the query: ;WITH ClassTree AS (SELECT ID, NAME, Parent_ID FROM ...
1
vote
2answers
270 views

PostgreSQL Database Migration

How can I migrate data from on PostgreSQL database to another PostgreSQL database? Is there any technique to migrate data with SQL scripting?
4
votes
1answer
10k views

Unique index corrupted SQL. Select query returns single row but create unique index fails

I am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here. When I run a select query for these key values, ...
8
votes
1answer
1k views

What are the security implications of sp_executesql?

By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql. However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql. As ...
0
votes
1answer
5k views

querying multiple values from one column [closed]

we have a table called transaction_details. when i write this query SELECT DESCRIPTION, item, cust_code, pack_description, uos, pack, total_value, total_value_vat, total_value_goods, price_indicator ...
2
votes
1answer
11k views

Compare a date and a timestamp with time zone with now() in the same query?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
3
votes
1answer
781 views

Error while creating index in SQL Azure

I'm trying to create an index on table [Children] in SQL Azure but get the following error: The operation failed because an index or statistics with name 'IX_Children_ParentID' already exists on ...
1
vote
3answers
120 views

User Rights - Can alter Sp's but not tables

How can i make user permissions so that user can CREATE\ALTER\DELETE Store Procedures, but cannot CREATE\ALTER\DELETE tables in database.
3
votes
2answers
311 views

how to GROUP BY on specific column and COUNT() the other columns?

I've queried a few tables and result is as below: I want to count correct col upon ct_id(course) col. I queried a lot but gained no success. How to query such a thing? for example in SELECT I ...
2
votes
1answer
885 views

SQL query to INFORMATION_SCHEMA.TABLES to only return those I have select access to

I currently have a query in SQL 2008 that can return me a list of tables for a given database, provided I have View Definition access to them. select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ...
9
votes
4answers
31k views

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
5
votes
2answers
5k 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
3answers
1k views

SQL Join slower than subquery on View with a Function

My database has a view that calls a function. Joining on that view is surprisingly slow. Is there a way to speed up joining to a view like that? I've included 2 queries which illustrate the issue. ...
5
votes
2answers
12k views

SQL Query to get last day of every week

Using MySQL, I need to query a table that has a datetime field. I need to count all the records for a particular week. Using date_format(colname, "%Y %V") returns the week #, but I need the last day ...
1
vote
1answer
742 views

SQL - double column used from primary key with autoincrement

I am user the following as primary key in the SqlDB: I am doing the Index as Autoincrement, but it is autoincremented in general not per user. Example userid=1,index=1, then insert another for ...
6
votes
1answer
2k views

Does the order of columns has a significant effect on INSERT?

Do we experience a noticeable difference in performance for these two queries? INSERT INTO table (col1, col2, col3, col4, col5) ... and INSERT INTO table (col5, col3, col1, col2, col4) ... Do we ...
9
votes
3answers
8k views

Storing date as integer (numeric), what are the advantages

Question 1 I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example ...
3
votes
1answer
139 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
5
votes
3answers
5k views

“Table of Constants” - is this common practice?

College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the ...
1
vote
2answers
9k views

Why is this MySQL proc using cursors failing to retrieve results?

I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, ...
2
votes
1answer
8k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) ...
2
votes
1answer
709 views

Replicate SQL Data Inside Firewall to Outside Firewall - Suggestions

Currently I have an internal CRM application that houses all my data and runs my entire company. The front end is ASP, and the back end is SQL. The external site will run SQL What I'd like to do ...
1
vote
1answer
278 views

How to create a Job for Merge Replication?

I want to create a step in a Job for Merge Replication. I basically want to know which command should I write so that a particular Subscription starts synchronizing?
1
vote
1answer
127 views

SQL database with temporal data

Is it possible to do this in a SQL database SELECT * FROM Employee WHERE BirthDate BETWEEN DATE 1970-01-01 AND DATE 1970-01-01 The result that was said to from this query is all the ...
1
vote
1answer
656 views

How to get a unique highscore per user

I have the following tables: users, highscores I would like to get the top ten best results in highscore. The scores should be unique, meaning one score per user. I tried using INNER JOIN and GROUP ...
0
votes
1answer
1k views

export SQL statements from Oracle 11g database via command line

I'd like to export an Oracle 11g database as a series of SQL statements via the command line. I'd like the functionality of SQL Developer (the process described here ...
4
votes
1answer
525 views

What factors prevent and delay TRUNCATE command?

I had a quite large table, perhaps a 50M rows and I had to truncate it. Usually truncate is really fast but in this case it took hours to complete. What are the factors that can prevent truncate ...
7
votes
1answer
560 views

How to store and query products the price of which depends on a complex set of conditions?

I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB. Let the products be packages of cable tv providers: { name: "Virgin TV", price: 20 }, { name: ...
3
votes
2answers
1k views

How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table. CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT ...
4
votes
2answers
442 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 ...
1
vote
2answers
6k views

Sum values from two tables ORACLE

I want to use two values, in two different tables, to be used as a total in a third table. For instance: table accommodation and flight Add value from acc_price with value from flight_price Add ...
6
votes
3answers
9k views

Why is this Full Outer Join not working?

I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join. I have 2 tables (which ...
11
votes
3answers
10k views

Why is an aggregate query significantly faster with a GROUP BY clause than without one?

I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one. For example, this query takes almost 10 seconds to run SELECT MIN(CreatedDate) FROM MyTable WHERE ...
8
votes
1answer
15k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
8
votes
4answers
27k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
6
votes
2answers
9k views

SQL query slow-down from 1 second to 11 minutes - why?

Question: I'm porting the below query (listing tables by foreign key dependencies) to PostGreSql. WITH Fkeys AS ( SELECT DISTINCT OnTable = OnTable.name ,AgainstTable = ...
1
vote
1answer
569 views

Right Tool for Data Mapping in Relation to Business Intelligence

When you are working as a BI developer you also need to create a data mapping between dimensional modeling and source system. What kinda application to visualize a data mapping?
1
vote
1answer
714 views

Why is the output of this query being returned like this?

I have a T-SQL query : select CAST(0 as float(53)),CONVERT(nvarchar(max),CAST(0 as float(53)),128) the output of which is 0,0.0E0 Can some one explain why it is not 0,0 ? The database is SQL ...
0
votes
1answer
194 views

Sensible data management tool for importing and exporting of large data files

I am looking for some sensible suggestions on data management tools for large data files. I have been experimenting aith excel and access only to find that access has limitations on import files size ...
1
vote
2answers
503 views

changing a range into discrete values for a field in oracle

I have a data table that contains records like this: ZIP Ground Air 010-041 005 305 150-163 004 304 As I have it right now, it's actually an excel file but I eventually need to ...
3
votes
3answers
2k views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
2
votes
1answer
317 views

Review Technical ETL process for knowledge management

I'm looking for source code and all related component/material to review technical ETL process between source database and Data Warehouse in order to increase my knowledge in technical Business ...