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)

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
3k 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
352 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 - ...
1
vote
2answers
22k views

Error: Specified cast is not valid. (SqlManagerUI)

I am trying to restore database created on server to my localbox however i am getting 'Error: Specified cast is not valid. (SqlManagerUI)' error message. Server Info: Windows Server 2008 Standard ...
3
votes
3answers
116 views

~15 data sets w/ 25k rows & ~20 columns + quick one time reports = Access VS SQL?

I am working with around 10-15 datasets each containing around 25,000 rows and around 20 columns. Data is text in name/adress/desc and the rest numbers. I need to be able to calculate certain fields, ...
1
vote
0answers
33 views

Monoblock, Multiblock queries?

What exactly mean the terms monoblock / multiblock queries in data warehouses when talking about materialized views? Does a monoblock query just use one data source (a normal table) while a multiblock ...
14
votes
1answer
418 views

Poor SQL Server performance when management studio closed

I've noticed a strange behaviour of SQL Server 2008 r2 express: usually my query execution time is ~650ms, but if I open Management Studio and run any simple query (for example SELECT * FROM ...
2
votes
5answers
13k views

Full Outer Join in MS Access

I have two staff lists: List A: StaffID Supervisor ==================== 0001234 NULL 0001235 0001234 0001237 0001234 0001239 0001237 and List B: StaffID Supervisor ...
-1
votes
1answer
3k views

sql server - could not allocate space for object

I am trying to retrieve the first 1000 records of my view and am getting this error message: Msg 1105, Level 17, State 2, Line 2 Could not allocate space for object 'dbo.SORT temporary run ...
3
votes
4answers
824 views

Help with Query. Finding records that have the same relationships (MySQL)

I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this: listings ---------------- id (int) name (varchar) listings_taxons ...
6
votes
3answers
5k views

How to make sure that simultaneous INSERT and SELECT on one table won't make a deadlock?

I have the following table in my SQL Server 2008 database: CREATE TABLE [dbo].[SomeTable] ( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Column1] [varchar](30) NOT NULL, [Column2] [varchar](30) ...
5
votes
2answers
1k views

Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios

I need to keep track of deleted items for client synchronization needs. In general, is it better to add a tombstone table and a trigger that tracks when a row was deleted from the server database - ...
1
vote
1answer
71 views

How can this data be joined?

Definition: table_A table_B id,description id,description,id_table_A Column id_table_A is a foreign key to the primary key of table_A and is not mandatory. Sample data: ...
5
votes
2answers
533 views

Large accounting table, best way to partitioning by two dates

I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call ...
4
votes
1answer
519 views

How to change database owner for login and database created inside stored procedure?

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...
2
votes
1answer
130 views

Can I use a Clob as a table key (from Java)?

Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)? I know it is probably a ...
1
vote
1answer
445 views

oracle - does this have the potential to cause a lock?

Note how no commit was done in between each statement. Could this cause a table lock or blocked session? delete from tableblah; insert into tableblah (select * from othertable);
1
vote
1answer
478 views

Unable to connect to MySQL cluster database

I am unable to connect to mysql cluster database after starting my management node, data node, mysql node. I made the necessary changes by adding the loadbalance in the dbURL. But I am not able to ...
1
vote
1answer
883 views

creating a table from a selection and then using group by - oracle sql

I'm working in oracle and I can't figure out what's wrong with this statement. The error it gives me is must name this expression with a column alias CREATE TABLE shipping_costs_grouped AS ...
3
votes
3answers
538 views

Database growing like crazy - what do I do?

I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ...
8
votes
2answers
5k views

Table not getting created under dbo schema

When creating tables in SSMS, I've realized that if you execute the following statement: CREATE TABLE [tableName]; the table will be created under your own schema (and not dbo). So in order to ...
2
votes
1answer
3k views

How is INDEX on Composite Primary Key in mysql?

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually? The reason that I am asking this question is that when ...
2
votes
1answer
567 views

how do I limit the characters in selected row and con-cat with '…'

I have a query which return a column named comment. I want to limit the number of characters returned by select statement to 50 and if the comment row contain more than 50 characters, I want to append ...
1
vote
0answers
217 views

Full Text Search with Encryption SQL Vs NoSQL? [closed]

Can any tell Which will be better for online and offline projects
0
votes
1answer
313 views

How to load more than one MS-Access-db tables with data in SQL-Server-DB

I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service. One of my computer folders contains more than one ...
1
vote
1answer
2k views

strategy for enabling a general query log on MySQL 5.0.95 with no restart?

I would like to configure a general query log for a busy MySQL 5.0.95 production server without effecting the availability. Obviously I can set the "log" option in the my.cnf file and restart the ...
1
vote
2answers
6k views

How to use RESTRICT for Foreign Key in mysql?

In the database structure of CREATE TABLE Country ( name varchar(40) NOT NULL, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE City ( name varchar(40) NOT NULL, ...
9
votes
2answers
1k views

Treating certain Arabic characters as identical

In Arabic we have characters like ا (alef) and أ (alef with hamza). Users write them interchangeably and we want to search them interchangeably. SQL Server treats them as separate characters. How ...
2
votes
4answers
2k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
1
vote
1answer
585 views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
6
votes
2answers
253 views

Do agile software development approaches apply to SQL?

I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any ...
1
vote
1answer
68 views

Is it safe to change the name of a database?

I have a SQL Server database which has data for several customers/devices. We are changing the prefix of our website (asp.net) and our application dynamically loads the proper connection string based ...
5
votes
2answers
271 views

How to find out what is causing my Identity column to keep going up

I have a table with an int identity as the PK. Something is causing it to increment, and I'd like to know what that is. To give a quick example, yesterday I inserted a record and got ID 41,773. Today ...
3
votes
2answers
1k views

Using Foreign Key and References for ON DELETE CASCADE in mysql

I was thinking that these two ways for creating foreign keys are the same CREATE TABLE child1 ( id int(11) not null auto_increment, parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE ...
-14
votes
1answer
986 views

Please help with these database interview questions [closed]

What is your company backup policy? If something happend to your db how to restore it? What type of clustering present used in your enviroment? Whis is the best replication? Why? If sql server want ...
3
votes
3answers
1k views

How to restructure this slow query containing subquery?

This query takes about 2-6 seconds to run depending which tag id(s) is/are selected: SELECT t2.id, t2.name, count(*) as kpl FROM (SELECT pt1.post_id FROM posts_tags pt1 WHERE pt1.tag_id ...
5
votes
3answers
1k views

After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

I have two tables Location Master ([LocatioNo],[LocationName],[Description]) Location Data ([LocationNo],[LocationCode],[DDate],[BillNo],[Model],[Quantity],[SNo]) and a view involving the 2 ...
4
votes
2answers
236 views

Complex SQL query - need help

I have a really hard SQL question! I've inherited a system that allows people to enter dog shows. I have inherited the database with its present architecture, and since there are office systems that ...
4
votes
2answers
2k views

Access denied for user 'myuser'@10.% to database 'mydb' - Mysql Import SQL Script

I have a database that I am trying to recreate at my web host. I used mysqldump to create a sql script. My web hosting company, IPage, has a hosted mysqladmin site where I am supposed to be able to ...
1
vote
1answer
602 views

Why is my maintenance plan save overwriting job ownership?

I'm slowly untying all the system dependencies to the previous DBA's system account and moving them to a service account. The old DBA had ownership of all the jobs in our maintenance plan. In SSMS I ...
0
votes
3answers
142 views

Is it possible to check a date in SQL?

I'm trying to write a Microsoft SQL Server stored procedure which takes an account ID, and returns an authentication ticket and whether the ticket is expired. Something like this: SELECT ...
0
votes
1answer
105 views

Regular expression problem

SELECT FIRSTNAME,LASTNAME, EMAIL FROM CUSTOMERS WHERE REGEXP_LIKE(EMAIL,'[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]','i'); I know this is not practical or make a ton of sense, but its a ...
2
votes
1answer
2k views

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

I have to restore a DB table every day from a postgresql 8.4 to 8.1. For the backup, I use this code (windows server): D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > ...
2
votes
1answer
356 views

Table Statistics Affect Index Only Query?

We have a query that was getting poor performance. The root of the problem could be reproduced using a simple query accessing only one index to retrieve one column (the indexed column) from eight ...
12
votes
7answers
12k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...