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
5answers
8k views

Copying Oracle 11g express Database from one server to another

Basically, I am a MS-SQL developer,and relatively new to Oracle. Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another. In ...
1
vote
3answers
1k views

sql server 2008 - controlling size of log file

I am getting this error. Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc ...
4
votes
1answer
1k views

How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings: FOR COLUMNS SIZE 5 some_column and FOR COLUMNS some_column SIZE 5 both will create a ...
14
votes
1answer
10k views

How do I read Query Cost, and is it always a percentage?

I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric. According to any documentation I could find via ...
7
votes
2answers
7k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
1
vote
3answers
558 views

Upgrading to SQL Server 2008 R2 but downgrading the edition - will this cause problems?

I am looking at upgrading our SQL Servers from 2005 Enterprise edition to SQL Server 2008 R2 Standard edition. Obviously, the upgrade to 2008 R2 gives us more features even in Standard edition, but I ...
1
vote
3answers
689 views

why locks a table will improve performance during transaction?

I just started to read about table lock in SQL (from this page) It saids: table lock may reduce the overhead of acquiring a large number of row or page locks and save overall locking time ...
1
vote
1answer
67 views

How do I determine if a database restore file should be restored to the database based on backupset?

I have a production database that is fully backed up nightly and incrementally backed up hourly. On our backup machine, we have the differential backups and full backups restored ion a regular basis ...
2
votes
1answer
14k views

In which folder / directory does SQL 2008 store files?

Where can I set the folder to store a database in when I create a new Database in the Microsoft SQL 2008 Server?
1
vote
3answers
465 views

How to implement superkey sub key method on cascading tables

If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to ...
5
votes
2answers
3k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
1
vote
3answers
2k views

Local user login access in sql server 2008

I have a created a local user called 'MynetDB' with SQL Server Authentication (password) in SQL server 2008. Server roles is public and user mapping to 2 databases as db_owner and public. Status has ...
1
vote
1answer
622 views

SQL database restore failed [duplicate]

Possible Duplicate: SQL database restore failed I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the ...
3
votes
1answer
314 views

SQL database restore failed

I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that ...
6
votes
4answers
544 views

Find the first gap in an aggregation of integers in SQL Server

Let's say I have a table called dbo.GroupAssignment. GroupID | Rank ------------------ 1 1 1 2 1 3 2 1 2 3 2 4 3 2 3 4 3 5 The PK is GroupID, Rank. Normally, the ranks ...
31
votes
3answers
24k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
4
votes
3answers
3k views

NULLs in a composite primary key - SQL Server

I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
1
vote
2answers
1k views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
3
votes
2answers
5k 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
179 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
1k 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 ...
19
votes
2answers
4k 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
105 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
294 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
3k 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
11k 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
122 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
583 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
341 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
186 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
4k 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
4k 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
1k 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
917 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
4k 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
535 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
6k 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
6k 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
927 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
192 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
387 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
499 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
5k 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 ...
17
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
561 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
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