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)

18
votes
2answers
11k views

Timeseries: SQL or NoSQL?

I don't care about the general differences between SQL and NoSQL (or their traditional differences). I am currently looking at altering the storage of our internal time series. They all contain ...
8
votes
3answers
949 views

SQLITE: A problem of tags and products

I'm searching for a way to create a query to do the following: Let's consider 3 tables: products: List of products tags: list of tags tag_ties: table used to associate a tag to a product Let's ...
1
vote
2answers
422 views

How do I add a constraint after I've truncated a table?

I am writing a script. The purpose of this script is to Get all foreign key constraints on a database. Truncate a table. Re-Add the foreign key constraints that were removed. I'm having trouble ...
1
vote
1answer
3k views

How to “Select column from ALL” tables in sqlite3 db?

I hope this question it's not so trivial as I couldn't find answer with Google I have sqlite3 database with many tables which have same columns. I would like to list single column from all tables in ...
1
vote
2answers
973 views

how to reduce transaction log size automatically? [duplicate]

Possible Duplicate: Why Does the SQL Server Transaction Log Keep Growing or Run Out of Space? how can i reduce the size of Transaction log programatically and automatically, because no one ...
1
vote
0answers
289 views

unable to run multiple create index scripts in pgadmin query tool [closed]

I have created CREATE INDEX scripts using the indexdef column of pg_indexes in one database and tried to run the script on another database. When each line of the script is executed separately it ...
6
votes
3answers
1k views

Query best possible matches and order them

I'm trying to write a query along these lines: select * from tbl where col1 = 1 and col2 = 2 and col3 = 3 order by ... ; I want first all the results where all 3 WHERE ...
1
vote
1answer
143 views

Use of '@' '.' and 'public operator/keywords in Oracle database?

I got into a weird SQL query-patch which looks for @ . and public keywords. I was wondering what is the purpose of these operators/keywords? I suspect that it might be looking for SQL injection or ...
8
votes
5answers
3k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
10
votes
2answers
931 views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity. One thing the DBA mentioned was I should ...
4
votes
1answer
815 views

Property Owner is not available for Database 'DBNAME'

Property Owner is not available for Database 'DBNAME'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo) I am ...
11
votes
3answers
5k views

Should I use SQL JOIN or IN Clause?

I have a question about best approach. I am not sure which approach is best when data is considered variable in size. Consider the following 3 TABLES: EMPLOYEE EMPLOYEE_ID, EMP_NAME PROJECT ...
3
votes
2answers
1k views

Concurrently update with limit mysql

I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db. After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned. I ...
1
vote
1answer
931 views

Set statistics time showing different elapsed_time than dm_exec_query_stats view

When I exec a SQL statement after setting set statistics time on and running dbcc freeproccache and dbcc dropcleanbuffers I get the following output: DBCC execution completed. If DBCC printed error ...
10
votes
1answer
1k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
2
votes
2answers
2k views

Password mismatch while logging to sql server

Alright, I have a classic asp application and I have a connection string to try to connect to db. MY connection string looks as follows: Provider=SQLOLEDB;Data Source=MYPC\MSSQLSERVER;Initial ...
1
vote
1answer
793 views

Exporting from SQL Server 2005 for import into ER Studio 9.0

I am new to ER Studio and need to create an ERD for a remote system. That system can only be accessed by LogMeIn remote control and by FTP. The remote system is running SQL Server 2005. The local ...
26
votes
3answers
27k views

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...
1
vote
2answers
970 views

Access exporting to CSV is weirdly slow

I have searched but could not find anything similar. If I just run the query, I can get the result pretty quickly (~5 mins); but after I saved the query to export as CSV, the exporting part is ...
2
votes
1answer
6k views

Comparing DISTINCT, GROUP BY and ROW_NUMBER() in SQL Server 2008 with data warehouse workloads

I recently heard the advice: "If you're using DISTINCT, I challenge you to fix your code." However, I'm wondering what considerations are behind this advice, and whether it means that I should ...
1
vote
1answer
329 views

Visually create a join using PHPMyAdmin

I recently started using SQL Server 2008 express and it has a really nice joins interface, whereby you can visually create a join. Is this possible on PHPMyAdmin?
1
vote
1answer
80 views

Design for a Testing system

I want to redesign a part of my system. I got the following objects: Site: id Name Site Version: id idSite Name Task: id idSite Name Test: id idTask Name TestResult: idTest idSiteVersion ...
2
votes
2answers
3k views

min(column) with nulls?

I am new to databases, but I hope you can help me to understand something. Take part of this query: select min(date) as date... Assume that some of the dates are null. What effect would it ...
3
votes
1answer
2k views

Are there disadvantages to using Full-Text Search in SQL Server?

Are there disadvantages to using Full-Text Search in SQL Server? Is there much of an increased CPU or memory load on the server?
5
votes
4answers
241 views

What's the best way to list groups of values that are contained in other groups

For example, starting with a table like this: create table t as select 'A' as x, level as y from dual connect by level<=5 union all select 'B' as x, level+2 as y from dual connect by level<=5 ...
0
votes
0answers
121 views

how do i bulk copy a huge amount of data and keep the same table updated through stored procedure in SQL SERVER [duplicate]

Possible Duplicate: How to move data from big table In my project i need to fetch datas from different tables in a data base and create a new table with the same colmn names for all those ...
1
vote
2answers
1k views

How to troubleshoot SQL “The timeout period elapsed prior to completion of the operation or the server is not responding.”

I'm trying to troubleshoot a timeout issue that occurs on one of our SQL 2005 servers. The database responds normally for about a day until we start to see sql timeout errors. After that all ...
2
votes
3answers
160 views

Ordering by date and other columns

I have a one table Q&A like below: CREATE TABLE `totaalconceptterreinonderhoud`.`acties` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `type` int(11) NOT NULL DEFAULT '3' ...
2
votes
2answers
215 views

SQL aliasing to redirect traffic between databases on the same server

In order to upgrade a database with no client disconnects, I clone an existing database and put it in a readonly mode. Now, I want all user traffic to be redirected from the original database to the ...
1
vote
2answers
170 views

SQL Server 2005 datacube processing in high transaction system

We currently have a highly transactional SQL 2005 database which is growning by around 1.2 million rows weekly. On top of this we have a SSAS datacube which is set to process every two hours for ...
9
votes
1answer
11k views

Performance implications of using OPENQUERY in a view

Please see this question on stackoverflow : I am using an EasySoft ODBC driver to link a SQL Server 2008 R2 Express instance to Interbase and I am having some difficulty with getting metadata ...
2
votes
1answer
1k views

SQL Server Error 18483 - When Setting Up SQL Distribution

I'm trying to synchronize a couple tables in one SQL Server 2000 database into another SQL Server 2005 database using replication. When I am attempting to set up my SQL Server as a distributor, I'm ...
2
votes
2answers
369 views

What does `x` stand for in this query?

select convert(varchar(10), TotalSeconds / 3600) +':'+ convert(varchar(10), TotalSeconds % 3600 / 60) +':'+ convert(varchar(10), TotalSeconds % 60) as Seconds from (select DateDiff (second, ...
1
vote
1answer
321 views

Query optimization: Which is better in my case - CURSORs or a Set-based approach?

First of all, I can describe the schema of the table, but my table itself is around 80GB so I am hoping some expert eyes can look at any obvious optimizations that can be performed. I am carrying out ...
0
votes
1answer
831 views

Using Sync Orchestrator to Syncronize two SQL CE 3.5 Databases

I am trying to set up synchronization between two SQL Compact 3.5 databases using SqlCeSyncProvider. I am able to create a scope for the two databases, which creates a meta table. When I try to run ...
5
votes
1answer
440 views

How can I design a table for quick versioning of textual data?

Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
4
votes
2answers
713 views

How to restore a SQL Server 2008 R2 database

Here's my issue. I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the ...
2
votes
1answer
7k views

Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

I've developed a timesheet recording system, that allows users to record: A Period of time (9:00 to 12:00) What they did during that time (DutyActivity: 1 hour report writing, 2 hours training) ...
4
votes
3answers
8k views

Need to Check if a Table Exists Before Deleting a Record

I have the following SQL query in my Ruby app: sql = "DELETE FROM #{database}.table1 WHERE same_id = #{some_id};" The problem is that in rare circumstances 'table1' might not exist. I need to ...
6
votes
2answers
1k views

Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
2
votes
2answers
253 views

Improving the performance of a long query

I have a query that i have let run for a few hours. I am forced to kill the query because it is taking up 60% of the CPU. Is there anything i can do to improve its performance? select ...
2
votes
1answer
2k views

creating buckets in oracle sql

I saw another post where they showed you how to do this with a case statement and had you define your buckets in advance. how do I make it dynamic? I know about the width_bucket and ntile functions. ...
8
votes
2answers
423 views

Mirroring the Distributor

Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
7
votes
4answers
3k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
0
votes
3answers
196 views

How can I select data and the total to cursors?

I have a table like this: ID | Name ------------ 1 | AA 2 | AA 3 | AA 4 | BB I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another ...
3
votes
3answers
62 views

Listing Non-Record

I have two tables. 1st table: called kimlik id ad --------------- 1 ahmet 2 mehmet 3 ali 2nd table: called siparis id kimlikid tarih ...
13
votes
5answers
67k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
6
votes
5answers
71k views

How to show the column names of a table with a simple sql query?

The case is simple: You have an mysql database where you have only sql query interface and you want to know the database structure with queries. You can list tables with show tables; command but how ...
8
votes
6answers
3k views

How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Possible Duplicate: Passing array parameters to a stored procedure Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read: WHERE ...
3
votes
1answer
365 views

How can I alter the output for the sql desc command?

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a ...