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)

3
votes
2answers
1k views

SQL Server 2008 Query Saved in Logs?

Every time I create a query and run it to create a report I save the query in my Projects folder in case I have to rerun the query in the future or if I have to modify it for any reason but it seems ...
2
votes
3answers
175 views

Setting up database structure for Students, Managers, and Administrators

I'm trying to figure out how to set up the structure for users. Users can have multiple Managers and Managers can manage multiple Business Units. Administrators have full control of the system. I'm ...
2
votes
1answer
553 views

Help with a complicated MySQL Query inserting data using select from 2 tables

I am stuck with a case and unable to get the desired result from my query.. I have to insert into a table some records based on multiple records from 2 different tables.. Tables: Target: ...
5
votes
3answers
792 views

How to transfer files at remote location using sql server job?

I have a job configured in which it transfers the file at remote location. My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on ...
4
votes
3answers
814 views

Restore a SQL Server database to an offline copy

I have SQL Server Management Studio. I need to access a backup file (*.bak). How do I create a new database engine that is OFFLINE (running from my PC not the server) and then restore the database ...
4
votes
2answers
309 views

What is the primary key on a temporal table?

If I use the following table design to capture history...: CREATE TABLE MyTable ( insertion_timestamp TIMESTAMP, deleted_flag BOOLEAN, natural_key INT, attribute VARCHAR ); ... then ...
8
votes
5answers
204 views

What is the best way to find out how many records do not have a proper phone number in SQL Server?

I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them. I've been looking around in SSIS ...
4
votes
2answers
176 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
0
votes
1answer
144 views

Updating a table based on condition

I have a database having a staging_table. From this table, entries are added to other table named Profile. Whenever staging_table is updated, it checks whether all its entries are present in Profile, ...
15
votes
2answers
1k views

T-SQL query using completely different plan depending on number of rows I'm updating

I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
8
votes
3answers
2k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...
5
votes
2answers
300 views

Create function in central database or repeat in each database?

One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
1
vote
1answer
44 views

What is this type of query called, and how can I formulate it?

I have following tables: events(event_id, name, description) groups(group_id, event_id, title, description) event_group(event_id, group_id) users(user_id, name, email) ...
4
votes
1answer
1k views

Using FLOATs with RAISERROR

I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but ...
3
votes
1answer
253 views

All-or-none exclusive lock on 2 SQL tables

My simplistic testing suggested that I can acquire an exclusive lock on 2 tables or postpone acquiring the lock on either until both can be acquired at once by using a query like this: SELECT ...
4
votes
1answer
1k views

Oracle subquery column aliasing syntax?

I have something like: SELECT a, b INTO v_a, v_b FROM ( SELECT x, y FROM whatever UNION SELECT z, w FROM something ) AS subquery (a, b) Which of course doesn't work in Oracle ...
-1
votes
1answer
791 views

What exactly is SOLR? [closed]

It seems like a magical technology that can Do spatial search do full text search The problem is, It doesn't say where does it store all the data. Does it have it's own proprietary database If it ...
0
votes
2answers
99 views

SQL and Multiple Indexes

Consider the following table: i | a | b | c -------------- 0 | 1 | 2 | 3 1 | 4 | 5 | 6 2 | 7 | 8 | 9 3 | x | y | z The table, in the end, will have several thousand rows, and even more columns then ...
4
votes
1answer
167 views

Parameter sniffing work arounds

I have used two methods to get around parameter sniffing issues in the past: 1) Use WITH RECOMPILE 2) Reassign parameter values to local variables and use those instead of the parameters From what ...
2
votes
1answer
757 views

Can I Rebuild indexes in a DB except for one table?

I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This ...
0
votes
1answer
1k views

Access 2007 sql editor

Is there an ACCESS 2007 JET SQL editor or client (not sure the right term is) that can be used to code Access Jet SQL queries? It is very difficult to write SQL queries in ACCESS 2007 since it's like ...
2
votes
3answers
1k views

SQL Server dates - can I get date and hour?

In SQL Server, can I do the equivalent of to_char(date_field, 'YYYY-MM-DD:HH24') in Oracle to get 2012-01-30:23?
0
votes
1answer
695 views

Select DISTINCT records with extra information

I have a MS Access database full of customer names and addresses. I want to send out a mailing but only one per address. Therefore if there are 3 different customers at the address I want only one ...
0
votes
2answers
1k views

Is it possible to join derived tables to actual in Access 2007

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below: SELECT * FROM --MyOtherTable is a "real" table MyOtherTable ...
8
votes
3answers
4k views

When should indexes be dropped and recreated?

We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month. For certain tables we are doing daily ETL processes and others we are doing weekly/monthly. ...
2
votes
0answers
447 views

SQL Developer Data Modeller and Data Dictionary question

I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please? I can connect SQL Developer to Oracle XE's data dictionary without any problem, ...
2
votes
2answers
124 views

Database Files-Related Issue

I am using MySQL database and using Ubuntu linux machine. I noticed that my database, db_test, is under the path /var/lib/mysql/db_test There are files with the suffix .frm, .MYD, .MYI like the ...
0
votes
2answers
3k views

How to manage a huge database?

We are building a service witch require a huge database. We are actually planning on getting more than 100 millions rows in our biggest table. So, what are the best options to store all this data in ...
4
votes
3answers
2k views

NULLs in a composite primary key - SQL Server

I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
1
vote
1answer
1k views

Really slow DISTINCT ON query with multiple joins

Originally posted: http://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres The songs table has only about 4k rows, posts and stations have ...
-4
votes
1answer
3k views

primary key, foreign key relationship between the department and employee tables How do I install? (sql_developer) [closed]

EMPLOYEES TABLE COLUMN NAMES: EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_LASTNAME JOB EMPLOYEE_BUSINESS_PHONE EMPLOYEE_GSM SALARY HIRE_DATE E_MAIL DEPARTMAN_NO NOTE:FOR THIS TABLE: EMPLOYEE_ID IS PRIMARY ...
8
votes
5answers
2k views

Why is the SELECT clause listed first? [closed]

I'm not sure if this question is on-topic - if not, feel free to close. Also, if it'd be a better question for another StackExchange site, I can ask it elsewhere. I was wondering why SQL places the ...
5
votes
2answers
912 views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...
1
vote
1answer
519 views

How to avoid duplicating information in a series of nested SELECT statements?

I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per ...
1
vote
1answer
421 views

Return a percentage of the full record set

I'm pulling back a list of orders but I need to only display a percentage of the records. for example we want the top 30 or top 50. The percentagbe is variable so I'd like to not use TOP. I tried ...
0
votes
1answer
56 views

2 Very similar queries return different number of result (one is null)

I want to find businesses near a point that contain a word that start with sea lat=-6.185068&long=106.773121&keyword=sea&distance=5000&startfrom=0&howmany=20&prominent=5 ...
3
votes
1answer
114 views

Complex MySQL Query returning almost all the correct rows

I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work ...
-2
votes
1answer
2k views

Why does MATCH (FullTextSearch) AGAINST ('*' IN BOOLEAN MODE) return nothing?

MATCH (FullTextSearch) AGAINST ('k*' IN BOOLEAN MODE) return something Namely anything with a word that start with k MATCH (FullTextSearch) AGAINST ('ku*' IN BOOLEAN MODE) return less MATCH ...
5
votes
2answers
510 views

SQL to select random mix of rows fairly

Here's my problem: I have a set of tables in a database populated with data from a client that contains product information. In addition to the basic product information, there is also information ...
-1
votes
1answer
78 views

Index Proposals

As a theoretical database question, if you are asked 'which indexes would you propose and why?' are the answers from primary, clustering and secondary index? How would we know if we should introduce ...
0
votes
2answers
123 views

Very slow request with small difference

I use sqlite3 on a Mac Mini. I'm a Java and Web programmer, with some SQL experience, not as much as an expert. I have three requests. First and second are fast (0.2s), and the third is very slow ...
0
votes
1answer
278 views

MySQL Partition based on month-year

Is there a way that I can create a month-year partition on the below table? Without having to define all the permuations of month/year combos ? CREATE TABLE visitor_visits ( id int(10) UNSIGNED ...
3
votes
1answer
265 views

Microsoft SQL Server 2008 R2 Developer Edition Data Tuning Advisor tool

I have Microsoft SQL Server 2008 R2 Developer Edition installed on my machine which has Win7 64 bit OS. I am not able see Data Tuning Advisor tool in my SSMS. Before this installation my machine ...
1
vote
2answers
225 views

SQL Trigger to verify account, if not verified dont process stored procedure/transactions

Is there a way to create a trigger or function to check a user against the database and if not verfied, the following stored procedure is not ran. I would like this option for verification check ...
1
vote
2answers
1k views

Unable to connect SQL Server 2008 from SQL Server 2005 DB

I have SQL Server 2005 on my work station and on my Server, we have SQL Server 2008. When I tried to connect to SQL Server 2008 from my SQL Server 2005 edition, I am receiving the following error: ...
5
votes
1answer
538 views

Release SQL Server Memory usage

How can release memory usage of sql server by command. my system have 16GB RAM, but when get huge query by sql server, memory usage become full. if I restart SQL Server service memory usage become ...
3
votes
3answers
552 views

This query is choking the server. How can I improve it?

Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof! The MySQL Processes list shows that as the number of requests ...
3
votes
1answer
1k views

Script to Restore/Create SQL Server 2000 Database from Another Database's Backup

I'm trying to create a database, NewClientDB, by restoring another database's, OldClientDB, backup, d:...oldclientdb.bak. I've always done this via SQL 2000 Enterprise Manager, but the GUI keeps ...
2
votes
2answers
521 views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for ...
3
votes
1answer
2k views

Create view in a different database with dynamic SQL

As part of a larger project I'm trying to create a new view on each database in a SQL Server instance. I've created a stored procedure that among other things is looping through the databases in ...