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)

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
350 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 ...
45
votes
4answers
16k 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
3k 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
1k 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
881 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
784 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
452 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
732 views

MAX for each subset

I have a table that looks like this: +---------------------------------------------+ | EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE | +---------------------------------------------+ | 123 | 1 ...
1
vote
1answer
100 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
986 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
3k 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 ...
5
votes
6answers
490 views

Running a database without constraints

We're looking at utilizing a new proprietary high performance database engine for OLAP and OLTP scenarios. One important aspect of it right now is that it does not support foreign key or other ...
3
votes
2answers
4k views

datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float

What datatypes could be given to this value --> 45.58 Could these be given: numeric money decimal float What's the difference then? Which one would be the best to use?
2
votes
2answers
506 views

How to get a user's friends' names?

I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow ...
1
vote
3answers
2k views

How to choose the last recorded added without sorting

I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int) I need to run a simple query: SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC ...
1
vote
1answer
896 views

Select statement to retrive xml output as below

Hi could someone provide me some clues or solution to retrive record sets like below note: i read the msdn documentation but leads me no where but hair loss :( Just for the assumption think i have 2 ...
13
votes
7answers
118k views

How do I Create Tablespace in Oracle 11g?

I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used: SQL> create ...
5
votes
1answer
2k views

Print Parameters in Dynamic SQL

I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement. EG: Declare @SQL nvarchar(max), @Params ...
7
votes
4answers
5k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
4
votes
1answer
4k views

How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
12
votes
5answers
4k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
13
votes
1answer
1k views

Sql Anywhere 11: Restoring incremental backup failure

We want to create remote incremental backups after a full backup. This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible ...
3
votes
1answer
945 views

Oracle query against SQL Server database giving inconsistent results

We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
4
votes
1answer
2k views

nest SELECT in INSERT

I'm trying to do something like: INSERT INTO stories (vid, ip, body, timestamp) VALUES ( (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1), INET_ATON('127.0.0.1'), 'test', ...
3
votes
1answer
7k views

How do I calculate values based on the previous row after skipping the first 12 rows?

I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be ...
12
votes
4answers
20k views

Methods of speeding up a huge DELETE FROM <table> with no clauses

Using SQL Server 2005. I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
3
votes
2answers
2k views

Select XML values when attribute involved

I have a table that stores XML values in field: OtherData. An XML value from one row: < file path="C:\WINDOWS\system32\svchost.exe" /> I would like to select all rows that have an ...
6
votes
2answers
3k views

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | text ...
2
votes
2answers
1k views

How can can I get object definitions stripped of all comments?

When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments. Is this possible by a SQL ...
3
votes
2answers
386 views

Searching for a Data Dictionary for SQL or Oracle

I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases. By metadata map, I mean a definitive description of what objects make up database model and their ...
4
votes
2answers
2k views

Updating rows conditionally with a gapless ascending value

A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
14
votes
7answers
3k views

Is polling the only way for updating app's data from a database?

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...
1
vote
2answers
870 views

How to group a resultset by day?

I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day ...
3
votes
3answers
5k views

bcp in of datetime type data results in an “Invalid date / time format”

I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited. I then bcp in to a truncated table ...
0
votes
2answers
2k views

How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?

SQL Server DELETE only marks as DELETED though leaves as data in the data pages. Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record ...
1
vote
1answer
199 views

Multiple Distinct Columns

I have a table named 'DATAENTRY' with the following columns Title FName MName LName Add1 Add2 Add3 City Now I want to select records which have different (FName, MName ,LName) so I tried SELECT ...
2
votes
1answer
851 views

Query between two related tables

I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are ...
14
votes
5answers
2k views

Needing A Database Design Book

I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
4
votes
1answer
13k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
4
votes
1answer
420 views

Help with Query Tuning

EDIT - drachenstern: see this question for more information on this problem and on the source of his query below: Group data by non-unique keys by distinct time range Can someone please advise ...
2
votes
3answers
247 views

multi-table mysql query

I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project. A User is associated with Message/Thread/Project as either the Creator ...
9
votes
5answers
4k views

How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
7
votes
3answers
2k views

Problem with MySQL subquery

Why does this query DELETE FROM test WHERE id=(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); sometimes delete 1 row, sometimes 2 rows and sometimes nothing? If I write it in this ...
15
votes
5answers
1k views

Why do primary keys have names of their own?

From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table ...