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)

2
votes
2answers
171 views

Representing SQL constraints on a table

I have this table: CREATE TABLE `sold` ( `part_ID` INT(11) NOT NULL , `date` DATE NOT NULL , PRIMARY KEY (`part_ID`, `date`) , FOREIGN KEY (`part_ID` ) REFERENCES `part` (`part_ID` ); This ...
0
votes
2answers
544 views

Remote Desktop Session's Machine ID for SQL Server Query

How to get machine name of a user, who is connected on an instance through Remote Desktop and used windows authentication login. I need it for each query she has executed. sys.dm_exec_connections just ...
4
votes
3answers
440 views

Backup strategies for zero data loss

I am working on one project and it has around 100K records. We can afford a downtime of 1 day but we need the data to be always available to us in latest state. I've gone through two options: 1) ...
2
votes
2answers
228 views

Is this relation in 3NF?

I created a relation called Customer and defined as: I just wanted to know if it is in 3NF? My concern is regarding address, since customer may have the same address.
2
votes
1answer
845 views

ON DELETE cascade alternate

I am trying to create a database for 2 Tables like: Table1 : tb1-id(pk) | tb2-id | other Table2 : tb2-id(pk) | other FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE ...
0
votes
1answer
102 views

Implementation of stack in MySQL

I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion. The Stack must be a collection of tables with the stack top pointing ...
6
votes
1answer
538 views

Does large table variables fill up logs in tempdb?

I'm running into an issue with a DBA who claims that table variables reside in ldf of the tempdb and when large amount of data is loaded into the table variable, the tempdb's logs fill up. The DBA's ...
2
votes
2answers
226 views

SQL Max() revision number

This SQL returns a table with everything unique, which is what I want. However, if there are two (or more) event numbers, I need it to return the line with the highest rev_num for that eventnum. I ...
1
vote
0answers
58 views

Will Startup Procs Run Again After Database Recovery?

We have a number of stored procedures that run on startup via the sp_procoption startup: exec sp_procoption 'ProcName', 'startup, 'true' A customer's server started up with a very large database in ...
4
votes
1answer
80 views

Covering Index Question

I have the following query Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED, PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND, CASE Left(PT.INVENTREFID, 3) WHEN ...
1
vote
2answers
122 views

Does this query make sense?

I recently migrated a database from Sybase ASE to Microsoft SQL 2008r2. One category of queries that was broken in transit is: SELECT person_id FROM leaveperiods WHERE group_id=146 GROUP BY ...
0
votes
1answer
171 views

What is the maximum number of rows in a clustered index on datetime column?

I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.
0
votes
3answers
195 views

Select MaxDate where hh:mm:ss makes the difference

How to select the maximum date from a table where hh:mm:ss is selected? My query is the following, and returns the latest date, but if there are different times recorded, the date will be returned ...
0
votes
2answers
387 views

Large time difference between two almost identical simple queries…why?

I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following: SELECT ...
1
vote
2answers
3k views

How to find DELETE and UPDATE statements that have been executed on a database?

I am trying to find out what delete and update statements have been executed on a certain database in a certain period of time. I came across this following query, but it generates an error message: ...
2
votes
2answers
635 views

Check if row exists

I need to write a query that will insert a row only once, even if the query is run multiple times. Being new to SQL (well new-ish), I did an if not exists(...) but a friend said he preferred deleting ...
6
votes
2answers
3k views

SQL Server alter table to change TEXT to NVARCHAR(MAX) greatly increasing database size

I need to update a SQL Server database thats about 18GB in size to change a significant number of TEXT columns to NVARCHAR(MAX). The problem I'm having is after executing all the alter table ...
0
votes
0answers
62 views

Database too slow after CHARSET Conversion

I have an Oracle database which runs on version 9.0.1 with WE8859P1 charset and i'm trying to migrate it to the version 9.2i which has the WE8MSWIN1252 charset. The conversion is made without any ...
1
vote
3answers
471 views

Which RDBMS has the most advanced “table-valued” functions?

Every Database Management System focuses on specific features and applications. I am using mysql, probably as it is popular and satisfies common needs. However, I came across a problem, which needs ...
4
votes
1answer
2k views

Join To a @Table Variable is running ineficiently

Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
3
votes
1answer
167 views

Creative workaround for granting edit rights to individual jobs for a group?

We have two developers that say they can not function without full rights to a few jobs on our database server. They both want rights to the same jobs. Given that it seems that MS doesn't seem to be ...
2
votes
2answers
2k views

MySQL Replication without stopping master

I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then ...
0
votes
2answers
2k views

Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question. As stated, this query does return me 95% of the data I need -- SELECT ...
0
votes
3answers
719 views

Fixing a query that returns failed job information

I' using a SQL Server 2008 Sp3 environment. I have a query that returns information about failed jobs from the last 10 minutes. If a job has two schedulers (for example - a job that runs every 10 ...
1
vote
1answer
536 views

Question about Oracle's interpretation of “host cpu utilization” in v$sysmetric view

I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization" It says in the metric_unit as % busy/(idle + busy), but what is busy? ...
6
votes
2answers
201 views

How to script database validations?

I want to create a smart way to execute a SQL script when rolling out DB changes into QA, UAT and production environments in order to smoke test database changes are correct after maintenance has been ...
1
vote
1answer
99 views

Is it possible to query “NOT IN” in NO SQL?

I asked the same thing about a specific NoSql engine (Big Table) on SO. It seems that Big Table doesn't support NOT IN queries? Is that possible in any other NoSQL databases? Will it ever be possible ...
1
vote
0answers
540 views

Efficiently retrieve big amount of data based on date range

Table EVENTS TimeStamp |O.Name |Location |Source | 2008-11-12 |Pretorian|Európe |England | 2009-12-24 |Lex Rosa |Európe |Italy | 2010-01-01 |Nasdaq |USA |Whasington | ...
2
votes
2answers
2k views

CREATE INDEX for long columns

I am looking for alternative to create index on long column: create table line ( field_key integer not null, value varchar(4000), ... ); create index key_value_idx on line ...
3
votes
1answer
17k views

What queries is Oracle executing, how frequently, and time-taken?

I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database. I want to find out the following for last 7 days: SQLQuery ...
2
votes
2answers
2k views

Only one NULL for one part of primary key

I have two date columns : "start date" and "finish date". I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one ...
1
vote
1answer
69 views

Scheduling a backup in SQL Server 2005

I am trying to schedule back up in SQL Server 2005 with management option. I am unable to find database name while scheduling a backup with full option. Database name is not over there in selected ...
1
vote
1answer
61 views

Hard Parse on Non-Identical Statements?

Let's say I have this query : SELECT X FROM Y If I run it for the first time then it would do a hard parse. After that, I run this query : select x from y With just a difference in alphabetical ...
7
votes
2answers
1k views

Different results rebuilding an index online and offline

I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages. When I run ...
0
votes
0answers
1k views

Postgres error ( XX000: invalid memory alloc request size 4294967290 )

I'm getting the following error in my postgres 8.4 database: XX000: invalid memory alloc request size 4294967290 Operating System : Windows 7 64 bit Memory : 8GB Database Version: Postgres 8.4 ...
4
votes
1answer
758 views

Store SSRS Report export directly to database

As a requirement, I have to check for a certain condition on some data in the database, if the condition is true, then I have to create a pdf report and store it on a table. I have everything set up ...
1
vote
1answer
495 views

mysql query - how to delete duplicates based on highest int?

I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId. ...
3
votes
1answer
4k views

The server principal “<mydbuser>” is not able to access the database “<mydatabase>” under the current security context

When I try to install a new database, I'm getting this error from SQL Server 2008. The server principal "<mydbuser>" is not able to access the database "<mydatabase>" under the current ...
2
votes
1answer
3k views

Select the top 5 records and the specific column from each table in the same database

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select top 10 * from {table_name} So, is it possible to display the first 10 records PLUS only ...
0
votes
3answers
4k views

How to select the first 10 records for each table in the same database using sql

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select * from {table_name} So, is it possible to display the first 10 records for each table ...
7
votes
2answers
476 views

What is the purpose of the USING parameter in SQL?

Working with some code on a standard MySQL database, and I came across this statement: SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 What's throwing me is: what is USING for, ...
4
votes
1answer
81 views

Index Evaluation

I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index. The non ...
2
votes
0answers
213 views

Limit Output Flat File Size In SSIS

I am using Flat File Destination to export my data's to a Text file. I need to limit the output File size to 1 MB. Is it possible?
0
votes
2answers
70 views

some tips on setting up sql server database server

I am currently setting up a new sql server database server with databases. It is supplied by a third party company. I need to request access for them around ports etc. I would like to be able ...
2
votes
2answers
1k views

What is the difference between using varchar(100) and varchar(255)?

I want to know difference between varchar(100) and varchar(255) in terms of memory usage and programming problems of using more memory.
3
votes
1answer
67 views

Special ordering

I have a hierarchical query but I don't manage to order it as I want. I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly. This a ...
1
vote
2answers
191 views

Suggestion for storing a lot of simple data

I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables ...
7
votes
2answers
859 views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
3
votes
1answer
74 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...
0
votes
1answer
72 views

Faster joining on 'most recent' related rows

I have a bug tracking application. The bug table stores very little, because every state change to a bug happens by adding a comment. For example, the current state of a bug is determined by the state ...