An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

2
votes
2answers
832 views

Reference a non primary key in another table in postgresql

I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.
2
votes
2answers
265 views

Advanced search engine or server for relational database

In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities. In the Project ...
1
vote
1answer
571 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 ...
2
votes
2answers
171 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
1answer
45 views

building a view w/ fields that only contain a certain word

Trying to build a simple SQL query... I have a table filled with "Yes" and "No" I want a view that only shows me records that contain "No" SELECT PhysicalBuild.Name AS Name, PhysicalBuild.Labels ...
2
votes
2answers
2k views

WITH using INDEX hint in Oracle

I have a fairly complicated query. I have written two different query to accomplish my goal. First one is using WITH, other one is using global temporary tables. First One: WITH A ( KNO .. ) , ...
1
vote
2answers
2k 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 | ...
3
votes
1answer
926 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
117 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
443 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 ...
3
votes
2answers
2k 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 ...
5
votes
2answers
510 views

How do I search to see if a MySQL user exists on the system?

I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I ...
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
379 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 ...
1
vote
1answer
3k 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 ...
1
vote
1answer
1k 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 ...
0
votes
1answer
66 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, ...
10
votes
3answers
4k 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). ...
4
votes
2answers
5k 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 ...
2
votes
6answers
800 views

Optimize this SQL query for performance

I'm trying to optimize the following statement: 'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR) +'/00000' +CAST(@number+1 AS VARCHAR) The statement produces a value ...
2
votes
2answers
16k 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) ...
2
votes
1answer
314 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: ...
0
votes
1answer
2k 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 ...
10
votes
3answers
3k 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 ...
8
votes
3answers
1k views

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find ...
5
votes
4answers
3k 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 ...
3
votes
2answers
2k 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
2k 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 ...
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 ...
0
votes
4answers
725 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
2k views

PostgreSQL: Using schema and table names from other tables?

Consider the situation where I have all my schema names in one table and all my table names in another table. Is it possible to do something like in the following? SELECT value FROM (SELECT ...
1
vote
1answer
1k 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.* ...
3
votes
2answers
460 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
514 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 ...
1
vote
2answers
394 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
2k 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 ...
10
votes
3answers
4k 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 ...
1
vote
2answers
647 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 ...
4
votes
1answer
636 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 ...
3
votes
2answers
937 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 ...
4
votes
5answers
551 views

EXISTS clause in SQL

I have following requirement: main_set table has all the ids, user_input table has all user entered ids. I want result of ids that exist in user_input table. If nothing in user_input table then I ...
6
votes
1answer
3k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
3
votes
1answer
1k 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?
4
votes
4answers
226 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 ...
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 ...
1
vote
2answers
747 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
4k 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 ...
2
votes
1answer
944 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
2k 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 ...