DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

3
votes
2answers
134 views

I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin?

I have an Oracle database (10.2.0.1.0 I believe) on a Windows 2003 server that I believe has successfully crashed the OS itself twice. Debates on platforms and stability aside, the evidence suggests ...
3
votes
3answers
253 views

Does separating VARCHAR values based on size have a performance gain?

I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ...
11
votes
3answers
7k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
5
votes
2answers
604 views

Date alignment and pair matching extraction best done with TSQL or C#?

For starting with: my title sucks, so help me figure out a new one? I can't post all the SQL here (over 30k characters for the lot of it), so I stuck it on pastebin.com The problem: I get an XML ...
4
votes
2answers
740 views

Fetch first 9 records and make one more record which will be as “Others” in MySQL

I am fetching records from one table with count of one field with other field as name. I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as ...
6
votes
3answers
5k views

LIKE to select the independent existence of the word whereever in the text

I have a table with the following texts and the key word i am searching for is 'Search'.So i have written a query SELECT [ID] ,[TextValue] FROM [dbo].[SearchLike] WHERE [TextValue] LIKE ...
1
vote
3answers
268 views

Selecting an entry that spans adjacent months

So i have a database table that contains the a "event name", "start-date", "end-date" columns which is used in a calendar application im working on. The events get shown one month at a time, and that ...
3
votes
2answers
108 views

How do you handle adding nullable columns to each other in a query?

Currently whenever I write a query that is adding columns that can contain nulls I resort to wrapping each field in isnull or coalesce, such as coalesce(score1,0) + coalesce(score2,0). Is there a ...
3
votes
5answers
2k views

How different are the top DB vendor's SQL (DML-) dialects from each other?

Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from ...
6
votes
1answer
3k views

SQL Server 2000 : List and Disable all triggers

I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the ...
4
votes
1answer
352 views

what is the first schema checked?

I am a little confused right now. Can someone answer me with a simple answer to this question. I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test]. ...
14
votes
1answer
3k views

How to get the MAX row

In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability. ...
3
votes
2answers
700 views

Why can't I use newid() in a user-defined table-valued function?

I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this? I have an alternate solution that suits my needs, so I'm not looking for ways to ...
19
votes
3answers
7k views

What is the best way to get a random ordering?

I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How ...
14
votes
1answer
15k views

Difference between Hash, Merge and Loop join?

In SQL Server you can specify the join hints: HASH JOIN MERGE JOIN LOOP JOIN What is the definition of those three join hints, and when should each be used?
7
votes
1answer
2k views

Unexplained InnoDB timeouts

I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example: //# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE photos ...
2
votes
2answers
2k views

What is the best way to filter or otherwise manipulate the results returned by a stored procedure?

Often I find myself wanting to filter or order the results returned by execing a stored procedure - for example to order the output of sp_who2 by login or CPU Time, or filter it by host name. What is ...
18
votes
5answers
8k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
25
votes
3answers
12k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
2
votes
1answer
2k views

What is the corresponding MySQL TimeStamp in MS SQL Server?

I need a timestamp to be created automatically when I insert information in a table. mySQL has TimeStamp but MS SQL Server TimeStamp doesn’t correspond mySQL. Is there any other way around?
5
votes
4answers
4k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
2
votes
6answers
894 views

Optimize this SQL query for performance

I'm trying to optimize the following statement: 'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR) +'/00000' +CAST(@number+1 AS VARCHAR) The statement produces a value ...
8
votes
3answers
2k views

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find ...
18
votes
3answers
7k views

Is it a good idea/approach to index a VARCHAR column?

We're using PostgreSQL v8.2.3. There are tables involved: EMPLOYEE and EMAILLIST. Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email) 2 tables are ...
17
votes
3answers
3k 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 ...
18
votes
11answers
49k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
5
votes
2answers
1k views

How do I search to see if a MySQL user exists on the system?

I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I ...
5
votes
4answers
11k views

Building Dynamic Oracle Where Clause

I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
5
votes
1answer
640 views

Case Behavior Changed Between 11.2.0.1 and 11.2.0.2

I have a case open with Oracle and they say they can't reproduce this. Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query ...
5
votes
4answers
8k views

Emulate REGEXP like behaviour in DB2 SQL

I posted the same on stackoverflow (please, let me know if I have to delete one). I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries). So I ...
6
votes
1answer
787 views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) ...
4
votes
1answer
378 views

Execution plan issue requires reset on SQL Server 2005, how to determine cause?

We have a web application that delivers training to thousands of corporate students running on top of SQL Server 2005. Recently, we started seeing that a single specific query in the application went ...
4
votes
2answers
162 views

Randomizing table contents and storing them back in the table

I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read ...
0
votes
1answer
626 views

One record, multiple updates in one query (MySQL)

I run into a problem for which I didn't find an answer anywhere: If I have a table in which one specific value(here amount) from multiple records can add into a target record(from the same table), can ...
9
votes
3answers
7k views

How do you clear out all old query plans from within Microsoft SQL Server?

We have an off the shelf application that uses a Microsoft SQL database. Within this application we pick and choose various selection criteria for each report. This application then runs these ...
13
votes
4answers
3k views

What is the significance of a semicolon at the end of SQL*Plus commands?

Some statements like create table, insert into etc take a semicolon at the end: CREATE TABLE employees_demo ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name ...
6
votes
3answers
585 views

Online resource for writing Access SQL statements [closed]

I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, ...
7
votes
4answers
2k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
1
vote
1answer
527 views

What does HSJOIN mean in an Explain Plan?

I have the following EXPLAIN PLAN results from a query on my DB2 database. 0 SELECT STATEMENT ( Estimated Costs = 5.928E+02 [timerons] ) 1 RETURN 2 HSJOIN 3 [O] ...
27
votes
8answers
25k views

What is the difference between an INNER JOIN and an OUTER JOIN ?

I am new to SQL and wanted to know what is the difference between those two JOIN types? SELECT * FROM user u INNER JOIN telephone t ON t.user_id = u.id SELECT * FROM user u LEFT OUTER JOIN ...
7
votes
2answers
5k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
13
votes
4answers
33k views

How to find the latest SQL statements within the database?

I like to get the latest executed statements within my database, along with performance indicators. As such, I like to know, which SQL statements were most CPU/DISK intensive.
4
votes
1answer
5k views

How can I query names using regular expressions?

How could I search for a specific pattern in a column? For example, I like to get all user names that start with either letter A or B. (btw: this is tagged oracle, but it might be interesting in ...
15
votes
3answers
4k views

Traversing tree-like data in a relational database using SQL

Is there a way to traverse tree data in SQL? I know about 'connect by' in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using 'connect by' is easier ...
74
votes
7answers
13k views

Where are some useful SQL puzzles to teach SQL in a workplace?

I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice. I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there ...
16
votes
2answers
46k views

Sql Server database in recovery: How to stop, Take ofline or delete?

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so. I need to either stop the recovery, or kill it in some way. I dont care one ...
4
votes
1answer
347 views

Query Execution Time

I am querying a SQL server 2008 database table having more than 500 million records. The query I fired to move around 10 million records to temp table took 9 hours. Similar query I fired which has ...
12
votes
2answers
143 views

What programming language is this?

:#PCA=Campaign//CampId//CampDesc//CampBusId = 2 :#PE=PubEdition//EdId//EdCode//EdCampId = :#PCA :#PCT=ContactType//ContTypeCd//ContTypeDesc//ContTypeUsrId in (21, 25) ...
2
votes
2answers
4k views

SQL Server 2008 DBCC Problems

We have a database Ms DBCC CHECKDB (MS) Or ALTER DATABASE MS SET SINGLE_USER DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE MS SET MULTI_USER Error message Msg 0, Level 11, State 0, ...
12
votes
2answers
28k views

How to pass in parameters to a SQL Server script called with sqlcmd? [closed]

Is it possible to pass parameters to a SQL Server script? I have a script that creates a database. It is called from a batch file using sqlcmd. Part of that SQL script is as follows: CREATE DATABASE ...