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)

2
votes
1answer
71 views

Using scope_identity() to get ID “during” insertion of row?

I have a column that has an ident_current constraint on it to populate another column, like so: alter table dbo.Employee add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for ...
2
votes
2answers
37 views

PostgreSQL using count() to determine percentages (cast issues)

I'm trying to run the following query to provide the % of records in my patients table that have a value entered in the refinst field. I keep getting a result of 0. select (count (refinst) / (select ...
1
vote
1answer
64 views

What is a good/healthy mysql query execution time?

What is a good/healthy mysql query execution time? To my calculations: The site gets 1000 unique visitors per hr 10 page view for each unique visitor 5 minutes for each unique visitor ...
4
votes
3answers
120 views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
7
votes
5answers
514 views

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the ...
0
votes
1answer
42 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I ...
0
votes
1answer
31 views

RAID drives or replicate data?

I have an old Microsoft SQL server that, for financial reasons, I cannot replace until later in the year. The OS, DB and temp files are on one 10k RAID 5 array (I didn't design it) The server is ...
0
votes
0answers
38 views

msdb 'backupset' log history

One of our production server‘s backup job is getting failed for one DB because of Low disk space when I looked into ‘physical_device_name’ column in ‘msdb.dbo.backupset’ to get the path of backups I ...
1
vote
1answer
41 views

Get data per Customer and per Date

How will I get the all the date even though there is no order between two dates? Sample Table OrderID | Date | CusID 33942 | 6-21-2014 | 6005 34059 | 6-20-2014 | 4003 53333 | ...
1
vote
3answers
80 views

Block user access to certain tables

DBMS allows the admin to grant table access to a user using something like: GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost'; However, is it possible to grant all tables to a user and ...
1
vote
0answers
38 views

Is it recommended to have redundant foreign key columns?

Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs: Without redundance A | id | ... | ------------ | ... | ... | B | id | a_id | ... | ...
0
votes
0answers
16 views

Checking for records in lookup table before deleting

In my MySQL database, I have products table, a locations table and a lookup table (called location_products) where the ID of the product and ID of the location are stored. I am creating a transaction ...
-2
votes
1answer
43 views

how to select column3 from rec1 value which is matched to column1 val in rec2

i am having following scenario : col1 col2 col3 10 2 13 13 4 14 14 5 15 15 6 16 form the above given table col3 value(13) in row1 is seen in col1 value(13) in ...
0
votes
2answers
49 views

How to print ORDER wise using LEFT JOIN in SQL?

Below SQL query is not displaying ORDER wise. How can I fix it? SELECT TabTitle,A.ButtonName,A.CheckBoxName,B.SubCheckboxName FROM (Select * from PersonalTrainerCheckboxData where ...
2
votes
1answer
23 views

remove a paritition table

I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2. The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you ...
0
votes
1answer
49 views

Changing Databases Dynamically using Variables [closed]

I want change into a database programatically using in code: DECLARE @SQL nvarchar(2000) DECLARE @DBName sysname SET @DBName= 'foo' SET @SQL = 'USE [' + @DBName + ']' select @SQL execute @SQL ...
0
votes
1answer
53 views

ORA-00907: missing right parenthesis on query with subqueries

I am getting ORA-00907: missing right parenthesis for the following query: SELECT PEOPLE.NAME FROM CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L WHERE NOT EXISTS ( (SELECT DISTINCT ARTIST.ID_U ...
0
votes
0answers
39 views

How to aggregate similar columns inside a very long table? [duplicate]

I have quite a difficult problem that I don't have any idea how to approach it. I'll be glad to receive some advice. I have a very long table which contains details about a specific house residents. ...
0
votes
2answers
44 views

“GROUP BY” in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | ...
0
votes
2answers
41 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
0answers
14 views

Specific id to appear first in result of query [duplicate]

I have a sample query where: Select gID, entID, cID, gString From tblGF Where ((entID=$entID And cID=0) Or (entID=$entID And cID=$cID)) And (gFP='BB' Or gFP='CP' Or gFP='PS' Or gID=$gID) Order ...
1
vote
2answers
42 views

Time elapsed Calculation

I need to calculated time elapsed for each user to perform his part in a transaction. The sequence is: 0 -1 - B2 or 0-B3 The table looks like the following: ...
0
votes
0answers
17 views

Would an INNER JOIN query in a sharded relational database hit the same shard when using a compound primary key?

In this question I have asked about modulus calculation in sharded tables, this design is used: CREATE TABLE Orders (order_id INT PRIMARY KEY, ...); CREATE TABLE LineItems (order_id INT, lineitem_id ...
0
votes
0answers
10 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
0
votes
0answers
21 views

Indexed column INSERT vs. UPDATE

I have an indexed column. I could give it a value on INSERT. But it would be easier if I would add this value via UPDATE later (I have to update this row anyway). But I was thinking if it would be ...
0
votes
1answer
64 views

Need help optimizing query to prevent full table scans

I have the following query, which while it works, is also doing a full scan on each left joined table (the log* tables). I thought by using a subquery in the FROM that this would eliminate the ...
0
votes
1answer
38 views

COUNT and GROUP BY performance

I have a table where I store visits (id, datetime, country, browser). I want to count how many visits are from which country. I use this query: SELECT country, COUNT(*) as hits FROM hits_table ...
0
votes
1answer
50 views

Join 3 tables returns too many results [closed]

I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table). The problem is that when I try to run inner join query and return all posts and ...
2
votes
1answer
61 views

Error Creating Foreign Key in Oracle

I have 2 tables: PRODUCTS PREFIX SUFFIX ATTR1 ATTR2 ----------------------------------------------------- 001 AAA 50 100 001 BBB 20 300 010 ...
0
votes
1answer
60 views

UPDATE not working properly with linked server

Please see a question I asked here earlier: http://stackoverflow.com/questions/24324823/update-not-working-properly-with-linked-server Dommer suggested using OPENQUERY and this does appear to resolve ...
-3
votes
2answers
97 views

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
0
votes
1answer
42 views

SQL query for all combinations without repetition

I need a function for calculating combinations without repetition from a list. Example: I have a list of names e.g. Tom Bob Paul Tina Now I need a list of 3 names out of the 4 without having a ...
0
votes
0answers
20 views

Low performance oci_execute

I work with a PHP application that connects to an Oracle database. Sometimes, the PHP executes a large query from a large database the page loads for ages (more than 200s). But I've seen something ...
1
vote
2answers
48 views

Making a Database Online [closed]

I am so confused with this issue, I have vb.net application and it has a sql database . Now, I want this application to access the sql server from anywhere , because there are many clients sharing ...
1
vote
0answers
54 views

Database Mail not sending

I'm trying to send email using the DatabaseMail on SQL Server 2008, I can see my emails sitting in the msdb.dbo.sysmail_unsentitems But they just sit there, I get no error messages. I've checked ...
1
vote
1answer
36 views

Turning of Autogrowth for particular file

We have a primary file group with several files each on different disks. One of the disks is nearing capacity and as such we have had to switch off the auto growth for that file. Is there anything I ...
1
vote
1answer
30 views

What is the generally accepted method and tools for profiling MySQL queries?

Profiling database queries is difficult, and dependent on database technology. In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query? For example: Run the ...
0
votes
0answers
11 views

Creation of a table with foreign keys in postgres is taking longer time [duplicate]

I am wondering if someone can help me understand this. I tried to add an column to a table of 22000 rows which is referencing other tables(one of these tables have 0.5 million records). It didn't ...
1
vote
1answer
61 views

Way to run SQLCMD without storing password in text format

Is there a way to run sqlcmd from a batch file where I don't want to store the password in plain text format. I have done some research and found the system variables method(store password in system ...
0
votes
1answer
39 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | ...
0
votes
1answer
26 views

selecting rows that have n foreign key conditions satisfied

I'm trying to filter a master table rows based on 2-more conditions from the relative foreign key relation, i came up with this which is working fine, but i just can't convince my self that it's that ...
1
vote
0answers
43 views

Stored procedure taking much longer than query [closed]

I have a simple code segment that runs in about 1 second when run alone, but more than 5 minutes when deployed as a stored procedure. I'm using sql server 2008. Unfortunately, I can't post the code ...
1
vote
2answers
46 views

How to get the first or last row in ordered result set, depending by column value?

I have the follwoing result set from the table PUNCHREPORT EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE .................................................................. 70 ...
1
vote
1answer
54 views

Running queries directly on DB2 databases

Just have a general query, if it is really fine or safe to run long select queries on RDBMS production Databases specifically DB2? If NO, what are the major risks and issues in executing select ...
3
votes
0answers
35 views

Using Import Wizard with Access - No null values

I am trying to import a table from Access to SSMS using the Import Wizard but when I do the resulting table has a lot of empty strings instead of the NULL values that I am expecting. How do I make ...
0
votes
0answers
9 views

Datastructure to save relations between more than two entities and lists

I am trying to building an Information Extraction system(IE). I wrote a program that will detect entities and extract relations between them. Now I am saving the relations in the following SQLITE ...
0
votes
1answer
27 views

two auto incrementing columns

OK I have hit an issue, now I was going to look at having two auto incrementing columns, but I cant using InnoDB. So I am after a Solution. I have a table called queue. Everything to do with my task ...
0
votes
1answer
33 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
3
votes
1answer
101 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
1
vote
2answers
44 views

Oracle patching

Hi everyone I'm a student and I need a help with Oracle patch's. My DB 10.2.0.5.0 I don't understand how to check which of CPU and PSU patch's were installed. When I try to use sql select * from ...