Hot answers tagged

44

These two constraints would do: CREATE TABLE dbo.Configuration ( ConfigurationID TINYINT NOT NULL DEFAULT 1, -- the rest of the columns CONSTRAINT Configuration_PK PRIMARY KEY (ConfigurationID), CONSTRAINT Configuration_OnlyOneRow CHECK (ConfigurationID = 1) ) ; You need both the PRIMARY KEY (or a UNIQUE constraint) so no two rows have the ...


20

I'm having trouble imagining anything where the data model could legitimately contain 2000 columns in a properly normalised table. My guess is that you're probably doing some sort of "fill in the blanks" denormalised schema, where you're actually storing all different sorts of data in the one table, and instead of breaking the data out into separate tables ...


19

You could define the ID as a computed column evaluating to a constant value, and declare that column to be unique: CREATE TABLE dbo.Configuration ( ID AS CAST(1 AS tinyint), -- or: AS bit ... -- other columns CONSTRAINT UQ_Configuration_ID UNIQUE (ID) );


11

The latest draft SQL standard that I could find on the internet (dated 21/12/2011) has the following available for use in a query expression: <result offset clause> ::= OFFSET <offset row count> { ROW | ROWS } <fetch first clause> ::= FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }


10

Why would you need to create a table with even 20 columns, let alone 2000 ??? Granted, denormalized data can prevent having to do JOINs to retrieve many columns of data. However, if you have over 10 columns, you should stop and think about what would happen under the hood during data retrieval. If a 2000 column table undergoes SELECT * FROM ... WHERE, you ...


9

MySQL 5.0 Column-Count Limits (emphasis added): There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors. Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on ...


9

You're limiting the resultset of the aggregate function count(), which will always return 1 row. IE: It's limiting the output of the count(*) function, rather than LIMITing just FROM data WHERE datetime < '2015-09-23 00:00:00'. Basically: Postgres reads all the rows FROM data WHERE datetime < '2015-09-23 00:00:00' Postgres then count(*)s them ...


9

You can also use trigger.. create trigger LimitTable on YourTableToLimit after insert as declare @tableCount int select @tableCount = Count(*) from YourTableToLimit if @tableCount > 50 begin rollback end go


8

My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high. Speaking from a SQL Server perspective, it depends. Here is an overview of what it depends on, and why: Row Goals Adding a top-level TOP (n) clause (with or without ORDER BY) has the same row goal effect as if a FAST (n) ...


7

It's a measurement system with 2000 sensors Ignore all the comments shouting about normalization - what you are asking for could be sensible database design (in an ideal world) and perfectly well normalized, it is just very unusual, and as pointed out elsewhere RDBMSs are usually simply not designed for this many columns. Although you are not hitting the ...


5

Use correct ANSI group by (not the MySQL abomination extension) and see what happens select sum(score) total,name,gender,dob,country from users join scores on users.id = scores.user_id where date between '2012-01-01' and '2012-01-31 23:59:59' group by name,gender,dob,country having sum(score)>=1000 order by sum(score) desc limit 50 Why? GROUP BY in ...


4

The size of the database is the size of the file. Look at the actual size of the data file (the transaction log doesn't count). Yes indexes count. If you are running out of space consider an upgrade to SQL 2012 Express as that increases the size limit to 10 Gigs.


4

What is wrong with the (maybe too obvious?): select * from noun n, noun_inflection ni where n.label = 'handlebar' and n.label ilike '%'||ni.label_singular order by char_length(ni.label_singular) DESC limit 1;


4

In SQL Server and other systems that support ROW_NUMBER()... WITH GoalsWithGoalNum ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY [In] ORDER BY [Goal ID] ) AS GoalNum FROM Goals ) SELECT * FROM GoalsWithGoalNum WHERE GoalNum <= 2;


4

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1. I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user': SELECT * FROM user WHERE lastmodifieddate > (SELECT sync_time FROM _sync_history WHERE object_name = ...


4

PostgreSQL thinks it cannot use the index defined on (tank_id, dpg DESC NULLS LAST) to satisfy this query without the sort. If it is just DESC, that is fine. Or if it was just on (tank_id, dpg), that too would work (it would scan the relevant part of the index backwards). If you can't change the definition of the index, then making the query match the ...


3

Ok, it was simple :) I wanted to limit each database to 256MB of space per student. In order to achieve that, I enabled smallfiles in /etc/mongod.conf and ran the mongod process with mongod -f /etc/mongod.conf --quota --quotaFiles 4 which limited the size of the database to 256MB. smallfiles starts preallocating space with 16MB, then 32MB, 64MB and finally ...


3

Here is a good script I shamelessly ripped from here: use [Insert DB Name] select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) , ...


3

First some more flaming, then a real solution... I mostly agree with the flames already thrown at you. I disagree with key-value normalization. Queries end up being horrible; performance even worse. One 'simple' way to avoid the immediate problem (limitation of number of columns) is to 'vertically partition' the data. Have, say, 5 tables with 400 ...


3

I think some of it goes back to requirements. How much of a name are you storing? Do you ever want to keep the "names" separate (like first, middle, last)? Do you want to handle multiple middle names? I'd say 50 characters should be fairly sufficient for each name if you wished to separate them out. (That will be way more than enough for most names, but ...


3

Things to try: Adding an index on (user_id, date, score) Group by only on scores table and then join to users: SELECT s.total, u.name, u.gender, u.dob, u.country FROM users AS u JOIN ( SELECT user_id, SUM(score) AS total FROM scores WHERE date >= '2012-01-01' AND date < '2012-02-01' GROUP BY user_id HAVING SUM(score) >= 1000 ...


3

From the postgres manual (http://www.postgresql.org/docs/current/static/queries-limit.html): If a limit count is given, no more than that many rows will be returned (but possibly less, if the query itself yields less rows). Limit does not constrain how many rows your query will scan - it only affects how many rows will show up in your record set.


3

From the EXPLAINs it seems quite clear what happens - the "limit only" one uses index on latitude because it finds it most useful, then tries all found rows for longitude until it gathers 100 of them and quits. The "order only" uses the same path, but does not stop after 100 because it needs all of them - it then sorts all matching rows by a filesort (I ...


2

Table have 1,000,000 records but It look like Table doesn't have 800000+ records where `articles`.`hash` NOT IN ( '1z8y' ) But table have 2000+ records where `articles`.`hash` NOT IN ( '1z8y' ) LIMIT 800000,10 will return 10 records after 800000th records but it looks like you didn't have 800000 records which satisfy your where clause condition. You ...


2

MyISAM or InnoDB? Your link does not say anything about SQL_CALC_FOUND_ROWS being slower. And I would expect it to be faster if for no other reason than having one fewer round trip to the server. The risk comes when you have thousands of rows. Then either method may be spending more time than you can afford. Come back when you encounter that; we can ...


2

In terms of operation SELECT id,name,description FROM tablename LIMIT 1000,25 SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000 there is absolutely no difference in the statements @siride's comment is exactly the point. LIMIT 1000,25 means LIMIT 25 OFFSET 1000 From the same Documentation LIMIT row_count is equivalent to LIMIT 0, ...


2

You could do it using UDV: #MIN sync_time of user SET @StartDate=(SELECT MIN(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1); #MAX sync_time of user SET @EndDate=(SELECT MAX(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1); #Query: SELECT * FROM test.user WHERE lastmodifieddate BETWEEN @StartDate AND @EndDate;


2

A btree index on (sender,arrival) could help. That would allow it to jump directly to the first-arrived message for a given sender. One on (arrival,sender) is less likely to help. That allows you to jump to the first-sent message globally, but then you still have to walk along those messages until you hit one from the specified sender. If that ...


2

This composite index may help: INDEX(account_id, lastname) When adding it, you may as well remove INDEX(account_id), since it will then be redundant. This may be even better, but I am not sure: INDEX(deleted, account_id, lastname) However it does not supersede INDEX(account_id).


1

I have the same issue. Mongo does not respect quota. I haven't found the answer, but I found SERVER-5136 . According to this, it is recorded issue but sadly resolution is still not planned.



Only top voted, non community-wiki answers of a minimum length are eligible