pl/pgsql: Dynamicly get a column name from a record Greeting,
I want dynamicly get a column name from a record.
As showing in the code below that I created a cursor and I used a loop to process each record in that cursor by fetching each row to a record type r1.
In my table I have these columns [dlq_2000,dlq_2001,...,dlq_2017,dlq_2017].
Also I created on top of it a loop to process each column separate.

The issue I am facing is getting the field name from r1 dynamicly and I am getting this error when I run the code:

  [Err] ERROR: record "r1" has no field "'dlq_'||counter::text" CONTEXT:
  SQL statement "SELECT ( r1."'dlq_'||counter::text" = 1 )"

Please advice how to solve this issue.

Thank you,

CREATE OR REPLACE FUNCTION update()
   RETURNS VOID AS $$
DECLARE
cur SCROLL CURSOR   FOR select * from my_tbl;
r1 RECORD;
begin
OPEN cur ;
FOR counter IN  2000..2017 LOOP
    r1 := NULL;
    LOOP
        FETCH cur INTO r1;
        EXIT WHEN NOT FOUND;

        IF (r1."'dl_'||counter::text" = 1 ) THEN
            -- do some thing
            RAISE NOTICE 'processing year of : %', counter;
        END IF;
    END LOOP;
END LOOP;
CLOSE cur;
END;

 $$

LANGUAGE plpgsql;

Using row_to_json function:

do $$
declare
  r json;
  i int;
begin
  for r in
    select row_to_json(t.*)
    from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)
  loop
    raise info '%', r;
    for i in 11..13 loop
      if r->>('y'||i) like '%2' then -- Condition here
        raise info 'Do something for %', r->>('y'||i);
      end if;
    end loop;
  end loop;
end $$;

INFO:  {"x":1,"y11":"a1","y12":"a2","y13":"a3"}
INFO:  Do something for a2
INFO:  {"x":2,"y11":"b1","y12":"b2","y13":"b3"}
INFO:  Do something for b2

Using array constructor:

do $$
declare
  r record;
  i int;
begin
  for r in
    select x, array[y11,y12,y13] as y
    from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)
  loop
    raise info '%', r;
    for i in 1..3 loop
      if r.y[i] like '%2' then -- Condition here
        raise info 'Do something for %', r.y[i];
      end if;
    end loop;
  end loop;
end $$;

INFO:  (1,"{a1,a2,a3}")
INFO:  Do something for a2
INFO:  (2,"{b1,b2,b3}")
INFO:  Do something for b2

And using data normalization:

do $$
declare
  r record;
  i int;
begin
  for r in
    with
      test as (
        select *
        from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)),
      norm as (
        select *, unnest(array[y11,y12,y13]) as y, unnest(array[11,12,13]) as z from test)
    select * from norm
    where y like '%2'  -- Condition here
  loop
    raise info 'Do something for %', r;
  end loop;
end $$;

INFO:  Do something for (1,a1,a2,a3,a2,12)
INFO:  Do something for (2,b1,b2,b3,b2,12)

There are simplified examples, just to show how it could be done in the your more complex task.But it was tested on the PostgreSQL 9.5


MySQL do not accepts wildcard for tables name level grant Trying to grant a user select privilege on multiple tables using wildcards as following:
GRANT SELECT ON 'DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password';

But keep giving the following error:
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 ''DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password'' at line 1

Server version: 5.7.30-log MySQL Community Server (GPL)
I'm sure that the syntax of the query is correct, but for some reason it won't accept it, Any advise?

The MySQL 5.7 GRANT Syntax Documentation says something interesting about wildcards
Under the subheading Object Quoting Guidelines, Paragraph 1 says:

Several objects within GRANT statements are subject to quoting, although quoting is optional in many cases: Account, database, table, column, and routine names. For example, if a user_name or host_name value in an account name is legal as an unquoted identifier, you need not quote it. However, quotation marks are necessary to specify a user_name string containing special characters (such as -), or a host_name string containing special characters or wildcard characters such as % (for example, 'test-user'@'%.com'). Quote the user name and host name separately.

Paragraphs 3 and 4 say

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level (GRANT ... ON db_name.). This means, for example, that to use a _ character as part of a database name, specify it as _ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern (for example, GRANT ... ON foo\_bar. TO ...).
When a database name not is used to grant privileges at the database level, but as a qualifier for granting privileges to some other object such as a table or routine (for example, GRANT ... ON db_name.tbl_name), wildcard characters are treated as normal characters.

What about table privileges ? Please note the Docs on Table Privileges:

Table privileges apply to all columns in a given table. To assign
table-level privileges, use ON db_name.tbl_name syntax:
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than db_name.tbl_name, the statement
applies to tbl_name in the default database. An error occurs if there
is no default database.
The permissible priv_type values at the table level are ALTER, CREATE
VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES,
SELECT, SHOW VIEW, TRIGGER, and UPDATE.
Table-level privileges apply to base tables and views. They do not
apply to tables created with CREATE TEMPORARY TABLE, even if the table
names match. For information about TEMPORARY table privileges, see
Section 13.1.18.2, “CREATE TEMPORARY TABLE Statement”.
MySQL stores table privileges in the mysql.tables_priv system table.

As shown from the MySQL 5.7 GRANTS Documentation

Wildcards are applicable to hostname (which are bold italiczied)
Wildcards are applicable to database name (which are bold italiczied)
Nowhere do the docs specify wildcards for table names
Wildcard characters are treated as normal characters for all other database objects

The workaround given in the comment is what I used to do when giving away SELECT on views.


How do I stop PG from wasting ids when doing ON CONFLICT DO NOTHING? I have an empty table with one bigserial id column and one unique text column, and do this:
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test2

The table now has two rows:
1, test
3, test2

I expect and want:
1, test
2, test2

Why is the bigserial id column being increased even when "NOTHING" is happening for the second row due to the uniqueness of the text column?
More like "ON CONFLICT DO SOMETHING".
I doubt that this is related to the real error I'm having and which has been eluding me for months, but I need to straighten this out first as it seems like it could be related.
I could not find any mention in the manual on how to make it not increase the id when it doesn't INSERT any row.

This is Working As Designed™.
Firstly, serial data types are not "real" types, they are just syntactic sugar for integer columns populated from sequences.
Then about sequences the manual says so:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused “holes” in the sequence of assigned values.

Subsequently (no pun intended), you cannot "stop PG from wasting ids" (even if you stop using Postgres altogether, because all other DBMSes behave in the same manner).


Why does PostgreSQL create an additional NULL bitmap if all fields after the 8th are NOT NULL? I don't refer to the 1 Byte null bitmap included in the row header that could be used to  flag null values for any of the first 8 columns. See:

https://stackoverflow.com/questions/12145772/do-nullable-columns-occupy-additional-space-in-postgresql/12147130#12147130

My question is about the new 8 bytes null bitmap added by Postgresql to each row:

with 9 or more columns
and including at least one null

I have done some tests by creating / populating some tables and checking row size using pgstattuple extension and I have observed this strange behavior.

The new null bitmap is created even if the 9th (and last) column is created with NOT NULL constraint.

Do you have any explanations? Or am I doing something wrong?

The answer to your question is also in the linked answer:

  NOT NULL constraints do not directly affect that.

The size of the NULL bitmap is not influenced by NOT NULL constraints at all.

And there is only ever one (or no) NULL bitmap per row. Never an "additional" NULL bitmap. For rows up to 8 user columns the spare byte between row header and column data (or an optional OID) is used. With more than 8 user columns, the NULL bitmap is extended by MAXALIGN (typically 8 bytes). Column data moves back by the same number of bytes. Resulting in a single NULL bitmap.

Even if it might seem reasonable not to extend the bitmap for trailing columns with NOT NULL constraints, complications might ensue. Like: removing a constraint would have to trigger a whole-table rewrite.

And even if that might be handled, I expect no core developer would be willing to complicate the code to save a few bytes per row for this rare special case.


How to keep history of a mysql table that has autonum as key? My solution to this (probably typical) Mysql problem is horror performancewise, see below. But first the problem.

I've got a table

prices (fkey AUTONUM*, b STRING[3], c STRING[3], validon DATETIME, price FLOAT)

fkey is the int32 primary key. The (b,c) pair is another UNIQUE index and is needed for various reasons.

The following kind of queries are executed all the time and are the only way to update or insert into the table:

INSERT INTO prices (b, c, validon, price) VALUES (...),(...),...
ON DUPLICATE KEY UPDATE price = VALUES(price)

So autonum runs freely as needed and price is updated if a clash is encountered.

This works superb, until I need to have another table "history" which keeps track of all prices ever inserted or updated. I designed it like this:

history (a INT32, validon DATETIME, price FLOAT)

This table has only one key - the combination of all three columns.

My question is: How to insert rows in table "history" as rows of table "prices" are being inserted/updated? The issue I have is to retrieve the correct autonum values quickly and efficiently.

Right now my solution in pseudocode is this:

LOCK TABLES prices, history WRITE;

k := SELECT MAX(fkey) + 1 FROM prices

// add_to_history() always returns 0 and has only one statement :
// INSERT IGNORE INTO history VALUES (a, b, c);

INSERT INTO prices VALUES ......
ON DUPLICATE KEY UPDATE
    price = if(validon < values(validon),
         values(price) + add_to_history(fkey, values(validon), values(price)),
         price),
    validon = if(validon < values(validon), values(validon), validon)

INSERT IGNORE INTO history
SELECT fkey, validon, price FROM prices
WHERE fkey >= k;

UNLOCK TABLES;

The solution "works", but it blocks the entire server occasionally for random periods of time (not indefinitely, but for hours) and it must go.

Both tables can be either MyIsam or InnoDB (right now they are MyIsam). Please let me know if a certain DB engine is needed to solve this properly.

In my opinion your best bet is adding a trigger to the prices table after the insert so that it adds the desired information in the history table. You will need another trigger that is called after update to deal with insert collitions.

After that you go on simply inserting in prices as you have been doing all along and let MySQL do the rest.

Example for insert trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER INSERT ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price)
    /* Example insert end */
END $$
DELIMITER ;

Example for update trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER UPDATE ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price - OLD.price)
    /* Example insert end */
END $$
DELIMITER ;

Please change the triggers' insert queries since I do not understand your history table and these examples will fail on duplicate ids.


Why do we need partitions in PostgresSQL? (or any other RDBMS) So, I am trying to teach myself partitions in PostgreSQL. I understand that a database can become slow when a table hits millions+ of lines and indexes don't fit into memory anymore.

One thing that strikes me is how partitions with sub-partitions resemble a lot to some kind of manually managed indexes. What is wrong with regular indexes? Why can't them partition themselves?

I imagine this must be hard, judging by the fact that this problem seems to have been around for a while. Maybe the DB cannot know how we plan to access our data? If so, why isn't there some simple way to tell the DB how we want to access our data?

Why do we have to do the whole table inheritance dance when partitionning is such a common case?

EDIT: There seem to be some work in progress to support a nicer syntax, Cf. https://wiki.postgresql.org/wiki/Table_partitioning

A general goal in designing data storage and retrieval systems is that query time should scale with the amount of data being retrieved, not the amount of data that exists in total.  Partitioning is a powerful tool for achieving that goal.

Consider a table of session data that looks something like:

create table clickstream (
  clickstream_id bigserial primary key
, created timestamp default now() not null
, user_agent text
, client_ip inet
, entry_page text
, session_id text
--and so on...
);

Obviously you would have an index on created which would allow you to perform queries on a particular date/time range of the data.  If you get 10k sessions a day and you have 3 months of data online, querying for a day's worth of data works quite well.  If you get 1M sessions a day and you have 4 years of data online, this works poorly.

But why?  The nature of the I/O you're performing -- random reads -- doesn't scale well.  The query engine has to find the location in the created B-tree that corresponds to the start of your range, and likewise the end of your range, and traverse every element of the tree in between, and read each row individually.  Or rather, read each relevant block individually (since all I/O is at the block level), but many rows means many blocks, especially for wide tables.

What you would prefer to do is read all of those blocks sequentially, as in a full table scan.  In fact what if you are doing this kind of operation over and over again, you'll find yourself creating a temporary table with just the day's data and querying that instead, as an optimization.  This is the kind of optimization that partitioning let's the query engine do automatically.

Partition the table by day, and when you write (for example) select ... from clickstream where created >= current_date-1 and created < current_date the query engine will understand that this data falls entirely within one partition, and then compute that even if created is indexed on that partition that using said index is not worthwhile, and perform a much faster sequential scan.

Now, that's for an analytic workload.  Partitioning can also be valuable in a transactional workload, but in more limited ways.  Obviously if your transactional system is doing range scans in real-time then the exact same pattern can apply.  But it can also be useful to partition on certain keys.  For example if you have an orders table with a customer_id, and the nature of your business is such that most customers have many orders, and the orders table is always accessed using customer_id, you can divide the orders table into an arbitrary number of partitions, say 64, on a hash of customer_id.  This has the effect of reducing the overall table and index size when performing a query.  More of the rows in a particular block will be relevant, making read-ahead more effective. Partition-local B-trees will have fewer nodes, and therefore fewer blocks, and potentially even less depth.

To get back to the heart of your question, which if I understand it is "why do we have to do this at all?", the simple reality is that YOU always know more about your data than the RDBMS does.  This is the same reason query planners sometimes make mistakes.  Query planners can be supplied with better statistics, or given hints, but they're just not smart enough to wholesale restructure your data for you.  That is a high-level pattern recognition task that your brain is better suited to than any piece of code.  What's more, this kind of restructuring must be carefully considered and then implemented by hand.  I wouldn't even trust an RDBMS to automatically create indexes for me, much less rebuild the physical layout of a multi-terabyte table!

(One final note, PostgreSQL in particular only sorta supports partitioning. It's much easier to implement all of the above in an RDBMS like Oracle that supports it as a first-class construct.)


Can't restore mysqldump database backup in Ubuntu 12.04 I'm trying to restore a database backup created using mysqldump. If I do:

    $ mysqldump -u ROOT -p DATABASE_NAME < DATABASE.SQL

the database remains empty, even though I get some screen output and the line "Dump completed...", followed by a timestamp. I could not spot any error messages in the output

I made sure I quit MySQL before attempting the restore and I have tried to include the password in the command line, with no success. However, if I try:

    mysql>source /path_to_dump_file/DATABASE.SQL

it works just fine.

Any thoughts?

Many thanks in advance.

Try

mysql -u ROOT -p DATABASE_NAME < DATABASE.SQL

instead of

mysqldump -u ROOT -p DATABASE_NAME < DATABASE.SQL


Efficiently finding distinct values I have a number of tables with the primary key (month, year, number) and differing cardinalities differ somewhat. For the tuple (month, year) the history doesn't go back very far, this will probably not grow beyond 50 in the very long term. For every (month, year) tuple  there are not more than 2 million unique numbers. I want to know which combinations of month and years are available. I do this using this query:

select month, year from table group by month, year

This returns the correct result but does not seem to be very efficient. What is an efficient way to obtain this result (utilizing the unique index)?

The tuning advisor suggests to add an index on month-year for this query but this seems wasteful because a larger index is already available.

You may be able to use a variation of the following technique - which forces repeated 'MIN/MAX' range scans:

Assumptions

You can produce a list of all possible year/month combinations
number is not null (which it can't be as it is in the PK, but I mention it as there is a way of working around if nulls are permitted)

testbed:

create table foo(month, year, num, primary key(month, year, num)) as
with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, num
from m cross join
     (select level as num from dual connect by level<100000 order by dbms_random.random());

normal query:

select distinct month, year from foo;
--gets=11656

min/max technique:

with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, decode(( select min(num)
                             from foo
                             where month=m.month and year=m.year )
                           ,null, 'N', 'Y') as has_data_yn
from m;
--gets=294

Some explanation in response to comments:

In each case (the testbed and the min/max query), the subquery factoring clause just generated a list of (year, month) tuples:

with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select * from m;
/*
MONTH                  YEAR
---------------------- ----------------------
1                      2012
12                     2011
11                     2011
10                     2011
...
...
*/

Then the technique uses a subquery in the select clause to check if any rows are present for the (month, year) - this subquery necessarily must only produce at most 1 row:

select min(num)
from foo
where month=m.month and year=m.year;

This is very quick because it makes use of the ordered nature of the PK - however it needs to be executed once for each month - if there are millions of rows for each month that makes sense, but not if there are few enough to fit in a small number of block.


Informix JDBC login fails, local login succeeds I'm troubleshooting a "sudden" issue on our somewhat old Informix server (11.50.UC9GE). After years of running without issue (and connecting w/o issue), suddenly today we can't connect to the server remotely.

When I use dbaccess locally on the server and choose a connection method that prompts for username / password, the credentials work fine and I'm able to establish a connection. (note I can also intentionally enter invalid credentials and it will fail). This leads me to believe that the basic credentials I have are fine / unchanged.

However, when we try to connect to the server remotely (via a JDBC driver in DB Visualizer), we get this error:

An error occurred while establishing the connection:

Long Message:
Incorrect password or user com.informix.asf.IfxASFRemoteException: informix@ip-10-15-45-226.ec2.internal[MacBook-Pro.local] is not known on the database server.

Details:
   Type: java.sql.SQLException
   Error Code: -951
   SQL State: IX000

Up until today, this has always worked for us - any thoughts on what to check?

Ultimately an Informix restart solved the issue.


how to Import MySql Dump file in MySQLWorkBench I have a MySQL Dump file for few objects of a database and I have to import only one table from that file in MYSQL Work Bench.

My question is
How can I import a dump file in MySQL Work Bench for specified table or for all tables?

I guess you need to extract that particular table and then import in to the workbench.

sh mysqldumpsplitter.sh --source filename --extract TABLE --match_str table-name

This will extract your table in "out" folder and then you can import that to the workbench.

Source: MySQLDumpSplitter


Why are there different Database Collations for different languages? There are different database collations, I want to know the need for different database collations with respect to different languages.

(The following copies portions of the first part of my answer to a related DBA.StackExchange Q&A: Why not use SQL_Latin1_General_CI_AS for a global system? and adds some additional information.)

Collations in SQL Server, with respect to handling differences between various languages (meaning: this is not an all-inclusive list of what Collations do), handle several aspects of string data:

Locale / LCID (referring to the Culture: en-US, fr-FR, etc)

This is used to determine culture-specific overrides to the default linguistic sorting and comparison rules used by Unicode / NVARCHAR data across all Collations as well as non-Unicode / VARCHAR data for Windows (i.e. non-SQL_) Collations.

The Unicode Collation Algorithm (UCA) defines how to sort strings based on various rules and options (various sensitivities, which case sorts first, which characters are "ignorable", etc). There is a default sort weight given to each character and this is known as the Default Unicode Collation Element Table (DUCET). However, it is not possible to capture the sorting and comparison rules for all languages in a single set of rules because characters are shared between languages that have different rules for how to treat those same characters. So the LCID indicates which set of overrides, if any, to apply to the default set of rules. And those culture-specific overrides are described in the Common Locale Data Repository (CLDR). For example:

SELECT CHAR(105) AS [LowerCase], CHAR(73) AS [UpperCase],
       UPPER(CHAR(105) COLLATE Turkish_100_CI_AS) AS [TurkishUpperCase];

SELECT 1 WHERE CHAR(105) = CHAR(73) COLLATE Turkish_100_CI_AS;
SELECT 2 WHERE CHAR(105) = CHAR(73) COLLATE Hebrew_100_CI_AS;

SELECT 3 WHERE NCHAR(105) = NCHAR(73) COLLATE Turkish_100_CI_AS;
SELECT 4 WHERE NCHAR(105) = NCHAR(73) COLLATE Hebrew_100_CI_AS;

Returns:

LowerCase    UpperCase    TurkishUpperCase
i            I            İ

2
4

To see additional examples of how different languages behave against the same comparison, please see my StackOverflow Answer: What is the point of COLLATIONS for nvarchar (Unicode) columns?.

For non-Unicode / VARCHAR data using SQL Server Collations (those starting with SQL_), the sort orders should be what is described in the following MSDN pages: Appendix D Sort Order for Selected Languages.
Code Page

This is the character set used for non-Unicode / VARCHAR across all Collations. To be clear, Code Pages do not apply to Unicode / NVARCHAR data as Unicode is a single character set. And to be super clear, Unicode is a single character set regardless of how it is encoded: UTF-8, UTF-16, or UTF-32.

Non-Unicode / VARCHAR is 8-bit, but that does not mean that it is always 1 byte per character. There are four Double-Byte Character Sets (DBCS) supported by Windows & SQL Server to allow mapping more than 256 characters. Similar to how UTF-8 works, these Double-Byte Character Sets are variable-length and will use 1 byte for some characters and two bytes for others.

The Code Page is determined by the LCID (noted above), and is the only meaningful difference between the various binary Collations (across all deprecated _BIN Collations or separately across all _BIN2 Collations; there is another difference between the _BIN and BIN2 Collations, but it isn't language-specific). This is because a) Unicode is a single character set, and b) sorting is based on the numeric value of each character, and that does not vary between languages.

Example of different characters for the same numeric value between Code Pages:

Check the top-right element in each of the following grids:

Code Page 1254 Windows Latin 5 / Turkish
Code Page 1255 Windows Hebrew

-- 0xF0 == 240 == ğ (in Turkish, Code Page 1254) == נ (in Hebrew, Code Page 1255)
DECLARE @Table TABLE (Turkish VARCHAR(10) COLLATE Turkish_100_CI_AS,
                      Hebrew VARCHAR(10) COLLATE Hebrew_100_CI_AS);

INSERT INTO @Table (Turkish, Hebrew) VALUES (0xF0, 0xF0);
SELECT * FROM @Table;

Returns:

Turkish    Hebrew
ğ          נ

Sensitivity

Case and Accent sensitivity can be controlled across all Collations. Kana and Width sensitivity can only be controlled when using the Windows Collations and are assumed to be "insensitive" when using the SQL_ Collations.

Also, all of the Windows Collations should have a binary option (at least the deprecated _BIN, if not also _BIN2) whereas there are only two SQL_ Collations that have the _BIN / _BIN2 options: SQL_Latin1_General_CP850 and SQL_Latin1_General_CP437.

Also, there are some behavioral differences for non-Unicode / VARCHAR data only when using the SQL_ Collations:

When sorting data using a case-sensitive Collation (e.g. SQL_Latin1_General_CP1_CS_AS), upper-case characters will sort ahead of lower-case characters.
String-sort is used, which sorts all punctuation ahead of all letters, instead of Word-sort, which ignores dashes and apostrophes within words (e.g. a-f sorts before aa using String-sort, but after it when using Word-sort).
No culture-specific character expansions are done (e.g. 'æ' = 'ae'). These expansions are based on the Unicode rules and vary based on the LCID, and if you need them while using VARCHAR data, then you need to use Windows Collations (i.e. not starting with SQL_).

There is, however, behavioral consistency between NVARCHAR data using any collation and VARCHAR data using a Windows Collation.

Hence, ideally, the SQL_ Collations shouldn't be used given the above restrictions and differences, not to mention that they are also deprecated (and there are only 77 of them and 3810 Windows Collations as of SQL Server 2014). If anything, try to use the most recent version of a particular Collation (e.g. _100_), and if offered, use one ending in _SC.

Unfortunately, SQL_Latin1_General_CP1_CI_AS is the default when installing a new instance in the US (at least). But one should not willingly pick a SQL_ Collation for new development, especially when needing to deal with multiple cultures.

Please also note:
Collation is not just set at the Database-level, it is also set at the Instance-level:

The Instance default Collation controls:

the default Collation of the system databases: master, model, msdb, and tempdb (including Temporary Table names)
the default Collation when creating new Databases, unless overridden via the COLLATE clause
cursor variable names
GOTO labels
variable / parameter names
the default Collation for new columns (via CREATE TABLE or ALTER TABLE...ADD) in Temporary Tables (but not Table Variables), unless a) overidden via the COLLATE clause, or b) the current Database when the Temporary Table is created is a Contained Database.

The Database default Collation controls:

the default Collation for new string (CHAR / VARCHAR / NCHAR / NVARCHAR) columns (via CREATE TABLE or ALTER TABLE...ADD), unless overridden via the COLLATE clause. this default also applies to Table Variables, but not Temporary Tables which take their default Collation from tempdb's default Collation (unless the database is a Contained Database in which case the default is the same as the local / current database).
the Collation used for string literals and local variables (the variable data, not the variable name, which is governed by the Instance's default Collation)


Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB I am very new to database design. I am hoping for suggestion or solutions for a feature I am working.

Note: We are majorly using JSONB type in Postgres

Feature:
I have a orders table which would contain the every order information, every order has a unique items user ordered.

Sample Order Schema

{
    id: "parent_order_id_uuid",
    "items": [
        {
            item_id: "uuid-#1",
            details: {
                etc....
            }
        },
        {
            item_id: "uuid-#2",
            details: {
                etc....
            }
        }
    ]
}

I am working on a feature were users can initiate a replacement for the item he purchased for n-number of times.

For Eg - "uuid-#1" can be replaced separately and "uuid-#2" can be replaced separately or both "uuid-#1" & "uuid-#2" n-number of times.

so every time user replace an item I need to create a new order which would only contain the replaced item only.

Sample Replacement Order:

{
    id: "order_id_uuid",
    "items": [
        {
            item_id: "uuid-#1",
            details: {
                etc....
            }
        }
    ]
}

{
    id: "order_id_uuid",
    "items": [
        {
            item_id: "uuid-#2",
            details: {
                etc....
            }
        }
    ]
}

Requirements:

At any point in time, i need to able to create a tree-like structure based on the "parent_order_id_uuid".
At any point in time, I need to able to query the replaced order based on the "parent_order_id_uuid".
I need to have relationship between parent_order_id and replacement_orderIds + parent_item_id and replacement_ids + replacement_ids and replacement_ids

How to store this data in Postgres to get the all this requirement, Once again, I am okay with storing this data as JSONB. Some design suggestions will be helpful for me.

(Parent to Child + Child to Parent + Child to Child)

Simply to Put my requirements:

Parent to Child for N-Times

{
  "parent_order_id_uuid": [
    {
      "replacement_order_id_1": {
        "items": [
          {
            "parent_item_id": "uuid-#1",
            "item_id": "uuid-#3"
            "details": {
                etc...
            }
          }
        ]
      },
      "replacement_order_id_2": {
        "items": [
          {
            "parent_item_id": "uuid-#2",
            "item_id": "uuid-#4"
            "details": {
                etc...
            }
          }
        ]
      }
    }
  ]
}

If this is not very verbose, please let me know.

P.S: This is my first question in Database Stack.

Thanks in Advance for helping minds.

First, as always a_horse_with_no_name is right. Read this

As to how you can factor out your jsonb. You can use a single-table hierarchy.

CREATE TABLE items (
  item_id    serial  PRIMARY KEY
  parent_id  int     REFERENCES items,
  details    jsonb
);

At the very least with this scheme you've removed out the IDS. Now you just use a recursive query to query the data.

For more information look up

an example with id/parentid/query
[hierarchy] + [postgresql]


oracle database login information In a database there is a username called u1, someone tried to access u1 but due to wrong password it got locked. How to find who did this suspicious activity.

You can find information about connection in listener.log
Location is under $ORACLE_HOME/diag/ or $ORACLE_HOME/network/log


SQL Server Data Refresh I need to be able to restore around 6TB of data from one MS SQL 2008 instance into another SQL Instance. DBA's and backup folk are estimating between 36 and 48 hours for the restore. I can't afford this amount of down time on the target system.
Would it be possible to mount additional drives on the target SQL server, restore the data files to the new drives and then simply change the location of the data files in the DB once the restore is complete?
This way the only down time is getting the drives mounted and switching data file location.

Would it be possible to mount additional drives on the target SQL server, restore the data files to the new drives and then simply change the location of the data files in the DB once the restore is complete?

Yes, if the two databases on the target server have the same file layout.  A simpler and safer procedure is to restore the database with a different name, and then rename the databases to swap.
If you can avoid detaching a databse, setting it offline or shutting down SQL Server you have extra protection against losing data.


Can a AWS PostgresSQL RDS instance be provided with a stop words file? I need to provide a custom list of stop words. From the manual section on dictionaries the way to do this is to put a stopwords file in $SHAREDIR/tsearch_data/.

Is it possible to do this when using AWS? If no, can the stopwords file be provided over the command line instead?

PostgreSQL 10

It does just that, you can see the code here,

void
readstoplist(const char *fname, StopList *s, char *(*wordop) (const char *))
{
    char      **stop = NULL;

    s->len = 0;
    if (fname && *fname)
    {
        char       *filename = get_tsearch_config_filename(fname, "stop");
        tsearch_readline_state trst;
        char       *line;
        int         reallen = 0;

**stop could be modified easily to read from the yet-nonexistant parameters, but currently it just pulls from the file. A few lines above and you'll find get_tsearch_config_filename(fname,extension) which looks in just the place you were referring too,

snprintf(result, MAXPGPATH, "%s/tsearch_data/%s.%s",
         sharepath, basename, extension);

There is nothing else in the code base that writes to a StopList pointer. As of PostgreSQL 10, it's not possible.

Amazon RDS

There is an unresolved issue from 2014 on AWS Developer for this,

Amazon confirms we're right in saying there is no way

  Unfortunately, because RDS doesn't provide filesystem access, you won't be able to upload your custom files to $SHAREDIR.

And Amazon follows up with

  I have raised a feature request with the RDS team.


Should I load the data into Temp table or keep calling it from the normal table? I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two where clauses cut the data down to a few hundred rows in total.

I run the exact same select statement over a dozen times and the data results will always be the same, within the same run. Would it be better, performance-wise, to keep running the same select statement or should I do an insert into select to fill a temp table with only the data I need and pull from there instead?

Select statement ran (with some some IDs redacted):

SELECT WorkDay1, WorkDay1Brea, StartBreak, StrtTm
FROM dbo.DriverTimes
WHERE DriverTime.DrvrID = @DriverID
AND CONVERT(VARCHAR(12), dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND PryllID IN (
 ListOfIDs
);

This select is ran a total of 8 times for each Driver and there are 40 some odd drivers.

If the volume of data you're retrieving is large - roughly, millions of narrow rows, or hundreds of thousands of wide rows, or tens of thousands of rows with large objects - you should probably cache the data in a temp table and reuse that.  If you're performing significant processing to get the data - such as a table scan - you should probably cache it.  Aside from speed, I can think of a couple other reasons to dump the data to a temp table:

It minimizes the length of time the source table is locked (this can be very important).
It gives you the opportunity to rename fields or use more restrictive data types (such as NOT NULL constraints) that only apply to your data set.
It gives you a chance to add calculated fields or indexes which are not present in the original.

...and against:

It entails a little extra development work.
It requires additional space, in memory, on disk, or both.

That said, this may be premature optimization; how long does your sproc take to run?


SQLRemote: Implicit synch of connected objects I'm looking for the best way to synchronise foreign key relations with SQLRemote. So having e.g. this two tables

CREATE TABLE  table1(
    id integer not null default autoincrement,
    name varchar(40) NOT NULL,
    primary key (id)
);

CREATE TABLE table_items(
    id integer NOT NULL default autoincrement,
    name CHAR(40) NOT NULL,
    table_1_id integer NOT NULL,
    cl1 bit default 0,
    cl2 bit default 0,
    FOREIGN KEY ( table_1_id )
    REFERENCES table_1( id ),
 PRIMARY KEY (id));

Implementing maybe an invoice with invoice items. cl1 and cl2 shall trigger which client shall this dataset by synched with. But now I'm wondering, what's is the best way to synch also corresponding data of table_1.

A first approach would be from my mind to put also cl1 and cl2 fields into table_1 and add them to pubcliation so publication looks little like:

CREATE PUBLICATION sync_cl1 (
    TABLE table_1,
    TABLE table_items
    WHERE cl1 = 1
);

CREATE PUBLICATION sync_cl2 (
    TABLE table_1,
    TABLE table_items
    WHERE cl1 = 2
);

Is there any better way doing this maybe with integrated integrity check?

It appears that this can be done with subqueries inside publications.

From documentation:

CREATE PUBLICATION SalesRepData (
TABLE SalesReps
TABLE Customers
   SUBSCRIBE BY rep_key
TABLE Contacts
SUBSCRIBE BY (SELECT rep_key
   FROM Customers
   WHERE Contacts.cust_key = Customers.cust_key )
);

This would catch all customer contacts for sales rep's customer.

Quiet important seems to be, that this needs to be updated once the connection has been updated -- e.g. a sales rep is getting new customers. This needs to be done with an BEFORE trigger for UPDATE/INSERT/DELETE.


changing tablespace seems to waste space I have a large (several TBytes) Postgres database (v10 on windows) containing tables that are partitioned by date-range. Depending on volumes, some of them are partitioned by day, others by month.

I have two main tablespaces 'fastspace' on an SSD drive, and 'slowspace' on a traditional magnetic hard disk.

As data becomes older (and less relevant) I move the partitions from fastspace to slowspace using

ALTER TABLE..... SET TABLESPACE slowspace

I've started checking available disk space (at an OS level) before and after changing the tablespace, and have been surprised to notice that the destination drive (slowspace) is losing a lot more free space than the source drive (fastspace) is gaining.

A typical table might contain a 100 million rows of data for a month occupying several Gbytes of space. Changing the tablespace might free up x Gbytes of space from fastspace, whilst the destination tablespace slowspace will lose 2x Gbytes of space. (I'm moving both the table, and the indexes at the same time)

Why the difference, and what can be done to improve the situation?

Changing the tablespace is a relatively quick process (time measured in minutes) but doing another VACUUM after moving the partition to slowspace is very slow (time measured in hours). This is doubly frustrating because I do my best to ensure that performance intensive operations (like a VACUUM) are done on my fastest disk. To move the data to slower disk, and then be immediately forced to start a very slow operation to regain lost space is very annoying!.

Update in response to comments...
Not sure it's a block size issue. Both my discs have 512 bytes/sector, and the postgres page size is (I think) set per database, not per tablespace)

Both disks are NTFS. According to system information/components/Storage/Disks the 'fastspace' disk looks like this....

Description Disk drive
Manufacturer    (Standard disk drives)
Model   BIWIN SSD
Bytes/Sector    512
Media Loaded    Yes
Media Type  Fixed hard disk
Partitions  4
SCSI Bus    0
SCSI Logical Unit   0
SCSI Port   0
SCSI Target ID  0
Sectors/Track   63
Size    238.47 GB (256,052,966,400 bytes)
Total Cylinders 31,130
Total Sectors   500,103,450
Total Tracks    7,938,150
Tracks/Cylinder 255
Partition   Disk #0, Partition #0
Partition Size  260.00 MB (272,629,760 bytes)
Partition Starting Offset   1,048,576 bytes
Partition   Disk #0, Partition #1
Partition Size  236.67 GB (254,119,553,536 bytes)
Partition Starting Offset   407,896,064 bytes
Partition   Disk #0, Partition #2
Partition Size  960.00 MB (1,006,632,960 bytes)
Partition Starting Offset   254,528,192,512 bytes
Partition   Disk #0, Partition #3
Partition Size  500.00 MB (524,288,000 bytes)
Partition Starting Offset   255,535,874,048 bytes

And the 'slowspace' disk like this.....

Description Disk drive
Manufacturer    (Standard disk drives)
Model   TOSHIBA MQ01ABD100
Bytes/Sector    512
Media Loaded    Yes
Media Type  Fixed hard disk
Partitions  1
SCSI Bus    2
SCSI Logical Unit   0
SCSI Port   0
SCSI Target ID  0
Sectors/Track   63
Size    931.51 GB (1,000,202,273,280 bytes)
Total Cylinders 121,601
Total Sectors   1,953,520,065
Total Tracks    31,008,255
Tracks/Cylinder 255
Partition   Disk #1, Partition #0
Partition Size  931.39 GB (1,000,068,874,240 bytes)
Partition Starting Offset   135,266,304 bytes

I'm using a c# app to monitor disk space before/after the move using DriveInfo.AvailableFreeSpace immediately before and after using the ALTER TABLE ... SET TABLESPACE command.

The comment about file system block size may be relevant. Please note that it is about file system blocks, not hard disk blocks. This is normally referred as a cluster, in NTFS terminology. You may check it with command fsutil fsinfo ntfsinfo [your drive] on a cmd opened as Administrator.

You might also check the storage parameter, like fillfactor. I think (althought I am not sure) that moving a table to a new tablespace will not just copy tha table blocks, but will reorder them. If you copy a table where you never issued update or delete statements, the table on the new tablespace will be as the original one, but if you updated/deleted records, the the table on the new tablespace will be "compacted" and will fill all new blocks only until the fillfactor, leaving some space for future updates on every block. If the fillfactor is too large, this is a waste of disk space.


Solution for a readonly copy of a standalone MongoDB server I store some data in many databases/collections on a standalone MongoDB server. This server is used for production.

I need to access those data (or at least nightly build data, and not all databases/collections), for data analysis purpose : readonly, possibly high consumption queries. I don't want to operate data analysis on the production server.

For now, I use mongoexport and mongoimport to export and re-import the data into another standalone server. This has many drawbacks :

indexes are not kept,
night export/re-import is a long-time process that may fail,
data is not continuously synced (not absolutely necessary).

I studied the MongoDb replica set documentation, but some questions are still unanswered to me :

Can I choose which databases/collections are replicated ?
Is a 2-members (a primary and a secondary) replica set eligible ?
Can I make our production application servers "always" read-write on the primary, and our data-analysis server "always" read on the secondary ?

I know I have many questions and it might break Stackoverflow Q/A principles, but I hope the question label could be useful for many others.

Can I choose which databases/collections are replicated ?

A replica set is a group of mongodb instances that maintain the same data set, so it is necessary for the entire set of data to be replicated between nodes.

  Is a 2-members (a primary and a secondary) replica set eligible ?

A replica set should have an odd number of voting members. If you want to maintain only two data-bearing nodes, you would want to include an arbiter in the replica set as a third voting member.

  Can I make our production application servers "always" read-write on the primary, and our data-analysis server "always" read on the secondary ?

All writes must be directed to the Primary node of the replica set; clients are not allowed to write to a data bearing node in the 'Secondary' replica set state.

Specifying a read preference is a good way of directing queries to different nodes of a replica set.


MS SQL temporal tables not properly I have an existing table and wanted to create temporal tables. I used following queries, it worked in local and dev server. But in QA and PROD the same query created some anonymous temporal table not the one I wrote. What could be the issue?
ALTER TABLE dbo.abc
    ADD StartTime DATETIME2
GO
ALTER TABLE dbo.abc
    ADD EndTime DATETIME2
GO
UPDATE dbo.abc SET StartTime = '19000101 00:00:00.0000000', EndTime = '99991231 23:59:59.9999999'
GO
ALTER TABLE dbo.abc
    ALTER COLUMN StartTime DATETIME2 NOT NULL
GO
ALTER TABLE dbo.abc
    ALTER COLUMN EndTime DATETIME2 NOT NULL
GO
ALTER TABLE dbo.abc
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
GO
ALTER TABLE dbo.abc
    SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.abc_history, DATA_CONSISTENCY_CHECK = ON))
GO

So instead of creating system versioning for abc_history, it created MSSql_TemporalHistoryFor_<some-random-number>.
I see abc_history table in DB, it is just not system versioned.
select @@version gives "Microsoft SQL Azure (RTM) - 12.0.2000.8   Feb 20 2021 17:51:58   Copyright (C) 2019 Microsoft Corporation "

Do you have a PK in the abc table?
Try this=
ALTER TABLE dbo.abc
    ADD StartTime DATETIME2(2) GENERATED ALWAYS AS ROW START NOT NULL
GO

ALTER TABLE dbo.abc
    ADD EndTime DATETIME2(2) GENERATED ALWAYS AS ROW END NOT NULL
GO

ALTER TABLE dbo.abc
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
GO
ALTER TABLE dbo.abc
    SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.abc_history,     DATA_CONSISTENCY_CHECK = ON))
GO


Currently all connections getting "Timeout expired" I have an application which connects to SQL Server. Currently I am getting,

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

When I ran,

SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
     DB_NAME(dbid) ='MyDb'
GROUP BY
    dbid, loginame

DBName  NumberOfConnections LoginName
MyDb    10                     sa
MyDb    109                   MyUser

The status of all process is sleeping and cms is AWAITING COMMAND

Here is my code,

private async Task<object> ExecuteAsync<T>(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel, SqlParameter[] parameters, Func<IDataReader, T> callback = null)
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();
    using (var connection = new SqlConnection(_settings.DatabaseConnectionString))
    {
        using (var command = new SqlCommand(commandText, connection) {CommandType = commandType})
        {
            command.Parameters.AddRange(parameters);
            await connection.OpenAsync().ConfigureAwait(false);
            command.CommandTimeout = _settings.CommandTimeout;
            var transaction = connection.BeginTransaction(isolationLevel);
            command.Transaction = transaction;
            try
            {
                object result;
                switch (executionType)
                {
                    case ExecutionType.Reader:
                        var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
                        using (reader)
                        {
                            var list = new List<T>();
                            while (reader.Read())
                            {
                                if (callback != null)
                                {
                                    var item = callback(reader);
                                    if (item != null)
                                    {
                                        list.Add(item);
                                    }
                                }
                            }
                            result = list;
                        }
                        break;
                    case ExecutionType.NonQuery:
                        result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
                        break;
                    default:
                        result = await command.ExecuteScalarAsync().ConfigureAwait(false);
                        break;
                }
                transaction.Commit();
                stopwatch.Stop();
                var elapsed = stopwatch.Elapsed;
                if (elapsed.Seconds > 2)
                {
                    _logger.Log(string.Format("{0} took {1} time", command.CommandText, elapsed));// only log if it tooks more than 2 seconds
                }
                return result;
            }
            catch (Exception exception)
            {
                _logger.Log(exception);
                transaction.Rollback();
                throw;
            }
        }
    }
}

It looks like you have an application that isn't properly closing or disposing of the SqlConnection objects.  By default, SqlConnection has a max pool size of 100.

The fix here would be to work with the application to find out why connections aren't being cleaned up, as they are still "active" in the particular connection pool, which is why you are unable to grab another one, because there are no inactive connections in the pool to use.

The application should be making a call to SqlConnection.Close() or SqlConnection.Dispose() in order to release the connection and mark it as "inactive".

I wrote an extensive blog post (Connection Pooling for the SQL Server DBA) about connection pooling, and it should shed some light on why you are seeing what you are seeing, and also the programmatic fix to the issue.


DB: Having redundant fields in Table This is a basic DB design question but I now run multiple times into the same situation which I try to illustrate in the following example.

Lets say I have make a DB-system for messaging in a classifieds platform.

A user publishes many ads and other users can message her for a specific ad.

I thus have an ads table, a user table and a message table in my DB.

Since users message people for specific ads, storing the ad AND the user is redundant.

On the other hand, when listing all incoming messages for a given user it would be much more efficient to have the user name as a field in the message table too, because a user might have many ads and only very few of them have messages.

So should the message table now have a user field or not?

For proper relational design redundancy should be avoided - anything that can be derived from other data (looking up the user that relates to an advert in this case) should not be stored separately again. This helps enforce the integrity of your data, stopping bugs in your code allowing and with appropriate keys and indexes the performance should not be noticeably impacted.

As we are not in the perfect world assumed by theoretical models with perfect implementations of those models and AI to interpret any query we throw at the database, it is sometimes advantageous to de-normalise your data in the manner you describe for a performance gain - but best practise is to only do this once you have identified a specific performance problem rather than baking it into your design. Never de-normalise your data purely for conveniences sake - instead define your tables in normal form and create views to reduce query complexity in other layers of your code.

Of course if you intend to allow messages about an advert to go to other users, a complaint perhaps which would go to an admin initially and not the user who placed the advert, then you must include the destination user as a specific property as it can no longer be unambiguously derived from other data.


Database design for a time tracking application I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he worked for a specific project. What would be, in your opinion, the best way to store these values?

Have a look at the Timesheet model on databaseanswers.org. This covers it nicely

Simply, a table with

Date
User (FK to User table)
Project (FK to Project table)
Time worked

The first 3 columns are PK.

Now, do you want to:

log activity eg Analysis, Coding, testing separately?
log project phases separately?
log non-project work eg training, holidays (eg user focus or project focus?)
...


News Database Design for a Desktop Application I am making an application that will process a big chunk of information. This information was retrieved using some web crawlers and is about news, containing data such as News Title, URL, Publication Date, Category and Content. The crawled data is in XML format, and I will load that information into my application.

From there, 10 users will process every news and tag the category of the news manually by reading the title. I've used 9 main categories to be used, and for each news the users will decide from 0-5 how much this particular news belongs to each category.

Users will also search for news by title and decide if this news is discussing the same event as another news, or if this news is similar to another news (like a news about a football game at 3 PM and a news about a football game at 5 PM).

I have no problems with making the application itself, I just need some help with the part of how to design one/many tables that can link news that talk about similar events or about the same event, since there can be many many news that can talk about the same event.

So far I've done something like this:

Table News

ID
Title
URL
PublicationDate
NewsContent

Table Category

NewsID
User_ID
Economy
Politics
Present_Day
Sport
Technology
Showbiz
Culture
Region
World

Table User

ID
FirstName
LastName

Each category field in the Category table holds a tinyint data type (I'm using SQL Server) and I've restricted the values the user can input via a check constraint so that it allows values from 0 - 5. I don't know if this is the right approach so far for the database design, and I need to add the table/s that allow to add information about similar news or news that talk about the same event, such as a table Similar_News containing some fields like News_ID, SimilarNews1_ID, SimilarNews2_ID and so on, and must contain the information about which user 'said this', but this sounds like a flawed design to me.

Any help is appreciated, thank you.

Based on how dynamic this sounds like I would suggest looking at a NoSQL data structure. I do not use them so much myself, but looking into something like MongoDB might be worth it. Good luck on your application!


Adding non-nullable NEWSEQUENTIALID() column to existing table In SQL Server 2008, I am adding a non-nullable column to an existing table, and want to make the default NEWSEQUENTIALID().

ALTER TABLE MyTable
ADD MyColumn UNIQUEIDENTIFIER NULL
CONSTRAINT DF_MyTable_MyColumn DEFAULT NEWSEQUENTIALID()

UPDATE MyTable SET MyColumn = NEWID()

ALTER TABLE MyTable
ALTER COLUMN MyColumn UNIQUEIDENTIFIER NOT NULL

Is it safe to populate the column with NEWID() as shown above, or is there a better way to use a sequential ID value?

My concern is that NEWID() will generate a new guid that may be close to the sequential guid.  Then when the sequential guid hits that value from NEWID(), it will run into issues.

1) there is no need to add a NULL-able column, update, then alter it. It can be done in one pass using the WITH VALUES syntax (see http://sqlfiddle.com/#!3/5e1dea/2):

ALTER TABLE MyTable
ADD MyColumn UNIQUEIDENTIFIER NOT NULL
  DEFAULT NEWSEQUENTIALID()
WITH VALUES;

2) Use of NEWID() vs NEWSEQUENTIALID() is a moot point if you do it in one pass. But for the sake of the argument, lets see what happens. The use of sequential GUIDs is only relevant if you index the column. Because random GUIDs are so random, inserting new values results in frequent page splits. For a clustered index column this can be really problematic. But you are not updating an indexed column, so the point is irrelevant. Had you add an nonclustered index later, the index builder would first sort the column then build the index. Afterwards new inserts would find a spot in the table where the sequential GUIDs would happen to sort between the random GUIDs and the start growing the table from there. This would be basically a tail-page split (even though is not a tail-page) so it would still be OK.

Overall I think the use of WITH VALUES syntax (which is surprisingly little known) renders your question obsolete. I don't think you had to do the ALTER in three steps.


Change SQL Server 2008 name I want to change my SQL Server's name.

Currently I am connecting to SERVER1\SQLSERVER. SERVER1 is the machine name. I want to change it so I could connect to SERVER2\SQLSERVER and keep SERVER1 as the machine name.

I tried:

sp_dropserver 'SERVER1\SQLSERVER'
sp_addserver 'SERVER2\SQLSERVER', local

Then I restarted the server.

If I verify I get the new server name:

select @@servername

Looks like the name was changed but i cant connect to SERVER2\SQLSERVER while the SERVER1\SQLSERVER connection still works.

What am I missing here ... and is this even possible?

If you aren't changing the physical server's name you'll need to change the name that SQL Server knows back.

sp_dropserver 'SERVER2\SQLSERVER'
sp_addserver 'SERVER1\SQLSERVER', local

You then just need to put an alias in DNS so that when people try to connect to SERVER2 then are redirected to SERVER1 without knowing about it.  Specifically you'll want (or you'll want your sysadmin) to put a CNAME record into DNS pointing SERVER2 to SERVER1.


is restart necessary after a config change in mongodb? After a configuration change is it necessary to restart the db so that the new config take effect?

The configuration was changed via this command:

  db.getSiblingDB('admin').runCommand( { setParameter: 1, cursorTimeoutMillis: 300000000 } )

As per Jira ticket and MongoDB documentation, it does not mention of requiring a restart.

ISSUE SUMMARY

  Client cursors that are no longer used may not be closed by the
  server. These cursors continue to consume resources inside mongod. By
  default, inactive cursors expire after 10 minutes, this option makes
  it possible to configure this timeout.

RESOLUTION DETAILS

  By specifing the cursorTimeoutMillis option, administrators can
  configure mongod or mongos to automatically remove idle client cursors
  after a specified interval. The timeout applies to all cursors
  maintained on a mongod or mongos, may be specified when starting the
  mongod or mongos and may be modified at any time using the
  setParameter command. Consider the following examples.

cursorTimeoutMillis

  New in version 3.0.2.

  Default: 600000 (i.e. 10 minutes)

  Available for both mongod and mongos.

  Sets the expiration threshold in milliseconds for idle cursors before
  MongoDB removes them; i.e. MongoDB removes cursors that have been idle
  for the specified cursorTimeoutMillis.

  For example, the following sets the cursorTimeoutMillis to 300000
  milliseconds (i.e. 5 minutes).

But some users saw the value did not take effect till restart.

https://groups.google.com/forum/#!msg/mongodb-user/_nYxRY0iDr0/HeRpqRIIBwAJ


Azure SQL cross-database queries returns too many rows with joins I have a problem with Azure SQL cross-database queries, it looks like if we join data across databases the join is not distributed as input to the external database.

Currently I tried to implement a simple example and therefore I used this starting point:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-getting-started-vertical.

As preperation I created 199999 CustomerInformation entries and 200 OrderInformation entries.

Following query works as expected the CustomerInformation Table returns just one row:

SELECT o.OrderId, c.CustomerID, c.CustomerName
  FROM OrderInformation o
  JOIN CustomerInformation c ON o.CustomerID = c.CustomerID
 WHERE c.CustomerID = 2

But any query with a join that does not specifiy the CustomerID returns all the rows from the Customers Database.

SELECT o.OrderId, c.CustomerID, c.CustomerName
  FROM OrderInformation o
  JOIN CustomerInformation c ON o.CustomerID = c.CustomerID
 WHERE o.OrderId = 1870

Therefore the queries are really slow because the servers transfer to many rows. Do I miss some setting or is it not possible to separate this data with the suggested queries.

Thanks for your help.

Recreating the issue

Test data below

SELECT o.OrderId, c.CustomerID, c.CustomerName
FROM OrderInformation o
JOIN CustomerInformation c ON o.CustomerID = c.CustomerID
WHERE o.OrderId = 155;

All records are returned by the remote query operator, to filter down to one when the inner join is applied.

Some more background on pushing down the predicate to a remote database:

  Previously, elastic database query could not push parameterized
  operations to remote databases. As a result, sometimes large row sets
  had to be unnecessarily brought local to evaluate these operations.
  With the recent improvements, parameterized operations can now be
  pushed to remote databases and be evaluated remotely. For a query over
  an external table and a local table like the following, this can now
  avoid transferring millions of rows by evaluating the selective filter
  in the WHERE clause on the remote database:

DECLARE @low int
DECLARE @high int
SET @low = 100
SET @high = 200

SELECT c.CustomerId, c.Name, count(OrderId)
FROM remote_customers c
JOIN local_orders o
ON c.CustomerId = o.CustomerId
WHERE c.CustomerId > @low and c.CustomerId < @high
GROUP BY c.CustomerId, c.Name;

Source

Above query shows an example of using parameters to define a range from where to read the data. The difference with your problem is that the filtering is happening on the local table.

If we know that this predicate can be pushed down to the remote table

SELECT o.OrderId,c.CustomerID, c.CustomerName
FROM CustomerInformation c
JOIN OrderInformation o on o.CustomerId = c.CustomerID
WHERE o.customerID > 1 and o.Customerid < 10;

You could look for a range of customerid's and filter on these

MAX() and MIN() functions on o.Customerid are  used to get the upper and lower boundaries for WHERE o.OrderId = 155

DECLARE @MAX INT, @MIN INT;

SELECT @MAX= max(o.Customerid) , @MIN = MIN(o.Customerid)
FROM OrderInformation o
WHERE o.OrderId = 155;

SELECT o.OrderId,c.CustomerID, c.CustomerName
FROM CustomerInformation c
WHERE c.customerID >= @MIN and c.Customerid <= @MAX;

The complete result

 DECLARE @MAX INT, @MIN INT;

 SELECT @MAX= max(o.Customerid) , @MIN = MIN(o.Customerid)
 FROM
 OrderInformation o
 WHERE o.OrderId = 155;

 ;WITH CTE as(
 SELECT c.CustomerID, c.CustomerName
 FROM CustomerInformation c
 WHERE c.customerID >= @MIN
 AND c.Customerid <= @MAX
 )

 SELECT o.OrderId, c.CustomerID, c.CustomerName
 FROM CTE c
 INNER JOIN  OrderInformation o
 on o.customerID = c.CustomerID
 where o.Orderid = 155;

Pushing down the predicates:

Execution plan of this query

RemoteQuery="SELECT [c].[CustomerID] [Col1005],[c].[CustomerName] [Col1007] FROM [CustomerInformation] [c] WHERE [c].[CustomerID]&gt;=? AND [c].[CustomerID]&lt;=?"

It depends on your data & parameters passed how much this could filter out on the remote table. YMMV

Test data

Customerinformation (remote db)

CREATE TABLE dbo.CustomerInformation (CustomerID int identity(1,1) , CustomerName nvarchar(255));

DECLARE @i int = 1;
WHILE @i < 20000
BEGIN
INSERT INTO CustomerInformation(CustomerName)
VALUES ('Frodo');

SET @i += 1;
END

On the database that will use the external table

CREATE TABLE OrderInformation(OrderId Int identity(1,1), CustomerId int,Ordervalue int);

SET NOCOUNT ON;
DECLARE @i int = 1;
WHILE @i < 200
BEGIN
INSERT INTO OrderInformation(CustomerId,Ordervalue)
Values (@I,111);

set @i += 1;
END

CREATE EXTERNAL TABLE  [dbo].CustomerInformation(
    CustomerID [int],
CustomerName nvarchar(255))
    WITH
(
    DATA_SOURCE = Stackoverflow
);


Very weird Innodb Corruption Issue I'll try to keep this to the point.

System:
OPenVZ VPS, debian 6, 512 MB RAM, 50 GB HDD (25GB free)

I have a mysqldump dump file (3GB), that was created when the innodb database was working fine.

After purging, removing  mysql and any related files/folders mariadb was installed (Actually this process has been repeated several times).
Next the dump file created earlier was imported (mysql -u user -p < sql.sql)

Immediately after importing the dump, I tried to take a backup using xtrabackup, it complained of corruption and crashed.

Output of check table tableName.

+------------------------------+-------+----------+---------------------------------------------------+
| Table                        | Op    | Msg_type | Msg_text                                          |
+------------------------------+-------+----------+---------------------------------------------------+
| db.table                     | check | Warning  | InnoDB: Index "product_id" is marked as corrupted |
| db.table                     | check | error    | Corrupt                                           |
+------------------------------+-------+----------+---------------------------------------------------+

Now comes the weird part. In The mysql-error log the page, the ascii page dump has strange sentences that should not be part of my VPS let alone the database or the table :

Part of the InnoDB: Page dump in ascii and hex (16384 bytes):

A fun three-picture set of the Okavango Delta in four sizes each  Okavango Jigsaw publish                                                      open

                       3@ me      < l                Z  N    Z  N       Cute Puppies is a slider with 6 different pictures of super cute puppies. 3 pictures are easy, and 3 puppy pictures are a bit harder. Have fun!  Cute Puppies publish                                                      open

The table in question has 3 columns - INT | INT | VARCHAR(50)

A cat trans.sql | grep  "super cute puppies" confirmed that the unexpected sentences were not in my DB dump.

Anybody knows why my table is corrupt ?  I have even tried to do a import from an older backup with similar results. And what about those strange sentences in the page dump ??

Lastly this all began after my VPS went offline for a couple of days (SSH brute force attacks, migration to new datacenter)

Thanks

Edit:
Before: Mysql 5.5 | After: 5.5.42-MariaDB

I thought Repair table was not for innodb..

btw can there be a unconfigurable limit to a mysql table size for the VPS ?

Edit: The unexpected sentences were part of a website that is also hosted by the same host. Found this after googling. Not sure what it means.

I've been having the exact same issues on my OpenVZ VPS's recently. Both running Debian 7 (official OpenVZ template), both with MySQL 5.6.23 from the Dotdeb repository. Random InnoDB corrupted tables, with the dump in the error log showing strange data that is definitely not part of any of my own databases, nor any files stored in my container.

Both started only recently (within the past month or so), one server was running fine for over a year prior, the other was a brand new VPS with fresh install of Debian.

My KVM VPS's running the exact same configurations have been fine thus far.

I'm sorry I can't offer a solution, but now I at least know that this is most likely not something that I caused. It has been extremely frustrating trying to figure out what is happening.

I'll let my host know that you have been having the exact same problems. Perhaps with this info they can figure out what the heck is going on. If they and I can get to the bottom of this I'll definitely let you know.


Optimize query between two MySql tables I currently have a query that works on two tables of expense and income. This is an example of what the structure of both tables looks like since they have the same columns:

|       id|        date |   amount|
|---------|-------------|---------|
|        1|  2019-02-02 |     2500|
|        2|  2019-03-16 |    4000 |
|        3|  2019-04-02 |    5430 |

and this is the query I currently have:

SELECT
    t1.month,
    COALESCE(t2.amount, 0) AS expenses,
    COALESCE(t3.amount, 0) AS incomes
FROM
(
    SELECT 1 AS month UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12
) t1
LEFT JOIN
(
    SELECT MONTH(date) AS month, SUM(amount) AS amount
    FROM expenses
    GROUP BY MONTH(date)
) t2
    ON t1.month = t2.month
LEFT JOIN
(
    SELECT MONTH(date) AS month, SUM(amount) AS amount
    FROM incomes
    GROUP BY MONTH(date)
) t3
    ON t1.month = t3.month
ORDER BY
    t1.month;

Here is the thread to see in detail and perform tests:http://sqlfiddle.com/#!9/466bd69/1

The query returns the total amount of both tables for each month of the current year, this works well. However, when reviewing the execution plan since I have many records, it takes too long to go through all the records of both tables, so how can I optimize it? Try adding an index to the date field without any improvement. Should I restructure my query?

Hi Max and welcome to DBA.SE.

  "when reviewing the execution plan since I have many records, it takes
  too long to go through all the records of both tables, so how can I
  optimize it?"

Are you having a real performance issue or are you assuming you are going to have one? If your query needs the data from all rows, there is no magic trick that MySQL can do and not scan each and every one. If this is a real production challenge, we will need to see your execution plans for the real data. If it's not, I wouldn't worry about it too much, but there are several things you can do to help the optimizer and allow more efficient access methods.

Instead of the derived table for the months, I would create a permanent table for months with just 12 rows. probably the month number and its name and index both. This will make the query more readable, and might change the optimizer choice when the set is indexed. you will most likely use it in other places as well. I also like to create full calendar tables in my databases, see for example this script. It's a SQL Server T-SQL script, so you may need to adjust it slightly to work in MySQL.
The optimal index for your query is on (date, amount). The latter key is needed to cover the query so the optimizer can get all it needs directly from the index without performing lookups.
If you can upgrade to MySQL 8, it offers functional indexes where you can create an index on (MONTH(date), amount) directly, which will speed up this query, but may hurt modification performance like any other index.

HTH


SQL query - Do I need a full join to produce this combined result? With two tables structured like this:

Table1                       Table2
----------------             ----------------
id, time, x                  id, time, y, z
1   1     1                  1   2     2  2
1   2     2                  1   3     3  3

I am trying to join the tables to get a result that looks like this:

Result
--------------------------------
id, time, x, y, z
1   1     1
1   2     2  2  2
1   3        3  3

I was able to get a partial result with a query like this, but it is returning all of the values from table2 (including undesired ids) along with the joined values of table1.

select *
from Table1
full outer join Table2 on Table1.time=Table2.time and Table1.id ='1' and Table2.id ='1'
order by Table2.time

Does anyone know how to fix? I'm trying to run this query in AWS Athena/Prestodb if that matters.

Thanks for the help!

I don't use amazon-presto, but I'm wondering if you could adapt this SQL Server solution to your needs.

--demo setup
drop table if exists table1;
drop table if exists table2;
CREATE TABLE Table1
    (id int, time int, x int)
;

INSERT INTO Table1
    (id, time, x)
VALUES
    (1, 1, 1),
    (1, 2, 2)
;

CREATE TABLE Table2
    (id int, time int, y int, z int)
;

INSERT INTO Table2
    (id, time, y, z)
VALUES
    (1, 2, 2, 2),
    (1, 3, 3, 3)
;

------------------
--solution
;with cte as
(
select id, time from Table1
union
select id, time from Table2
)
SELECT c.id, c.TIME, t1.x, t2.y, t2.z
FROM cte c
LEFT JOIN table1 t1
    ON t1.id = c.id
        AND t1.TIME = c.TIME
LEFT JOIN table2 t2
    ON t2.id = c.id
        AND t2.TIME = c.TIME;

| id | TIME | x    | y    | z    |
|----|------|------|------|------|
| 1  | 1    | 1    | NULL | NULL |
| 1  | 2    | 2    | 2    | 2    |
| 1  | 3    | NULL | 3    | 3    |


Using GROUPing or ROLLUP rather than SUMing every column I am currently looking for a way to achieve what this query is doing without using SUM for every column and, if possible, GROUPing or using ROLLUP or something similar.

This query gives me a total for orders across all currencies.

SELECT DATE, TYPE, SUM(orders), SUM(qty),
SUM(shipped_orders), SUM(shipped_qty), SUM(return_qty),
SUM(shipped_minus_returns_units)
    FROM daily_report
    WHERE `date` >= '2015-07-01' AND `date` <= '2015-07-29'
GROUP BY DATE ASC, TYPE ASC;

My current query looks like this:

SELECT DATE, currency, TYPE, orders, qty, shipped_orders, shipped_qty, return_qty,
shipped_minus_returns_units
    FROM daily_report
    WHERE `date` >= '2015-07-01' AND `date` <= '2015-07-29'
GROUP BY DATE ASC, TYPE ASC, currency ASC;

which returns this DB table view:

CREATE TABLE `daily_report` (
  `date` date NOT NULL,
  `currency` varchar(5) NOT NULL,
  `type` enum('customer','exchanges') NOT NULL,
  `orders` int(11) NOT NULL DEFAULT '0',
  `qty` int(11) NOT NULL DEFAULT '0',
  `value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `value_ex_ship` decimal(9,2) NOT NULL DEFAULT '0.00',
  `average_order_value` decimal(7,2) NOT NULL,
  `average_item_value` decimal(7,2) NOT NULL,
  `average_item_per_order` decimal(7,2) NOT NULL,
  `shipped_orders` int(11) NOT NULL DEFAULT '0',
  `shipped_qty` int(11) NOT NULL DEFAULT '0',
  `shipped_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `net_shipped_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `return_qty` int(11) NOT NULL,
  `return_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `returns_to_shipped` decimal(9,2) NOT NULL DEFAULT '0.00',
  `shipped_minus_returns_units` int(11) NOT NULL DEFAULT '0',
  `shipped_minus_returns_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`date`,`currency`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Is there a way to total up these columns using group rather than SUM'ing everything?

If I understand correctly, the ideal solution would need GROUP BY GROUPING SETS which is not available in MySQL.

Your two options are to use

WITH ROLLUP:

SELECT date, type, currency,
       SUM(orders) AS orders, SUM(qty) AS qty,
       SUM(shipped_orders) AS shipped_orders,
       SUM(shipped_qty) AS shipped_qty,
       SUM(return_qty) AS return_qty,
       SUM(shipped_minus_returns_units) AS shipped_minus_returns_units
FROM daily_report
WHERE date >= '2015-07-01' AND date <= '2015-07-29'
GROUP BY date, type, currency
  WITH ROLLUP ;

two queries, one simple and one with the aggregations needed and UNION ALL:

SELECT date, type, currency,
       orders, qty, shipped_orders,
       shipped_qty, return_qty, shipped_minus_returns_units
FROM daily_report
WHERE date >= '2015-07-01' AND date <= '2015-07-29'

UNION ALL

SELECT date, type, NULL,
       SUM(orders), SUM(qty), SUM(shipped_orders),
       SUM(shipped_qty), SUM(return_qty), SUM(shipped_minus_returns_units)
FROM daily_report
WHERE date >= '2015-07-01' AND date <= '2015-07-29'
GROUP BY date, type

ORDER BY date, type, currency ;

The first option will have more groupings than you need (per date only and a single total in the end).


Why can't I drop this Oracle datafile - even though it seems empty? This system is Oracle 11.2.0.3.0, using ASM.  We had an issue where there seemed to be a corruption issue of some kind - every time a row tried to get inserted into the table, we'd get the following:

ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 23: '<filepath>'
ORA-15081: failed to submit an I/O operation to a disk

Luckily, this is a test system and there happened to be only one table on data file 23, according to dba_extents.  I had the team drop the table and recreate it in another tablespace for the moment.

However, now I'd like to drop the datafile that was reporting the issue.
select count(*) from Dba_Extents where file_id = 23; reports 0 - so there seem to be no extents using that data file.  However, ALTER TABLESPACE tablespaceName DROP DATAFILE '<filepath>'; doesn't work.  I get the following:

Error report -
SQL Error: ORA-03262: the file is non-empty
03262. 00000 -  "the file is non-empty"
*Cause:    Trying to drop a non-empty datafile
*Action:   Cannot drop a non empty datafile

How can I drop this file?

Oracle here warn you from dropping datafile because it contain amount of data, to make sure the datafile is completely  empty please execute the following command

col tablespace_name format A30 heading tablespace
col file_name format A40
col tot_KB format 99,999,999
col tot_freeKB format  9,999,999
col Kb_max format  9,999,999

select f.tablespace_name
      ,f.file_name
      ,f.bytes/1024 tot_KB
      ,sum(nvl(s.bytes,0)/1024) tot_freeKB
      ,max(nvl(s.bytes,0)/1024) maxextent_freeKB
      ,( (sum(nvl(s.bytes,0)/1024)) / (f.bytes/1024) )*100 Pct_available
from   sys.dba_data_files f
      ,sys.dba_free_space s
where  f.file_id = s.file_id(+)
group by f.tablespace_name, f.file_name, f.bytes/1024
order by 1,2
;

if the datafile is completely empty, then you have one of the two option:

1) reboot the database ( sometime it help)

2) move all the data from one tablespace to another one, then drop the tablespace and rename the new one


Should I add a clustered Index on a foreign key that is not unique? I have access to a table that has a primary key unique clustered index on an nvarchar(36).
I need to create another table that hangs our own data off that unique clustered index in another table and use the field from the table above as the foreign key reference (these 2 fields will be the fields that JOIN the 2 tables). In this table we keep a record of the data as it changes, so we get repeated rows for the FK reference where the previous row has been end dated.
The question I have is would it be best to put the clustered key on the foreign key (that’s the field we’ll always join on) or is that bad practice because it’s not unique and also the order that rows would get added may not be sequential?
If uniqueness is an issue would you consider it a good approach to use the foreign key and the default start date column to make it unique?
Would no clustered index but a non clustered index (filtered for NULL end dates) be the best approach, problem is – there are a lot of columns in the FK table and I’d need to include them all in the NC index that might take it over the limit.
Further info as requested:
We're moving from one system supplier to another, but locally we hang extra data off the suppliers systems, The tables are as follows:
Main table that holds the ExternalID as the PK clustered index, when we go live this will hold 1.4 million rows
CREATE TABLE [dbo].[Inpatient_Activity](
    [RowID] [nvarchar](50) NOT NULL,
    [ExternalID] [nvarchar](36) NOT NULL,
    [Spell_ExternalID] [nvarchar](36) NULL,
    [Activity_Type] [nvarchar](128) NOT NULL,
    [Accepted] [bit] NULL,
    [Ward] [nvarchar](128) NOT NULL,
    [Bed] [nvarchar](50) NULL,
    [Activity_Start] [datetime] NOT NULL,
    [Activity_End] [datetime] NULL,
    [LOA_Outcome] [nvarchar](128) NOT NULL,
    [Reason_Leave_Was_Ended] [nvarchar](128) NOT NULL,
    [Expected_End] [datetime] NULL,
    [Home_Leave_Reason] [nvarchar](128) NOT NULL,
    [Discharge_Delay_Reason] [nvarchar](128) NOT NULL,
    [Updated] [datetime] NOT NULL,
    [DATVER] [int] NOT NULL,
 CONSTRAINT [PK_ipActivity] PRIMARY KEY CLUSTERED
(
    [ExternalID] ASC
)
) ON [PRIMARY]

As we're moving systems I need to make the table below point at the new one, the old one had an id int identifier but that now seems a bit pointless hence I'm asking the question above.
AT the moment the table below has 174793 rows, but as we move forward every row in the top table will get at least one row in this table of not 2, 3 or 20 rows.
[SERVICE_POINT_STAYS_PATIENT_STATUS](
    [ID that will link to the table above] nvarchar(36),
    [ESTIMATED_DISCHARGE_DATE] [datetime] NULL,
    [REASON_ID] [int] NULL,
    [START_DTTM] [datetime] NOT NULL,
    [END_DTTM] [datetime] NULL,
    [USER_NAME] [varchar](50) NULL,
    [STATUS_ID] [int] NULL,
    [SPECT_REFNO] [numeric](10, 0) NULL,
    [REFERRED_WARD] [numeric](10, 0) NULL,
    [REFERRED_WARD_TIME] [datetime] NULL,
    [DISCHARGE_DESTINATION] [varchar](500) NULL,
    [EDD_VERIFIED] [char](1) NULL,
    [EDD_START_DTTM] [datetime] NULL,
    [DISCHARGE_TODAY] [varchar](150) NULL,
    [DISCHARGE_TODAY_DTTM] [datetime] NULL,
    [REFER_SPECT_DTTM] [datetime] NULL,
    [DECISION_TO_ADMIT_DTTM] [datetime] NULL,
    [DECISION_TO_ADMIT] [varchar](10) NULL,
    [REFER_SPECT_REFNO] [numeric](10, 0) NULL,
    [EDD_CHANGED_REASON_ID] [int] NULL,
    [MODIF_DTTM_TRIGGER] [datetime] NULL,
    [GRAVIDA] [varchar](50) NULL,
    [BABY_WEIGHT] [varchar](50) NULL,
    [GESTATION] [varchar](50) NULL,
    [ANTE_POST_NATAL] [varchar](50) NULL,
    [POST_OP_WARD_SPONT_REFNO] [numeric](10, 0) NULL,
    [THEATRE_STATUS_ID] [int] NULL,
    [THEATRE_STATUS_DTTM] [datetime] NULL

It would be a great design approach to put the  clustered index on your FK if you will be fetching all the lines (I mean all the data of the line) for a given FK, or making joins based on that key : as they're stored physically in this order, they will come up much more quickly. Otherwise you could use a non-clustered index (slower fetching of the actual rows' data).

You may encounter a slower insert performance though.

Values can be multiple in a CI, they are also very efficient to quickly fetch a single, uniquely identified, row's data (Is it the reason why MS puts the clustered index on the PK by default ?).

I would recommend to make some tests using both approaches with a subset of your data, and check the estimated execution plans using CTRL+L, then include the EP via CTRL+M before you execute the test statement. This way you will see wich indexes are being used and how the engine modifies its strategy depending on what you are querying.

I generally avoid includig dates for unicity beacause on multiple insert statements the date for all inserted records is the same... I prefer using the default value of the uniqueidentifier type : myCol uniqueidentifier NOT NULL DEFAULT(NEWID()) which ensures uniqueness among all.

If you need all the columns' data of several rows with the same foreign key I would use the clustered index on non unique values. You may however check the execution plans.


SP runs slower on new server We migrated our databases from SQLServer 2012 to SQLServer 2019. Our ETL's are build in Visual Studio and are set up from a master package. The masterpackage calls on different packages, which are not deployed in SSIS. One of the packages calls a stored procedure. This stored procedure call on different stored procedures. On the old server, this SP step took 4 hours. On the new server, this step takes 7 hours. What could we do to speed up this proces? Does the compatibility level of the database affect this proces? And would it help if we deploy the package in SSIS? We are open for any suggestions.
Things we already tried:

Rebuilding indexes and updating statistics
Improving certain queries
Creating 8 files instead of 1 in the tempdb (old sever has one)

Thank you for your help.
Esmee

The big issue when upgrading across SQL Server 2014, meaning, going from anything less than 2014 to anything 2014 or greater, is the fact that the 2014 release of SQL Server included a new Cardinality Estimation Engine.
For the majority of queries, the new CE won't affect them in any way. Some queries will run faster. A few queries, usually queries that were already problematic edge cases, will indeed run radically slower.
The best way to deal with this would have been to use Query Store as part of your upgrade, as I outline in this recent blog post. You could, possibly, still do this. Change the compatibility mode of the database back to the old version, then follow the steps outlined. However, that's predicated on the fact that you haven't already started using new coding mechanisms in the database that are 2019 only.
Otherwise, you'll need to look first at the execution plans for the queries involved. Compare the plans from 2012 with the plans from 2019. See where the issues lie. It's likely that what you really need are updates to the code and underlying data structures. However, many can't, or won't, do that work. Instead, read through the details of the CE in this Microsoft article. There, you can see the options available to you from leaving the compatibility mode at the old setting, to changing the CE through Database Scoped Configuration changes, to query hints, and finally, back around to the Query Store.
One of these solutions will help. However, the best one, is to look at the code & structures and adjust them to be more optimal.


MySQL slave replication permission error I am having a rather frustrating issue. I have granted replication client to a user on the slave instance of MySQL. I can login using the username and password. I can exectute "SHOW SLAVE STATUS;" but I cannot start or stop the slave thread. I get the error below when i issue "STOP SLAVE" or "START SLAVE".

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Any ideas on how to resolve this? Thanks.

you may need to grant super privileges to the user. Replication client is responsible only to replicate binary logs to slave


Master-Master Replication: Missing PK Values I have configured master-master replication in mysql 5.7.22 and its working fine. I am using below settings for PK values. I found issue in their ID sequence and pk values missing their sequence values

1
2
3
5
6
8

its missing 4 & 7 because its replicate latest auto increment value from one server to another server so its skip the numbers. Is there any workaround through which we can maintain this sequence

Server A:
auto-increment-increment=2
auto-increment-offset = 1

Server B:
auto-increment-increment=2
auto-increment-offset = 2

Aside from @mustaccio's link, in general, mysql will guarantee auto_increments will be unique aand monotonically increasing, but for several reasons it cannot guarantee there is no gaps.

One reason is distributelly generated ones like in your case, but it can also happen with 2 concurrent write threads on the same server- so your applications should always avoid assuming anything about concrete id growth. Use last_insert_id() and ignore gaps.


Conversion failed when converting the nvarchar value 'value' to int I have the following code to pull a number of values from several rows into a single column and pipe-delimit the values.

SELECT DISTINCT P2.PatientID,
SUBSTRING(
    (
        SELECT '| '+ CAST(P1.ProcedureCode AS NVARCHAR(MAX))  AS [text()]
        FROM #Proc50 P1
        WHERE P1.PatientID = P2.PatientID
        ORDER BY P1.PatientID
        FOR XML PATH ('')
    ), 2, 1000) [ProcedureCodes]
INTO #Proc_Concat
FROM #Proc50 P2

I then have a dataset that looks like this:

PatientId   ProcedureCodes

1111111      11100| 17271| 1CHKS| 1CHN1| 1CHNS| 36415| 51798| 69210| 70450| 70544

When I try to insert the values into the final table I receive the conversion error.  I tried casting the value but that hasn't helped.  I also created a table for insertion where the field was nvarchar.

I am using SQL Server 2018.  Any help would be appreciated.

EDIT to answer questions:
Here is the definition of #Proc50

Both P1.PatientId and P2.PatientId are int

For the code that I am using to insert the results of the pipe-delimited procedure, I am creating a table for the insertion.

    CREATE TABLE dbo.tmpWellMedTest (
                    PatientId int not null
                    ,Lastname  varchar(30) not null
                    ,Firstname  varchar(30) not null
                    ,ProcedureCodes nvarchar(MAX) null
                    ,Diagnosis1 varchar(10) null
                    ,Diagnosis2 varchar(10) null
                    ,Diagnosis3 varchar(10) null)

Then the insertion script:

SELECT
 ME.PatientId
,ME.LastName
,ME.FirstName
,P.ProcedureCodes
,ISNULL(ME.Diagnosis1,'')                AS Diagnosis1
,ISNULL(ME.Diagnosis2,'')                AS Diagnosis2
,ISNULL(ME.Diagnosis3,'')               AS Diagnosis3
FROM vwPatientDemographic ME
LEFT OUTER JOIN #Proc_Concat        P
    ON P.LbPatientId = P.ProcedureCodes

Since #Proc_Concat and #Proc50 only have two columns in play here, PatientID and ProcedureCodes, one of them is throwing the error.

Guessing not ProcedureCodes as it's pipe-delimeted so must be a varchar.

That leaves PatientId.  Numeric data types can't handle the space character '1111111 11100', so that's the first place I'd look.

Another possibility is within your subquery, P1.PatientID = P2.LbPatientID, in case one is a varchar and one is an int, and implicit conversion won't allow the varchar value to be converted to an int to test the equality.

Good luck.


Find the Sharepoint application server based on Sharepoint Databases I'm doing some cleanup in my SQL Server farm and I found on one of our SQL servers (2012) several databases that I believe belong to a Sharepoint server.

No one knows in the company what is the SP that is linked to it.

I'm don't have much experience with Sharepoint.

Can you please help me with some direct queries in the Sharepoint database - Where can I find a URL for the SP ? Or Where is the Sharepoint Application server name (It is not on the same box as the database server).

I want to gather information before I'll recommend to delete those databases.

Thanks in advance,

Roni.

Look at the sessions on that database:

SELECT host_name
FROM sys.dm_exec_sessions
WHERE database_id = db_id('yourSharepointDatabaseName');

If something is using the database, the client host name will show up here.


Database design: User table and relationships I'm designing a DB for a small school project, but currently struggling with the design of the User table and it's related tables.

Basically:

A User can be a Student, a Teacher or an Admin.
Students, Teachers and Admins share some common data (name, last_name, login dates) but each type has unique information (Teachers have a professional license; Students have one Carrer associated to their profile, and so on).

So I'm stuck with two designs. The first one is as it follows:

This aproach allows me to have the unique fields (not listed in the image) in each type table, but the problem lies with the field duplication.

The second approach would be to have a single User table and a second User_data table (instead of the three separated tables in the first design), but I think that would create another set of problems, like having NULL values if the current register is a Student, Teacher or Admin.

Any suggestions on how to improve this design?

Here is one idea that I believe is pretty common:

CREATE TABLE users
( user_id ... not null PRIMARY KEY
, user_name ... not null UNIQUE
, surname ... not null
, ...
, user_type ... not null
,     CHECK (user_type IN ('T','S','A'))
,     UNIQUE (user_type, user_id)
);

CREATE TABLE teachers
( user_id ... not null PRIMARY KEY
, license --- not null UNIQUE
, ...
, user_type ... not null
,     CHECK (user_type = 'T')
, FOREIGN KEY (user_type, user_id)
      REFERENCES users (user_type, user_id)
);

Similar tables for STUDENTS and ADMINISTRATORS.

To determine the name of a teacher, you join with users

CHECK constraints may not be availible in your version of MySQL, you can achieve the same thing with foreign keys to a domain table

All of the above assumes that - for example - teachers and students are disjoint.