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
3answers
560 views

Database growing like crazy - what do I do?

I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ...
2
votes
1answer
136 views

Can I use a Clob as a table key (from Java)?

Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)? I know it is probably a ...
5
votes
1answer
337 views

Replication of database used by two programs

I have a peer to peer transactional replication set between two identical databases, each one of the databases is being used by a different program on two different servers. Since we implemented the ...
1
vote
1answer
480 views

oracle - does this have the potential to cause a lock?

Note how no commit was done in between each statement. Could this cause a table lock or blocked session? delete from tableblah; insert into tableblah (select * from othertable);
1
vote
1answer
972 views

creating a table from a selection and then using group by - oracle sql

I'm working in oracle and I can't figure out what's wrong with this statement. The error it gives me is must name this expression with a column alias CREATE TABLE shipping_costs_grouped AS ...
3
votes
2answers
571 views

Testing Query Speed

When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, ...
2
votes
1answer
3k views

How is INDEX on Composite Primary Key in mysql?

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually? The reason that I am asking this question is that when ...
2
votes
1answer
81 views

Bad Windows Authentication sign on

I do not have install privileges on my machine. Another user who has admin privileges installed it, but told the system to use Windows Authentication for the sign on. Is there a way they can change ...
5
votes
2answers
275 views

How to find out what is causing my Identity column to keep going up

I have a table with an int identity as the PK. Something is causing it to increment, and I'd like to know what that is. To give a quick example, yesterday I inserted a record and got ID 41,773. Today ...
7
votes
2answers
265 views

Do agile software development approaches apply to SQL?

I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any ...
2
votes
1answer
653 views

how do I limit the characters in selected row and con-cat with '…'

I have a query which return a column named comment. I want to limit the number of characters returned by select statement to 50 and if the comment row contain more than 50 characters, I want to append ...
3
votes
3answers
1k views

How to restructure this slow query containing subquery?

This query takes about 2-6 seconds to run depending which tag id(s) is/are selected: SELECT t2.id, t2.name, count(*) as kpl FROM (SELECT pt1.post_id FROM posts_tags pt1 WHERE pt1.tag_id ...
1
vote
0answers
224 views
1
vote
1answer
2k views

strategy for enabling a general query log on MySQL 5.0.95 with no restart?

I would like to configure a general query log for a busy MySQL 5.0.95 production server without effecting the availability. Obviously I can set the "log" option in the my.cnf file and restart the ...
1
vote
2answers
6k views

How to use RESTRICT for Foreign Key in mysql?

In the database structure of CREATE TABLE Country ( name varchar(40) NOT NULL, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE City ( name varchar(40) NOT NULL, ...
1
vote
1answer
68 views

Is it safe to change the name of a database?

I have a SQL Server database which has data for several customers/devices. We are changing the prefix of our website (asp.net) and our application dynamically loads the proper connection string based ...
1
vote
1answer
619 views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
3
votes
2answers
2k views

Using Foreign Key and References for ON DELETE CASCADE in mysql

I was thinking that these two ways for creating foreign keys are the same CREATE TABLE child1 ( id int(11) not null auto_increment, parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE ...
5
votes
3answers
1k views

After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

I have two tables Location Master ([LocatioNo],[LocationName],[Description]) Location Data ([LocationNo],[LocationCode],[DDate],[BillNo],[Model],[Quantity],[SNo]) and a view involving the 2 ...
4
votes
2answers
237 views

Complex SQL query - need help

I have a really hard SQL question! I've inherited a system that allows people to enter dog shows. I have inherited the database with its present architecture, and since there are office systems that ...
2
votes
2answers
554 views

SQL Joins Order of Operations

I'm new to SQL but happen to be dealing with a couple of rather larger table (on the order of a billion rows each). I want to do an inner join like the following: SELECT * FROM XTABLE X INNER ...
1
vote
1answer
617 views

Why is my maintenance plan save overwriting job ownership?

I'm slowly untying all the system dependencies to the previous DBA's system account and moving them to a service account. The old DBA had ownership of all the jobs in our maintenance plan. In SSMS I ...
6
votes
3answers
3k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
2
votes
1answer
254 views

Execute Queries Against CMS within SSRS Reports?

Is it possible to execute queries against a Central Management Server (CMS) using SSRS? For example, I know of no way to tell a stored procedure that it is inherently supposed to call the CMS, so it ...
2
votes
1answer
1k views

Save Transaction: Is the name local to the stored procedure or not?

Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused. There is a subset of stored procedures that preserve state on failure. That is, if the ...
-2
votes
1answer
767 views

Query database by date, month, year

How can I query order by date. Like this: And the same for week, month, year...?
1
vote
2answers
2k views

Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of CREATE TABLE column1 ( first_id int(11) NOT NULL AUTO_INCREMENT, ...
1
vote
2answers
172 views

Is it possible to have indexes for like '%*%' type queries

Now for my thesis project i need to make a lot of queries like the below select * from tblMyTable where ItemName like '%item%' Now my question : is that possible to create indexes for such queries ...
2
votes
1answer
570 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. ...
6
votes
1answer
579 views

Loyalty reward - record for every day of a week

I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week. So, the real question, how do I get every ...
3
votes
2answers
276 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) ...
1
vote
1answer
77 views

Design for a Testing system

I want to redesign a part of my system. I got the following objects: Site: id Name Site Version: id idSite Name Task: id idSite Name Test: id idTask Name TestResult: idTest idSiteVersion ...
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 ...
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 ...
2
votes
4answers
656 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 ...
1
vote
1answer
222 views

Show all variations of 2 characters in postgres

How would I do this in postgres : Lets say I have an x and a y and I want to show all possible combinations : xx yy xy yx How can I make postgres do this for me ?
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.
3
votes
2answers
3k 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 ...
-1
votes
3answers
168 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
130 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 ...
0
votes
1answer
256 views

FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and ...
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 ...
1
vote
3answers
257 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 ...
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
2answers
7k 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. ...
8
votes
3answers
791 views

overstating field size in database design

I have some fields for my tables that are strings and at the moment, most of the field size have pretty high character limits. For instance, 100 char for the street name. Is there a penalty for using ...
1
vote
1answer
294 views

Query optimization: Which is better in my case - CURSORs or a Set-based approach?

First of all, I can describe the schema of the table, but my table itself is around 80GB so I am hoping some expert eyes can look at any obvious optimizations that can be performed. I am carrying out ...
1
vote
3answers
64 views

SQL Server - Index Including PK

Lets assume we have a table as follows: PK -- nonclustered primary key Col1 -- unique clustered Col2 Col3 Is this index: nonclustered index on (Col2, Col3) any different from this index: ...
3
votes
2answers
3k views

datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float

What datatypes could be given to this value --> 45.58 Could these be given: numeric money decimal float What's the difference then? Which one would be the best to use?