MYSQL - Error in After Insert trigger affects the actual insert on the trigger table I have two tables call it - table_1 and table_2

I have an After insert trigger on table_1 and in the trigger I insert on table_2 which has some fk constraints on another table_3.

When the insert on table_2 fails, the table_1 insert fails.

Is this normal? and Is there a workaround for this?

Thanks

The entire trigger chain constitutes one atomic operation inside a transaction -- that's "A" in ACID. So yes, this is working as designed.

If atomicity is not important to you, that is, if you are fine with the fact that table_1 is updated but the insert into table_2 is lost, you need to introduce error handling in your trigger, something along these lines:

create trigger table_1_trig1 after insert on table_1
for each row
begin
 -- no-op handler that just continues;
 -- sqlstate 23000 is the foreign key violation
 declare continue handler for sqlstate '23000' begin end;
 insert into table_2 ...;
end

dbfiddle link

The condition for sqlstate '23000' is the SQL standard equivalent of the MySQL-specific for 1452. If you want to ignore all errors when inserting into table_2 just use for sqlexception instead.

So yes, there is a workaround. However, one should think twice before going that route; if one doesn't care if rows are inserted into table_2 or not, why bother inserting them in the first place? May be take another look at your business logic.

Note that such error handling may not necessarily work in other DBMSes; MySQL treats trigger code much like stored procedures invoked upon DML execution, with all associated overhead.


Why is Postgres CTE slower than subquery? I have a somewhat involved query that splits strings and outputs each word as a record.

I did a quick test, one with a CTE and one with a subquery and was somewhat surprised to see that the CTE takes twice as long to execute.

Here is the gist of what the query does:

-- 1. translate matches characters from comment to given list (of symbols) and replaces them with commas.
-- 2. string_to_array splits string by comma and puts in an array
-- 3. unnest unpacks the array into rows

Inline subquery

SELECT
    sub_query.word,
    sub_query._created_at
FROM
(   SELECT unnest(string_to_array(translate(nps_reports.comment::text, ' ,.<>?/;:@#~[{]}=+-_)("*&^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
        nps_reports.comment,
        nps_reports._id,
        nps_reports._created_at
    FROM nps_reports
    WHERE nps_reports.comment::text <> 'undefined'::text
) sub_query
WHERE sub_query.word IS NOT NULL AND NOT (sub_query.word IN ( SELECT stop_words.stop_word FROM stop_words))
ORDER BY sub_query._created_at DESC;

CTE

WITH split AS
(
SELECT unnest(string_to_array(translate(nps_reports.comment::text, ' ,.<>?/;:@#~[{]}=+-_)("*&^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
    nps_reports.comment,
    nps_reports._id,
    nps_reports._created_at
FROM nps_reports
WHERE nps_reports.comment::text <> 'undefined'::text
)

SELECT
    split.word,
    split._created_at
FROM split
WHERE split.word IS NOT NULL AND NOT (split.word IN ( SELECT stop_words.stop_word FROM stop_words))
ORDER BY split._created_at DESC;

And here are the EXPLAINs for each:

Subquery Explain

Sort  (cost=15921589.76..16082302.91 rows=64285258 width=40) (actual time=16299.150..17697.914 rows=4394788 loops=1)
  Sort Key: sub_query._created_at DESC
  Sort Method: external merge  Disk: 116112kB
  Buffers: shared hit=22915 read=7627, temp read=34281 written=34281
  ->  Subquery Scan on sub_query  (cost=2.49..2311035.10 rows=64285258 width=40) (actual time=0.177..13274.895 rows=4394788 loops=1)
        Filter: ((sub_query.word IS NOT NULL) AND (NOT (hashed SubPlan 1)))
        Rows Removed by Filter: 3676303
        Buffers: shared hit=22915 read=7627
        ->  Seq Scan on nps_reports  (cost=0.00..695825.11 rows=129216600 width=88) (actual time=0.073..9781.244 rows=8071091 loops=1)
              Filter: ((comment)::text <> 'undefined'::text)
              Rows Removed by Filter: 844360
              Buffers: shared hit=22914 read=7627
        SubPlan 1
          ->  Seq Scan on stop_words  (cost=0.00..2.19 rows=119 width=4) (actual time=0.016..0.034 rows=119 loops=1)
                Buffers: shared hit=1
Planning time: 0.115 ms
Execution time: 18451.245 ms

CTE Explain

Sort  (cost=17213755.76..17374468.91 rows=64285258 width=40) (actual time=44008.467..45508.786 rows=4394788 loops=1)
  Sort Key: split._created_at DESC
  Sort Method: external merge  Disk: 116112kB
  Buffers: shared hit=23031 read=7531, temp read=34281 written=353942
  CTE split
    ->  Seq Scan on nps_reports  (cost=0.00..695825.11 rows=129216600 width=135) (actual time=0.057..10451.951 rows=8071091 loops=1)
          Filter: ((comment)::text <> 'undefined'::text)
          Rows Removed by Filter: 844360
          Buffers: shared hit=23027 read=7531
  ->  CTE Scan on split  (cost=2.49..2907375.99 rows=64285258 width=40) (actual time=0.162..37888.364 rows=4394788 loops=1)
        Filter: ((word IS NOT NULL) AND (NOT (hashed SubPlan 2)))
        Rows Removed by Filter: 3676303
        Buffers: shared hit=23028 read=7531, temp written=319661
        SubPlan 2
          ->  Seq Scan on stop_words  (cost=0.00..2.19 rows=119 width=4) (actual time=0.009..0.030 rows=119 loops=1)
                Buffers: shared hit=1
Planning time: 0.649 ms
Execution time: 46297.825 ms

CTE's in PostgreSQL are an optimization fence. That means the query planner doesn't push optimizations across a CTE boundary.

Blog entry about this

I think a lot of this is silly though you can just write it like this.. Here we use CROSS JOIN LATERAL rather than the complex wrapping and NOT EXISTS rather than NOT IN

SELECT word,
  _created_at
FROM nps_reports
CROSS JOIN LATERAL unnest(regexp_split_to_array(
  nps_reports.comment,
  '[^a-zA-Z0-9]+'
)) AS word
WHERE nps_reports.comment <> 'undefined'
  AND nps_reports.comment IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM stop_words
    WHERE stop_words.stop_word = word
  )
ORDER BY _created_at DESC;

All of that said, whatever you're doing seems to be reinventing FTS. So that's also a bad idea.


How to store and query products the price of which depends on a complex set of conditions? I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB.
Let the products be packages of cable tv providers:
{
    name: "Virgin TV",
    price: 20
},
{

    name: "Sky",
    price: 25
}

Every package comes with some conditions. The Sky package for example may be:

The first three months the price is XXX then it becomes YYY.
If you watch TV after 00:00 is XXX-10.
If you are the N'th customer you have 10% discount.
...

But the conditions for the Virgin package will be different.
1) How do I actually store the products along with all their conditions?
I could only think of creating something like stored procedure for every different condition?
It would be nice if I don't have to normalize all conditions to fit a database schema because new conditions can easily come and go.
EDIT :
I've realized 2 essentials

MongoDB supports anonymous functions as a value of a field.
Map/Reduce can transform a collection to another using a function

Now our the Sky package along with its condition can be easily stored:
db.packages.insert({
    name:      "Sky"
    price:     25,
    condition: function(object, user_input){
        time_discount = user_input.time > 0 and user_input.time < 6 ? 10 : 0;
        price = price - time_discount;
        return 6*price + 6*(price*1.2);
    }
})

Then if we use mapReduce we can query in like:
    db.packages.mapReduce(
        function(){
            emit(this.name, {...some fields..., annual_price: this.cond(this, user_input)});
        },
        function(key, values) {
            return values;
        },
        {out: "tempCollection"}
    ).find()

I'm not happy with the fact the mapReduce output is a an object which stores the transformed values in a single key named "value". Is there another way to transform a collection other than MapReduce?
------- Outdated ------
MongoDB has server-side functions which definition I presume can say something like:
db.system.js.save( { _id : "sky_annual_price" , value : function(price, user_input){
    time_discount = user_input.time > 0 and user_input.time < 6 ? 10 : 0;
    price = price - time_discount;
    return 6*price + 6*(price*1.2);
}});

for the Sky example above.
Yes, in theory looks nice but how can this be used in a query?
------- End of outdated ------

2) How do I query such information so that I can sort by the annual price ?
Any ideas on both 1) or 2) are welcome. I've spent considerable amount of time but I can't figure that out.

Check out chapter 3 of

The Data Model Resource Book Volume 1 by Silverston

It has a pricing model that is very flexible

A very brief summary:

It's kinda complicated...

(*) means this column references another table

PriceComponent

id
fromDate
thruDate null
price null
percent null
geographicBoundary* [pizza costs more outside of city]
partyType* [surcharge to seniors]
productCategory* [discount for CRT monitors]
quantityBreak* [discount for high volume]
orderValue* [discount for big order]
saleType* [surcharge for retail]
currency* [discount for Canadians]
productFeature*
product*

BasePrice : PriceComponent

DiscountComponent : PriceComponent

SurchargeComponent : PriceComponnent

ManufacturersSuggestedRetailPrice : PriceComponent

Charge : PriceComponent

OneTimeCharge : Charge

RecurringCharge : Charge

UtilizationCharge : Charge

So, the price of something is a set of PriceComponents, which can be either an actual price amount or a percentage. You can have a component of the final price based on geographical location, the type of sale (retail vs wholesale), the value of the order and so on.


Alter a view that refers to a non-readable secondary database I have a database (DB1) that is not readable because it is secondary in an AG group.
I have another database (DB2) that is not replicated with the AG group. It contains some views that reference the first database. I need to periodically alter some views.
The error is:

Msg 976, Level 14, State 1, Procedure vw_xxx, Line 4 [Batch Start Line
9] The target database, 'DB1', is participating in an availability
group and is currently not accessible for queries. Either data
movement is suspended or the availability replica is not enabled for
read access. To allow read-only access to this and other databases in
the availability group, enable read access to one or more secondary
availability replicas in the group.  For more information, see the
ALTER AVAILABILITY GROUP statement in SQL Server Books Online."

I do not have these options:

Enable the readable secondary (lack of licenses)
AG failover (this is a DR site)
Add DB2 to the same AG (too large, I only want to replicate schema not data)

Are other options available?
I have 3 AG nodes. The DR is not readable because it lacks an active license to be readable. The ALTER VIEW occurs on DB2 at the DR site, referencing DB1 in that instance. I'm altering the views because I'm deploying changes with a dacpac in that DR site. I do not have licences to cover that DR site in an active way.

Can you create a linked server on each AG node that references the AG listener?
You can then alter your view to query DB1 via the linked server using 4-part naming, instead of querying DB1 using 3-part naming. Then you shouldn't encounter this error as the linked server will redirect to the AG primary.


what AD groups logins my user belongs to? I am not sure if I have chosen the right title for this question. what
  I am really after is, given a individual windows AD user, I would like to
  find out the list of the windows AD groups (logins) that have access to an
  specific database in this server

when I run the following query

select
    name,
    principal_id,
    type,
    type_desc,
    default_schema_name,
    create_date,
    modify_date,
    owning_principal_id,
    sid,
    is_fixed_role
from sys.database_principals

in my server

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011
  00:54:03      Copyright (c) Microsoft Corporation     Standard Edition
  (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

I get the following results (partial list):

  I need to know all the permissions a particular login has. this login
  has access to my server/databases through AD groups.

  1) what AD groups, from the list above, does my login belong to?

I have been doing this, below,
but I would really like to find out the list of the AD groups (that have access to this server according to the above picture) that this user belongs to.

  First I execute as the user in question

    EXECUTE AS LOGIN='mycompany\HThorne'

    DECLARE @User VARCHAR(20)
    SELECT @USER = SUBSTRING(SUSER_SNAME(),
CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))

  i  make sure that I have the right credentials

    SELECT   @USER
            , SUSER_SNAME()
            ,SYSTEM_USER
            , USER_NAME()
            , CURRENT_USER
            , ORIGINAL_LOGIN()
            , USER
            ,SESSION_USER

  I go to the specific database and use the fn_my_permissions - run as
  the user in question

    use WebDataImportStage
    go
    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
    GO

    REVERT

  and that is giving me the result below:

what AD groups, from the list above, does my login belong to?

All you need to do is execute the following command:

EXEC xp_logininfo 'domain\useraccount','all';
GO

If the account does not have access via any group on that server, AND is a legit account in the domain, you will get no records returned. If the user is found to have permissions you can identify the group they have access from by checking the permission path. This will return the group domain\groupname that is giving the domain user access.


SQL database restore failed I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that time onwards i am getting this error. I refresh the database, make a copy of it, delete it & try to attach it again and thus the error occurs.

The log cannot be rebuilt because there were open transactions/users when the database
was shutdown, no checkpoint occurred to the database, or the database was read-only. This  error could occur if the transaction log file was manually deleted or lost due to a hardware  or environment failure.

If i use the log file while attaching the db, i got this error

The log scan number (1224:41:1) passed to log scan in database 'test' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Kindly help
Thanks

Try attaching the databse with ATTACH_REBUILD_LOG after moving the original log file.


Where are connections to databases made from when using SSRS? If I have SSRS set up in the following format

Server 1 : Running SQL Server, hosting the SSRS database

Server 2 : Running SSRS, configured to store metadata on Server 1 database

And SSRS reports are configured to connect to say, Server 3

Will the connection to server 3 take place from server 1 or server 2?

From Server2 to Server3.  The SSRS service is what connects to the database which contains the database which the report is querying.


Configure wallet location in sqlnet.ora and listener.ora I have a host containing multiple DBs and the connection to the DBs need to be SSL enabled. Since each DB has a unique wallet, I specify the wallet location in sqlnet.ora as:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=wallet_folder/$ORACLE_UNQNAME)))

This way the listener can use the correct wallet depending on the value of ORACLE_UNQNAME variable. But I have two issues with this method.

Since ORACLE_UNQNAME is a shell environment variable a client has no
control over it.
Even when connecting from the same machine I have to bounce the listener after exporting ORACLE_UNQNAME value.

What is the solution for such a scenario ?

You can change the $ORACLE_UNQNAME to $ORACLE_SID. Assuming each Container DB has a unique SID, you can start the CDB with the right wallet as ORACLE_SID is required to start the database anyway.


Get last Reoganized OR Rebuild index date I am currently working on migrating sqlserver 2008r2 to SQL server 2016.The DBA before me used "IndexOptimize" from https://ola.hallengren.com/downloads.html to create the iIndex solution.
I can see the job is schedlued and run on a monthly basis and logic apply is:
if Fragmentation < 20 then reorganize index
if fragmentation > 40  rebuild index
I am planning on using  the Maintenance plans via SSMS in order to reorganize or Rebuild indexes.
How do i find which one has run "reorganize/rebuild", I  can see fragmentation for some table are above 66. Am right in thinking that rebuild  Index is not taking place?

As Aaron mentioned in the comment, the indexes with a lot of fragmentation you are seeing probably got skipped due to the index being too small or too big (default for this is no index is too big).

You can search for the below parameters in the sp definition, and check the job to make sure a different value is not being passed through:

@MinNumberOfPages INT = 1000,
@MaxNumberOfPages INT = NULL,

Another possibly is that the index is being rebuilt and then re-fragmenting again. This to me would be a sign that constant rebuilds of that index actually aren't a good help.

If you're looking for when\if the index was ever rebuilt, the Ola sp does have the ability to log to a table and the previous DBA hopefully utilized this feature. If so, you can use the below to figure out when the last time an index was rebuilt.

SELECT
'[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']' AS ObjectName,
MAX(StartTime)

FROM
dbo.CommandLog

WHERE
CommandType = 'ALTER_INDEX'

GROUP BY
'[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']'


Why insert in with is returning unexpected results? Context

Schema setup

create table users( id serial primary key );

Query 1

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1;

Expectations

1

Result

1

Query 2

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t2;

Expectations

2

Result

1

Query 3

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1
union all
select id from t2;

Expectations (taking in account results of Query 2)

1
1

Result

1
2

Question

What's wrong with my expectations and how to get the expected value from Query 2?

This is an expected and documented behaviour

Quote from the manual

  Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable

In your second query the database apparently chose to run t2 before t1 and thus it returns 1.

In the third query there have to be two different values, because that's what a sequence guarantees: it never generates the same number twice (unless you have enabled cycle)


SQL Server Restore w/o "Closing Existing Connections" By default in SSMS (2012/2014) when you choose to restore a database, the "close existing connections" option located in the
is by default unchecked, is it possible to restore a database while maintaining a connection to it (i.e. in an Enterprise Edition)? If not, why have this option (besides as a sanity check), if so? How?

No, the option is there for you so that you can decide to either:

Kick all users out right now and restore immediately; or,
let the restore operation wait until it can get exclusive access to the
database before it begins.

You can't restore and keep connections active, sorry. This is like changing the keg without interrupting someone actively pouring a beer...


mysql dump best practices for development I have an application that has a large database that includes, among many other tables, a table called content and a table called users.

I would like to set up a development environment for this application, however I would like to bring in only some parts of the content and users tables.

If there are 50,000 rows for each of these tables, I only want to bring over 10 rows so my development environment has at least some data.

What is the best way to create a dump of the database data and structure, while also limiting the size of the dump and the risk of putting sensitive information in a development environment?

mysqldump --opt --where="1 limit 10" database

It's an SQL injection:
The --where option is basically appended to a query of the form SELECT * from table WHERE , so in this case you get SELECT * from table WHERE 1 limit 10. Without the 1, you would have an invalid query. Specifying 1 for a where clause (since 1 is always true) simply selects all records.

Source: Limiting the number of records from mysqldump?


AlwaysON Migrate from 2014 to 2016 I have like 20 Listners on 2014 with approx 500 DBS,

what is the best way to migrate these with least downtime, effort

My idea is:
  Stop access
  Backup dbs
  Start restore dbs
  While restore is running:
  Drop AOG on 2014
  Create it on 2016
  Done

This seems simple, but with TB data this will take some time.

Have found this ->
Recommended ways to upgrade AlwaysOn AG from 2014 to 2016
But it niether does explain

Hope someone can help

I'd recommend configuring log shipping to migrate your databases. Hours/days/weeks before your planned migration, configure log shipping from your current (2014) production Availability Group. You should perform the log shipping restores on all nodes of the new (2016) Availability Group.

Also in advance, pre-configure the Availability Groups on the new servers, but do not create the Listener.

This will minimize downtime during your cutover maintenance by restoring the majority of your data ahead of the maintenance window.

During the cutover, the steps to complete the migration would be:

Shut down applications/kill existing sessions.
Perform tail log backups for the (2014) databases you are migrating. (BACKUP LOG... WITH NORECOVERY)
Allow the log shipping restore jobs to run a final time, or manually restore the final logs to all new (2016) servers WITH NORECOVERY.
On one of the new servers, recovery the databases (RESTORE DATABASE... WITH RECOVERY).
Add the databases to the Availability Groups, and join secondary servers.
Drop the Listeners on the old (2014) AGs.
Create the Listeners on the new (2016) AGs.
Turn on applications.


Performance Tuning for Huge Table (SQL Server 2008 R2) Background:
I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test.

Table Features:

No of Dimensions ~ 45
Measures ~ 30
Non-additive measures and other columns~ 25
Current data size ~ 200 Million (2 years data)
Time View: 3 different Month views: Fiscal/Calendar/Adjusted (i.e same row can fall in different months based on which view one is looking for)
Only One view will be required at a time by a user. (ie. only one Month Column will be used in the query, it's stopping us to do partitioning on time view)
Indexes: 1 Clustered Index on the Natural Keys (8 columns).Created 3 covering Non Clustered Indexes one on the each Month column including few Dimension SKs (FKs) and all the measures).
Indexes are huge(total 190 GB) because of this.
Space is not constraint (1 TB allocated)
64 GB of RAM available in server.
Table compression also done.

Requirement:
Queries on this Fact table should give result within 30 seconds (General queries select sum(measure) joining few Dims group by Dim Values). Reports are directly done on top of this Fact table.

Issue:
Any query which includes columns available in the Index works fine, but if we include any other columns which are not in the include..It sucks. It takes more than 5-10 minutes. Can any one suggest some solution where it works fine for any dimension/column we select. Can Index view help in this situation?

Upgrade to SQL Server 2012 and use columnstores. They thrive in these requirements. Seriously, download the evaluation edition and give it a try. Drop all indexes, drop the clustered index, simply add a non-clustered columnstore index on all columns and give it a whirl. I've seen cases just like your that reduced the execution time to 2-3 seconds, mostly because of segment elimination kicking in. Some supplemental reads:

SQL Server Columnstore Performance Tuning
SQL Server Columnstore Index FAQ
Achieving Fast Parallel Columnstore Index Builds
Work Around Performance Issues for Columnstores Related to Strings
Use Outer Join with Columnstores and Still Get the Benefit of Batch Processing
Using Statistics with Columnstore Indexes


Is there any value in adding an already indexed column to a composite index? The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE clause? For example....

index1('col1')
index2('col2')
index3('col1','col2')

select some_column from table where col1 = this;                    //uses index1
select some_column from table where col2 = that;                    //uses index2
select some_column from table where col1 = this and col2 = that;    //uses index3

Explain... seems to indicate it chooses an index with the least number of pertinent columns.

Thanks.

The query

select col1,col2
from mytable
where
  col1 between a1 and b1
  and col2 between a2 and b2

can be answered using index3 without accessing the table.

If there is only index1 and index2 for example the following methods are possible:

method I:
1) check for each row from mytable if col1 between a1 and b1 and col2 between a2 and b2
method II:
1) find all rows with col1 between a1 and b1 by using index1
2) lookup each row in mytable and check if the col2 between a2 and b2
method  III:
1) find all rows with col1 between a1 and b1 by using index1
2) find all rows with col2 between a2 and b2 by using index2
3) find the intersection of the rows found by step 1 and step2

All of these methods are inferior to using index3:

Method I needs to scan the whole table even if there are only a view (or no) rows that satisfy the condition.

Method II can be even worse than method I if step 1 returns a lot of possible candidates that must be looked up.

In Method III both step 1 and step 2 can return a lot of rows but the intersections is only a small number of rows.


Sql server AOAG license on readable secondary Scenario:
Microsoft sql server AlwasOn Availabiliry Group:
For SQL server License on readable secondary if :
Example
Primary server  cores : 32
Secondary Replica cores : 32, but at the time it's secondary only 12 cores assigned to sql server.
The question:
Will the license be calculated for the 32, or the 12 cores on active readable secondary

You have to cover the readable secondary with proper license. So you have to count the cores on the secondary.
Count the cores assigned by the virtualizer (private cloud) or physical total cores (on-premise install).


Best way to populate a new column in a large table? We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL constraint to it).

If I understand Postgres correctly an update is technically a delete and insert so this is basically rebuilding the entire 2.2 gb table. Also we have a slave running so we don't want that to lag behind.

Is there any way better than writing a script that slowly populates it over time?

It very much depends on details of your setup and requirements.

Note that since Postgres 11, only adding a column with a volatile DEFAULT still triggers a table rewrite. Unfortunately, this is your case.

If you have sufficient free space on disk - at least 110 % of pg_size_pretty((pg_total_relation_size(tbl)) - and can afford a share lock for some time and an exclusive lock for a very short time, then create a new table including the uuid column using CREATE TABLE AS. Why?

What causes large INSERT to slow down and disk usage to explode?

The below code uses a function from the additional uuid-oss module.

Lock the table against concurrent changes in SHARE mode (still allowing concurrent reads). Attempts to write to the table will wait and eventually fail. See below.
Copy the whole table while populating the new column on the fly - possibly ordering rows favorably while being at it.
If you are going to reorder rows, be sure to set work_mem high enough to do the sort in RAM or as high as you can afford (just for your session, not globally).
Then add constraints, foreign keys, indices, triggers etc. to the new table. When updating large portions of a table it is much faster to create indices from scratch than to add rows iteratively. Related advice in the manual.
When the new table is ready, drop the old and rename the new to make it a drop-in replacement. Only this last step acquires an exclusive lock on the old table for the rest of the transaction - which should be very short now.
It also requires that you delete any object depending on the table type (views, functions using the table type in the signature, ...) and recreate them afterwards.
Do it all in one transaction to avoid incomplete states.

BEGIN;
LOCK TABLE tbl IN SHARE MODE;

SET LOCAL work_mem = '???? MB';  -- just for this transaction

CREATE TABLE tbl_new AS
SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order>
FROM   tbl
ORDER  BY ??;  -- optionally order rows favorably while being at it.

ALTER TABLE tbl_new
   ALTER COLUMN tbl_uuid SET NOT NULL
 , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1()
 , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid);

-- more constraints, indices, triggers?

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME tbl;

-- recreate views etc. if any
COMMIT;

This should be fastest. Any other method of updating in place has to rewrite the whole table as well, just in a more expensive fashion. You would only go that route if you don't have enough free space on disk or cannot afford to lock the whole table or generate errors for concurrent write attempts.

What happens to concurrent writes?

Other transaction (in other sessions) trying to INSERT / UPDATE / DELETE in the same table after your transaction has taken the SHARE lock, will wait until the lock is released or a timeout kicks in, whichever comes first. They will fail either way, since the table they were trying to write to has been deleted from under them.

The new table has a new table OID, but concurrent transaction have already resolved the table name to the OID of the previous table. When the lock is finally released, they try to lock the table themselves before writing to it and find that it's gone. Postgres will answer:

  ERROR:  could not open relation with OID 123456

Where 123456 is the OID of the old table. You need to catch that exception and retry queries in your app code to avoid it.

If you cannot afford that to happen, you have to keep your original table.

Keeping the existing table, alternative 1

Update in place (possibly running the update on small segments at a time) before you add the NOT NULL constraint. Adding a new column with NULL values and without NOT NULL constraint is cheap.
Since Postgres 9.2 you can also create a CHECK constraint with NOT VALID:

  The constraint will still be enforced against subsequent inserts or updates

That allows you to update rows peu à peu - in multiple separate transactions. This avoids keeping row locks for too long and it also allows dead rows to be reused. (You'll have to run VACUUM manually if there is not enough time in between for autovacuum to kick in.) Finally, add the NOT NULL constraint and remove the NOT VALID CHECK constraint:

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID;

-- update rows in multiple batches in separate transactions
-- possibly run VACUUM between transactions

ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL;
ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;

Related answer discussing NOT VALID in more detail:

Disable all constraints and table checks while restoring a dump

Keeping the existing table, alternative 2

Prepare the new state in a temporary table, TRUNCATE the original and refill from the temp table. All in one transaction. You still need to take a SHARE lock before preparing the new table to prevent losing concurrent writes.

Details in these related answer on SO:

Best way to delete millions of rows by ID
Add new column without table lock?


Are all matching indexes (indices) searched for a query? If I have multiple indexes that include a common attribute - will each index be searched upon a query involving this attribute? Or is only one index searched?

It depends very much on the DBMS being used. Some can never user more than one index per table and query other can do so.

But if there are multiple indexes sharing the same column, I doubt that even the DBMS that can use more than one index would do it, especially if the leading columns are identical.

If there is an index on (foo)  and one on (foo, bar) the first one is redundant as every query that would benefit from it could also use the second one.

I highly recommend the site "Use the index Luke", it has a lot of in-depth information about how indexes work and how they are used including the differences for most of the popular DBMS


Backup a large postgres database for PITR I have one postgres 9.1 master and 2 slaves setup with streaming replication with WAL archive.

Now, I am trying to set up for PITR for at least a window of 1 week. This is to cover for any administrative errors (like dropping an important table by mistake). Here is what I understand of PITR

We need a base backup of the database at a known point in time.
We need a WAL archive from the time of base backup to the point of error.

Using the above 2 we can rebuild the database to any point, in between.

Database involved is large, approx 1TB, growing at 30 GB a week

I want to understand what is the best way to achieve fast base backup creation and low performance overhead on the running instances. I can think of following alternatives but not sure

if there are any pros and cons to each
if there are other better alternatives

Is it better to take a base backup once every week and accumulate
WAL until the next base backup OR take just one base backup (say
now) and backup only WAL week-after-week.
In case we do base backup every week, is it better to use
pg_basebackup or pg_start_backup + rsync + pg_stop_backup. Will the
second option be significantly faster if I use the same destination
disk/volume/folder week-after-week, because of rsync ?
Is it better to run base backup against the master or is it possible
to take a disk snapshot of the postgres cluster after stopping one
of the slaves temporarily and resuming the replication after the
snapshot. Will this snapshot be usable to setup an instance in
recovery mode in case I want to do PITR or will there be any data
consistency or time line issues ?

Thanks for any insights.

There are several ways of doing what you're wanting.

I would recommend using a specific solution such as WAL-E, barman, or OmniPITR to make the logistics of managing basebackups and WAL archives easier on yourself, or using pg_basebackup. Some of these tools, depending on the versions, will allow you to back up off your replica, which will reduce load on your primary server, and not push data out of the filesystem cache, which are all very good things.

And as you expected, there are definitely pros and cons with your approaches.

For your first question, you have to consider that if you take a single basebackup and just keep archiving WAL segments, the time to restore will grow longer with each WAL segment you archive, as it takes a significant amount of time restore each file.
So that is not ideal, and most people tend to take a backup on a schedule that allows them to meet recovery requirements, and sometimes keep multiple basebackups as their storage allotments allow.
Additionally, with a very, very, very large WAL archive, sometimes things can happen with individual WAL segments (which is why you always test your backups), and if a WAL segment fails to restore, no following segments will restore, and you're out of luck.

A general rule I've heard and use myself is if I take weekly basebackups, keeping around 3 weeks of WAL segments and 2 basebackups allows me enough peace of mind to have at least one known-good backup, and a known-good set of WAL, and a fairly reasonable restore time. I am a huge believer in testing the backups I take regularly.

For your second question, either one works well, though, pg_basebackup is easier to use and requires less of your own assembly. If you use rsync in your own script with start and stop basebackup, you have to check the exit code of every command. If you don't and a command fails, you can end up with a corrupt basebackup that's useless. The above mentioned tools, along with pg_basebackup, take care of all of those pedantic details for you.

For your third question, in some cases, yes, you can make a backup off of a replica, instead of the primary. If you can manage it, doing the basebackup and archiving off of a replica is the way to go, because taking a basebackup generates a lot of I/O, and it affects your OS filesystem cache. Unfortunately, it is not supported until 9.2+ with pg_basebackup, so you'll need to use an external tool such as OmniPITR or to upgrade your version of PostgreSQL. So long as you have the WAL to go with it, there shouldn't be any timeline issues or consistency issues with the snapshot from the replica.

Finally, a lot of your questions and best practices are covered in the following talk from Magnus Hagander at PgCon 2013: PostgreSQL Backup Strategies

It is well worth the time spent to watch the video.

Hope that helps. =)


TempDB will not shrink. No Open Transactions I have a TempDB on SQL 2008 that has gotten very large (>40gb) and I want to shrink it down.  I have used the dbcc shrinkdatabase, dbcc shrinkfile and the shrink command through Management Studio.

I get the following error:

  Page 1:4573184 could not be moved because it is a work table page.

I have been able to reclaim some space to get me out of danger by running DBCC FREEPROCCACHE and re running one of the shrink routines, but obviously this isn't ideal and will likely only buy me a bit time.

I have run DBCC OpenTran and there is nothing that is hanging out there.

Everywhere I have read on internet comes down to recycling SQL Server...surely there has to be a better way...anyone?

Thanks,

Tom

Note: this post might be useful too:

Issues with TempDB mdf file ever increasing

Unless you can figure out what process is using that work table (and can safely kill it), I'd have to agree with what your searches have already yielded: cycle the server and you should be able to shrink tempdb.

A different question has dealt with figuring this out for #temp tables; I don't know if it can be adapted for work tables:

Find which session is holding which temporary table

I've also blogged about it (again, for #temp tables):

http://sqlperformance.com/2014/05/t-sql-queries/dude-who-owns-that-temp-table

I doubt the work table is related to snapshot isolation / version store, but just in case:

Find transactions that are filling up the version store

Also, don't rely on DBCC OPENTRAN; - I've observed many scenarios where I know I have an active transaction but it doesn't show up there. And note that database context is important; the database where the transaction is active is not necessarily tempdb. What do you see here? Anything?

SELECT * FROM sys.dm_tran_active_transactions
  WHERE name = N'worktable';

Once you've shrunk tempdb

Of course, this isn't a permanent solution. You're going to shrink tempdb, and then it's going to grow again. This can become very boring and tedious to play this game every time it happens. And if it is going to grow again, what are you going to do with that free space in the meantime? Lease it out and then evict people when tempdb needs it again? You need to either:

Fix the process that is making tempdb grow abnormally large in the first place.
Allocate enough space for tempdb so that it doesn't need to grow, and stop shrinking it (especially if only temporarily; this is just wasted work!).

A couple of other suggestions:

Don't use SHRINKDATABASE (which should be called auto-fragment) or the UI. Write specific, targeted SHRINKFILE commands to affect individual files.
Consider using multiple files for tempdb (which you could spread out to different storage if/when possible), and consider trace flags 1117 (as long as this behavior will not affect your user databases too) and 1118.
Some tips on minimizing tempdb utilization here:

How to identify which query is filling up the tempdb transaction log?


How to concatenate a column after x rows? I have a table with columns

object varchar(255),
frequency tinyint(3) unsigned,

I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as

.. query loop {
$object .= $row['object'].",";
$frequency = $frequency+$row['frequency'];

if($frequency > 20) { // 20 is a given value
    $mysqli->query("INSERT INTO table2 (concatenated_column) VALUES ('$object')");
    $frequency=0;
    $object='';
    }
}

Is it possible to do this with a native query of mysql instead of bringing the data into PHP and returning back to mysql?

Since the process is simple, I think mysql has enough functions to do so, but I do not know how to do so.

In fact, I want to group concatenate the entire column and split it when sum of frequency reaches the given value (i.e. 20). This needs a loop of INSERTs in which the sum of frequency reset to 0 upon reaching 20.

UPDATE: The process with do as:

table1:

object    frequency
object1   7
object2   5
object3   6
object4   8
object5   2

table2:

concatenated_column
object1,object2,object3,object4
object5,....

Basically, you want to result the running total and group it

This requires iterative variable manipulation.

First, your sample data quadrupled:

mysql> drop database if exists all_db;
Query OK, 1 row affected (0.03 sec)

mysql> create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql> use all_db
Database changed
mysql> create table table1
    -> (
    ->     object varchar(255),
    ->     frequency tinyint unsigned
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 values
    -> ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into table1 select * from table1;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into table1 select * from table1;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+---------+-----------+
| object  | frequency |
+---------+-----------+
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
+---------+-----------+
20 rows in set (0.00 sec)

mysql>

Let's make the query that generates group values:

set @freq_limit = 20;
set @freq_count = 0;
set @freq_group = 1;
set @freq_sum   = 0;
select cc,GROUP_CONCAT(object) objects from
(
select * from
(
    select *,
        @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
        @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
        @freq_group    := @freq_group + @freq_groupinc                 cc,
        @freq_count    := @freq_count + frequency                      dd
    FROM table1
) AA) A GROUP BY cc;

Would you believe it works?

First I'll let you see the inner subquery's output

mysql> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA;
+---------+-----------+----+------+------+------+
| object  | frequency | bb | aa   | cc   | dd   |
+---------+-----------+----+------+------+------+
| object1 |         7 |  0 |    0 |    1 |    7 |
| object2 |         5 |  0 |    7 |    1 |   12 |
| object3 |         6 |  0 |   12 |    1 |   18 |
| object4 |         8 |  0 |   18 |    1 |   26 |
| object5 |         2 |  1 |    0 |    2 |    2 |
| object1 |         7 |  0 |    2 |    2 |    9 |
| object2 |         5 |  0 |    9 |    2 |   14 |
| object3 |         6 |  0 |   14 |    2 |   20 |
| object4 |         8 |  1 |    0 |    3 |    8 |
| object5 |         2 |  0 |    8 |    3 |   10 |
| object1 |         7 |  0 |   10 |    3 |   17 |
| object2 |         5 |  0 |   17 |    3 |   22 |
| object3 |         6 |  1 |    0 |    4 |    6 |
| object4 |         8 |  0 |    6 |    4 |   14 |
| object5 |         2 |  0 |   14 |    4 |   16 |
| object1 |         7 |  0 |   16 |    4 |   23 |
| object2 |         5 |  1 |    0 |    5 |    5 |
| object3 |         6 |  0 |    5 |    5 |   11 |
| object4 |         8 |  0 |   11 |    5 |   19 |
| object5 |         2 |  0 |   19 |    5 |   21 |
+---------+-----------+----+------+------+------+
20 rows in set (0.00 sec)

mysql>

Look at the cc column. It increments every time the sum exceeds @freq_limit (20) and resets.

Now just run GROUP_CONCAT on object, grouping by cc

mysql> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object2,object3,object5,object1 |
|    3 | object4,object5,object1,object2 |
|    4 | object5,object1,object3,object4 |
|    5 | object2,object3,object4,object5 |
+------+---------------------------------+
5 rows in set (0.00 sec)

mysql>

Just set @freq_limit to whatever value you need.

To show it works for just the 5 rows you supplied:

mysql> drop database if exists all_db;
Query OK, 1 row affected (0.04 sec)

mysql> create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql> use all_db
Database changed
mysql> create table table1
    -> (
    ->     object varchar(255),
    ->     frequency tinyint unsigned
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into table1 values
    -> ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object5                         |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!


Record versioning and promotion Let's say we have this hierarchy:

   -World
        --USA
        ---WA
        ----Seattle
        -----Downtown
        ------1st Ave
        -------945 1st ave
        ------3rd Ave
        -----South
        ----Bellevue
        ---TX
        ---MI
        --Canada
        ---BC

Now, this will not reflect real life completely, but.

At location World we have some global settings. Let's say: list of Races, list of Languages, etc. All locations underneath it can access this data.
Country level holds data about laws, currency, etc.

3.Each city can modify laws. After they modified the law, it is available to the city itself and to the regions within the city. After 3-step approval process, it can become a country level law, and all children will inherit new version of the law. Each city still can modify new version of a law to have changes.

Every City has sandboxes. This means that different organizations within city can test out editing different versions of laws without affecting City master revision.

Currently we solve this issue like this:

We have table Location with HierarchyId
Then we have table Law that has columns StateType, Location_Id, etc.
StateType can be: Master (Available to all within country), Edited (Edited for a specific location), Approved (Approved at a specific location).

Because of this we have to use Table-valued functions in our application to filter specific records that are relevant to the current location.  (Some laws can come from country level, some laws come from city level, some laws come from organization level). This makes database much slower.

We cannot use native unique constraints on our fields, because all fields must be unique only within a branch, so we have to use custom check constraints to keep data clean.

Also, insertion and editing records is much slower, since we have to promote some records to a country level, and then apply data transformation for other countries.

Currently we have around 150 tables, and we use a lot of joins.

I'm not sure what's the best way to model complex hierarchical data with versioning. (I'm thinking we need an enchanced version of source control..)

Use one of the various techniques for modeling hierarchical data in a relational database.  My preferred approach is visitation numbers (see my answer to this question for more information about visitation numbers) but you could also use adjacency lists.

This will simplify the retrieval of Laws that pertain to a particular Location.

You will also want to include some kind of level indicator on your locations.  This will allow local overrides of broader laws to be identified.  You can sort by the level indicator to pick the most localalized version of any particular law.


SQL Server High Hash Match and Sort I have an SQL statement that is taking about 40 seconds to run and the only thing I see is an high hash match and sort. I have added indexs to the temp table but still taking an long time to complete.

https://www.brentozar.com/pastetheplan/?id=rJ7iMDC9m

SQL Server Execution Times:
   CPU time = 46735 ms,  elapsed time = 9004 ms.

SELECT g.CustomerId, g.LogDate
INTO #TempGuidelineLog
FROM vwGuidelineLog g --nolock
where g.LogDate >= '2017-10-01' and g.LogDate < dateadd(day, 1, '2018-09-30')

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)
INCLUDE ([LogDate])

select
g.*,
a.StateId,
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId)
INCLUDE ([CustomerId])

SELECT States=(SELECT Total=COUNT(c.CustomerID), c.StateId, st.Name
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN [State] st ON st.StateId = c.StateId
where c.CountryId = 1 and st.StateId NOT IN (65,66)
GROUP BY c.StateId, st.Name
ORDER BY 1 DESC
FOR XML PATH('State'),type)

,Dates = (SELECT StartDate = CONVERT(VARCHAR(10), '2017-10-01' , 101), EndDate = CONVERT(VARCHAR(10), '2018-09-30', 101)
FOR XML PATH('Date'),type)

,Countries=(SELECT Total=COUNT(c.CustomerID), co.CountryID, co.Name, ISOCode=co.TwoLetterISOCode
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN Country co ON co.CountryID = c.CountryId
GROUP BY co.CountryID, co.Name,co.TwoLetterISOCode
ORDER BY 1 DESC
FOR XML PATH('Country'),type)
FOR XML PATH('Report')

First of all true test of sql is to take variable instead of constant value.
So ,

Declare @From date='2017-10-01'
Declare @To date='2018-09-30'

Immediate correction ,put the filter here

select
g.*,
a.StateId,
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId
where g.CountryId = 1 and g.StateId NOT IN (65,66)

and remove the same filter from where it is now.

I see no requirement of LogDate in #TempGuidelineLog so remove it.

so new index,

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)

Since countryid is more selective,

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId,Stateid,CustomerId)

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CustomerId)

IMO, group by St.Name and co.Name is also hurting your performance.

so you can seperatly query group by function and then with that resultset join State and country table.hope it is clear.

The way you are creating XML is also complicated.You can choose one of the xml design which suite you from here,XML


Create SQL Query in function or PROCEDURE I am trying to create function inside MySQL to create several queries for example :

    DELIMITER |
    CREATE PROCEDURE queryBuilder(_tableName varchar(100))
    BEGIN
          SET @str_query = 'SET @countRows = 0;SELECT COUNT(*) INTO @countRows FROM';
          SET @str_query = CONCAT(@str_query,_tableName, '; SELECT @countRows');

         PREPARE stmt1 FROM @str_query;

         EXECUTE stmt1;

   END|
   DELIMITER;

but it doesn't work? how can i change to work? I know i can create IF statement but i want something with more flexibility.

Don't set @countRows twice.
Don't combine two queries into a single "statement".
Watch out for syntax errors when concatenating without a space.
SELECT @str_query; for debugging.


On what basis DMA identifies columns for Dynamic Data Masking? Can anyone help me explain on what basis 'Data Migration Assessment' features recommendation suggests for Dynamic Data Masking and displays certain objects/tables columns for masking. On what basis DMA identifies those columns for vulnerabilities.

Is there any set algorithm for identification or is it simply based on the name of the column?

Please comment in case of any more details or clarification required for the question.

From what I understand, DMA looks at the column names and tables for keywords that would hold PII, Sensitive Data (such as logins, passwords, etc.), or other unique data attributes. If it finds anything then it should mark it for both dynamic data masking and always encrypted.

If you change the name of the column to not have easily identifiable keywords, the recommendation should not show up for that column/table.


Recurstive query parent child speed Recursive queries to find the tree-structure (parent-child) in a table are commonly found here, however most have the problem that the childID must be higher than the parentID.

A possible solution for this is using a query like (given parent X, find all children)

SELECT * FROM (
    SELECT @pv:=(
        SELECT GROUP_CONCAT( child_id SEPARATOR "," ) FROM link_parent_child WHERE FIND_IN_SET( parent_id, @pv )
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$parentStartID$) tmp
    ) a
WHERE lv IS NOT NULL

OR Given child X, find all parents

SELECT * FROM (
    SELECT @pv:=(
        SELECT parent_id FROM link_parent_child WHERE child_id = @pv
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$ChildStartID$) tmp
    ) a
WHERE lv IS NOT NULL

However, with a table of over 500k records, this is somewhat slow... (understatement)

CREATE TABLE `link_parent_child` (
    `parent_id` int(11) NOT NULL,
    `child_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `link_parent_child`
    ADD PRIMARY KEY (`child_id`,`parent_id`);
    ADD KEY `child_id` (`child_id`),
    ADD KEY `parent_id` (`parent_id`),
    ADD KEY `parent_id_2` (`parent_id`,`child_id`);

even with indexes set, and only 10k records, the query takes more than 80 seconds to run.

So, this query (correct me if I'm wrong) is so slow because the inner part returns NULL for almost all rows, which (i guess) are stored in a temp table and filtered out by the outer part of the query. I guess that the query would speed-up if the NULL's are filtered out earlier, but is that possible?

My tree's have a dynamic length, but mostly not deeper than 3-10 layers. Should I take my losses and solve this programmatically, or is there a method to speed this up?

Well, with the help of Rick James' remark I managed to find the answer.
I installed MariaDB 10.2.5 (10.2.2 or higher required) and imported all MySQL data files by just copying the files in the Data dir to the MariaDB Data dir, and running mysql_update on the MariaDB database.

WITH RECURSIVE tree (parent_id) AS (
    SELECT parent_id FROM link_parent_child WHERE child_id = $ChildStartID$

    UNION ALL

    SELECT c.parent_id FROM link_parent_child lpc
    JOIN tree t ON t.parent_id = lpc.child_id
)
SELECT parent_id FROM tree

It really is that simpel :|

What are CTEs


Migrating SSRS from SQL Server 2012 to 2016 I have a requirement of migrating my whole SQL Server from SQL Server 2012 to 2016. I have already migrated the database engine part. Since I have not really worked on SSRS (reporting part), so need some immediate help.

Following steps I will be taking:

I have to backup and restore the ReportServer database and ReportServerTempDB
I have to backup and restore the encryption keys
I will congifure SSRS through Reporting Server Configuration Manager

What else steps should I be doing to be able to run the reports here on SQL 2016 same as it used to run on the server for SQL 2012. I have heard we have to manually copy so RDL files to new server etc.

Kindly help!

Try RSMigrationTool.exe and RSMigrationUI.exe from Microsoft

Here's  a brief excerpt from the white paper link I included below, together with the migration tool download URL and Backup/Restore Encryption Key URL:

Download the tool at http://www.microsoft.com/en-us/download/details.aspx?id=29560.
Extract the files from the MigrationToo.zip file. You need the following two executable programs in the extracted files.
RSMigrationTool.exe: Command line utility that helps in exporting the reporting services artifacts.
RSMigrationUI.exe: GUI interface that allows the users to export the reporting services artifacts.
Export the SSRS items by using the RSMigrationTool.exe.

a.  Open a command prompt as an administrator.

b.  Navigate to the folder that contains the extracted RSMigrationTool.exe.

c.  Run the tool with the following parameters.

RSMigrationTool.exe [-n native_report_server_url] –I
native_report_server_instance [-h] –f
migration_metadata_files_folder [-p password]

White Paper

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/MigratingContentBetweenReportServers.docx

Migration Tool

https://www.microsoft.com/en-us/download/details.aspx?id=29560

Backup and Restore Encryption Keys

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

Alternately try restoring the SSRS databases

And, if you want to go that route, you can try to restore the SSRS ReportServer and ReportServerTempDB  databases to the target server. This example shows moving from SQL 2005 to 2008 SSRS:

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

As always, try all these things on a test/dev SQL Server first.


Can spatial index help a "range - order by - limit" query Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.

We have the following table with a tree structure (Nested Set model) of words and their frequencies:

lexikon
-------
_id   integer  PRIMARY KEY
word  text
frequency integer
lset  integer  UNIQUE KEY
rset  integer  UNIQUE KEY

And the query:

SELECT word
FROM lexikon
WHERE lset BETWEEN @Low AND @High
ORDER BY frequency DESC
LIMIT @N

I suppose a covering index on (lset, frequency, word) would be useful but I feel it may not perform well if there are too many lset values in the (@High, @Low) range.

A simple index on (frequency DESC) may also be sufficient sometimes, when a search using that index yields early the @N rows that match the range condition.

But it seems that performance depends a lot on the parameter values.

Is there a way to make it perform fast, regardless of whether the range (@Low, @High) is wide or narrow and regardless of whether the top frequency words are luckily in the (narrow) selected range?

Would an R-tree/spatial index help?

Adding indexes, rewriting the query, re-designing the table, there is no limitation.

You may be able to achieve better performance by searching first in rows with higher frequencies. This can be achieved by 'granulating' the frequencies and then stepping through them procedurally, for example as follows:

--testbed and lexikon dummy data:

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create table lexikon( _id serial,
                      word text,
                      frequency integer,
                      lset integer,
                      width_granule integer);
--
insert into lexikon(word, frequency, lset)
select word, (1000000/row_number() over(order by random()))::integer as frequency, lset
from (select 'word'||generate_series(1,1000000) word, generate_series(1,1000000) lset) z;
--
update lexikon set width_granule=ln(frequency)::integer;
--
create index on lexikon(width_granule, lset);
create index on lexikon(lset);
-- the second index is not used with the function but is added to make the timings 'fair'

granule analysis (mostly for information and tuning):

create table granule as
select width_granule, count(*) as freq,
       min(frequency) as granule_start, max(frequency) as granule_end
from lexikon group by width_granule;
--
select * from granule order by 1;
/*
 width_granule |  freq  | granule_start | granule_end
---------------+--------+---------------+-------------
             0 | 500000 |             1 |           1
             1 | 300000 |             2 |           4
             2 | 123077 |             5 |          12
             3 |  47512 |            13 |          33
             4 |  18422 |            34 |          90
             5 |   6908 |            91 |         244
             6 |   2580 |           245 |         665
             7 |    949 |           666 |        1808
             8 |    349 |          1811 |        4901
             9 |    129 |          4926 |       13333
            10 |     47 |         13513 |       35714
            11 |     17 |         37037 |       90909
            12 |      7 |        100000 |      250000
            13 |      2 |        333333 |      500000
            14 |      1 |       1000000 |     1000000
*/
alter table granule drop column freq;
--

function for scanning high frequencies first:

create function f(p_lset_low in integer, p_lset_high in integer, p_limit in integer)
       returns setof lexikon language plpgsql set search_path to 'stack' as $$
declare
  m integer;
  n integer := 0;
  r record;
begin
  for r in (select width_granule from granule order by width_granule desc) loop
    return query( select *
                  from lexikon
                  where width_granule=r.width_granule
                        and lset>=p_lset_low and lset<=p_lset_high );
    get diagnostics m = row_count;
    n = n+m;
    exit when n>=p_limit;
  end loop;
end;$$;

results (timings should probably be taken with a pinch of salt but each query is run twice to counter any caching)

first using the function we've written:

\timing on
--
select * from f(20000, 30000, 5) order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 80.452 ms
*/
select * from f(20000, 30000, 5) order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 0.510 ms
*/

and then with a simple index scan:

select * from lexikon where lset between 20000 and 30000 order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 218.897 ms
*/
select * from lexikon where lset between 20000 and 30000 order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 51.250 ms
*/
\timing off
--
rollback;

Depending on your real-world data, you will probably want to vary the number of granules and the function used for putting rows into them. The actual distribution of frequencies is key here, as is the expected values for the limit clause and size of lset ranges sought.


Always Encrypted - Lock request time out period exceeded I'm trying to encrypt a column and am getting the following error: Set-SqlColumnEncryption : Lock request time out period exceeded. Are there parameters to specify the lock timeout? What could be locking it? The table has about 10k records. It works fine with empty tables or tables with hundreds of records.

Apparently, you can't have certain options turned on for the table, like "large value types out of row". Here's what I did:

exec sys.sp_tableoption
    @TableNamePattern = 'dbo.SomeTableName'
    ,@OptionName = 'large value types out of row'
    ,@OptionValue = 'off';


List users permissions to tables and schemas on MSSQL 2016 List all permissions for a given role? has a nice query for listing users and their roles on a DB.

How could we list all tables and users with their permissions on these tables?

Give this a shot.

SELECT ST.NAME, su.name,
CASE WHEN SYP.[ACTION] = 193 THEN 'SELECT' WHEN SYP.[ACTION] = 195 THEN 'INSERT' WHEN SYP.[ACTION] = 196 THEN 'DELETE' WHEN SYP.[ACTION] = 197 THEN 'UPDATE' ELSE CAST(SYP.[ACTION] AS CHAR(3)) END AS Permission
FROM
SYS.SYSPROTECTS SYP INNER JOIN
SYS.SYSUSERS SU
ON SU.uid = SYP.UID
INNER JOIN SYS.SYSOBJECTS SO
ON SO.id = SYP.id
INNER JOIN SYS.TABLES ST
ON ST.object_id = SO.id


Using default values in primary key Good day.

I have a table like this:

CREATE TABLE mytable (
    myname varchar(30),
    mydate date DEFAULT CURRENT_DATE,
    PRIMARY KEY(myname, mydate)
);

EDIT:
Forgot to mention I am using a view and a rule

So I have a view that is basically the same thing as the table

CREATE VIEW mytable_view (name, _date)
AS SELECT (myname, mydate)
FROM mytable;

And the rule is as follows:

CREATE RULE update_mytable
AS ON INSERT TO mytable_view
DO INSTEAD (
    INSERT INTO mytable(myname, mydate)
    VALUES(NEW.name, NEW._date);
);

Now I try to insert into the table using the view by doing this:

INSERT INTO mytable_view(name)
    VALUES("Mozzart");

However, I get an error saying:

  ERROR:  null value in column "mydate" violates not-null constraint

Why is this? I expected the default value to be used if the value being inserted is null or wasn't specified. Is this not how default works?

To begin with: for a simple view like that you don't need a rule (at least not in any current Postgres version).

The reason you get that problem is this:

INSERT INTO mytable(myname, mydate)
VALUES(NEW.name, NEW._date);

Now if you don't supply value for mydate when inserting into the view, then NEW._date will be null, and thus you are trying to put an explicit NULL value into that column. You need to take that situation into account:

INSERT INTO mytable(myname, mydate)
VALUES(NEW.name, coalesce(NEW._date, current_date));

Unrelated but: you have another problem in your statement: "Mozzart" is a column name, string literals need to be enclosed in single quotes: 'Mozzart'. Postgres should have thrown a column "Mozzart" does not exist error.

For details, please see the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

Finally: your view definition is wrong:

SELECT (myname, mydate)

selects a single column that is an anonymous structure with two elements. You need to get rid of those useless parentheses, the CREATE VIEW should have thrown an error CREATE VIEW specifies more column names than columns.

You need to use:

CREATE VIEW mytable_view (name, _date)
AS
SELECT myname, mydate --<< NO parentheses!
FROM mytable;


Are there limitations on size or record count in MySQL? Are there any limitations to MySQL such as size or record limits?

The biggest tables in the database I am working on have over 150,000 rows.  Will MySQL be able to handle that?

Here are a couple resources from the docs on MySQL limitations.

  Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables. [src]

  There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table.  [src]

  Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. [src]

Bottom line is, MySQL can easily handle your scenario.


Finding and fixing InnoDB index corruption I encountered a new issue yesterday with one of my MySQL 5.5 slave DBs that runs on EC2 in AWS. The DB was created from a snapshot of another slave. The data is correct, but for at least one table a secondary index is returning incomplete results. Querying a child table by the parent id was returning 498 rows when it should have returned 504. Querying the missing 6 rows by primary key worked, and the correct parent id was returned, so the problem is with the secondary index.

This problem is highly concerning to me since presumably even if all of the data on the slave matches the master I will still get incorrect results from some queries run on the slave.

My brute force solution was to rebuild the entire table like this:

alter table my_table engine = innodb;

That resolved the issue for this specific table, but I'm left with the following questions:

1) How can I determine if other tables have similar index corruption?
2) What's the most efficient way to fix the corrupt indexes?

I've found some good resources online for finding and fixing InnoDB data corruption, but haven't found anything relevant for InnoDB index corruption.

I looked in the MySQL error log and didn't find a smoking gun. I did find some troubling InnoDB errors. I'm assuming this is a separate issue, but it could be related:

130109  9:46:41  InnoDB: unable to find a record to delete-mark
InnoDB: tuple DATA TUPLE: 2 fields;
 0: len 4; hex 04af1f21; asc    !;;
 1: len 4; hex 0a1c03bd; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 04af1f21; asc    !;;
 1: len 4; hex 0a052a77; asc   *w;;

Thanks Rolando and Michael for your responses.

To close the loop, here's the answer I came up with for my original questions:

Q: How can I determine if other tables have similar index corruption?
A: Use CHECK TABLE. I ran mysqlcheck -c on all of the relevant InnoDB tables to find out which ones had index corruption
Q: What's the most efficient way to fix the corrupt indexes?
A: Use OPTIMIZE TABLE to rebuid the InnoDB table that have corrupt indexes. This causes a complete table rebuild which fixes the corruption.


SQL query with GROUPING SETS to get subtotals with a HAVING clause Let's say I have a (fictional) table City with the following columns:

Country: string
State: string
City: string
Population: integer

I want a query that shows the total population by State, by Country and overall, excluding states below 1,000,000 inhabitants.

SELECT Country, State, SUM(Population) AS [Total]
FROM   City
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )
HAVING SUM(Population) < 1000000
ORDER  BY Country, State

The problem with this query is that the Country and Overall totals will include the population of states below 1,000,000 inhabitants (meaning the total of states doesn't match the total of its parent country).
(This is assuming that the countries have over 1,000,000 inhabitants)

How can we amend the query above to fulfill the requirements?

I'm using sql azure...

You have to apply the "having clause"  before grouping set

SELECT Country, State, SUM(Population) AS [Total]
FROM
(
    SELECT Country, State, SUM(Population) AS [Population]
    FROM   City
    GROUP  BY Country, State
    HAVING SUM(Population) > 1000000
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )
--HAVING SUM(Population) > 1000000
ORDER BY Country, State

here it's a sample :

;with City
AS
(SELECT 'Count1' as Country,'St1' as State,'C1' as City,100 As Population Union ALL
 SELECT 'Count1', 'St1' , 'C2', 50 Union ALL
 SELECT 'Count1','St2' , 'CCC 1', 50 Union ALL
 SELECT 'Count1','St2' , 'CCC 2 ', 40
)

SELECT Country, State, SUM(Population) AS [Total]
FROM
(
    SELECT Country, State, SUM(Population) AS [Population]
    FROM   City
    GROUP  BY Country, State
    HAVING SUM(Population) > 100
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )
--HAVING SUM(Population) > 100
ORDER BY Country, State

output:

Country State   Total
                150
Count1          150
Count1  St1     150

dbfiddle here


How do I remove the excess white space from the values in my SQL table? still kinda new to SQL.

My problem is this, say I created a table with one of its columns at nchar(200). This column contains the value 'Logged in.'. Every time I called it from the front-end, it returns 'Logged in                                                  '. Is there any way to remove the white space within the table? Or is there some setting that will remove this excess white space? I want it to return 'Logged in.' only. L/Rtrim don't seem to work.

Thanks

The first thing you should do is change the datatype of the column to nvarchar(200),

The char and nchar datatypes are intended for data that is more or less fixed length. Your example shows that you are not dealing with fixed length 200 character strings.

Currently each instance of the 'Logged in' string takes 400 bytes. Stored as nvarchar it will take 20 bytes (and 11 as varchar).

Then you can RTRIM in an UPDATE statement to remove the trailing spaces.

NB: In some circumstances you might find that creating a new table from scratch actually works better than the above as the insert can be minimally logged and it doesn't copy the data once and then trim it. It also ensures that no space is consumed by the dropped column. You can use the SSMS table designer to generate the script for this and then add a RTRIM to the result for something like the below.

SET XACT_ABORT ON;
BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_YourTable
  (
     /*Other columns*/
     YourColumn NVARCHAR(400) NULL
  )

GO

INSERT INTO dbo.Tmp_YourTable WITH (TABLOCKX)
            (YourColumn)
SELECT /*Other columns*/
       RTRIM(CONVERT(NVARCHAR(400), YourColumn))
FROM   dbo.YourTable WITH (HOLDLOCK TABLOCKX)

DROP TABLE dbo.YourTable

GO

EXECUTE sp_rename
  N'dbo.Tmp_YourTable',
  N'YourTable',
  'OBJECT'

GO

COMMIT


Error 0x80131904 failed to load assembly id 65536 / HRESULT: 0x8013150A for microsoft.sqlserver.integrationservices.server I am running SSIS Packages through an Agent job and getting error 0x80131904:

Message:

  Executed as user: NT Service\SQLSERVERAGENT.
  Microsoft (R) SQL Server Execute Package Utility
     Version 14.0.3023.8 for 64-bit
     Copyright (C) 2017 Microsoft. All rights reserved.

  Started:  1:00:00 PM
  Failed to execute IS server package because of error 0x80131904. Description:
  An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

  System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

  System.IO.FileLoadException:
        at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
        at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
        at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
        at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
        at System.Reflection.Assembly.Load(String assemblyString)

  Source: .Net SqlClient Data Provider
  Started:  1:00:00 PM
  Finished: 1:00:00 PM
  Elapsed:  0.328 seconds.

  The package execution failed.
  The step failed.

I researched and found that I should enable CLR and set TRUSTWORTHY to ON for the DB. I did that and in addition checked the database owner account; it's enabled and not orphaned. Still getting the error. I would highly appreciate any help on this.

Have you tried this:

USE <DATABASE>;
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO

First Enable Trustworthy database property

ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
GO

And change the database owner to ‘sa’

USE [DatabaseName]
GO

EXEC sp_changedbowner 'sa'
GO


How do I increase the storage available for a SQL Server instance on AWS RDS? The Amazon docs state that you cannot increase the storage allocated to a SQL Server RDS instance:

  Note that you cannot change the storage capacity nor the type of storage for a SQL Server DB instance due to limitations of striped storage attached to a Windows Server environment.

Oh well, so I guess you take a snapshot and restore it to a new instance with more storage... except that seems to be impossible too:

  Amazon RDS does not support changing the storage configuration for a Microsoft SQL Server DB instance when restoring from a DB snapshot.

That quote may refer to changing between general SSD and provisioned I/O, but there's no --allocated-storage parameter for restore-db-instance-from-db-snapshot, so it does seem to be impossible when restoring a snapshot.  For that matter, simply specifying a new number of IOPS gives an error:

  Cannot restore this instance based in Windows OS because the request has a different iops than the backup.

What is the most convenient way to increase the storage space allocated to a SQL Server instance?  Is there nothing better than Database Migration Services in this situation?

Amazon support confirmed that S3 and DMS are the only options for SQL Server.

This question has been raised on SE.  I've opened this question provisionally; if there's a consensus in Meta that we should not duplicate DBA questions which exist elsewhere on SO, I'll delete this one.

2019: Some time since I raised this question, it seems that Amazon lifted some restrictions.  I was able to resize a SQL Server instance without taking any special steps: just edit the config and allow it to reboot.

What is the most convenient way to increase the storage space allocated to a SQL Server instance? Is there nothing better than Database Migration Services in this situation?

Native backup and restore through S3 might be an alternative to DMS. However it also has it's cons:

Requires you to stop writing to the existing database while the backup/restore is performed in order to ensure the latest data is included.
Won't work for databases larger than 1TB in size (limit on backup size).
If you've already run out of space on RDS, the backup stored procedure might not work.

AWS explains the procedure in their Import/Export documentation for SQL Server, here are the headlines:

Spin up a new RDS instance with the desired disk capacity
Ensure both source and target databases have an option group that includes SQLSERVER_BACKUP_RESTORE
Stop writes to the source database
Use the stored procedure rds_backup_database on the source instance to backup your database(s) to an S3 bucket you have permissions to write to
Use the stored procedure rds_restore_database on the destination database to restore the backup
Point your clients to the new RDS instance


What is the proper way to relate many to many? I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in one table for data integrity and UX purposes. I have a shipment table and I want to join a personal_data.

What I'm thinking is:

shipment has one shipper_information which has one personal_data
shipment has one reciever_information which has one personal_data
shipment has one notify_information which has one personal_data

The thought behind this is it would be easier to separate the specific role in the shipment process.

Or would to be a better idea to relate these in a many to many join table and separate the specific role (shipper, reciever, notify) in the front end?

i.e.shipment has many shipment_personal_data which has many personal_data?

If the model is stable and there are always just these three references from shipment to personal_data, plain columns with FK constraints are the right and most efficient way to do it. Something like:

CREATE TABLE shipment (
  shipment_id serial PRIMARY KEY
, shipper_id  int NOT NULL REFERENCES personal_data
, receiver_id int NOT NULL REFERENCES personal_data
, notify_id   int NOT NULL REFERENCES personal_data
, ... -- more columns
);

NOT NULL may or may not fit your requirements.

Related:

How to implement a many-to-many relationship in PostgreSQL?

A junction table like you considered in closing could make sense for a varying number of references and/or references of dynamic nature (some types of references may be added / removed dynamically). More flexible, but takes up more storage and leads to more complicated queries.


Optimal way to record and retrieve a time-dependent value I have a table that has transactions for a bus (boarding riders). Given the route ID and the date, I need to look up in another table what service type it was doing that day. Bus schedules change at most every 6 months or so, with most going years unchanged.

Currently the schedule table is defined like so:

CREATE TABLE [dbo].[Routes](
    [ID] [int] NOT NULL,
    [RouteID] [int] NOT NULL,
    [Type] [varchar](50) NOT NULL,
    [StartDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
));

An example might look like:

ID  RouteID  Type          StartDate
--  -------  ------------  ----------
 1      301  Standard      2015-01-01
 2      301  Discontinued  2016-06-01
 3      302  Standard      2015-01-01
 4      302  ParaTrans     2017-01-01

So, if I have a transaction from 2015-04-20 for RouteID 301, I want to get back "Standard", but if the transaction is from 2018-01-20, it should return "Discontinued". For transactions prior to 2015-01-01, it should return NULL (or "", or anything other than a result that might conflict with a valid answer, i.e., "Standard", "Paratrans", or "Discontinued").

Basically, the table represents that route 301 was a standard route between 2015-01-01 and 2016-05-31 (and thus any transactions during that period should be categorized as "Standard"), then it was Discontinued on 2016-06-01 (through current day, implicitly as there is no later schedule change noted), while 302 was a Standard route from 2015-01-01 through 2016-12-31, then a ParaTrans(it) route after.

Route   Type          Start       End
-----   ----          -----       ---
301
        Standard      2015-01-01  2016-05-31
        Discontinued  2016-06-01  Present
302
        Standard      2015-01-01  2016-12-31
        ParaTrans     2017-01-01  Present

Currently, the query to do this looks like this:

SELECT
    TRANSIT_DAY,
    ROUTE_ID,
    (SELECT TOP (1) Type FROM Routes
     WHERE (RouteID = dbo.DAILY_SALES_DETAIL.ROUTE_ID)
     AND (StartDate <= dbo.DAILY_SALES_DETAIL.TRANSIT_DAY)
     ORDER BY StartDate DESC) AS NCTD_MODE
FROM dbo.DAILY_SALES_DETAIL

Questions

What I'd like to know is: Is this the most effective combination of (a) structure of the Routes table and (b) query to achieve this result? In other words, could a more efficient query be used with the existing structure? Could a change to the route table allow for a more efficient query?

Considerations

The table of transactions is imported from a vendor on a daily basis, and so changing the schema of that table is not trivial and it is preferred to be avoided. More importantly, this lookup is used across a number of tables and databases using transactions or other bus related data sourced form multiple vendors; this is just a single example. We have one vendor (and thus one database) for monetary transactions, another for rider count, and still another for performance, and so on, with the route number and date being the only reliably consistent identifier across them all.

The route table has an index of (RouteID, StartDate). Currently there are 56 rows in the Route table and 26M rows in the transaction table. The route table is comprised of 45 routes, and currently there are no routes with more than 2 rows, or one change. There is no limit to how many changes a single route could have, but I include this stat to show that the number is likely to remain small for the foreseeable future.

I can add any required indexes in order to optimize a suggested query. The question is more about finding the best strategy, assuming all reasonable optimizations are made to the considered strategies, than about finding the best optimization of a particular strategy.

db<>fiddle here

You could increase performance of your setup, as shown in your question, by changing the dbo.Routes table to:

CREATE TABLE dbo.Routes(
      RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , CONSTRAINT PK_Routes
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate DESC)
) WITH (DATA_COMPRESSION = PAGE)
ON [PRIMARY];

The key here is we're defining the clustered index, which is the table, on the compound of RouteID and StartDate DESC.  This provides the data in exactly the way that is most efficient for the query you've written.  The caveat emptor here is inserts into the dbo.Routes for an existing route with a new date will cause page splits to occur since we'll be filling rows in descending order of date.  Having said that, with a small number of rows in the Route table, and with the occasional index maintenance, this should not be a big concern.

Instead of doing that, I would consider modifying the dbo.Routes table to include an EndDate column.  This eliminates the need to do a subquery with TOP(1) and ORDER BY ....   Something like:

CREATE TABLE dbo.Routes(
      RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , EndDate datetime NOT NULL
    , CONSTRAINT PK_Routes
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate ASC)
);

Note the clustered index is now on (RouteID, StartDate ASC).

The query can now use an INNER JOIN, instead of the correlated subquery, and looks like:

SELECT
      t.TRANSIT_DAY
    , t.ROUTE_ID
    ,  NCTD_MODE = r.Type
FROM Transactions t
    INNER JOIN dbo.Routes r ON t.ROUTE_ID = r.RouteID
        AND t.TRANSIT_DAY >= r.StartDate
        AND t.TRANSIT_DAY < r.EndDate
ORDER BY t.TRANSIT_DAY
    , t.ROUTE_ID;

This allows SQL Server to do a simple inner loop join to obtain results.  Granted, if you are returning a large amount of rows, there will be a significant sort required, that will probably spill to tempdb.

Using the MCVE I've shown below, we can compare plans for the the two variants.  The first plan is your original query with the correlated subquery.  The second plan is with the EndDate column included.

The 2nd variant has a plan cost which is about 4 times lower than the 1st variant.  The sort operators in both plans are requesting 108MB of memory and spilling over 9,000 pages to tempdb - however it's fairly unlikely you'll be requesting the entire result set vs getting a single route, or perhaps a date range.  If you add a filter for a single route, there is no large memory grant or spill to tempdb.

What follows is a sample MCVE with 10,000 route rows, and 1,000,000 transaction rows, that can be used to run tests against various designs:

Do this in tempdb to avoid any "accidents" with real tables.

USE tempdb;

Drop the tables if they exist (this works on SQL Server 2016+):

DROP TABLE IF EXISTS dbo.Routes;
DROP TABLE IF EXISTS dbo.Transactions;

Create the dbo.Routes table, with a clustered index on RouteID, StartDate DESC:

CREATE TABLE dbo.Routes(
        RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , CONSTRAINT PK_Routes
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate DESC)
);

Insert 10,000 route rows:

;WITH src AS (
    SELECT t.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))t(n)
)
, src2 AS (
SELECT RouteID = (s1.n * 1000) + (s2.n * 100) + (s3.n * 10)
    , Type = REPLICATE(CHAR(65 + CONVERT(int, CRYPT_GEN_RANDOM(1) % 26)), 50)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
)
INSERT INTO dbo.Routes (RouteID, [Type], StartDate)
SELECT s.RouteID
    , s.Type
    , StartDate = DATEADD(DAY, ROW_NUMBER() OVER (PARTITION BY RouteID ORDER BY s.RouteID) - 1, '1997-01-01T00:00:00')
FROM src2 s

Create the dbo.Transactions, with a clustered index on ROUTE_ID, TRANSIT_DAY.  Building the clustered index like that optimizes queries that filter on both route and day.

CREATE TABLE dbo.Transactions(
     TRANSIT_DAY datetime NOT NULL
    , ROUTE_ID int NOT NULL
    , CONSTRAINT PK_Transactions
        PRIMARY KEY CLUSTERED
        (ROUTE_ID, TRANSIT_DAY)
);

Insert 1,000,000 rows into the dbo.Transactions table:

;WITH src AS (
    SELECT t.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))t(n)
)
INSERT INTO dbo.Transactions (TRANSIT_DAY, ROUTE_ID)
SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '1997-01-01') + DATEADD(MILLISECOND, ABS(CONVERT(int, CRYPT_GEN_RANDOM(4))), '00:00:00')
    , r.RouteID
FROM dbo.Routes r
CROSS JOIN src s1
CROSS JOIN src s2

For a Routes table with an EndDate column that can be used for comparative tests, I used this:

CREATE TABLE dbo.RoutesEndDate(
      RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , EndDate datetime NOT NULL
    , CONSTRAINT PK_RoutesEndDate
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate ASC)
);

INSERT INTO dbo.RoutesEndDate (RouteID, [Type], StartDate, EndDate)
SELECT r.RouteID
    , R.Type
    , R.StartDate
    , EndDate = COALESCE(LEAD(r.StartDate) OVER (PARTITION BY r.RouteID ORDER BY r.StartDate), GETDATE())
FROM dbo.Routes r

Query both tables for a specific route:

SELECT
      t.TRANSIT_DAY
    , t.ROUTE_ID
    ,  NCTD_MODE = (
        SELECT TOP (1) Type
        FROM Routes r
        WHERE (r.RouteID = t.ROUTE_ID) AND (r.StartDate <= t.TRANSIT_DAY)
        ORDER BY r.StartDate DESC
        )
FROM Transactions t
WHERE t.ROUTE_ID = 750
ORDER BY t.TRANSIT_DAY
    , t.ROUTE_ID;

The plan for the above query:

I/O and time Stats:

Table 'Routes'. Scan count 1000, logical reads 2142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transactions'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2 ms,  elapsed time = 2 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

Query for all transactions/routes:

SELECT
      t.TRANSIT_DAY
    , t.ROUTE_ID
    ,  NCTD_MODE = (
        SELECT TOP (1) Type
        FROM Routes r
        WHERE (r.RouteID = t.ROUTE_ID) AND (r.StartDate <= t.TRANSIT_DAY)
        ORDER BY r.StartDate DESC
        )
FROM Transactions t
ORDER BY t.TRANSIT_DAY
    , t.ROUTE_ID;

The plan:

Nasty spill to tempdb for sort operator:

If we modify the clustered index on dbo.Transactions to be (TRANSIT_DAY, ROUTE_ID), and re-run the full query, we see a plan without that ugly sort and spill-to-tempdb:


PL/SQL - Member Function: Link directly to object without parameters I want a table generated from a table function in oracle.

Like this:

+--------------------------------------------------------------------+
| CUSTOMERS                                                          |
+----+-------+------------+-------------+---------------+------------+
| ID | FIRST |    LAST    | LOCATION_ID | CREATION_DATE | FULL_YEARS |
+----+-------+------------+-------------+---------------+------------+
|  1 | Max   | Mustermann |          54 | 2017-10-05    |          0 |
|  2 | Hans  | Dimitry    |          87 | 2016-12-26    |          1 |
|  3 | Olga  | Trausch    |          71 | 2015-06-02    |          3 |
+----+-------+------------+-------------+---------------+------------+

But I want that the column FULL_YEARS is a member function of my object customer.

So I implement my customer object as

CREATE TYPE "CUSTOMER" AS OBJECT (
    customer_id           NUMBER,
    customer_firstname    VARCHAR2,
    customer_lastname     VARCHAR2,
    customer_location_id  NUMBER,
    creation_date         DATE,
    MEMBER FUNCTION "FULL_YEARS"(in_date DATE) RETURN NUMBER
);
/

My customer object body

CREATE OR REPLACE
TYPE BODY CUSTOMER AS

  MEMBER FUNCTION "FULL_YEARS"(in_date DATE) RETURN NUMBER AS
    v_years NUMBER;
  BEGIN
    SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, in_date)/12) INTO v_years
    FROM dual;
    RETURN v_years;
  END "FULL_YEARS";
END;
/

As example I declared an anonymous plsql block for testing.

DECLARE
  v_customer CUSTOMER;
BEGIN
  v_customer := CUSTOMER(2001, 'Hugo', 'McKinnock', 16, SYSDATE - INTERVAL '18' YEAR);
  DBMS_OUTPUT.PUT_LINE(v_customer.FULL_YEARS(v_customer.CREATION_DATE));
END;
/

But, how can I directly access in my member function of the column creation_date from my customer object. I don't want the parameter in_date of my member function.

I want only a call like this for example

 DBMS_OUTPUT.PUT_LINE(v_customer.FULL_YEARS());

Volker

Forget my comment.  I was thinking of something else.

Your question is really about "How do I access the MEMBER attributes from within a MEMBER function?".

In this case, you use the keyword SELF as the current instance's name.

Spec

CREATE TYPE "CUSTOMER" AS OBJECT (
    customer_id           NUMBER,
    customer_firstname    VARCHAR2(30),
    customer_lastname     VARCHAR2(30),
    customer_location_id  NUMBER,
    creation_date         DATE,
    MEMBER FUNCTION "FULL_YEARS" RETURN NUMBER
);
/

Body

CREATE OR REPLACE
TYPE BODY CUSTOMER AS

  MEMBER FUNCTION "FULL_YEARS" RETURN NUMBER
  as
     v_years  NUMBER;
  BEGIN
    -- don't do the SELECT INTO FROM DUAL thing.  that is just ugly code.
    v_years := FLOOR(MONTHS_BETWEEN( sysdate, self.creation_date)/12);

    RETURN v_years;
  END "FULL_YEARS";
END;
/