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)

5
votes
3answers
2k 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
4k 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, ...
1
vote
1answer
3k 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
511 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
182 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
120 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
401 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
959 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
281 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 ...
5
votes
1answer
371 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
899 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
334 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
3k 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 ...
2
votes
1answer
167 views

Sql Server 2008 Database server Minimum Requirements

Does anybody knows what is the base scenario for the sql server 2008 r2 minimum hardware requirements?. By base scenario I mean: Database Size, average # of transactions, number of users connected, ...
6
votes
3answers
4k 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 ...
10
votes
3answers
4k 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 ...
5
votes
1answer
6k 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 ...
1
vote
0answers
69 views

How to write up the specs for the server that we will need

I am a dev. and have written basic app. which will be used by customers this app. Basically, I present them screen to select their excel file which has some billing info. we are analyzing this billing ...
7
votes
2answers
13k 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
5k 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
346 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
402 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
177 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
322 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
996 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
281 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 ...
2
votes
4answers
1k views

Remove sub-string from WHERE clause

I have a stored procedure that is using the SUBSTRING function in the WHERE clause. SELECT DISTINCT ColumnBB, ColumnCC FROM TableAA WITH (NOLOCK) WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA; ...
5
votes
2answers
144 views

Product of n Rows

From this data (assuming the number of rows is not known in advance): with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4) select * from q1; /* C1 -- 2 1 4 1 */ I ...
3
votes
3answers
2k views

Can Oracle SQL developer export data as a set of Update commands?

Oracle SQL developer gives us the option of exporting data. But it only gives us the option to export as a set of Insert statements. I have two databases with a mirrored table. One of them has some ...
0
votes
0answers
542 views

Update Statistics Maintenance Plan - Ambiguous column name error

Microsoft SQL Server 2008 R2 Web Edition (RTM) - 10.50.1600.1 (Intel X86) Windows 2003 R2 SP2 I am in the process of setting up a maintenance plan to update statistics on a daily basis for all user ...
0
votes
1answer
511 views

MySQL table with multiple values in one field

I'm building a database for a real estate company. The real estate properties are managed by sales people using a CodeIgniter website. How should I proceed with the database design for this table? ...
4
votes
1answer
541 views

How to query EndDate value from ReportServer..Subscriptions.MatchData

In SQL 2008 R2, I'm putting together some administrative queries on SSRS metadata to check proper configuration, and other sorts of proactive things. While querying the ReportServer db, I've been ...
1
vote
1answer
1k views

MySQL UPDATE to replace text in LONGTEXT field

I have a WordPress table wp_posts and I'd like to run an UPDATE that will replace a url from my old domain to my new domain. For example, let's say that one record in the field post_content has the ...
4
votes
3answers
3k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
2
votes
2answers
1k views

Adding Timestamp to Exported Filename in SQL Server Management Studio

I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is... EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout ...
2
votes
2answers
2k views

SQL Server Management Studio .bak files

I'm new to SQL SMS and need help. I have been asked to check that .BAK files restored be checked using SELECT queries in SQL SMS. I have tried to create a new database but get the error "CREATE ...
0
votes
3answers
574 views

Is it possible to edit sys.messages texts?

Is it possible to add a new language to SQL Server 2008 and edit SQL error messages with message_id less than 50000?
1
vote
2answers
62 views

stack data into a single column

SELECT * would result in: course_id + group ----------------- 1 + SE123 1 + BA134 2 + SE213 3 + SE223 3 + BA344 4 + SE222 I would like it be like ...
2
votes
1answer
260 views

Query logical processing phases while mixing old and new join syntax

Could anyone clarify why order of tables in FROM matters if I mix old and new syntax for joining tables? For example, SELECT * FROM table1 a, table2 b INNER JOIN table3 c ON (c.a_id = a.id) -- ...
6
votes
1answer
145 views

Are there any fully declarative implementations of SQL

I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes: select * from smalltable st inner join bigtable bt on st.btid = bt.btid select * from ...
1
vote
3answers
4k views

How should I Schedule SQL Server 2008 queries to run every month?

I need to schedule some queries to run at the beginning of every month (for monthly reports) and I read that creating a JOB in sql server 2008 was the way to go. Unfortunately, I don't have the ...
2
votes
3answers
981 views

performance of multi field primary key or 'contrived' 'semi artificial' key

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can ...
4
votes
1answer
2k views

Dropping unique constraint on PostgreSQL table taking 10+ hours

We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about ...
6
votes
3answers
2k views

Do you know an easy way to generate one record for each hour of the past 12 hours?

I have a report that shows the count of events for the past 12 hours, grouped by the hour. Sounds easy enough, but what I am struggling with is how to include records that cover the gaps. Here is an ...
3
votes
3answers
101 views

A join aggregate behaviour I can't understand

Query 1: SELECT c.class_id, SUM ( CASE WHEN s.student_id IS NULL THEN 0 ELSE 1 END ) as total_students FROM classes c LEFT JOIN students s ...
1
vote
1answer
350 views

Execute Stored Procedure against CMS from within a SQL Server Agent job

Is it possible to execute a stored procedure (or any T-SQL) against a Central Management Server (CMS) via a SQL Agent job? The idea here is to exercise the CMS to hit all the registered instances ...
2
votes
3answers
148 views

SQL Query Against Previous SQL Query Results

Is there an SQL database (for C#) that allows me to query against on-disk database and then later on (probably offline at client side) query against the in-memory result set using SQL? I basically ...
21
votes
4answers
6k views

SQL Server “empty table” is slow after deleting all (12 million) records?

I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
7
votes
2answers
5k views

SQL Server: query performance (search 2 million rows)

Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much? ...
4
votes
2answers
391 views

Splitting a column in two and returning a summary of results

Lets take a very simple table Department | Staff Name ---------------------|--------------- Web - Electronics | Matthew Store - Electronics | Mark Web - Home | Luke Store - ...