Replicate a database with a subset of data offered by custom queries As seen in this question I can use the following sql command:

CREATE DATABASE newdb WITH TEMPLATE olddb;

In order to replicate a database into my machine. But in my case my new database only to contain a subset of the data used in the first database offered by custom queries.

Lets suppose that in my current db has an existing tables:

|- ohimesama:
|-- id PK SERIAL
|-- name VARCHAR(100)
|
|-oujisama:
|-- id PK SERIAL
|-- name VARCHAR(100)
|
|- ohimesama_oujisama_suki:
|-- ouji_id FK oujisama
|-- ohime_id FK ohimesama

And In my database I want to be able to keep only the records from the tables ohimesama and oujisama that maches the current queries:

SELECT * from ohimesama where id in (SELECT ohimesama_oujisama_suki JOIN oujisama where oujisama="WILLIAM");

SELECT * from oujisama where id in (SELECT ohimesama_oujisama_suki JOIN oujisama where ohimesama="MARRIE");

My database is rather large (~100GB) so truncating or deleting rows from the whole database is rather time consuming. Do you have any Idea how to solve that problem?

As you can quess both of the databases are in the same server. The reason why I want to do that is because I want to keep a subset of data for software development.

Follow up: I have issues setting up the JDBC driver. I get the following error:

No suitable driver for jdbc:postgresql://

...though I have installed the PostgreSQL JDBC driver via gnu/linux apt command and specified the correct directory.

I have been through the same problem recently and can comment on some attempts and my conclusions after a lot of research into it.

In my case I had to partition some data that would be distributed in different databases. But note that we are dealing with the same fundamental problem that is partial data migration.

The first problem is regarding data extraction, pg_dump does not provide any dynamic and consistent partial data extraction mechanism.

You can do a full backup and try to erase data you don't want to use, but as you have already realized, this is a costly task and demands a lot of runtime for large volumes of data. And depending on the complexity of your database structure, assembling the delete scripts can be quite difficult. What's more, if your bank is constantly evolving this method is not reusable if you want to extract partial data later.

You will find many suggestions from people saying to use the COPY feature, but all in all I can say that it is extremely limited to the complexity of this task. Because it would be necessary to assemble a data select from all tables consistently and then execute them in a single transaction. Again, since your bank is dynamic and constantly evolving, this method is not reusable either. Because you would have to rewrite all selects every time you were extracting partial data.

Usually the partial data migration comes from a specific table that will restrict the other data, we will name it as a restrictive table. That said we can consider some more possibilities.

Let's assume a case where your primary bank has a table called user (restrictive table) with 100 records and you want to migrate all your bank records that link to the first 10 records in that table.

We can only dump/restore the structure.
We can generate a sql with the data from the 10 user table records
we need and insert it into the database.
Then we can dump all data in simple sql disregarding the user table.
We can run this sql script without transaction block and let the key
constraint check do the dirty work.
As in the previous case, this can take a long execution time
depending on the volume of data, but this solution is functional and
replicable.

Note that your database cannot contain cyclic key problems (This is the condition to dump data only correctly).

Also note that the dependency level of your restrictive table must be the lowest minimum, otherwise an adaptation would have to be made to this methodology, which I will not write because the text is already getting too large.

That said, what really worked for me was using the Jailer tool. This tool is developed in java and using ORM it can map the key constraint set of your database and assemble a data extraction model as you configure it.

The output is a simple sql script with partial data consistent with the restrictive rules that have been set.

If you have any questions about its use I can help you understand and set up your configuration environment.

A similar question was asked in Stack Overflow.


AWS RDS SQL Server Native Backups We have some AWS RDS SQL Server instances that we are setting up.  Some of these will be Multi-AZ for HADR.

We know that RDS will take system level backups and snapshots.  We are wanting to perform individual backups into S3 buckets so we can use the backups for stage and dev restores on central dev server.

We are using SQL Server Agent jobs to perform these backups but the jobs on SQL Server Agent read successful even if the backup to the S3 bucket fails.

What I am curious about is if anyone has figured out a solution to monitor these backups to the S3 buckets since SQL Server Agent jobs are not showing the failure correctly.  Only possible solution I can think of is Powershell but would love to hear anyone else's solution if they have one.

As far as I understand, your task is not so much to make a backup - how to automatically restore it to another RDS SQL Server in the development environment.
In general, there are two ways on how to achieve it:

Create a native backup and restore it to dev
Export \ Import Data tier application

Amazon RDS SQL Server does not support the BACKUP DATABASE command, you need to use a stored procedure instead:
EXEC [msdb]. [Dbo]. [Rds_backup_database]
@source_db_name = '<database name>',
@ S3_arn_to_backup_to = N'arn:aws:s3:::<bucket name>/sample.backup '

To restore a database to a dev server, please use the following stored procedure:
exec msdb.dbo.rds_restore_database
@restore_db_name = '<database name>',
@s3_arn_to_restore_from = 'arn:aws:s3:::<bucket name>/sample.backup';

You will need to do some pre-configuration on your RDS SQL Server - you can find more details here.
Note. These stored procedures do not directly perform the backups, but run it asynchronously. After completing the procedure, you will receive a Task that should be tracked.
You can view all the tasks using the
EXEC procedure [msdb].[Dbo].[Rds_task_status]

Unfortunately, this architecture makes it difficult to use Agent Job. And asynchrony prevents us from making a very reliable solution on powershell.
However, you do not have to use backup to transfer a database to a dev server.
You can use the sqlpackage.exe utility which is intended for data export / import. It is not recommended to use it for backups, as it is necessary to set a global read lock to export consistent data. However, for your task, this can be neglected.
SqlPackage can either create a .bacpac file or .dacpac file. If you specify a /p:ExtractAllTableData = True parameter for the /action:Extract command, then you will get actually identical files.
My experiments have shown that it is better to export to .dacpac, since in this case, you can avoid import errors using the /p option: TreatVerificationErrorsAsWarnings = True
Thus, to export the database, use
sqlpackage.exe /action:Extract /SourceServerName:<RDS Server name> /SourceDatabaseName:<db name> /SourceUser:admin /SourcePassword:<password> /TargetFile:<patch to target .dacpac> /p:ExtractAllTableData=True

and for import
sqlpackage.exe /action:Publish /TargetServerName:<RDS Server name>  /TargetDatabaseName:<db name> /TargetUser:admin /TargetPassword:<password> /SourceFile:<patch to source .dacpac> /p:TreatVerificationErrorsAsWarnings=True

Such a script is much easier to automate in powershell, since both commands are synchronous


Can I use SPARSE somehow on a non-nullable bit column with mostly false values? I have a table that stores the results of queries that are run at least once a day. There's a bit column that represents whether the row is from the most recent run of a particular query with a particular set of arguments. (Yes, it's a functional dependency, but a necessary denormalization for performance, since most queries on this table are only interested in the most recent result.)

Since this bit column is almost always a false value, I'm looking for the best way to tune queries returning only the true values. Partitioning isn't an option (Standard Edition). It seems like making the column SPARSE would be an interesting solution, but I believe that would require me to change the column to nullable and use NULL rather than 0 for false values. Seems a little kludgy.

Is there an option similar to SPARSE that would optimize space/performance for a non-null bit column with mostly (well over 99%) false values?

Pinal Dave's article indicates that both zero and null values are optimized, but this doesn't seem right to me, since these are different values -- unless MSSQL is using the same mechanism for non-null columns to indicate the default value. This would be great if it were true, but the BOL doesn't mention this.

A filtered index (WHERE IsMostRecentRun = 1) sounds like a better idea to me than using sparse. If you can make it so that false is instead represented by null, you may be able to do both, but while that will potentially save some space in the base table, I suspect the bigger gain would be in query performance from the filtered index - as long as it's covering. If you need too many columns to cover and/or need to also filter or join on other columns, then you may find a balancing act between the improvements you get from the seek or range scan on the filtered index and the costs of things like lookups to get at the rest of the columns.

That all said, it seems suspicious you would need an additional column to serve as a flag for this. Isn't this information redundant? Can't it be determined by other data? In which case I would focus on index tuning to optimize the use of the existing columns instead of storing and maintaining redundant data.

(Also I don't think Pinal is correct.)


Difference Between Database Mail Profile and SQL Server Agent Operator in MS SQL Server I have configured Database Mail profile with several accounts. In the SQL Server Agent Properties I've enabled my profile.

If I specify whom to send email with Database Mail profile, why do I need to use Operators which are provided by the Agent? What is their difference?

Thanks for your time.

I believe Database Mail profiles are purely for the set up of what mail server to use and what email address the emails appear to be sent from.

SQL Server Agent Operators are essentially user(s) who are able to receive notifications of job statuses, effectively the receivers of email.

So Database Mail is for sending email, Operators is for receiving email.

https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/ssms/agent/operators?view=sql-server-2017

Note on this link https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail?view=sql-server-2017 it discusses setting up Database Mail for use in SQL Server Agent and it lists setting up the operators as a follow up task.


"SELECT LAG(id) FROM cb_cat WHERE id=450"; I want to return the previous row based on the current id number.
All id numbers are UNIQUE, but are NOT (necessarily) SEQUENTIAL.
SELECT LAG(id) FROM cb_cat WHERE id=450;

I thought this query should work.
If possible, I would like to return * from the previous row before the current id.
I do not know which id number that is, but it is smaller than the current id number, e.g. 450. It could be 449 or even 1.
How can I achieve this?

Window functions work on the result after the WHERE clause is applied. Since id is unique and you ask for exactly one id, that result has a maximum of one row. There just isn't any previous row whatsoever.
Further more relational tables represent relations which are a special form of (multi) sets. And (multi) sets aren't ordered. Unless there's an explicit ORDER BY, the order the tuples of a result are returned in is to be considered random. Window functions depending on an order therefore also need an ORDER BY specified in the OVER clause, unless they just should pick some random row. You didn't specify what defines your order, I assume it's the id in the following.
Using lag() you'd have to select all rows in a derived table where you apply the lag() and then go for the row with the id of 450 in the outer SELECT.
SELECT l
       FROM (SELECT id,
                    lag(id) OVER (ORDER BY id) l
                    FROM cb_cat) x
       WHERE id = 450;

This can be optimized a little. Since id defines the order, we actually only need to look at the records with an id lower than or equal to 450 in the inner SELECT. (I have doubts the optimizer would figure this out by itself. But maybe I'm underestimating its capabilities.)
SELECT l
       FROM (SELECT id,
                    lag(id) OVER (ORDER BY id) l
                    FROM cb_cat
                    WHERE id <= 450) x
       WHERE id = 450;

But if we think of it, what we actually want is the the largest id that is less than 450. lag() isn't the best tool to get this, LIMIT is. We get all ids less than 450, order them in descending order and pick the first one of that ordered result.
SELECT id
       FROM cb_cat
       WHERE id < 450
       ORDER BY id DESC
       LIMIT 1;


Application considerations when using a multi-primary innodb cluster I'm developing a scalable cloud application and I wish to use a multi-primary cluster to be better able to scale horizontally when needed. I have experience with a single primary cluster configuration.

When in multi primary mode, the group replication is slightly delayed from node to node. So, for example, two sequential requests, say PUT /users/1 and GET /users/1, can successfully insert but then fail to select until some time later.

Not necessarily specific to that issue above, are there established ways of designing an application to be built on top of a multi-primary cluster?

I considered making each user session stick to one of the databases for the lifetime of the session. That way anything that's created will be available on subsequent requests because it'll request the same cluster node.

This could be implemented by connecting to the node with id sessionId % n where n is the node count. However, actually doing that seems rather brittle and non-standard. So I was wondering if there are better, established methods of achieving this kind of consistency.

Thank you!

The short answer is no, unfortunately. Have you considered using Galera instead? It has mature synchronous replication and can virtually guarantee consistency across nodes. I would highly recommend it. This article compares what you are using now with the Galera option: https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/

Otherwise you would have to develop an ugly solution like you describe, and it still won't be 100% adequate in the event of a node failure.


Should I use a subquery to help SQL Server find the correct plan I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are company employees and each company gets their own database, and both usage patterns and data ratio's between tables vary greatly between companies.  This is a query that directly effects the user's perception of the speed of the system, and almost all users need to run this query every time they use the system, so it is worth my time to get it right.

We've mostly had good luck letting SQL Server choose the plan (since the best plan probably varies on different databases at different times), but as we've expanded to more users and added more databases (and database servers), we have found cases where it simply has decided on the wrong plan, and the performance goes from a half second (which, at this point, is faster than the user interface) to 10 seconds or worse (which is effectively broken as far as the users are concerned).  I've finally been able to isolate a situation where the performance is consistently bad, so I can now try to fix the problem.

Here is what we currently have (simplified considerably, of course):

SELECT
    -- about 100 columns
FROM
    base_table
LEFT JOIN
    limiting_table
INNER JOIN
    problem_table
LEFT JOIN
    tables_only_used_in_select
INNER JOIN CONTAINSTABLE(
    base_table, *, 'extended text to search for', LANGUAGE 1033) AS rank1
ON
    base_table.row_id = rank1.[KEY]
LEFT OUTER JOIN CONTAINSTABLE(
    base_table, *, 'text to search for', LANGUAGE 1033) AS rank2
ON
    base_table.row_id = rank2.[KEY]
WHERE
    various criteria on base table

The "problem_table" in the above query seems to be the key.  The vast majority of the time, this has no affect on the rows returned by the query.  My first fix was to change it from an INNER JOIN to a LEFT JOIN.  That fixed the problem.  Of course, now I'm going to have to make sure to limit the results a different way (which is possible, but is not an easy code change to get that information to the query), and all the joins still give the database a chance to find another slow plan.

However, I've also had good luck trying to isolate the "important" joins into a subquery, such that the plan won't get distracted by the other tables that aren't as critical.  Like this:

SELECT
    -- about 100 columns
FROM (
    SELECT base_table.*
    FROM
        base_table
    LEFT JOIN
        limiting_table
    INNER JOIN CONTAINSTABLE(
        base_table, *, 'extended text to search for', LANGUAGE 1033) AS rank1
    ON
        base_table.row_id = rank1.[KEY]
    LEFT OUTER JOIN CONTAINSTABLE(
        base_table, *, 'text to search for', LANGUAGE 1033) AS rank2
    ON
        base_table.row_id = rank2.[KEY]
    WHERE
        various criteria on base table
    ) base_table
INNER JOIN
    problem_table
LEFT JOIN
    tables_only_used_in_select

Am I looking at problems doing it this way that I just don't have enough experience to know better?  Or is this a reasonable practical strategy in this case (maybe not ideal, perhaps, but worthwhile trying it and seeing if it works)?

UPDATE:

Here is the plan for the original query that runs on one database in 1 second (and returns 46 rows):

And here is the exact same query running in a different database that takes 55 seconds and returns 369 rows:

Changing from INNER JOIN to LEFT JOIN on the problem_table runs in under a second and results in this plan:

And the subquery, which also takes one second, results in this plan:

Note: I really thought I saw something about the correct way to post plans, but I can't seem to find it.  If someone comments with a hint, I'll try to do a better job.

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments.  I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it.

From what I can see, the advantage of using LEFT JOIN in this case is that it removes the attraction for the plan builder to consider this a good thing (which it isn't, in this case), while still letting the plan builder choose from all the other choices.  The advantage for the subquery is that you still get the INNER JOIN ability to remove any (rare) invalid rows, but you do not let the plan builder include that fact in the critical plan (because the INNER JOIN is in the outside query, not the subquery).  In essence, you are restricting what joins the plan builder can use by placing them in the subquery, and doing the other joins in the main query.

Of course, anytime you restrict the plan builder, you are taking a chance that you restrict it too far and it comes up with a non-optimal plan.  Going forward, I won't necessarily use either technique until I find I need to.  Note that I also tried to use hints or explicitly tell SQL Server to use a particular type of join, but those were never as fast as when I let it choose everything itself.

In the end, it turned out that, due to the ugliness of the code that built this query (on the fly, even), the LEFT JOIN was far easier to implement and have some confidence that I wouldn't break anything, so I went that way.  But that decision was made by factors external to the database.


Why is :r SQLCMD command marked as wrong in Post Deployment Script? I have worked a couple of times with post deployment scripts and always, intuitively used the build action "PostDeploy", because that is what it is.
Now for the first time I try to follow the built-in instruction from the script's template to use the ":r somescript.sql" syntax.

Immediately this line is getting marked as wrong:

  "SQL80001 wrong syntax next to ':'"

I found suggestions to set the PDS to Build Action "none". This does not help, the error stays. What am I missing here?

Assuming that the error happens during the build process, and that you do not have any actual syntax errors, and it is a simple

:r path\to\file.sql

then the error also occurs when the file being imported does not exist. Please check the location of the file. If you did not provide an absolute path, then the path will be relative to the solution folder (at least for me it is).

If, while developing, you want to see the error that the build process reports, then make sure to enable "SQLCMD mode" in the T-SQL editor in Visual Studio. There is a button on the far right in the button bar with a "!" in it that should enable this. Or you can go to the SQL menu, select Transact-SQL Editor ->, select Execution Settings ->, then finally select SQLCMD Mode. Now when you Execute the script, it will correctly interpret :r, just like the build / publish process does.

Also, your Post Deploy Script (PDS) needs to have its Build Action set to PostDeploy. If it is set to None then it will be skipped entirely by the SSDT build process.


WHERE on SQL with JOIN on large tables We have a set of large tables (millions of records) on a MySQL DB, having schema like so (simplified):

T1: id, uid, rid, text1, text2, int1, int2, ...
T2: id, rid, tag_id, created_at
T3: id, owner_id, tag_name

Indices:

T1: Primary(id), unique(uid,rid), index(rid), index(uid,int1)
T2: Primary(id), unique(tag_id,rid), index(tag_id), index(created_at)
T3: Primary(id), unique(owner_id,tag_name)

And a requirement to do a select which returns 'rids' having tag_name = XX but not YY:

SELECT t1.rid
FROM t1
LEFT JOIN t2 ON t1.rid = t2.rid
LEFT JOIN t3 ON t2.tag_id = t3.id
WHERE t1.uid = 123
AND t1.int1 = 3
AND t3.tag_name eq 'XX'
AND t3.tag_name != 'YY'
LIMIT 100

This naturally does not work, since the WHERE does not eliminate an rid having more than one tag. How can we achieve this with performance in mind for the large tables?

More about data:

A user represented by uid will have about 100,000 records in T1, out of which about 10% have T2 records 10,000 (rids which are tagged), and less than 10 tags in T3.
There are 1000s of users (uids) in T1.

A given rid can be one of:

) Has a single tag --> a single T2 record
) Has multiple tags --> Multiple t2 records
) Has no tags --> 0 T2 records

We can also alter the table structure and indices for T2 and T3 to accommodate for that, as long as we maintain ability to filter 'tags' and 'T2' creation time.

You can use NOT EXISTSas:

SELECT t2.rid
FROM t2
WHERE NOT EXISTS (
    SELECT 1
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name <> 'YY'
)
AND EXISTS (
    SELECT 1
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name <> 'XX'
);

Your index T2:index(tag_id) is already covered by T2:unique(tag_id,rid) som you can get rid of that

I don't work much with MySQL, but I get the impression that JOINs are often preferred over EXISTS/NOT EXISTS. Translating the query (Note the DISTINCT):

SELECT DISTINCT t2.rid
FROM t2
JOIN t3 AS t31
    ON t2.tag_id = t31.id
   AND t31.tag_name = 'XX'
LEFT JOIN t3 AS t32
    ON t2.tag_id = t32.id
   AND t32.tag_name <> 'YY'
WHERE t32.id IS NULL;


SQL Server domain account login and user What is the difference between

adding a domain account login to an SQL Server (2008 R2) instance then add a corresponding user to a database
adding a domain account user to a database without adding as a login first

Is there any advantage, benefits, etc of one over the other.  I notice when the latter strategy is used (i.e. user only), sp_helpuser has a NULL LoginName

When the former is used (i.e. login and user) apart from say being able to assign an instance level role (as opposed to simply a database role) is there any problem with just adding a user without login (the latter)

Unless you're referring to contained databases (which are new in SQL Server 2012), you can't use the second approach. Whenever a person authenticates to SQL Server, they are authenticating against a particular server login (or possibly multiple logins if you're creating logins for domain groups). This server login can then be mapped to one or more database users to allow access to different databases.

Unless you're doing some tricks with EXECUTE AS or using application roles, there's no way for somebody to authenticate directly as a database user without a server login being mapped to it.


Why am I getting an email size error with SQL Server database mail? I am sending 6 .csv files as attachments through SQL Server Email. But Email is failing with the following message in the database mail log:

Exception Message: Cannot send mails to mail server. (Service not
available, closing transmission channel. The server response was: DATA
length exceeds 52.4MiB limit)

Then I ran the following command to increase the maxfilesize limit 2 GB
EXECUTE msdb.dbo.sysmail_configure_sp
'MaxFileSize', '2147483647';

Filesize limit was changed successfully to 2 GB, but still I am getting the same error message.
Following are my file sizes in KBs
File A    213   KB,
File B    265   KB,
File C    6246  KB,
File D    35    KB,
File E    60    KB,
File F    31371 KB
I am using mail-gun(smtp) to send an email. Email is running successfully without FILE F. But When I attached FILE F, email fails. Why my email is failing when I increased maxfile size to 2 GB. Any Idea?

You mentioned that you're using Mailgun as your mail server. Mailgun's terms page say they limit email sizes:

An individual email message may not exceed the per-message size limit
(including attachments) of 25MB or it may be permanently lost.

You'll want to reduce your email sizes to match, or consider using a different SMTP server.


How to store a 64-digit key of alpha numerical values into MySQL Database I am student studying in Database Administration (first year), one of our task requires a to design a login form for corporate and regular users. Below are the requirements for the username and password fields:

User name should be any combination of letters and numbers at least
five characters in length and no more than 20. (Same requirements as
user login ID for corporate account ID).
The corporate password is a 64-digit key of alpha numerical values
that all employees in the corporation has access to.
The regular password for both corporate client employees/users and
regular users should contain these types of characters: one
alphanumeric, one number, one uppercase english letter, one lowercase english letters      and one special character and should be at least 8 characters in length and no more than 20.

I do not have any problem in designing the regular expression for regular username and password.

The following is the regular expression for the regular user's password field (meets above requirements):

^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-]).{8,20}$

The problem now is that I have no clues on how to create a 64-digit key of alpha numerical values nor to store in Database for the corporate id and password

Storing:  Use a field with datatype BINARY(64) or CHAR(64) CHARSET ascii COLLATE ascii_bin.

Generating:

This is not an SQL question because it should not be done in the database.  It should be done in the UI layer that receives and checks passwords, etc.

So, given that you should write this in Perl/PHP/VB/Java, you have much stronger tools with which to generate the corporate password.

Of what use is a password that everyone knows?  As soon as a disgruntled employee gets fired, the password may become known to the world.

I'm serious -- some things do not belong in SQL; instead they should be in the code that access the database.

But if you must do it (for the class) in SQL, consider creating a table of the possible characters, one per row, plus another table of (at least) 64 distinct integers.  Then use a JOIN, GROUP_CONCAT, ORDER BY RAND(), LIMIT 64, and probably a subquery to get the INTs.


Restoring ORACLE 11gR2 database with Flash Recovery Area Only I was succeded in making backup using RMAN in Archivelog mode in my host server and this is the screenshot when i list the backup,

and the next step, i copy content from Flash Recovery Area folder inside BACKUPSET Folder to a new host to execute the restore.

but unfortunately when i logged in to the new host and issue list backup command in rman, I cant see any backup file thus i cant restore the database. Please help me was there anything missing in my procedure ??

Thanks again

Just start rman, and issue the command:

CATALOG START WITH 'c:\path\to\your\backups';

... and it'll prompt you for confirmation & add the backups to the rman catalog.


Difference between changed compatibility level database and new database What is the difference between SQL Server databases A & B where:

A. Database restored from lower level compatibility(100) to higher compatibility (130)

B. New Database created in higher compatibility (130)

I'm not sure if this gets to the root of your question - if not, please update your question with more information.

According the the documentation for ALTER DATABASE (Transact-SQL) Compatibility Level, the compatibility mode of a database:

  Sets certain database behaviors to be compatible with the specified
  version of SQL Server.

As I understand the documentation, two databases running under the same compatibility mode should operate the same way with regard to those specific behaviors.

Restored databases do not (necessarily) automatically change their compatibility mode.

From the documentation:

  When a database is upgraded from any earlier version of SQL Server,
  the database retains its existing compatibility level, if it is at
  least minimum allowed for that instance of SQL Server. Upgrading a
  database with a compatibility level lower than the allowed level,
  automatically sets the database to the lowest compatibility level
  allowed. This applies to both system and user databases.


error while trying to retrieve text for error ORA-12154 I am connecting to Oracle server using PLSQL,this is my tnsnames.ora(C:\Oracle\ora90\network\ADMIN) config:

  MY_ERP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =  orcl)
    )
  )

and this is my env variable of ORACLE_HOME:

C:\Oracle\ora90

When I am connecting to server,it throw this error:

error while trying to retrieve text for error ORA-12154

Am I missing something? The OS: Windows 10. PLSQL:7.1.4.1390. Oracle 9.

If those lines really start with spaces in tnsnames.ora, that will not work. Remove the leading spaces in the first line (MY_ERP =), becasue an alias can not start with a space.

You can also try setting TNS_ADMIN to C:\Oracle\ora90\network\ADMIN, because ORA-12154 means the connection identifier could not be resolved, mabybe PL/SQL Developer searches for tnsnames.ora somewhere else:

$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause:  A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
//   - If you are using local naming (TNSNAMES.ORA file):
//      - Make sure that "TNSNAMES" is listed as one of the values of the
//        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA)
//      - Verify that a TNSNAMES.ORA file exists and is in the proper
//        directory and is accessible.
//      - Check that the net service name used as the connect identifier
//        exists in the TNSNAMES.ORA file.
//      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
//        file.  Look for unmatched parentheses or stray characters. Errors
//        in a TNSNAMES.ORA file may make it unusable.
//   - If you are using directory naming:
//      - Verify that "LDAP" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Verify that the LDAP directory server is up and that it is
//        accessible.
//      - Verify that the net service name or database name used as the
//        connect identifier is configured in the directory.
//      - Verify that the default context being used is correct by
//        specifying a fully qualified net service name or a full LDAP DN
//        as the connect identifier
//   - If you are using easy connect naming:
//      - Verify that "EZCONNECT" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Make sure the host, port and service name specified
//        are correct.
//      - Try enclosing the connect identifier in quote marks.
//
//   See the Oracle Net Services Administrators Guide or the Oracle
//   operating system specific guide for more information on naming.

Finally: Oracle 9 was release 18 years ago, install a recent client, if your database is compatible with it.


Database design for changing data I'm trying to design a database to store information about an entity. This entity will pass through 5-6 stages. In every stage may happen some property modifications. It is necessary to have access to the state of the entity in every stage. Also each stage will have some additional properties unique for the current stage.

I'm wondering what would be the best way to design the tables. I was thinking to have a separate table for each stage (but the tables would have the same structure as the structure remains the same through all stages). Also for each stage table I would be able to create relations tables.

The only thing that is bugging me about this design is that I will have 5-6 tables with the same structure. On the other hand if I would put all the stages in one table and have a column which determinates the stage, then I don't see a way to create relations table per stage.

Any thougts ?

There are generally 2 approaches:

Store all in one table, with a timestamp / version number.
Store the current version in one table, with an archive table for the older versions.

The first makes sense in a data warehouse mostly, while the second approach allows applications to be easier (as long as they do not care about the historical data). The archive table can be automated via triggers.

Having one table per state is the worst of post worlds - it makes application development horrendous, particularly as you have to rework all applications when you add an interim state. It also makes referential integrity painful.


Encrypt application name in c# and decrypt in SQL Server I've encrypted application name in my c# app using MD5, so when constructing the connection string the application name is passed to SQL as encrypted. I would like to decrypt in SQL server the value to see if there is a match?
How can I get the original text in SQL ?

I don't believe this is possible natively in SQL Server since MD5 is a one-way (apparently loss-full) hash. Here's a good StackOverflow answer that goes over MD5 in general.
SQL Server has a built in function to generate hashes called HASHBYTES() which maybe you could leverage (and one of the other hashing algorithms that are loss-less) to potentially generate a hash string that is reversible. But I have feeling even that might not be possible to do.


SA Account has not been disabled There has been a security audit for many of our sql servers and per script there are many findings.

One of them says "SA Account has not been disabled"

I know it can be disabled, but can it be renamed.

Please confirm what should i go with "Remove, rename or disable the SA account" and whats he best practise?

Yes, sa can be renamed and disabled since SQL Server 2005. It can't be removed

You can then if you wish, create a dummy sa account with, say, a GUID as password and then disable that or for any software that needs it.

Note that this will let you see the new name of the original sa account

SELECT SUSER_NAME(0x01)


Hierarchical data in MongoDB? I have a question regarding Hierarchical data.

Our app shows posts, that are structured inside parents. Here's how it goes:

prof->sprof->subject->post

(Each prof can contain multiple sprofs, and each sprof can contain multiple subjects, and each subject contains multiple posts)

Right now, we've set up a temporary MySQL Database that looks like this:

There are 4 tables: profs, sprofs, subjects and posts. Inside the table profs you have a row for each prof - and each prof has it's unique ID.

Inside the table sprofs, each sprof has it's own unique ID, but It also has a field for the ID of its parent. For example, if there's a prof with the ID 1, a sprof might have a parent_id of 1 (to indicate that its prof (its parent) is prof number 1).

And so on - each subject has a field for its sprof's ID, and each post has a field for its subject's ID.

So for example, if we want to get all the posts under the subject number 2, we do:

SELECT * FROM 'posts' WHERE 'subject_id'=2

A friend told me, that MongoDB can have structured data, so for example I could put a "table" of "posts" under a "row" inside the "table" of "subjects".

Is it true? Will it help? Will it be faster than using MySQL?

I'm not familliar with MongoDB at all, but I'd like to learn that anyway.

The question to answer first is, are any of the possible children bounded or unbounded sets of data? The next question is, how are you going to be requesting the data? Another question would be, how often will the data be written?

One of the things you'll learn quickly with MongoDB is the answer to any schema questions is almost always "It depends". It depends on the answers to the questions I asked above and a few more. To get you on your way though, MongoDB has some great documentation on the subject of hierarchies. This gets you part of the way to your solution.

Then you have to understand your data access patterns and when to normalize or denormalize data. Something to help you make that decision can be read in this great blog.

Sorry to leave you with a not so cut and dry answer. Yes, MongoDB can take care of hierarchical data. How should it be done? It depends. Will it be faster than MySQL, it depends. Can it be faster than MySQL? Most definitely.

Scott


In-memory SQL Server log file I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services.  Its data comes from another SQL Server instance which is the production instance.

As this database can be rebuilt at any time from the production OLTP database, we've been thinking about putting its log file in memory (using something like RAMDisk).

I've done just that and, to my surprise, we haven't gained much performance; it's become only about 2x better.

Given that I'm no longer writing the log to disk, why is the performance gain this low?  The CPU is not reaching anywhere near 100%.  Where is the bottleneck now?

Note: We have also tested successfully with minimally logged operations using the trace flag 610, but for the purpose of this question please just ignore this.

You haven't indicated the methodology you're loading your DW database with, but you may be seeing quite a bit of logging due to IDENTITY values per this blog.


Oracle monitoring db status I am looking for a way to monitor Oracle database status (health) via PHP script executing SQL query. There are system variables containing actual number of connection, sessions, work load, etc...

How I can access to those values: via regular SELECT statement, or built-in stored procedure, or it need to be custom build stored procedure...

How I can detect, after I get those values, is it a normal, heavy, extreme work load. What are the criteria?

What is 'normal' will depend on your situation. An OLTP database and a warehouse will have very different usage profiles.

If you use a connection pool, then you probably don't expect to see a wide variation in the number of connections or sessions. Such a change would indicate a problem.

Generally you are best off looking for dramatic changes in your measures.

Grab SQL Developer 3 Early Adopter edition from the Oracle website. Check out some of the system reports, and look at the underlying SQLs.


Inserting empty string on Windows MySQL doesn't use default value Why inserting '' on a column with default integer value on MYSQL/WINDOWS produces
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

The query is:

insert into test2 (id,test) values('','');

If I use this query on MYSQL/LINUX, it runs just fine:

mysql>  insert into test2 (id,test) values('','');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql>

id is autoincrement
test column is INT and has default value 0

Thanks.

SOLUTION:
on my windows machine i disable "strict" mode
in my.ini file, i removed STRICT_TRANS_TABLES from sql-mode=

PROBLEM

You need to insert nothing ?

SOLUTION

Then, insert nothing at all ... literally !!!

One of the following will work

INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);

SAMPLE DATA

use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE test2
    -> (id int not null auto_increment primary key,
    -> test int default 0);
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql>

QUERIES EXECUTED

If id and test are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2 besides id and test, name the test column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0 will become 0.

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
+----+------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT #1 : I did this in Windows 8.1 from the command line

mysql> select * from information_schema.global_variables
    -> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
| VERSION                 | 5.6.15                       |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)

mysql>

CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.


In a MySQL Master/Slave replication, what would happen if I write to the Slave? Several Questions

Would the Slave entry remain until there is an insert/update/delete to the table on the Master that could affect the Slave?
If not, how do I ensure that the Slave is in-sync with the Master going forward (either by deleting the Slave entry or copying that entry to the Master manually)?
Most importantly, how do I detect that the tables are out-of-sync?

As long as you do not perform any INSERT/UPDATE/DELETE statements directly on the Slave, your Slave should be just fine. Otherwise, MySQL Replication could break if you INSERT an new row in mydb.mytable on the Slave and, via Replication, the Slave later detects an INSERT of a row to mydb.mytable with the same PRIMARY KEY. This produces error 1062 (Duplicate Key).

The only way you could write to Slave without breaking MySQL Replication is this:

Master has databases db1,db2,db3
Slave is replicating databases db1,db2,db3 from the Master
You run CREATE DATABASE db4; on the Slave
You do INSERTs/UPDATEs/DELETEs only to db4 on the Slave
You do not do INSERTs/UPDATEs/DELETEs only to db1,db2,db3 on the Slave

To detect a Master and Slave being out of sync without downloading any tools, pick any table and run CHECKSUM TABLE against a table on the Master and the Slave's copy of the same table.

EXAMPLE

If you have a table mydb.mytable, run the command against it:

mysql> CHECKSUM TABLE mydb.mytable;

If the values do not come back the same, then something is out-of-sync.

If you want to examine a bunch of table in bulk, you can down Percona's MAATKIT. You will need two specific tools (Percona also has the Percona Toolkit that they themselves forked from MAATKIT which is now being promoted more)

mk-table-checksum
mk-table-sync

or

pt-table-checksum
pt-table-sync

pt-table-checksum will perform a CHECKSUM TABLE against all tables on Master and Slave. You can configure it to do all databases on just specific ones.

pt-table-sync can be run on a Slave against any table. Using the --print and --sync-to-master options, you can see what SQL statements need to be executed on the Slave to have it perfectly match the Master. This tool does not work with table that lacks a PRIMARY KEY or UNIQUE KEY.

I have used MAATKIT for years. I still do. I have not tried the Percona Toolkit yet, but I am sure it should be of the same quality as MAATKIT.


Restore failed for server (Microsoft.SqlServer.SmoExtended) I am using SQL Server 2008. While I am restoring database from backup file I got the error.

  Restore failed for Server 'WIN-TUT3YRM1MMN\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: RESTORE detected an error on page (44262:41495) in database "KBCLDBNEW" as read from the backup set. (Microsoft.SqlServer.Smo)

I have tried restoring in new database but still gives the error. I am cannot find the what is the problem. Thanks for helping me.

Try running RESTORE VERIFYONLY and see if you get more information about the failure.

You could also try running RESTORE with CONTINUE_AFTER_ERROR and then run DBCC CHECKDB:

RESTORE DATABASE database_name
FROM backup_device WITH CONTINUE_AFTER_ERROR

(i.e. running as TSQL rather than through SMO)

DBCC CHECKDB(N'databasename') WITH EXTENDED_LOGICAL_CHECKS;

  Specifying WITH CONTINUE_AFTER_ERROR in a RESTORE statement attempts
  to restore the database. However, there are many kinds of corruption
  that prevent recovering a database. We strongly recommend that you
  reserve using the CONTINUE_AFTER_ERROR option until you have exhausted
  all alternatives.

All backup strategies should include regular DBCC CHECKDB runs.

Also NOTE: You do not have a backup unless you periodically successfully test restores.

Responding to SQL Server Restore Errors Caused by Damaged Backups


PostgreSQL predicate `IS OF` and `IS NOT OF` supported or documented? Reviewing some of the regression tests for PostgreSQL, I can see

SELECT x, x IS OF (text) AS is_text FROM q;

This apparently dates back to 7.3

  Add IS OF / IS NOT OF type predicate (Thomas)

Is this form supported. In my own code, I would have written,

pg_typeof(x) = 'text'

But I like IS OF better.

It's not documented, yet, but it is certainly supported and should be moving forward. It's actually in the SQL 2011 spec as type predicate.

8.19 <type predicate>
Function
Specify a type test.

Format
<type predicate> ::=
<row value predicand> <type predicate part 2>

<type predicate part 2> ::=
IS [ NOT ] OF <left paren> <type list> <right paren>

<type list> ::=
<user-defined type specification>
[ { <comma> <user-defined type specification> }... ]


Can't find stored procedure for updating after creating it I'm trying to create a procedure to update my table

create proc UPDATE_PRODUCT
@ID_CAT INT,
@ID_PRODUCT VARCHAR(30),
@LABEL NVARCHAR(30),
@QTE INT,
@PRICE VARCHAR(50),
@IMG IMAGE
AS
UPDATE [Product_DB].[dbo].[PRODUCTS]
   SET [ID_CAT] = @ID_CAT
      ,[LABEL_PRODUCT] = @LABEL
      ,[QTE_IN_STOCK] = @QTE
      ,[PRICE] = @PRICE
      ,[IMAGE_PRODUCT] = @IMG
 WHERE ID_PRODUCT = @ID_PRODUCT

When I run this code the first time, it says "completed successfully"
However, I can't find it in Stored Procedure folder. I tried to do multiple refreshes and restarts but still the same.

When I try to create a procedure for other purpose like selecting or deleting, it works with no problems.

What's the solution?

The problem here is typically because you were in the wrong database context (in this sense, "wrong" means really "unexpected".

In other words, you were expecting to create the stored procedure in Database1, but you were really in the context of Database2.

This is the exactly reason why I'm always explicit, especially with DDL.  At the top of your script/query window/etc., always put:

use YourDatabase;
go

This will ensure you don't run into this snag again.  I tend to not rely on GUI drop-down lists and my memory to double-check which database is selected before running management SQL queries.


Listing SQL Server instance startup parameters I have a server I have just restarted and verified which trace flag are active using DBCC TRACESTATUS:

  Trace Flag : 3688 Function: Removes messages to errorlog about traces
  started and stopped

Here you can see what each trace flag does.

Flag 3688

The start parameters are as follows:

Question:

How can I find what the startup parameters of the SQL Server services are, through T-SQL?

In SQL Server 2008 R2 SP1 or later, this is made considerably easier via the sys.dm_server_registry DMV:

SELECT
    DSR.registry_key,
    DSR.value_name,
    DSR.value_data
FROM sys.dm_server_registry AS DSR
WHERE
    DSR.registry_key LIKE N'%MSSQLServer\Parameters';

From: An easier way to get SQL Server startup parameters


Can I use WHERE IN when I have combined primary key? So if I have a table with primary key consisted of one id, I can update multiple rows by writing

UPDATE tableA SET colA = 'newValue' WHERE id IN (1,3,5,7,9)

Can I do it somehow if I have primary key consisted of 2 columns? I would like to update multiple rows providing some kind of array of keys.

Yes, this is possible:

UPDATE
  tablea SET cola = 'newValue'
WHERE (id1,id2) IN ( (1,1), (2,2), (3,3) );


Should I use one-to-many or many-to-many to log previous records? I have 2 tables, company and interns. the initial relationships are,

An intern belongs to only one company.
A company has many interns

I have a slight problem with these relationships.

If the student changes to another company halfway through but needs the record of the previous company to be kept, is it correct if I change the relationship to become 'An intern belongs to many company' and create a third table to handle the 'many-to-many' relationship?

Or should I just leave it at that and instead create a 'log' table that logs the students' previous company?

Or is there a better way?

Note : Imagine that this system requires the most efficient way of storing data and that student cannot belong to two or more different companies at the same time.

I don't think there is many-to-many relationship exists in this case as the Intern will always be associated with a single company.

I would prefer to go with say a LOG (or InternCompanyHistory) table to save InternId, CompanyId and DateOfChange or any other attributes associated with change of company.


Mapping a Login to a User On my production system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns CPSDOM\mconnors.

We use Windows authentication on our DBs.

On my test system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns dbo.

So I my test system I went to security/logins/ Login Properties/UserMaping and entered in CPSDOM\mconnors for the user. When I run SELECT CURRENT_USER, I still get back dbo. I went back to the Login properties/UserMapping to verify the change was still there and it was.

What am I missing?

If you want the same result on both systems, your choices are:

If you always want to know the server-level Windows account in use, change your code to use ORIGINAL_LOGIN() in both places instead of relying on database mapping (or use SUSER_SNAME()).
Add your login to the sysadmin role on the production system.
Create a new login on your test system (or at least be sure you know the sa password), remove this login from the sysadmin role, and make sure they are no longer the database owner (e.g. ALTER AUTHORIZATION ON DATABASE::dbname TO sa;). You may then need to manually add them as a user and add permissions within the database (sorry, this is my least preferred option, and I didn't feel like messing up my own login on my system to test it).


Oracle SQL count occurances of character in VARCHAR Is is possible to count the number of occurrences of a character in a VARCHAR in oracle? Something that would allow me to determine '11001110' has 5 ones?

What version of Oracle?

If you're using Oracle 11.1 or later, you can use REGEXP_COUNT

  1* select regexp_count( '11001110', '1' ) from dual
SQL> /

REGEXP_COUNT('11001110','1')
----------------------------
                           5

In earlier versions, there are various less elegant options such as

SQL> select length( '11001110' ) - length( replace( '11001110', '1' ) ) cnt
  2    from dual;

       CNT
----------
         5


Update column of a large set of records with a calculated value I am trying to figure out what a good way would be to write the pseudo query below in TSQL taking into account that there will be about 50 actualResult records and 100.000 prediction records per actual result involved.

update prediction
set points = (
    @points = 0
    if(prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB)
        @points = @points + 3
    if (prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB)
        @points = @points + 1
    select @points
)
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

what I am wondering about is:

Is there a way of doing the calculation which in reality is more complex inline? Right now I can only get it to work if I put it in a function but from what I understand this means it's constantly re-calculted even if the input is the same (which I quite likely in allot of the predictions)
Would this way lock the entire range of records? And if so is there a way to prevent this? For example by copying it all to a table variable and then doing the calculation on that and then updating the records once all calculations are done?

am I on the right track here or would you write it in a different way?

Try this:

update prediction
set points =
    (
        0
        +
        CASE WHEN prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB
        THEN 3 ELSE 0 END
        +
        CASE WHEN prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB
        THEN 1 ELSE 0 END
    )
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

AFAIK - if function is not depend on data in query, then it calls one time per query,

AND

if the function is deterministic and schema bound - it will not call multiple times for the same data, BUT there has to be an index on argument data AND optimizer must want to use that index

If you want per-row locking, use ROWLOCK hint


How can I eliminate this scalar function or make it faster? I'm working on pretty lengthy view and one of SQL statements calls a scalar function, which really degrades the performance. The following is the function that is called in a select statement. (sql server 2016)
Really not sure the best ways to modify this process.
Simplified select statement.
Select vendorpartid,
dbo.ufn_StockUomQuantityToOrder(vpp.priorityLevel, vpp.monthlyUsageRate, vpp.minimumPurchaseUomOrderQuantity, vpp.purchaseUomConversionFactor, vpp.orderFrequencyDays, vpp.boxQuantity) stockUomQuantityToOrder
FROM vendorpartpriority vpp

ALTER FUNCTION [dbo].[ufn_StockUomQuantityToOrder] (
            @priorityLevel decimal(38, 10),
            @monthlyUsageRate decimal(38,10),
            @minimumPurchaseUomOrderQuantity decimal(38, 10),
            @purchaseUomConversion decimal(38, 10),
            @orderFrequencyDays decimal(38,10),
            @boxQuantity int
        )
        RETURNS int
        WITH SCHEMABINDING
        AS
        -- Calculate the quantity that needs to be ordered
        BEGIN
            DECLARE @quantityToOrderDecimal decimal(38, 10) = NULL;
            DECLARE @quantityToOrderInt int = NULL;
            DECLARE @orderFrequencyMinimumQuantity decimal(38, 10) = NULL;
            DECLARE @minimumStockUomOrderQuantity decimal(38,10) = NULL;

            --set the default order quantity
            SELECT @quantityToOrderDecimal = (-1.0 * @monthlyUsageRate * @priorityLevel);

            --get the minimum order quantity in stock UOM
            SELECT @minimumStockUomOrderQuantity = (@minimumPurchaseUomOrderQuantity * @purchaseUomConversion);

            --calculate the order frequency minimum
            IF(@orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL)
                SELECT @orderFrequencyMinimumQuantity = (@monthlyUsageRate * @orderFrequencyDays / 30.0);

            --do we need to meet a vendor minimum
            IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @minimumStockUomOrderQuantity)
                SELECT @quantityToOrderDecimal = @minimumStockUomOrderQuantity;

            --do we need to meet an order frequency minimum
            IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @orderFrequencyMinimumQuantity)
                SELECT @quantityToOrderDecimal = @orderFrequencyMinimumQuantity;

            --convert to the int
            SELECT @quantityToOrderInt = CAST(CEILING(@quantityToOrderDecimal) AS int);

            --did we come up with a number that needs to be an increment of boxQuantity
            IF(@quantityToOrderInt IS NOT NULL AND @boxQuantity > 0)
                BEGIN
                    --get the partial box quantity if any
                    DECLARE @partialBox int = @quantityToOrderInt % @boxQuantity;

                    --remove the partial box and add a full one (if we are not of box increments)
                    IF(@partialBox <> 0)
                    BEGIN
                        SELECT @quantityToOrderInt = (@quantityToOrderInt - @partialBox + @boxQuantity);
                    END
                END

            RETURN @quantityToOrderInt;
        END;

GO

2019 isn't an option now. I have dabbled with the in-line table value, but I'm not quite sure how to get rid of the declares.
I'm not 100% sure on the nulls. My gut says I shouldn't be passing any values that are nulls, but looks like input table has quite a few in different columns.
Right now I'm trying to make some glaring performance changes until we get time to dismantle the whole process.

Rewrite the scalar function as an in-line table-valued function, or upgrade to SQL Server 2019, which will automatically perform that translation for you.

Machine Translation
The following is the most direct T-SQL representation of the in-lined function produced by SQL Server 2019 for your scalar user-defined function. One wouldn't rewrite the function this way; I present it for its educational and entertainment value:
DROP FUNCTION IF EXISTS dbo.ufn_StockUomQuantityToOrder;
GO
CREATE FUNCTION dbo.ufn_StockUomQuantityToOrder
(
    @priorityLevel decimal(38, 10),
    @monthlyUsageRate decimal(38,10),
    @minimumPurchaseUomOrderQuantity decimal(38, 10),
    @purchaseUomConversion decimal(38, 10),
    @orderFrequencyDays decimal(38,10),
    @boxQuantity int
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Node0.Expr1019
    FROM
    (
        SELECT Expr1002 = CONVERT(decimal(38,10), NULL)
    ) AS Node7
    CROSS APPLY
    (
        SELECT Node10.Expr1007
        FROM
        (
            SELECT Expr1006 =
                CASE
                    WHEN @orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL
                    THEN 1
                    ELSE 0
                END
        ) AS Node9
        OUTER APPLY
        (
            SELECT Expr1007 =
                CASE
                    WHEN Node9.Expr1006 = 1
                    THEN CONVERT(decimal(38,10),@monthlyUsageRate * @orderFrequencyDays/(30.0),0)
                    ELSE Node7.Expr1002
                END
        ) AS Node10
    ) AS Node6
    CROSS APPLY
    (
        SELECT
            Expr1004 = CONVERT(decimal(38,10), -(1.0 * @monthlyUsageRate * @priorityLevel), 0),
            Expr1005 = CONVERT(decimal(38,10), @minimumPurchaseUomOrderQuantity * @purchaseUomConversion, 0),
            Node6.Expr1007
    ) AS Node5
    CROSS APPLY
    (
        SELECT Node13.Expr1009
        FROM
        (
            SELECT Expr1008 =
                CASE
                    WHEN Node5.Expr1004 IS NULL OR Node5.Expr1004 < Node5.Expr1005
                    THEN 1
                    ELSE 0
                END
        ) AS Node12
        OUTER APPLY
        (
            SELECT Expr1009 =
                CASE
                    WHEN Node12.Expr1008 = 1
                    THEN Node5.Expr1005
                    ELSE Node5.Expr1004
                END
        ) AS Node13
    ) AS Node4
    CROSS APPLY
    (
        SELECT Node16.Expr1011
        FROM
        (
            SELECT Expr1010 =
                CASE
                    WHEN Node4.Expr1009 IS NULL OR Node4.Expr1009 < Node5.Expr1007
                    THEN 1
                    ELSE 0
                END
        ) AS Node15
        OUTER APPLY
        (
            SELECT Expr1011 =
                CASE
                    WHEN Node15.Expr1010 = 1
                    THEN Node5.Expr1007
                    ELSE Node4.Expr1009
                END
        ) AS Node16
    ) AS Node3
    CROSS APPLY
    (
        SELECT Expr1012 = CONVERT(int, CEILING(Node3.Expr1011), 0)
    ) AS Node2
    CROSS APPLY
    (
        SELECT Node17.Expr1016
        FROM
        (
            SELECT Expr1013 =
                CASE
                    WHEN Node2.Expr1012 IS NOT NULL AND @boxQuantity > 0
                    THEN 1
                    ELSE 0
                END
        ) AS Node18
        OUTER APPLY
        (
            SELECT Node19.Expr1016
            FROM
            (
                SELECT Expr1014 =
                    CASE
                        WHEN Node18.Expr1013 = 1
                        THEN Node2.Expr1012 % @boxQuantity
                        ELSE NULL
                    END
            ) AS Node20
            CROSS APPLY
            (
                SELECT Node21.Expr1016
                FROM
                (
                    SELECT Expr1015 =
                        CASE
                            WHEN Node20.Expr1014 <> 0
                            THEN 1
                            ELSE 0
                        END
                ) AS Node22
                OUTER APPLY
                (
                    SELECT Expr1016 =
                        CASE
                            WHEN Node18.Expr1013 = 1 AND Node22.Expr1015 = 1
                            THEN Node2.Expr1012 - Node20.Expr1014 + @boxQuantity
                            ELSE Node2.Expr1012
                        END
                ) AS Node21
            ) AS Node19
        ) AS Node17
    ) AS Node1
    CROSS APPLY
    (
        SELECT
            Expr1019 = CONVERT(int, Node1.Expr1016, 0)
    ) AS Node0;
GO

Example call:
DECLARE
    @priorityLevel decimal(38, 10),
    @monthlyUsageRate decimal(38,10),
    @minimumPurchaseUomOrderQuantity decimal(38, 10),
    @purchaseUomConversion decimal(38, 10),
    @orderFrequencyDays decimal(38,10),
    @boxQuantity int

SELECT
    stockUomQuantityToOrder = FN.Expr1019
FROM dbo.ufn_StockUomQuantityToOrder
(
    @priorityLevel,
    @monthlyUsageRate,
    @minimumPurchaseUomOrderQuantity,
    @purchaseUomConversion,
    @orderFrequencyDays,
    @boxQuantity
) AS FN;

The execution plan is:

Human Translation
This human would rewrite your scalar function as:
CREATE FUNCTION dbo.ufn_StockUomQuantityToOrder
(
    @priorityLevel decimal(38, 10),
    @monthlyUsageRate decimal(38,10),
    @minimumPurchaseUomOrderQuantity decimal(38, 10),
    @purchaseUomConversion decimal(38, 10),
    @orderFrequencyDays decimal(38,10),
    @boxQuantity int
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        stockUomQuantityToOrder = Step6.quantityToOrderInt
    FROM
    (
        SELECT
            --set the default order quantity
            quantityToOrderDecimal =
                CONVERT
                (
                    decimal(38,10),
                    -1.0 * @monthlyUsageRate * @priorityLevel
                ),
            --get the minimum order quantity in stock UOM
            minimumStockUomOrderQuantity =
                CONVERT
                (
                    decimal(38,10),
                    @minimumPurchaseUomOrderQuantity * @purchaseUomConversion
                ),
            --calculate the order frequency minimum
            orderFrequencyMinimumQuantity =
                CONVERT
                (
                    decimal(38,10),
                    IIF
                    (
                        @orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL,
                        @monthlyUsageRate * @orderFrequencyDays / 30.0,
                        NULL
                    )
                )
    ) AS Step1
    CROSS APPLY
    (
        SELECT
            --do we need to meet a vendor minimum
            quantityToOrderDecimal =
                IIF
                (
                    Step1.quantityToOrderDecimal IS NULL OR Step1.quantityToOrderDecimal < Step1.minimumStockUomOrderQuantity,
                    Step1.minimumStockUomOrderQuantity,
                    Step1.quantityToOrderDecimal
                ),
            Step1.orderFrequencyMinimumQuantity
    ) AS Step2
    CROSS APPLY
    (
        SELECT
            --do we need to meet an order frequency minimum
            quantityToOrderDecimal =
                IIF
                (
                    Step2.quantityToOrderDecimal IS NULL OR Step2.quantityToOrderDecimal < Step2.orderFrequencyMinimumQuantity,
                    Step2.orderFrequencyMinimumQuantity,
                    Step2.quantityToOrderDecimal
                )
    ) AS Step3
    CROSS APPLY
    (
        SELECT
            --convert to the int
            quantityToOrderInt = CAST(CEILING(Step3.quantityToOrderDecimal) AS integer)
    ) AS Step4
    CROSS APPLY
    (
        SELECT
            Step4.quantityToOrderInt,
            --get the partial box quantity if any
            partialBox = CONVERT(integer, Step4.quantityToOrderInt % @boxQuantity)
    ) AS Step5
    CROSS APPLY
    (
        SELECT
           --did we come up with a number that needs to be an increment of boxQuantity
           quantityToOrderInt =
           IIF
           (
                Step5.quantityToOrderInt IS NOT NULL AND @boxQuantity > 0,
                --remove the partial box and add a full one (if we are not of box increments)
                IIF
                (
                    Step5.partialBox != 0,
                    Step5.quantityToOrderInt - Step5.partialBox + @boxQuantity,
                    Step5.quantityToOrderInt
                ),
                Step5.quantityToOrderInt
            )
    ) AS Step6;

The original view in the question becomes:
SELECT
    VPP.vendorpartid,
    FN.stockUomQuantityToOrder
FROM dbo.vendorpartpriority AS VPP
CROSS APPLY dbo.ufn_StockUomQuantityToOrder
(
    VPP.priorityLevel,
    VPP.monthlyUsageRate,
    VPP.minimumPurchaseUomOrderQuantity,
    VPP.purchaseUomConversionFactor,
    VPP.orderFrequencyDays,
    VPP.boxQuantity
) AS FN;


How to write a shell script to check the mysql database backup status. I have mysql production databases running in main server(Linux) and from this server every night the backup is taken into local server.
Now i want to find a shell script to check if all the databases backup is up to date or not(for an example: i have twenty database in the main server so i want to know all of them have been backed up completely or not) and compare the each database's current size with their previous size, in a sense i want to make sure with through this script that my backup is perfect and if not i will have a track of that occurrence.

Please help me to get a solution of this problem.

This would indeed be nice to be able to do it in an automatic way. Unfortunately, to be 100% sure your backups are fine, you need human intervention. After taking the backup, you must be able to check if you can import it safely. Of course, you can not always do it. You can check your backups for example every 2-4 days.

On the other hand, you can always check if the size of the new backup is significantly smaller than the previous one (considering that the database always increases in size). This could be an indication that your backup might not have been taken correctly or is corrupted.


One case statement across multiple columns I have a query in which I am executing the same case statement across multiple columns, i.e. something like this

select
case when (cond) then
     'Column1 Cond T'
else'Column1 Cond F'
end,
case when (cond) then
     'Column2 Cond T'
else'Column2 Cond F'
end,
from table

I would like to "merge" these two case statement into one so I have something like this:

 SELECT
 CASE WHEN (cond) then
     'Column1 Cond T', 'Column2 Cond T'
ELSE 'Column1 Cond F','Column2 Cond F'
END,
FROM table

However, I am getting a syntax error when I try running that. What is the correct syntax for this type of case statement?

Store the condition result into user variable and use it in the second CASE:

select
case when @cond := (cond) then
     'Column1 Cond T'
else'Column1 Cond F'
end,
case when @cond then
     'Column2 Cond T'
else'Column2 Cond F'
end,
from table


How to create an INSERT/UPDATE/DELETE trigger My knowledge with triggers is very poor.

I want to create an INSERT/UPDATE/DELETE trigger on a tableA with column names (A1,A2,A3,A4,A5,A6). After the triggers fires, I want to put that data in tableB with at least two different column names (B1, B2, A3, A4, A5, A6).

Basically if  there is a change in tableA, that change should go in to tableB in the respective columns, i.e. A1->B1, A2->B2, A3->A3, like that.

Can anyone please help me with this if possible?

Here is a snippet of how you would construct the INSERT trigger. UPDATE would be very similar and DELETE would depend on how you want to handle it. We'll need a create table statement to help you in depth with all triggers.

Triggers are just T-SQL with a little logic tied in more or less. So how would you take data from A to B and rename it? Below is an example:

CREATE TABLE TableA (A1 INT
                   , A2 INT
                   , A3 INT
                   , A4 INT
                   , A5 INT
                   , A6 INT);

CREATE TABLE TableB (B1 INT
                   , B2 INT
                   , A3 INT
                   , A4 INT
                   , A5 INT
                   , A6 INT);
GO

CREATE TRIGGER TriggerTest
ON TableA
AFTER INSERT
AS
BEGIN
    INSERT INTO TableB (B1
                      , B2
                      , A3
                      , A4
                      , A5
                      , A6)
    SELECT i.A1
         , i.A2
         , i.A3
         , i.A4
         , i.A5
         , i.A6
    FROM   inserted i;
END;
GO

I'm matching up the column I want to INSERT with the SELECT clause, SQL knows A1 is going into B1 because of the ordering and declaration of the INSERT and SELECT. You can also use aliasing later if you need, example below:

SELECT 1 AS 'FakeNumber';

SELECT 1 AS 'NewColumnName';

Also, triggers can be both helpful and extremely dangerous. Use with caution. Put your development through the ringer with QA and testing, propagate up to production with caution. And as always, evaluate that you are using the best tool for the job, not all problems are nails and not all solutions are hammers.


Combining multiple rows into single column The return from my MySQL statement displays an example below:

id 1 | status | id 2 | name | category
001  | open   | 011  | john | person
001  | open   | 011  | john | male

How would I combine the multiple rows to the category column? Like the example below.

id 1 | status | id 2 | name | category
001  | open   | 011  | john | person, male

Below is what you need

CREATE TABLE customers
    ([id 1] int, [status] varchar(4), [id 2] int, [name] varchar(4), [category] varchar(6))
;

INSERT INTO customers
    ([id 1], [status], [id 2], [name], [category])
VALUES
    (001, 'open', 011, 'john', 'person'),
    (001, 'open', 011, 'john', 'male')
;
select * from dbo.customers

---- You have to use STUFF function with FOR XML PATH

 select distinct cs.[id 1]
    ,cs.[status]
    ,cs.[id 2]
    ,cs.[name]
    ,stuff((
            select ',' + c.[category]
            from dbo.customers c
            where cs.[id 1] = c.[id 1]
            for xml path('')
            ), 1, 1, '') as [category]
     from dbo.customers cs


How to use database users as a foreign key field in any table I want to create a table where I can have a column username which will be the foreign key and it will only contain usernames of existing users on that database. For example, if I want to insert 'John' in that field, there have to be a database user 'John' otherwise database should throw error. Any help?

You cannot create a foreign key that references an Oracle data dictionary table.  So assuming the "users on that database" are Oracle database users that exist in dba_users, you can't reference dba_users in your constraint.

You can, however, create your own copy of the dba_users table and create a job (using dbms_scheduler or dbms_job or some external scheduler) that will periodically copy the data from dba_users to your copy of the table.  You can then create a primary key constraint on the username column in your copy and create a foreign key constraint that references your copy of the table.  You may need to work with the DBA team to ensure that your copy of the table gets refreshed as part of any process that creates new database users depending on how soon after a user is created you would expect rows to be created in the child table.


Anyway to view original query on top of views using postgres For example, I have a simple query:
SELECT * FROM information_schema.columns where table_schema = 'public';

Which turn out very complex, information_schema.columns is a complex view.
Right now, to understand the query, I need to read all the definitions of all the views involved.
Is there any way to "expand" query and make it easier to read side by side with query plan output?

You can use EXPLAIN (ANALYZE, BUFFERS) on the query, which will show what is really executed on the base tables. That allows you to understand where the slowness is and what causes it. This works for catalog tables just as well as for other tables.


Incorrect :NEW syntax I work in Oracle SQL Developer and use Oracle 11g express edition.

Tables, that are invoked here look like:

CREATE TABLE orders
(
    id_order             INTEGER NOT NULL ,
    total                NUMBER(10,2) NOT NULL ,
    id_user              NUMBER NOT NULL ,
    purchase_date        DATE NOT NULL ,
    id_delivery          NUMBER NOT NULL ,
    id_payment           NUMBER NOT NULL ,
    id_status            NUMBER NOT NULL ,
    distance             NUMBER(5) NULL
);

CREATE TABLE order_item
(
    id_item              NUMBER NOT NULL ,
    id_order             NUMBER NOT NULL ,
    id_watch             NUMBER NOT NULL ,
    quantity             NUMBER NOT NULL
);

CREATE TABLE users
(
    id_user              NUMBER NOT NULL ,
    login                VARCHAR2(100) UNIQUE NOT NULL ,
    pass                 VARCHAR2(100) NOT NULL ,
    email                VARCHAR2(200) NOT NULL ,
    full_name            VARCHAR2(200),
    birth_date           DATE ,
    balance              NUMBER(15,6),
    id_role              NUMBER NOT NULL ,
    discount             NUMBER
);

CREATE TABLE watches
(
    id_watch             NUMBER NOT NULL ,
    id_brand             NUMBER NOT NULL ,
    id_mechanism         NUMBER NOT NULL ,
    id_bracelet          NUMBER NOT NULL ,
    id_gender            NUMBER NOT NULL ,
    cost                 NUMBER(8,2) NOT NULL ,
    model                VARCHAR2(100) NOT NULL ,
    description          VARCHAR2(2000) NOT NULL ,
    image_path           VARCHAR2(100) NOT NULL ,
    id_factory_country   NUMBER NOT NULL ,
    id_stock             NUMBER NULL
);

When I try to compile next code:

CREATE OR REPLACE TRIGGER addItemInOrder AFTER INSERT ON order_item
FOR EACH ROW
BEGIN
UPDATE orders
SET total = total + (:NEW.quantity * (SELECT cost FROM watches WHERE id_watch = :NEW.id_watch))
WHERE orders.id_order = :NEW.id_order;
END;

I get a message box like this:

This seems that :NEW is treated as bindable variable, but when I omit semicolon I get another error:

  Error(4,25): PL/SQL: ORA-00904: "NEW"."ID_ORDER": invalid identifier

That is sad as well.

So I have 2 questions:

1) Is it correct to use :NEW in AFTER INSERT trigger in WHERE clause?

2) Why everything compiles and works fine when I execute SET DEFINE OFF; before DDL statement?

And of course, thanks for your answers)

You were nearly there with your code. Fixed trigger below:

CREATE OR REPLACE TRIGGER add_item_in_order AFTER INSERT ON order_item
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
UPDATE orders
SET total = total + (:NEW.quantity * (SELECT cost FROM watches WHERE id_watch = :NEW.id_watch))
WHERE orders.id_order = :NEW.id_order;
END;


Unable to create a self signed Certificate for SQL Server 2017(14.x.xxxx) Using SQL Server 2017 Developer Edition

The command I'm using is:

  New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName host.doman.com -KeySpec KeyExchange -FriendlyName SQLCert

The error message is:

  New-SelfSignedCertificate : A parameter cannot be found that matches parameter name 'KeySpec'.

My powerShell version is:

PS C:\Windows\system32> $PSversionTable

Name                           Value
----                           -----
PSVersion                      4.0
WSManStackVersion              3.0
SerializationVersion           1.1.0.1
CLRVersion                     4.0.30319.42000
BuildVersion                   6.3.9600.18773
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0}
PSRemotingProtocolVersion      2.2

You need to enclose CertStoreLocation, Subject, DnsName and FriendlyName with double quotes.

There is no need to specify a location as it will default to "Local Computer/Personal/Certificates" where it needs to be in order to use it by SQL Server.

This will generate a valid certificate on Windows Server 2016 that will be usable by SQL Server 2017:

New-SelfSignedCertificate -Subject "CN=insert FQDN here" -DnsName "<insert FQDN here>","insert NetBIOS name here" -FriendlyName "Self Signed Certificate Friendly Name" -KeySpec KeyExchange -KeyLength 2048 -NotAfter (Get-Date).AddYears(5)

Please replace 'insert FQDN here' and 'insert NetBIOS name here' with the actual FQDN and NetBIOS name keeping the double quotes.


How can I script out a copy of the backup to multiple locations? Is there a way to script a copy of the backup file to multiple destinations? For example, the following will split the backup into 3 segments which make up the entire backup file. But, I'm looking to send 3 complete backups to 3 different locations. Is this possible?

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK',
DISK = 'D:\TestDB2.BAK',
DISK = 'E:\TestDB3.BAK'
GO

I'm looking for something more like...

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK',
TO DISK = 'D:\TestDB2.BAK',
TO DISK = 'E:\TestDB3.BAK'
GO

Thanks in advance.

You want to use the MIRROR TO option. This will allow you to create up to 3 different backups - each effectively a mirror or copy of the main backup you are doing.

In your case:

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK'
MIRROR TO DISK = 'D:\TestDB2.BAK'
MIRROR TO DISK = 'E:\TestDB3.BAK' WITH FORMAT
GO

You can see more about the commands and options for backup here. There are many other options you should look into - such as verify, checksum, etc.

A couple notes also - 1.) The WITH FORMAT is necessary the first time I execute this here since this mirrored set is not yet formatted/created. So the backup doesn't operate like a traditional backup in that regard - because you are effectively creating a new mirrored backup set. The link above will help explain that a bit more.

And seeing you ask this does lead me to ask the question of "What is the goal?" - This is a feature for a reason and it may just be a requirement you have. But it could also be that you are trying to handle something that may be better handled in other ways. For instance backing up to a device with redundancies built in, backing up to a network drive, backing up to a SAN that has proper SAN replication in place, grabbing a copy of a backup right after you take it, etc.


Why doesn't Oracle show my privileges? I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do these operations without the privileges or why doesn't Oracle show them?

Running following queries returns nothing:

SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM all_tab_privs_recd WHERE grantee = 'user_kshitiz';

Running SELECT * FROM USER_SYS_PRIVS returns:

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
user_kshitiz                   UNLIMITED TABLESPACE                     NO
user_kshitiz                   CREATE SEQUENCE                          NO

Running SELECT * FROM USER_ROLE_PRIVS returns:

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
user_kshitiz                   CONNECT                        NO  YES NO
user_kshitiz                   RESOURCE                       NO  YES NO
user_kshitiz                   SCHEMA_1_RW_ROLE               NO  YES NO

Checking privileges on each of those roles returns nothing.

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'CONNECT';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'RESOURCE';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'SCHEMA_1_RW_ROLE';

So where does the power come from?

As you can see you have RESOURCE role granted to the user user_kshitz. And you have to query the ROLE_SYS_PRIVS.

SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                   PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
RESOURCE               CREATE TRIGGER               NO
RESOURCE               CREATE SEQUENCE              NO
RESOURCE               CREATE TYPE              NO
RESOURCE               CREATE PROCEDURE             NO
RESOURCE               CREATE CLUSTER               NO
RESOURCE               CREATE OPERATOR              NO
RESOURCE               CREATE INDEXTYPE             NO
RESOURCE               CREATE TABLE             NO

8 rows selected.

Also you can query the SESSION_PRIVS to find all the privileges granted to the user.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

9 rows selected.


Better ways to update columns of a table than using Merge Statement I have a table with this structure

Table1(Cust_No    Varchar2(50),
       First_Name Varchar2(50),
       Last_Name  Varchar2(50),
       Error_Code Number(1)   ,
       Error_Desc Varchar2(50))

there is another table with this structure

Table2(Cust_No            Varchar2(50),
       Customer_FirstName Varchar2(50),
       Customer_LastName  Varchar2(50)
       )

Imagine that Column Cust_No from Table1 has data.By this I mean there are some data for Cust_No in this field. I need to Update other columns of the table based on this column and column Cust_No of Table2 and find First_Name and Last_Name for each Customer Number. What I have written so far is this :

-- FIND CUSTOMER'S FIRST AND LAST NAME

   MERGE INTO (SELECT * FROM REQUEST R WHERE R.IS_CHECKED IS NULL) R
   USING VMI_DIMCUSTOMER V
   ON (V.CUSTOMER_NUM = R.CUST_NO)
    WHEN MATCHED THEN
    UPDATE
      SET R.FIRST_NAME = V.CUST_FIRST_NAME,
          R.LAST_NAME  = V.CUST_LAST_NAME,
          R.ERROR_CODE = 0,
          R.IS_CHECKED = 1;

            COMMIT;

   -- CUSTOMERS NOT FOUND

  UPDATE /*+ PARALLEL(4) */ REQUEST T
     SET T.FIRST_NAME = '--',
         T.LAST_NAME  = '--',
         T.ERROR_CODE = 2,
         T.ERROR_DESC = 'Customer Not Found',
         T.IS_CHECKED = 0
      WHERE T.FIRST_NAME IS NULL;

        COMMIT;

What I'm doing in this query is that First of all, I'm using a Merge statement to find customer's first and last name and update column Is_Checked to 1 when Customers have first and last name. In the second part I'm updating columns that does not match to any records in Table2.

I want to know wether there are better ways to write this query in terms of performance.

Assuming your second update needs to only work on the records that were just updated in the merge statement, the following should do the trick:

MERGE INTO request tgt
  USING vmi_dimcustomer src
    ON (tgt.cust_no = src.customer_num AND tgt.is_checked IS NULL)
WHEN MATCHED THEN
  UPDATE SET tgt.first_name = CASE WHEN src.cust_first_name IS NULL THEN '--' ELSE src.cust_first_name END,
             tgt.last_name = CASE WHEN src.cust_first_name IS NULL THEN '--' ELSE src.cust_last_name END,
             tgt.error_code = CASE WHEN src.cust_first_name IS NULL THEN 2 ELSE 0 END,
             tgt.error_desc = CASE WHEN src.cust_first_name IS NULL THEN 'Customer Not Found' ELSE NULL END,
             tgt.is_checked = CASE WHEN src.cust_first_name IS NULL THEN 0 ELSE 1 END;

COMMIT;

This works by checking the results of the join between the source and target tables and using the relevant values for the columns being updated, depending on whether the first_name column in vmi_dimcustomer is null or not.


From freelancer to team of two : best way to share/sync db? I am a webdev. I have just hired a dev. Before that I was alone. Thus, I have to change a bit my organization.

I am looking for the best way to develop on the same database (MySQL). I am considering different options:

Get a small server in the office and set an access to this database (actually, this is what we are doing. Performance are poor)
Use a big platform like Amazon RDS or Google Cloud Platform
Use a small VPS like Linode or Digital Ocean
Keep a db on  each dev machine and use a kind of syncing/replication

What would be the best way for you?

Do not consider your database a development asset. Instead consider the code an asset. Make all changes to the database through code, have the code checked in into source control. Conflicting changes resolve as source merges. Rails migrations capture this philosophy pretty well. But it can be applied to any platform and any language, eg. Version control and your database.

This approach will solve not only your team work issues, but also deployment, QA, upgrade of live site(s), tracking history of database changes, explain the current database schema and so many more.


Is there a way to determine automatic storage table spaces? First off, we are on both Windows and AIX and are using DB2 ESE 9.7 FP 4.

I am trying to determine dynamically whether or not table spaces are set up via automatic storage or not (so we can dynamically script how to lower the high water mark when necessary). I can see whether they are defined SMS or DMS "under the covers" via statements like db2 list tablespaces or db2 list tablespaces show detail.

I have also issued queries against the system catalog tables and views (ie, select * from syscat.tablespaces and select * from sysibm.systablespaces).

All I can tell is that the tablespace is defined as SMS or DMS. How can I tell whether or not the tablespace is configured for automatic storage? I know there must be a way I can do it dynamically, because if I use IBM Data Studio and visit the tablespaces "view" and click on properties, I can see whether or not the table space is automatic storage or not. I am assuming there must be a configuration or a query behind that.

Just wondering, how do we figure this out?

Try the monitoring function:

select
  tbsp_name,
  case TBSP_USING_AUTO_STORAGE
    when 1 then 'YES'
    else 'NO'
  end auto_storage
from table (sysproc.MON_GET_TABLESPACE('',-1))


Does Postgres read through entire WAL when starting replication from later point? I have been investigating an inefficiency in our ETL pipeline, where on one side we have Postgres database with logical replication slots.
A consumer keeps track of the LSN where it's at, and sometimes disconnects by design. Upon return, it tells the database the LSN where it would like to begin. However, the time it takes for the replication slot to start returning any content seems to depend on the amount of WAL files currently on disk. It appears as if the replication slot parses through them all to find the right position, which can sometimes take hours.
This behavior does not seem normal or necessary, am I missing something?

I think I found the explanation by looking at open file descriptors while running through some scenarios and waiting for the replication slot to start sending anything. It appears that Postgres does indeed parse a potentially large block of WAL before the replication slot send its first message.
Transactions spanning across several WAL files exacerbate the problem. If the slot's restart/flushed LSN is in the middle of a long transaction, and you tell Postgres you want to resume from that point, it will first read all the WAL files that encompass that transaction (potentially reading far back from where you left off, and far ahead of the requested starting point).
In addition to Postgres reading more WAL files than it does if you only have small transactions, the files which are part of a long transaction seem to take order(s) of magnitude longer to process.
Below are some excerpts from my test log, which include the output from a process monitoring osxfs's file descriptors.
Here the requested starting LSN is in the middle of a long transaction (ending at 0/213AF688), which appears to be why these files take a long time to process, and also why Postgres reads that far ahead before it send the first message (0/1892F2A8)
00:03:52.839   DB current LSN: 0/389866A8, test_slot_1 flushed currently to: 0/179F4948
00:03:52.993   Replication slot test_slot_1 opened with starting LSN: 0/18875A48
00:03:53.160   000000010000000000000017 is open
00:04:06.363   000000010000000000000018 is open
00:04:31.311   000000010000000000000019 is open
00:04:49.656   00000001000000000000001A is open
00:05:01.984   00000001000000000000001B is open
00:05:14.989   00000001000000000000001C is open
00:05:26.570   00000001000000000000001D is open
00:05:38.112   00000001000000000000001E is open
00:05:51.576   00000001000000000000001F is open
00:06:05.497   000000010000000000000020 is open
00:06:26.949   000000010000000000000021 is open
00:06:32.220   Received first message after PT2M39.227S

These WAL files, on the other hand, are full of single statement transactions transactions, Postgres doesn't take too long to go through them. It also doesn't seem to read any further ahead than the requested starting point.
23:30:40.633   DB current LSN: 0/389865C0, test_slot_3 flushed currently to: 0/26321EF0
23:30:40.757   Replication slot test_slot_3 opened with starting LSN: 0/37321EB8
23:30:40.847   000000010000000000000026 is open
23:30:41.522   000000010000000000000027 is open
23:30:42.525   000000010000000000000028 is open
23:30:43.395   000000010000000000000029 is open
23:30:44.278   00000001000000000000002A is open
23:30:45.271   00000001000000000000002B is open
23:30:46.143   00000001000000000000002C is open
23:30:47.137   00000001000000000000002D is open
23:30:47.764   00000001000000000000002E is open
23:30:47.891   000000010000000000000031 is open
23:30:48.023   000000010000000000000033 is open
23:30:48.151   000000010000000000000035 is open
23:30:48.279   000000010000000000000037 is open
23:30:48.289   Received first message after PT7.532S

What remains unclear is why there is such a big difference in how fast Postgres can get through the files it needs to, depending on whether they're part of a single long transaction, or full of smaller ones.
I also wish it was possible to control this behavior with some slot option, I get the feeling it is being overly cautious for my specific use case.


Does SHOWPLAN permission toggle exist in SSMS I'm using SQL Server 2008 R2.

Is there an option in SSMS 2016 to toggle SHOWPLAN permissions for logins (I know this is issued at the database level)?

Is there a query to see if a user, other than myself, has SHOWPLAN permissions?

I know of fn_my_permissions, but am looking for something similar that can be ran against another user account. For security reasons I am unable to use EXECUTE AS

Is there a query to see if a user, other than myself, has SHOWPLAN permissions?

Before answering this question lets first see what privileges you need for using SHOWPLAN.

You can grant it by using:

GRANT SHOWPLAN
TO <database_principal> [ , ...n ]

Sysadmin, dbcreator and db_owners role members automatically get privilege for using SHOWPLAN. Details here.

  For SELECT, INSERT, UPDATE, DELETE, EXEC stored_prodedure, and EXEC
  user_defined_function statements, the following permissions are
  required to produce a Showplan: Appropriate permissions to execute the
  Transact-SQL statements. SHOWPLAN permission on all databases
  containing objects referenced by the Transact-SQL statements, such as
  tables, views, and so on.

Now to answer your question about "a query to see if a user, other than myself, has SHOWPLAN permissions?"

You will need to find if the user is a member of any of these 3 roles or SHOWPLAN privilege is granted explicitly.

exec sp_helpuser 'test' will give the list of roles a user is a member of. For explicit privilege you can use this and check if SHOWPLAN privilege was grated explicitly. This script is modified from here.

SELECT
USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
dppriper.class_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.permission_name,
dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE USER_NAME(dppriper.grantee_principal_id) ='TEST'

Here is my test:

USE [master] GO CREATE LOGIN test WITH PASSWORD  ='tes545###45454545454t'
GO
USE [WideWorldImporters]
GO

CREATE USER test FOR LOGIN test
GO
GRANT SHOWPLAN TO test

Now run the query above and you will see the explicit SHOWPLAN permission for user test.

Once you run above tsql you will be able to see the permission for the securable (database name is this case) in the bottom window 'permission for .....'  (you have to add the database as a securable first) .