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

learn more… | top users | synonyms (2)

6
votes
3answers
10k 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
3k 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
3k views

SQL Server Management Studio .bak files

I'm new to SQL Server 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 ...
0
votes
3answers
1k 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
73 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
350 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
150 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
7k 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
2k 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 ...
5
votes
1answer
5k 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 ...
9
votes
4answers
7k 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
115 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
663 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
168 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 ...
23
votes
4answers
9k 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
10k 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
911 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
41k 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
126 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, ...
14
votes
1answer
748 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 ...
7
votes
5answers
25k 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 ...
0
votes
1answer
7k 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 ...
4
votes
4answers
2k 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
10k 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) ...
11
votes
2answers
3k 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
74 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
714 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
655 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
238 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
818 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
670 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
2k 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
916 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 ...
11
votes
2answers
14k 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 ...
6
votes
1answer
7k 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
2k 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 ...
0
votes
1answer
733 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
3k 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 ...
3
votes
2answers
11k 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
3answers
3k 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 ...
3
votes
5answers
3k 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
1k 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 ...
8
votes
2answers
560 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
80 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
341 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 ...
7
votes
2answers
3k 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 ...
3
votes
3answers
2k 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
2k 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
286 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
4k 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 ...