Tagged Questions

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
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
101 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
245 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
9k 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
116 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
505 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
284 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
164 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
710 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
759 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
414 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
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
4k 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
854 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
184 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
317 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
415 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
487 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
1k 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
6k 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
76 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
219 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
370 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
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 ...
1
vote
2answers
2k 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
1k 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
302 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
486 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 ...
6
votes
2answers
29k 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
315 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
650 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
459 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
3k 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
1k 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
88 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
6k 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
507 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
2k 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
2answers
568 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 ...
0
votes
1answer
8k views

Working example of OPENDATASOURCE command using SQL Authentication

I need to run some ad-hoc queries on several hundred servers that are not part of a domain. Each server has a low-privelage SQL user account that has read-only access to a few tables of interest. My ...
7
votes
3answers
4k 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 ...