What ciphers need to be enabled to install and run SQL Server 2014 SP1 on Windows 10? Our organization is trying out Windows 10 and our distribution had some modifications to the ciphers in use due to recent security concerns.

As it stands SQL Server 2014 Developer SP1 fails to install properly. The services fail to start. I have tried enabling TLS 1.0 trough TLS 1.2, this led to me being able to start the SQL Engine using Local Service Account. But not SQL Agent or Browser. Nor could i connect to the server locally with SMSS all logins fail (sa or the current windows user).

Disabled SSL/TLS:
SSL 3.0 / TLS 1.0 / TLS 1.1
Enabled TLS:
TLS 1.2

Disabled Ciphers:
DES 56/56
NULL
RC2 128/128
RC2 40/128
RC2 56/128
RC4 40/128
RC4 56/128
RC4 64/128
RC4 128/128
Enabled Ciphers:
AES 128/128
AES 256/256
Triple DES 168/168

Hashes:
MD5 (Disabled)
SHA (Enabled)
Diffie-Hellman (Enabled)
PKCS (Enabled)

Ciphers are then sorted from most secure to least secure.

Setup Bootstrap Log:

  Feature:              Database Engine Services
  Status:               Failed: see logs for details
  Reason for failure:   An error occurred during the setup process of the feature.
  Next Step:            Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:       SQL Server Database Engine Services Instance Features
  Component error code: 0x851A0019
  Error description:    Could not find the Database Engine startup handle.

ERRORLOG when trying to start SQL Server Engine during setup:

Error: 17190, Severity: 16, State: 1.
Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

My next course of action is to try to enable the most likely culprits like SSL 3.0 and then move on to enabling ciphers.

But if anyone can shed some light as to the requirements of SQL Server 2014 SP1 or if i am looking in the wrong place, please let me know.

After re-enabling all the ciphers and SSL 3.0 I completely uninstalled the broken installation.

I logged out and used a second administrator account to wipe the User Profile who installed the SQL Server. I removed any fragments of SQL Server Installation in the registry that i could find.

Also i deleted any user profile created by SQL Server and removed them from the registry.

After this the Installation worked, almost anyways, the default Service account for SSAS failed to start the AS Service but switching to another service account worked.

Still not sure if it was a corrupted User Profiler or the fact that SSL 3.0 and Ciphers were disabled. It could have been a combination.


Should a MySQL slave used for backup also be used to service read-only requests in a production environment? Based on best practices, I'm wondering if we should have at least one slave that's used solely as a backup server, without servicing any forward-facing requests from users.

For example, if we have a master server with a single slave, and that slave is used to back up data via mysqldump a few times a day, are there any compelling reasons not to use that same slave to calculate some quick-to-load (<10 seconds) analytics for a handful of customers in production?

Not a proper slave because you are not using mysql replication but anyway it's very common to have an slave or more than one to reduce the load in the master server.
Make really sure that its only used for read queries.


mysql encryption key storage on file I have a MYSQL server on a linux server. I am using AES encryption functions to store data in the DB server. Commands are like this ones:

INSERT into userc (name, town) VALUES ('john',AES_ENCRYPT('nebraska', 'usa2010'));
SELECT CAST(AES_DECRYPT(town, 'usa2010') AS CHAR(50)) town_decrypt from userc;

My concern on this kind of encryption is that everything needed to access my data is travelling in clear, so a sniffer or a Debug level log are capable of capturing everything.

Is there a way of not sending the key in the command, but having it stored on a file (/home/user/key.txt) and so calling the encryption in a way similar to this:

INSERT into userc (name, town) VALUES ('john',AES_ENCRYPT('nebraska', key1));

Where key1 is the reference to the file where the key is stored?

Depending in your perspective, the preferable approach would be to continue doing it the way you're doing it, but to use SSL to encrypt communications between the application server and the database -- because if you don't, then not only are you sending the encryption key in the clear, you're also sending the decrypted data in the clear.

It seems a little bit pointless to protect the encryption key from packet sniffer snooping, while continuing to transmit the data you're trying to protect, over the same channel, also in the clear.

Granted, the exposure would theoretically be less, since you wouldn't be giving away the key, itself, but you're still exposing the "secure" data.

You could avoid passing the key over the wire by declaring a stored function that returns the key, and using a call to the stored function in place of the constant in the queries, but that creates what is arguably a worse scenario, because a breach of your database's security exposes both the encrypted data and the keys necessary for decryption.  But, this would avoid the specific issue that you're intending to avoid.

mysql> CREATE FUNCTION aes_key () RETURNS TINYTEXT DETERMINISTIC NO SQL RETURN 'usa2010';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT aes_key();
+-----------+
| aes_key() |
+-----------+
| usa2010   |
+-----------+

Now, your queries look like AES_ENCRYPT('nebraska', aes_key()).  As I say, this does accomplish the direct purpose you intend, and the permissions on the stored function would serve to keep honest people honest, but putting it right out there on the server along with the data seems like a particularly bad idea.

Perhaps slightly more orthodox would be to compile a simple User Defined Function (UDF) in C, which would return a constant string -- the decryption key -- when called, and reference that function in the queries.  The only advantage of this approach is that the UDF wouldn't be returned if an attacker did a full database dump to extract the data.  One potentially significant disadvantage is that user-defined functions are global and are not controlled by permissions -- they are accessible to any user who can connect to the database server.

And, of course, either of these alternatives, like the mechanism you proposed in the question, involves storing the encryption key on the hard drive of the database server, where it is more vulnerable if the database server is breached.  If the key is stored with the application code, then both platforms have to be breached for your sensitive data to be effectively stolen.

So, no... I would suggest that the appropriate place for storing the encryption key would be somewhere other than the database server itself, specifically in the application server, and using SSL to connect from the application to MySQL if data on the wire between the two systems is a security concern.


How to execute a query from psql without waiting for the result? My query (to create a new table from existing table) takes a very long time. So I've set up a remote database in my office - more RAM there.

I can connect to my database from home as usual with psql.

How can I tell the remote server to execute my query from the terminal without having to wait for a response?

(postgresql-9.2 , linux enviroment)

Edit:
I'm open to other solutions, it's not necessary to use psql

You can try sending psql to the background:

psql -f your_sql_file.sql &

Or, connecting to the local DB, you can use dblink to dispatch a query to the remote DB:

SELECT dblink_connect('your_connection_name', 'your_connection_string');
SELECT dblink_send_query('your_connection_name', 'your_query');

Note that dblink_send_query can only send one query at a time.  So, if you want to run multiple SQL statements, this is not your solution.

Or, you can start a pg_agent job on the remote server, which requires no manual intervention, therefore the state of your home box has no effect on running your job.  The same can be achieved by setting up a cron (or even better, at - thanks, Erwin) job executing your script.

Additionally, if you have a long running job which you start manually, you can start a screen session on the server and run the file from there.  In this case, you can log off and go home, and the script will keep running.


Define calculated column of BOOLEAN/TINYINT(1) type in a View? I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view:

CREATE VIEW `ItemView` AS
SELECT Item.ID
    ,ItemProperty1
    ,ItemProperty2
    ,ItemProperty3
    ,EXISTS(SELECT ItemComponent.ItemID FROM ItemComponent WHERE ItemComponent.ItemID = Item.ID) AS Operation1Required
    ,Item.Property4 <> ItemType.Property AS Operation2Required
FROM Item
INNER JOIN ItemType
    ON Item.TypeID = ItemType.ID
ORDER BY ItemProperty2 ASC

At the moment, MariaDB metadata says that the type for the "Operation1Required" and "Operation2Required" columns is INT, even though they are both expressions with fundamentally Boolean results.

I want them to be BOOLEAN/TINYINT(1), because the view is being read by a strongly-typed client which is expecting Boolean values for these columns.

But the CAST() and CONVERT() functions in MySQL/MariaDB 10.1 don't seem to be able to convert to TINYINT(1).

Is there another way I can specify the correct data type for these calculated columns in the View?

I looked at How to cast an integer to a boolean in a MySQL SELECT clause? and tried it out, but the results of IF(expression, TRUE, FALSE) still seem to be INT according to phpMyAdmin's report of the view's structure.

This is currently impossible.

MySQL doesn't have a real BOOLEAN type, (or a real array type.. or a real JSON type). It has an alias for TINYINT.
Any condition returns an integer. This is the fastest datatype for a CPU, and presumably this implementation detail is reflected here. For instance, 'true' IS TRUE and 1=1 both return 1 as an int.
CAST does not provide a TINYINT format.

I would highly suggest migrating to PostgreSQL. It's so much less terrifying... and liberating.


Migrate foreign key type from char to binary - ways to deal with the fallout? I am in the process of slimming down a database that has been using char/varchar fields where binary/varbinary would be a better choice (given the data represented is byte arrays).

One of the fields I am changing is used in foreign key contraints on a number of other tables.

I cannot drop the current FK constraints, migrate the column to its new type and then simply re-create the FK constraints as the data types would not match.

What approach should I take in migrating this? How would you go about it?

Given the next example:

CREATE TABLE A
(
    [ID] VARCHAR(10) NOT NULL,
    CONSTRAINT [PK_A] PRIMARY KEY ([ID])
);

CREATE TABLE B
(
    [ID] INT PRIMARY KEY,
    [A_ID] VARCHAR(10) NOT NULL,
    CONSTRAINT [FK_B] FOREIGN KEY ([A_ID]) REFERENCES A([ID])
);

CREATE TABLE C
(
    [ID] INT PRIMARY KEY,
    [A_ID] VARCHAR(10) NOT NULL,
    CONSTRAINT [FK_C] FOREIGN KEY ([A_ID]) REFERENCES A([ID])
);

INSERT INTO A VALUES ('001'), ('010'), ('100');
INSERT INTO B VALUES (1, '001'), (2, '001'), (3, '010');
INSERT INTO C VALUES (4, '010'), (5, '100'), (6, '100');

0- Backup, backup & backup your database.

1- Due you are trying to convert varchar to varbinary and there isn't an explicit conversion you should add new columns to your tables. NOTE: You cannot make it non nullables.

ALTER TABLE A ADD [ID_VB] VARBINARY(10);
ALTER TABLE B ADD [A_ID_VB] VARBINARY(10);
ALTER TABLE C ADD [A_ID_VB] VARBINARY(10);

2- Copy current values to the new columns:

BEGIN TRANSACTION
  UPDATE A SET [ID_VB] = CAST([ID] AS VARBINARY(10));
  UPDATE B SET [A_ID_VB] = CAST([A_ID] AS VARBINARY(10));
  UPDATE C SET [A_ID_VB] = CAST([A_ID] AS VARBINARY(10));
COMMIT TRANSACTION

3- Drop current constraints:

ALTER TABLE B DROP CONSTRAINT [FK_B];
ALTER TABLE C DROP CONSTRAINT [FK_C];
ALTER TABLE A DROP CONSTRAINT [PK_A];

4- Once you have checked new values are correct, drop actual columns:

ALTER TABLE A DROP COLUMN [ID];
ALTER TABLE B DROP COLUMN [A_ID];
ALTER TABLE C DROP COLUMN [A_ID];

5- Rename new columns with old names:

EXEC sp_rename 'A.ID_VB', 'ID', 'COLUMN';
EXEC sp_rename 'B.A_ID_VB', 'A_ID', 'COLUMN';
EXEC sp_rename 'C.A_ID_VB', 'A_ID', 'COLUMN';

6- Make new columns not nullables:

ALTER TABLE A ALTER COLUMN [ID] VARBINARY(10) NOT NULL;
ALTER TABLE B ALTER COLUMN [A_ID] VARBINARY(10) NOT NULL;
ALTER TABLE C ALTER COLUMN [A_ID] VARBINARY(10) NOT NULL;

7- Add constraints again:

ALTER TABLE A ADD CONSTRAINT [PK_A] PRIMARY KEY ([ID]);
ALTER TABLE B ADD CONSTRAINT [FK_B] FOREIGN KEY ([A_ID]) REFERENCES A([ID]);
ALTER TABLE C ADD CONSTRAINT [FK_C] FOREIGN KEY ([A_ID]) REFERENCES A([ID]);

8- Check final result:

SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;

| ID       |
| :------- |
| 0x303031 |
| 0x303130 |
| 0x313030 |

ID | A_ID
-: | :-------
 1 | 0x303031
 2 | 0x303031
 3 | 0x303130

ID | A_ID
-: | :-------
 4 | 0x303130
 5 | 0x313030
 6 | 0x313030

db<>fiddle here


Why did Postgres UPDATE take 39 hours? I have a Postgres table with ~2.1 million rows. I ran the below update on it:

WITH stops AS (
    SELECT id,
           rank() OVER (ORDER BY offense_timestamp,
                     defendant_dl,
                     offense_street_number,
                     offense_street_name) AS stop
    FROM   consistent.master
    WHERE  citing_jurisdiction=1
)

UPDATE consistent.master
SET arrest_id=stops.stop
FROM stops
WHERE master.id = stops.id;

This query took 39 hours to run. I am running this on a 4 (physical) core i7 Q720 laptop processor, plenty of RAM, nothing else running the vast majority of the time. No HDD space constraints. The table had recently been vacuumed, analyzed, and reindexed.

The whole time the query was running, at least after the initial WITH completed, CPU usage was usually low, and the HDD was in use 100%. The HDD was being used so hard that any other app ran considerably more slowly than normal.

The laptop's power setting were on High performance (Windows 7 x64).

Here's the EXPLAIN:

Update on master  (cost=822243.22..1021456.89 rows=2060910 width=312)
  CTE stops
    ->  WindowAgg  (cost=529826.95..581349.70 rows=2060910 width=33)
          ->  Sort  (cost=529826.95..534979.23 rows=2060910 width=33)
                Sort Key: consistent.master.offense_timestamp, consistent.master.defendant_dl, consistent.master.offense_street_number, consistent.master.offense_street_name
                ->  Seq Scan on master  (cost=0.00..144630.06 rows=2060910 width=33)
                      Filter: (citing_jurisdiction = 1)
  ->  Hash Join  (cost=240893.51..440107.19 rows=2060910 width=312)
        Hash Cond: (stops.id = consistent.master.id)
        ->  CTE Scan on stops  (cost=0.00..41218.20 rows=2060910 width=48)
        ->  Hash  (cost=139413.45..139413.45 rows=2086645 width=268)
              ->  Seq Scan on master  (cost=0.00..139413.45 rows=2086645 width=268)

citing_jurisdiction=1 only excludes a few tens of thousands of rows. Even with that WHERE clause, I'm still operating on over 2 million rows.

The hard drive is whole drive-encrypted with TrueCrypt 7.1a. That slows things down a bit, but not enough to cause a query to take that many hours.

The WITH part only takes about 3 minutes to run.

The arrest_id field had no index for foreign key. There are 8 indexes and 2 foreign keys on this table. All other fields in the query are indexed.

The arrest_id field had no constraints except NOT NULL.

The table has 32 columns total.

arrest_id is of type character varying(20). I realize rank() produces a numeric value, but I have to use character varying(20) because I have other rows where citing_jurisdiction<>1 that use non-numeric data for this field.

The arrest_id field was blank for all rows with citing_jurisdiction=1.

This is a personal, high end (as of 1 year ago) laptop. I am the only user. No other queries or operations were running. Locking seems unlikely.

There are no triggers anywhere in this table or anywhere else in the database.

Other operations on this database never take an abornmal amount of time. With proper indexing, SELECT queries are usually quite fast.

I had something similar happen recently with a table of 3.5 million rows. My update would never finish. After a lot of experimenting and frustration, I finally found the culprit. It turned out to be the indexes on the table being updated.

The solution was to drop all indexes on the table being updated before running the update statement. Once I did that, the update finished in a few minutes. Once the update completed, I re-created the indexes and was back in business. This probably won't help you at this point but it may someone else looking for answers.

I'd keep the indexes on the table you are pulling the data from. That one won't have to keep updating any indexes and should help with finding the data you want to update. It ran fine on a slow laptop.


How to reorganize MS SQL Server database I'm looking for a way to reorganize a MS SQL Server database. By re-organize I mean that both tables and indexes are left with no fragmentation.
The resulting database will only be used in read-only mode. One of the aims of the exercise is to release space in the database and the other is to try to make it go as fast as possible. (assuming here that a contiguous layout of both tables and indexes actually has a positive impact on performance, I understand there's some debate about this in modern SSD world  ... but at the very least it doesn't have a negative impact on performance). All in all the result will be a frozen copy of its source. The clone-and-compact operation must take place periodically and must be able to run unattended.
The size of actual data (tables and indexes) is approx 500 GB but there's a lot of unused space so the on-disk size is almost 2 TB. I don't care if the clone is inaccessible during the reorganization operation, nor do I care if it takes days to execute the reorg. But it must be automated (meaning it must run unattended, please don't point me to MS SSMS)
I come from PostgreSQL background. In PostgreSQL I would use pg_dump/ pg_restore to export/import the whole database and such operation would by its very nature re-write tables and indexes to be contiguous. In Oracle world a so-called exp / imp would achieve the same if I remember correctly. Isn't there are similar export/import tool in MS SQL world ?
I've looked at:

BACKUP / RESTORE.  Not applicable. This method retains table and index fragmentation.
DBCC SHRINKFILE / DBCC SHRINKDATABASE. These methods release unused space at the tail. Not quite what I'm after. They are also insanely slow as they are meant to execute while the database is operational. ... I appreciate the reorg-while-online feature but it is not what I need. (I haven't been able to find any docs if such operations can be made go faster if I put the db into SINGLE_USER mode or whatever)
bcp. Can only export table data, not a full database with all its tables, constraints, indexes, users, triggers, stored procedures, etc.

I'm looking for a Microsoft way of doing this. Buying a third-party tool just to do this type of export/import doesn't seem fair to me. SQL Server is expensive enough as it is.
Perhaps I'm just hung up on how this is done in other database engines and therefore looking for information in the wrong place?

Edit 1: Aaron noted in a comment that compression can be beneficial here. Good point. Compression options include columnstore indexes (clustered or nonclustered - let's not go into details at this point), row compression and page compression.
As you know, backup and restore will give you a binary copy of the database, so it won't help you.
Shrink will fragment the indexes even more since it move pages towards the beginning of the file without consideration of the page ordering of an index.
So you are left with index reorg or rebuild. Rebuild is more thorough since it creates a new index. But it need space for this. So if you shrink and then rebuild, then the rebuild will create a new index "at the end of the file" possible making the file grow (depending on how much free space you left when you did the shrink).
So, it is a bit of a contradiction to both have stuff nicely aligned and have no free space. What you can hope for is to have free space corresponding to you your largest allocation (index/heap) in the file. I.e., "working space" for the largest one.
(If that isn't acceptable, then you are left with deciding which allocations are too large to have spare room for (remember, you only need spare room for one). For indexes you can drop them, shrink and then create. For the data (clustered index and heap) you'd have to export outside SQL Server, truncate the table, shrink and then import the data.
Edit 2: Nikita added a good point on moving the data to a different filegroup as an option to exporting it outside SQL Server. Good point and definitely worth considering. If you decide to go the "get the data out of the filegroup" route.)
How to go about this? Use Ola's scripts as suggested by Nikita for your B-trees. And for your heaps (I hope you don't have that many) you can for instance use my stored procedure.
So, shrink first, then rebuild your b-trees and heaps. And it can take a very long time, especially the shrink (depends on if you have LOBs, heaps etc).
Come to think about it, we have two options for exporting and importing the data:
BACPAC. This will give you a zipped file containing an XML file describing your tables and BCP files with the data. And then you use a tool to import based on this. You can use BACPACs from SSMS (right-click a database and the databases folder), sqlpackage.exe and of course there are a few Powershell options. BACKPACs are designed to work with Azure SQL database so it will choke on anything what isn't available there, AFAIK.
SSMS Generate Script. You find this if you right-click a database. This will create a .sql file containing DDL and INSERT. Cross your fingers that it does it right. And for 500 GB..., I have my doubts. Anyhow, you can also access this functionality using SSIS, if that is beneficial for you.


Why is the query_hash zero for EXEC statements? I'm setting up a monitoring system for SQL Server using Extended Events to find heavy queries as 'production feedback' for our developers. I'm using the events sp_statement_completed and sql_statement_completed, with predicate filters on cpu_time, logical reads, etc.  I was hoping to aggregate the results on database_name and query_hash like demonstrated in numerous examples all over the internet, but in the results I see that query_hash is 0 for all statements using EXEC, like in the table below (timestamp and queryhash shortened for readability).

name                       timestamp      query_hash plan_handle        statement
sql_statement_completed    2016...6414    0          050056019600764... exec Shared.dbo.SyncFirm
sql_statement_completed    2016...9946    0          06003d00e01e730... exec spSetUserAuth @userid;
sql_statement_completed    2016...7184    0          0600e30028c9da0... exec spSetUserAuth @userid;
sp_statement_completed     2016...0409    9826...578 0600c00028e6aa0... SELECT obfuscated_columns FROM dbo.SomeTable
sp_statement_completed     2016...1448    8660...775 060084006d2d660... INSERT INTO dbo.SomeTable ( obfuscated_columns)  EXEC(@sql)
sql_statement_completed    2016...7752    0          0600f9006c23f03... exec spSetUserAuth @userid;
sql_statement_completed    2016...1443    1304...641 06005a0008a9b11... select SUBQ.ontrackstatus, COUNT(SUBQ.ontrac

All results do have a value for plan_handle and they're all different, so a lot of plans are being generated. Other statements without query_hash (that I've seen) include ALTER INDEX, CHECKPOINT, UPDATE STATISTICS, COMMIT TRANSACTION, FETCH NEXT FROM Cursor, some INSERTs, SELECT @variable, IF(@variable = x).

Does anybody know why the query_hash is 0? I'm probably missing the point somewhere about the SQL Query Analyzer and EXEC, but I'm not able to find any clues to point me in the right direction. If the results I'm having are 'normal', then how to best aggregate the results?  Wouldn't grouping by statement include literals, whitespace, etc... which is removed when calculating query_hash?

EDIT: as I see it now, EXEC SomeStoredProcedure, starts a stored procedure (obvious), and the individual statements in that stored procedure end up in the event session as sp_statement_completed events, and those all have a query_hash.

So for sp_statement_completed (i.e. 'real' queries), I can aggregate on query_hash and database_name, and for sql_statement_completed without query_hash (the EXEC SomeStoredProcedure), I can use the client_connection_id to group the statements within a specific execution of a stored procedure, to see what's the most costly part of the procedure.

To explain why the hash is created:

When we submit a query to the server, the algebrizer (yes that's what it is called) process creates a hash, like a coded signature, of the query. The hash is a unique identifier. An identifier is unique for any given query, including all the text that defines the query, including spaces and carriage returns, the optimizer compares the hash to queries in the cache. If a query exists in the cache that matches the query coming into the engine, the entire cost of the optimization process is skipped and the execution plan in the plan cache is reused.

EXEC starts a stored procedure which can have its code change, since SQL Server knows that it does not need to compare EXEC to optimize it, SQL Server does not create a hash.


Audit select queries against specified tables by specified users I have a requirement to audit all SELECT queries (by access) against specified tables by specified users.

As far as I can tell, this can't be done with normal auditing; it only supports auditing all queries by specified users or all queries agains specified tables (by any user). This is a problem because there are system accounts that would generate too much audit and needs to be excluded from audit.

This leaves fine grained auditing, where it is possible to do this by specifying appropriate predicate for audit_condition parameter in dbms_fga.add_policy. However, FGA has unfortunate effect of writing duplicate rows in audit trail when parallel query is used; not desirable when the goal is to minimize audit information.

Currently, I'm planning to go with FGA and frequently remove the duplicate rows. Is there any better way to implement this auditing strategy?

I have found a way to prevent duplicate rows in FGA audit trail with parallel query

First create a function that return 1 if AUTHENTICATION_METHOD is PQ_SLAVE in USERENV context (coordinator process gets 0)

create or replace function is_pg_slave return integer as
begin
  if (sys_context('USERENV', 'AUTHENTICATION_METHOD') = 'PQ_SLAVE') then
    return 1;
  else
    return 0;
  end if;
end;
/

Then add policy to table

begin
      dbms_fga.add_policy(
        object_name       => 'table_name',
        policy_name       => 'noaudit_pq_slave',
        audit_condition   => 'is_pg_slave = 0',
        statement_types   => 'SELECT');
end;
/

Now only the query coordinator is audited, so no matter what the parallel degree is, only one row is inserted into fga audit trail.

I have not noticed any performance issues with this approach compared to normal auditing or no auditing.


is it possible to get innobackupex to print the filenames it's busy with? I am getting crashes when trying to run innobackupex --apply-log. How can I find out what files it's processing at the time? I don't see a relevant option mentioned in the documentation.

Check the version you used to backup the data is the same as the one you are trying to use to restore. This can bite.


Synchronize Queries Retrieving Sessions And Locks From DMVs I have a SQL script that I run when I want to see what is happening in the database.  The script has many queries that return information from DMVs, but the two I use most are "Requests"  (sys.dm_exec_requests, sys.dm_exec_sessions, etc.) and "Locks" (sys.dm_tran_locks).  The output is similar to SQL Server Activity Monitor, but it displays more information.

Sometimes, a request appears in the Requests query, but it completes before the Locks query runs.  For example, the Requests query may show SPID 51 is waiting on a lock resource, but the Locks query does not include any lock information for SPID 51.  (I know about the wait_type and wait_resource columns from sys.dm_exec_requests.)

Is there a way to ensure that these two separate queries display a coherent snapshot of database activity?

I expect commercial database monitoring applications must encounter the same problem.

I have experimented with running these queries in SERIALIZABLE concurrency and added locking hints to the DMV joins, but the queries acquire no locks.  I would not want to do this in production anyway.

The best ideas I have so far are:

Run these queries simultaneously from different sessions.
Join requests and locks together in one query.  Considering that I have seen over 100,000 locks at once, this join would return a lot of duplicate request and session data, but it might work.

I am not familiar enough with extended events to know if they would work better-- event pairing, maybe?

Not possible. These DMVs do not offer even self-consistency guarantees. For instance it is possible for a resource to appear in different mutually exclusive states, in the same run, in the same result set. If such DMVs would take the necessary steps to 'stabilize' the data during a scan the results would be disastrous for performance (imagine all locks being frozen in place while a scan over sys.dm_tran_locks is progressing).

Extended Events do offer a much better picture, but obviously is the picture of events, not of state. In theory is always possible to reconstruct state from the sequence of events knowing the initial state and achieve the same picture you get from the state dump (the DMVs) but that is only theory...


How to access apex data from sqlplus? I have setup an oracle database in my Ubuntu machine. Now I have took the following steps to create tables

1) login to my ubuntu account

2) su oracle

3) sqlplus "sys as sysdba"
//Connected to an idle instance

4) sql prompt sql>startup

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

5) in sql prompt.. sql> exec dbms_xdb_config.sethttpport(8080);

6) sql> exit

7)lsnrctl start

Then I have login to oracle apex in a workspace which I created earlier and created a table named  person

but when I again login to to sqlplus like sqlplus "sys as sysdba" and run the following query

select * from person
It says , no table created.

I am a newbie in database , so please tell me how do I access the tables I have created in apex , from sqlplus console ? It used to be so simple in 10g.

Table person is situated in schema assigned to your apex workspace. You can go to APEX IDE to the section SQL Workshop -> Object Browser, schema name will be displayed in the top right part of the page. Or you can execute query:

select owner from all_tables
where table_name = 'PERSON';

You will get names of all users that have table with such name.
After that you need to use this name in your query:

select * from schema_name.person


Unable to restore (error 3456) I have a situation that is not easy to figure out, and thought I'd ask on this forum if others might have suggestions.

I'm running SQL Server 2008 R2 Standard SP3 on Windows Server 2008R2 Enterprise.

A database needed some maintenance, and after the fact I needed to restore on another server. I have a full db backup done with COPY_ONLY plus a set of 4 tlog backups.

before starting, create tlogbackup1
change from FULL to BULK_LOGGED recovery model
add new filegroup
add file to newfilegroup
set newfilegroup to be default
select into table (on newfilegroup)
drop original table
delete original file
delete original filegroup
change name of new table to match original table
change file name of newfilegroup to match original filegroup
change file name in catalog to match original file name
change file name at OS level to match original file name
set default filegroup to be the original
bring db online
change from BULK_LOGGED to FULL recovery model
After all steps have completed, create tlogbackup2

The restore of all backups must use WITH MOVE, due to drive letter changes on the restore server.

Recovery steps:

RESTORE database SomeDB FROM DISK = 'D:\REPRO\SomeDB.bak'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

RESTORE LOG SomeDB FROM DISK = 'D:\REPRO\tlogbackup1.trn'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

RESTORE LOG SomeDB FROM DISK = 'D:\REPRO\tlogbackup2.trn'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

The final tlog restore gets to 100% and then fails with error 3456:

  Processed 368 pages for database 'SomeDB', file 'SystemData' on file 1.

  Processed 7656520 pages for database 'SomeDB', file 'SystemDataPDS' on file 1.

  Processed 172430 pages for database 'SomeDB', file 'SystemData_log' on file 1.

  Msg 3456, Level 16, State 1, Line 1
  Could not redo log record (210388:123648:232), for transaction ID (0:1016710921), on page (4:8088), database 'SomeDB' (database ID 6). Page: LSN = (0:0:1), type = 11. Log: OpCode = 4, context 11, PrevPageLSN: (210388:122007:1). Restore from a backup of the database, or repair the database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

Just to verify that the full db backup was ok, I restored it ran CHECKDB, and there were no errors.

All feedback welcomed.

Thanks in advance,

Ned Otter

In order to understanding why error 3456 would be thrown, we need to take a little step back and understand how SQL Server handles this corner of recovery.

When SQL Server is redoing an operation, and that redo is a page modification, it makes a quick check.  In the page header there is ultimately going to be a PageLSN, which is an indication of the last LSN that has modified that page, recorded by the page.  Think about it like this, the page keeps track of the last LSN that has made modifications to it.  This is the PageLSN.

Every time there is a logged page modification operation, that log record includes a few LSNs.  Namely, the log record's LSN (think... Current LSN), and then it has what's called the Previous Page LSN (PrevPageLSN going forward).  So when we modify a page, one of the pieces of data that is put into the log record is what the page indicates as being the last LSN before you to have modified the page.

Think about it like this...  Your car needs to have work done on it.  Mechanic John works on your car, and in the engine bay it has a little tag and Mechanic John writes "John worked on this car last".  Then the next time you take your car in to another shop, Mechanic Mark looks in the engine bay and sees that Mechanic John worked on this car last.  On his data sheet he writes this information.  Same idea with SQL Server.

This can be somewhat confusing, so take a look at this image below on sequential page modifications, and how the PageLSN and PrevPageLSN relate:

Let's loop back around, as this all comes into play when you need to redo an operation on a page (restores, recovery, HA, etc.).  When SQL Server needs to redo a page operation, it makes a sanity check to see if the PageLSN on the page matches the PrevPageLSN that the log record includes.  If that is not equal, then you will see error 3456 get thrown.

Does PageLSN equal PrevPageLSN?  No???  Stop and raise error 3456...

Let's analyze your error message, which includes the how:

  Could not redo log record (210388:123648:232), for transaction ID (0:1016710921), on page (4:8088), database 'SomeDB' (database ID 6). Page: LSN = (0:0:1), type = 11. Log: OpCode = 4, context 11, PrevPageLSN: (210388:122007:1). Restore from a backup of the database, or repair the database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

I have bold'd the two pieces of data that have an inequality causing the error.  You can see that our PageLSN is 0:0:1 (this was found in the page's header), and our PrevPageLSN is 210388:122007:1 (this was found in the data on the log record that was attempting to be redone).  These are obviously not equal, hence err3456.

So in order to find out the why of this event, would be to find out why there is a disparity here.  We really need to trace the lifecycle of page 4:8088 and see where the disconnect is.  Unfortunately without further information, or hands-on troubleshooting there isn't much else I can do besides give you the background of this recovery operation and what causes the error.


Custom SQL Server Computer Build I hope this is the right place to ask this question. I'm a user at StackOverflow, and when I perused the StackExchange websites, this was the best match I could find. So, if I've posted in the wrong place, please let me know and I'll move the post.

I'm working with a group of people and we're creating a start-up investing firm. I'm the "tech guy" in the group (I can build PCs and program), and so it's up to me to figure out what we need for our database server.

We're going to use SQL Server because it's what I know (so, please don't suggest MySQL, Oracle, etc...).
I'm familiar with purchasing components and assembling a PC, although I've never done it specifically for a high-performance database server before.
Assume we have a several thousand dollar budget (my co-investers are doctors).
Assume we have a constant stock market data feed (something like 100 stocks at a time) that needs to log the data to a database as it comes in and also use the current data to query for similar past data and perform comparisons.

What I'm mainly interested in is where to spend more money and where additional power is less useful.

For instance, when it comes to drive set up, I was thinking of something along the following lines:

For the OS and SQL Server application installation, a RAID 1 array of 2 60 GB SSDs.
For the SQL Server data volume, a RAID 5 array of 4 medium-sized, fast HDDs (I'm thinking 300GB Velociraptor drives; So far, 9 YEARS of data takes up only about 100GB).
For the SQL Server log file volume, a RAID 1 array of 2 small-to-medium-sized, adequate HDDs (non-Velociraptor here).
For the SQL Server temp database volume, a RAID 1 array of 2 60 GB SSDs.

The OS/SQL Server application volume will be a software RAID using the motherboard; the rest of the RAID arrays will be controlled by a dedicated RAID card.

From what I've read online, the above setup is likely to give me great performance and stability.

Now, when it comes to other components, what's necessary?

Is the latest i7 Quad Core useful for this, or is that overkill with processing power? Can I get away with an i5? An i3? A Core 2 Quad?
I'm assuming that a 64-bit OS with 64-bit SQL Server is better because it will allow me to go beyond 4GB RAM. If that's true, is 8GB enough or do I actually get performance benefits at 16GB? Does DDR3 RAM offer significant performance enhancements?
I know that the graphics on this machine are extremely unimportant, but would a dedicated graphics card off-load work from the CPU, increasing performance, or is that a waste of money?

Most of the work I've done with SQL Server has been for small companies. As a result (due to price), we've mostly done installations of SQL Server Workgroup. For everything I've ever done, that's been just fine and dandy, and I'd like to continue using that version. Is there any practical benefit to upgrading to Standard?

Now, I'm guessing that for our needs, a server like this will cost about $5,000-$6,000. Am I better off buying the components myself and building the system or buying a server from Dell/HP?

Also, (and I know this is a big wrench in things), if, during the initial, testing phase, we decided that we didn't want to spend $6,000, what cuts would you make to bring this down to a $2,000 system?

This is essentially a hardware capacity planning question. The problem with them is that if you give enough details that we can tell you what hardware you need, it'll become "too localized" to your particular set of circumstances. That said, we've got a number of pseudo-answers already written to help you:

Can you help me with my capacity planning?
How do you do Load Testing and Capacity Planning for Databases
How do you do Load Testing and Capacity Planning for Web Sites

Those are from Server Fault's list of Canonical Question/Answers.

They're not all directly related to what you're asking for, but tangentially similar. It boils down to the fact that this is your job; if you want to get it right you really either need to learn how to do it, or find a consultant who does.

One last thing; the TCO of building your own server from components is always higher on the average than buying a prebuilt server from HP or Dell. I happen to prefer HP, but the SE Sites run on Dell and the site seems to work quite well. It will be cheaper up front to build your own; but warranty, compatibility, scalability, and support will all chew that cost savings like a hungry dog with a fresh steak.

Edit:
Since you're discussing RAID levels in the comments above: What are the different widely used RAID levels and when should I consider them?


Writing a view that caches to another table I have a table market_trades with columns timestamp, market_id, and amount:

api_production=# \d market_trades;
                                       Table "public.market_trades"
     Column     |            Type             |                         Modifiers
----------------+-----------------------------+------------------------------------------------------------
 id             | integer                     | not null default nextval('market_trades_id_seq'::regclass)
 market_id      | integer                     | not null
 timestamp      | timestamp without time zone | not null
 amount         | numeric(16,8)               | not null

And I just created a table market_trades_sum_cache:

api_production=# \d market_trades_sum_cache;
                                       Table "public.market_trades_sum_cache"
     Column     |            Type             |                         Modifiers
----------------+-----------------------------+------------------------------------------------------------
 id             | integer                     | not null default nextval('market_trades_sum_cache_id_seq'::regclass)
 market_id      | integer                     | not null
 start_time     | timestamp without time zone | not null
 end_time       | timestamp without time zone | not null
 sum            | numeric(16,8)               | not null

I want to write a view (or some other type of reusable function) called trade_volume that takes a market_id, start_time, and end_time as parameters and does the following:

If there is a row in market_trades_sum_cache with the given market_id, start_time, and end_time, return the corresponding sum.
Otherwise, add up the amounts of all rows in market_trades with the given market_id and whose timestamp is between start_time and end_time, store that sum as a new row in market_trades_sum_cache, and return the sum.

Is this possible in PostgreSQL 9.3?

BONUS: Use PostgreSQL's timestamp range types (tsrange in PostgreSQL 9.2+) instead of start_time and end_time in market_trades_sum_cache.

CREATE OR REPLACE FUNCTION trade_volume (_market_id  int
                                       , _start_time timestamp
                                       , _end_time   timestamp)
  RETURNS SETOF numeric(16,8) AS
$func$
BEGIN

RETURN QUERY
SELECT sum                          -- dubious identifier
FROM   market_trades_sum_cache
WHERE  market_id  = _market_id
AND    start_time = _start_time
AND    end_time   = _end_time;

IF NOT FOUND THEN
   RETURN QUERY
   INSERT INTO market_trades_sum_cache(market_id, start_time, end_time, sum)
   SELECT market_id
        , _start_time
        , _end_time
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
END IF;

END
$func$ LANGUAGE plpgsql VOLATILE;

A couple of warnings

It's a bad idea to use names of basic functions (sum) or data types (timestamp) as column names.
BETWEEN includes lower and upper bound. Often, you would rather include the lower, but exclude the upper bound.
There is a tiny chance for a race condition in the above function. To be prepared for heavy concurrent load, consider this related answer:

Is SELECT or INSERT in a function prone to race conditions?

Alternative with tsrange

...
AND    ts_range = tsrange(_start_time, _end_time, '[]')
...
   INSERT INTO market_trades_sum_cache(market_id, ts_range, sum)
   SELECT market_id
        , tsrange(_start_time, _end_time, '[]')  -- incl. bounds
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
...

I would consider a multicolumn GIN or GiST index for the range type. Though, if you only check for equality, a btree index would do the job, too. But the latter isn't much use for anything else. Details in the manual.

To include market_id in the index (like I'd suggest), you also need the additional module btree_gin or btree_gist. Then create the index:

CREATE INDEX sum_mult_idx ON market_trades_sum_cache USING GIN (market_id, ts_range, sum);

The last column sum is optional and only makes sense if you get index-only scans out of it. Else don't include it in the index.

Or, if you want to disallow overlapping time ranges, use an exclusion constraint, based on a GiST index (GIN is not currently possibly). Details:

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
Optimizing queries on a range of timestamps (two columns)
2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

The last one also provides a clean solution for enforcing '[)' boundaries.


Why does SQL Server not use the Index it recommends? The query

I had a specific query I wanted to optimize:

SELECT * /*12 columns*/
FROM [dbo].[EnterpriseGroup]
WHERE
    (EnterpriseGroup.ChildId = 123 OR EnterpriseGroup.FatherId = 234)
AND StatusCd >= 2

There is also already an index on FatherId, but not on the ChildId. The primary key is among the 12 selected columns, but none of those used in the WHERE clause here.

The use

This is a simple query but it's run very, very often during daily work. The table is also small, around 8000 rows.

The query is used to find groups of enterprises. There are about 2 Millions of enterprise entries, so only less than 0.5% have a matching group row and thus most of the time, no group will be found.

The recommendation

When using SSMS and inspecting the "Actual Execution Plan" it gives this plan:

The predicate shown is actually the WHERE clause.

And, it recommends to create an index, which is basically indexing on the WHERE clause and adding all the queried columns directly into the index. Seems not very clever to me but maybe this is what this questions is all about:

/*
Missing Index Details from SQLQuery6.sql .....
The Query Processor estimates that implementing the following index could improve the query cost by 68.9052%.
*/

/*
USE [...]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[EnterpriseGroup] ([StatusCd])
INCLUDE (....all the 12 queried columns......)
GO
*/

The result

After I create the recommended index I get the following plan:

It's not used at all! (And the Table Scan hover info is exactly the same, still having a copy of the WHERE Clause as predicate)

The Question

Why does SQL Server not use an existing index that the SSMS adviced to create?

If not, then what is the significance of a missing index recommendation in the execution plan of SSMS (SQL Server Management Studio) against a Microsoft SQL server?

The notes

Note: I am no DBA, but a software developer. I have read a bit into this, including: https://www.brentozar.com/archive/2013/07/dude-who-stole-my-missing-index-recommendation/ but I did not clarify to me.

Note: In case it matters:
 - SQL Server Version 11.0.7493.4, running on Windows NT 6.3.
 - Microsoft SQL Server Management Studio is Version 11.0.7493.4

Assuming you WHERE condition still utilizes ChildId, FatherId and StatusCd.  It is possible that from a statistics perspective, that either ChildId or FatherId is more selective.  Taking ChildId and FatherId out of the WHERE clause should result in that new index being used, since StatusCd is the indexed column in the index.

If you hover over the Table Scan section you should see something like the below image:

It is possible that even though it recommended creating that index, that it is still querying by either ChildId or FatherId.

It would do this if ChildId or FatherId is more selective.  Lets say StatusCd >= 2 returns 6,000 of the 8,000 rows.  But either ChildId = 123 or FatherId = 234 only has 1 row.  Then doing a table scan on that one column, and applying the rest of the conditions after the fact is a more efficient query plan (theoretically speaking) than returning all 6,000 rows from StatusCd >= 2 and trying to apply the ChildId or FatherId conditions after.

This was something I learned from a question I asked a little bit ago.  The guy who answered it had a really great way of explaining what I tried to say here.  Does the Query Optimizer Prefer to Query on Constants before Columns?

Hopefully that helps.


Exporting results to XML I am trying to create an XML file from a SQL query.
The out put should look like the image below

Where the tag BranchID appears again as SubParentBranchID if there are more than one SubBranchID associated with BranchID. In the image below BranchID 94 has two SubBranchID 63 and 64.

I've manged this SQL query

SELECT
a.[heading_id] as BranchID,
c.[name] as BranchName,
a.[business_id] as SubBranchID,
a.[heading_id] as SubParentBranchID,
b.[name] as SubBranchName
   FROM [BUSINESSHEADINGLINK] as a
   join [BUSINESS] as b on a.business_id = b.business_id
   join [HEADING] as c on   a.heading_id = c.heading_id
FOR XML PATH ('Branch'), ROOT('BranchInfo')

This gives me the following XML

Here you can see that neither do i have the tag <SubBranches> nor <SubBranch>. Also i do not have <SubParentBranchID>. The XML file should be like:

Can anyone help me with the SQL code?

Also i would like to save it on C:/temp after execution.

Part 1: Proper XML Struture

In order to get anything other than a flat XML layout, you need to use either FOR XML EXPLICIT mode, or nested FOR XML AUTO queries.  Please see the following MSDN sections for complete details, including examples:

Use EXPLICIT Mode with FOR XML
Use Nested FOR XML Queries

Also, you do not need to generate the <SubParentBranchID> element as it is entirely superfluous. One of the nice things about XML is being able to get the parent node from your current location :).

Part 2: Exporting to a File

This can be done rather easily with a simple SQLCLR function to save the contents of a variable (or query) into a text file.

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = false, IsPrecise = true)]
public static SqlString SaveXmlToFile([SqlFacet(MaxSize = 4000)] SqlString FilePath,
    SqlXml XmlData)
{
    try
    {
        File.WriteAllText(FilePath.Value, XmlData.Value, Encoding.Unicode);
    }
    catch (Exception __Exception)
    {
        return __Exception.Message;
    }

    return String.Empty;
}

And there is no need to do any NULL checking of the input parameters via .IsNull() since I am using the RETURNS NULL ON NULL INPUT option:

CREATE FUNCTION [dbo].[SaveXmlToFile](@FilePath NVARCHAR(4000), @XmlData XML)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER,
     RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SomeAssemblyName].[FileUtils].[SaveXmlToFile];

Then you can use like this:

DECLARE @Output XML;

SET @Output = (
   SELECT ...
   FOR XML ...;
);

DECLARE @ErrorMessage NVARCHAR(4000);
SET @ErrorMessage = dbo.SaveXmlToFile(N'path/to/file.xml', @Output);

A few easy steps to get the above SQLCLR function working (and pretty much any Assembly you create that needs EXTERNAL_ACCESS or UNSAFE):

The assembly needs to be signed. In Visual Studio, go to Project Properties -> SQLCLR tab -> Signing... button.
"CLR Integration" needs to be enabled:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Create an Asymmetric Key in [master] from the DLL:

USE [master];
CREATE ASYMMETRIC KEY [KeyName]
FROM EXECUTABLE FILE = 'Path\to\SomeAssemblyName.dll';

Create a Login [master] from the DLL:

CREATE LOGIN [SomeLoginName]
FROM ASYMMETRIC KEY [KeyName];

Grant the Key-based Login the appropriate permission:

GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLoginName];

Please notice how none of those steps was to turn the database property of TRUSTWORTHY to ON!!!

An alternate means of getting this SQLCLR function without doing any coding, compiling, creating of Asymmetric Keys or Logins, etc, is to get a pre-done library that you just install. The SQL# library contains several File System functions and bypasses all of the steps shown above. Please note that I am the author of SQL#, and while there is a Free version, the File System functions are only available in the Full version.


How can I change the location for backup - Oracle 11g I have my database in disk C: , and also the default folder for backup is in disk C. I need to change location for database backup from C: to D:.
How to do that?!

That should be pretty simple. You should do this from rman. Also run a "show all;" to see how your backups are configured.

CONNECT TARGET /
SHOW ALL;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\rman_backup\%d_%Y_%M_%D_%t_%s_%c_%p';


pg_xlog_archive folder size is very big While doing DB operations on PostgreSQL pg_xlog_archive is consuming more than 150GB disk space with the option "archive_mode = on". How to cleanup/reduce pg_xlog_archive folder size.

What will happen if I disable "archive_mode= off". I have tried "off" option also but DB operations are taking too much time. How archive_mode do work?

A WAL archive can be used for disaster recovery, PITR (point in time recovery), or as part of a replication set up.  If you don't know why you set it up in the first place, then maybe you don't need it.  The consequences of turning it off is that you can't use that type of disaster recovery, can't do PITR for after the time it was turned off, and can't use that kind of replication anymore.

Also, note that WAL often compresses extremely well, often 25 fold or more, with something like "xz" or "lzip". So if you mostly concerned with storage, you can implement compression.


Is it possible to limit connecting users to a schema from specific terminal or program? I read about Oracle ACL, but on first sight, it doesn't look like it limits the access to a schema on terminal, program level. Is it even possible? I did experience such limitation once. I was connecting by sqlplus from unix box, the same box the application scripts were connecting from. Is it, perhaps, related to Oracle Wallet?

Goal:

I want to limit direct access to an application schema. I already enabled proxy access. However web application on localhost does access application schema, from the same host that I want to limit direct access through any IDE. Is it possible to limit connectivity to schema from the same machine, depending on what terminal, or program wants to connect?

I am not aware of something like that native in the database. However, it should be doable using a logon trigger.

Regards,
Daniel


Is it possible to compress CLOB columns in DB2 10.5 I have a table with a CLOB(1M). If I enable compression (compression yes adaptive) on the the table the CLOB is not compressed. Is there a way to make this work in DB2 10.5?

Historically LOB data are stored in data structures separate from the normal row data, these data structures are not compressed. With Db2 for LUW 9.7 and later you have an option to store part of LOB columns, subject to the row length limitation, inline with data rows. Inline LOB data will be compressed along with the rows they belong to.

CREATE TABLE foo (
  lob_col CLOB (1M) INLINE LENGTH 2000
)

This is only helpful if the majority of your LOB values fall within the inline length limit. Note also that storing LOBs inline can have impact on query performance (fewer rows fit on a page, thus potentially increasing I/O demand) and logging overhead (inlined LOBs are always logged).


Database Recovery Model - Simple vs. Full Does the SIMPLE Recovery Model perform the same transaction logging as FULL Recovery Model? i.e. keep record of each insert/update/delete transaction?

If this is the case, then why should FULL Recovery model be used? In SIMPLE Recovery, Checkpoints run every minute on a busy database, so the data is written to disk.

Where does the data loss potentially occur on SIMPLE Recovery Model?

Thanks!

Does the SIMPLE Recovery Model perform the same transaction logging as FULL Recovery Model?

Technically, no.  The simple recovery model minimally logs certain operations under certain conditions, just like the bulk-logged recovery model.

  keep record of each insert/update/delete transaction?

Yes, your data modifications will be logged (with a few DML exceptions, like SELECT INTO and INSERT ... SELECT under certain conditions).

  then why should FULL Recovery model be used?

For point-in-time recovery.  With the simple recovery model, you can only restore full and differential backups.  In other words, if you take a full backup at 2pm today, and you have a disaster at 5:05pm that demands a restore, then the most recent backup that you can restore will be your 2pm backup.  3+ hours of data loss.

But if you were in the full recovery model, and you had a full backup at 2pm today, followed by hourly transaction log backups and if the same disaster happened at 5:05pm, you could restore your 2pm full backup, and then restore your transaction log backups in between.  Depending on if your database's transaction log was affected or not by the disaster (in other more technical words, if you can backup the tail of the log) then you could potentially have zero data loss, by recovering to the point of the disaster.  Even if your transaction log is no longer available, and you can't take a tail log backup then you could still restore to your most recent transaction log (your SLAs will dictate your recovery point objective, and that will influence how frequently you take your transaction log backups).

  Where does the data loss potentially occur on SIMPLE Recovery Model?

Provided that the database isn't salvageable at all, then it'll be from the last full/differential backup to the time of disaster.

Restore a SQL Server Database to a Point in Time


LIMIT to one row per ID This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid?

'select DISTINCT v.id,
           v.purchasedate,
           v.customerid,
           v.assetid,
           va.description,
           vb.title
  from purchases v,
           asset va,
           assetdescription vb
 where customerid = '$kid'
   and v.assetid = va.id
   and vb.assetid = va.id
 order by v.purchasedate'

If you don't care which you get, you can use something like this:

select max(v.id),
       max(v.purchasedate),
       max(v.customerid),
       v.assetid,
       max(va.description),
       max(vb.title)
  from purchases v,
           asset va,
           assetdescription vb
 where customerid = '$kid'
   and v.assetid = va.id
   and vb.assetid = va.id
 group by v.assetid
 order by v.purchasedate;

If you want e.g. the "latest", then you can use something like this:

select t.id,
       t.purchasedate,
       t.customerid,
       t.assetid,
       t.description,
       t.title
from (
    select v.id,
           v.purchasedate,
           v.customerid,
           v.assetid,
           va.description,
           vb.title,
           row_number() over (partition by v.assetid order by v.purchasedate desc) as rn
      from purchases v,
               asset va,
               assetdescription vb
     where customerid = '$kid'
       and v.assetid = va.id
       and vb.assetid = va.id
) t
where rn = 1
order by v.purchasedate

The decision which one to take is controlled by the ordering inside the window definition (partion by v.assetid order by ....)


Is there a Successor or an Alternative to mk-parallel-dump? Just read about mk-parallel-dump in a possibly-outdated version of High Performance MySQL. Sounded great, but when I went to download it I found out that it's deprecated with no equivalent command in the Percona toolkit.

Are there other tools that do something similar-- ie fast database backup?

A bit late to the party but as a former mk-parallel-dump user I came across the same question recently.  I tried using the parallelism feature of MySQL mysqlimport however that only works for different tables even if you break up the dump files into smaller chunks.  Shame.

However I then discovered MySQL Data Dumper which is apparently being developed/maintained by Percona people.

MySQL Data Dumper project page

There are source tar balls for the various versions downloadable here:

Source downloads

The latest release supports dumping of AWS RDS instances too.  A typical invocation might be like:

./mydumper --user=root -o dump --rows=100000 -B database_name
./myloader --user=root -d dump -B database_name

You can also chunk by statement size, or number of output bytes.  By default dumping and loading use four threads, but you can set this.  There are a number of options available to control locking strategies and limiting which tables are dumped (the examples above do all the tables). For dumping across a slower network connection it supports data compression.

So far my experiments have been good although as ever I think you have to be careful to optimize MySQL for heavy parallel write access to get the best import time paying special attention to things like InnoDb log file and buffer sizes and concurrency settings.


Grant user from my database to execute xp_cmdshell I'm working with SQL Server 2016 and I need to let an user from MyDatabase to execute xp_cmdshell. I do the following:

USE [MyDatabase]
GO

GRANT EXECUTE ON xp_cmdshell TO myUser
GO

myUser is an user from MyDatabase and when I run the above code, logged as sa, I get the following error:

  Permissions on server scoped catalog views or system stored procedures
  or extended stored procedures can be granted only when the current
  database is master.

Do I need to add the user to master database? I don't think it is safe.

I need this because I want to write a text file from a stored procedure. I have found the following article, http://www.nigelrivett.net/WriteTextFile.html, saying that I can write a text file using:

You will need to create the data to be output as in dynamic sql statements
The first command here creates or overwrites the file - the rest append to the file.
exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'

I ask this because my stored procedure will generate a large string (nvarchar(max)) json and when I try to write it down with C# I don't get anything: neither the file or an exception. I've thought to write the json string inside the stored procedure (if I can).

The stored procedure ends correctly, I have checked in the debugger that I get something into the output parameter json (I know it because Visual Studio sets that there is not enough memory to show its contents), the File.WriteAllText is run because I have a breakpoint on it, and also, the path is absolute. But maybe, there is something that I ignore and I have to double check everything again.

I suggest you wrap the code that executes xp_cmdshell in a stored proc in your user database and sign it with a certificate that has the needed permissions. That way, users are limited to the user stored procedure and can't execute ad-hoc xp_cmdshell commands. OS permissions are also limited to the proxy account for non-sysadmin role members. This certificate technique is detailed in Erland Sommarskog's Packaging Permissions in Stored Procedures article.

Below is an example script.

-- Enable xp_cmdshell and create proxy account
USE master;
EXEC sp_configure 'show',1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE;
EXEC dbo.sp_xp_cmdshell_proxy_account 'YourDomain\YourProxyAccount', 'YourPr0xy@accountPassw0rd';
GO

-- Create certificate in master.
CREATE CERTIFICATE xp_cmdshell_cert
   ENCRYPTION BY PASSWORD = 'All you need is love'
   WITH SUBJECT = 'For xp_cmdshell privileges',
   START_DATE = '20020101', EXPIRY_DATE = '20300101';
GO

-- Create a login for the certificate.
CREATE LOGIN xp_cmdshell_cert_login FROM CERTIFICATE xp_cmdshell_cert;
CREATE USER xp_cmdshell_cert_login;
GRANT EXECUTE ON dbo.xp_cmdshell TO xp_cmdshell_cert_login;
GO

-- Copy cert to user database
DECLARE @cert_id int = cert_id('xp_cmdshell_cert')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'All you need is love',
              'All you need is love')

SELECT @cert_id, @public_key, @private_key

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE xp_cmdshell_cert
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''All you need is love'',
          ENCRYPTION BY PASSWORD = ''All you need is love'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO

USE YourUserDatabase;
GO

CREATE PROC dbo.CreateFile
AS
EXEC master..xp_cmdshell 'echo hello > c:\file.txt';
EXEC master..xp_cmdshell 'echo appended data >> c:\file.txt';
EXEC master..xp_cmdshell 'echo more data >> c:\file.txt';
GO
ADD SIGNATURE TO dbo.CreateFile BY CERTIFICATE xp_cmdshell_cert
   WITH PASSWORD = 'All you need is love';
GRANT EXEC ON dbo.CreateFile TO YourUserOrRole;
GO
ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO


Joining to same table and updating column on row match for multiple columns I have a single table (table_1) of schema below:

row_id      identifier   col1    col2    col3    col4    status
1           A            1       2       3       4
2           A            2       3       4       5
3           B            1       2       3       4
4           B            2       3       4       6
5           C            1       2       3       4

I want to join on identifier = A such that any rows with identifier != A but which match on values for col1, col2, col3 and col4 will update the status column with values for any matched ('B,C'), and another for mismatch (foo).

Note that the status column values need to update with the identifier(s) matched.

row_id      identifier   col1    col2    col3    col4    status
1           A            1       2       3       4       B,C
2           A            2       3       4       5       foo
3           B            1       2       3       4
4           B            2       3       4       6
5           C            1       2       3       4

I've used a single SELECT statement with multiple subqueries for each separate column to match to on 'A', but had to explicitly select other identifiers and resulted in errors.

Corrected row_id to show the primary key.

You can do it using a self-join and an aggregate function (string_agg) to aggregate the identifiers that are different from the current row as below.

The first and last lines initializes the status column to NULL and finalizes it to foo if it isn't assigned, respectively:

UPDATE table_1 t1 SET status = NULL;

UPDATE table_1 t1
SET status = (
  SELECT string_agg(t2.identifier,',') FROM table_1 t2
  WHERE t2.identifier != t1.identifier
    AND t1.col1 = t2.col1
    AND t1.col2 = t2.col2
    AND t1.col3 = t2.col3
    AND t1.col4 = t2.col4
);

UPDATE table_1 t1 SET status = 'foo' WHERE status  IS NULL;


MongoDB Stuck with Jumbo chunks while exhausting a shard I'm running a shared MongoDB 2.4 cluster.
I'm currently in a process of exhausting a shard and I'm down to the last 15 chunks needs to be migrated out of 9660.

My issue is that these chunks are too big as the shard key wasn't the best one, and dumping/restoring the 2 collections that has these issues is not an option at this moment.

I've tried to manually split the chunks, but it keeps on failing with this error: { "cause" : { }, "ok" : 0, "errmsg" : "split failed" }

I also tried to raise the chunk size, but that didn't work out as well.
Is there another solution I might be missing?

EDIT - Added chunk information as requested - Based on AdamC function.

mongos> AllChunkInfo("db.coll1", true)
ChunkID,Shard,ChunkSize,ObjectsInChunk
db.coll1-company_name_-6727988435900812827,shard0003,148256192,248752
db.coll1-company_name_-6615369920888926287,shard0003,88895188,149153
db.coll1-company_name_-6595735839523245520,shard0003,87448696,146726
db.coll1-company_name_-6490024596613592713,shard0003,32099368,53858
db.coll1-company_name_-3125507653387230887,shard0003,89171732,149617
db.coll1-company_name_-3084465672933415339,shard0003,105551600,177100
db.coll1-company_name_-2997585070019050064,shard0003,120763904,202624
db.coll1-company_name_-2993901072357262199,shard0003,86105908,144473
db.coll1-company_name_-2543788723715016046,shard0003,123507888,207228
db.coll1-company_name_4754688535895874026,shard0003,91061648,152788
db.coll1-company_name_5062850413490905708,shard0003,118145676,198231
db.coll1-company_name_5483223634690519314,shard0003,111475244,187039
Summary Chunk Information
Total Chunks: 12
Average Chunk Size (bytes): 100206920.33333333
Empty Chunks: 0
Average Chunk Size (non-empty): 100206920.33333333

mongos> AllChunkInfo("db.coll2", true)
ChunkID,Shard,ChunkSize,ObjectsInChunk
db.coll2-company_name_-5389355967913336416,shard0003,95282040,94808
Summary Chunk Information
Total Chunks: 1
Average Chunk Size (bytes): 95282040
Empty Chunks: 0
Average Chunk Size (non-empty): 95282040

mongos> AllChunkInfo("db.coll3", true)
ChunkID,Shard,ChunkSize,ObjectsInChunk
db.coll3-company_name_-3231146661862124155,shard0003,101878644,173263
db.coll3-company_name_658930972165413978,shard0003,142545900,242425
Summary Chunk Information
Total Chunks: 2
Average Chunk Size (bytes): 122212272
Empty Chunks: 0
Average Chunk Size (non-empty): 122212272

Thanks,
Meny

I was able to complete the exhaust by increasing the chunk size and removing the jumbo tag from the problematic chunks.
After getting the results from AdamC function, I found out the biggest chunk of that shard was ~148 MB of size.
After figuring this one out, the first step is connect to the config database and change the max chunk size:

use config
db.settings.save( { _id:"chunksize", value: 150 } )

The second step was to clear the jumbo flag from the relevant chunks.
This can be found in the MongoDB Documentation: Clear jumbo flag

Once the chunks were no longer marked as 'jumbo' the balancer had no problem migrating them to another shard.
When all of this was done, I restored the chunkSize back to 64

db.settings.save( { _id:"chunksize", value: 64 } )

As I do not want that all of my chunks will be as big. when possible, I'll correct the hashed shard key on the problematic collections to create a more divisible shard keys.

Thank you AdamC, your initial guidance set me on the correct path to solve this problem.


Select previous value in row without using a subquery Say you have a table in PostgreSQL 11 with sets of values something like this below.

12 18
11 12
5  12
9  18
7  19
14 18
11 16
6  12
4  11
5  9

Is there a way to do a select without using sub-selects or select by using functions or trigger/functions to find where each value is lower than previous or could be higher than previous but this example is lower than previous (could be 2 row or 15+ rows) in both column and first column went higher than last row or both first and second column to terminate the select making the condition positive?

I do not know of a way to have present variables inside of a select.  If I could keep a look back variable then I should be able to do this.  I am wondering if anyone out there has ever come across this and made it work inside a select or they went outside of the database into GoLang / Python / C++ / Perl / PHP / etc.  Very simple to do once I leave the database but I would really like to keep this inside PostgreSQL 11.

As Akina has pointed out in comments LAG() function requires an ORDER BY clause to show the properly values.

Given the next example:

CREATE TABLE t (id int, bar int);

INSERT INTO t VALUES
(1, 7),
(2, 8),
(3, 30),
(4, 25),
(5, 24),
(6, 24),
(7, 35),
(8, 40);

You can get the previous bar value ordering by id, (if you don't set an order, what is the previous row?)

SELECT
    id,
    bar,
    LAG(bar) OVER (ORDER BY id)
FROM
    t;

id | bar |  lag
-: | --: | ---:
 1 |   7 | null  --<< Note the NULL value
 2 |   8 |    7
 3 |  30 |    8
 4 |  25 |   30
 5 |  24 |   25
 6 |  24 |   24
 7 |  35 |   24
 8 |  40 |   35

Then simply use a conditional statement IF or CASE

SELECT
    id,
    bar,
    CASE
        WHEN LAG(bar) OVER (ORDER BY id) < bar THEN 'Lower'
        WHEN LAG(bar) OVER (ORDER BY id) > bar THEN 'Higher'
        WHEN LAG(bar) OVER (ORDER BY id) = bar THEN 'Equal'
        ELSE 'Nothing to compare'
    END As Previous
FROM
    t;

id | bar | previous
-: | --: | :-----------------
 1 |   7 | Nothing to compare
 2 |   8 | Lower
 3 |  30 | Lower
 4 |  25 | Higher
 5 |  24 | Higher
 6 |  24 | Equal
 7 |  35 | Lower
 8 |  40 | Lower

db<>fiddle here


How to create a SQL Server 2008 backup without logs with mirroring How do I create a backup of a SQL Server 2008 in a mirrored setup without any logs?

We need to do this so that when we restore our local development databases, we would have the most up to date data, but we don't necessarily have the space for all the transaction logs. (we're all using ssd's).

Currently, because it is mirrored, I can't create simple backups, it must be full backups.

Backing up a database (BACKUP DATABASE ...) backs up the pages from all of the database files (data and log) and upon restore those files will be recreated as a result.

A full database backup does just that:  It backs up the database in its entirety.  There is no way to get around not backing up the transaction log file.  If you are in full recovery mode, then you should ensure you are doing the proper transaction log backups so the log doesn't grow out of control.  Outside of that, you are constrained with a full backup.  A database consists of data file(s) and log file(s).

A workaround to this could be just to generate scripts from you database.  When you right-click your database to generate scripts, you can script out the entire database.  There is an option to script out both Schema and Data.  Then on your other machine/instance you can run this script.  It's not a traditional "backup" by any means, but it should get you what you want and you'll just have a script file as a result.  Mind you, when the CREATE DATABASE portion of the script runs, a log file will be created.  You can't have an RDBMS database without a log file (or at least not on SQL Server that I know of).


Stored procedure body missing I was going through one of our databases in order to answer a question from the boss, and I peeked at a stored procedure.  As usual, I right-clicked it in SSMS, clicked Script stored procedure as... then create, and to my surprise, all I found was the CREATE PROCEDURE statement:

To double-check, I had someone else look and they got the same thing.  They were signed in as database owner, I was signed in as sysadmin.  I next ran
select
m.definition
from sys.procedures p
left join sys.sql_modules m on p.object_id=m.object_id
where p.name in ('spPUpdateContactUserLockout', 'spPLINK_SYNC_CONTACT');

and got this (note I included another stored proc for comparison purposes)

I've not seen this before.  Ever.  I didn't think one could create a stored procedure without defining a body, and there are several such procedures in multiple databases on this instance.  At this point I'm concerned, but I'm not sure if I don't have to be or if I should be more concerned than I currently am.
This instance is SQL Server 2017 Standard x64 RTM-CU11, and I'm using SSMS 17.9.  Any insight is appreciated!

Yes, this is possible, no body required. How it got this way? Your guess is as good as ours. It is easy to do but we can't possibly know whether this was human error or some kind of truncation from a script or tool.

CREATE PROCEDURE dbo.blat
AS
GO
EXEC dbo.blat;

Output:

  Command(s) completed successfully.

Good to have source control to sort this kind of thing out.

If you're concerned, though, you should check your application and the rest of the procedures to see if anything calls this empty procedure and actually expects it to do anything. Error handling, unit tests, etc. may be passing simply because it isn't throwing an error.


Restoring a replicated SQL Server database in a non-replicated testing enviroment We have a SQL Server 2008R2 database in production. Originally, the database didn't have replication. Every time there was some problem with our software, we'd just create a backup from the production server, restore it on our testing environment, and reproduce the problem to solve it. Simple as that.

A new gov-related requirement pushed us to set up a Transactional replication on the Production database. We modified our software accordingly, and it's now working well. Our software is able to function both with a replicated or non-replicated database.

The problem now, is that we can't simply create a backup and restore in our environment as simple as before, every time there's a problem we need to reproduce. When we restore a backup, we can't modify any of the schema and/or functions of it, because SQL Server says the database is tied to a non-existent replication (because it obviously copies the replication config from the Production environment).

Is there any way to create a backup of a replicated database and restore it cleanly in a non-replicated environment? Just the schema, data, and functions. 'Generate scripts' isn't possible because there's a LOT of data.

I see many questions related to how to restore the database keeping the replication active, but I just need the clean database for testing purposes.

You have to just restore the database using

restore database <db_name>
from disk = 'Disk path\db_name.bak'
with recovery, replace -- only if there is already database that you need to overwrite
,stats =10,
move 'logical_data' to 'physical_path.mdf',
move 'logical_log' to 'physical_path.ldf'

once the database is restored, you can remove the replication bit

sp_removedbreplication 'database_name'


How to convert varchar iPv4 Address into a number deterministically What are the various ways to convert a varchar iPv4 Address into a number without using PARSENAME() (non-deterministic) function in SQL Server, in order to create a persisted calculated column?

I used the string manipulation technique from Extract part of string based on nth instance of character to create the following:

Query

select
((256*256*256)*convert(bigint, ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),1, 9)))))
+
((256*256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),10, 9)))))
+
((256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),19, 9)))))
+
(convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),28, 9)))))

Here is a visual of what the string manipulation looks like before each segment is converted to BIGINT and multiplied:

... This works to create a deterministic calculated column that can be persisted. What other more efficient techniques are there out there?

One option is to use SQLCLR. It is rather easy to create a method to split the string on the periods, convert each to an Int64, and multiply by the appropriate power of 256. Be sure to set IsDeterministic=true in the SqlFunction attribute, which will not only allow it to be persisted, but will also allow it to participate in parallel plans :-). And in fact, the SQL# SQLCLR library (that I created) contains functions (which are in the Free version) to do this conversion: INET_AddressToNumber and INET_NumberToAddress.

Test Setup:

CREATE TABLE #TempAddresses (IPAddress VARCHAR(15) NULL);

-- populate 1 million rows, with a NULL every 1000 rows
;WITH cte AS
(
  SELECT  TOP (1000000) ac1.column_id,
          ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [RowNum]
  FROM    master.sys.all_columns ac1
  CROSS APPLY  master.sys.all_columns ac2
)
INSERT INTO #TempAddresses ([IPAddress])
SELECT CASE cte.[RowNum] % 1000
         WHEN 0 THEN NULL
         ELSE CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
       END
FROM   cte

Regarding the following three tests, please note:

They are shown in order of fastest to slowest.
I ran each SELECT several times and kept the best time for each, not average time.

SQLCLR Test and Results:

DECLARE @Test1 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test1 =  -- Comment out "@Test1 = " to test returning the value
       SQL#.INET_AddressToNumber(CONVERT(NVARCHAR(15), tmp.[IPAddress]))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2454 ms,  elapsed time = 5133 ms.
-- CPU time = 1594 ms,  elapsed time = 1617 ms. (into variable)

MartinSmith's Test and Results:

DECLARE @Test3 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test3 =  -- Comment out "@Test3 = " to test returning the value
         256 * 256 * 256 * CAST(FLOOR(LEFT(tmp.[IPAddress],3)) AS BIGINT)
       +       256 * 256 * CAST(FLOOR(SUBSTRING(tmp.[IPAddress],1 + CHARINDEX('.', tmp.[IPAddress]),3)) AS BIGINT)
       +             256 * SUBSTRING(REPLACE(tmp.[IPAddress],'.',SPACE(8)),19, 9)
       +                   RIGHT(tmp.[IPAddress], -1 + CHARINDEX('.', REVERSE(tmp.[IPAddress])))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2781 ms,  elapsed time = 5151 ms.
-- CPU time = 2156 ms,  elapsed time = 2156 ms. (into variable)

O.P.'s Test and Results:

DECLARE @Test2 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test2 =  -- Comment out "@Test2 = " to test returning the value
       ((256*256*256) * CONVERT(BIGINT, LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),1, 9)))))
       + ((256*256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),10, 9)))))
       + ((256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),19, 9)))))
       + (CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),28, 9)))))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 3531 ms,  elapsed time = 5249 ms.
-- CPU time = 2515 ms,  elapsed time = 2534 ms. (into variable)

One thing to keep in mind regarding the SQLCLR option: using a SQLCLR function in a computed column is a dependency that will prevent the Assembly containing the function from being dropped. If you are able to use ALTER ASSEMBLY, then you won't being dropping and re-creating the Assembly. But ALTER ASSEMBLY is only allowed if there are no new or removed methods, and if the signatures of existing methods are still the same. If any of those changes are being made, then you will have to drop the Assembly and then create it again. And that will require that you first drop the computed column that is referencing the SQLCLR function contained in that Assembly. This might make the SQLCLR option less desirable in the use case of placing a function in a computed column, whereas if the function was in a query, Stored Procedure, View, etc, then this wouldn't be a concern.

It is possible to wrap the SQLCLR function in a T-SQL function and reference the T-SQL function in the computed column, but then you lose the benefit of SQLCLR functions being able to participate a parallel execution plan.


Can we export Selected Row with Subquery using data pump I am trying to export selected rowS from my database using SUBQUERY but I got error.

expdp directory=exp_data dumpfile=selected_row.dmp logfile=rows.log tables=sample,super query="where name IN (select name from azeem.sample where name like 'Ten');"

Export: Release 11.2.0.4.0 - Production on Tue Jan 12 21:58:37 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

Can we use subquery in datapump?

Can we use subquery in datapump?

Yes we can. The following example shows how to do it.

I have created a parameter file to get this job done. In my test case I have two tables namely tbl1 and tbl2 under HR schema. You may change the name of the schema as your requirements.

[oracle@testsrv Desktop]$ cat query.par
Directory=user_dir
DUMPFILE=select_row.dmp
logfile=rows.log
tables=hr.tbl1, hr.tbl2
Query=hr.tbl1:"where name IN (select name from hr.tbl2 where name like '%T%')"

Then I have used the following expdp command to accomplish the task.

[oracle@testsrv Desktop]$ expdp system/oracle parfile=query.par

Export: Release 11.2.0.4.0 - Production on Mon Mar 21 15:59:18 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/password parfile=query.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HR"."TBL1"                                     5 KB       0 rows
. . exported "HR"."TBL2"                                 5.007 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/Desktop/select_row.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 21 16:00:05 2016 elapsed 0 00:00:41


How to check SQL database files for physical fragmentation? I search the Internet and did not find clear explanation if I have to try defragmenting physical database files when my DB resides on SAN. I understand that there are tools and procedures inside some SANs that deal with that, but also there are SANs which lack that features.

My question has 3 parts:

How to check for physical fragmentation of SQL files?
Is it necessary to do this if the files reside on SAN?
Is it necessary to create a bigger physical database files to avoid fragmentation?

To answer your questions in order:

You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment.  Or close to that, depending on which version of the OS you are using your specific steps may be different.  But you will only be able to see the fragmentation on a volume level, not specific files.
For the most part, no, you don't need to worry about physical fragmentation of your files if you are using a SAN (unless the SAN is dedicated to this one purpose).  Due to how most SANs are used, your IO is going to be random; which is fine since you should have LOTS of spindles to spread the load out.
Larger physical database files would prevent fragmentation, but again, you shouldn't worry about that if you are on a SAN.  The more important thing to make sure of is that when the file grows, it grows at a set size (rather than percentage) and that size is sufficient to handle a good amount of growth.  You would want to grow the file to what you estimate the required size will be in X days and THEN defragment the volume.

This link - Brent Ozar goes into more detail and should include some more information you can use to either prove or disprove that the SAN is the reason for your bottleneck.


Method for Finding Top Record with WHERE conditions in Child Tables Setup

Recently I worked on an optimization for a SQL Query which returned the Prescription Product most recently used by a Patient.  The original query handled this via 3 nested CTE's and had really poor performance after migrating to a new server.  Instead of trying to make the original version work with either server tuning or index changes we modified the query using ROW_NUMBER() partitions.  This took the query execution time from about 10 minutes to about 6 seconds for approximately 215,000 rows in the final output.

Question

I am looking to see if there is a better way than how I re-wrote it since in my mind this almost seems like a hack.  I just want to see if this is only working well because of the size of our data set or if this really is a good solution.  I would assume we would rather use some type of solution that utilizes a couple of different JOINs that find this kind of information instead of using ROW_NUMBER() and a CTE.

Additional Details

For the sake of this question I am going to leave out certain details in our database that are less than desirable (composite primary keys, using VarChar(1) instead of a BIT, etc...) so that the root of the question can be better addressed.

We have 4 tables in scope:

Patient - we are only going to return the Identity Column from this table
OrderHeader - holds information on the package, like the address it is going to.  And is the parent record to OrderDetail.  This table is tied to Patient via a PatientID column.
OrderDetail - creates a relationship between OrderHeader and Product to indicate which Products went in the actual box when it was shipped out
Product - holds the list of products

We are needing a query that returns for each patient, the top OrderDetail record, for the top OrderHeader record, where the Product record tied to the OrderDetail record Product.IsRx = 1.

Unfortunately we can't just get the MAX value for OrderDetail.ID where Product.IsRx = 1 since that OrderDetail.ID may not belong to the MAX value for OrderHeader.ID.  We also can't rely on MAX value for OrderHeader.ID since it isn't guaranteed to have a record in OrderDetail nor is any record in there guaranteed to have a Product.IsRx = 1

I solved this problem with the below query:

WITH CTE
(
    PatientID,
    OrderHeaderID,
    OrderDetaillID,
    ProductNDCCode,
    ProductNDCDescription,
    RowNumber
)
AS
(
    SELECT P.ID AS 'PatientID',
    H.ID AS 'OrderHeaderID',
    D.ID AS 'OrderDetaillID',
    P.NDCCode AS 'ProductNDCCode',
    P.NDCDescription AS 'ProductNDCDescription',
    ROW_NUMBER()
    OVER
    (
    PARTITION BY P.ID
    ORDER BY
        P.ID ASC, --This part in the ORDER BY may not be needed, I apologize if it is unnecessary
        SH.OrderHdrID DESC,
        SD.OrderDtlID DESC
    ) AS 'RowNumber'
    FROM Patient P
        INNER JOIN OrderHeader H
            ON P.ID = H.PatientID
        INNER JOIN OrderDetail D
            ON H.ID = D.OrderHeaderID
        INNER JOIN Product PR
            ON PR.ID = D.ProductID
    WHERE PR.IsRx = 1
)
SELECT PatientID,
OrderHeaderID,
OrderDetaillID,
ProductNDCCode,
ProductNDCDescription
FROM CTE
WHERE RowNumber = 1

An guidance or expertise I could gain would be very helpful.

I wouldn't consider this a hack.  Windowing functions, including ROW_NUMBER are fairly efficient and generally perform equal to or better than alternatives.  On new instances of SQL Server, I assume that FIRST_VALUE would be more performant, but have not specifically confirmed this.

Note: I've seen LAST_VALUE produce incorrect results, and would recommend staying clear of it, opting instead to use FIRST_VALUE with the opposite sort.

A word of caution, however.  If your ordering criteria are not specific enough to produce unique a single "best" record (e.g. suppose you omitted OrderDtlID, any of the detail records for that order could be first), then which item is chosen is functionally random (or, more accurately, based upon hidden variables that you can neither easily discover nor control in many cases).  If you change from ROW_NUMBER to RANK and your candinality changes (you start getting a different number of results) this is a good indicator of a problem.  Also worth pointing out this can be a problem with other means of picking the latest/best record, not just ROW_NUMBER.

Even if you are indifferent to which would be selected, the results of such a query may not be repeatable, though you may no see it right away (the hidden variables don't general change after each query).  It can cause problems troubleshooting issues later and, if you run any sort of parallel environment (dev/qa), make accurate comparison between the two impossible.


How to get number of begin transactions in PostgreSQL? In Microsoft MSSQL, SELECT @@TRANCOUNT method is available to get number of BEGIN TRANSACTION statements.
What is the equivalent command in PostgreSQL?

A_horse_with_no_name's comment had me curious, and it seems like PostgreSQL doesn't use transactions in the same way that you're used to in Microsoft SQL Server. Rather they use Save Points. This StackOverflow answer gives a little context about Save Points and how to track them (see the comments on the answer for additional details).
There typically can only be one transaction open at a time, but you can achieve nested transactions utilizing save points.


Can't create table, but table doesn't exist I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db:

mysql> USE my_db;
mysql> DROP TABLE my_user;
mysql> ERROR 1051 (42S02): Unknown table 'my_user'
mysql> CREATE TABLE my_user (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
mysql> ERROR 1005 (HY000): Can't create table 'my_db.my_user' (errno: -1)

Tried # mysqladmin flush-tables and repeated the steps above but it wasn't helpful. Also, restarted the mysql service, but no good.

Any ideas? Google has failed me so far. Thanks.

Extra info:

mysql> SHOW engine innodb STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140703 15:15:09 Error in foreign key constraint of table my_db/my_user
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "FK_CFBD431E285FAC6D" FOREIGN KEY ("group_id") REFERENCES "my_group" ("id")

InnoDB Architecture

ANALYSIS

Somehow, you lost the my_user.frm and my_user.ibd files. The data dictionary still has an entry for that table.
You cannot run DROP TABLE my_user; because mysqld looks for the my_user.frm first. Since this is no my_user.frm, the table cannot be dropped.
Although my_user.frm does not exist, you cannot run CREATE TABLE my_user ... because mysqld thinks it is OK to create the table but then defers to the storage engine. InnoDB says "I already have the tablespace_id of my_user registered".

This sequence of events can be proved if you create the table using MyISAM. mysqld will allow it. Once you switch to InnoDB, it goes right back to the data dictionary, which is faulty on that one entry.
I have two suggestions
SUGGESTION #1
Don't create the table with that name anymore. Use a different table name
CREATE TABLE my_usertable (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

This will result in you changing the table name in your application code
SUGGESTION #2

mysqldump all the data, triggers and stored procedures
backup ibdata1
delete ibdata1
reload the mysqldump
Detailed steps from my StackOverflow post

I have dealt with this problem before in my post InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)


SQL Server Database is corrupted Unfortunately hard disk of my system is damaged, now I recovered my database. But it cannot be attached again to SQL management studio 2005.
The error is:

myfile.mdf is not a primary database file. (SQL server , error 5171)

You should restore it from your backup (which you can do by right clicking on "Databases" and selecting restore database)