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)

6
votes
2answers
6k views

Allowing Foreign Key to be Null

I'm trying to create a relationship between two tables where one column in TABLE A is the foreign key for a column in TABLE B. However, there is one row in TABLE B that is currently null in that ...
6
votes
1answer
117 views

What happens behind the scenes when adding a nullable column in SQL Server?

This question came up today. When you add a column that accepts null values to the end of a table what exactly happens? Does the table get fully rebuilt similar to what happens when you change column ...
6
votes
1answer
2k views

SQL Server transaction log file refuses to grow

Problem: I have a SQL Server database sitting on a 1 TB drive. The log will not grow past 5 MB even when the MAXSIZE is set much larger than 5 MB. Error received: The transaction log for database ...
0
votes
1answer
50 views

Mark exported rows as exported

I need to write an export module which marks exported rows as exported. We currently run SQL Server Express 2012, but may switch to Oracle, so I need a client-side approach. We are currently using ...
2
votes
1answer
46 views

In real terms, how much effect does a bad fillfactor have on performance?

There's a storm brewing, or maybe I should say firestorm. A customer has recently implemented SQL Server 2012 Always On but that's not the most important bit. A list of fires to be put out has been ...
2
votes
2answers
745 views

Closed loops in database model, does it affect the performance?

I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the ...
2
votes
3answers
5k views

Managing SQL Server MDF and LDF file size

I am new to SQL Server and don't fully understand its architecture. I've looked around, Googled, asked around but found no simple (understandable) answer to the problem. I'm not even sure if it is ...
1
vote
4answers
4k views

Select from linked server to local server causing waits on both sides

The queries in question are here: http://pastie.org/4833351 Run individually, each of these queries runs through (albeit slow). Run as a group, it just sits there, showing either OLEDB waits on the ...
2
votes
1answer
2k views

How do I completely remove MySQL from a 64-bit windows 7 machine?

I'm new to MySQL and struggling to find a version and workbench which is stable on my 64 bit Windows 7 machine. I've decided to attempt to completely remove MySQL from my machine and to restart the ...
2
votes
2answers
1k views

Running a CTE query in a loop using PL/pgSQL

I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
1
vote
0answers
131 views

Use of hash functions in group by?

I'd use a listagg() to generate a list of components a certain customer has and compare it to another list generated the same way somewhere else to detect inconsistencies. The problem is that ...
1
vote
2answers
111 views

How can I improve this query

I have the following table: CREATE TABLE `twitter_relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `source_twitter_id` bigint(20) NOT NULL, ...
2
votes
4answers
157 views

SELECT Statements only please

I have a user that wants to use SQL Server Management Studio (SSMS) to run SELECT statements. I don't want him to be able to modify data. I can't regulate his permissions through Windows ...
1
vote
3answers
130 views

Query missing one date; only happened today

This should be pretty reproducible I hope. I'm on Oracle 11g. This query, which I use as part of another query, is acting strangely: SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM ...
2
votes
0answers
481 views

SQLite Performance Tuning

I have the following query, which fetches data from only one table. It is a query which is supposed to return data for an autocomplete function. Autocomplete data can be in text1 or text2. The ...
1
vote
1answer
51 views

Query from database

I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product. It is one to many relationship from ...
0
votes
0answers
221 views

How do I get the date that each user last logged onto the server / database in SQL Server 2005

Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the LASTLOGIN_DATE. ALTER ...
3
votes
2answers
1k views

Unable to disable SQL statement logging in PostgreSQL 9.1

I am running PostgreSQL 9.1 on Windows Server 2008 R2. I enabled SQL statement logging to the stderr log by modifying my postgresql.conf file, but now I can't seem to disable the logging without ...
2
votes
1answer
453 views

Oracle sequence generation performance during persists of large data sets

In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000? That is to say does ...
1
vote
1answer
1k views

Convert spatial points data to line data for use in Line Layer in SQL Server Reporting Services (SSRS)

I have a query that returns the fields Order, Lat, Long, SpatialData, <OtherStuff>. SpatialData is a point. I want to create a line layer that draws lines between my points based on the order ...
1
vote
1answer
335 views

The best way to log access to SSRS reports

We have a number of SSRS reports created using BIDS 2008 on SQL Server 2008 r2. The business wants us to log access to these reports. For example who and when run the Report A. What is the best way ...
1
vote
1answer
148 views

Does MySQL compress the stored data?

When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB. Are they compressed in order to reduce disk space usage? Is that 80MB real physical-storage size?
2
votes
2answers
3k views

Parallel inserts cause deadlock on DB2

The scenario is the following: The application attempts to insert into the same table from two parallel threads making 4000 insertions in each thread (separate transaction per thread). It causes the ...
2
votes
1answer
91 views

What are the effects of GRANT DELETE?

What is the difference between: GRANT DELETE ON TABLE1 TO ROLE1 and GRANT DELETE TO ROLE1 One of our DBAs executed the second version by mistake. I want to be certain I understand all the ...
1
vote
1answer
456 views

Can I install sql server on Win XP machine with user profile on D drive?

Is it possible to install sql server on a windows xp machine where the user profiles are created on a non system root directory, that is, on D drive rather than C drive? I am trying to install SQL ...
0
votes
3answers
2k views

How to attach .mdf file to SQL Server 2005 without an .ldf file?

I want to attach a database to SQL Server 2005 but I have only its .mdf file How can I attach it?
3
votes
2answers
789 views

Deprecating SQL Server, need to redirect clients to new server

I am in the process of deprecating an old SQL Server server and replace it with a new server. My old SQL Server is a named instance called: SQL\Development My new SQL Server is a default instance ...
1
vote
2answers
3k views

Move postgresql database from one server to another

I have created a (spatial) database at my home which is several gigabytes and I want to move the whole database as it is from one computer my works computer. Nothing fansy, I will create a new ...
2
votes
1answer
986 views

SQL Query - Set Date Variable by Table/Column Reference

How do I set the @DATE variable by using a table/column reference? Right now I've set it manually as you can see below with the opening: SET @DATE = '2012-03-31 01:00:00'; The table from which I ...
-1
votes
2answers
203 views

Information sources for multiple hierachy trees in a single table

I need some quality sources - books, websites etc. - to educate myself about putting multiple hierarchy trees in a single table using SQL. I'm looking for some good theoretical and practical ...
6
votes
3answers
5k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
3
votes
3answers
4k views

Alternative query to this (avoid DISTINCT)

Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines I have this table "Users": UserID User CountryID 1 user 1 1 2 user 2 2 3 user 3 3 4 user ...
1
vote
1answer
1k views

Production SQL Server High CPU for a Single Query

I have a single query which is consuming a larger amount of CPU than I would expect in SQL 2008. I am not able to determine why the CPU is spiking for this single query. I can trigger an ...
3
votes
1answer
114 views

Slow MySQL query, not sure why

I'm working with an intranet application for property tax collection, and I've hit a snag. The following view runs much slower than I'd expect: create or replace view propertytaxessub0 as select ...
2
votes
1answer
152 views

Is there a name for a query that returns results which have sub headings?

I inhered a set of queries / macros from the previous employee in my position and one of the other people here showed me a query result (output from when the query worked correctly) which had ...
2
votes
0answers
583 views

SQL fulltext accent insensitive

I am using SQL Server fulltext containstable and the column which is indexed has collation Czech_CS_AS, so why when I have word "byt" it returns records with word "být" too. I thought that the ...
1
vote
2answers
4k views

Sql Server agent can not start

I can not start Sql Server agent to create job. Can anyone help me? . I'm using Windows 7 Ultimate , Sql server 2008 Rc2 See this images:
4
votes
2answers
332 views

Database design for part-number generator

Crossposted to Stack Overflow: Database design for part-number generator I'm working on a generator for part numbers, where each space will have a meaning and each space will have a number of ...
1
vote
2answers
7k views

How to Export SQL Server Database To A File For Later Import To Different Computer

I'm using the Sql Server 2012 Import and Export data (64) i'm not sure what options i should be picking to do what i want. I just want to be able to export all the data in the database to a file which ...
2
votes
1answer
778 views

Find which numbers in [1, 161] are not in the result set?

How to find which two number in [1, 161] is not in the result set? Can I find this two numbers using sql command? mysql> select blog_id from wp_blogs; ... | 149 | | 150 | | 151 | | ...
1
vote
1answer
3k views

execute() vs executeQuery() in MySQL Connector C++

With MySQL Connector C++, I can use the sql::Statement::execute() function if I am going to modify the database (INSERT, UPDATE, DELETE, MERGE), or the sql::Statement::executeQuery() function if I am ...
2
votes
1answer
588 views

Oracle With Statement and Delete Together

I am trying to use WITH statement and DELETE together. But I get ORA-00928 error. Basically my code is like following WITH TO_DELETE AS ( SELECT TableKey FROM SOME_TABLE WHERE SomeConditions LIKE ...
1
vote
3answers
626 views

Restoring an entire SQL Server database instance

I have a server that we will be completely reformatting then rebuilding. Everything will be rebuilt exactly as it is now - same filenames, same directories, etc. After SQL Server is installed, I ...
2
votes
1answer
2k views

change encoding of database from latin1_swedish_ci to utf8

I have an huge database in latin1_swedish_ci. Now i need to convert all data to utf8 collation. My question is about the consistency of the information. When I do this change it is possible corrupt ...
1
vote
3answers
557 views

Compare two tables amount with respect to priortization of FundType

I have two tables one is PaymentDetail and another is Ledger Here i want to prioritize paymodetype in manner that cash should be considered first for R,A and then I FundType, then Credit Card, ...
1
vote
1answer
418 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
3
votes
2answers
283 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
4
votes
3answers
39k views

How to select distinct for one column and any in another column?

I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column. For example, consider the following table with two columns, key and value: ...
1
vote
2answers
267 views

User-defined sort order for a query

I'm storing user preferences for sort order in a table: $queryHistory = "SELECT orderby,ascdesc FROM user WHERE UserID= ".$id; $resultHistory = doQuery($connection, $queryHistory); $numHistory = ...
1
vote
1answer
233 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);