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)

3
votes
2answers
975 views

How to load data into Vertica from C?

I'm investigating column oriented databases and came across Vertica. My need is to feed the Vertica database from C code. I don't succeed in grabbing this information from Vertica: I'm told to use ...
0
votes
1answer
68 views

How to select rate for all selected modules and all selected years no matter they got rate for that year or not

How to write a SQL to achieve the "goal" section? Select rate for all selected modules and selected years no matter they got rate for that year or not. The years and modules will be input by users, ...
13
votes
3answers
6k views

Does “WHERE 1=1” usually have an impact on query performance?

I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language). ...
1
vote
1answer
2k views

Creating an index in a temporary tablespace

I am trying to create an index for a relatively large table (80M rows). First I got an ORA-01652 error, then I searched in SO and found a related question. However, trying to implement the steps ...
3
votes
2answers
32k views

How to include a datetime parameter within a stored procedure along with string query?

My stored procedure is as follows, -- Add the parameters for the stored procedure here @FromDate datetime, @ToDate datetime --Select query DECLARE @query nvarchar(max) ...
1
vote
4answers
4k views

Can't create user in SQL Server

Hi guys when I try to create user for my database I always encounter this kind of error. This is the sequence of task I done. I manage to create new login using windows authentication of SQL then ...
2
votes
1answer
414 views

Return Output from SQL Function using command line SQLCMD

I have a SQL Function that returns an Integer as the Output. I need to execute it via sqlcmd and I only want to get it's integer output. However right now I'm getting back this type of output: ...
1
vote
1answer
3k views

High PageIOLatch_SH Waits with High Drive Idle times

We are experiencing high volume of PageIOLatch_SH waits on our database (row counts in the Billions). However it seems that our drive Idle time Percentage hovers around 50-60 percent. CPU usage is ...
1
vote
4answers
1k views

How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?

I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query. Simply i can use ...
6
votes
5answers
487 views

Are these good indexing guidelines?

I use indexes regularly but it's still hard for me in certain conditions to know if they are helping or hurting. There are a few guidelines I follow, but I am not sure if they are good nor am I sure ...
5
votes
2answers
7k views

How to know if I am running SQL Server 2008 R2?

I ran @@version. This is what it gave me: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on ...
11
votes
3answers
6k views

Best way to design tournament database

I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase. I have created a mockup ...
11
votes
2answers
55k views

Oracle: Quick way to list all database links

The title says it all, is there a way to quickly list all the current database links in oracle? Something along the same lines as this, which lists the current user's tables: select * from ...
6
votes
2answers
2k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
5
votes
4answers
4k views

SQL Rebuild Index, Recovery Model, and db log filesize?

I have a database that we currently run transaction log backups throughout the day, every 30 minutes to be exact, and we run a full backup each day at 2am. Every Saturday at 3am, we have a job setup ...
5
votes
2answers
4k views

Write a slow query to test slow query logging?

Is there a simple query that would take > 2 sec so that I can test the slow query logger? I am looking for something like a generic recursive or iterative statement.
2
votes
2answers
4k views

Restore Database script

I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and ...
2
votes
0answers
178 views

Stored procedures over SQL in the code, good idea? [duplicate]

Possible Duplicate: What are the arguments against or for putting application logic in the database layer? Assuming I got over the problem of maintainability and debugging. My assumption is ...
1
vote
1answer
2k views

Help optimizing MySQL slow query

i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort" query: EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
0
votes
4answers
1k views

Execute Stored Procedure that cannot be manually stopped

I am looking for a solution to: Allow an admin to manually run a stored procedure Once the stored procedure runs the same admin cannot stop it using the "Cancel Executing Query" The stored procedure ...
3
votes
2answers
586 views

Transaction Log 16GB on 2MB database [duplicate]

Possible Duplicate: Why is My SQL Server Transaction Log Growing or Running out of Space? The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. ...
2
votes
1answer
934 views

Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

I have a view which selects information from INFORMATION_SCHEMA.COLUMNS, which seems to give me both columns and views. Is there an easy way to tell which rows are columns and which are views? Should ...
19
votes
2answers
12k 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
1k 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
425 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
1k 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
304 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 ...
11
votes
2answers
992 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
881 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
968 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
2k 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
3k 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
828 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
29k 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
1k 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
355 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
81 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
162 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' ...