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)

1
vote
2answers
8k 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
2k 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
365 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
186 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
626 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
253 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
168 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 ...
5
votes
2answers
946 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
769 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
8k 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 ...
4
votes
1answer
11k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
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
133 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
598 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
669 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
899 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
11k 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
800 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
76 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 ...
6
votes
2answers
9k 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
388 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
906 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
5k 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
519 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
212 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 ...
7
votes
1answer
403 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
68 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
1k 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?
1
vote
1answer
200 views

Change tracking SQL Server 2008

My requirement is to track old rows and modified rows from some particular table and the time when changes are made. Using "Change Tracking" and with help of any system table. Due to some specific ...
1
vote
1answer
203 views

SQL from/where or join?

I was wondering performance wise what would be faster a join or a from and where query. I am trying merge and update query but performance is terrible?
3
votes
2answers
384 views

Using Central Management Server to provide convincing performance statistics

Central Management Server The company I work offers dedicated vmware hosted servers to its Windows clients. Among those Windows clients, there are essentially 60 single instance MSSQL servers ...
3
votes
2answers
90 views

FK on NON Primary Keys

Is there any performance problem / difference if I have one Foreign Key that points to another table Indexed Fields that is NOT the PK ? Something like DECLARE @T1 TABLE ( T1_ID INT ...
1
vote
1answer
692 views

T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!): -------------------------------- shipments ...
8
votes
2answers
4k views

Create View in specified database with dynamic sql?

I am writting a dynamic sql to drop and create view in different database. So I wrote: set @CreateViewStatement = ' USE ['+ @DB +']; CREATE VIEW ...
4
votes
2answers
2k views

Does SQL Server have Date_Format function?

As title, does it? All I found is a list of predefined DateFormat that I can choose from, like this As I remember in MySQL (and PostgeSQL too?) you can defines your Date Format: i.e ...
1
vote
1answer
51 views

Database partition efficiency

Let's assume that you have a table that partitions on CATEGORY and SUBCATEGORY. Also assume that every subcategory can be mapped to a category, i.e. subcategory are proper subsets of categories. ...
3
votes
2answers
2k views

How can I optimize performance on a MySQL ndbcluster?

So, this is what I have: A simple table classes__to_students: |class |student| --------------- Math Alice Math Bob Math Peter Math Anne Music Bob Music Chis Music ...
5
votes
2answers
2k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
1
vote
1answer
2k views

Copy blob from one row to another

I have a corrupt blob in a row. I'd like to replace that with another blob from the same table. How can I do that with sql statements only? (No file system access if at all possible.)
0
votes
1answer
118 views

TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs

I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs. Each office has already imported data ...
1
vote
1answer
154 views

Why isn't the result of an OpenQuery refreshing?

I'm trying to get some data from the Active Directory in SQL. I use the code: SELECT * FROM OpenQuery ( ADSI, 'SELECT objectGUID, objectSID, employeeID, lastLogon, mobile, telephoneNumber, mail, ...
0
votes
1answer
2k views

restoring a table from sql file in postgresql through Windows Command prompt

Im trying to restore an sql file which contains the dumpf a table (with COPY commands) into a table into the database, it would be great if someone could share an example, as the pg_restore doesn't ...
1
vote
4answers
4k views

Attaching MDF without LDF

I only have an .mdf file of my database which is not attaching. I am using the following command: EXEC sp_attach_single_file_db @dbname = 'Test_Data', @physname = N'D:\Test_Data\Test_Data.mdf'; ...
1
vote
2answers
366 views

Can I physically split a SQL Server database?

I seem to remember some time ago reading an article saying I could split a SQL Server database into more than one file, allow me to elaborate. I have a database that has a dozen or so tables that ...
0
votes
1answer
45 views

How can I disallow restore from backup on SQL Server?

I want to disallow restore from backup. How can I do this? One Of my friends said to me "Take a certificate for your database and anyone cant restore from your backup" I searched about ...
0
votes
1answer
1k views

sp_dts_getfolder isn't in my MSDB database, any ideas why?

We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is ...
0
votes
1answer
4k views

Oracle SQL for left outer join to rownum = 1 of another query?

Background I have a query joining many tables together, the chief of which is a workorder table. I also have a table called WOStatusHistory, which stores every status for every workorder. A diagram ...
0
votes
1answer
1k views

Who changed the job schedule on a SQL Server Agent Job (SQL 2000)

Someone (or some unknown process) changed the job schedule on a SQL Server Agent Job on a SQL 2000 database. The original start time was 6:00, but was changed to 4:45; that much I can determine. Is ...
9
votes
2answers
499 views

How many Sproc Parameters is too many?

I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...