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
136 views

SQL Server: reset all database tables

I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to ...
3
votes
1answer
109 views

Dissecting Joins

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
1
vote
2answers
267 views

Select Into a table on server1 from server2 without using 'Linked Servers'

I am trying to get all the records from one table into another table but these tables (which are identical) are on separate servers. Any help would be appreciated. Thanks
4
votes
1answer
356 views

Does the order of fields in SELECT query matter when using composite indexing?

I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index? For example, if I ...
2
votes
4answers
10k views

Get counts grouped by month and year

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year. SELECT YEAR(crdate) AS y, MONTH(crdate) AS m, COUNT(*) AS tally FROM ...
0
votes
1answer
82 views

SQL Server 2008 Mirroring

How would one start the secondary mirror instance if the principal SQL server instance went off line?
4
votes
1answer
202 views

What is the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this?

I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following ...
1
vote
2answers
394 views

Query Performance Tuning on a huge table

I have the following table Transactions. The table looks like this: CREATE TABLE [dbo].[Transactions]( [TransactionID] [bigint] IDENTITY(1,10) NOT NULL, [PlayerID] [bigint] NOT NULL, ...
1
vote
2answers
112 views

How to Perform Two Simple Queries using Select Value from First Query

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select ...
2
votes
2answers
64 views

2 databases in sql server 2005 “Production Server”

Can 2 databases decrease the performance in a sql 2005 production server? Server specs: Dell Power edge 2950, 2 Quad core Xeon, 16 GB of RAM, 3x73 GB sata hard drive hot plug, windows server 2003 R2 ...
0
votes
1answer
467 views

inconsistent Data Type -ORA-00932

I know there is question related to inconsistent datatypes but that comes from a query. while I am getting this error on database side when we try to access user defined datatype on our schema that ...
3
votes
1answer
699 views

Index Key Column Order

I have created a joining table for many-to-many relationship. The table only has 2 cols in it, ticketid and groupid typical data would be groupid ticketid 20 56 20 87 20 ...
1
vote
1answer
655 views

Possible to query all SQL Server Indexes which have page locks enabled?

Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?
-1
votes
1answer
82 views

Select query from two tables which only specified rows [closed]

I want to try querying two tables, like this: Table A user_id primary_indicator ------- ----------------- 9012445 Y 9012445 N 9012442 N 9012332 Y 9012332 N 9012445 9012332 Table B ...
1
vote
2answers
375 views

Count events on “user based” sliding window

So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be ...
13
votes
3answers
360 views

What is the correct result for this query?

I came across this puzzle in the comments here CREATE TABLE r (b INT); SELECT 1 FROM r HAVING 1=1; SQL Server and PostgreSQL return 1 row. MySQL and Oracle return zero rows. Which is correct? ...
1
vote
2answers
4k views

Getting SQL Server query history

I have used below query to get the history queries on ran SQL Server but it's not giving me the whole history it just returns queries ran on nearly about last 1/2 an hour. I want to get the queries ...
6
votes
4answers
1k views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...
4
votes
2answers
13k views

Primary filegroup is full SQL Server 2008

I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error: Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' ...
3
votes
1answer
282 views

How to recover data around corrupted data in table? [closed]

I have a SQL Server database with some corrupted data unrepairable via DBCC CHECKDB in a table. I can get many of the rows before the corruption out - but how do I get the rows after the corruption ...
5
votes
3answers
5k views

What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive? [duplicate]

Possible Duplicate: Files - in the database or not? I've been reading "Programming Entity Framework Code First" ...
1
vote
2answers
368 views

How to link each column in sql?

I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase ...
3
votes
1answer
535 views

Outer Join vs Dummy Rows

I found that in a dimensional layout, it is beneficial to create a dummy row in the dimension to avoid writing LEFT OUTER from the fact table. There are other reason for this aside from perceived ...
0
votes
1answer
7k views

To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them?

I know you cannot drop a table that has active foreign key constraints. So I was wondering is it safe to: ALTER TABLE myTable NOCHECK CONSTRAINT all DROP TABLE myTable Rather than dropping the ...
2
votes
2answers
110 views

Index included columns

After running a fairly hefty query, the execution plan gave me a missing index suggestion which was of the form: (Timestamp) INCLUDE (CustomerID, EventID, ID, EmployeeID) Which seems to be a ...
1
vote
0answers
192 views

Calculate lower and upper quartile of SQLiteDB via query

I want to calculate the upper and the lower quartile of the value what I call "diffs" in these fiddle with my DB and the query How can I do this with SQLite? There's an extension on the ...
4
votes
3answers
639 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
0
votes
2answers
18k views

Using a Select in a Where clause for another Select

I have made a draft remote application on the top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functionning of the application. For each final business result that I ...
1
vote
1answer
451 views

Set based solution to group multiple rows within a sequence

Using SQL Server 2008 and a set based solution, how could I manipulate the following data: GroupID Code StartSeq EndSeq StartDayNo EndDayNo 1755549 3506 0 0 59442 ...
3
votes
1answer
2k views

sql error 1241 operand should contain 1 column

This is my query select * from players where sport='football' and position='DEF' and pname!='Binoy Dalal' and pname not in (select ...
10
votes
2answers
6k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
5
votes
1answer
2k views

Oracle extract slow function call from a WHERE clause

I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this: CREATE TABLE MY_TABLE ( SOMETHING NUMBER(15,1) NOT NULL, ...
0
votes
2answers
757 views

Database design of Ledger Account and Balance sheet generation

I am building an application which has deposit and loan accounts management, for which i have to develop ledger accounts and then generate balance sheet. Being new to database design, i am having a ...
3
votes
2answers
3k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
6k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
1
vote
1answer
210 views

Query for localization app

Here are my tables: mysql> SELECT * FROM locale; +-----------+---------+ | locale_id | value | +-----------+---------+ | 1 | English | | 2 | Deutch | | 3 | French | ...
4
votes
1answer
113 views

How to get the information of the optimizer of sql server?

The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql ...
0
votes
0answers
89 views

Which mysql server 5.5 variables would be involved in queries locking in sending data state?

A large portion of my queries (~58%) be it for queries with previously quick return rates or other, are spending in my monitor as sending data. I made some configuration changes but it was very broad ...
2
votes
0answers
618 views

Best book on query optimization, rewriting, refactoring [closed]

I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient. But in a more general sense, I need a book that explains ...
2
votes
1answer
713 views

SQL Server Error 833 On LDF File

I'm getting error 833 on my ldf file and i'm getting it a lot. Please see the following. When this happens, my application is hang already. I know error 833 has been reported a lot, but what does it ...
0
votes
0answers
481 views

RAND in T-sql function

Can any one tell why t-sql not allowing using RAND function in side a function SELECT CAST (RAND() * 10000000 as varchar(20)) the error is Invalid use of a side-effecting operator 'rand' ...
1
vote
1answer
3k views

SQL Query Killed, remains in Kill/Rollback with estimated time increasing

I killed a transaction and now it remains in Killed/Rollback status and whenever I run Kill 64 with statusonly I get this output: SPID 64: transaction rollback in progress. Estimated rollback ...
3
votes
3answers
674 views

How do I go about creating this SQL query?

I'm attending the free DB course at Stanford Online, and frankly one of the exercises gave me some trouble. I have a feeling that this should be horribly simple, so for a DBA, I'm obviously not very ...
1
vote
1answer
81 views

What is a proper way to handle a “sequence number” like field?

The fields of a trimmed-down version of a database I'm working with looks like this: Model Number, Model Name, Customer, Date, SeqNum The customer can purchase the exact same item on the same date ...
3
votes
2answers
104 views

SQL server 2000 transactions with the same time

I have a BIG problem here. I'm creating a program that can be looping if the connection to the server is broken. But when the program is running for a few months, I found a bug on SQL server 2000. I ...
2
votes
2answers
279 views

INSERT succeeds but all inserted values become NULL

I tired to execute an insert directly from phpMyAdmin as follows: INSERT INTO oracle.PLAYLIST_MUSIC ( TID, ID, STATUS, CREATED_BY, ...
4
votes
1answer
251 views

Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
1
vote
2answers
1k views

Factoring out subselect (with clause) ends in ORA-00904

I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again. This is my base query which works quite well: select tka.id, tka.name, txt.text as langname, ...
0
votes
0answers
3k views

0x84BB0001 Error when installing SQL Server 2012. Could not create a handshake [closed]

I have hopelessly attempted to install SQL server 2012 onto my 64 bit Windows 8 machine. The database engine always fails to install with error 0x84BB0001. Native reporting also fails, I reckon from ...
3
votes
2answers
5k views

query_cache_type = 0 vs query_cache_type = 1

Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf. However, I can see that, by default, the generated settings for my.cnf use query_cache_type = 0. ...