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

learn more… | top users | synonyms (2)

3
votes
2answers
395 views

Is index_type ignored on MySQL primary keys in create table?

I have seen a create table whose primary key had an identifier where it should have an index_type, something like that: create table a (foo INTEGER, PRIMARY KEY foo_id (foo)); The thing is that ...
3
votes
1answer
198 views

Grant to one; then revoke from all: does it work in Oracle SQL?

Consider the following: grant select on books to frank. revoke select on books from public. At the end of executing the above SQL script, will frank have 'select' access to the 'books' table?
1
vote
1answer
2k views

Can't restore database: A transport-level error has occurred when receiving results from the server

This is not a good day for me. I have a backup (master.bak) for SQL Server 2005 Express SP4 (v.9.0.500) that I want to restore to my master db instance. I am running SQL Server in single-user mode ...
2
votes
2answers
6k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
3
votes
2answers
6k views

Shared and IX locks causing deadlock (Sql server)

In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table). Now, we insert values into these 2 tables using the below 2 queries: INSERT ...
15
votes
2answers
2k views

Why did creating this new index improve performance so much when existing index included all columns in new index?

I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems The query in question is complicated so I'm ...
1
vote
2answers
1k views

Is Using Execute Immediate Bad Practice in Oracle?

So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data. As the sequence ...
8
votes
5answers
5k views

Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
1
vote
1answer
698 views

Using a sequence created in the same script in Oracle

SQL Server developer working on Oracle so apologies in advance if I'm fundamentally coming at this incorrectly from an Oracle perspective. I've got a script I've written which: Checks ALL_SEQUENCES ...
7
votes
4answers
7k views

Do SQL Server stored procedures cache data results?

I have heard this from friends, but I never investigated whether this is true. Is it true that the data results of an executed query are stored in cache? I mean, if I have a stored procedure like : ...
4
votes
3answers
9k views

Why so many logical reads?

I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server. SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK) JOIN ...
5
votes
3answers
1k views

Recalculate row size in SQL Server 2008?

It is possible to recalculate row size in SQL Server 2008? Take this example: declare @counter int declare @statement nvarchar(max) set @counter=0 drop table kua2 create table kua2(id int) while ...
6
votes
2answers
207 views

How the indexes are managed?

I am trying to understand how the indexes are managed for below common data types: 1- Numeric (Integer, Decimals) 2- String (Varchar, Char) 3- DateTime I have few questions: 1- How the indexes are ...
6
votes
2answers
474 views

Definition for “logical difference”?

I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
7
votes
3answers
632 views

How to express this constraint in a database schema?

I have the following functional dependencies which are in BCNF: a,b -> c a -> d b -> d With the additional constraint, that no a and b should be combined with a c, where a and b have ...
4
votes
3answers
6k views

Stored procedure executes too long

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
18
votes
2answers
3k views

Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

I have been getting myself very confused. Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE? My guess is that I would use it if I wanted to conditionally ...
2
votes
1answer
637 views

SQL Server 2008 - Return records in chronological order, given a separation factor

This question was originally posted here http://stackoverflow.com/questions/8492149/return-records-in-chronological-order-given-a-separation-factor and re-posted on this forum given a user's advice. ...
7
votes
2answers
2k views

Implementing a one to zero or one relationship in SQL

Let us say that I am designing a database for a scenario where there exists a one-to-zero-or-one (1-0..1) relationship. For example: There is a set of Users, and some Users may also be Customers. ...
-4
votes
1answer
7k views

T-SQL to list all the user mappings with database roles/permissions for a User

I am looking for a t-sql script which can list the databases and and the respective roles/privileges mapped for a particular user. Using SQL Server 2008 R2. Regards Mohammed
2
votes
1answer
81 views

SQL Server 2000 query error

When I attempt to run on the SQL Server 2000 to a query which was developed for SQL Server 2008, I receive the following the error. ambiguous column name SucNo Here is my query: SELECT ...
1
vote
1answer
287 views

Show all variations of 2 characters in postgres

How would I do this in postgres : Lets say I have an x and a y and I want to show all possible combinations : xx yy xy yx How can I make postgres do this for me ?
2
votes
1answer
487 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
3
votes
2answers
2k views

6th normal form, Recomposition query, efficient implementation

I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the ...
2
votes
2answers
3k views

Materialized View - Oracle

I have materialized view for which i want to alter its refresh time: REFRESH FORCE START WITH SYSDATE NEXT SYSDATE +1 DISABLE QUERY REWRITE I want to know few things in this. What does NEXT ...
2
votes
2answers
2k views

altering a db2 table with huge amount of data

I have a table in DB2 which is having 99 columns and 900,000 records of data. I want to alter the table with one extra column as below: ALTER TABLE MYSCHEMA.PRODUCT ADD UPDATE_DT DATE However, the ...
6
votes
3answers
306 views

Query performance

Why SELECT Barraportfolioname FROM portfolio WHERE id IN (SELECT DISTINCT i1.portfolioid FROM Import i1 LEFT OUTER JOIN Import i2 ON ...
2
votes
1answer
2k views

dbvisualizer: number of rows limited by maxrows

I'm running a simple query on an Oracle database table like select * from tableA and DBVisualizer cuts the query short at 1000 rows. How do I remove this limit? Message is "number of rows limited ...
5
votes
6answers
595 views

How do you choose a SQL statement timeout?

We have a huge (part-VBA, part-.NET-based) LOB application, with thousands of (different) SQL statements littered all over the code¹. Currently, there is one global "Query Timeout" that can be ...
12
votes
2answers
76k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
6
votes
1answer
351 views

What 'resets' the transaction log

My question might sound rather strange but I'm not really a SQL expert and I can't find the answer.. I'm trying to create a backup plan which creates a full backup twice a day. A differential back-up ...
0
votes
1answer
922 views

Getting the metadata about a Query and a Stored procedure in SQL Server

I would like to get all the names of the columns in a given query [SQL Textual Query] and also the columns that are in the SELECT clause in a Stored Procedure. Is there any ways other than using ...
1
vote
1answer
646 views

Stock management - needs suggestions

I need to create a database which will have below modules: Items [Food items, stationary] Items Stock Management [stock in out] Customers Items Sale Management Issue: The system will have ...
1
vote
1answer
7k views

Oracle Script For Getting Long Active Sessions

Is there any script that you could provide for v$session? I seem to be encountering an issue in which my oracle active sessions are filled up. This is when I do SELECT * FROM V$SESSION WHERE STATUS = ...
2
votes
1answer
2k views

Differing results from gv$session

The following query returns no results, yet it seems like it should. SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID'); In my 11.2.0.2 Standard Edition RAC environment the above ...
1
vote
1answer
105 views

What is QP layer?

From blogs.innodb.com: InnoDB Memcached with Binlog Capability In our earlier Labs release, we presented a MySQL NoSQL solution through InnoDB Memcached Daemon Plugin (see earlier Calvin’s and my ...
4
votes
2answers
5k views

Sort by match of LIKE

I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the ...
5
votes
1answer
13k views

How to find the date a login password was last changed in SQL Server 2008?

I'm porting stored procedures from Sybase 15 to SQL Server 2008. In Sybase this statement calcs an expiration date by adding a number of days to syslogins.pwdate (Date the password was last changed) ...
4
votes
3answers
672 views

Most performant SQL query needed

I'm storing documents with fields that are set in a DataSource table. The document is written to the database as follows: Document table (ID, DataSourceID) Document fields (DocumentID, ...
6
votes
3answers
3k views

Can you prevent more than X number of rows being updated in SQL Server?

Is there a way to configure SQL Server to limit the number of rows than an update statement can modify? Say I wanted the limit to be 30,000 rows and someone fired an update that would modify 45,000 ...
3
votes
3answers
1k views

SQL Azure dm_exec_query_stats clearing?

I'm doing a lot of work with SQL Azure at the moment, trying to improve performance of a web app. I am using the dm_exec_query_stats view to look for poor performing queries. Does anyone know a way ...
13
votes
4answers
11k views

Why are NULLs sorted first?

Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first? select 1 as test union all select 2 union all select NULL union all select 3 ...
35
votes
3answers
2k views

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...
7
votes
3answers
3k views

Performance issue with xpath in SQL Server 2008

I have a table with lots of large xml-documents. When I run xpath expressions to select data from those documents I run into a peculiar performance issue. My query is SELECT p.n.value('.', 'int') ...
3
votes
2answers
3k views

Reference a non primary key in another table in postgresql

I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.
2
votes
2answers
127 views

Database Files-Related Issue

I am using MySQL database and using Ubuntu linux machine. I noticed that my database, db_test, is under the path /var/lib/mysql/db_test There are files with the suffix .frm, .MYD, .MYI like the ...
2
votes
2answers
520 views

Advanced search engine or server for relational database

In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities. In the Project ...
1
vote
1answer
47 views

building a view w/ fields that only contain a certain word

Trying to build a simple SQL query... I have a table filled with "Yes" and "No" I want a view that only shows me records that contain "No" SELECT PhysicalBuild.Name AS Name, PhysicalBuild.Labels ...
2
votes
2answers
6k views

WITH using INDEX hint in Oracle

I have a fairly complicated query. I have written two different query to accomplish my goal. First one is using WITH, other one is using global temporary tables. First One: WITH A ( KNO .. ) , ...
1
vote
2answers
1k views

serial field issue

Q: I use a serial as a datatype for my primary keys in my tables. I insert a lot of data then truncate these data one after one many times to test the data entry. My question is: Is there any ...