ORA-40341: Access violation on model storage object in Oracle? While I was trying to drop a table, it throws following error in Oracle SQL Developer:

ORA-40341: Access violation on model storage object

The tables are temporary tables created while pushing into the database using Oracle R Enterprise. The names of the tables are: DM$PRORE$21_473, DM$PGORE$21_473, ...

I need to drop all these tables as these tables have occupied large space of my database. While googling, I found this link but it provides no solution clues.

Database tables created using ore.push will have the prefix ORE$. Normally, these are automatically cleaned up when the R session ends or the ORE db connection terminated (unless you save the ore.frame objects in an ORE datastore).

In-database models from Oracle Data Mining (ODM)  as created from ore.odmSVM will also normally be deleted automatically and the end of the R session or ORE db connection termination. You should not attempt to remove DM$ tables as they are part of the in-database model. If you do delete any of these, their corresponding models will be corrupted/invalid.

You can invoke

SELECT name FROM dm_user_models;

to see the list of models in the database for your schema.

You can drop ODM models using the following in SQL*Plus or SQL Dev:

BEGIN
  DBMS_DATA_MINING.DROP_MODEL('MY_MODEL');
END;

Lots of thanks goes to Mark Hornick, Senior Manager, Oracle Advanced Analytics for his constant support in ORE implementation in my project.


Migrate replication from SQL Server 2008 R2 to SQL Server 2016 - Old to new hardware I will be working on a project shortly that would involve moving the replicated databases off of old to the new SQL Server instance. I haven't done this in the past so I don't have much knowledge either on how to move ahead except to do some research.

As of this date our current config sits on a Windows 2008 R2 server with SQL Server 2008 R2 and publisher along with the distributor is the same SQL Server instance e.g sqla and the subscriber is sqlb and also now sqlb is acting as a publisher and distributor for sqlc... these are all pull subscribers with one way transactional and snapshot replication.

Now, back to my original question. Is there a way to migrate the databases off of the old to the new with least downtime so I don't have to script out and reinitialize subscriptions as it would take a lot of time (that's what most of the people mentioned on google). Do we have any hacks or tricks to make this work without causing problems or if there is any step by step to work it out even if we use script method.

I am going to that with my same type migration this past February, I had to do all systems in one day. I had all my servers prepped before had with the exact same folder structure and included the folder name MSSQLSERVER10.  I shut down all services and disabled them.  I then copied all the system databases to the exact same folder location on the new system.  I started SQL Services only to ensure systems came up.  Once that was confirmed I brought over 1 of the replicated systems to ensure they come up.  Once that was successful, I brought over everything else, turned the systems on and waited a bit to start the agent as I wanted to check and ensure every db came back online.  I then turned on agent service on Primary first and then started all of the secondaries.  Forcing replication, or rebuilding it was not an option for me.  There are other ways to do it but this was the one I decided to use for the environment we have.  I was luckily enough to have an outage window to complete this.


should I denormalize for user profiles? Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing name, email, and a few other columns.

Say the app is a Life Dashboard, where each user profile has all kinds of disparate data, from favorite movies, to money spending statistics, to pictures. And since its a dash, we want to return all of it to the single page app upon user login.

Is this a use case for denormalization? without denormalization, it would require multiple queries just to return the first request: an authorization query, followed by one for each of the potentially 10-15 different normalized tables on userID. is this common? or in this case is denormalization or even switching to NoSql the preferred method? or case by case depending on how whether we want each entity in the normalized table represented and queryable?

Normalization is not done to make reading data easier or more efficient.  Normalization is done to make changing data simpler and less risky.  It's when you insert, update or delete data that you will see the benefits of normalization.

Therefore, if you are going to update your data much less frequently than you are going to read it, for example, if your data almost never changes, then optimizing for reading might make good sense.  However, if you are going to be adding new data and changing existing data pretty often then you will be better off with Third Normal Form (3NF) as your starting point for your database design.


How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres? Here is an example of the json object

rawJSON = [
   {"a":0, "b":7},
   {"a":1, "b":8},
   {"a":2, "b":9}
]

And I have a table that essentially looks like this.

demo Table

id | ...(other columns)      | rawJSON
------------------------------------
0  | ...(other columns info) | [{"a":0, "b":7},{"a":1, "b":8}, {"a":2, "b":9}]
1  | ...(other columns info) | [{"a":0, "b":17},{"a":11, "b":5}, {"a":12, "b":5}]

What I want is to return a row which insideRawJSON has value from "a" of less than 2 AND the value from "b" of less than 8. THEY MUST BE FROM THE SAME JSON OBJECT.

Essentially the query would similarly look like this

SELECT *
FROM demo
WHERE FOR ANY JSON OBJECT in rawJSON column -> "a" < 2 AND -> "b" < 8

And therefore it will return

id | ...(other columns)      | rawJSON
------------------------------------
0  | ...(other columns info) | [{"a":0, "b":7},{"a":1, "b":8}, {"a":2, "b":9}]

I have searched from several posts here but was not able to figure it out.
Extract JSON array of numbers from JSON array of objects
How to turn json array into postgres array?

I was thinking of creating a plgpsql function but wasn't able to figure out .

Any advice I would greatly appreciate it!

Thank you!!

I would also like to avoid CROSS JOIN LATERAL because it will slow down the performance.

There is no way around unnesting the array for every row (the drawbacks of de-normalizing data). But you don't need a cross join, an EXISTS condition should work just fine.
select *
from demo d
where exists (select *
              from jsonb_array_elements(d.rawjson) as r(jdoc)
              where (jdoc ->> 'a')::int < 2
                and (jdoc ->> 'b')::int < 8);

If your columns is not a jsonb (which it should be), use json_each() instead
Online example


Getting vacuumdb :invalid page header in block error while vacuuming I am newbie to PostgreSQL and using version 9.2.Disk space for data folder is full causing DB to stop. When i am trying to perform a vacuum to reclaim space, i am getting below error.

  vacuumdb: vacuuming of database "mcfdb3" failed: ERROR:  invalid page header in block 15871 of relation base/17148/30976

Can anybody help?

The error you pasted indicates that there has been corruption in your database.  The best approach to dealing with this kind of corruption is to shut down the database, make a complete copy of all of the files, and then restore them to another system where forensics can be performed.

If you have a complete copy of your database, such that you can retrieve any valid data from it, then you can use the "zero_damaged_pages" configuration parameter in PostgreSQL to have invalid or damaged pages wiped out entirely, allowing the database to progress even in the face of corrupt or invalid data.  Note that this option will cause you to lose valid data, as the entire page will be completely wiped out if any piece of it is deemed to be damaged or invalid.

Depending on the specific corruption which has occurred in this database, it may be possible to retrieve some of the data.  This retrieval is extremely time consuming and requires expertise knowledge of the database system and is generally only sensible when the data is extremely valuable.


TransactionLog not getting truncated in AlwaysOn Cluster Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing.
Only one database in the availability group, is refusing to truncate it's log file and rest of the databases in the AG are truncating upon tlog backup.
One of the readonly (applicationintent) secondary replica was rebooted yesterday and this was the only new activity that happened on the AG cluster.
Three node AG cluster. SQL Server 2016 SP1
I tried these steps

15 min Tlog backups working properly. Tlog backups are done on the secondary replica.
select name , log_reuse_wait_desc from sys.databases  --> NOTHING
dbcc opentran  --> No active open transactions.
select database_id, log_send_queue_size, log_send_rate,redo_queue_size,redo_rate from sys.dm_hadr_database_replica_states  --> redo_queue_size is 0 and log_send_queue_size is 0
Verified that data movement from primary to both secondary databases is working fine and realtime
There is no blocking on on any of the nodes in AG cluster.
There are no active long running transactions on any of the nodes in AG cluster.

I'd really appreciate any input. Thanks!

Restarting the secondary replica server and taking another fullbackup and then subsequent Tlog backup fixed the issue.


Can the replicated database be the publisher with SQL Server I need to setup replication for a Client but their production database server doesn't seem to have the components installed to enable publishing.

Is it possible to setup what should be the subscriber as the publisher and pull changes from the master database? And is this going to cause problems?

No.

The publisher is local to the data being published and can't be remote.

You can have a separate distributor of course

You'll have to consider database mirroring perhaps as an alternative or enable replication at the client


PostgreSQL 8.0 - Windows Server 2008 R2 install - Service won't start I am trying to install postgreSQL 8.0 on a windows server 2008 R2 machine. After the installation, the service does not start. When I try to start it manually, I get a message saying that the service started and stopped automatically. I tried running the service on a postgres account and as a local system account (in the service properties).

I don't know if this is relevant, but during the installation, I got a few alert messages about "procedural languages" and "contrib files" being installed but not activated on the database.

Every log file in pg_log is empty.

Does anyone know what's going on here?

Any suggestions would be much appreciated

Try uninstalling postgres, install this:

Microsoft Visual C++ 2008 Redistributable Package (x86)

Reinstall postgres and see if it works then.


Why do I need witness in WSFC with two nodes? I have Windows Server 2019 and SQL Server 2019 running in both nodes of my cluster. In my case everything is working fine without witness. I need Windows Server Failover Clustering (WSFC) for availability group which I'm using as DR solution.

When both nodes are running, I'm shutting down one node (which has primary database of my AG) and do failover of Availability group from another and vice-versa.  Can someone please explain why we need a witness in two node failover cluster or how can I recreate that scenario in which we'll need a witness.

Straight from the docs, with a two-node cluster without a witness:

  One node's vote is zeroed, so the majority vote is determined out of a
  total of 1 vote. If the non-voting node goes down unexpectedly, the
  survivor has 1/1 and the cluster survives. If the voting node goes
  down unexpectedly, the survivor has 0/1 and the cluster goes down. If
  the voting node is gracefully powered down, the vote is transferred to
  the other node, and the cluster survives. This is why it's critical to
  configure a witness.

Understanding cluster and pool quorum

And you can simply use Cloud Witness, so you don't need a shared disk, third node, or file share.


SQL Server Transactional Replication and Security Setup I am new to SQL Server replication. I was able to setup transactional replication successfully, but I feel like I am missing some important concepts regarding security and accounts. I have a few basic questions:

1 - Location of Snapshot.

Currently my distribution server and publication is on the same machine so my path is local and I guess it works. Besides distribution server does publisher also need access to "Snapshot" directory? Say what if my distribution server was on a different PC within LAN (I can use UNC) or maybe on a completely different remote location, then how would publisher access the snapshot?

2 - Publisher Security

What is the role of the Snapshot Agent here, what resources does it need access to? is it asking for the access to the physical "SnapShot" directory?

And what about Log Reader Agent, which resource does this user needs access to?

3 - Subscription Security

"Agent Process Account", what is the role of this user? what resources does this user needs access to at subscriber?

Firstly, it doesn’t matter how you configure your replication, the snapshot agent always runs on your distributor. It takes a snapshot of your publication and puts it in snapshot folder, then depends on your replication type, it will be send to subscriber by either distribution agent or merge agent. So publisher doesn’t need access to snapshot folder, actually no agent will be running on publisher.

Secondly, as the snapshot agent takes snapshot from publication and puts it in snapshot folder, it will need access to publisher and the folder. The log reader agent runs on distributor and reads transaction log on publisher then moves it to distribution database, so it will need access to both publication database and distributor database.

Thirdly, “Agent process account” here is the account setting for distribution agent. In transactional replication, distribution agent will apply the initial snapshot to subscriber and also takes transactions from distribution database to subscriber, so it will need access to both distribution and subscriber database, as well as snapshot folder.

If you need more information about replication, please refer to Book Online.

The only reason the snapshot directory need to be a share is when the subscribers are pull subscribers.Whatever account you are pulling with (the account the distirbution agent runs under on the subscriber, or the publisher login account) needs to be able to read the snapshot share and read files and list files and folders permissions on c:\snapshot.


SQL Logins in db_owner role on database cannot create stored procedures I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the account the db_owner role in the database. The developer reports that he can no longer create stored procedures.

I have validated this is true in this particular SQL 2014 Instance, I cannot reproduce it anywhere else. I have done about everything I can think of to fix this. The best clue I have is that I created a new SQL login, and gave it db_owner in the same database. It also cannot create a procedure. The original login cannot create procedures in other user databases even though it has db_owner.

Attempting to create a procedure gives:

  Msg 262, Level 14, State 18, Procedure TheProcedure, Line 30
  CREATE PROCEDURE permission denied in database 'DBTest'.

The error message is correct, CREATE PROCEDURE does not show up for the user, even after I explicitly assign it. The user can Alter any procedure. UDF creation gets a similar error:

  CREATE FUNCTION permission denied in database...

Anyone have an idea of what could be happening?

When I do a:

select * from fn_my_permissions(null, 'DATABASE')

CREATE PROCEDURE is not listed. I have checked for any DENY in sys.permissions, there is none.

The following query:

SELECT * FROM fn_my_permissions('xyz', 'USER');

returns:

IMPERSONATE, VIEW DEFINITION, ALTER, CONTROL

Interesting one - difficult to pin this one down. Have you thought about looking at the public role?

sp_helprotect 'CREATE PROCEDURE',NULL,NULL,'s'

Does that bring you back anything?


Why is intarray's push `+` so much faster than array-to-element concatenation `||`? When it comes to using arrays of ints, I always default to using intarray. But, now I'm wondering why is the default concatenation so much slower.

This answer probably requires some understanding of internals. Internally, intarray's calls intarray_push_elem, and || calls array_append

Sample Data

CREATE EXTENSION intarray;
CREATE TABLE foo AS
  SELECT ARRAY[x::int] AS bar
  FROM generate_series(1,1e7)
    AS gs(x);

Same result

SELECT ARRAY[1] + 42 = ARRAY[1] || 42;
 ?column?
----------
 t
(1 row)

With ||

EXPLAIN ANALYZE
  SELECT bar || 42 FROM foo;

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..198528.55 rows=9999884 width=25) (actual time=0.029..5193.752 rows=10000000 loops=1)
 Planning time: 0.051 ms
 Execution time: 5679.489 ms
(3 rows)

With + from intarray extension

EXPLAIN ANALYZE
  SELECT bar + 42 FROM foo;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..198528.55 rows=9999884 width=25) (actual time=0.026..2066.786 rows=10000000 loops=1)
 Planning time: 0.052 ms
 Execution time: 2388.462 ms
(3 rows)

Routinely intarray's + is faster than ||. Why is || so slow?

It seems as if || is doing a lot more for multidimensional arrays.

For instance with || catching the following case

SELECT ARRAY[ARRAY[1]] || 5;
ERROR:  argument must be empty or one-dimensional array

Whereas, with + the array is flattened..

SELECT ARRAY[ARRAY[1]] + 5;
 ?column?
----------
 {1,5}
(1 row)

In addition it seems intarray does not handle nulls.

SELECT ARRAY[null]::int[] + 5;
ERROR:  array must not contain nulls

It also seems || uses PG_RETURN_DATUM instead of passing by reference which seems to be what + uses with PG_RETURN_POINTER.


How to detect JSON data type support in MariaDB/MySQL My first thought was to check server version but it is not the nicest solution.
Is there something like PHP's function_exists() in mysql?

I'd like to detect JSON_EXTRACT() or the JSON data type: CREATE TABLE t1 (jdoc JSON);

Thank you.

I don't think there is any such feature in MariaDB or MySQL themselves at the moment (MariaDB 10.3, MySQL 5.7, 8.0).

So the only way I can imagine actually detecting support for specific data types and built-in functions on-the-fly would be to try using the data type or funtion, and then catching the exception, if any.

It's also possible that certain software libraries can give you a hint as to what features the underlying RDBMS can offer. If so, this is probably done by storing a list of features associated with each RDBMS version inside the software library.


mariadb multiline regexp_replace I'm trying to replace multiple lines using the mariadb regex_replace function.

CREATE TABLE `test` (
  `entry` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`entry`) VALUES ('LINE1\r\nLINE2\r\nLINE3\r\nLINE4\r\nLINE5');

This creates one entry which the following content:

LINE1
LINE2
LINE3
LINE4
LINE5

Let's say I want to replace LINE2 through LINE4 with ''. This should be the result:

LINE1
LINE5

I've tried so many different regexes, but I'm out of ideas, as none of these work (no lines are removed).

select regexp_replace(entry, 'LINE2.*LINE4', '') from test
select regexp_replace(entry, 'LINE2[.\r\n]*LINE4', '') from test
select regexp_replace(entry, 'LINE2[.\R]*LINE4', '') from test
select regexp_replace(entry, '(*CRLF)LINE2.*LINE4', '') from test
select regexp_replace(entry, '(*ANYCRLF)LINE2.*LINE4', '') from test

I've also tried selecting using RLIKE, but it also does not work (no entries returned):

select entry from test where entry RLIKE 'LINE1.*LINE4'
select entry from test where entry RLIKE '(*ANYCRLF)LINE1.*LINE5'

It should be possible, so I don't really know why this isn't working.

I'm using latest mariadb on debian (10.3.22).

To make sure this has nothing to do with the switch to PCRE2 in 10.5.1., I also check the historical mariadb pcre docs, but the syntax did not change.

This can be solved with the dotall-modifier (?s):

 select regexp_replace(entry, '(?s)LINE2.*LINE4\r\n', '') from test

More modifiers can be found here: https://mariadb.com/kb/en/pcre/#option-setting


SQL database mail configuration to use a domain account that is not the SQL Service account We have been asked to use a domain service account for everything releated to email (smtp). I looked at the SQL Database Mail and the only option that looked like Windows Auth. is the first one:

The problem is that the account we will use will not be the SQL service account.
Is there a way to make it work with a domain service account that is not the SQL Service account ?

Using Database Mail Configuration Wizard
You can achieve that by selecting the Basic authentication option and filling the user name in the Domain\Login format:

You can check the New Account Page doc to understand each parameter of the wizard.
Using Transact-SQL
You can also use the system stored procedure sysmail_add_account_sp to do that. Here I used the example from the doc and added the last three parameters that are related to your question:
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'AdventureWorks Administrator',
    @description = 'Mail account for administrative e-mail.',
    @email_address = 'dba@Adventure-Works.com',
    @display_name = 'AdventureWorks Automated Mailer',
    @mailserver_name = 'smtp.Adventure-Works.com',
    @username = 'Domain\Login',
    @password = 'yourPassWord',
    @enable_ssl = 1;

Security Implication
Notice that on both examples I'm using the SSL option because, as Dan Guzman pointed out, the use of that option

Specifies whether Database Mail encrypts communication using Secure
Sockets Layer

as you'll see in the doc. So, if you're not using a secure connection in your environment, those credentials, along with the email content, are gonna be sent in plain text to the SMTP mail server, therefore being a security issue.


What would you use ENUM for in SQL? I am trying to complete an assignment for my CIS class and I am required to include a data type called ENUM. I know that this stands for enumerated lists, but I am not sure when would be an appropriate time to use ENUM. A couple of the examples given in the book I am using were like a list of the continents. Could I use this to describe a list of departments for a workplace?

Generally enums are for static lists, i.e. lists that never change. Departments is a bad choice because these can change at any time. Continents is good because these are highly unlikely to change, but countries would be bad because these do change. Think of things that are constants, such as seasons, months, day of week etc... You want to keep enums small. As a general rule that I apply personally, if a enum needs 10 or more entries, I create a regular list.


How to completely restrict a postgres role to a single schema I'd like to create a readonly role for a third party to access a handful of tables from the public schema without being able to view the rest of the tables in public.

My initial idea was to create a new schema 'readonly' and then create views in that schema:

create view readonly.table1 as select * from public.table1

Then alter the search path for the readonly user to limit it only to the 'readonly' schema. However, it looks like I can still view the public schema as that role (though I can't select anything from any tables in schema public).

Is there a way to remove all visibility into the public schema from a role? Unfortunately, moving everything to a different schema is not possible.

You have to revoke the users rights to the schema

Its kinda hard to revoke public schema as its default is well public which is granted automatically to any user granted access to database and all users are added to the public role.

Commands like this have to be run

REVOKE ALL PRIVILEGES ON public FROM public;

There are lots of gotchas with this
here are a couple of articles going over how to do this READ them CAREFULLY and TEST  before doing it on a production machine

https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf

https://blog.dbi-services.com/avoiding-access-to-the-public-schema-in-postgresql/

EDIT FORGET TO STATE

I have never tried to grant access from one schema that reads a table from another schema that user does not have access to..  This may not work as security should be passed up the chain blocking the read.  I DO NOT KNOW


PostgreSQL: Use column with hash based on other columns as unique index? My previous problem is still not fixed: We cannot set a unique index because NULL values are allowed...

we use this table in our database:

CREATE TABLE offer (
   offer_id   serial PRIMARY KEY
 , product_id int NOT NULL REFERENCES product
 , price_old  numeric(10,2);
 , price      numeric(10,2);
 , price_alt  text          -- overrules price if present
 , valid_from timestamp NOT NULL
 , valid_to   timestamp     -- optional
 --   more attributes of the offer
 , CONSTRAINT some_kind_of_price_required
      CHECK (price IS NOT NULL OR price_alt IS NOT NULL)
);

And we use a unique index which doesn't work:
ALTER TABLE offers ADD CONSTRAINT offer_unique_index
UNIQUE(product_id, price_old, price, price_alt, valid_from, valid_to);

But the unique index doesn't work because price_old, price and price_alt can be NULL.... It's possible that only price is filled, only price and price_old or only price_alt...

Possible solution:

Now I might have a solution, but I don't know how to realize it exactly...

I thought if I could use NEWID() or MD5() to create an extra column named "uid" (which contains a hash based on the data of all other columns) and add a unique_index on that "uid" column, it could fix the problem. So when I try to insert the exact same values (prices, valid dates etc.), it will create a same hash (because the values are the same, obviously) and that triggers the unique index violation for the hash column.

Nice idea? But more important: possible?

You could use a UNIQUE INDEX instead of the UNIQUE CONSTRAINT using the function coalesce to treat null as a regular value for your uniqueness:

CREATE UNIQUE INDEX ON offer (
       product_id,
       coalesce(price_old,-1),
       coalesce(price,-1),
       coalesce(price_alt,''),
       valid_from,
       valid_to);

This will enforce your uniqueness as you described.

For details between a Unique constraint and a unique index,see:
https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index


What happens in Microsoft SQL Server 2012 .ldf when the database log file if full? Recently we found out that .ldf file of our  Microsoft SQL Server 2012 database was growing large. We have set limit as follows:

Now we have a question: What will happen when log .ldf file reaches its size limit?

Thank you

If the log is full, your database will fail any transaction that requires something written to the log (insert,update,delete, schema change, etc.).

From your screenshot, are you really planning to limit it to 15MB? How big is the database? How big is the largest table in the database? You also mention that it is growing very large. How do you define very large?

Generally speaking, you need your log file to be at least as big as your largest index, so that if you rebuild it, it won't fail. If the log has grown, then that's usually because you need a bigger log file. The only time you'd want to shrink the log file is if you know that a 1 off operation happened that will never happen again (e.g. some sort of data load/export). If your log file is growing significantly larger than your data files, you may want to check your recovery model - if it's full then you need to be taking log backups.


How to query for records based on most recent version I have the following table structure

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
3, 1, 1-3-01, active
4, 2, 1-1-02, proposal
5, 2, 1-2-02, cancelled
6, 3, 1-1-03, proposal
7, 3, 1-2-03, proposal
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active
10, 4, 1-3-04, active

Here is the SQL to recreate the table.

CREATE TABLE [dbo].[tableX](
    [ID] [int] NOT NULL,
    [Ordernumber] [int] NULL,
    [Date] [date] NULL,
    [Status] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [tableX]
    ([ID]
    ,[Ordernumber]
    ,[Date]
    ,[Status])
VALUES
    (1, 1, '1-1-01', 'proposal'),
    (2, 1, '1-2-01', 'proposal'),
    (3, 1, '1-3-01', 'active'),
    (4, 2, '1-1-02', 'proposal'),
    (5, 2, '1-2-02', 'cancelled'),
    (6, 3, '1-1-03', 'proposal'),
    (7, 3, '1-2-03', 'proposal'),
    (8, 4, '1-1-04', 'proposal'),
    (9, 4, '1-2-04', 'active'),
    (10, 4, '1-3-04', 'active')

I need to query for all records that have a most recent state of active, in the case of the example it is ordernumber 1 and 4.
Input parameter: Status = active
Result:

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
3, 1, 1-3-01, active
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active
10, 4, 1-3-04, active

Further I need to split this into history and current statuses so for state active and current the Id's 3 and 10. For state active and history the Id's 1, 2, 8 and 9.
Input parameters: Status = Active, Date = Most current
Result:

ID, ORDERNUMBER, DATE, STATUS
3, 1, 1-3-01, active
10, 4, 1-3-04, active

Input parameters: Status = Active, Date = Not most current
Result:

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active

I've tried a lot of possibilties, but I cannot find a query that works. Can someone help me out or point me in the right direction?

I am working in a SQL Server environment.

You can use ROW_NUMBER() and OVER clause to "group" (partition) the rows per ordernumber, then order them by date (descending) and keep only the first rows (WHERE rn = 1):

WITH cte AS
  ( SELECT  id, ordernumber, [date], status,
            rn = ROW_NUMBER() OVER (PARTITION BY ordernumber
                                    ORDER BY date DESC)
    FROM    tableX
  )
SELECT  id, ordernumber, [date], status,
FROM    cte
WHERE   rn = 1
  AND   status = 'active' ;

And I really do hope that the date column is a DATE column and not a CHAR one. Otherwise the above ORDER BY will not work as you expect.

After the last, edit, it seems that the date column is actually of DATE dataype, which is good.

But really, you should't use '1-3-04' format for input. For output it may be fine (the users of the application may like that dubious format) but for input, no. Because you can never be sure if the date that will actually be stored in the database will be 2004-01-03 or 2004-03-01 or 2001-03-04 (or some other.)

Use a format that is unambiguous, like '2004-01-03' (the ISO YYYY-MM-DD format) or even better (for SQL-Server specifically) the YYYYMMDD format: '20040103'. See the blog by Aaron Bertrand: Bad habits to kick : mis-handling date / range queries


What is a DBA's definition of "batch"? I've heard it being used in relation to SQLite sometimes but never completely understood what it means.

A batch is a series of SQL commands that are sent to the database server at once. This can be faster than sending each command individually because there is less network communication.

SQLite is an embedded database; SQL commands are executed directly by the SQLite library.
There is no separate server, so it would not make sense to have batches. SQLite does not have such batches.

Another meaning of "batch" might be the multi-row form of the INSERT command:

INSERT INTO MyTable(ID, Name) VALUES (1, 'this'), (2, 'that'), (3, 'whatever');


Postgres Materialized View Dramatically Increasing In Size I have a production postgres 10 database. Every hour I run "refresh materialized view concurrently" on my materialized view to recalculate the values; only a few new rows get added each day.

All the materialized views (and their indexes) are increasing in size dramatically. The view that should be 102 MB is now 1700 MB and the indexes have suffered similar inflation. And the refresh time has increased from 17 seconds to 10 minutes. The overall DB size has grown from 4 GB to 21 GB in the last day due to the views and their indexes growing. I can't do a non-concurrent refresh as these views are constantly being read from.

Running a refresh triggers a large amount of disk I/O.

I have no idea what is causing this issue. I have autovacuum enabled and configured. I have 8GB of memory, and work_mem is set to 256 MB.

My issue was running a "refresh materialized view concurrently" before the autovacuum had finished running, leading to table bloat. Now I manually vacuum analyze after every refresh.


Can I trust the Database Engine Tuning Advisor recommendations? Our database has become large, and we are trying to optimize performance.

Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with Profiler?

Is this a good way to optimize? How accurate are these recommendations?

The tuning advisor (DTA) can yield information about missing indexes, but the results are only as good as the workload you've provided and your ability to interpret the results and fill in the gaps.

Your .trc file may only contain certain events, not all, and may not cover a full business cycle. So if you have a big set of reports that people run at the end of the month, you may be missing the most important part of your workload.
DTA only considers improving the performance of read queries, and puts no thought whatsoever into what impact new indexes will have on the write portion of your workload. Indexes aren't free.
DTA has a tendency to recommend multiple redundant indexes, that only differ by one additional key column or an include column. In a lot of cases, one index might be able to satisfy both queries that generated the recommendation for the two individual indexes.

So, the information can be useful, but I would never let any client blindly implement all of the indexes. You need to take a lot more data points into consideration.

For example, how have you come to the conclusion that adding indexes will improve any performance issues you are having? Have you performed any baselines and identified any bottlenecks?


Global Temp Table Error when Stored Proc run by SQL Job on a Schedule I have a puzzling situation I am trying to confirm/solve.
A stored procedure I have written creates a series of Global Temp Tables and fires off an email if any records show up in the final one. This all works well. It has been tested over and over, so I am convinced that there is nothing amiss in the way the tables are being instantiated or accessed.
The stored procedure is then kicked off by a Job using the SQL Job Agent. This also works fine as long as I start the SQL Job manually.
But strangely, if the SQL Job is put on a schedule (say it runs every hour on the hour), the last Global Temp Table set to be created sometimes can't be found and the job sometimes fails while sending back an error:
Server Error Message = Invalid object name '##GlobalTempTableName'
But it doesn't fail every time. It might fail twice and the succeed the next time only to begin failing again the next hour. In addition, I have created a workaround to force the scheduled Job to succeed by setting it to make two more attempts before giving up. This solution has satisfied the stakeholders, because all they care about is that the SQL Job runs, they don't care HOW it runs.
But I am a very curious person and I would really like to know why this might be occurring.
Thanks for the geeky answers in advance.
Lars

With global Temp Tables, when the session that created it no longer exists and no other T-SQL statements are actively referencing the table, it gets dropped automatically.
Per Microsoft's Create Table docs:

Global temporary tables are automatically dropped when the session
that created the table ends and all other tasks have stopped
referencing them. The association between a task and a table is
maintained only for the life of a single Transact-SQL statement. This
means that a global temporary table is dropped at the completion of
the last Transact-SQL statement that was actively referencing the
table when the creating session ended.

This is only a guess, but perhaps whatever session that is creating the global temp table and code that is actively referencing it both stop before the Agent Job gets a chance to access it? (E.g. if you're using multiple steps in the Agent job, and it's a subsequent step that tries to access the global temp table.) That would make the difference from you just executing the whole thing in a new query window in one go.


Create bacpac from SQL Server 2008 Is it possible to export bacpac file from SQL SERVER 2008 (running 10.50.6220)? I can only see option for dacpac.

You can use Sql Database Migration Wizard or command line tool sqlpackage.exe for creating .bacpac file for SQL Server 2008.

Download + documentation for SQL Database Migration wizard:

https://sqlazuremw.codeplex.com

This is from Migration Wizard page, which mentions about supporting SQL Server 2008 version. Though in the download links it only starts from SQL Server 2008 R2 but I would give that a try. I have not tested anything below SQL Server 2012.

  SQL Database Migration Wizard (SQLAzureMW) is designed to help you
  migrate your SQL Server 2005/2008/2012/2014 databases to Azure SQL
  Database.

How to use sqlpackage.exe?

https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx?f=255&MSPPError=-2147217396

There is a step by step guide about using these tools.


SQL Server Management Studio 2012: How to have folders in the SSMS project? I'm using SSMS 2012 to talk to the SQL server 2012 and Azure SQL servers we use. I admit I'm not a SQL expert so I've been saving most of my SQL scripts for future reference. I've quickly run into 20 or so .SQL scripts in the SSMS project and they are all living under the same 'Queries' folder.

Is there a way I can create "sub-folders" in the project so I can organize my scripts properly? How do most other folks keep their scripts organized? I'm pretty sure if this bugs a novice like me, it must be a real issue for real admin (with potentially hundreds of scripts?)

Finally, subfolders were made possible in SSMS 2016.

Cautious users: You can install SSMS 2016 and still keep using old one. See note [1] below.

Once there, start using solutions instead of projects.

Subfoldering works like this:

Adding solution subfolder:

Right-click solution item (tree root) and from menu select Add > New Solution Folder.
Step 1 now works also on any folder you added.

Adding SQL script:

Right-click any folder and from its Add menu, either add existing SQL file or new SQL file (in the list, find item type which has SQL extension).

Your Good old projects (Bad old projects)

You can still host projects in your solution if you see them beneficial for some legacy reasons, for hosting connections, etc.  Just add new or import existing projects into the solution.

[1] Regarding upgrading:

SQL Server 2008 - 2016 are officially supported by SSMS 2016. See more information at download page. For older versions, you can keep your favourite old SSMS, see point 2 below.

the only setting which might require your attention is Options > SQL Server Object Explorer > Script for server version > (set your server version)

Different SSMS versions live well together, i.e. you can have SSMS 2008 and SSMS 2016 installed together without issues – tested.


mongodb - monthly archival of data Coming from a RDBMS background, this is what I have done in the past.

Create a table with partitions based on a date field. There are typically three tables (partitions) - current (M0), previous month (M1) and previous to previous month (M2).
Detach M2, download data and drop table. Create M2 (empty) again.

How do we achieve the same with MongoDB? I have read about Capped Collections and TTL setting. doesn't seem to fit the bill. There is ranged sharding too; but, we aren't such a huge installation requiring a clustered deployment.

The aim is to have a 'small' data set so that queries run efficiently and API invocations (depending on these queries) do not incur latency.

As at MongoDB 4.0, there isn't built-in support for partitioned collections but you can implement a similar pattern:

Your application would always write new entries to collection M0 (current)
You would set up a scheduled task to rotate collection names at the end of the month using renameCollection (renaming M1 to M2 and M0 to M1)
Your scheduled task would download the data from M2 and drop this collection when no longer needed

This approach should minimise storage fragmentation since your M0 collection is recreated every month rather than being long-lived, however it would limit your ability to easily run simple queries against data spanning both M0 and M1.

  I have read about Capped Collections and TTL setting. doesn't seem to fit the bill. There is ranged sharding too; but, we aren't such a huge installation requiring a clustered deployment.

Capped collections have a number of limitations and aren't appropriate for this use case. Capped collection growth is limited by size in bytes or number of documents, but you cannot control document expiry time. You also cannot directly delete documents in a capped collection as they are designed for FIFO (First In, First Out) use cases.

A TTL (Time-To-Live) index is a definite candidate for your use case. Documents have a TTL expiry date based on a number of seconds or a specific clock time. You could use the first type of expiry to smooth out deletions over time (so documents expire after ~62 days) or the second type of expiry to set a stricter date based on the first day of the month when the documents would normally be dropped as part of M2.

Sharding adds a lot of operational complexity if your only use case is removal of archival data. The relevant feature to consider is zone-based sharding which would allow you to associate contiguous ranges of a shard key with specific shards. This is more typical for an archival use case where older data should remain online but move to lower cost/performance storage. For more information see Tiered hardware for varying SLA or SLO in the MongoDB documentation.


How Selective Should Index Be Is there a general Selectivity rule of when to apply Nonclustered index?

We know not to create an index on a bit column, 50/50.   "Rows with 50/50 distribution, it might buy you very little performance gain " Index Bit Field in SQL Server

So how selective should a query be in SQL Server, before an index should be applied? Is there a general rule in SQL Server guidelines? At 25% average selectivity distribution in a column? 10% Selectivity?

This article is stating around 31%? How Selective Should Index Be?

Considering column selectivity only when deciding which columns to index ignores quite a bit of what indexes can do, and what they're generally good for.

For instance, you may have an identity or guid column that's incredibly selective -- unique, even -- but never gets used. In that case, who cares? Why index columns that queries don't touch?

Much less selective indexes, even BIT columns, can make useful, or useful parts of indexes. In some scenarios, very un-selective columns on large tables can benefit quite a bit from indexing when they need to be sorted on, or grouped by.

Joins

Take this query:

SELECT COUNT(*) AS records
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id = p.OwnerUserId;

Without a helpful index on OwnerUserId, this is our plan with a Hash Join -- which spills -- but that's secondary to the point.

With a helpful index -- CREATE INDEX ix_yourmom ON dbo.Posts (OwnerUserId); -- our plan changes.

Aggregates

Likewise, grouping operations can benefit from indexing.

SELECT   p.OwnerUserId, COUNT(*) AS records
FROM     dbo.Posts AS p
GROUP BY p.OwnerUserId;

Without an index:

With an index:

Sorts

Sorting data can be another sticking point in queries that indexes can help.

Without an index:

With our index:

Blocking

Indexes can also help avoid blocking pile-ups.

If we try to run this update:

UPDATE p
SET p.Score += 100
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656;

And concurrently run this select:

SELECT *
FROM   dbo.Posts AS p
WHERE  p.OwnerUserId = 8;

They'll end up blocking:

With our index in place, the select finishes instantly without being blocked. SQL Server has a way to access the data it needs efficiently.

In case you're wondering (using the equation Kumar provided) the OwnerUserId column's selectivity is 0.0701539878296839478

Wrap it up

Don't just blindly index columns based on how selective they are. Design indexes that help your workload run efficiently. Using more selective columns as leading key columns is generally a good idea when you're searching for equality predicates, but can be less helpful when searching on ranges.


in-memory DB startup time SQL Server I have a question on amount of time it takes for an in-memory OLTP SQL Server 2019 database (no Accelerated DB recovery configured) to come online.
I have 2 tables each with 40GB in size (in-memory) part of the database. My question is, does SQL Server have to load the complete 2 tables into memory first before bringing the DB online?
Let's say each table takes 20seconds to load from disk to memory depending on the disk throughput, can we say the DB will not be available in online state for at least 40 seconds (2 *20 seconds for each table) + regular recovery process of analysis,redo and undo phases for DB recovery?
Is my understanding correct? Can I get some pointers for the above concepts please?

Not only must all memory-optimized data be loaded into memory before any tables are available in the entire database, but because non changes to indexes on memory-optimized tables are logged, all indexes must be recreated on all memory-optimized tables as well.
That's why it's critical to have fast storage, and spread your containers across multiple volumes.
http://nedotter.com/archive/2016/09/in-memory-oltp-the-moving-target-that-is-rto/
http://nedotter.com/archive/2016/02/backup-and-recovery-for-sql-server-databases-that-contain-durable-memory-optimized-data/


2 Linked Servers using MSDASQL with conflicting Provider settings I have a linked server problem on a SQL Server 2012 database.

Linked Server 1 accesses a sql server 2000 database using sql server native client 10.0 and MSDASQL provider.

Linked Server 2 accesses a mysql database using mysql odbc 5.1 driver and MSDASQL provider.

If Level Zero Only is unchecked on the MSDASQL provider Linked Server 1 works but Linked Server 2 does not ("An invalid schema or catalog was specified for the provider “MSDASQL” for linked server").

If Level Zero Only is checked on MSDAQL provider Linked Server 1 does not work (error as above) but Linked Server 2 does.

Is there a way to provide the value of Level Zero Only on the provider string in the linked server settings? Or can anyone explain why this scenario is happening and how to fix it. Thank you!!

couldn't figure it out so I just solved it using openquery instead of selecting from linked server! not great but time constraints needed it solved.


Delete an oracle instance, the right way I want to delete an oracle instance (oracle 10.2.0.4.0) created in the AIX 6.0 OS.
I know i can delete all the dbf and ctl files in the Terminal but i thinks that's no the best way to do it. I think must be a cleaner way to do that.

Thanks in advance.

You can delete databases with DBCA which takes care of most of it.

Or you can do as below, but this will do the same as removing the datafiles, redo logs, controlfiles manually.

sqlplus / as sysdba
startup mount exclusive restrict
exit
rman target /
drop database including backups noprompt;
exit

After this, you still have to remove the entry that belongs to the database from /etc/oratab, remove init.ora/spfile, password file from $ORACLE_HOME/dbs, and clean log directories (adump, bdump, cdump, udump).


Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore? TL;DR: Un-stoppable, unusable postmaster is being spawned when Postgres is started right after restoring its data directory from a WAL base backup. Why?

Context:

We run postgresql 8.4, on CentOS 6, using the PGDG packages. We have a script for use on developer test environments that restores a nightly backup of our production server's data directory (created between calls to pg_start_backup and pg_stop_backup). The script decompresses the file, and uses restore_command to reapply any WALs that were generated during the time that the backup was taken on production.

It usually works fine, and restores hundreds of times faster than an SQL-based restore of a pg_dump'ed file.

Problem:

Sometimes, after it unzips the data dir, the script starts postgres by running /etc/init.d/postgresql start (which is a symlink to /etc/init.d/postgresql-8.4. This makes it a predictable init script for when we eventually upgrade to 9.*). It reports "OK", as in: it started correctly. Then WALs don't restore; it hangs indefinitely waiting for a recovery.done file to appear.

What I've Tried:

When I ran /etc/init.d/postgresql status during the indefinite hang, the init script reports dead but pid file exists.

Then I ran ps -ef | grep post. Oddly, the postmaster process and archivers etc were running. All of the invocation parameters were correct (right datadir etc etc).

When I ran psql, it detected a running postmaster and an initted postgres DB, but did not detect the main DB--the one we care about restoring via the WAL script.

I then checked the perms on the data dir, and everything looked OK.

Running /etc/init.d/postgresql stop reported "OK", and killed the archiver/watcher processes, but the postmaster stayed running.

The same thing happened when I tried killall -r '*.postmaster*.'.

The only thing that worked to resume the stuck WAL restore was a killall -s 3 -r '.*postmaster.*' (Signal 3 is SIGQUIT), and then a /etc/init.d/postgresql start.

I checked pg_startup.log and the daily files in pg_log during the unmanageable state, and everything looked fine. pg_startup.log registered a successful start as the last entry.

Possible Causes:

A couple of (I think minor) things are nonstandard about our config.

Our init script is symlinked, as I said before, to a version-agnostic script at /etc/init.d/postgresql. This points wherever we want it to. At present it points to /etc/init.d/postgresql-8.4.
Our postgresql.conf file lives in /etc/ (with an owner and group of the postmaster user), and has a symlink into the data directory. Our WAL restore script ensures that the symlink is re-created before attempting to start postgres.
We recently upgraded our infrastructure from Postgresql 8.4.11 to 8.4.12. We are testing the new version for stability. Our production servers are running 8.4.11. However, we are pulling data off of them via pg_dump, scrubbing it, and then 'packaging' it for WAL restore elsewhere (on 8.4.12), so we're not restoring WALs across incompatible versions of Postgres.

Question:

Why is it doing this? Is one of the possible causes listed below possibly to blame?

In general if you are seeing problems of this sort, it may be best to take them up on the pgsql-bugs list.  People there can help figure out what information to gather to help determine what the scope of this misbehavior is and help fix it for you.

Also 8.4.11 to 8.4.12 wal restore should work just fine.

If this is only occasionally happening, I don't think your explanations get there.  It sounds like something that really could use additional troubleshooting by people who can determine if a code fix is required.


How do I delete only related records in a multi-key MERGE in SQL Server? Suppose you have something like this:

Source table (variable):

Values (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)

Target table:

Mapping (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)

I want to merge Values into Target with the following rules:

Match on source.LeftId = target.LeftId AND source.RightId = target.RightId

when matched in target, update CustomValue
when not matched in target, insert

Delete any unmatched values in the target that do match a LeftId in the source, i.e. only delete records that related to the LefIds of what I'm merging.

(That last rule is hard to describe, sorry!)

For instance:

Source:

1, 10, foo
1, 11, foo

Target:

1, 10, bar
1, 12, foo
2, 20, car

Merge result:

Result Target:

1, 10, foo (updated)
1, 11, foo (inserted)
1, 12, foo (deleted)
2, 20, car (unchanged)

So...

Here's what I have so far, which takes care of update and insert:

MERGE Mapping AS target
USING (SELECT LeftId, RightId, CustomValue FROM @Values)
  AS source (LeftId, RightId, CustomValue)
  ON target.LeftId = source.LeftId
    AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
  UPDATE SET
    CustomValue = source.CustomValue;

How do I do the delete part of my rule?

You can filter out the rows you need to consider from the target table in a CTE and use the CTE as the target in the merge.

WITH T AS
(
  SELECT M.LeftId,
         M.RightId,
         M.CustomValue
  FROM @Mappings AS M
  WHERE EXISTS (SELECT *
                FROM @Values AS V
                WHERE M.LeftId = V.LeftId)
)
MERGE T
USING @Values AS S
ON T.LeftId = S.LeftId and
   T.RightId = S.RightId
WHEN NOT MATCHED BY TARGET THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (S.LeftId, S.RightId, S.CustomValue)
WHEN MATCHED THEN
  UPDATE SET CustomValue = S.CustomValue
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
;


MySQL "Out of sort memory" error on a simple GROUP_CONCAT query I have a (what it seems to me) simple query which fails to run on a simple table, the error is:

Out of sort memory, consider increasing server sort buffer size

These are the SQL statements which I'm trying to run:

CREATE TABLE `permissions` (
  `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`)

)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

INSERT INTO `permissions` (`id`, `name`) VALUES
  (1,'ocjena_dobavljaca.view'),
  (2,'ocjena_dobavljaca.edit'),
  (3,'ocjena_dobavljaca.delete'),
  (4,'zaposlenici.view'),
  (5,'zaposlenici.edit'),
  (6,'zaposlenici.delete'),
  (7,'vatrogasni_aparati.view'),
  (8,'vatrogasni_aparati.edit'),
  (9,'vatrogasni_aparati.delete'),
  (10,'organizacijska_shema_drustva');
COMMIT;

SELECT
    SUBSTRING_INDEX(name, '.', 1) as n,
    COUNT(*) as num,
    GROUP_CONCAT(name)as nn
FROM permissions
GROUP BY n

You can check it in this SQL Fiddle.

This answer states:

  While raising sort_buffer_size can help queries queries with GROUP BYs and ORDER BYs, you are better off improving the queries that you can improve and adding indexes that can be used by the Query Optimizer.

but I don't see any way to improve the query or add indexes to the table, as they are quite simple.

Increasing the sort_buffer_size from 64K (default) to 256K solves the issue, but I'm not sure if this is the right solution.
The other solution is to make the query with a subquery (two SELECTs), which seems a bit redundant:

SELECT
  n,
  COUNT(*) as num,
  GROUP_CONCAT(name)as nn
FROM (
    SELECT
        SUBSTRING_INDEX(name, '.', 1) as n,
        name
    FROM permissions) a
GROUP BY n;

Can anyone explain why the error?

It looks like your name column concatenates two items.  Try building the table with the column split.

CREATE TABLE `permissions` (
  `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `object` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `permission` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `object_permission` (`object`, `permission`)
d
)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

INSERT INTO `permissions` (`id`, `name`) VALUES
  (1,'ocjena_dobavljaca','view'),
  (2,'ocjena_dobavljaca','edit'),
  (3,'ocjena_dobavljaca','delete'),
  (4,'zaposlenici','view'),
  (5,'zaposlenici','edit'),
  (6,'zaposlenici','delete'),
  (7,'vatrogasni_aparati','view'),
  (8,'vatrogasni_aparati','edit'),
  (9,'vatrogasni_aparati','delete'),
  (10,'organizacijska_shema_drustva','');
COMMIT;

SELECT
    object,
    COUNT(*) as num,
    GROUP_CONCAT(permission)as nn
FROM permissions
GROUP BY object;


Problem understanding an execution plan I'm new to execution plans in SQL Server and I'm trying to understand why SQL Server is doing some operations. The query is:

select P.PERSONID
from KSS_V_EMPLEADOS_PER_3_CERRADOS p, COMBHOMEACCT c, LABORACCT L, kss_carga_empl_centros k, CALENDAR ca
where p.PERSONID = c.EMPLOYEEID
and c.LABORACCTID = l.LABORACCTID
and k.empleado = p.PERSONNUM
and convert(date,ca.CALENDARDTM,103) between DBO.KSS_PrimerDiaMes(convert(DATE,@DFECHA, 103)) and DBO.KSS_UltimoDiaMes(convert (DATE,@DFECHA, 103))
and convert(date,ca.CALENDARDTM,103) between p.Inicio and p.Fin
and convert(date,ca.CALENDARDTM,103) between convert(date,c.EFFECTIVEDTM,103) and convert(date,c.EXPIRATIONDTM-1,103)
and convert(date,ca.CALENDARDTM,103) between k.FECHA_INI and k.FECHA_FIN
and l.LABORLEV4NM <> k.CENTRO

Table Information

KSS_V_EMPLEADOS_PER_3_CERRADOS is a view that has no indexes and involves some tables and functions. It has 12827 rows
COMBHOMEACCT c is a table with 16771 rows
LABORACCT is a table with 2460 rows
kss_carga_empl_centros is a table with 6594 rows
Calendar is a table with 146462 rows (it's just a table with a column that has one row for every day)

I'm able to understand most of the plan, but just before the last Inner join it does a Index spool that returns 830563 rows and I don't know why it is doing this index spool and cannot find where this number come from. I'm able to calculate all the other row counts without problem, but this branch of the tree calculates the days of the current month and passes it to the index spool operator. But what is this operator doing and why it returns 830563 rows?

I've uploaded the plan, just in case someone wants to see it:

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

If you need more details please ask me

Thanks in advance

The eager index spool is accessed multiple times, each time fetching about 4 rows.

It's called by the Row Count Spool (note that the number of rebinds matches the number of executions):

Which is called iteratively by the Nested Loops Join after the Sort operator:

I'd be a bad consultant if I didn't also mention that using multi-statement table valued functions, and converting dates (some with date math) on the fly will often have a negative performance impact on queries. I realize that's not what your question is about, but hey.

If you'd like to learn more about spools (and many other operators), Fabiano Amorim has an excellent series of articles:

Operator of the Week – Spools, Eager Spool

Hope this helps.


Index rebuild log growth How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are trying to build azure database indexes and it has  a limitation of max 2 GB.

My database is in full recovery model.

Table Size:

log size:

from the pictures you can see log generation was very less with online index rebuild operation.Can some one correct me if i am missing anything

If you are rebuilding a clustered index, you are essentially rebuilding the table, but a non-clustered index is just a subset of the table (usually). If you use the SORT_IN_TEMPDB option, you will offload some of the logging to the tempdb transaction log. Version will also matter, in 2005 online rebuilding is minimally logged but was changed back to fully logged in 2008. You might also want to take a look at this article which might be useful in deciding whether to rebuild or reorganize with the log size in mind.

----Update----

Using Shanky's test setup I performed the same steps, but I added a few extra size checks.
Before the rebuild, I perform a log backup and check the log space and utilization as well as the records in the transaction log for my index. Then I do the rebuild test and recheck the log size and the number of log records:

backup log IndexLogging to disk = 'NUL'
--check log space/usage
dbcc sqlperf (logspace)
--check log records for the index, exclude the where for the total number of logs generated
SELECT LogRecordsGenerated = COUNT(*)
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING'
--Perform your rebuild operation and then recheck the same parameters

I tried this about a dozen times and each time I get the same results, the DMV is only telling you for the transaction, but there are several nested transactions that are spawned as a result of the log_test transaction that don't show up including the allocation of extents and pages and the insertion of the data. If you look at the actual contents of the log file, you can see how the offline rebuild is very efficient. It just allocates the pages/extents, formats them and sets them and then deallocates the old ones. An online rebuild is doing a lot more work since it has to keep the index available while it rebuilds.  This is probably more evident in the locking: offline it locks the entire object but online it has to go page by page, key by key.  You can take a look for yourself to compare:

SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitName],
[AllocUnitId],
[Page ID],
Description,
[Lock Information], [Transaction ID]
FROM FN_DBLOG(NULL,NULL)

You can reduce the log generated by temporarily switching to BULK_LOGGED while your index maintenance runs, but you lose the ability to do point in time recovery for that period of time.  Apparently it also does not reduce your log backup.

--More info on log usage from DBCC SQLPERF (logspace) and SELECT LogRecordsGenerated = COUNT(*)
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING' to show actual log sizes, note the index is only 648KB

This is the log after populating it with data:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219        70.89956            0

LogRecordsGenerated

10903

Here is the log after a log backup:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219        5.100313            0

LogRecordsGenerated

0

Log after log backup and then Online rebuild:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219        22.642              0

LogRecordsGenerated

11160

Log after log backup and then offline rebuild:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219    10.79338    0

LogRecordsGenerated

140


Pivoting on Date Category I have some data in a DB, which I'm trying to get out into a spreadsheet programmatically.  The data is a dollar value for a number of categories.  Each fortnight, each category has a value added.

The data looks like this (simplified):

PayDay      Category        Average
2016-12-01  Entertainment   $32.32
2016-12-01  Extra Expenses  $39.18
2016-12-01  Shopping        $591.13
2016-12-15  Entertainment   $34.34
2016-12-15  Extra Expenses  $42.73
2016-12-15  Shopping        $577.69
2016-12-29  Entertainment   $34.34
2016-12-29  Extra Expenses  $53.22
2016-12-29  Shopping        $580.08

But I want to be able to select directly from the DB as:

PayDay      Entertainment   Extra Expenses  Shopping
2016-12-01  $32.32          $39.18          $591.13
2016-12-15  $34.34          $42.73          $577.69
2016-12-29  $34.34          $53.22          $580.08

Been trying, but I can't get my head around it.

Here are examples of both a STATIC and DYNAMIC PIVOT

First, let's create a test table

--Create demo temp table
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
    DROP TABLE #TestTable
Create Table #TestTable (
    PayDay Date
    ,Category varchar(20)
    ,Average DECIMAL(11, 2)
    );

--insert some test data
insert into #TestTable values ('2016-12-01','Entertainment',32.32);
insert into #TestTable values ('2016-12-01','Extra Expenses',39.18);
insert into #TestTable values ('2016-12-01','Shopping',591.13);
insert into #TestTable values ('2016-12-15','Entertainment',34.34);
insert into #TestTable values ('2016-12-15','Extra Expenses',42.73);
insert into #TestTable values ('2016-12-15','Shopping',577.69);
insert into #TestTable values ('2016-12-29','Entertainment',34.34);
insert into #TestTable values ('2016-12-29','Extra Expenses',53.22);
insert into #TestTable values ('2016-12-29','Shopping',580.08);

Example of STATIC PIVOT where the Category column has a fixed set of known values.

--Example of STATIC pivot if the Categories are fixed
;
WITH cte_TestTable
AS (
    SELECT PayDay
        ,Category
        ,Average
    FROM #TestTable
    )
SELECT *
FROM cte_TestTable
PIVOT(SUM(Average) FOR Category IN (
            [Entertainment]
            ,[Extra Expenses]
            ,[Shopping]
            )) AS pvt
ORDER BY Payday;

Now, let's add another row to the test table with a new category

--Insert a new category for use in a DYNAMIC PIVOT
insert into #TestTable values ('2016-12-29','Bar Hopping',1000.08);

And now an example of a DYNAMIC PIVOT to handle variable categories

--Example of DYNAMIC PIVOT
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Category)
                    from #TestTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT PayDay, ' + @cols + ' from
             (
                select *
                from #TestTAble
            ) x
            pivot
            (
                SUM(Average)
                for Category in (' + @cols + ')
            ) p '

execute(@query)


Moving Local PostgreSQL 8.4.20 Database to Amazon EC2 or RDS I'm running PostgresSQL 8.4.20 on a local server and plan to migrate the data to either Amazon EC2 or RDS. I have working knowledge of databases and need advice from experts. I've been researching this topic and I've come across some helpful and similar answers like this and this already.

I'm leaning towards Amazon RDS because of automatic backups and less maintenance. But will I be able to install v8.4 on RDS? Or do I need to upgrade to v9.3+? Will moving data from v8.4 to v9.3 cause any issues? If so, maybe it is best to install v8.4 on EC2?

Amazon's RDS only offers PostgreSQL versions 9.3.x, and it seems unlikely that they'll ever offer to host older versions of Postgres. So by jumping from a local 8.4 install directly to RDS, you would in effect be making two significant changes at once (jumping up several Postgres versions, as well as switching to managed hosting). That may be alright or not -- it all depends on what features you're using and depending on.

You should do some reading on RDS's limitations (no external hot standby, limited extensions, no shell access to the database instance, etc.) and benefits (hopefully much less maintenance work) and decide whether it's right for you.  Also, I suggest you walk through the steps of dumping and restoring your data into RDS and ensure your application works OK, as well as reading through the Postgres major-version release notes for 9.0, 9.1, 9.2, and 9.3, paying particular note to the incompatibilities listed to see if any of them would affect you.


Join two tables using pivot combining two columns in one table I have two tables in this format:

Job_Skill
ID_Skill  Min_Job  Idea_Job   Max_Job
=====================================
1        0           0       1
2        0           1       1
7        1           1       1
8        1           1       1

Job_Education
Degree_Job  Field_Job  Min_Job   Idea_Job   Max_Job
===================================================
7        37         0         0         1
7        106        0         1         1
13       37         1         1         1
13       106        1         1         1

Desired Result
Criteria   1  2  7  8 [ 7  37 ] [ 7 106 ]  [13 37]   [13 106]
=============================================================
Min        0  0  1  1    1         1         0          0
Ideal      0  1  1  1    0         1         1          1
Max        1  1  1  1    1         1         1          1

How can I achieve this using a pivot, or any other method?
I am using SQL Server 2014. The columns are not fixed; they are dynamic. A static version is also acceptable, as I just want to understand how I should approach this.

You can UNPIVOT and then PIVOT.

Half of it is below (untested):

WITH P1_Src As
(
SELECT ID_Skill, Val, Col
FROM Job_Skill
UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U
), P1 AS
(
SELECT Col, [1], [2], [7], [8]
FROM P1_Src
PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P
)
SELECT *
FROM P1

You would need to apply a case expression to get the desired Criteria string, then extend the example to do the same for the other table and join the two results on Criteria.

To make this dynamic, you would need to look at the places in the query that currently have hard-coded values, extract the distinct values from the table instead, and use these instead of the hardcoded values.

For an example of the basic technique, see Dynamic pivot: sum of sales per month or search for "dynamic pivot".


How to view tuples changed in a PostgreSQL transaction? BEGIN;

   INSERT INTO foo_table(foo_column) VALUES('a'),('b'),('c');

   -- SELECT * FROM pg_stat_activity WHERE ...
   -- shows records with a flag if they have been added/deleted/updated

END;

Is there a way to view the uncommitted inserts/deletes of a transaction for a given table in PostgreSQL 9.x? This is purely for visual confirmation.

While my initial thought is no (just perform a SQL-Select of the table), certain users wanted to visualize differences within the transaction.  I could see how this might be helpful if having a lot of savepoints and transactions. Thus, it was worth asking to the community at large.

You can inspect the internal xmin to identify newly inserted row versions (note, that includes results from UPDATE):
SELECT * FROM foo_table
WHERE  xmin::text = (txid_current() % (2^32)::bigint)::text;

How so?
The function txid_current() returns the current transaction ID. However, while the internal transaction ID type in xmin is 32 bits wide and wraps around every 4 billion transactions,  txid_current() returns a bigint quantity, per documentation:

that is extended with an "epoch" counter so it will not wrap around during the life of an installation.

There is a thread on pgsql-hackers with Tom Lane discussing it.
I derived the safe WHERE condition from it.
Addressing comment: Deleted rows that were deleted in the same transaction are always invisible either way, so not included in the result.
Disclaimer
However, this does not work with subtransactions started with SAVEPOINT (or some other way, plpython can use subtransactions, too). Those spawn separate xids and there is currently no way to get the full list of xids belonging to the top transaction returned by txid_current(). In my search for a solution I found this closely related thread on pgsql-hackers:

Re: rows modified in current transaction

I don't currently see a reliable solution working around that limitation.
Related:

Can I select data inserted in the same uncommited transaction?


Autovacuum is not running even after configuring it on I am using aurora-postgresql version 9.6.8. and I have one main instance , one writer and one reader replica.

I am looking for below information:

How I can check on a particular instance when autovacuum was executed when I am trying to use "pg_stat_user_tables" this show lot of dead tuple. But when we see max last autovacuum executed date its one month back. Seems like autovacuum not executed from last one month on any of the table. In my environment we have huge insert update and delete every day.

Thank you in advance for any suggestion!!!

Below is my auto vacuum configuration:

name    setting
autovacuum_analyze_scale_factor 0.05

autovacuum_analyze_threshold    50

autovacuum_freeze_max_age   200000000

autovacuum_max_workers  4

autovacuum_naptime  5

autovacuum_vacuum_cost_delay    5

autovacuum_vacuum_cost_limit    -1

autovacuum_vacuum_scale_factor  0.1

autovacuum_vacuum_threshold 50

maintenance_work_mem    4122624

vacuum_cost_limit   200

vacuum_freeze_min_age   50000000

Did you contact AWS support? The version 9.6.8 aurora-postgresql has a metadata corruption bug that could lead to this symptom that you have described. You probably need to upgrade.

I have recently encountered this problem twice in production, and AWS support was helpful in each event.


Merging multple pg_dumps with columns using auto key I have two unique instances of Postgres using the same database schema but with independent datasets. One of the tables has a column setup for auto increment and it is a primary key. No other tables depend on this primary key.

Is there a way to merge these two databases together and have the import mechanism reassign new keys (the auto increment column) on this specific table only?

What I would do is,

Start a transaction.
Update all constraints on the database you're merging in to ON UPDATE CASCADE
Set all of the ids to be offset by the max(id) of the table you're merging into, with a comfortable margin (so you know they won't overlap at the time of merger)
Merge them in.
Restart the sequence at a number equal to or greater than the maxid of the table's column.

Sample

CREATE TABLE foo ( foo_id serial PRIMARY KEY );
INSERT INTO foo DEFAULT VALUES;
CREATE TABLE bar ( foo_id int REFERENCES foo );
INSERT INTO bar (foo_id) VALUES (1);

Now

BEGIN;
  ALTER TABLE bar
    DROP CONSTRAINT bar_foo_id_fkey ,
    ADD CONSTRAINT bar_foo_id_fkey
      FOREIGN KEY (foo_id)
      REFERENCES foo(foo_id)
      ON UPDATE CASCADE;
  UPDATE foo SET foo_id = foo_id + 1e6;
COMMIT;

Now you've got,

TABLE bar;
 foo_id
---------
 1000001
(1 row)

Now when you load db2, set the max id to max(foo_id) on the seq.

SELECT setval('foo_foo_id_seq', (SELECT max(foo_id) FROM foo), true);
 setval
---------
 1000001
(1 row)

And, now when the next row is inserted, it calls nextval (under the hood), and gets

SELECT nextval('foo_foo_id_seq');
 nextval
---------
 1000002
(1 row)

If you're doing this kind of a stuff a lot, look at using UUID's instead of integer sequences.


MySQLWorkbench with MariaDB 10.0 Performance tab not working I'm using MySQLWorkbench 6.3.3.0 (build 592) and I cannot use the performance tab on a Mariadb10 connection.

This is the error I receive:

  This feature requires MySQL version 5.6.6 or newer

The error as you can see is that workbench cannot see that MariaDB 10 has the performance schema and is assuming that the MySQL version is lower than 5.6.6 which somewhat is true as I'm not using MySQL.

I can assume that this is a compatibility issue between MySQLWorkbench and MariaDB 10?

AFAIK MariaDB is supposed to be compatible with MySQL 5.6 or am I wrong about that?

Has anyone a workaround for this?

This is on purpose and has nothing to do with the performance schema. MySQL Workbench is designed for MySQL and is only guaranteed (and tested) to work with MySQL servers (and checks this explicitly in certain places). And even then there are sometimes problems, caused by features not available in older versions. Let alone derivative work like MariaDB.


Optimizing a four-year-old MySQL database I developed an application with MySQL 5.1 back-end four years ago. After that, I only gave remote support a couple of times, but that too for application crashes, and not for database. The database is still running well. The customer takes the backup on decided schedules. I want to optimize the database as I think in these years many records have been deleted and many added.

How do I tune up the database and bring it into proper shape? What things need attention?

I want to optimize the database as I think in these years many records
  have been deleted and many added.

You have some other reason for wanting to fiddle with a database that is "running well"?

Good reasons might include:

The backups are getting unmanageably large
The customer has mentioned a specific area of performance they want improved

If you don't have a good reason, then my advice would be to do nothing


could not open log file "/var/log/postgresql-2015-05-24_181212.log": Permission denied I installed postgresql from source on Ubuntu 14.04 (I also installed from repositories too before, I just want to get a feel for both of them right now, as I will heavily rely on this database). So when I installed from source, in my postgresql.conf, I specified I want all standard error from the postmaster executable to go to /var/log:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

I know that only root can write to /var/log (and that is the defaults on Ubuntu), so how are people doing this? How can you make that log file writable before it even exists? Or are people manually creating the log file first and then changing its permissions?

Unfortunately, my temporary solution is to hard-code a filename e.g. postgresql-9.4.2-main.log for log_filename and then as root create that file in /etc/log and then change its ownership to postgres, and then when I start the server it works fine. But I want the log file to be dynamic. After it reaches a specified size, I want the old one to be zipped up, and a new one created with the current date and time.

I know that only root can write to /var/log ...

You can change that. As root user (or with sudo) in the shell:

mkdir /var/log/postgresql
chmod 0700 /var/log/postgresql
chown postgres:postgres /var/log/postgresql

And in postgresql.conf:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = '1d'          # That's what makes it "dynamic"

Depesz has a detailed article on the topic:
https://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/


What is difference between mongodump and mongoexport? I know that when we have to take full backup at database & collection level we use mongodump but with mongodump we can take backup of a particular record also so what is difference between mongodump and mongoexport?

mongodump generates binary copies of data; it creates better, more efficient backups.

mongoexport can create JSON files; these can be used by other programs, and are basically human-readable as is.


Calling a View from Procedure I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters.

Ex:

 CASE pSearchType
  WHEN 1 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1 ;
  WHEN 2 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1
      AND Col2 = pInput2;
  WHEN 3 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1
      ORDER BY Col4;
  END CASE;

So is it advisable to have:
1) The SQL Statement as a View and Call the View from procedure with the Where clause and Order By added?
* Good for maintenance *

2) Or Have the SQL Statement inside each of the CASE statement?

Which one will be preferred way for performance?

PS: My procedure will have parameters that will have NULL (mean Optional) and those parameters will be used in where conditions.

Thanks.

How about Dynamic SQL ?

SET @query = 'SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...';
CASE pSearchType
  WHEN 1 THEN
    SET @where = 'Col1 = pInput1';
  WHEN 2 THEN
    SET @where = 'Col1 = pInput1 AND Col2 = pInput2';
  WHEN 3 THEN
    SET @where = 'Col1 = pInput1 ORDER BY Col4';
END CASE;
SET @sql = CONCAT(@query,' WHERE ',@where);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Please make sure you create indexes or any appropriate tuning that will support each case.

Give it a Try !!!


Specify type on SQL Server Computed Column I have just added a computed column to a table, and it is showing up as an int and I'd like to make it a bit is this possible? I'm having a hard time finding any info on this. Below is the computed column:

IsActive  AS case when DateDecommissioned is null then 0 else 1 end PERSISTED

You should be able to CAST to  your preferred data type inside the CASE:

...IS NULL THEN CAST(0 as BIT) ELSE CAST(1 as BIT) END


PostgreSQL and default Schemas Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas:

Now from my understanding Schemas are like folders for organization, etc. So the question I have is are all these schemas needed when I create a new DB? If so what are they used for on PG side as I won't ever use them myself.

I can understand information_schema as this is default for an install of MySQL on a server, though I don't get why a database would need it's own as opposed to the entire server, but to each DB type his own I guess.

What you're seeing is mostly one user interface's way of displaying the structure of a PostgreSQL database.

If you were using pgAdminIII, which is just another administrative interface, each database would usually show two "catalogs": information_schema and pg_catalog. It would also have a schema named "public".

Database objects named "pg_*" are system objects. The pg_toast schema holds TOAST storage for large tables.

The information_schema views are part of the SQL standard. They're supposed to provide a vendor-independent way to determining information that's stored in vendor-specific system tables.

You can make a good case for not exposing every namespace as if it were a user-level schema. (Internally, these are namespaces. Try select * from pg_namespace;.)  The only schema in a newly created database that really matters to users is "public".


Sql profiler replay table I have captured a trace in profiler and stored the trace to both a table and an xml for replay. I open the xml and replay the trace on the same server/database with modified stored procedures (I want to check the performance of some changes). In the replay options I choose "save to table" to store the replay results for later analysis. The replay went well with a few errors about 'bulk inserts' which I accept since the profiler documentation states that it does not replay bulk events.

My problem is that the replay trace table does not have the same columns as my original trace! specifically the duration and cpu columns. I really need these in order to compare the two sets of results. Is there anything I am missing in the replay setup or anywhere?

Thanks
dat_girl

Are you using one of the default traces in Profiler or are you creating it manually?

If I were in the same situation, I would just save the trace to a regular text file and then import that file into a SQL Server table.

USE mydb
GO

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)
GO

At least that way you are certain to get the same columns everytime.   As to the BULK INSERT statements, you could try this filter.

Column Filter for TextData with the Not Like set to %BULK INSERT%.


SQL to read XML from file into PostgreSQL database How can I write SQL to read an XML file into a PostgreSQL XML value?

PostgreSQL has a native XML data type with the XMLPARSE function to parse  a text string to that type. It also has ways to read data from the filesystem; the COPY statement, among others.

But I don't see a way to write native PostgreSQL SQL statements to read the content from a filesystem entry and use that to populate an XML value. How can I do this?

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding).

This function works for any path, but needs to be created as superuser:

create or replace function stack.bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function stack.bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data
             from pg_largeobject
             where loid = l_oid
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

select convert_from(stack.bytea_import('/tmp/test.xml'), 'utf8')::xml;


How can I execute a PL/SQL procedure for Oracle via JDBC? I have a Java webapp with an Oracle DB, and I try to execute this statement at the end of a data migration script:

EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_SCHEMA', tabname => 'MY_TABLE', estimate_percent => dbms_stats.auto_sample_size);

It works in sql*plus, but not via JDBC:

"Error: ORA-00900: invalid SQL statement"

What am I doing wrong?

Instead of EXEC, you should put it between begin ... end, or use the { call } syntax.
Here is an example, in the documentation:

http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#i1014127


Why do sequential GUID keys perform faster than sequential INT keys in my test case? After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with newsequentialid(), and 2) a table with an INT primary key initialized sequentially with identity(1,1). I would expect the latter to be fastest because of the smaller width of integers, and it also seems simpler to generate a sequential integer than a sequential GUID. But to my surprise, INSERTs on the table with the integer key were significantly slower than the sequential GUID table.

This shows the average time usage (ms) for the test runs:

NEWSEQUENTIALID()  1977
IDENTITY()         2223

Can anyone explain this?

The following experiment was used:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000

WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN

DECLARE @LocalCounter INT = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @LocalCounter = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestInt (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @BatchCounter +=1
COMMIT
END

DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]
FROM TestGuid2
GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [IDENTITY()]
FROM TestInt
GROUP BY batchNumber

DROP TABLE TestGuid2
DROP TABLE TestInt

UPDATE:
Modifying the script to perform the insertions based on a TEMP table, like in the examples by by Phil Sandler, Mitch Wheat and Martin below, I also find that IDENTITY is faster as it should be. But that is not the conventional way of inserting rows, and I still do not understand why the experiment went wrong at first:
even if I omit GETDATE() from my original example, IDENTITY() is still way slower. So it seems that the only way to make IDENTITY() outperform NEWSEQUENTIALID() is to prepare the rows to insert in a temporary table and perform the many insertions as a batch-insert using this temp table. All in all, I don't think we have found an explanation to the phenomenon, and IDENTITY() still seems to be slower for most practical usages. Can anyone explain this?

I modified @Phil Sandler's code to remove the effect of calling GETDATE() (there may be hardware effects/interrupts involved??), and made rows the same length.

[There have been several articles since SQL Server 2000 relating to timing issues and high-resolution timers, so I wanted to minimise that effect.]

In simple recovery model with data and log file both sized way over what is required, here are the timings (in seconds): (Updated with new results based on exact code below)

       Identity(s)  Guid(s)
       ---------    -----
       2.876        4.060
       2.570        4.116
       2.513        3.786
       2.517        4.173
       2.410        3.610
       2.566        3.726
       2.376        3.740
       2.333        3.833
       2.416        3.700
       2.413        3.603
       2.910        4.126
       2.403        3.973
       2.423        3.653
    -----------------------
Avg    2.650        3.857
StdDev 0.227        0.204

The code used:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(88))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,1) PRIMARY KEY, rowNum int, adate datetime)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum, adate) VALUES (@LocalCounter, GETDATE())
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate, batchNumber)
SELECT adate, rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber)
SELECT adate, rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms, @IdTimeStart, @IdTimeEnd) AS IdTime, DATEDIFF(ms, @GUIDTimeStart, @GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp
GO

After reading @Martin's investigation, I re-ran with the suggested TOP(@num) in both cases, i.e.

...
--Do inserts using GUIDs
DECLARE @num INT = 2147483647;
DECLARE @GUIDTimeStart DATETIME = GETDATE();
INSERT INTO TestGuid2 (SomeDate, batchNumber)
SELECT TOP(@num) adate, rowNum FROM #temp;
DECLARE @GUIDTimeEnd DATETIME = GETDATE();

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber)
SELECT TOP(@num) adate, rowNum FROM #temp;
DECLARE @IdTimeEnd DateTime = GETDATE()
...

and here are the timing results:

       Identity(s)  Guid(s)
       ---------    -----
       2.436        2.656
       2.940        2.716
       2.506        2.633
       2.380        2.643
       2.476        2.656
       2.846        2.670
       2.940        2.913
       2.453        2.653
       2.446        2.616
       2.986        2.683
       2.406        2.640
       2.460        2.650
       2.416        2.720

    -----------------------
Avg    2.426        2.688
StdDev 0.010        0.032

I wasn't able to get the actual execution plan, as the query never returned! It seems a bug is likely. (Running Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64))


Postgresql - Minor version upgrade 9.3.5 to 9.3.6 on Ubuntu I am two servers: A & B. My master database is running on A and the version is 9.3.5. It's replica is running on B and the version is 9.3.6. I am trying to use B as UPSTREAM slave, and trying to start another database (on different port) which would be replica of B.
So the desired setup is like this:
A:5432(master) -> B:5432(slave-upstream) -> A:5433(slave-downstream)

When I try to start A:5433(slave-downstream), it says:

FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "5.6.7.8", user "replicator", SSL on
        FATAL:  no pg_hba.conf entry for replication connection from host "5.6.7.8", user "replicator", SSL off

I've following entries on B:

host    all             all             5.6.7.8/32       md5
host    replication     all             5.6.7.8/32       md5

I am suspecing the problem might be due to the difference of versions? If it's the case, how can I upgrade postgresql on A to 9.3.6?

Or anyother help?

Thanks

I'm guessing your log on server A is being shared by the two databases (master of A and the slave of B).

It looks like you have set ssl = on in your master's postgresql.conf file and have set ssl = off in your slave(B). You should either set ssl to on on all servers or none.

PostgreSQL doesn't usually have a problem replicating across minor versions


Auditing sysadmin members by a specific group A Sql Server 2012 Database Audit performs an audit on sysadmins when you specify the principal DBO. However, this causes ALL the sysadmins to be audited. What configuration or solution can be used to audit ONLY 1 specific group that has sysadmin membership without auditing all the activity by ALL sysadmins? We are trying to cut down on action events that are not necessary (i.e. sql engine service account or other service accounts that are sysadmin and do not need audited). This is a user database.

One option may be to add individual sysadmins as users to the databases in question, add those users to a custom role with no explicit permissions (effectively like public), and setup the database audit specification to track actions for the custom role.


Severity 20 - Fatal Error in LOCAL CURSOR after FETCH This week our database on a SQL2014 SP2 CU11 server started throwing fatal errors (severity 20), on the FETCH statement of code, which could be reduced to:

DECLARE @dummy int
DECLARE CurName CURSOR LOCAL FOR
        SELECT TOP (1) t.id FROM dbo.t_table AS t;
OPEN CurName;

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CurName INTO @dummy;
    IF @@fetch_status <> 0 BREAK;
    PRINT @dummy
END;

DBCC CHECKTABLE and DBCC CHECKDB did not find any problem, a server restart did not help (and since it occured on multiple versions of the same database at the same time, it was very unlikely, that this was a database structur problem).

On a developement server with SQL2014 SP2 and only CU6 the same statements runs without problem (on a SQL2017 Server it works too).

The table

has around 4 mio rows
is partitioned by an integer column
has an clustered PK over two columns (the partition column plus a bigint)
has 25 indexes (I guess, I should clean up a little bit :-))

First solution:
When I changed the CURSOR declaration to

DECLARE CurName CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR ...

(or at least added the STATIC keyword), it runs fine.

Final solution:
The table had a geometry and a geography column, which were dropped this weekend (together with the two corresponding spatial indexes).

Since this was the only change to this table, I decide to rebuild the clustered primary key index, which solved this problem.

It seems, that the SP2 CU11 in SQL2014 has a small bug in this special case, so maybe someone with a similar problem find this solution useful...