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)

7
votes
2answers
2k views

A good SQL sniffer

Can anyone point me to a good SQL sniffer where I can watch the SQL commands that run against my database in real time? If it's free, that would be great.
5
votes
2answers
1k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
1
vote
1answer
2k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
2
votes
2answers
290 views

pull records that are inactive

I need help with this query. I have records in customer table Main table - M +---------+---------+----------+ | company | Account | Active | +---------+---------+----------+ | A | A123 | ...
1
vote
1answer
79 views

Rename cell based on duplicate compound key in MS Access

I'm using MS Access (I know, I know...), and I'm trying to rename a cell when there are duplicates of mutliple columns. Here's an example: Col_1 Col_2 Col_3 Note A1 B1 C1 A1 ...
1
vote
2answers
305 views

MySQL Cluster Node Connections

I am a brand new user to the MySQL cluster world (literally hours). I was following a tutorial just to get a simple cluster set up (one management node on one machine, a second machine with a SQL node ...
1
vote
2answers
2k views

LEFT JOIN conversion to INNER JOIN, can't change FROM clause

I have 2 tables: BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation. These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date. ...
3
votes
2answers
554 views

SQL Server consuming all my CPU

I already have a question on gis.stackexchange.com assuming it may be geoserver the problem but as I go deeper into problem I found that I have a database bottleneck. My SQL server CPU usage is almost ...
2
votes
1answer
5k views

Compare a date and a timestamp with time zone with now() in the same query?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
3
votes
3answers
468 views

SQL JOIN Syntax in MS SQL

I have been taught in my MSSQL classes, this is how to join two tables select * from FirstTable A JOIN SecondTable B on A.ID= B.ID Now in my professional life, I came across JOIN queries like ...
2
votes
3answers
472 views

Identifying rows which don't match a master row

I'm comparing a bunch of tables from different databases on different servers to a Master record. I need to know which servers, identified by locationID, have the non-matching rows because they might ...
1
vote
2answers
137 views

Get Total in a single line in SQL Server

I have a very simple SQL Query which is getting results in the following way COLUMN A COL-B WINDCHILL 99.998889 WINDCHILL 96.000000 Now how i can modify my query so that I get the ...
3
votes
2answers
271 views

Is index_type ignored on MySQL primary keys in create table?

I have seen a create table whose primary key had an identifier where it should have an index_type, something like that: create table a (foo INTEGER, PRIMARY KEY foo_id (foo)); The thing is that ...
1
vote
1answer
3k views

TSQL Get minute part from time data type

With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to ...
1
vote
1answer
135 views

Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which ...
2
votes
2answers
74 views

Data Backup and Restoring the same if requried in SQL SERVER 2008

In Multi-tenancy , Shared Database Shared Schema. How to take Tenant level backup and restored the tenant data when required? Can any one guide me or point me to the right direction. Thanks!!! NB: ...
3
votes
2answers
110 views

SQL Statement Help… Ignore already existing rows

I have a table with a foreign key constraint and the command below gives me an error because it's trying to set a value that already in the provider table. How do I update this command to ignore ...
21
votes
3answers
4k views

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
5
votes
1answer
354 views

How to store and query products the price of which depends on a complex set of conditions?

I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB. Let the products be packages of cable tv providers: { name: "Virgin TV", price: 20 }, { name: ...
15
votes
5answers
251 views

Enforcing database integrity

Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc? How much of a performance improvement one can expect for not ...
12
votes
2answers
21k views

How to pass in parameters to a SQL Server script called with sqlcmd? [closed]

Is it possible to pass parameters to a SQL Server script? I have a script that creates a database. It is called from a batch file using sqlcmd. Part of that SQL script is as follows: CREATE DATABASE ...
3
votes
2answers
673 views

Select total number of records from common table expression

We are using common table expressions to temporarily save the search results. WITH Results ( ------ ) AS ( select --- ) SELECT * FROM ...
6
votes
1answer
236 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
1
vote
0answers
202 views

Cannot Check if the Access Database Engine is Installed and Configured

All, I want to check if the Access Database Engine is installed and linked to SQL Server. On my development machine (x64-bit) the following queries both show the Access Engine to be avalible as a ...
18
votes
2answers
27k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
4
votes
3answers
2k views

PostgreSQL - Help Optimizing SQL performance - full text search

I have a big table ~15M records called entities. I want to find top 5 entities matching "hockey" in their name. I have a Full text index built for that, which is used : ...
5
votes
1answer
196 views

Oracle: DB *partial* constraint?

I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation. I currently have a table at hand that records ...
4
votes
3answers
949 views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
2
votes
1answer
1k views

Is there any way to get the position of a value in a table returned by a SELECT statement?

I have the following statement: SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id which returns a table like this one for example: id 1 3 4 5 But what I need is to know the position of a ...
3
votes
1answer
153 views

SQL Calculation based on 'similar' rows within table

I'm using SQL Server 2008 R2, building a fact table for SSAS. I can therefore accept solutions to this via either SQL query to SSIS task. Let's say I have a fact table that looks a little like this, ...
2
votes
0answers
237 views

Oracle faster overlap check [closed]

We've a table with two columns beginrange and endrange, which contain hex values. To this table no overlapping ranges should be inputted. We had implemented the check by having a composite functional ...
3
votes
1answer
322 views

See actual results of SQL UPDATE command

Is there a way to get the actual results of SQL UPDATE command (ie the table rows themselves), not just get the number of rows (other than by converting the UPDATE command to a SELECT)? I'm using ...
1
vote
1answer
2k views

pg_restore: [archiver] unsupported version (1.11) in file header postgresql

My server is working on Ubuntu with PostgreSQL 8.4 and the development machine I'm using has Debian Lenny with PostgreSQL 8.3. My machine can't be upgraded to PostgreSQL 8.4 and I need to restore a ...
1
vote
1answer
91 views

SQL Data type for “Reference” fields in table - SQL Server

I am learning SQL Server. I need a bit of advice regarding data type for fields that will be referred to but will not actually themselves require values. I don't know the correct term to use(edit ...
2
votes
2answers
2k views

One query two MySQL servers

I have two MySQL servers on different server computers. Is it possible to execute single query like this: INSERT INTO database1.xxxxx SELECT table1.field1, table2.field1 FROM database2.xxxxx INNER ...
1
vote
0answers
70 views

Tool to list down resultset from SQL Server for bulk data [closed]

I have many records with me for which I need to query the database. How can I write a query that can list down all the resultset at once using my large set of data? The database that I used in SQL ...
8
votes
1answer
860 views

What are the security implications of sp_executesql?

By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql. However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql. As ...
1
vote
0answers
67 views

Drill down report links fall outside cell [closed]

Apologies for the vague title, not sure how to summarise the problem... I'm creating a dashboard report showing server state for a group of SQL servers, the problem is that the links for the drill ...
6
votes
2answers
158 views

Optimization: One time vs Staging

I ran into a situation with two options: Use a single query with a cost of ~0.07 INSERT INTO @vals SELECT ... FROM ... JOIN ... JOIN ... WHERE a.col = 1 OR b.col IS NOT NULL Use two ...
2
votes
2answers
886 views

Trying to optimize query for performance (MySQL)

So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
7
votes
2answers
4k views

SQL Server: query performance (search 2 million rows)

Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much? ...
2
votes
3answers
2k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
1
vote
3answers
114 views

User Rights - Can alter Sp's but not tables

How can i make user permissions so that user can CREATE\ALTER\DELETE Store Procedures, but cannot CREATE\ALTER\DELETE tables in database.
4
votes
4answers
1k views

CTE Running in Infinite Loop

My CTE runs in an infinite loop for a specific customer and I am not able to find out why. Here is the query: ;WITH ClassTree AS (SELECT ID, NAME, Parent_ID FROM ...
2
votes
1answer
881 views

How to create a train schedule in SQLite3?

I am interested in what a SQLite3 database schema would look like for a train timetable. Generally this is presented in table format as the final result. Sample Schedule for M to F STATION A ...
4
votes
1answer
504 views

How to change database owner for login and database created inside stored procedure?

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...
4
votes
1answer
7k views

Unique index corrupted SQL. Select query returns single row but create unique index fails

I am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here. When I run a select query for these key values, ...
0
votes
1answer
2k views

querying multiple values from one column [closed]

we have a table called transaction_details. when i write this query SELECT DESCRIPTION, item, cust_code, pack_description, uos, pack, total_value, total_value_vat, total_value_goods, price_indicator ...
1
vote
0answers
55 views

Link-table(s) vs Redundant Columns? [duplicate]

Possible Duplicate: Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server I have two tables, Users and People, both of which share a common ...
-7
votes
3answers
231 views

Business Intelligence = Technical OR Business Project? [closed]

Do you consider a project of Business Intelligence to be a technical or business project?