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
1answer
558 views

SQL Return Count of Answers with 0's for Choices from another table

My question went dead after one unhelpful answer on SO here I need this to be optimized because it will be called often. It is going into a stored procedure. I am given @slide (tblILDSlide.id) and ...
3
votes
2answers
2k views

PostgreSQL: count datasets / lines containing NULL values

I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved. For example: CREATE TABLE tab1 ( id INTEGER ...
5
votes
5answers
610 views

SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
4
votes
2answers
927 views

MySQL connection doesn't work anymore

I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can ...
5
votes
4answers
7k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
4
votes
1answer
867 views

Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

I wrote this script without COMMIT in a new Query window (Query1): BEGIN TRAN SELECT [RegionID], [RegionDescription] FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK) WHERE RegionID = 3 and in ...
4
votes
2answers
2k views

SELECT statement with an explicit list of columns and then an asterisk (*)

SQL Server allows to use SELECT statements similar to: SELECT FirstName, LastName, City, * FROM dbo.Customers When I try to execute such a query on Oracle database, I get the following error: ...
6
votes
2answers
1k views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
11
votes
5answers
94k views

How to update a table from a another table

I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2) Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. ...
5
votes
3answers
901 views

What could be the reason for disallowing a sub query in the values clause?

For example SQL> create table dates(d date); Table created. SQL> insert into dates select sysdate from dual; 1 row created. SQL> select * from dates; D --------- 28-MAY-11 SQL> ...
6
votes
5answers
15k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
40
votes
1answer
92k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
12
votes
1answer
2k views

Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?

I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks ...
3
votes
1answer
333 views

Badly performing query plan after adding ORDER BY to Full Text CONTAINS search

The following query is proving beyond my abilities to fix - hope someone can help. SELECT top 100 Id, Name, InsertDate FROM Foo WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"') ORDER BY ...
2
votes
1answer
278 views

MySQL Update: why would rows matched change based on the set clause?

I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the ...
2
votes
2answers
347 views

update stored proc ony fields that are passed to the proc and leaving others alone

i have a stored proc that does a simple update (see below). i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want ...
4
votes
3answers
360 views

Help with complicated MySQL query

I asked an earlier question about modeling MySQL data modeling help. The recommendation was the diagram labeled Multiple Tables for Role and Sponsor. Now I'm trying to write the queries and ...
5
votes
1answer
472 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...
1
vote
1answer
115 views

Are there situations where queries are formed geometrically?

The topic came up in this other question. We were discussing sign value on a primary key having any significance. If there are good times to use geometry in building queries the sign value could be ...
1
vote
1answer
1k views

How can I alter the type of a money-column to decimal in PostgreSQL?

I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead. How can I do it? I tried with: alter table my_table alter column vat type ...
8
votes
4answers
2k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
1
vote
3answers
333 views

Insert to particular location in Oracle DB table?

Suppose I have a table containing the following data: Name | Things ------------- Foo | 5 Bar | 3 Baz | 8 If I want to insert a row, so that the final state of the table is: Name | Things ...
10
votes
5answers
20k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...
21
votes
4answers
7k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
5
votes
2answers
10k views

Relational Design - Multiple tables into one foreign key column?

Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was. But, the different types of meat are unique in some way (such as USDA ...
15
votes
3answers
782 views

Alternatives to concatenating strings or going procedural to prevent SQL query code repetition?

Disclaimer: Please bear with me as someone who only uses databases a tiny fraction of his work time. (Most of the time I do C++ programming in my job, but every odd month I need to search/fix/add ...
8
votes
2answers
792 views

LNNVL Justification

LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of ...
8
votes
2answers
10k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
1
vote
3answers
126 views

Can I automate the “on” statement from MySQL queries?

Background In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL. I would like to know how (if) I can simplify join statements, e.g. by setting default fields ...
5
votes
4answers
1k views

how to migrate data from spreadsheet to SQL server management

I want to know how to migrate data from spreadsheet to SQL server
13
votes
4answers
33k views

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights). There are no other ...
9
votes
5answers
4k views

Group By hour over large dataset

Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits ...
7
votes
2answers
2k views

How to effectively query and aggregate a normalized SQL db?

In relation to Most efficient way to return multiple aggregates in a single stored proc? I have an email type application and want to select all the messages (inbox) for a user. The problem is that I ...
4
votes
4answers
343 views

Do cross-joins get optimised by the client library?

Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ. I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
3
votes
2answers
3k views

Stored procedure parameters using tsql functions?

Environment sql server 2005 sp3 I have a stored proc that takes an INT as input. I want to CAST a CHAR to an INT during the call to the stored proc. it seems I cannot do that. I get a syntax error ...
42
votes
4answers
12k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
2
votes
2answers
2k views

Sp_helptext Error

I am using sp_helptext to list some stored procs instead of going through the ui. I am having a problem with the naming convention we have chosen to adopt. I have a stored proc nameed [Test.FooBar]. ...
12
votes
4answers
906 views

Creating centralized DBA database

We have 200+ servers and I am thinking about creating a centralized DBA database where it logs backup, errors, space jobs and generate reports on SSRS. I am not sure where to start and I would highly ...
1
vote
0answers
513 views

DBCC CheckDB Consistency error on an empty table [duplicate]

I restored a DB from SQL Server 2000. I ran DBCC CHECKDB on SQL Server 2000, and it did not report any errors. What could be the reason behind a DBCC CHECKDB reporting a consistency error on a empty ...
0
votes
2answers
2k views

Query PostgreSQL 9.0 table on foreign key value?

Noob-type question: Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code. ...
3
votes
2answers
2k views

Sql Server output parameters

Is there a way to use named parameters when getting the output parameters of a stored proc? Currently my knowledge of output parameters is very limited. It looks like I have to use them in order of ...
5
votes
2answers
765 views

SQL Server - RangeX-X and RangeI-N locks

I came to a dead point in a deadlock analyze. According to msdn: RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range. RangeI-N are Insert range, null resource ...
1
vote
1answer
185 views

MySQL queries in SQL-Server?

How can we best use our existing MySQL queries with SQLserver? Alternately, what type of changes do we need when we are converting our database server from MySQL to SQLServer?
0
votes
2answers
712 views

does DBCC ShrinkDatabase do a Reorganize of files?

does DBCC ShrinkDatabase doa Reorganize of files? Basically does it mimic the same process as if i checked Reorganize in the GUI?
2
votes
2answers
449 views

Calling a stored procedure in a stored procedure

Environment sql server 2005 My question is Calling a stored proc within a stored proc within another stored proc. When is this type of development detrimental? In order to promote code reusablity ...
4
votes
2answers
617 views

MAX for each subset

I have a table that looks like this: +---------------------------------------------+ | EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE | +---------------------------------------------+ | 123 | 1 ...
5
votes
3answers
777 views

Deadlock Error from SQL Server 2000 to SQL Server 2008

After migrating data from SQL Server 2000 to SQL Server 2008,when I am doing any a transaction in the table which find out more row on the database,that's gives me error. Transaction (Process ID ...
1
vote
1answer
94 views

Sql DateDiff Question

Select distinct DATEDIFF(ww, GETDATE(), siparis_test) AS Hafta The first day of week is sunday in the above function. How can I change it so that monday is the first day? Thanks in advance.
6
votes
1answer
855 views

What is a residual predicate?

Although I was able to find the definition of predicates online at MSDN: Is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE ...
4
votes
3answers
2k views

Query to normalize table/combine row text

I have a table (call it oldTable) with columns like so: ID (int),Rank (int),TextLineNumber (int),SomeText (varchar) The primarykey is multi-part: ID+Rank+TextLineNumber. I'm trying to ...