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)

5
votes
3answers
1k views

Querying special characters

I work with language with special characters (example: e -> é). I have a query with a LIKE but when I search a word with special char and my query doesn't have the special char, I don't get the ...
1
vote
2answers
3k views

Print database_size from sp_spaceused

Using SQL Server 2008, I have found that the database size can be obtained using exec sp_spaceused This procedure returns two tables: database_name | database_size | unallocated space reserved | ...
0
votes
1answer
476 views

Coalesce Function Returning Duplicates

I have a many-to-many relationship setup between three tables. CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations I would like to retrieve all rows from the CompanyNotices table for ...
3
votes
1answer
2k views

How to get super parent from table with self relationship

i need to create Function that will return the super parent of a given id from a table the table look like table1 (id, parentId, someData) the parentId is related to the id for example i have 1 - 2 ...
8
votes
4answers
130 views

What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs?

In a lot of relational database designs there are fields that are referenced in other tables. For example consider a user table with a unique username and a second table storing address data. One ...
4
votes
1answer
2k views

Calculate months between two dates

For a University course I've been given the following task Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every ...
3
votes
2answers
3k views

How to optimize this dynamic stored procedure

I have a stored procedure which is retrieving data using 20 tables. Sample of the procedure: CREATE PROCEDURE GetEnquiries ( @EnquiryDate DATETIME = NULL ) AS ...
4
votes
1answer
574 views

Kill CLR stored procedure process

I have a CLR stored procedure which exports a large table into a text file. The procedure works fine but. When I manually stop its execution from SQL Management Studio I cannot delete the file it ...
1
vote
1answer
5k views

Select a single row from two distinct columns [duplicate]

Possible Duplicate: How to get the MAX row I need to fetch the "latest" row for certain conditions in my tkhist1 table. Tickets are bound to TicketNo and LocationID, and I need to select ...
3
votes
2answers
998 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
7k 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
36k 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
5k 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
438 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
507 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 ...
12
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
61k 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
179 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
608 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. ...
3
votes
1answer
1k 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 ...
22
votes
2answers
13k 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
431 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
313 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
2k 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
1k 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
956 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
2k 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
1k 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: ... ...
4
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
861 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 ...
31
votes
3answers
31k 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 ...