Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

1
vote
1answer
132 views

SQL Server Query performance with window function

We write a query that include unpivot, partition by and order by.Query is: SELECT PersonId ,SalaryDate ,ID ,Type ,SalaryValue ,ROW_NUMBER() OVER ( PARTITION BY PersonId ...
1
vote
2answers
3k views

How to split one big table that has 100 million data to multiple tables? [closed]

I have one database in SQL Server 2012. In it there is one table that has more than 100 million rows. I want to split that table to multiple similar table with 1 million rows in each table. What is ...
0
votes
1answer
472 views

Mysql pagination without offset

Let's assume I have got a table with these data: I want to order it by createdAt column DESC, and id column DESC select id, createdAt from post order by createdAt desc, id desc Now it looks like ...
1
vote
2answers
608 views

TSQL substitute for MAX() string ranking

I have a table with repeating values (col1) and I need to return col2 based on a ranking system. In my example the ranking system is handled by MAX() giving me the highest value. I then return the ...
0
votes
0answers
62 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
0
votes
2answers
62 views

SQL Performance: COUNT or extra attribute?

If I want to count the number of a multivalued attribute, which way is faster: a) using COUNT(attribute) on a whole table or b) adding an extra attribute which counts these stored values (to be ...
6
votes
3answers
247 views

Most cost efficient way to page through a poorly ordered table?

I have a table that has three columns: HashUID1, HashUID2, Address_Name (which is a textual email address, and the previous two hash colunms are of some crazy creation to link event participant tables ...
0
votes
1answer
59 views

How to design a query that gives count of different parameters based on different condition from a single table

I have a table books containing columns as bookid, issue_date,return_date. Once a book is issued from library an issue_date corresponding to it is made into the table while return_date can have valid ...
0
votes
1answer
84 views

SQL: return values as column table

I am running this query to get all returns for a list of tickers: select return1 from STOCK_DATA where TDATE between 20130101 and 20131231 and TICKER in (SELECT DISTINCT TICKER FROM STOCK_DATA) ...
0
votes
1answer
60 views

How can I Use Access to combine tables of information

I have two tables in access with a common ID number in both. One table has mail to info and the other table has account information. The account information can have multiple listings per ID. The mail ...
1
vote
3answers
144 views

Clustered Index improvemens for particular queries

I found a table that looks like this: CREATE TABLE [dbo].Table1 ( id INT primary key IDENTITY (1, 1), [idUser] INT NOT NULL , [Amount] INT NOT NULL , [Attempts] INT NOT NULL , ...
4
votes
4answers
852 views

Keep track of time intensive stored procedure with prints

I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = ...
0
votes
0answers
40 views

Thoughts on further optimizations

So I have this application. It runs on a server. There is a portion of the code that is taking around 10-15 seconds to run, it contains some queries, logic, as well as some code to update the ...
0
votes
3answers
282 views

Get latest status of an item in the database

I have this table: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | OK 2 | 123 | ... | FAILED ... I want to query get all items which last status was FAILED. ...
1
vote
1answer
477 views

MAX (id) function with MySQli

How can I use the following statement to return only the record with the maximum PersonID from the table? /* Create the prepared statement */ if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS ...
0
votes
0answers
487 views

SQL Server : error 15208 Level 16, State 1, Line 1

Trying to Backup a certificate from SQL Server causes this error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format. ...
2
votes
1answer
140 views

How to get all logged in users to SQL Server database for this week?

I'm using SQL Server 2008R2 and I setup a testing database that I need to know who tried logging in so far. Is there a stored procedure or a View or anything that I can gather these type of ...
1
vote
1answer
152 views

How can I design this database?

So I have a ton of transaction logs in pipe-separated format spread across a few dozen files. Concatenated into one file, it's around 2.4 million lines. Each transaction has a store number and a ...
0
votes
1answer
307 views

MySQL Loop Through Each Row

I have two tables, division & template, in a MySQL database. division - DivisionID - DivisionName template - TemplateID - TemplateName Here is an example of my current data ...
2
votes
2answers
448 views

SQL Backup tuning large databases

I am tuning SQL backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have SQL instance with a 4 TB database and several smaller databases ...
1
vote
1answer
541 views

Select statement for selecting every week's log record

Here is a simplified version of the table in question: +------------------------------------------------------+ | DatabaseName | LogRecordNumber | LogTimestamp | ...
1
vote
2answers
354 views

Are there any special characters that are never allowed in SQL?

I am a Java programmer. We always make database-related things configurable so that they can be easily modified by the DB team as per their requirements. I want to make some queries as configurable, ...
0
votes
0answers
63 views

Paging via Number of Result Record vs COUNT(*)

I have been working on fixing a piece of code for a customer that has been very slow for them. I have made that fix, and thats all lovely, but I noticed something odd in the paging. When the query ...
5
votes
2answers
499 views

How to store immutable, unique, ordered, lists?

I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists ...
1
vote
1answer
4k views

Store the resulting list of a SELECT in a variable to count AND use the result

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the ...
2
votes
1answer
33k views

Case with multiple conditions

I need to change returned value, from select statement, based on several conditions. I tried something like that: ,CASE i.DocValue WHEN 'F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal ...
1
vote
1answer
280 views

SQLAnywhere: Visualize in real time CPU and RAM consumption of sql connections

I want to keep track of which connection use most of resources. Within Sybase Central (SQL Anywhere 9, 12), for each server and database, we have pane to check in real time All Connected Users and ...
2
votes
4answers
111 views

Trigger or Stored Procedure?

Let's say I have two tables, foo and bar: CREATE TABLE foo( foo_id serial NOT NULL, count integer NULL, PRIMARY KEY(foo_id) ); CREATE TABLE bar( bar_id serial NOT ...
3
votes
1answer
389 views

SQL Azure Performance of new servers

I have a fairly small database ~200MB which when running in the web edition of SQL azure is lightning fast. I've switched it to the basic edition and all of a sudden a group of queries which took less ...
3
votes
1answer
135 views

Why postgres is using an odd index with no relation to the query

(Debian 7, Postgres 9.3, dedicated machine with huge cache) I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two ...
0
votes
1answer
64 views

Design: Fitting variable hierarchy of text files into a relational db

I have a large data corpus (several TB) made up of data gathered through customer support queries. I've been asked to set up a database to make this data more accessible, but as I'm kind of new to ...
1
vote
2answers
205 views

How to deal with very large transaction logs in SQL Server [duplicate]

Here is my situation: I have being asked to deal with a SQL Server 2008 R2 database that has a transaction log that is several times bigger than the database itself. The database is in full recovery ...
1
vote
1answer
82 views

Getting Time Duration

I have a table like below: I want to get time duration for each record which SPEED is greater than 100. I written a function for that, it’s working fine , but my problem was i have millions of ...
1
vote
1answer
88 views

Adding a “position” number in Mysql

I got the following query and result currently: mysql> SELECT songname,COUNT(*) as count FROM etrstats WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH) GROUP BY ...
5
votes
2answers
10k views

What are some common and useful fields for a users table in a database?

I want to begin implementing user functionality on my website, but I'm not sure exactly what kind of information I should be storing. Obviously the basic things like username, password, password hash, ...
-1
votes
3answers
619 views

How to load multiple values in cell from Excel to SQL Server many to many table [closed]

I have values in excel like this. StudentNumber| Subjects 1| english, science, maths 2| maths, biology 3| history, zoology I have Student table in database ...
1
vote
1answer
5k views

SQL Server 2012 how to create new schema from existing database selecting only subset of database objects?

I'm looking for a way to create new schema from existing database selecting only subset of database objects. I.e. I want to have some "database representation" which only includes subset of tables ...
0
votes
1answer
780 views

Insert record only if a combination of column values doesn't exist

I have a simple MYSQL insert query: INSERT INTO table('col1','col2','col3') VALUES('123','456','some text') I want to insert only if the above combination of columns does not already exist and this ...
1
vote
2answers
54 views

Multidimensional aggregates

Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is ...
0
votes
1answer
40 views

multi-column FKs with NULL values

I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the ...
1
vote
1answer
74 views

Matching a value with every element of a set of values - Postgresql

I'm new in Postgres and I can't achieve what I'm trying to do. I have a list of films in a Films table ID_FILM FILM NAME 1 Terminator 2 Forrest Gump 3 Nueve reinas A ...
2
votes
2answers
297 views

Call UPSERT function multiple times to different inputs

I have a function: merge_vehicles(vid, cid, vname, reg_no, name, name_1st) Can i call it multiple times on inputs like (2335, 55, '246BDH', '246BDH', '811', 1), (2336, 55, '038THX', '038THX', ...
0
votes
1answer
38 views

Select Ingredients of a product based on unit type

I have three tables UnitType,NutritionMaster, NutritionInProduct UnitTypeID UnitName 1 100ml contains 2 250ml contains NutritionID Name Description 1 Energy NULL 2 ...
0
votes
1answer
320 views

moving binary database folder is causing issues with innodb tables

so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables ...
-3
votes
1answer
159 views

Best design to handle multiple query keys [closed]

I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games. The data I want to save has the ...
2
votes
2answers
9k views

Problem sending an attachment using dbo.sp_send_dbmail

This question has been asked on the main stackexchange site I was referred here by a user there. ...
0
votes
1answer
31 views

MySQL - Arrange a table so that it shrinks as much as possible?

I'm a newbie and I have +10 million articles(200 to 1000 words) in a InnoDB table. I use this to select them: SELECT article,title,other_fields from table where id=123; There is no full-text search ...
0
votes
1answer
38 views

MYSQL improve query performance when using OR

I have a very simple MYSQL table with 2 columns and I run this query: SELECT * FROM table WHERE (col1 = '123' AND col2 = '456') OR (col1 = '456' AND col2 = '123') Col1 and ...
1
vote
2answers
32 views

MySQL improve query syntax

I have this simple MySQL query: SELECT * FROM table1 INNER JOIN table2 ON table2.col = table1.id WHERE table1.id = '123' id is the primary key in table1. table2 has composite primary key of two ...
0
votes
1answer
2k views

Oracle Nested Query with Group By and Having Clauses

So here's the question Find the IDs of aircraft which have flown more distance for a destination than the average distance of all flights for that same destination Here is the schema ...