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)

17
votes
5answers
5k views

SQL: What is slowing down INSERTs if not CPU or IO?

We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower ...
4
votes
2answers
620 views

How to sync a database with an external full text engine?

How do you keep your external full-text engine (Lucene, Solr, etc) in sync with your database data? For example, keeping track of inserts, deletes, updates. My current solution involves a big union ...
3
votes
2answers
268 views

SQL Data aggregation

I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database. select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct me.id) ...
3
votes
1answer
154 views

update with nested join problem

First of all newbie here and to the world of Oracle, just trying to get some code working: UPDATE CUSTOMERS SET RE.LASTNAME = 'NO REFERRAL' WHERE RE.LASTNAME IS NULL (SELECT CU.CUSTOMER#, ...
3
votes
1answer
2k views

How do I update a table with the aggregted values (min, max) of a related table, fast?

I have a table create table w ( id integer primary key, -- min_xyz double, max_xyz double, min_abc double, max_abc double ); with the min* and max* values currently being null. I have ...
18
votes
1answer
5k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
1
vote
2answers
162 views

Execution Plans Query

I want to check the estimated number of rows of a query in MS SQL Server. I can right click on the query and select "display estimated execution plan". but I want to do the exact thing with a hard ...
4
votes
2answers
6k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
2
votes
4answers
631 views

Should I use a Column naming convention or rely on column descriptions?

Frankly I am stunned that after some basic (very basic) internet searches I have not been able to find a comprehensive naming convention or guideline or SQL object and column naming. The parallel ...
3
votes
2answers
4k views

How to monitor SQL Server data and log file growth?

I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
1
vote
1answer
1k views

When to open your MySQL cursor

A relatively simple question but from all my research I have been unable to find a decent answer. Which is technically more correct, open your cursor after your handlers have been defined or before ...
6
votes
1answer
2k views

SQL 2008 R2 - Odd timeout issues

I'm having some recent timeout issues in our SQL 2008 R2 server. This started occurring late last week and has since somewhat improved. Issues: When using SSMS and right clicking over a ...
3
votes
2answers
2k views

How to check when statistics was last executed?

We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
2
votes
1answer
548 views

How to serialize a record / row in SQL?

Is there a way to serialize a record/row in SQL? And conversely, to deserialize something that has been serialized. By serialize, I mean put all of the fields in a record into one field or string. ...
-1
votes
3answers
163 views

SQL Server - Transaction Slowness

Using: SQL Server 2008 R2 I'm seeing some behavior right now in SQL Server, and I can't decide if the server is underperforming or not. I've set up a test to insert an integer into a table, as ...
4
votes
1answer
127 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
2
votes
3answers
2k views

How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()?

How do I use sys.dm_exec_connections in combination with SUSER_SNAME()? I want to see the username corresponding to each record in sys.dm_exec_connections.
2
votes
3answers
92 views

transaction log after restore

Can you let me know why a transaction log would be a lot smaller in size after restoring from a full backup. Currently we have a massive log file on the live server, but after a restore to a test / ...
2
votes
1answer
1k views

SQL Server Maintenance Plan Fails to Rebuild the Indexes

When I run a maintenance plan to reorganize and rebuild tables indexes, it fails. Below the last two rows that displays the fail: Source: Reorganize Index Executing query "ALTER INDEX ...
2
votes
1answer
874 views

Access SQL Server from a different subnet

I'm creating a program which accesses MS SQL Server 2008, the problem is that I can run the program smoothly as an administrator account, even on different subnet computers and using terminal service ...
2
votes
1answer
1k views

Tracing/Logging of Sybase ASA

Trying to get logging/tracing of Sybase 9.0.2.3396 (Adaptive Server Anywhere). I wish to log all the SQL executed against the database. I tried RIBO and while I got it "working," I never saw anything ...
1
vote
3answers
235 views

What is this error about in sql server?

Trying to restore database, getting a dialog in SQL Server Management Studio You cannot select backup sets from different databases" Any idea why I am getting this error? BTW backup is from ...
1
vote
2answers
6k views

ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

I got that error when I tried to execute this query: select * from lc_history2 lc where lc.view_name <> 'LC Main' and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc ...
2
votes
1answer
1k views

Database restoring taking too long

I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible. ...
4
votes
2answers
323 views

Getting to know SQL via the “short questions/simple answers style of learning”

Looking for a incremental SQL course that would likely be similar to the this Ruby Koan course; which is in fact inspired by the book, "The Little Lisper", which uses a short questions/simple answers ...
0
votes
1answer
174 views

I want to be databse specialist (SQL) [closed]

Hello there database specialists, I have Bachelor degree and one part of study was database, where I learned about database design, SQL language... and I'm working with mysql, but I decided to be ...
0
votes
1answer
536 views

SET keyword in SQL Server

I have been using SET keyword to assign the values to parameters declared in SQL Server something like, Declare @StudentName Nvarchar(max) SET @StudentName='ABC' I'm not sure how to write this if ...
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 ...
2
votes
2answers
167 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
1
vote
1answer
111 views

SQL Server function invocation syntax question

The following excerpt is extract from this page. CURRENT_USER (Transact-SQL) Returns the name of the current user. This function is equivalent to USER_NAME(). When I execute the two ...
4
votes
2answers
886 views

Why did Microsoft kill “English Query” on SQL Server?

Given the popularity of the natural language user interface Siri, which was acquired by Apple for $200 million, I've wondered if anyone knows why Microsoft killed "English Query" on SQL Server.
-3
votes
1answer
690 views

Best practices for finding and updating a handful of records?

Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".
7
votes
3answers
7k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
2
votes
2answers
266 views

SQL Server 2008R2 Ent + Win2003 Ent R2 32bit will not use all available memory

server -HP ProLiant DL380 G5 2 proc -2x QuadCore Intel Xeon E5450, 3000 MHz Memory- 12288 Мб (DDR2-667 Fully Buffered ECC DDR2 SDRAM) Set: /3GB in the boot.ini. /PAE in the boot.ini. Enabled AWE ...
2
votes
3answers
131 views

Document versioning

I want to store in SQL Server 2008 XML files, but in a way that versioning is supported. For example, I have a XML document and I save it in the DB(in the XML datatype column). After that, I edit it. ...
4
votes
2answers
491 views

Specify type on SQL Server Computed Column

I have just added a computed column to a table, and it is showing up as an int and I'd like to make it a bit is this possible? I'm having a hard time finding any info on this. Below is the computed ...
5
votes
2answers
591 views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
2
votes
2answers
836 views

Postgres Index scan forward vs backward = speed difference of 357X slower

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit Dedicated DB server 4GB ram Shared_Buffers = 1 GB Effective_cache_size = 3GB Work_mem = ...
4
votes
3answers
9k views

Login to SQL Server using Windows Authentication

I have an (.Net Console App) EXE which connects to various SQL databases using SQL server Authentication with appropriate credentials. Now there is change required in the EXE to use windows ...
-1
votes
2answers
714 views

Multiple child tables

I would like to know what is the best solution for making a parent table with multiple children? I have to make multiple children table. One for each profile. Is there a better idea than having 3 ...
0
votes
1answer
70 views

Redundant / partially duplicated data in a DB - how to structure?

I'm building a website for which a large amount of user generated data will be stored in a back end db and will be discoverable by all users. By its nature, a portion of the user entries will be ...
5
votes
2answers
8k views

How do I find an object that doesn't seem to exist in the objects table?

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
2
votes
3answers
382 views

Express union which is dependent on other results

I would like to make a union of two tables that are actually in a hierarchical constellation. How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship. ...
1
vote
2answers
797 views

How to add ansi null and ansi warning in connection string

Using RDO Connection Connection String Set RdoVisPay = rdoVISENV.OpenConnection("VISA", 1, False, "DSN=VISA;UID=sa;PWD=sa;APP=Visual Basic;WSID=" & ServerName & ";DATABASE=" & ...
7
votes
6answers
4k views

Maximum value from multiple tables

Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this ...
2
votes
4answers
494 views

What computations can NOT be performed in standard SQL?

I know loops cannot be expressed in standard SQL. What other forms of processing can it not perform?
1
vote
1answer
197 views

Okcupid.com (can find matches without accessing a database!?) [closed]

I'm doing a research project on a popular dating site called: OkCupid I would like to talk about how the database is used. After reading this explanation from the Co-Founder of the site, I became very ...
5
votes
1answer
353 views

Adding SPARSE made table much bigger

I have a generic log table, about 5m rows. There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of ...
0
votes
1answer
67 views

Replicating Results of Queries on Oracle

I wan to setup a database such that its data come from queries run on a remote database. Basically the source database is very slow and we have no control over it. So, I want to create a second ...
2
votes
1answer
965 views

Oracle SQL count occurances of character in VARCHAR

Is is possible to count the number of occurrences of a character in a VARCHAR in oracle? Something that would allow me to determine '11001110' has 5 ones?