GROUP BY with date ranges when values change but then change back Consider a historical table where a value was changed and then changed back:

value | date
  3   | 1/1/2019
  3   | 1/2/2019
  3   | 1/3/2019
  4   | 1/4/2019
  4   | 1/5/2019
  3   | 1/6/2019
  3   | 1/7/2019
  3   | 1/8/2019
  3   | 1/9/2019
  2   | 1/10/2019
  2   | 1/11/2019
  3   | 1/12/2019
  3   | 1/13/2019
  3   | 1/14/2019

How do I write a query that shows min and max dates that still shows the value change? I'm looking for:

value | min(date) | max(date)  | days_at_this_val
  3   | 1/1/2019  | 1/3/2019   |        3
  4   | 1/4/2019  | 1/5/2019   |        2
  3   | 1/6/2019  | 1/9/2019   |        4
  2   | 1/10/2019 | 1/11/2019  |        2
  3   | 1/12/2019 | 1/14/2019  |        3

GROUP BY obviously lumps all the 3s together, and RANK and ROW_NUMBER resume counting 3s where they left off. What's the best way to do this?

A common way of doing this is to compare the total order (regarding date) vs the relative order within each value, let's call the difference grp. If the grp changes, it means that date from another partition of value interfered with the current group. So by picking the min date for each value, grp we can achieve what you want. I'll leave days_at_this_val as an exercise for the reader;-)

select v, min(d) as mind, max(d) as maxd
from (
    select v, d
         , row_number() over (order by d)
         - row_number() over (partition by v order by d) as grp
    from t
) as tmp
group by v, grp
order by min(d);

DB<>fiddle

For future postings, you will get more (and thereby better) answers if you simplify for those trying to answer, by posting ddl and sql instead of ascii tables:

create table t (v int not null, d date not null);

insert into t (v,d) values ( 3   , '1/1/2019'),
                           (3   , '1/2/2019'),
                           ...
                           (3   , '1/14/2019');

or by creating a fiddle like the one I provided in the link.


MySQL group concatenation - creation of insert statement on temp table I have a temporary table TEST_GROUP, from where I'm working its data to fill another one.

From this table, giving the example in the picture, my expected result is to group these three rows, so I can have a result like

INSERT INTO TABLEDATA Select * from TEST2 where (field1 = 'B') and (field2 = 'T') and (field3 != 'S' and field3 != '')

Easy to say that when the EXP = EQ only validates the VAL1, but when the EXP=NE should validate VAL1 and VAL2, even if VAL2 is empty.

Not so easy for me to get the expected result.

Can you provide some guidelines how to get the expected result?

There is no SQL that can directly perform what you have described.  Instead...

Read that data into your programming language (PHP, Java, VB, etc) and use code to construct the statement in question.  Then execute it.

Yes, a Stored Procedure could be constructed, but that might be messier.


SSRS URL Changes while navigating I am trying to track down the root cause of this issue I keep seeing.

We have SSRS running with a custom URL like https://reporting.companyname.com/reports.

When I connect to that address I can login and see reports.  If I progress to a report and click manage I get the report detail and the list of properties on the side screen.

Now if I click on data source it gives me a failure to load page and I notice that the URL in the address bar is http://reporting.companyname.com/reports .

If I put an "s" in that URL the page loads.  I am trying to figure out how I can fix this.

Any ideas would be helpful.

SSRS 2008 R2 enterprise edition

The URL to manage the reports is different than the URL to execute the reports (the RDLs files)

You can check it in the Report Configuration.

Regards,
Martín G


Obtain the sum of count of multiple schema and tables in one query in mysql The query belows return 4 rows and it's good. But i need also to sum the results of all the counts in the same query. How to achieve it? I've tried different things but i get sintax errors.
SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1

Here's an ugly solution:
SELECT MAX(tmp.col1) as col1,
       MAX(tmp.col2) as col2,
       MAX(tmp.col3) as col3,
       MAX(tmp.col4) as col4,
       MAX(tmp.col1) + MAX(tmp.col2) + MAX(tmp.col3) + MAX(tmp.col4) as total
  FROM (SELECT COUNT(*) AS col1, NULL as col2, NULL as col3, NULL as col4
          FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, COUNT(*) AS col2, NULL as col3, NULL as col4
          FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, COUNT(*) AS col3, NULL as col4
          FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, NULL as col3, COUNT(*) AS col4
          FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) tmp

This will give you something like:

col1
col2
col3
col4
total

10
20
30
40
100

It's not very efficient, but it gets the job done.


SQL INDEX - how it works? See this question here: Database Administrators meta

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases.

I have read few books and I have taken part in few trainings about databases such as MySQL, PostgreSQL, SQLite, Oracle and also few nonSQL dbs such us MongoDB, Redis, ElasticSearch etc.

As well as I said, I am begginer, with a lot of lacks of knowledge but today, someone told something, what is totally against my begginer's knowledge.

Let me explain. Let's take SQL database and create simple table Person with few records inside:

id | name   | age
-----------------
1  | Alex   | 24
2  | Brad   | 34
3  | Chris  | 29
4  | David  | 28
5  | Eric   | 18
6  | Fred   | 42
7  | Greg   | 65
8  | Hubert | 53
9  | Irvin  | 17
10 | John   | 19
11 | Karl   | 23

Now, it's the part, I would like to focus on - id is the INDEX.

So far, I thought it works in this way: when a table is being created the INDEX is empty. When I am adding new record to my table the INDEX is being recalculated based on some alghortims. For example:

Grouping one by one:

1    ... N
N+1  ... 2N
     ...
XN+1 ... (X+1)N

so, for my example with size = 11 elements and N = 3 it will be like this:

id | name   | age
-----------------
1  | Alex   | 24     // group0
2  | Brad   | 34     // group0
3  | Chris  | 29     // group0
4  | David  | 28     // group1
5  | Eric   | 18     // group1
6  | Fred   | 42     // group1
7  | Greg   | 65     // group2
8  | Hubert | 53     // group2
9  | Irvin  | 17     // group2
10 | John   | 19     // group3
11 | Karl   | 23     // group3

So, when I am using query SELECT * FROM Person WHERE id = 8 it will do some simple calculation 8 / 3 = 2, so we have to look for this object in group2 and then this row will be returned:

8  | Hubert | 53

This approach works in time O(k) where k << size. Of course, an alghoritm to organise rows in groups is for sure much more complicated but I think this simple example shows my point of view.

So now, I would like to present another approach, which has been showed me today.

Let's take once again this table:

id | name   | age
-----------------
1  | Alex   | 24
2  | Brad   | 34
3  | Chris  | 29
4  | David  | 28
5  | Eric   | 18
6  | Fred   | 42
7  | Greg   | 65
8  | Hubert | 53
9  | Irvin  | 17
10 | John   | 19
11 | Karl   | 23

Now, we are creating something similar to Hashmap(in fact, literally it is a Hash Map) which maps id to address of row with this id. Let's say:

id | addr
---------
1  | @0001
2  | @0010
3  | @0011
4  | @0100
5  | @0101
6  | @0110
7  | @0111
8  | @1000
9  | @1001
10 | @1010
11 | @1011

So now, when I am running my query: SELECT * FROM Person WHERE id = 8

it will map directly id = 8 to address in memory and the row will be returned. Of course complexity of this is O(1).

So now, I have got few questions.

1. What are the adventages and disadventages of both solutions?

2. Which one is more popular in current database implementations? Maybe different dbs use different approaches?

3. Does it exist in nonSQL dbs?

Thank you in advance

What are the adventages and disadventages of both solutions?

The second solution can not perform range scans.  It is great for selecting a single ID.  But what if you want ids 3 through 8?  It has to grab all the records individual which in the real world isn't just O(1) * 6 records to retrieve.  In a large, production database with a HashMap index you would get records on different pages, requiring you to hit the disk and read six different pages into memory.

In a B-Tree structure, like how your first situation would actually be implemented, the ids would be sequential on disk and a single page would likely hold ids 3 - 8 increasing the speed of range scans will making individual access O(log n).

Which one is more popular in current database implementations? Maybe different dbs use different approaches?

I don't have a huge experience in lots of different databases.  I know Sql Server uses B-Trees mostly, but SQl 2014 has some new Hash Indexes you can use on certain table.  I hear a lot of No Sql databases and caching databases built on retrieving individual records use hash indexes as well.  This makes sense for caches since you want the record for user A Page 11 and don't need range scans.

Does it exist in nonSQL dbs?
Yes.  Taking a quick look at the create index documentation for postgressql I see it supports both Hash and B-Tree indexes as well as a few others.


What are the alternatives for prefixed indexes in Oracle? You can create an index and define prefix length, so the index will store only first starting symbols from each of column value. It looks like this in MySQL:

CREATE INDEX table_idx ON emp(last_name(8));

Are there an alternative how to do that in Oracle?

You can create an index on an expression (which you cannot do in MySQL):

CREATE INDEX table_idx ON emp(substr(last_name,1,8));

But that won't help anything unless you also use that expression in the where clause, e.g.

where substr(last_name,1,8) = 'foobar'

What exactly is the usage of an index in MySQL that doesn't index the whole value?


Where are join practice questions? Joins are very complicated. I've been trying to fully understand them for years. The only way to do this is practice. I thought this was a good website to practice but most of the questions have many answers that return the same results, since the data is limited. Like this question's answer uses a full outer join. But I get the same result with this query

select c.cust_name, c.city, o.ord_no, o.ord_date, o.purch_amt
from orders as o
join customer as c on o.customer_id=c.customer_id

So there's no way I can tell that either both answers are correct, or my answer would produce bad results with different data.

I'm looking for SQL join practice problems where I'll know if my answers are wrong. SQL version doesn't matter to me.

https://www.hackerrank.com/domains/sql/join

https://www.hackerrank.com/domains/sql/advanced-join

As far as I remember, the solution you give is evaluated on different datasets.

Also you can choose which RDBMS to use: DB2, SQL Server, MySQL, Oracle (unfortunately no PostgreSQL).


Importing external data with specific time format into MS Access I need to import data from a flat file to MS Access.  Part of the data is time information in the format HH:MM:SS.SSSS (i.e. 24-hour time, fractional seconds).  I have tried all of the standard time formats, and have monkeyed around with input masking, but Access keeps complaining that I am violating formats and won't import my data.  Anybody know how to do this?  Thanks.

(BTW, the "date-format" tag was the closest I could find to something like "time format".)

As you have discovered, the Date/Time column type in Access does not support fractional seconds. If you need to preserve the precision (and not round to the nearest second) then you'll need to import the time values as Text and then populate another Long Integer column with the times converted to tenths-of-milliseconds (which I have abbreviated to "tms"):

After the initial import of the times as Text you will have something like this

ID  hms_time       tms_time
--  -------------  ---------
 1  00:00:00.0001
 2  23:59:59.9999
 3  00:00:00.0000
 4  03:25:45.6789
 5  22:57:40.0000

If you create a VBA function in an Access Module like this

Option Compare Database
Option Explicit

Public Function Hms2Tms(hms As Variant) As Variant
    ' convert "hh:mm:ss.dddd" String
    '     to Long Integer tenths-of-milliseconds (0..863999999)
    Dim arr() As String, rtn As Long

    If IsNull(hms) Then
        Hms2Tms = Null
    Else
        arr = Split(hms, ":", -1, vbBinaryCompare)
        rtn = CLng(arr(0)) * 36000000 + CLng(arr(1)) * 600000
        arr = Split(arr(2), ".", 2, vbBinaryCompare)
        rtn = rtn + CLng(arr(0)) * 10000 + CLng(arr(1))
        Hms2Tms = rtn
    End If
End Function

then you can use the query

UPDATE TimesTable SET tms_time = Hms2Tms([hms_time])

to calculate the numeric equivalents

ID  hms_time       tms_time
--  -------------  ---------
 1  00:00:00.0001          1
 2  23:59:59.9999  863999999
 3  00:00:00.0000          0
 4  03:25:45.6789  123456789
 5  22:57:40.0000  826600000

You can also use the following VBA function to convert a numeric "tms" time into an "hms" string:

Public Function Tms2Hms(tms As Variant) As Variant
    ' convert Long Integer tenths-of-milliseconds (0..863999999)
    '     to "hh:mm:ss.dddd" String
    Dim hh As Long, mm As Long, ss_dddd As Currency, remainder As Long, rtn As String

    If IsNull(tms) Then
        Tms2Hms = Null
    Else
        hh = Int(CCur(tms) / 36000000)
        rtn = Format(hh, "00")
        remainder = tms - hh * 36000000
        mm = Int(CCur(remainder) / 600000)
        rtn = rtn & ":" & Format(mm, "00")
        remainder = remainder - mm * 600000
        ss_dddd = CCur(remainder) / 10000
        rtn = rtn & ":" & Format(ss_dddd, "00.0000")
        Tms2Hms = rtn
    End If
End Function


Do you need SQL Server AAG when using SQL Server FCI/RAID10? If I have a 2-Node AlwaysOn Automatic Failover Cluster Instance with a shared disk and RAID-5 or RAID-10 to account for a SAN failure with the same setup in a second datacenter with data propagation via SAN Replication, is there a way to benefit from using AAG in this scenario?

We're not looking to use AAG for scaling-out, offloading backups or READ-ONLY requests. Scaling-out can be implemented via new nodes and NLB. A new node can be added to offload backups. I'm not seeing a use for AAG here. I think the one feature that we'd miss is automatic page repair. I'm not seeing any other uses. But I am new to AlwaysOn.

Thanks for the help!

If I have a 2-Node AlwaysOn Automatic Failover Cluster Instance with a shared disk and RAID-5 or RAID-10 to account for a SAN failure with the same setup in a second datacenter with data propagation via SAN Replication, is there a way to benefit from using AAG in this scenario?

That depends, are you worries about any of the following scenarios?

Strict RPO/RTO
Does your SAN support write ordering across multiple LUNs?
OS/SQL Patching issues
Controller level IO corruption
Downtime for Maintenance

If your answer to the above is, "Not worried" then I don't see any reason to use any AlwaysOn technology (FCIs or AGs). If your answer is, "Yes, one or more worry me" then I would consider looking into a set of technologies to meet your specific concerns and scenarios.


There is a column named in table but it cannot be referenced from this part of the query I've been learning Postgres (coming from SQL Server), and this error really confuses me.

Here is the code with some sample data:

create table T (
ID serial primary key,
A varchar(1),
B varchar(1),
C varchar(1)
)

↑ Testing table.

insert into T (A, B, C)
values('A', 'B', 'C'), ('A', 'B', 'C')

↑ Insert duplicates

delete from T
where ID in (
    select t.ID
    from (  select ID, row_number() over (partition by A,B,C order by A,B,C) as rn
            from T) as t
    where t.rn < (select max(t.rn) from t)
    )

↑ Delete duplicate keeping the last entry.

The problem is in the (select max(t.rn) from t) I am assuming this is a noob mistake related to not knowing postgres syntax when it comes to referencing columns with aliases?

First off, partition by A,B,C order by A,B,C makes no sense. Since you intent to keep the "last" row (meaning the one with the greatest ID), you probably meant:

partition by A,B,C order by ID

Still, the syntax is invalid on top of this. This subquery expression contains a reference to a column of the outer query: (select max(t.rn) from t). The scope of the subquery does not include columns from the outer query so rn is not visible in there. Only columns of the table t are.

You could use a CTE to allow the reference and make the syntax valid:

WITH cte AS (SELECT id, row_number() OVER (PARTITION by a,b,c ORDER BY id) AS rn FROM t)
DELETE FROM t
WHERE  id IN (
    SELECT id
    FROM   cte
    WHERE  rn < (SELECT max(rn) FROM cte)
    )

Still, the query is dangerous nonsense. Do not use this!
Comparing to the greatest row number is logical nonsense as each group of peers might have a different number of dupes. Would delete a lot more than it should.

Simpler, and correct:

DELETE FROM t
WHERE  id IN (
    SELECT t1.id
    FROM  (SELECT id, row_number() OVER (PARTITION by a,b,c ORDER BY id DESC) AS rn FROM t) t1
    WHERE  t1.rn > 1  --  all but the latest
    );

Which, in turn, can be had more cheaply as (assuming all columns NOT NULL!):

DELETE FROM t
WHERE  EXISTS (
   SELECT FROM t AS t1
   WHERE t1.a = t.a
   AND   t1.b = t.b
   AND   t1.c = t.c
   AND   t1.id > t.id
   );

"... where a dupe exists with a greater ID".

Related:

How do I remove duplicate records in a join table in PostgreSQL?


Index method for very few updates and many inserts I am using Postgresql 9.1 with *pg_trgm* extension. I need to create an index on a text-based field. I do not need full-text searches, I use ILIKE queries to make my searches.

I will use pg_trgm but do not have much experience with gin and gist indexes. I will have many INSERT statements (~15000 daily) and very few UPDATE statements (may be 1 or 2 in a week).

What will be the index update overhead for a gin index on a such table? Or is gist more suitable?

The manual for the pg_trgm module has some advice for your question here:

  As a rule of thumb, a GIN index is faster to search than a GiST index,
  but slower to build or update; so GIN is better suited for static data
  and GiST for often-updated data.

The FASTUPDATE feature of GIN indexes (introduced in Postgres 8.4, ON by default) should be interesting for you. INSERTs (and UPDATEs) are stored in a pending list and integrated into the index in later bulk operations by VACUUM. Makes those operations a lot faster, since direct GIN maintenance tends to be expensive, especially for columns with many indexable elements, i.e. many words in your case.

More basic advice on GIN vs GiST in the manual here.
In particular, if your rows are big and you insert in bulks, you may want to raise the work_mem setting.

For very big loads (large parts of the table), it might pay to drop the index and recreate it afterwards.
Although, 15k rows spread out over a day, shouldn't be a problem at all.

Finally, there is a whole chapter on performance of GIN and GiST in the Full Text Search section. Applies generally. Read this.

The gist of it (no pun intended): GIN is considerably bigger and more expensive to maintain, but faster for most lookups (special exceptions apply for pg_trgm). Since the addition of FASTUPDATE writes are only moderately more expensive.


SET or VARCHAR for list of tags in a MySQL photos table? I just discovered the SET column type for MySQL tables. I have a photos table with ARIA engine where I want to store tags/keywords for each photo.

Now I use a VARCHAR type and then find the photos with a MATCH query and a FULLTEXT INDEX, the tags are space separated like "one two three".

When I use SET, I could possibly use tags with spaces, like "a tag with spaces, another one", but how fast would this be? And can I use FIND IN SET to list photos by tag and also list all tags from a range of photos?

Which is the better choice for tags, SET, VARCHAR or even another type?

Neither

Add another table for tags with one row per tag and photo

either just (photo_id, tag) - tag is varchar (or maybe enum, but then it is harder to add more tags), so if two photos have the same tag, there is some redundancy
or two tables - (tag_id, tag) and (photo_id, tag_id) where each tag is unique, so no duplication of texts, and the second table just links tags to photos, thats more effective but a bit more complex

Do not store multiple values in one column if you want to work with them (search by them etc.) - a database is not just a CSV storage and this is what rows and joins are for. It's called Normalization - learn about it and it will save you from many headaches.


Is it possible to set DEFAULT TRANSACTION ISOLATION for all connections in SQL Server? I want the default Transaction Isolation level to be SNAPSHOT.

How do I set this on a SQL Server 2016 database?

It is not possible to configure Snapshot Isolation (SI) as the default isolation level.

To use SI, the database must be enabled for Snapshot Isolation:

ALTER DATABASE CURRENT
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then each connection must explicitly request SI, using for example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

It is possible to set the default Read Committed isolation level to use row versioning (RCSI).

If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:

ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON;

Further reading:

Snapshot Isolation in SQL Server (documentation)
How to set the default transaction isolation level server wide? by Nacho Alonso Portillo


Partition of tablespace My postgres server is running in a windows server and three tablespace are created. Postgresql server is installed in C: drive and the tablespaces are in D: and E: drive. So how to take a backup and restore. Is it similar to normal backup and while restore, in another machine, do i need to have the same setup or i can restore it in a single drive? From the tablespace folder, is it possible to retreive information for only one table which is huge?

There are a few backup options for you.

A standard dump (pg_dump) will be restorable on another machine but you may want to create the tablespaces first before restoring.  Where the tablespaces are is not really a concern for the recovery.  Note you can also do pg_dump -Fc and this will allow you to select tables to restore at restore time.

Another option is to use pg_basebackup if you are on 9.1 or higher and this will rebuild your data directories over replication.  See http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html for more on this option.


Why does this OPTIMIZE FOR UNKNOWN improve my query by several seconds? Ok, so I have a non-stored-procedure query that we're using in an SSRS report. This query has been hellishly slow (I've had the original version of this query running for the past two hours, still not done), in an effort to improve it I rewrote it from scratch, and I came up with the following:

Now here's the boring word-problem portion:

We want to pull a list of the TOP 5 clients per sales rep, but exclude the TOP 10 total clients from that list. (So if John Doe has clients A, B, C, D, and E, and client C is one of the top 10, then only pull A, B, D, and E.)

To do this, the first query used a IN (... NOT IN ( ) ), so I thought that the nesting of IN was the issue, to rewrite it I did an OUTER APPLY that really broke everything.

Anyway, I fixed all that and I ran the query, and it still took 10-15 seconds which I assumed was parameter sniffing. To investigate I ran the query in SSMS, added OPTION (RECOMPILE) (to see what query plan would generate), and got the following:

It can be viewed here on Brent Ozar's 'Paste The Plan'. The query that generated this was:

DECLARE @Top10Temp TABLE (Id INT)
INSERT INTO @Top10Temp
SELECT TOP 10 Id
FROM Object1
WHERE Column2  = @ReportId
  AND Column3  = 0
GROUP BY Id
ORDER BY SUM(Column4 + Column5) DESC

SELECT Object2.*
FROM Object1 AS Object2
OUTER APPLY (
    SELECT TOP 5
        Object3.Id,
        SUM(Object3.Column4 + Object3.Column5) AS Column6
    FROM Object1 AS Object3
    WHERE Object3.Column3 = 0
      AND Object3.Column7 = Object2.Column7
      AND Object3.Column2 = @ReportId
    GROUP BY
        Object3.Id
    ORDER BY
        SUM(Object3.Column4 + Object3.Column5) DESC
) AS Object4
WHERE Object2.Column2 =      @ReportId
  AND Object2.Column3 =      0
  AND Object2.Id      =      Object4.Id
  AND Object2.Id      NOT IN (SELECT Id FROM @Top10Temp)
ORDER BY Object2.Column7
OPTION (RECOMPILE)

Now the same query but with OPTION (OPTIMIZE FOR UNKNOWN) generated the following plan:

Which can also be viewed at 'Paste the Plan'. This plan executed in less than 1 second.

If I add OPTION (OPTIMIZE FOR (@ReportId = #)), where # is the same as the @ReportId variable, I get the same query plan as the second.

Did I do something wrong? I'm having trouble understanding what happened, so any information is much appreciated. (I also really don't like trying to influence the optimizer via hints, but if it's necessary I'll keep it.)

"To investigate I ran the query in SSMS..."
That's the issue.  Local variables use the density vector of statistics yielding a much better row estimate, and consequently already OPTIMIZING for UNKNOWN.  Parameterized dynamic SQL uses the histogram, which pulls the entire row count for a given section.

Look at the estimated number vs actual number of rows for each of your Paste the Plan links.
The second link has waaaayyyy better estimations than the first.

I'd deploy your SSRS query to a dev instance and run some tests as I suspect you might have performance issues.

BTW, update stats or rebuild indexes on those beast tables, if you can.

Links:
Inside the Statistics Histogram & Density Vector


Normalising shared entities and applying constraints I'm looking for a specific "best practice" or "pattern" concerning entities that are shared between different entities, having a relation to one of many.

For example, one may have the generic entity "Address", which could be used to store the common address fields for customers, suppliers, employees, etc...

Would a seasoned DBA take that route or would he rather add the fields to the corresponding entities? I'm also thinking about maintainability, constraints that may (in the future) differ depending on the entity, things like that.

I would love to get references to any authoritative or established works on the subject.

The motivation for piling every kind of address into a single table is usually a misinterpretation and misapplication of the notion of code reuse.

People can make the mistake of assuming that because you have two entities with some common set of attributes, that those attributes belong in their own table.  Sometimes entities have similar or identical columns coincidentally.  One wouldn't create a table for every NAME or DESCRIPTION or EFFECTIVE_DATE in your database, at least I hope one wouldn't be tempted to do this.

Some people mistakenly refer to all instances of removing columns out to their own table as normalisation.  Normalisation involves removing columns to their own tables, but not every instance of removing columns in this way is actually normalisation.  Normalisation prescribes very specific reasons for removing columns from a table.  If none of these reasons are applicable then you aren't normalising you're just making things complicated.

You have two correct ways of thinking about this:  Either your addresses all belong in one pile because you have an entity super-type that incorporates all of the common features of several entity subtypes, including addresses, - or - your addresses belong in separate piles (tables) according to each kind of thing that has an address and you write your procedural code against an IAddress interface which is implemented for each address table.

If you actually have an entity super-type, say LEGAL_ENTITY which has subtypes like CUSTOMER, VENDOR, EMPLOYEE and so forth, then having an ADDRESS table that is a child of LEGAL_ENTITY is a legitimate approach.  It may even be a valuable approach if there is significant overlap between your customers, vendors, employees (or whatever you're tracking) because you can change addresses once instead of in multiple locations when a legal entity moves.  On the other hand, if you don't have such a super-type, then you are going to face the problems pointed out by Richard Tallent.

If you keep your addresses in different tables according to what type of entity owns the address, then you can still achieve code reuse, assuming the language you are using supports interfaces.

As an aside: tvCa pointed out in a comment that addresses may be stored as columns rather than as rows in a separate table.  That will depend largely on how many addresses you need for each entity.  If you are tracking two addresses (physical, mailing) or if you are storing address history then go with an address table.  If you only store one address per addressee, then a separate table is likely overkill.


Change the Row Order to alphabetical then numeral I have a table with a username as unique & primary key.

I ran this Query to order them in ascending order

ALTER TABLE  `table` ORDER BY  `Username` ;

So the rows are ordered like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho16793 | test1    |
| soho4595  | test2    |
| soho504   | test3    |
| soho931   | test4    |
+-----------+----------+

But I want them like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho504   | test3    |
| soho931   | test4    |
| soho4595  | test2    |
| soho16793 | test1    |
+-----------+----------+

How can I achieve this ?

I created a table called "billy".

mysql> SELECT * FROM billy ORDER BY f1;
+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho1234 | test4 |
| soho222  | test3 |
+----------+-------+
5 rows in set (0.00 sec)

which doesn't give the correct sort order - 1234 is bigger than 222!

Then, from here, I got the SUBSTRING() function and formulated
the following SQL:

mysql> SELECT CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED)
AS fred, f2 FROM billy ORDER BY fred ASC;

which gives you the integers you require to sort on ("fred" is an alias for the new integer column):

+------+-------+
| fred | f2    |
+------+-------+
|   10 | test5 |
|   12 | test1 |
|  123 | test2 |
|  222 | test3 |
| 1234 | test4 |
+------+-------+
5 rows in set (0.00 sec)

mysql>

This also helped for CASTing. And thanks to @oNare for his question in comments below which led me to change this post from using the MID() function to the more standard SUBSTRING().

And the answer you require is given by

SELECT * FROM billy ORDER BY CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED) ASC;

+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho222  | test3 |
| soho1234 | test4 |
+----------+-------+
5 rows in set (0.00 sec)

mysql>


too many connection error mysql: how to calculate max_connections how to calculate this values to get the best results to resolve this error "too many connection error mysql" and make my API faster
       in mysql

   wait_timeout = ??
   max_connections = ??
   max_user_connections = ??
   key_buffer = ??
   query_cache_size = ??
   query_cache_limit = ??
   tmp_table_size = ??
   table_cache = ??
   max_concurrent_transactions ??
   max_concurrent_queries ??

and in apache 2

   ServerLimit ??
   StartServers ??
   MaxClients ??
   MinSpareThreads ??
   MaxSpareThreads ??
   ThreadsPerChild ??

what values should i set to this variable to get the best performance

system specification

amazon RDS 100GB DB STORAGE db.t1.micro

1 amazon LBS having two node attached to it each with

   amazon EC2 m1.small 1.7 GB RAM and 8 GB MAGNETIC HARD DISK
   DEBAIN 7.1 WHEZZY O.S

Before deciding on value for max_connections I suggest that you look at WHY it reached the max_connection condition. A bunch of sleeping/idle connection due to loose coding is very common cause. Check if you have proper value for wait_timeout and adjust if this is your symptom.

Besides that, I would check for INSERT/UPDATE query that holds table locks for too long, as well as slow queries that get piled up once in a while.


SQL Server connection string I have moved a site from a Window Server 2003 server to a Window Server 2008 server (64 bit). The site connects to a SQL Server 2005 database. I imported the database, and I am running it under SQL Server 2014 (64bit).

I have the IIS on the Window Server 2008 server running the site. The site runs in classic ASP. I am attempting to determine what connection object type I need to make the classic ASP website connect to the SQL Server 2014 database.

This is the old DSN:

Provider=sqloledb;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=theDatabase;User Id=myID;Password=password

If the application is obfuscating the error message, try connecting with a Powershell script.  Perhaps this will provide more information about the cause of the connection error.  Even if you don't have the Native Client installed, the legacy SQLOLEDB provider that ships with Windows should still work:

$connection = New-Object -ComObject ADODB.Connection;

Write-Output "Connecting using provider SQLOLEDB";
$connection.Open("Provider=SQLOLEDB; Data Source=localhost\SQLEXPRESS; Initial Catalog=Calendar; User Id=userName; Password=password");

Write-Output "Connecting using provider SQLNCLI11.1";
$connection.Open("Provider=SQLNCLI11.1; Data Source=localhost\SQLEXPRESS; Initial Catalog=Calendar; User Id=userName; Password=password");

To use SQL Server Native Client, check to ensure SQL Server 2012 Native Client is listed as an installed program.  It can be downloaded and installed from the Feature Pack downloads: https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065.


MySQL Replace not working I have back slashes stored in mysql.

I want to remove backslash '\'. So, I run query like

UPDATE table_name set col_name = replace(col_name, "Equipe d\'Olivier", "Equipe d'Olivier");

More intrestingly, even if I query something like

Select * from table_name where column_name = "Equipe d\'Olivier"

It returns row even with "Equipe d'Olivier" as well

I could not get any idea on this. How can I replace or remove that backslash

Try replacing double-quotes " by single-quotes ', and use a double back-slash. \\

UPDATE tbl
set col_name = replace(col_name, 'Equipe d\\''Olivier', 'Equipe d''Olivier');

db<>fiddle here


How to install pgcrypto extension in postgres 10 on CentOS 7 I'm in the process of creating a postgres database for production in CentOS 7. So I already installed (yum install postgresql10-server postgresql10 after adding the repos of course) and configured postgres 10. However, in my scripts I need to install pgcrypto extension and I haven't successfully install it. This is what I've done so far:

the first error I got was saying that the /usr/pgsql-10/share/extension/pgcrypto.control files does not exist. Googling I realized that I have to install postgres-contrib package, which I did and then restarted postgres service, but the error continues due to the fact that the extensions were installed into /usr/share/pgsql/extension, so I copied the extension files from they were installed to they were expected and then
appears this message

  "ERROR:  could not access file "$libdir/pgcrypto": No such file or directory"

Googling again I found that maybe I need to give another option, so I ran CREATE EXTENSION pgcrypto FROM unpackaged; then
the error message now is

  ERROR:  function digest(text, text) does not exist

And I'm stuck and without any idea what to do next. Is anybody else using this extension in postgres 10 on Linux?, if so, how did you create the extension?

Version info: PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

From the docs on CREATE EXTENSION

  PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control.

You need to check what SHAREDIR is set to with pg_config --sharedir. If that's not set to /usr/share/pgsql/extension then likely your postgresql-contrib was packaged by someone different than your copy of postgresql. These are compile time variables and they should be the same. Moving them in likely won't work either because if these are packaged by two separate entities they're likely built against different libraries.

CentOS

In your case I assume

You're installing the PostgreSQL from the PostgreSQL repos.
You're installing the -contrib from the CentOS repos.

What you probably want to do is install from the CentOS repos. Uninstall all things related to PostgreSQL, and delete those files you copied or linked and run this

# Adds the PostgreSQL repo
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

# I think this should install them explicitly from the PostgreSQL repo
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-10.1-1PGDG.rhel7.x86_64.rpm
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-contrib-10.1-1PGDG.rhel7.x86_64.rpm


How to regenerate execution plans after clearing the plan cache? After clearing the plan cache on SQL Server, how do I regenerate execution plans for stored procedures without executing the stored procedures themselves?

When I updated statistics this morning, one of my stored procedures was running really sluggishly.   Per some google-fu, I found that clearing the plan cache can fix the issue - and it did.  However, if I have to do this again, I'd like to avoid the performance hit from regenerating the plans while a user is waiting for a response.

Version/edition of SQL Server is 2016 Enterprise.

The only way the plan cache can be repopulated is by running the relevant T-SQL from stored procs or ad-hoc T-SQL.

If you have several stored procs that need to be "primed" at the start of the day, you could use SQL Server Agent to run them with predefined parameters prior to your business coming "on line".


MariaDB / MySQL client table-formatting like `psql`? I hate to nitpick over style, but for the purposes of use Database Administrators is there a way to clean this up a bit,

SELECT 1 AS "myCol1", 2 AS "myCol2";
+--------+--------+
| myCol1 | myCol2 |
+--------+--------+
|      1 |      2 |
+--------+--------+

For comparison, with PostgreSQL,

SELECT 1 AS "myCol1", 2 AS "myCol2";
 myCol1 | myCol2
--------+--------
      1 |      2

Can I get the psql output from MySQL or MariaDB?

I know `psql, the client for PostgreSQL has a ton of options for formatting the output table

\C Sets the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title. (The name of this command derives from “caption”, as it was previously only used to set the caption in an HTML table.)
\a If the current table output format is unaligned, it is switched to aligned. If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a more general solution.
\pset,

border
columns
expanded
fieldsep
fieldsep_zero
footer
format (supports html, asciidoc, latex, latex-longtable, and troff-ms)
linestyle
null
numericlocale
recordsep
recordsep_zero
tableattr
title
tuples_only
unicode_border_linestyle, unicode_column_linestyle, unicode_header_linestyle
and on, and on.

Sadly, no, but there are a few options which may help

--skip-column-names, -NDo not write column names in results.
--skip-line-numbers, -L Do not write line numbers for errors. Useful when you want to compare result files that include error messages.
--silent, -s Silent mode. Produce less output. This option can be given multiple times to produce less and less output. This option results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

May be worth nothing that while silent is almost nice,

SELECT 1 AS "myCol1", 2 AS "myCol2";
myCol1  myCol2
1       2

You're actually losing the tabulated display and just getting a \t.

SELECT 10000000000000 AS "myCol1", 2 AS "myCol2";
myCol1  myCol2
10000000000000  2


Are there any MySQL Triggers or Events that my program can wait for? Say I want a live feed of emails on a web based email system. Emails are inserted into MySQL tables. Let's say I want my program to know immediately when the table is updated so it can check for new emails, would MySQL triggers be a good way to do this?

Lock tables waits for the tables to become available before it returns. Is there a way I can wait for another person to lock it instead of waiting for all others to unlock it.

I could just poll every few seconds to see if the table is updated, but I think it would be better if I could just use some sort of wait until the table is either locked-then-unlocked or wait for the table to be updated before seeing if there are new emails.

Alternatively there might be a way to execute a command when the table is updated?

Do I have to implement my own event waiting service for this, or can it be done in MySQL?

There is a simple way to find out if a table has been updated recently. Just check the table information_schema.tables. Here is the layout:

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

EXAMPLE : You have a table called mydata.mytable and you want to see if that table was updated in the last 10 minutes. Here is the simple query to tell you:

SELECT COUNT(1) UpdatedCount FROM information_schema.tables
WHERE table_schema = 'mydata'
AND table_name = 'mytable'
AND update_time > ( NOW() - INTERVAL 10 MINUTE );

It will give you either 0 (Was not updated) or 1 (Was Updated).

The update_time of the table should be updated before anyone locks it, especially if the table you are polling is MyISAM. InnoDB is another story. Given InnoDB's transactional nature, you could poll the timestamps in the table rather that information_schema.tables just in case information_schema.tables does not update as quickly as desired. Just make sure the table's timestamp column is indexed.

There is an alternative outside of MySQL but does not work for InnoDB tables configured with innodb_file_per_table disabled.

EXAMPLE : Data is installed in default datadir (var/lib/mysql) and you want to poll the table mydata.mytable.

For mydata.mytable being MyISAM

TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.MYD | awk '{print $8}'`

For mydata.mytable being InnoDB

TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.ibd | awk '{print $8}'`

Of course, you would shell script the time comparison to see if is is less than 10 minutes or whatever interval you want.

With regard to events, you will be OK if you use CREATE EVENT but make sure you do not update InnoDB tables in an event. I went through heartburn helping someone troubleshoot this in a chat room on a past question.

Give it a Try !!!


How to fix issue uuid_generate_v1() does not exist in postgresql , actually i have the function under public schema org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from da.test_get(cast(? as integer),cast(? as text),cast(? as text),cast(? as text),cast(? as text))]; nested exception is org.postgresql.util.PSQLException: ERROR: spiexceptions.UndefinedFunction: function uuid_generate_v1() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: Traceback (most recent call last):

Apparently, the CREATE FUNCTION statement used a wrong search path. (This is not shown by \df.)

You can either tell the function to use the value in the environment, or set it explicitly to a correct value:

ALTER FUNCTION da.test_get RESET search_path;

ALTER FUNCTION da.test_get SET search_path = da, public;


Cant restore database from A to B using any method in SSMS GUI I have two databases. One is production (A), the other is development (B).

I use to be able to restore database from a backup.BAK file of A in B by using SSMS > right clicking databases > restore database and using the appropriate settings.

Today I tried the following and in every situation I got the same error "Exclusive access could not be obtained because the database is in use"

Took database B offline (and selected option to drop all connections)
Tried with database B online and choosing option to drop active connections with risk of being left in single user mode (got stuck in single user mode and restore still failed with same error).
Tried with database B online and without choosing option to drop active connections.
Tried with database B online and with option to overwrite the existing database and without and with and without option to close existing connections to destination database.

I'm using SSMS 2017 and just looking for a simple way to restore database B with a copy of A. Anybody know a way to resolve?

I would try:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH NO_WAIT

Note this will make the database inaccessible but guess that doesn't matter if you are going to restore over the top of it anyway.

If that fails then:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH ROLLBACK IMMEDIATE

But if a big transaction is in progress then it could take a long time to rollback.


Postgres: Insert distinct values from one table into another table with constrains in place I'm trying to insert data from one PostgreSQL 9.3 table to another while removing duplicate entries.

I have two tables containing email addresses.

The main table contains emails and a tag for each email address. The combination (email, tag) must be unique, and for this there is a constraint Unique(email, tag) on it.

The second table get created on the fly from text files which contain only the email address. There are many duplicates there.

I need to import the data from the temp table into the main one without breaking the constraint above. For a certain file containing email addresses the tag is constant.

Table structures:

CREATE TABLE emails (
  email character varying(128),
  tag bigint,
  CONSTRAINT "unique-tag-email" UNIQUE (email, tag) )

and

CREATE TABLE emails_temp (email character varying(128)

Here is my query:

insert into emails(tag,email)
select
 655,t.email
from
 emails_temp as t
where
 not exists ( select email from emails where email = t.email )

Note: 655 is just a tag of a certain group of email addresses.

This is the error I get:

  ERROR: duplicate key value violates unique constraint
  "unique-tag-email" SQL state: 23505 Detail: Key (email,
  tag)=(user@hotmail.com, 655) already exists.

There are indeed, two email addresses user@hotmail.com in the file.

Useless to say, because of this error, there is nothing added in the main table (email).

What am I doing wrong?

There are 3 possible kinds of duplicates:

Duplicates within the rows of the bulk insert.
Duplicates between inserted rows and existing rows.
Duplicates between inserted rows and concurrently inserted / updated rows from other transactions.

Just like I explained in this closely related answer:

Using EXCEPTION to ignore duplicates during bulk inserts

But things have become easier for 2. and  3. since Postgres 9.5 introduced UPSERT (INSERT .. ON CONFLICT DO NOTHING).

INSERT INTO emails(tag,email)
SELECT DISTINCT 655, email
FROM   emails_temp
ON CONFLICT (email) DO NOTHING;

If your duplicates only stem from duplicate entries in the source (1.), like you indicated, then all you need is DISTINCT. Works in any version of Postgres:

INSERT INTO emails(tag,email)
SELECT DISTINCT 655, email
FROM   emails_temp;


MySQL multi-source replication with identical DB names How can I set up a multi-source master/slave relation with masters having the same database names?

I am using the MySQL labs 5.7.2 release with multi-source replication.  One of the use cases for multi-source replication is centralized backup of many mysql servers (https://dev.mysql.com/worklog/task/?id=1697).  I want to set up an environment for this purpose.  However, we have multiple database masters that all have the same database names.  There doesn't appear to be any way of rewriting the database name on the slave to avoid naming collisions (like prefixing it with host or channel name).

I found the replicate-rewrite-db option (http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-rewrite-db), which sounded promising but doesn't seem to apply to this case.

Is there any way to accomplish this on the slave side, or is the only option to prefix the names on the master side?

I briefly searched MySQL Documentation
I located the Rewrite DB Option embedded in a new mechanism : CHANGE REPLICATION FILTER
It allows you to manipulate the Rewrite DB stuff without restarting MySQL.
UPDATE 2014-04-02 15:48 EDT
After reading the MySQL 5.7 Docs, I can safely say that CHANGE MASTER TO and CHANGE REPLICATION FILTER still cannot help you. Here is why:

All databases are identically named on all Masters
Replication Filters are global in scope. It would be very convenient if a filter could be scoped toward each MASTER_BIND interface name. At this point, MySQL 5.7 does not do that.

ALTERNATIVE
Perhaps you should set up multiple MySQL Instances on the Slave Server. Make each MySQL Instance use standard MySQL Replication. You can scale down all the buffer sizes and settings for all the Instances on the Slave.
Then, you can set up a mysqldump from each of the MySQL Instances to different folders.
If you want to learn how to setup multiple MySQL Instances on a single server, please see my old posts

Sep 30, 2011 : Running multiple instances on the same host
Sep 17, 2012 : mysqlservice command syntax


Why does CCI creation on an empty table request a huge memory grant? On a QA server with very low memory, I experienced error 8545 when creating clustered columnstore indexes on new tables in SQL Server 2016:

  Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.

I can easily reproduce a large requested memory grant on my local machine. For the following code:

DROP TABLE IF EXISTS dbo.MY_FIRST_FACT_TABLE;

CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX MY_FIRST_CCI ON dbo.MY_FIRST_FACT_TABLE;

I get a memory request of around 512 MB of memory. There is a warning for the excessive memory grant in the actual execution plan:

Even though the query uses 0 KB of memeory it still might time out depending on other activity on the server. Why does SQL Server request so much memory? What can I do about it?

There's a hint about this in the documentation for CCI query performance:

Plan for enough memory to create columnstore indexes in parallel
Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.
The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

That quote suggests that the number of rows in the table matters for DOP but doesn't matter for the memory grant. The memory required also depends on the number of columns so let's test with a table with fewer columns to more easily see the expected difference in memory grant. I'm using this table and index definition:
DROP TABLE IF EXISTS dbo.MY_SECOND_FACT_TABLE;

CREATE TABLE dbo.MY_SECOND_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL
);

INSERT INTO dbo.MY_SECOND_FACT_TABLE WITH (TABLOCK) (ID, COL1)
SELECT TOP (0) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE CLUSTERED COLUMNSTORE INDEX MY_SECOND_CCI ON dbo.MY_SECOND_FACT_TABLE WITH (MAXDOP = 1);

Below are results from a few tests:
╔══════════════════╦════════════════╦═════════════════╗
║ REQUESTED_MAXDOP ║ NUMBER_OF_ROWS ║ MEMORY_GRANT_KB ║
╠══════════════════╬════════════════╬═════════════════╣
║                1 ║              0 ║           89928 ║
║                2 ║              0 ║           89928 ║
║                1 ║        2000000 ║           89928 ║
║                2 ║        2000000 ║          179896 ║
║                1 ║        5000000 ║           89928 ║
║                2 ║        5000000 ║          179896 ║
╚══════════════════╩════════════════╩═════════════════╝

With 0 rows the DOP is downgraded to 1, but otherwise the number of rows doesn't appear to matter for the memory grant. This is undoubtedly a gross oversimplification, but perhaps SQL Server builds the CCI one rowgroup at a time per DOP and maintains the string dictionaries as it goes along. That sounds fine in practice and means that the memory grant won't scale with the number of rows in the table, but without a check for a very low number of rows SQL Server could request an excessive memory grant. The excessive memory grant wouldn't matter in most cases because the CREATE INDEX is so fast, but it can matter if SQL Server waits on the memory grant.
So now I have an idea of why the memory grant is so large, but what can be done about it? SQL Server 2014 introduced the syntax to define an index definition as part of the table definition. It is possible to write a single statement that creates both the table and the index:

INDEX index_name CLUSTERED COLUMNSTORE
Applies to: SQL Server 2014 through SQL Server 2016 and Azure SQL Database.
Specifies to store the entire table in columnar format with a clustered columnstore index. This always includes all columns in the table. The data is not sorted in alphabetical or numeric order since the rows are organized to gain columnstore compression benefits.

For the original table the syntax would look like this:
CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL,
    INDEX MY_FIRST_CCI CLUSTERED COLUMNSTORE
);

A CREATE TABLE statement does not generate an actual plan so I can't immediately tell if this reduced the memory grant. I tried creating the table 1000 times while hammering the sys.dm_exec_query_memory_grants DMV but never got any results. That suggests that memory was not granted but isn't proof. I also tried extended events with query_memory_grant_usage and didn't get any results for the single CREATE TABLE statement.
Of course, the best way to test is to create the CCI the original workflow conditions with limited available memory. I did that and did not experience any memory timeouts.
I suppose it could be said that defining the CCI in the CREATE TABLE statement for a new table is a best practice. However, if your workload causes this error then changing the CREATE TABLE script probably isn't sufficient to address other problems that you'll run into, such as memory issues when you starting loading data into the CCIs.


How to Add Check Constraint between Tables (or change DDL)? Given the following PostgreSQL table definitions:

postgres=# create table if not exists messages(
postgres(#     id serial primary key,
postgres(#     max_length smallint not null check (max_length > 0),
postgres(#     default_text text check (length(default_text) <= max_length),
postgres(#     created_on timestamp with time zone not null
postgres(# );
CREATE TABLE
postgres=#
postgres=# create table if not exists customized_messages(
postgres(#     message_id serial references messages(id) not null,
postgres(#     message text not null,
postgres(#     created_on timestamp with time zone not null
postgres(# );
CREATE TABLE

how can I enforce length(customized_messages.text) is <= messages.max_length per the foreign key constraint?

Note that, as-is, I can do the following:

postgres=# insert into messages (id, max_length, default_text, created_on) values (1, 5, null, current_timestamp);
INSERT 0 1
postgres=# insert into customized_messages (message_id, message, created_on)  values (1, 'longer than 5', current_timestamp);
INSERT 0 1
postgres=# select * from messages;
 id | max_length | default_text |          created_on
----+------------+--------------+-------------------------------
  1 |          5 |              | 2019-01-18 14:12:59.692075-05
(1 row)
postgres=# select * from customized_messages;
 message_id |    message    |          created_on
------------+---------------+-------------------------------
          1 | longer than 5 | 2019-01-18 14:13:38.226987-05
(1 row)

So, I would like to not allow, e.g. check constraint or something else, the longer than 5 to be inserted since its length is > 5.

One way is to duplicate the max_length column and enforce that they have identical values in the two tables with a composite foreign key (instead of the simple one):

create table if not exists messages(
    id serial
        primary key,
    max_length smallint not null
        check (max_length > 0),
    default_text text
        check (length(default_text) <= max_length),
    created_on timestamp with time zone not null,
    unique (id, max_length)
);

create table if not exists customized_messages(
    message_id int not null,
    max_length smallint not null,
    message text not null
        check (length(message_text) <= max_length),
    created_on timestamp with time zone not null,
    foreign key (id, max_length)
        references messages (id, max_length)
);

Note (irrelevant to the question):  the message_id column in the 2nd table does not make sense to be serial and at the same time foreign key to the 1st table.


Row deletion operation on pg table with numerous foreign keys I maintain a Django app with a postgresql backend. Users log in and exchange messages (forum style) with one another.

All users are identified by unique usernames (saved in the auth_user table), that also double up as nicknames for the users when they interact with one another in the forum.

I want to delete/deprecate data of users who haven't logged a session in my web app since the past 3 months. I'm doing this in order to free up nicknames for new users. I.e. new users need to keep a nickname, and if one's taken, they are made to retry. So if I can get rid of old users, the average number of retries a new user has to do will go down too. I.e. it'll become easier for incoming newbies to register for my service. So that's my goal here.

Ideally, I would simply delete the required rows from auth_user, however there are foreign key constraints that get violated (since ON DELETE CASCADE is not set on them, most are DEFERRABLE INITIALLY DEFERRED).

I was going to edit all these foreign key constraints and change them to ON DELETE CASCADE. But before I do that, I wanted to get expert advice on whether that's my only option in the scenario I've described. I'll have to make changes to foreign keys in ~50 tables, and secondly, ON DELETE CASCADE feels a bit too aggressive.

Create an SQL that does the same as the ON DELETE CASCADE would do. Remove the rows from the corresponding ~50 tables in the right order before you remove the users. This is some work but it keeps your data safe from an application error that does an accidental remove of a user with (lots of) messages.


MySQL GROUP BY query drastically slow down when after data reaches a certain amount I need help finding out why the following query that aggregates peoples' responses by "item" slows down considerably after the data being aggregated reaches a certain size.

select
  t1.item,
  count(t1.val) as count,
  round(avg(t1.resp), 2) as mean,
  round(std(t1.resp), 2) as std
from
  person_response as t1
  join person_info as t2
    on t1.person_id = t2.id
where
  t1.resp between 1 and 5
  and t2.is_valid = 1
  and (t2.attr1, t2.attr2) in ((?, ?), ...)
  and t2.attr3 in (?, ...)
  and t1.item in (?, ...)
group by t1.item
;

t1 has about 15 million rows and t2 has about 200,000 rows. All columns involved in the query are indexed. If the WHERE clause captures under 50,000 rows, execution is generally under 100 ms. If the WHERE clause captures 138,665 rows, execution is under 500 ms. But when I extend the WHERE clause to capture 150,685 rows, execution takes considerably longer at 3 m 40 s 366 ms. Nearly 4 minutes!

I solved the problem. I needed a single-column index on t1.item. It had a multi-column index that was doing nothing for the query.


Setting NLS params in logon trigger don't work in oracle 11g We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session parameters are no longer persisting. To explain, I've pasted a session that shows the NLS_DATE_FORMAT not being used properly.

This is the trigger we're using:

create or replace
TRIGGER schmea.nls_tr
AFTER logon ON DATABASE
BEGIN
   execute immediate 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'' NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'' NLS_TERRITORY = ''AMERICA''';
END;

The formats above are not default, so they appear to change on login.

SQL> select * from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT' or parameter = 'NLS_DATE_FORMAT';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
YYYY-MM-DD HH24:MI:SS

NLS_TIMESTAMP_FORMAT
YYYY-MM-DD HH24:MI:SS.FF

SQL> select count(*) from TABLE where start_date > '2012-06-10 00:00:00';
select count(*) from TABLE where start_date > '2012-06-10 00:00:00'
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P024, instance
[domain.com]:[instance] (1)
ORA-01861: literal does not match format string

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select count(*) from TABLE where start_date > '2012-06-10 00:00:00';

  COUNT(*)
----------
      4901

SQL> select * from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT' or parameter = 'NLS_DATE_FORMAT';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
YYYY-MM-DD HH24:MI:SS

NLS_TIMESTAMP_FORMAT
YYYY-MM-DD HH24:MI:SS.FF

Please help. I've been tearing my hair out for 13 hours, 7 minutes and 4 seconds. Any ideas?

Thank you.

Changing the order of the SET seemed to make a difference, as it is working now.

create or replace
TRIGGER schema.django_nls_tr
AFTER logon ON DATABASE
BEGIN
execute immediate 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA'' NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'' NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF''';
END;

I don't really like this solution, as I still don't understand exactly why changing the order should matter. Perhaps the TERRITORY isn't being set correctly, but it was already the default so we're not TOO worried right now.

Also, django does do a TO_TIMESTAMP. It expects most date fields to be TIMESTAMP(6) but we had it pointed at a legacy db with DATE type fields. The NLS shouldn't matter too dramatically in standard deployments.


Drop partition scheme with orphan indexes I have a partition scheme in my SQL Server database that I am trying to drop. This scheme was used by 2 indexes, which I deleted.

I can't delete the scheme, since it appears to still be used by the indexes, even though they don't exist anymore.

Running the following query:

select object_name(i.object_id), i.name, i.index_id
FROM   sys.indexes i
JOIN   sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE  ps.name = 'CallScheme'

I get the names of the 2 tables that had the indexes, but the index names are NULL (and index id is 0).

How do I get rid of these indexes so that I can drop the partition scheme?

but the index names are NULL (and index id is 0)

That's because they are heaps.  BOL Reference on sys.indexes

One way to get these off of that partition scheme would be to create a clustered index for those tables and specify a different filegroup or another partition scheme.  Then once you have those tables off of that partition scheme you should be able to continue with removing it.

create clustered index IX_YourIndex
on dbo.YourTable (YourCiKeyCol)
on SomeOtherFilegroup;


How to retrieve the definition behind statistics added to tables Is there a way to programmatically retrieve the definition of each STATISTICS added to table columns and indexes. For both user added and system created indexes. There are many STATISTICS like '__WA_Sys_*' that are added by Sql Server.

I need to re-write some of them and add more, but there are too many to do them manually with Management Studio.

Between sys.stats and DBCC SHOW_STATISTICS you have all the info needed to see the statistics of your statistics. Eg.:

set nocount on;
declare @object nvarchar(256), @stat sysname;
declare crs cursor forward_only read_only static for
select quotename(object_schema_name(object_id)) +
    '.' +quotename(object_name(object_id)),
    name
    from sys.stats;

open crs;
fetch next from crs into @object, @stat;
while 0 = @@FETCH_STATUS
begin
    dbcc show_statistics(@object, @stat) with NO_INFOMSGS, STAT_HEADER
    fetch next from crs into @object, @stat;
end

close crs;
deallocate crs;

That being said, anybody who understands when the stats need to be updated already knows how to look them up, so I would venture a guess you should not be updating your stats manually.


problem with selecting correct values from database I have the following database and I'm having problems writing the correct select phrase. I am new to SQL and hope you could help me by showing me the correct way to select the desired data:

Employee (eid, ename, salary, did, classification)
Department (did, dname, floor, head)
dbudget (did, byear, budget)
Project (pid, pname, did, budget, ddate)
Onproject (pid, eid, fdate)

Explanation regarding the database:

Department.did = the department id of the project (what are they working on)
dbudget.byear  = the year of the budget

My assignment:

Show the name and salary of the worker with classification 4 who earns the most.

My query:

select ename ,salary from Employee where classification='4'

I don't know how to select the who earns the most....
Show the department id (did) and its name (dname) that have only two employees with classification 3

My query:

select did,dname from Department where classification='3'

I don't know how to select so that it will show only those who have two employees with classification
Show department id (did), first year, and the year after it of departments that in two consequentive years their salary was less than 700000 (hard)

I don't know how to create a query that will represent the first year and the year after it.

1)

select TOP (1) ename,salary from Employee where classification='4'
order by salary desc

2)

 select did,dname from Department
 where exists
 (select 1  from Employee
 group by did,classification
 having count(*) = 2
 and classification = '3' and Department.did = Employee.did)

3)

 with cte as(
 select did,byear,budget,LAG(byear) OVER (partition by did order by budget) as preceeding,
 LEAD(byear) OVER (partition by did order by budget) as next from budget
 where budget < 70000
 )
 select distinct did,coalesce(preceeding,byear) as FirstYear,coalesce(next,byear) as NextYear from cte where cte.byear = preceeding + 1 or cte.byear = next - 1

Hope this helps!


Organizing a comments table: How to justify the choice? Business domain description

Some of the entity types of interest with respect to the relevant business domain are money, user and progress.

The users may need to post comments about money and progress.

In the future, maybe there will be more entity types related to comment, not only money and progress.

My considerations

I have three solutions:

One comments table for all, keeps the "commented entity id" and the "type of entity" of relevance.

CREATE TABLE comment (
    user_id …, -- the identifier of who leaves comment
    text …, -- text of the comment
    created_at …, -- tells when comment has been left
    subject_type …, -- 'money' or 'progress'
    subject_id … -- identifier from 'money' or 'progress', depends on the 'subject_type' value
);

One comment table for all. Each table retains a foreign key in the comment_id column. The table has a "complex" primary key (id, created_at), and the id values are not unique.

CREATE TABLE comment (
    id…, -- not unique
    created_at…, -- indicates when the comment was left
    user_id…, -- identifier of who leaves comment
    text…, -- the text of the comment
    CONSTRAINT comment_pk PRIMARY KEY (id, created_at) -- "complex" primary key
);

When I need to get all comments associated with a given entity instance, I obtain them by means of an operation like SELECT * FROM comment WHERE id = comment_id.
Separate tables, e.g. comment_money, comment_job, ... All the tables will look the same:

CREATE TABLE comment_<entityName> (
    id…,
    entity_id…, -- id of the commented entity
    user_id…, -- who leaves comment
    text…, -- text of the comment
    create_at… -- when the comment was left
);

I prefer the third variant despite the fact that I have to create more tables, because the logic will be really simple and this approach is easy to manage and support.

Question

I will pass the model of database (see "business domain description") to a teacher and I'm not sure how to justify my choice. I think that the teacher can say that my approach is bad because all my tables have the same columns and structure.

What would be good arguments?
Maybe there are more solutions?

This is not really an answer, but more of a set of reflections. However it is to long for a comment so I'll add it as an answer anyhow. These are the base tables if I get it right:

CREATE TABLE USER
( USER_ID ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE MONEY
( MONEY_ID ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE PROGRESS
( PROGRESS_ID ... NOT NULL PRIMARY KEY
, ... );

Since I don't know your business I just invented some identifier as a primary key. It may very well be a composite key, but for simplicity I do it like this.

Alternative 1:

CREATE TABLE comment
( user_id ... not null references USER (user_id)
, text ...
, created_at ...
, subject_type ... -- 'money' or 'progress'
, subject_id ... -- identifier from 'money' or 'progress'
);

In all essential this is a union type for comments on either MONEY or PROGRESS. It is a construction that does not really fit well in a relational world. One major drawback is that it will not be possible to implement referential integrity (foreign key). I'm not sure what the primary key would be here.

Alternative 2:

I'm not sure I understand what you are saying here. Is MONEY dependent of the COMMENT? I.e.

CREATE TABLE comment (
    id…, -- not unique
    created_at ... not null
    user_id… not null references USER (user_id)
    text ... not null
    CONSTRAINT comment_pk PRIMARY KEY (id, created_at)
);

ALTER TABLE MONEY ADD CONSTRAINT ...
    FOREIGN KEY (MONEY_ID, created_at) -- add created_at in MONEY
    REFERENCES COMMENT (id, created_at);

and similar for PROGRESS. If this is what you mean then the COMMENT will have to exist before MONEY or PROGRESS and this seems counter intuitive to me.

Alternative 3

I would probably change the name to:

CREATE TABLE MONEY_COMMENT
( MONEY_ID ... REFERENCES MONEY (MONEY_ID)
, USER_ID ... REFERENCES USER (USER_ID)
, CREATED_AT ...
, ...
,    PRIMARY KEY (MONEY_ID, USER_ID, CREATED_AT) );

and a similar table PROGRESS_COMMENT. I share your thoughts that this is the most elegant solution. If you are interested in all COMMENTS by a USER you can create a view like:

CREATE VIEW COMMENTS AS
    SELECT 'money' as COMMENT_TYPE
         , MONEY_ID AS COMMENT_ID
         , USER_ID
         , CREATED_AT
         , ...
    FROM MONEY_COMMENT
    UNION ALL
    SELECT 'progress' as COMMENT_TYPE
         , PROGRESS_ID AS COMMENT_ID
         , USER_ID
         , CREATED_AT
         , ...
    FROM PROGRESS_COMMENT


SQL How to show information from others tables in select requet There are my database

user
id | user_code | user_name
1  | 1x2d       | Peter

shops
id | shop_code | shops_name
1  | 1234      | shop1
2  | 3456      | shop2

articles
id | art_code | art_name
1  | 5678     | computer
2  | 0987     | display

action
id | M_art_code | M_shop_code | M_user_code
1 | 5678        | 1234        | 1x2d
2 | 0987        |             | 1x2d

I would like to SELECT all value of action where .... with all information from other table

Result
M_art_code | M_shop_code | shops.shops_name | M_user_code| user.user_name
5678       | 1234        | shop1            | 1x2d       | Peter
0987       |             |                  | 1x2d       | Peter

I try with

SELECT * FROM user U, shops S, articles A, action AC
WHERE AC.M_user_code = U.user_code
AND AC.M_shop_code = S.shops_code AND M_art_code LIKE %%

But with this SELECT I've just row ths all value not empty. I need all value.
How I can do to have I would like?

PS: sorry for my English...I'm french

Instead of the (implicit) inner joins you have to use left joins if you want all rows from the left table regardless of the existence of a matching row from the right table.

SELECT ac.m_art_code,
       ac.m_shop_code,
       sh.shops_name,
       ac.m_user_code,
       us.user_name
       FROM action ac
            LEFT JOIN shops sh
                      ON sh.shop_code = ac.m_shop_code
            LEFT JOIN user us
                      ON us.user_code = ac.m_user_code;


MySQL - Large Table best practices/indexes I have a table which currently contains around 33 millions rows of data, and is constantly recording more rows second by second.  The query to pull data out of this table is gradually getting slower and slower, to the point where it is practically unusable.

My table schema is like so:

and the table has the following indexes:

The usual query from this table consists of selecting rows where the shift is equal to X, and the timestamp is between X and Y.

Can anyone provide/suggest a better table set up, or how I can improve upon the indexes to make this table more efficient?

Right now I'm trying to go through and re-index it, as I believe the indexes have become fragmented, and MySQL keeps loosing connection just trying to reindex the table.  So I'm not sure how I can even accomplish this.  My thought is to re-create the table structure and then slowly migrate over the data using a more efficient index set.

As a direct answer, what your query needs is a composite index on (shift, timestamp): ALTER TABLE historical_data ADD INDEX shift_ts(shift, timestamp)

However, you have a lot of indexes. and some are redundant.

tag_id: has low cardinality. Drop it if you don't have "WHERE tag_id=x" queries
shift, timestamp: combine them in 1 index (This is useful for your query)
hd_multicolumn: drop
value: if you don't query it, drop it
hd_multicolumn2: drop it
[IMPORTANT] If your table is an InnoDB table, add an auto increment field, and make it a primary key


Select DISTINCT records with extra information I have a MS Access database full of customer names and addresses. I want to send out a mailing but only one per address. Therefore if there are 3 different customers at the address I want only one row. Here is the basic query I have so far:

Group   Fname   Lname   Address

So my first attempt was:

SELECT DISTINCT Customers.[Address 1]
FROM Customers

This works as I wanted and returns only unique addresses. However I also need the group, fname and lname fields. When I add these in I lose the DISTINCT functionality as often customers in the same household will have different Lname and grouping.

Whats the best way to select the data I need but still use DISTINCT on the [Address 1] field.

SELECT c.*
FROM
      Customers AS c
   INNER JOIN
      ( SELECT [Address 1], MIN([Order Date]) AS min_od
        FROM Customers
        GROUP BY [Address 1]
      ) AS grp
         ON  grp.[Address 1] = c.[Address 1]
         AND grp.min_od = c.[Order Date] ;

This may still give you two rows for an address, if you have two rows with the same minimum [Order Date] for an address.


Query Performance When Dropping Multiple Columns In PostgreSQL, is there any performance gain when using a single ALTER TABLE statement to drop multiple columns, instead of using a separate statement to drop each column?

ALTER TABLE table DROP COLUMN col1, DROP COLUMN col2, DROP COLUMN 3;

// vs

ALTER TABLE table DROP COLUMN col1;
ALTER TABLE table DROP COLUMN col2;
ALTER TABLE table DROP COLUMN col3;

Yes, but it's too small to be noticed unless you plan to drop hundreds or even thousands of columns ;)

Each drop column is executed separately regardless if it's a separate statement, or part of a statement. The performance gain would be in the work postgres do before and after each statement, for instance taking a lock on the table, creating implicit transactions, and parsing the statement which it would only have to do once if all columns are dropped in one statement.


can we create number of fields at a time with same data type in mysql? alter table students
 add s1,s2,s3,s4,s5,s6,s7 int;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's2,s3,s4,s5,s6,s7 int' at line 2

You can add several columns at once but you need to declare the types per column:

alter table t add column (s5 int, s6 int);

You can also use:

alter table t add (s5 int, s6 int);

but I prefer the first one. Note that the parentheses are mandantory.

You don't mention your version, but the syntax have been valid since at least 5.1:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html


Database restore without deleting old database I've done a copy of my database, using SQL Server 2008 R2 (DB -> Tasks -> Back Up).
Now I'm trying to restore it (DB -> Tasks -> Restore DataBase).

But SQL Studio give me an error and say, that I need to delete old DB to do it.
How to restore it without deleting?

I'm assuming you want a second copy of your DB, but with another name... since you want to "restore it without deleting"? You can definitely do this.

Instead of selecting your current DB, right click on the "Database" item in the object explorer window
In the context popup window select "Restore Database...".
In the window that pops up, enter in a 'To Database' name for your copied DB, i.e. the new name for the DB copy. So if your original was TradeWind, call this one TradeWindCopy for example.
Make sure the 'From database' radio button is selected, and in the dropdown select the backup you just made (tick the required check box). You could also restore 'from device' if you wanted - in that case you need to select a ".bak" file to restore from.
Click the OK button and your DB backup is now restored as the new DB with the name you set in step 3.

Good luck.


Disable Primary Key on MyISAM Table I wanted to speed up a large insert bulk by disabling keys. So I ran the following command on a MyISAM table:

alter table my_table disable keys;

When I execute show index from answer_2011, I see a comment of disabled in all the keys, except for the primary key.

Also, as I'm inserting new rows, I see that my_table.MYI is being modified.

Is there a way to disable or drop the primary key that does not involves having MySQL create temporary tables?

Unfortunately, no there isn't because ALTER TABLE ... DISABLE KEYS disables nonunique indexes

  For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

If you have to do a bulk insert, you need to use LOAD DATA INFILE along with a larger bulk_insert_buffer_size. Here is why:

  MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.


Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible We are using Oracle 11g, 11.2.0.3.

We know a record exists in a table but a select is not returning it for some odd reason.

The execution plans the general query, not forcing any index, shows that an Index oneIndex is used. No record is returned.
Forcing another index, using the same query returns the missing record.
Setting oneIndex to invisible and back to visible...
Rexecuting the initial query (not forcing any index) does return the record (but somehow, the execution plan is not using oneIndex anymore). Maybe it woke up and found that stats were old ???
Taking the first query again and forcing the use of the faulty index (oneIndex) works fine now.

Facts: Statistics were quite old.

Session Parameter for ORACLE SQL Developer:

ALTER SESSION SET
   OPTIMIZER_FEATURES_ENABLE = '11.2.0.1'
   OPTIMIZER_MODE = FIRST_ROWS_1
   "_HASH_JOIN_ENABLED" = FALSE
   "_PUSH_JOIN_PREDICATE" = TRUE
   "_PUSH_JOIN_UNION_VIEW" = TRUE
   "_COMPLEX_VIEW_MERGING" = TRUE
   "_TABLE_SCAN_COST_PLUS_ONE" = TRUE
   "_ORDERED_NESTED_LOOP" = TRUE
   "_NEW_INITIAL_JOIN_ORDERS" = TRUE
   "_UNNEST_SUBQUERY" = TRUE
   "_INDEX_JOIN_ENABLED" = TRUE
   "_LIKE_WITH_BIND_AS_EQUALITY" = TRUE
   NLS_SORT = BINARY
   NLS_COMP = BINARY
   NLS_NUMERIC_CHARACTERS = '.,'
   QUERY_REWRITE_ENABLED = FORCE
   CURSOR_SHARING = EXACT
   DB_FILE_MULTIBLOCK_READ_COUNT = 0
   OPTIMIZER_INDEX_COST_ADJ = 5
   OPTIMIZER_INDEX_CACHING = 95

The SQL is a bit had to follow since it is produced by a Content Management application. I will not provide it for now.

Q1: Why is an index no longer chosen by the execution plan after
switching to invisible and then back to visible?
Q2: How can Oracle momentarily not see a record when searched from an
index instead of another index?

The data returned should be always the same independent of the access paths used. It never depends. The result set is defined by the text of the sql statement. Hints are comments and do not influence what data is retrieved. Indexes are auxiliary data structures that facilitate data retrieval but have no influence on what data is returned.
So this is a bug and you should contact Oracle support.


SQL Server 2005 Restore from Full and Differential Backup I'm not a DBA, however I have been a developer for 10yrs and am competent.

I wish to move our corporate application to a hosted service and I need to copy the data. The data centre is too far from our office to deliver a disk and ftp is too slow. Our current database uses SQL Server 2005; the target is SQL Server 2008.

My plan is to courier a disk with a full backup on a Friday morning and do a restore at the data centre over the weekend.  I then plan to FTP a differential backup with deltas from Friday's backup and restore the differential to make the data current.

To test the process, I plan to restore a full backup then create a differential and restore that.  My only environment for testing this is our production server.  I believe that I can restore the backup to a new database name using the following T-SQL:

-- make sure have good backup
RESTORE VERIFYONLY FROM DISK = 'x:\mypath\data.bak'

-- get logical names
RESTORE FILELISTONLY FROM DISK = 'x:\mypath\data.bak'

-- restore to new db
RESTORE DATABASE MyNewDb
FROM DISK = 'x:\mypath\data.bak'
WITH
    RECOVERY,
    STATS = 10,
MOVE 'olddb_Data' TO 'x:\mynewpath\MyNewDb.mdf',
MOVE 'olddb_Log' TO 'x:\mynewpath\MyNewDb.ldf',
MOVE 'sysft_ft_resume' TO 'x:\mynewpath\ft_resume'
GO

By running something similar with VERIFYONLY I have observed that this only works with a full backup if no subsequent differential has been run.

My questions are:

Is this safe to run on a production database (I am paranoid about overwriting files, etc)?
Since a differential appears to alter the full backup, will my plan to ftp the differential work given that the full backup will have no knowledge of the differential?

I don't think this is a complex task although I will pay for a professional, if that's necessary.

Any other suggestions would be gratefully received.

Hopefully, my previous research can be helpful for you. Here is a step-by-step guide I created when I had to move several very large databases to a data center. It uses log shipping.

Regards,
John


How do I get the some of the values from 2 foreign keys in a table? I've spent a good amount of time on this but, still have no idea as to the sql query I can use to get the student name, and class name from the payments_per_student table.

edit: This using JPA work with this DB. Knowing the JPA way as well SQl way would be interesting. I'm currently researching the JPA way.

payments_per_student:

PAYMENTS_PER_STU_ID
STUDENTS_STUDENT_ID
FAMILY_ACCOUNT_FAMILY_ID
REASON_FOR_PAYMENT
AMOUNT
DATE_PAID
CLASSES_CLASS_ID
ACTIVE_IN_CLASS
NOTES

The second is, Classes:

CLASS_ID
CLASS_NAME
DAY_OF_CLASS
TIME_OF_CLASS
DURATION_OF_CLASS
PRICE_PER_CLASS
PRICE_PER_TERM
PRICE_PER_YEAR
NOTES

The third table is, Students:

STUDENT_ID
FIRST_NAME
LAST_NAME
FAMILY_ACCOUNT_ID
SEX_TYPE
NAME_PREF
DATE_OF_BIRTH
PERFORMANCE_STU
HEALTH_ISSUES
NOTES

This is a good start:

select S.FIRST_NAME,    C.CLASS_NAME,    C.DURATION_OF_CLASS
FROM STUDENTS S
INNER JOIN PAYMENTS_PER_STUDENT A ON S.STUDENT_ID=PAYMENTS_PER_STU_ID
INNER JOIN CLASSES_R C ON A .CLASSES_CLASS_ID = C.CLASS_ID;

I found it + here.


Minimum user rights for connecting MS Sql database remotedly I have followed this tutorial for getting connected to a Microsoft SQL Server. I connect with ping functionality of Glassfish Server and thus I have to give some user rights to be able to even ping the Database.
My problem is that probably my user has not rights to take external connections to the database or even more sad, not any permissions. What kind of user permissions there should be applied before I can connect to my database with my user?
(I suppose there are needed some GRANTS before accessing the database, because making that on Mysql needed quite many of them. Now the actual problem is I could not apply the same queries here 'cause the SQL scripts are incombatible)

Check for "CONNECT" rights

SELECT SUSER_NAME(grantee_principal_id), *
FROM sys.server_permissions WHERE type = 'COSQ'
GO
USE MYDB
GO
SELECT USER_NAME(grantee_principal_id), *
FROM sys.database_permissions WHERE type = 'CO'

To fix as needed

USE master
GO
GRANT CONNECT SQL TO myLogin
GO
USE MYDB
GO
GRANT CONNECT TO MyUser
GO


How to config mongo on a remote server? I have two servers, one for app(rails) and one for db(mongo).My rails config for mongo will be like this(yaml file):

hosts:
  - xxx.xxx.xxx.xxx:57777
database: admin
username: root
password: some_password

So.. how can I configure on the mongo server including the 57777 port, password for root user?

For the port you need to start mongod with parameter --port 57777 . Alternative place it on the configuration file under

net:
   port: 57777

For the username and password part you need to enable security by start  mongod with parameter--auth . Alternative place it on the configuration file under security.authorization = enable
Then you can create the username:root assign a password and certain privileges.

More can be found here: http://docs.mongodb.org/manual/core/authorization/


ORA-00060 | DEADLOCK DETECTED We faced Oracle deadlock (ORA-00060) problem on live for our Java/J2EE based web application. The trace file is pasted below:

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                                          ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name                             process session holds waits  process session holds waits
TX-00030013-0000CC59-00000000-00000000        106     408     X             76     171           X
TX-000F001E-0001681C-00000000-00000000         76     171     X            106     408           X

session 408: DID 0001-006A-007D5293 session 171: DID 0001-004C-001C2F48
session 171: DID 0001-004C-001C2F48 session 408: DID 0001-006A-007D5293

Rows waited on:
  Session 408: obj - rowid = 0002A2AC - AAAqKsAAUAABE3oAAB
  (dictionary objn - 172716, file - 20, block - 282088, slot - 1)
  Session 171: obj - rowid = 0002A2AC - AAAqKsAAUAABE2pAAA
  (dictionary objn - 172716, file - 20, block - 282025, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 171:
  sid: 171 ser: 62179 audsid: 1760178 user: *******
    flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 76 O/S info: user: ****, term: UNKNOWN, ospid: 48169
    image: **************
  client details:
    O/S info: user: ****, term: unknown, ospid: 1234
    machine: ********* program: JDBC Thin Client
    client info: *********************
    application name: JDBC, hash value=3519240545
    action name: ProcessDummySearchData-UpdateSearchData, hash value=560080304
  current SQL:
  UPDATE TABLE1 SET COL3=:1 ,LASTMODIFIED=SYSDATE WHERE UUID=:2  AND COL2=:3

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=635v1mk350vst) -----
update TABLE1 set COL3= :1
where
COL4= :2
and COL5 =  :3

If I try to apply "proposed fix" from below URL: I fail to understand how creating composite indexes (UUID, COL2) & (COL4, COL5) in TABLE1 can solve this issue? Can anyone throw some light on how to solve this issue? I understand that this particular issue is perhaps due to simultaneous update on same table/row.

https://stackoverflow.com/questions/20474959/how-to-find-out-the-cause-of-an-oracle-deadlock

deadlocks are an applicative error and should be treated as such.
Look through the application to find the 2 flows which change this row and decide on the best course to avoid collisions.

The link you shared is related to changes locking foreign keys- this is not the case.

You just need to find & fix the app - happy hunting :)


Oracle how to manage the flashback log space? I set db_flashback_retention_target=1440min,Why the flashback log kept 7 days? Thanks!

closed as unclear what you're asking by Phil, Michael Green, Tom V, Shanky, Andriy M Jan 5 at 12:53

Why the sql-server users can close my question?
They know what flashback_log is?

Short answer: Oracle will keep flashback logs as long as it can, limited only by space in FRA.

Longer answer: The database FRA (Fast Recovery Area) is a fixed size, defined by parameter db_recovery_file_dest_size

In this, go various things, including:

Archive logs
Flashback logs
Redo logs
Standby recovery logs
Backups
And more!

Oracle will, under certain circumstances and under space pressure, automatically delete stuff from FRA to make room for new stuff.  It will delete flashback logs at will, even if that brings the possible flashback time under the value that the db_flashback_retention_target parameter is set to.  Remember: It's a target.

If, under space pressure, Oracle can't delete enough stuff (e.g., archive logs that  haven't been backed up) in order to continue, it will "freeze" and give an error on log in.

If there is no space pressure within the FRA, it will continue to store stuff for as long as it can.


SQL Server Agent Error 1053 A few months back I installed SQL Server 2008R2. At the time of installation I used

1.Mixed Authentication
2.NT Authority\NetworkService as the SQL Agent user name, and the same user name for all other components also.

At that time everything worked.  Now when I start the Agent I am getting this below error.

  Windows could not start the SQL Server Agent (MSSQLSERVER) service on Local Computer.
  Error 1053: The service did not respond to the start or control request in a timely fashion.

I followed this link (http://support.microsoft.com/kb/322140).  The steps are not clear to me but I tried a few. When I run by using cmd as admin I get this error:

  The program can't start because sqlncli10.dll is missing from your computer. Try reinstalling the program to fix this problem.

Please can anyone help me with easy steps?

I have seen such problems when other VS is installed\updated after installing SQL server. Easy step will be to run SQL repair - Steps here.

Alternative solution is to install SQL Native client and can be downloaded from here under install instructions.

Again, above solution will only solve current error. It will be great if you could share more information like when it got corrupt and what was installed to provide you better solution.