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)

3
votes
2answers
3k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
2
votes
1answer
140 views

Migrate Data and/or Schema from DBv1 to DBv2

I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null ...
3
votes
3answers
995 views

SQL Database size doesn't match with the total table sizes in the database

I have a database that is size about 104GB. When I calculate the total table sizes in the database it's only about 24GB. This includes Data+index+unused spaces. Why the DB size is too large?
1
vote
2answers
71 views

How can I return the number of values in each row which match a criterion?

I have a table "trial_volts" like this in an oracle database (I think it's 10g): trial_index NUMBER voltage_1 NUMBER voltage_2 NUMBER voltage_3 NUMBER voltage_4 NUMBER voltage_5 NUMBER And I want to ...
17
votes
2answers
3k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
3
votes
2answers
2k views

How to restore SQL Server database just by dbName and physical path

I have several database backups and I want to restore all of them to a server. Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and ...
1
vote
2answers
100 views

How does an alter work?

I have a table with a lot of records (more than 15M) and i am executing an alter query to add 2 columns to the table. the query is taking around 1hr to finish executing. So my question is: How does an ...
0
votes
2answers
186 views

Need help merging numerous databases into one. Same primary keys, tables, and relationships

I am not the smartest person at all when it comes to databases. It took me and Google a lot of time to create what I have created, and here's what I need. I built a database for a local charity ...
8
votes
2answers
2k views

How to delete a procedure which name is ambigous?

I am using informix... I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a DROP PROCEDURE myProc; then i get a error message ...
3
votes
2answers
8k views

How to install SQL Server Management Console in 2008 Express Version?

I started a dozen attempts to install the SQL Server Management Studio for the 2008 Express Version of SQL Server. What i use: Windows Vista 2007 SP 2 What i did: a) I downloaded the install file ...
1
vote
2answers
115 views

Quantity of entries

I have the below tables.The first one has posts with a foreign key to a category table.I'm looking for a query that gives me the amount of posts on every category. The post table. select ...
2
votes
1answer
472 views

Will Entity Framework and/or Linq-To-SQL queries show up as “Adhoc Queries” in SSMS' “Server Dashboard” Report?

Its pretty much all in the title. I have a SQL Server which is showing that 90%+ of CPU Usage and Logical IO Performed is due to "Adhoc Queries" The rest rest of the usage shows up under the actual DB ...
3
votes
2answers
263 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
155 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
1k 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
3k 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
3k 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 ...
14
votes
2answers
622 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
703 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
3k 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
376 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 ...
6
votes
4answers
4k 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 : ...
3
votes
3answers
3k 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
814 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 ...
5
votes
2answers
180 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
289 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
388 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
4k 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 ...
16
votes
2answers
2k 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
448 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. ...
4
votes
2answers
930 views

1 <-> 0..1 relationship

I have "1 <-> 0..1 relationship". For example: There is a set of users. Some users are customers. Thus we have two tables: the table users and the table customers. What is the best way to ...
-3
votes
1answer
5k 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
1
vote
1answer
75 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
211 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
349 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 ...
2
votes
2answers
1k 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 ...
1
vote
2answers
1k 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
994 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
299 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
1k 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
469 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 ...
5
votes
2answers
23k 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.
4
votes
1answer
310 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
601 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
431 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
2k 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
958 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
86 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 ...
2
votes
2answers
2k 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 ...
4
votes
1answer
5k 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) ...