Federated table of two LEFT JOIN tables not working For some reasons I am getting the following error when executing the below query:
(What I am trying to achieve is to sync and merge two left-joined table to into one

  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'ENGINE=FEDERATED' at line 35

CREATE TABLE users AS (
  SELECT
    ID, user_email, user_registered,
    first_name.meta_value as first_name,
    last_name.meta_value as last_name,
    telephone.meta_value as telephone,
    country.meta_value as country,
    company.meta_value as company,
    address.meta_value as address,
    city.meta_value as city,
    professional_title.meta_value as professional_title,
    state.meta_value as state,
    areas_of_interest.meta_value as areas_of_interest
    FROM wp_users
    LEFT JOIN wp_usermeta AS first_name ON first_name.user_id=ID
        AND first_name.meta_key='first_name'
    LEFT JOIN wp_usermeta AS last_name ON last_name.user_id=ID
        AND last_name.meta_key='last_name'
    LEFT JOIN wp_usermeta AS telephone ON telephone.user_id=ID
        AND telephone.meta_key='telephone'
    LEFT JOIN wp_usermeta AS country ON country.user_id=ID
        AND country.meta_key='country'
    LEFT JOIN wp_usermeta AS company ON company.user_id=ID
        AND company.meta_key='company'
    LEFT JOIN wp_usermeta AS address ON address.user_id=ID
        AND address.meta_key='address'
    LEFT JOIN wp_usermeta AS city ON city.user_id=ID
        AND city.meta_key='city'
    LEFT JOIN wp_usermeta AS professional_title ON professional_title.user_id=ID
        AND professional_title.meta_key='professional_title'
    LEFT JOIN wp_usermeta AS state ON state.user_id=ID
        AND state.meta_key='state'
    LEFT JOIN wp_usermeta AS areas_of_interest ON areas_of_interest.user_id=ID
        AND areas_of_interest.meta_key='areas_of_interest'
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION 'mysql://*********';

Note: FEDERATED Engine is on and working on other non-joined table.

To define a FEDERATED table the field definition on local table much match field types on remote table. You are creating a brand new table by joining existing tables. I don't think this is legal.

  A local server with a database table, where the table definition
  matches that of the corresponding table on the remote server. The
  table definition is stored within the .frm file. However, there is no
  data file on the local server. Instead, the table definition includes
  a connection string that points to the remote table.

http://dev.mysql.com/doc/refman/5.5/en/federated-description.html


How to deal with async calls to a stored procedure that updates data I have asynchronous calls from Entity Framework to a stored procedure.

The stored procedure selects data to a temp table and later on, it updates the real table. This works fine for sync calls.

The problem starts when the stored procedure is called 2-3 times asynchronous. Sometimes it returns wrong data.

Here is a flow of what happens (it's not accurate, it's just to demonstrate the problem):

First thread enters the stored procedure.
Second thread enters the stored procedure.
First thread selects a table and inserts it to a temporary table.
Second thread selects a table and inserts it to a temporary table.
First thread updates the real table from the temporary table [the table that was selected in to the temporary table (section 3) is also updated].
Second thread updates the real table from the temporary table [the table that was selected in to the temporary table (section 4) is also updated].

The problem is that thread 2 doesn't use the new data that thread 1 updated. It uses the old data and therefore the update is not working.

My question:

What is the best solution for dealing with it? So that the code will be asynchronous as possible, and more important will work perfectly.

I thought maybe to lock the temp table, so the second thread will wait until the first thread finishes with that table. Or to use transactions. Also, I can call it synchronous but then it hurts the performance.

Thanks.

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a queue system to process the asynchronous calls so that you know for a fact the data is already updated before calling the second or to use some other mutex/singleton to deal with it in the database.

While I personally don't like doing it this way, you can use sp_lock: http://msdn.microsoft.com/en-us/library/ms187749.aspx

There are a few drawbacks, namely you're locking an entire object and forcefully making something synchronous.

Other ways involve query hints such as holdlock with xlock. These will force the other calls to wait but you may introduce deadlocks into your environment. At that point, forcing serialization may be what you want as an isolation level.

Since the calls rely on the previous calls being completed, it would make more sense to implement a background queue thread or class to deal with all of the calls in order as using sp_lock will cause concurrency issues later. You could also try to redo the design so that the previous call doesn't have to complete to get the correct information. Since we are scant on details I do not know if that's possible or not.


Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN So we had a long running proc causing problems this morning (30 sec + run time).  We decided to check to see if parameter sniffing was to blame.  So, we rewrote the proc and set the incoming parameters to variables so as to defeat parameter sniffing.  A tried/true approach.  Bam, query time improved (less than 1 sec).  When looking at the query plan the improvements were found in an index the original wasn't using.

Just to verify that we didn't get a false positive we did a dbcc freeproccache on the original proc and reran to see if the improved results would be the same.  But, to our surprise the original proc still ran slow.  We tried again with a WITH RECOMPILE, still slow (we tried a recompile on the call to the proc and inside the proc it'self).  We even restarted the server (dev box obviously).

So, my question is this... how can parameter sniffing be to blame when we get the same slow query on an empty plan cache... there shouldn't be any parameters to snif???

Are we instead being affected by table stats not related to the plan cache.  And if so, why would setting the incoming parameters to variables help??

In further testing we also found that inserting the OPTION (OPTIMIZE FOR UNKNOWN) on the internals of the proc DID get the expected improved plan.

So, some of you folks smarter than I, can you give some clues as to whats going on behind the scenes to produce this type of result?

On another note, the slow plan also get's aborted early with reason GoodEnoughPlanFound while the fast plan has no early abort reason in the actual plan.

In summary

Creating variables out of incoming parameters (1 sec)
with recompile (30+ sec)
dbcc freeproccache (30+ sec)
OPTION (OPTIMIZE FOR UKNOWN) (1 sec)

UPDATE:

See slow execution plan here:  https://www.dropbox.com/s/cmx2lrsea8q8mr6/plan_slow.xml

See fast execution plan here:  https://www.dropbox.com/s/b28x6a01w7dxsed/plan_fast.xml

Note: table, schema, object names changed for security reasons.

The query is

SELECT SUM(Amount) AS SummaryTotal
FROM   PDetail WITH(NOLOCK)
WHERE  ClientID = @merchid
       AND PostedDate BETWEEN @datebegin AND @dateend

The table contains 103,129,000 rows.

The fast plan looks up by ClientId with a residual predicate on the date but needs to do 96 lookups to retrieve the Amount. The <ParameterList> section in the plan is as follows.

        <ParameterList>
          <ColumnReference Column="@dateend"
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@datebegin"
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@merchid"
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

The slow plan looks up by date and has lookups to evaluate the residual predicate on ClientId and to retrieve the amount (Estimated 1 vs Actual 7,388,383). The <ParameterList> section is

        <ParameterList>
          <ColumnReference Column="@EndDate"
                           ParameterCompiledValue="'2013-02-01 23:59:00.000'"
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@BeginDate"
                           ParameterCompiledValue="'2013-01-01 00:00:00.000'"
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@ClientID"
                           ParameterCompiledValue="(78155)"
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

In this second case the ParameterCompiledValue is not empty. SQL Server successfully sniffed the values used in the query.

The book "SQL Server 2005 Practical Troubleshooting" has this to say about using local variables

  Using local variables to defeat parameter sniffing is a fairly common
  trick, but the OPTION (RECOMPILE) and OPTION (OPTIMIZE FOR) hints
  ... are generally more elegant and slightly less risky solutions

  Note

  In SQL Server 2005, statement level compilation allows for compilation
  of an individual statement in a stored procedure to be deferred until
  just before the first execution of the query. By then the local
  variable's value would be known. Theoretically SQL Server could take
  advantage of this to sniff local variable values in the same way that
  it sniffs parameters. However because it was common to use local
  variables to defeat parameter sniffing in SQL Server 7.0 and SQL
  Server 2000+, sniffing of local variables was not enabled in SQL
  Server 2005. It may be enabled in a future SQL Server release though
  which is a good reason to use one of the other options outlined in
  this chapter if you have a choice.

From a quick test this end the behaviour described above is still the same in 2008 and 2012 and variables are not sniffed for deferred compile but only when an explicit OPTION RECOMPILE hint is used.

DECLARE @N INT = 0

CREATE TABLE #T ( I INT );

/*Reference to #T means this statement is subject to deferred compile*/
SELECT *
FROM   master..spt_values
WHERE  number = @N
       AND EXISTS(SELECT COUNT(*) FROM #T)

SELECT *
FROM   master..spt_values
WHERE  number = @N
OPTION (RECOMPILE)

DROP TABLE #T

Despite deferred compile the variable is not sniffed and the estimated row count is inaccurate

So I assume that the slow plan relates to a parameterised version of the query.

The ParameterCompiledValue is equal to ParameterRuntimeValue for all of the parameters so this is not typical parameter sniffing (where the plan was compiled for one set of values then run for another set of values).

The problem is that the plan that is compiled for the correct parameter values is inappropriate.

You are likely hitting the issue with ascending dates described here and here. For a table with 100 million rows you need to insert (or otherwise modify) 20 million before SQL Server will automatically update the statistics for you. It seems that last time they were updated zero rows matched the date range in the query but now 7 million do.

You could schedule more frequent statistics updates, consider trace flags 2389 - 90 or use OPTIMIZE FOR UKNOWN so it just falls back on guesses rather than being able to use the currently misleading statistics on the datetime column.

This might not be necessary in the next version of SQL Server (after 2012). A related Connect item contains the intriguing response

  Posted by Microsoft on 8/28/2012 at 1:35 PM
  We've done a cardinality
  estimation enhancement for the next major release that essentially
  fixes this. Stay tuned for details once our previews come out.    Eric

This 2014 improvement is looked at by  Benjamin Nevarez towards the end of the article:

A First Look at the New SQL Server Cardinality Estimator.

It appears the new cardinality estimator will fall back and use average density in this case rather than giving the 1 row estimate.

Some additional details about the 2014 cardinality estimator and ascending key problem here:

New functionality in SQL Server 2014 – Part 2 – New Cardinality Estimation


decrypt/get sql user password I have a sql server 2005 database server.  I have administrative access(sa access) however I need the password to a specific account on the server.  I can't really reset the password to this account because it would break some applications.

Is there anyway to get the decrypted password from the sql server?

There are ways to do it which involve attaching a debugger to the SQL Server and basically crashing the SQL Server so that you can get into the memory that SQL Server is using which will have the plain text password in memory.

It'll be much easier to get the password from the .NET source code.


How to safely populate pg_notify payload? Data makes its way into my application with prepared statements and stored procedures, so I'm not too worried about injection. When a new user signs up, I want to send an activation email. Presently I'm doing this with a trigger and a notification. It looks something like this:

CREATE FUNCTION on_sign_up() RETURNS trigger as $$
  DECLARE
  BEGIN
    PERFORM pg_notify('sign_ups', NEW.user_id || ',' || NEW.email || ',' || NEW.activation_code);
    RETURN new;
  END;
$$ language plpgsql;

CREATE TRIGGER on_sign_up_trigger AFTER INSERT ON user_account
  FOR EACH ROW EXECUTE PROCEDURE on_sign_up();

My problem is the notification payload. If the email contains a comma, then I can't just split the string on ','. How do I escape anything and put it into the payload? I can add my own custom CSV escaping function and invoke it, but any time I think something like that is the right answer, I'm usually missing something.

It doesn't necessarily need to be CSV, I just thought that'd be the easiest.

Note: there are other solutions - e.g. strip the "prefix" and "suffix" and then treat the rest as the email. I'm more interested for this general class of sanitization/escaping from within PostgreSQL itself.

The table I'm trying to extract is as follows:

                  Table "user_account"
     Column      |  Type   |         Modifiers
-----------------+---------+----------------------------
 user_id         | integer | not null
 email           | citext  | not null
 password_hash   | text    | not null
 activated       | boolean | not null default false
 activation_code | uuid    | default uuid_generate_v4()

Rather than worrying about how to escape it. Just use JSON as a transport layer. Here we use row_to_json

CREATE FUNCTION on_sign_up() RETURNS trigger as $$
  DECLARE
  BEGIN
    PERFORM pg_notify('sign_ups', row_to_json(NEW)::text );
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;


Shrink Transaction Log While Using AlwaysOn Availability Group 2014 I'm running a SQL Server 2014 Availability Group with a single secondary in asynchronous mode.

My logs in all the Availability Group databases keep growing and the built-in shrink tool only takes them down a notch.

Can anyone recommend a set of troubleshooting steps to find out why the logs keep growing or how to automate log shrink in Availability Group databases?

When you use AlwaysOn Availability Group, even if you take a proper backup the log file might grow larger and the log drive might get full over time. In order to maintain proper (shrink) log file size you can use the following technique.

On the AlwaysOn configuration, change the backup priority options to primary replica/server.
Since the databases are by default in a full recovery mode, take at least one a full and one transaction log backup.
Shrink the log files of all databases on primary replica. This will truncate the empty the log drives on all availability replicas.
Finally, schedule a job to take appropriate backup on a regular basis. This will keep you log file on the right size.
I hope this will help!


Moving SQL Server database to a new disk while online I have a 1.4TB SQL Server Database that's struggling massively with disk I/O. We've installed a new SSD array into the server that will solve all of our problems, we're just debating the best way to move the database across. Ideally if we can do it without downtime, that's best. But where the choice is between two days of poor performance (e.g. while copying data) versus two hours of downtime, the latter might be preferable.

So far, the solutions we've come up with are:

Simple copy. Take the DB offline, copy the files across, change the locations in SQL Server and bring it back online. Rough figures estimate this will take up to five hours, which is not really acceptable, but it is the easiest solution.
Block-level copy. Using an rsync-like utility, we copy the files across in the background while the DB is up. When we're ready to migrate, we take the DB offline, do a differential copy across using this utility, then point SQL server at the new files and bring it online. Timing here is unknown. We don't know how long it will take to do a differential analysis of 1.4TB and copy that across. Our other concern is that the block-level copy will leave the files in some state unreadable by SQL Server and we'll have waste our time.
SQL migration. Create a new 1.4TB SQL data file on the new disk and disable autogrowth on all of the other files. Then run DBBC SHRINKFILE(-file_name-, EMPTYFILE) on all of the other data files in turn. Once all of the data is across, I'll take a scheduled window at some point to move the MDF file over to the SSD and remove the other unused files. I like this because it minimises downtime. But I have no idea how long this will take and whether it will cause degradation in performance while it's happening.

We don't have any kind of load & performance environment to test this. I can verify that the strategies will work on our staging environment, but not the impact and not the performance.

One method to move the entire database is with BACKUP and RESTORE.  The database will be unavailable during the final switch but downtime should be minimal with planning.  This procedure assumes the FULL or BULK_LOGGED recovery model:

1) Perform a FULL backup (or use your existing one).

2) Restore latest full backup to a different database name, specifying the WITH MOVE option to relocate files on SSD storage as desired and the NORECOVERY option so that subsequent differential and log backups can be restored.

3) Apply incremental changes to the new database until the time of the final cut-over with transaction log backups and RESTORE...WITH NORECOVERY.  This will minimize downtime for the final switch to the new database.

4) To switch to the new database, take the application offline, perform a final transaction log backup, and apply to the new database WITH RECOVERY.  Finally, rename the original database to a different name and rename the relocated database to the original name.  Drop the old database at your convenience.

In the SIMPLE recovery model, you could use a similar process but without the step 3 transaction log backup/restores.  Instead, use a differential database backup/restore in the final step.  That may require more downtime, depending on the amount of changes since the initial FULL backup.


Inserting into mysql table with archive engine "duplicate key" error I am trying to archive some files using mysql archive engine. I am using this query to insert file contents:

insert into test_table (id,arch) values (123,'FILE_CONTENT')

After inserting 2 records, I get "duplicate key" error from mysql for a key that doesn't exist in the table. I checked it like 5 times but the record is not there. I selected count(*) for the duplicate id and the result is 0.

I checked the same code with an innodb engine and it works fine. Can anyone tell me what the problem is with the archive engine?

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `arch` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=100175977 DEFAULT CHARSET=utf8mb4;

This sounds very unusual for a table using the ARCHIVE Storage Engine. Why? A duplicate key error is not characteristic for ARCHIVE Storage Engine since

Engine does not support the creation of indexes
Engine supports INSERTs and SELECTs

Surprisingly, there can be a key internally present. How?

According to the MySQL Documentation

  The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE and ALTER TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.

Given this information, look back at the table and the query

insert into test_table (id,arch) values (123,'FILE_CONTENT')

If the id column has the AUTO_INCREMENT attribute, you should not specify id with a value. It would produce a normal 1062 error (Duplicate Key) for other Storage Engines.

SUGGESTIONS

Change the insert to a format that can handle the AUTO_INCREMENT attribute of id

insert into test_table (id,arch) values (0,'FILE_CONTENT')

or

insert into test_table (arch) values ('FILE_CONTENT')

Give it a Try !!!

UPDATE 2013-08-06 16:57 EST

If you are planning to do queries from the archive table, you need to get away from the ARCHIVE Storage Engine. Why? Again, according to the MySQL Documentation

  Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used

Note that every SELECT against an ARCHIVE table is a full table scan. If you lookup id 123 with 1,000,000 rows, you gotta read 1,000,000 rows every time.

SUGGESTION

Convert the table to MyISAM. Then you can have a proper index on id plus the ability to create other indexes on other columns as needed.

CREATE TABLE test_table_myisam ENGINE=MyISAM as SELECT * FROM test_table WHERE 1=2;
ALTER TABLE test_table_myisam ADD PRIMARY KEY (id);
INSERT IGNORE INTO test_table_myisam SELECT * FROM test_table;
DROP TABLE test_table;
ALTER TABLE test_table_myisam RENAME test_table;


How can I restrict the version of the target DB instance in a SSIS package? How can I restrict an SSIS Package to only execute on SQL Server 2012?

Is there a built-in way to do this (metadata?), or do I need to do a query?

(Possibly belongs in a new question):

I can get the version using SERVERPROPERTY('ProductVersion'), but how do I verify that it is like '11.%'?

Create an initial Execute SQL Task in the Control Flow and use the following code in it:

IF ((SELECT CAST(SERVERPROPERTY('ProductVersion') as CHAR(2))) = 11)
    BEGIN
        SELECT 'TRUE' As Result
    END
    ELSE
        SELECT 'FALSE' As Result

You can of course do several things with this, one of the easiest and clearest for SSIS developers would be to store the results in a SSIS variable and assign the Precedent Constraint after the SQL Task to Expression and Constraint.

If the Execute SQL Task Succeeds and returns TRUE it will allow the rest of the execution, which could be a single package, part of a larger execution stream, or a master package. If it fails or returns FALSE your package stops... might be worth putting in another path for an alert message or log of this though.

You can also use (SELECT @@VERSION) LIKE 'Microsoft SQL Server 2014%'. I like this as it's a more familiar version nomenclature.


Error deleting more than 1 row I have a stored procedure that is supposed to delete several rows from a table. It does not work and gives me the error:

The transaction ended in the trigger. The batch has been aborted

I tried running the individual queries to see if they would work separately. I found that the error is that it doesn't let me delete more than 1 row at a time. Here is an example:

DELETE FROM DetalleBusqueda
WHERE IdResultado IN (106577, 106578, 106579)

That query does not work, but the following one does:

DELETE FROM DetalleBusqueda
WHERE IdResultado IN (106577)

The first query is supposed to delete 3 rows, and the second one only 1 row. The second one does what it's supposed but the first one gives me the aforementioned error.

Does anyone have any idea what is it that I have to do so the procedure can delete more than a row at a time?

SQL Server does not support FOR EACH ROW clause in trigger.
So, I think that query in a trigger falls down with an error like this

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Maybe replacement of "=" by "IN" will help.


Behaviour of RAISERROR within a TRY/CATCH within a loop I have just encountered a section of SQL that is behaving not as I would expect (See below for a distilled version of the SQL in question that demonstrates the problem pattern).

(This is on SQL Server 2008 R2 SP2 64bit)

The 'do some work here' part raises an error and triggers the error handling in the CATCH block. The error number is 515 (attempt to insert a null into a non-nullable column), hence RAISERROR is reporting the error, but then the loop continues, attempts the work again, throws an error, etc. in an infinite loop.

I would expect RAISERROR to cause execution to exit the loop. What is going on here?

Thanks

WHILE (@Applied <> 1)
BEGIN

    BEGIN TRY

        -- === Do some work here ===

        -- Successfully applied
        SET @Applied = 1;

    END TRY
    BEGIN CATCH

        -- Save the error details
        SELECT
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Test for a deadlock or uncommittable transaction
        IF (@ErrorNumber = 1205 OR @ErrorNumber = 3930)
            -- Sleep for 5 seconds
            WAITFOR DELAY '00:00:05';
        ELSE
            RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH
END

RAISERROR does not typically stop execution.  THROW does, but that was introduced in SQL 2012.

Put a RETURN; or RETURN -1; after the RAISERROR.

The following is taken from the "Remarks" section of the MSDN page for RAISERROR:

  When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

  Outside the scope of any TRY block.
  With a severity of 10 or lower in a TRY block.
  With a severity of 20 or higher that terminates the database connection.

Below is a simple test of the behavior. If running on a version of SQL Server prior to 2012, comment out or remove the line with THROW.

RAISERROR(N'This is from RAISERROR - Severity 16', 16, 1);
PRINT N'----- 1';

;THROW 50505, N'This is from THROW', 1;
PRINT N'----- 2';

GO

PRINT N'----- 3';

-- WITH LOG needed for severity > 18
RAISERROR(N'This is from RAISERROR - Severity 20', 20, 1) WITH LOG;
PRINT N'----- 4';

GO

PRINT N'----- 5';

The above test returns the following in the "Messages" tab:

  Msg 50000, Level 16, State 1, Line 2
  This is from RAISERROR - Severity 16
  ----- 1
  Msg 50505, Level 16, State 1, Line 5
  This is from THROW
  ----- 3
  Msg 2745, Level 16, State 2, Line 4
  Process ID 54 has raised user error 50000, severity 20. SQL Server is terminating this process.
  Msg 50000, Level 20, State 1, Line 4
  This is from RAISERROR - Severity 20
  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

And in the bottom, left corner of SSMS it will indicate "Disconnected".


Database Implementations of ORDER BY in a Subquery I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g.

SELECT * FROM (
        SELECT ID, GEOM, Name
        FROM t
        ORDER BY Name
        ) as tbl

The application has many different database drivers. I mainly use the MS SQL Server driver, and SQL Server 2008. This throws an error if an ORDER BY is found in a subquery.

From the MS Docs (although this is for SQL Server 2000 it still seems to apply):

  When you use an ORDER BY clause in a view, an inline function, a
  derived table, or a subquery, it does not guarantee ordered output.
  Instead, the ORDER BY clause is only used to guarantee that the result
  set that is generated by the Top operator has a consistent makeup. The
  ORDER BY clause only guarantees an ordered result set when it is
  specified in the outermost SELECT statement.

However the same type of query when run in Postgres (9) and Oracle return results - with the order as defined in the subquery. In Postgres the query plan shows the results are sorted and the Postgres release notes include the item which implies subquery orders are used:

  Avoid sort when subquery ORDER BY matches upper query

http://en.wikipedia.org/wiki/Order_by states:

  Although some database systems allow the specification of an ORDER BY
  clause in subselects or view definitions, the presence there has no
  effect.

However from my own checking of query plans:

SQL Server 2008 does not support ORDER BY in a subquery
Postgres 9 does support ORDER BY in a subquery
Oracle 10g supports ORDER BY in a subquery

So my question are there any links that can officially confirm or deny that Postgres and Oracle do not allow sorting in a subquery?

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY optimized away anyway.

And in Oracle and PostGres, just because the syntax is supported, does not mean it is obeyed. And just because you observe it as being obeyed in some scenario, does not mean that it will continue to be obeyed as new versions come out or with subtle changes to your data, statistics, the query itself, or the environment.

I can assure you that, without a doubt, if you want a guarantee about order, you need to put the ORDER BY on the outermost query. This should be a doctrine you hold close no matter what platform you're using.

You are asking for a link that officially states that something is not supported. This is like looking in your car owner's manual for an official statement that your car cannot fly.


MongoDB: Is it possible add SECONDARY member with version 3.0 to a replica set where members have version 2.6? I have a replica set with 1 secondary and 1 arbiter running version 2.6. and I would like to introduce a new member with version 3.0 so that I can upgrade the whole replica set to 3.0. I wonder if this is a way to to a "rolling upgrade" to MongoDB version 3.0?

The upgrade process I'm thinking would be:

Introduce new member with version 3.0 and let it sync. With priority 0 so that it is not elected at any point.
Once new SECONDARY has caught up, increase priority to make it eligible and remove old SECONDARY server.
Step down PRIMARY (new member with version 3.0 becomes PRIMARY).
Repeat steps 1 and 2 to remove what used to be the original PRIMARY member.

Any other upgrade process suggestions are appreciated.

Thanks

This is basically the rolling upgrade procedure from the docs, with the only difference being that you will temporarily be going up to a 4 member replica set based on what you have described. Hence it's basically a viable strategy if you have concerns about 3.0.

Couple of things I will note:

First, the priority zero step shows good caution but is not really gaining you anything. There is no realistic way for that node to become primary before it catches up (not considered up to date, and if the other two members fail there will not be enough votes to elect it primary with 2 out of 4). There's no harm in doing it, it just doesn't add much in a 4 member set.

The other thing that I will note is that the addition of a new set member is not actually needed. You can stop your current secondary and simply update its binaries to 3.0, restart it and it is good. Do the same for the arbiter, then step down the primary and repeat. No resync is then necessary because 3.0 is fine as a straight drop-in replacement for 2.6.


In SQLCMD is there any way of shelling out to run arbitrary programs? In the past I have dropped json into a table in a nvarchar(Max) column.

This is structured data but it needed serving back to the client as json and came in from the client as json so it seemed a bit of a waste of time to map it out to a table and then recombine it again.

However doing this made me a little uneasy as you can't manipulate nvarchar(Max) columns in sql and lets face it sql is really poor at manipulating strings anyway.

Our database versioning process is entirely based around creating scripts to be run by sqlcmd.  This would make it impossible to refactor the data structure (using sql) in the future, which isn't a good state to be in.

So the question is this:  Is there anyway of shelling out of sqlcmd to run arbitrary programs?  This would want to be something synchronous, so the normal flow of patches could resume after the other program was called.  This would allow you write programs in languages more suited to manipulating strings.

The !! <command> syntax lets you shell out.

The external command is handled synchronously (i.e. the next line of the SQL script will not execute until this command finishes). This is why the documentation has a note in a few places stating:

  When you use the Query Editor in SQLCMD Mode, you must be careful not to execute interactive statements. The Query Editor cannot respond to operating system prompts.

and:

  ...because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

You can test this rather easily by creating a SQL script of nothing more than:

!!pause

If you run this in SSMS, you will have to cancel the query else it will just keep running. Once it is canceled, you will see the following in the "Messages" tab:

  Press any key to continue . . . Query was cancelled by user.

Please see this MSDN page for more information.

However, this introduces an external dependency on your build process, not to mention that SQLCMD mode is not enabled by default in SSMS and cannot be programmatically enabled (though it is possible to change the default setting to always be enabled). Hence you might be better off encapsulating the JSON functionality in a SQLCLR function or stored procedure.


[sys].[dm_db_index_usage_stats] returns more entries in some databases I am using the following query to get some indexes usage statistics:

SELECT DB.[name]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]
      ,IDX.[name] AS [Index]
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_index_physical_stats] (DB_ID(),NULL,NULL,NULL,NULL) AS PHS
    ON PHS.[index_id] = US.[index_id]
    AND PHS.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = PHS.[database_id]
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0
ORDER BY [Page_Count] DESC

The issue is on some database the [sys].[dm_db_index_usage_stats] returns more entries for certain indexes. For example:

As you can see each entry is updated in different times on different date. Could anyone tell why this is happening?

From taking a quick look, it's quite possible that you're actually looking at indexes on different databases.

Alternatively, you could have a partitioned table as the docs state that sys.dm_db_partition_stats:

  Returns page and row-count information for every partition in the
  current database.

Take it back to basics, don't use DB_ID() and actually use the database_id field within dm_db_index_usage_stats to join to sys.databases.

For now, it would also be best to remove dm_db_index_physical_stats (the INNER JOIN is not a good way to join to it anyway).

SELECT DB.[name] AS [database]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]
      ,IDX.[name] AS [Index]
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = US.database_id
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0

Check the database names. Are they all definitely the same database?

Add PRS.partition_id to your SELECT. Are they all the same partition?

If that looks to be correct, then perhaps APPLY the dm_db_index_physical_stats table.

SELECT DB.[name]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]
      ,IDX.[name] AS [Index]
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = US.database_id
CROSS APPLY sys.dm_db_index_physical_stats(US.DATABASE_id, US.OBJECT_ID, US.index_id, NULL, NULL)  AS ddius
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0
ORDER BY ddius.page_count DESC


Log shipping with a monthly archival process We will be implementing log shipping in our production environment in the next weeks.

We perform a monthly "cleanup" of the database, whereby we set the database to the SIMPLE recovery model, copy records to an historic database, then delete from the original to free up space.

I know that changing the database recovery model to simple will break log shipping (because the LSN chain will be broken). In that case, I would have to restore a full backup on the secondary server and start again with the log shipping process. I don't want to do this.

I am an 'accidental DBA' and inherited this archiving process. The process is as simple as selecting rows from the live database, inserting them into an archive database, and then deleting them from the live database.

My concern is that if I keep the database in the full recovery model during the deletion, the transaction log might grow in such a way that it could cause space issues and/or make the secondary server fall considerably behind the primary.

What options do I have here? Any advice will be appreciated.

You can script this whole process out with TSQL and SQL Agent jobs depending on the your cleanup process -- I assume you can or already have it scripted but here are the basics how I've dealt with similar issues in environments I maintain and support.

Switch recovery model to simple on primary DB (TSQL with SQL Agent job step) -- this will break the log shipping chain so time out your LSCopy, LSBackup, and LSRestore jobs around this time when this occurs -- then run your cleanup process on primary DB
Switch your primary DB back to full recovery model (TSQL with SQL Agent job step), grow your (or perhaps shrink) your primary log file back to the "usual" size (SQL Agent TSQL again)
Run (or TSQL script with SQL Agent job) a FULL backup of the primary DB to the "usual" full backup location
Restore the secondary DB with the FULL backup file as in #3 above (TSQL with SQL Agent job step)

Afterwards, as timed out with some testing, what you already know about the timing, etc. the LSBackup, LSCopy, and LSRestore jobs should start working again.

I don't remember exactly, but you may need to purge the LSBackup and LSCopy TRN files on both primary and secondary after step #1 above to ensure SQL doesn't try to apply the broken chain TRN files to the secondary DB.

This can be done and I've done it before.  It may not be "best practice" but if there's a business need, that should be enough justification if it works, gets the job done, and allows you to put some automation around it.


Check if 1/3 Queries Evaluates to True My large (+100 line) query contains a WHERE filter similar to:

select *
from (<complex 100+ line query>) d
WHERE (
   (EXISTS (select 1 FROM TABLE1 t1
            WHERE d.dummy = t1.x
              AND t1.col1 = 'FOOBAR' AND t1.col2 IN ('A', ))) OR
   (EXISTS (select 1 FROM TABLE2 t2
            WHERE d.dummy = t2.x
              AND t2.col1 = 'FOOBAR' AND t2.col2 IN ('A', 'B'))) OR
   (EXISTS (select 1 FROM TABLE3 t3
            WHERE d.dummy = t3.x
              AND t3.col1 = 'FOOBAR' AND t3.col2 IN ('A', 'B')))
)

Prior to this "type" of change, i.e. 3 EXISTS, the query ran in 1-3 seconds. However, with the above "type" of addition (as a WHERE filter, the query has ground to a halt - 60 seconds.

I've looked at the EXPLAIN PLAN, but I don't understand the output.

Basically, I only want to show the row from <query> d if its dummy column matches one of the x columns of TABLE1, TABLE2, or TABLE3.

From a performance and good SQL practice point-of-view, is the above WHERE inner query a reasonable way to determine if at least 1/3 conditions is true?

Make sure t3.x is indexed.

If you know one test is much cheaper than another, it may be faster to JOIN them in a manner that makes sense, then use the /*+ORDERED*/ hint to force the optimizer to join them in the order you've written them. This is particularly necessary if your inner query contains REGEX expressions since the optimizer prefers to procrastinate evaluating them.


Returning information in a nested select in the outermost select I've got the following working,

SELECT info1, info2
FROM   TableA
WHERE  id IN (SELECT xid
              FROM   TableB
              WHERE  userid=3 AND active=1)

which works fine, but in TableB I also have a team column which I'd like to select and return in the outermost select statement, something like this "pseudo" query, which of course doesn't work:

SELECT info1, info2, team
FROM   TableA
WHERE  id IN (SELECT xid, team
              FROM   TableB
              WHERE  userid=3 AND active=1)

I had a read around and thought maybe AS could help but I don't really know for sure. Can anybody suggest a solution? Is this even possible?

This is what JOINs are for:

SELECT  A.info1,
        A.info2,
        B.team
FROM TableA A
INNER JOIN TableB B
    ON A.id = B.xid
WHERE B.userid = 3
AND B.active=1
;


SSRS Merge Data Across 2 Group Values I have created a report for SSRS 2008 which produces the results below.

What I am looking to achieve is for those results in pink, AD, to be under both AM and PM with the text centered i.e. merged cells.

The weekdays and AM/PM are group headings in a matrix based on fields in my table. To display the data I am using a table within the data region of a matrix.

I've read other posts about creating an additional row in the table which has 2 cells merged together, using an expression to hide this row when it is not used though I've not managed to get this to work correctly.

UPDATE 28/03/2012:
I've managed to get the AD to appear under both AM and PM. What I did was remove the AM/PM column group and create 2 cells under the Weekdays group and input the text AM and PM in these cells. I then created 2 rows in the data section of the matrix with the bottom row having 2 columns. In each of these cells there is a table with the expression

=IIF(Fields!SessionIdentifier.Value="AD","AD",Nothing)

and the row visibility in each table is set using an expression:

=IIF(Fields!SessionIdentifier.Value="AD", False, True)

What I would like to do now is hide the top row in the data region of the matrix when it is empty but I haven't figured out the expression to do this.

It is a bit kludgey, but here's an idea sketch... instead of merging the cells, could you fake it by placing an A in the left cell aligned to the right and a D in the right cell aligned to the left?  You could then use expressions to control value, alignment, color, and borders of the two separate cells.

Let me know if you need more details.  I think this would give you the visual you want.  Good luck!


Log file growth issues We're consolidating data from a bunch of databases into four reporting databases each night.

Because the entire dataset is imported each night we do not need to be able to restore the data to a point in time. Thus the databases are in simple recovery mode.

Each time we run the import however, our database ldf files grow to absurdly large sizes (50+ Gigs).

Is there a way to turn off the logging altogether or get SQL Server to clear those log files sooner?

I'm guessing no for clearing as the log_reuse_wait_desc is ACTIVE_TRANSACTION.

You can import the data by using operations that can be minimally logged. See Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import.

And you should consider reducing the duration of transactions during the import. Use batches of limited size and commit periodically.


Field Level Auditing in Oracle 11g R2 I want to enable auditing on some column in a table. I have tested the SYS, Schema and Table level auditing. Guide me or refer me some document to enable auditing on column level.

Check that auditing is enabled:

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/phil12c1/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576

If audit_trail is not set (NONE), turn auditing on (as SYSDBA):

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

... then restart the database.

I'll create a test table to use:

create table fgatest
(
  id number primary key,
  value number
);

insert into fgatest values ( 1, 2 );

insert into fgatest values ( 2, 200 );

commit;

Now, I'll create an example of an FGA auditing policy that uses the above table. This will audit any SQL that contains a row with a value greater that 100 in its resultset. The parameters speak for themselves.

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'C##PHIL',
    object_name     => 'FGATEST',
    policy_name     => 'FGA_TEST_CHECK_VALUE_GT_100',
    audit_condition => 'VALUE > 100',
    audit_column    => 'VALUE');
END;
/

Test:

[oracle@ora12c1 ~]$ sqlplus c##phil/phil

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 30 09:55:57 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 30 2014 09:37:34 +00:00

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

SQL>
SQL> select * from fgatest where value>150;

        ID      VALUE
---------- ----------
         2        200
SQL>

SQL> conn / as sysdba
Connected.
SQL> select session_id,timestamp,db_user,sql_text
     from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER    SQL_TEXT
---------- --------- ---------- -------------------------
    280018 30-JAN-14 C##PHIL    select * from fgatest where value>150

SQL>

Note I've done my test on 12c, but this test will work perfectly on 11.2 too.


Giving EXEC (@Variable) a Column name and Concatenation I'm trying to writing a stored procedure that will check for orphan data before our customers attempt an upgrade, as orphaned records can cause issues. This is what I have thus far;

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DetectOrphanDataBeforeUpgrade')
DROP PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
GO

CREATE PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]

@TenantId   INT = NULL

AS
BEGIN

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX)
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

------------------------------------------------------------------------------------------------
/* Throw an error if the TenantId is NULL or Invalid.               */
------------------------------------------------------------------------------------------------
IF @TenantId IS NULL OR @TenantId NOT IN (SELECT Tenants FROM [Application].[dbo].[TenantIdNumber])

BEGIN
    THROW 51000, '@TenantId is invalid because it is NULL or does not exist.', 1;
END

------------------------------------------------------------------------------------------------
/* Checks for Orphan records related to the Audits table                */
------------------------------------------------------------------------------------------------
SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

END;

At the moment, this script is working, however the output is;

I want to return the results on a single line, and have each column with their own name, for example: @OrphanAuditItems result shows as column name: OrphanAuditItems, and so on.

How's the best way to achieve this?

Thanks,
Tom

You will need to mix your 3 dynamic SQLs into 1 that returns 3 columns, or keep each dynamic SQL but mix their result at the end. There are 2 possible solutions following:

One solution is to use UNION ALL and the PIVOT to get 1 row with 3 columns, all inside the dynamic SQL.

Change this part:

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX)
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

--.....

SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

For this:

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    ;WITH PrePivot AS
    (
        SELECT
            Amount = COUNT(*),
            Type = ''OrphanAuditItems''
        FROM
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
        WHERE
            FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])

        UNION ALL

        SELECT
            Amount = COUNT(*),
            Type = ''OrphanAuditAnswers''
        FROM
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
        WHERE
            AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])

        UNION ALL

        SELECT
            Amount = COUNT(*),
            Type = ''OrphanAuditQuestion''
        FROM
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
        WHERE
            AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])
    )
    SELECT
        P.OrphanAuditItems,
        P.OrphanAuditAnswers,
        P.OrphanAuditQuestion
    FROM
        PrePivot AS T
        PIVOT (
            MAX(T.Amount) FOR T.Type IN ([OrphanAuditItems], [OrphanAuditAnswers], [OrphanAuditQuestion])
        ) AS P '

-- PRINT (@DynamicSQL)
EXEC (@DynamicSQL)

Make sure to use the PRINT to validate the resulting SQL before executing the EXEC.

Another solution is to retrieve the values into variables, using sp_executesql with OUTPUT parameters:

-- Items
DECLARE @OrphanAuditItems INT

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    SELECT
        @OrphanAuditItems = COUNT(*)
    FROM
        #OrphanResults AS O
        CROSS JOIN [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
    WHERE
        FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditItems INT OUTPUT',
    @OrphanAuditItems = @OrphanAuditItems OUTPUT

-- Answers
DECLARE @OrphanAuditAnswers INT

SET @DynamicSQL = N'
    SELECT
        @OrphanAuditAnswers = COUNT(*)
    FROM
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
    WHERE
        AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

EXEC sp_executesql
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditAnswers INT OUTPUT',
    @OrphanAuditAnswers = @OrphanAuditAnswers OUTPUT

-- Questions
DECLARE @OrphanAuditQuestion INT

SET @DynamicSQL = N'
    SELECT
        @OrphanAuditQuestion = COUNT(*)
    FROM
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
    WHERE
        AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditQuestion INT OUTPUT',
    @OrphanAuditQuestion = @OrphanAuditQuestion OUTPUT

SELECT
    OrphanAuditItems = @OrphanAuditItems,
    OrphanAuditAnswers = @OrphanAuditAnswers,
    OrphanAuditQuestion = @OrphanAuditQuestion

I find the latter a bit more flexible and readable than the first.


Error when building documentation of Postgres I am trying to build the documentation of Postgres on OSX

After getting the source, installing node and jade, I do:

cd /doc/src/sgml
make html

This results in the error:

   fs.js:684
  return binding.lstat(pathModule._makeLong(path));

         ^
Error: ENOENT, no such file or directory '/Users/thomas/Source/postgres/doc/src/sgml/-p'
    at Object.fs.lstatSync (fs.js:684:18)
    at Object.realpathSync (fs.js:1272:21)
    at /usr/local/lib/node_modules/jade/node_modules/monocle/monocle.js:41:26
    at Array.forEach (native)
    at Object.watchFiles (/usr/local/lib/node_modules/jade/node_modules/monocle/monocle.js:39:16)
    at Object.<anonymous> (/usr/local/lib/node_modules/jade/bin/jade.js:98:13)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)

The directory that the make script is expecting to find looks really odd. What am I doing wrong?

My jade --version is 1.3.1.

Error: ENOENT, no such file or directory '/Users/thomas/Source/postgres/doc/src/sgml/-p'

I'd say you have some ancient version of one of the required tools, and it's seeing the -p command-line switch as a regular path argument instead.


How to wait for SQL Server replication reinitialization via TSQL We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands.

USE [dbImport]

-- Execute at the Publisher to reinitialize the push subscription.
EXEC sp_reinitsubscription
@subscriber = N'Target2',
@destination_db = N'dbImport',
@publication = N'dbImportPub';
GO

-- Start the Distribution Agent.
USE msdb
EXEC sp_start_job @job_name = 'Source1-dbImport-dbImport-Pub-Target2-35'
GO

The next step waits for 15 minutes because this reinitialization needs to take normally 10 minutes and we add some more time to be on the safer side.
Sometimes the reinitialization needs a little bit longer than 15 minutes, so the indexes which are created in next step on target aren't correct.

I can't check for stopping of the distibution job because in transaction replication it never stops.

How can I wait for the end of reinitialization (initial push of all scripts and data) on target2 (to afterwards add the indexes "on time")?

Stefan

The only way I can think of doing this is to slightly hack the process. It would involve the following steps:

Copy the Distribution agent job and rename it to indicate it is run
manually. You'll also need to change/disable the schedule and remove
the "-Continuous" flag from the exec command of this new job.
Create a job that does the following:

Step 1: Stops the main, continuous Ditribution job
Step 2: Executes the sp_reinitsubscription stored proc with
@invalidate_snapshot = 1
Step 3: Launches the Snapshot agent
Step 4: Checks that the Snapshot agent is complete*
Step 5: Launches the manual Distribution agent job

Create a job that checks if the manual Distribution agent job is finished

Step 1: Check if job is running.*
Step 2: Restart the main Distribution agent job

*I initially suggested setting the job to fail if the agent job was still running and configuring retry attemps. It might be better to write a WHILE loop with a WAITFOR DELAY instead:
  WHILE EXISTS(
   SELECT
     ja.job_id,
     j.name AS job_name,
     ja.start_execution_date,
     ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
     Js.step_name
   FROM msdb.dbo.sysjobactivity ja
   LEFT JOIN msdb.dbo.sysjobhistory jh
        ON ja.job_history_id = jh.instance_id
   JOIN msdb.dbo.sysjobs j
        ON ja.job_id = j.job_id
   JOIN msdb.dbo.sysjobsteps js
        ON ja.job_id = js.job_id
        AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
   WHERE ja.session_id = (SELECT TOP 1 session_id
                          FROM msdb.dbo.syssessions
                          ORDER BY agent_start_date DESC)
        AND j.name = 'Snapshot or distribution agent job name'
        AND start_execution_date is not null
        AND stop_execution_date is null)
BEGIN
  --PRINT 'Waiting...'
  WAITFOR DELAY '00:05:00'
END
RETURN


Dealing with large transaction logs in a cluster Disclaimer: I'm a dev, not a DBA. I've gleaned what I can from MSDN, but could do with some expert guidance!

I'm working with a SharePoint 2013 installation on a SQL Server 2012 cluster of two DB servers. It's only been up for a week or so and my transaction logs have already filled the 25GB allocated for them on each box. This is only a test environment, so I don't really need to keep the transaction logs (normally I just set the logging to "Simple" and let nature take its course) but obviously the logs are needed to ensure everything is replicated properly. What I'd like is for the logs to be dumped as soon as they are no longer needed, but I'm not sure how to accomplish this (or if it's even possible).

Based on my current (limited) understanding, I've set up a Maintenance Plan using the wizard that runs every day to backup the transaction log (so SQL Server will allow it to be deleted). I've set the backups to expire after a couple of days so that hopefully they don't grow too large. Do I then need steps to shrink the databases and cleanup after the plan to get rid of the logs?

I'm not expecting a complete solution (but that would be nice!) but any guidance as to whether I'm on the right lines would be greatly appreciated.

You are on the right track.  With the database in full recovery mode, no portions of the log file will be marked for reuse until it has been backed up.  I'm assuming you've created your maintenance plan to have the capability to backup the transaction log.  In which case, that should get you to a point where you should be allowing the reuse of log portions (there are other things that can prevent log reuse as well, but not worth mentioning until/unless you run into them).

How often are your transaction logs getting backed up?  Depending on the frequency of logged operations, you might need to set that to a more frequent schedule than once a day (again, it depends solely on the rate of logged transactions).

As for the physical log file sizes (25 GB NTFS files), they will not shrink just because you have done the transaction log backups.  Backups will not affect the file size.  You'd have to explicitly shrink the transaction log, if you in fact need to reclaim that space.

  Do I then need steps to shrink the databases and cleanup after the plan to get rid of the logs?

As for "steps to shrink the databases", no you shouldn't have to routinely do this.  You may want to do this once to get the log files to management sizes, but then with a well-chosen log backup schedule (see above) those log files shouldn't grow out of control again.  And with the "cleanup" of the transaction log backups, that's really up to you.  I'm not sure what type of disaster recovery plan you have for this environment, so the retention period of those transaction log backups will be driven by your requirements and the DR plan for these databases.


Where is Oracle home user being stored Oracle 12c has introduced Oracle home user in its installation process. I have searched and read some articles about Oracle home user. (Please see screenshot below)

My questions are:

If I use create new windows user, I typed username and password here, after the installation, I do not see a windows user is created in my windows system(control panel -> manage account). Do I need to create a windows user with the same name and password in order to use the database? Or I do not need to create anything, it is just a windows user hidden somewhere in my system. And what Did this create new windows user really do.
Oracle says use this create new windows user, the user will not have login privilege. What does this mean, it has no login privilege for windows? or for Oracle database.
First time I tried to install Oracle database, it was hanging at 88%. I uninstalled and deleted everything I know, then I started to install it again, this time if I use "Create new windows user", and use the same user name, it told me Specified User is already exists. As I said before, there is no such user in my windows.(control panel -> manage account). Now if I use a new one, it will work. Is there any way I can find where was the previous user stored? I checked the inventory.xml, its not in there.

I found the user folders was in the windows user path, but for some reason these are not normal windows users, so it is not visible to the account management. Thank you.


Numeric data type with variable significant digits I need to implement a data type which should preserve the last zeros in the decimal points in SQL Server 2008 R2. Example of the data are

1.002
1.210
0.00100
0.1401

My clients get lab measurements like these and the number of decimal points tell what typee of instrumentation, methods etc. the lab used. They require to store the data as it is (not 1.21 for 1.210). For now I have a custom data type which implements varchar type as a base type, but the developers do not like it as the conversion to use mathematical operators involve some overhead. Is there any numeric type solution to handle this type of issue?

I would store this as two different INT/BIGINT, one for each side of the decimal point. To make it easier to query, you can also add a computed column that renders the data as a varchar. In other words:

CREATE TABLE MyData
(
  IntegerPart INT NOT NULL
  , FractionPart INT NULL /* Null of no fraction */
  , HumanFormat AS CAST(IntegerPart AS VARCHAR)
      + '.' + COALESCE('.'+CAST(FractionPart AS VARCHAR), '')
)

Optionally, add more computed columns to store as float or other formats that you might need to calculate.


How to find the schema name of a data pump dmp file? I have been given a DMP data pump export file to import into my local Oracle instance.  I've tried running this command line:

impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp logfile=import.log

I get the errors:

ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import

And no data is imported.
From what I've Googled, one possible cause of this is that I need to specify remap_schama.  But I have no idea what the name is of the schema in the dmp file.  Any easy way to find out?
EDIT: I didn't find a solution to this question, but I did find a workaround... I tracked down the guy who made the DMP, and beat got the schema name out of him.  Specified remap_schema according to his definition, and Hey Presto!

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump.

For example:

[oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt

Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "PHIL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "PHIL"."SYS_SQL_FILE_FULL_01":  phil/******** directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PHIL"."SYS_SQL_FILE_FULL_01" successfully completed at 15:15:05

[oracle@oel61 ~]$

Looking for CREATE USER DDL statements in the file should show you what schemas are needed in order to perform an import.

For example, from my test dump:

-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "PHIL" IDENTIFIED BY VALUES 'S:924B2E756404611021428644B4DF06A4A7BAB886837FCCFA510151E0FC44;181446AE258EE2F6'
      DEFAULT TABLESPACE "PHILDATA"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "PHIL";
GRANT CREATE SESSION TO "PHIL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "PHIL";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "PHIL" DEFAULT ROLE ALL;

The only drawback is that if the .dmp file is large the resulting SQL dump will be huge.


Connection Pooling for PHP based website using Oracle 11g database I am using Oracle 11g database server and now I am getting to see that OCI's persistent connection isn't much of a use, and a new connection is created anyway when a new user accesses the page. I would like to have a pool of connections which are used, and no new connections are not created. I came across DRCP (database resident connection pooling), but I am unable to understand how to set the number of connections that can be created per server.

Questions

From the documentation available on the Oracle website, I can set the minimum and maximum number of pooled servers, but where would I be setting the number of connections??
Has anyone worked with Oracle 11g's database resident connection pooling?
If the database servers are unix boxes, can we do any ODBC connection pooling??

where would I be setting the number of connections?

You can specify the min and max number of connections in the pool using

SQL>execute dbms_connection_pool.configure_pool(null, minsize=>10, maxsize=>100);

  If the database servers are unix boxes, can we do any ODBC connection pooling?

I'm not quite sure why you're asking this.  You can take advantage of DRCP via OCI in PHP, as long as you have the Oracle 11g client.  Just specify your connection string like myhost.dom.com:1521/sales:POOLED and make your connections using oci_pconnect().

Here's a few resources that may be of help:

Oracle DRCP Whitepaper
DRCP entry on OraDBPedia
PHP OCI manual


Refreshing a stored generated column in Postgres How in PostgreSQL can I refresh the values of a stored generated column.

Example

CREATE TABLE people (
    first_name TEXT,
    last_name TEXT

);

CREATE OR REPLACE FUNCTION name(people) RETURNS text AS
$$
SELECT $1.first_name || ' ' || $1.last_name
$$ LANGUAGE SQL STRICT
                IMMUTABLE
        ;

alter table people add column   full_name text GENERATED ALWAYS AS (name(people)) STORED;

insert into people(first_name, last_name) values('John', 'Smith');

select full_name, full_name='John Smith' as pass from people;

If one then needs to change the function used to generate a stored column value, in this case name how can you ask postgres to recalculate these fields.

eg.

CREATE OR REPLACE FUNCTION name(people) RETURNS text AS
$$
SELECT $1.first_name || ' ' || $1.last_name  ||  ' function change'
$$ LANGUAGE SQL STRICT
                IMMUTABLE
        ;

 -- refresh generated column here

select full_name, full_name='John Smith function change' as pass from people;

Sorry this is a bit of an odd example.

Fiddle

The only way is to rewrite the whole table with a no-operation update like:

UPDATE people SET first_name = first_name;

That will re-evaluate the function.

You may want to run VACUUM (FULL) on the table afterwards to reclain the wasted space.


SQL Server Credential Password and Secret Difference? What is the difference between SQL Server secret and password for credentials(both server and db scope)?
For example while reading the BOL I can see specifying a secret. Is this just the same password?

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql?view=sql-server-2017

What is the difference between SQL Server secret and password for credentials

A credential has three fields: Name, Identity, and Secret.  Depending on what the credential is used for the fields are used differently.  For instance if the credential is used to store a Windows identity, the user name goes in Identity, and the password goes in Secret.  If it's used to store the access credentials for Azure blob storage, a SAS token is stored in Secret, etc.


Indexing order by columns Based on the query below it was suggested that I create an index on the column in the order by clause. My understanding is the sorting is done on the result set and not the table.

The person suggesting this insists it helps with performance. I don't think it does but I have been wrong once or twice before.

Thanks.

SELECT TOP 1 [Process_Activity_Id]
,[Process_Id]
,[Transaction_Type]
,[Company_Code]
,[Entity_Identifier]
,[Item_Request]
,[Item_Response]
,[Item_Description]
,[Status]
,[Status_Detail]
,[Start_Date]
,[End_Date]
,[Last_Update_Date]
FROM [dbo].[Process_Activity] (NOLOCK)
WHERE [Entity_Identifier] = @EntityIdentifier
  AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC

Based on:

WHERE [Entity_Identifier] = @EntityIdentifier
AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC

...the best thing is to have an index on

([Entity_Identifier], [Process_Id], [Message_Sequence]) WHERE [Status] = 'In_Progress'

As Status is filtered with a constant, a WHERE clause is going to be useful for this, so that the index only holds the "In_Progress" rows.

But you need to find the rows that match the Entity and Process, and to have those rows in Message_Sequence order (to save getting all the Message_Sequence values and then sorting them when the query runs).

The composite index like this will let the database engine seek into the appropriate Entity and Process, knowing that the first row it finds is the smallest Message_Sequence.

So the advice about indexing on Message_Sequence is kinda right, so long as you're only indexing on Message_Sequence within an index that's on Entity and Process, because those columns are more important.

If you only had an index on Message_Sequence, you'd be searching through the index for the first one that matched Entity and Process, which is only good for non-equality predicates (oh - I'm talking about this at the PASS Summit in Seattle this October).


Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this

SELECT *
FROM
  ( SELECT "people"."id", "name", "class",
    ROW_NUMBER() OVER (PARTITION BY "class" ORDER BY COUNT("violation") DESC) AS "v"
    FROM "people"
    INNER JOIN "discipline" on ("discipline"."people_id" = "people"."id")
    GROUP BY "people"."id", "name", "class"
  ) AS "v_table"
WHERE v < 3

The query seems inefficient because it sorts the count first with ORDER BY and then assigns a ROW_NUMBER. If I already have it sorted over a partition, how can I skip the ROW_NUMBER assignment & get the top 2.

Update :
Adding data tables & SQL fiddle (actually violation column is redundant, simply an entry in discipline table means that there's been a violation. If I remove it, should the Count be done on "discipline"."people_id"? like this )

Discipline                     People
-------------------------      --------------
id  people_id  violation       id  name  class
1   1          True            1   Rob   A
2   1          True            2   Jen   B
3   2          True            3   Tom   C
4   3          True            4   Ted   A
5   4          True            5   Tim   A
6   1          True            ...
7   4          True
...

Using PostgreSQL 9.3

First, to answer the questions implied in the comments, that the assignment of row numbers with the ROW_NUMBER() aggregate seems inefficient because we already have the COUNT(violation) numbers:

This is needed because the COUNT numbers can be different for each partition (class). Since we want the 2 top numbers (for each class), we can't find a useful condition for that. With the row numbers, we can use the WHERE v < 3 which gives us the top 2.

In version 9.3, the LATERAL joins were added in Postgres, which are similar to the CROSS and OUTER APPLY of SQL-Server. With this new kind of join, you can write a query that uses the COUNT numbers and a TOP 2 for each partition. Whether it is more or less efficient, you can test:

WITH classes AS
  ( SELECT DISTINCT class
    FROM people
  )
                             -- if you have a "classes" table, skip the above lines
SELECT
    v.id, v.name, c.class,
    v.violations
FROM
    classes AS c,
  LATERAL
    ( SELECT p.id, p.name,
             COUNT(d.violation) AS violations
      FROM people AS p
        INNER JOIN discipline AS d
          ON d.people_id = p.id
      WHERE p.class = c.class
      GROUP BY p.id, p.name
      ORDER BY violations DESC
      LIMIT 2
  ) AS v
ORDER BY
    c.class, v.violations DESC ;

Tested at SQL-Fiddle


How to convert database mysql4 to mysql5 I have mysql dump file named booster.dump. It was export from 4.0.27-standard mysql server. Now I want to convert this database to mysql 5.1.73-cll. How can I do it? I search about this topic in internet and I have got a lots of solution but no one work for me.

You can do the following on a Linux system:

sed 's/TYPE=/ENGINE=/g' booster.dump > booster_sane.dump and afterwards import the booster_sane.dump. If you don´t care about your original file you can also make the changes inplace before importing: sed -i 's/TYPE=/ENGINE=/g' (or specify a suffix to -i option to make a backup on the fly).

The reason for your error is explained in the comments to your question.


set unique key checks 0 in mydql In a table i have unique value check for the combination of box_number,price,start_val,end_val

so there can not be a two box number with same number which have same price , start_val and end_val..

table data right now

| box_type_id | box_number | price |start_val | end_val
|  1          |   7        | 5.00  |100       |1
|  2          |   5        | 10.00 |50        |40
|  3          |   5        | 5.00  |100       |1

what i want to do
i want to change box number 7 with 5 for box_type_id 1 and change box number 5 with 7 for box type id 3 ; my query is below... giving me error

MySQL Database Error: Duplicate entry '7-5.00-100-1' for key 'unique_index'

but here i am setting unique key check to 0. before executing query.

My query

SET UNIQUE_CHECKS=0;
 Update `box_types` `bt` SET `bt`.`box_number` = "7" where `bt`.`box_type_id` = "3" ;
 Update `box_types` `bt` SET `bt`.`box_number` = "5" where `bt`.`box_type_id` = "1" ;
SET UNIQUE_CHECKS=1;

how do i solve this problem.. is there any other prblem ? please guide me..

Swapping is a 3-step process (for this and many other situations).  Go via a temp id.  Something like:

Update `box_types` `bt` SET `bt`.`box_number` = "-1" where `bt`.`box_type_id` = "3" ;
Update `box_types` `bt` SET `bt`.`box_number` = "5" where `bt`.`box_type_id` = "1" ;
Update `box_types` `bt` SET `bt`.`box_number` = "7" where `bt`.`box_number` = "-1" ;

(If box_number is UNSIGNED, then pick some value that will work:  maybe 0?  Maybe 9999999?)


ASM File System type What is the underlying file system in ASM if exists? I have gone through many sites but neither of them answers my question directly rather each site I visisted raises a different point like 'ASM uses ACFS file system' or makes the topic complicated.

Oracle ASM is both a volume manager and filesystem from Oracle database files. It does not have an 'underlying filesystem', it is the filesystem.

  Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations.

ACFS is something else: it's a filesystem based on ASM, but for "regular" files. It is meant as a general purpose filesystem, that happens to use ASM as a backend.

  Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle Automatic Storage Management (Oracle ASM) functionality to support customer files maintained outside of Oracle Database. Oracle ACFS supports many database and application files, including executables, database trace files, database alert logs, application reports, BFILEs, and configuration files. Other supported files are video, audio, text, images, engineering drawings, and other general-purpose application file data.


How to put one table on one disk and anther table on another (a ramdisk)? I need to build a database with one table stored on one disk and anther table stored on a different disk (a ramdisk).

Is it possible to do it?

As far as I know, using two separate databases, one for each disk, is not efficient.

I am considering PostgreSQL, but other options can be ok. The reason I want to use a ramdisk is to deal with non critical data that can be deleted at any time, to avoid SSD wearing.

I have not done it but, if not mistaken, it can be easily accomplished with tablespaces.

Let us know how it went.


Determining the full postgresql size on windows While opening the Programs and Features tool panel on my Windows 7 (64-bit), I found that my postgresql (8.4) install was taking 108 GB.
I do not use postgresql myself, I just use a tool that need a postgresql db, and I am fairly confident in the fact that my postgresql databases are not that large.

When I go to the install folder of postgresql (C:\Program Files (x86)\PostgreSQL) and then clicks on properties, it displays a size of 1.45 GB. In c:\users\postgres I get 13.5 MB. I end up quite astonished by the windows diagnosis of 108 GB.

So here is the question :
How could I calculate the whole size on disk taken by postgresql (including everything : soft, logs, db, tools, etc...). Is everything located under the install folder and the postgres user folder?

I also would like to have your views on which is responsible of the mistake (if any) about the size calculation in the tool panel, postgresql or windows? How could windows end up showing that very large size information?

Thanks for your inputs!

Edit : I made several searches on the windows tool after having read the @a_horse_with_no_name comment's, and found some detailled information :

http://social.technet.microsoft.com/Forums/zh/w7itprogeneral/thread/95af2aa5-507f-45ab-9bd1-4c4c7cde8145
http://technet.microsoft.com/en-us/magazine/gg558108.aspx
http://community.spiceworks.com/how_to/show/2238-how-add-remove-programs-works

So, only the my main question remains, how could I calculate the whole size taken by postgresql on my disk. Thanks.

Part of the difficulty here depends on setup.  For this purpose I will assume a default setup.  I am also assuming here that we aren't counting system libraries here, or connection libraries, only PostgreSQL itself (i.e. no dependencies, and not libpq).

The best place to start is indeed the sum of the user folder plus the program files/postgresql folder.  If you are putting the logs elsewhere you will want to add those in as well.


How can I force a scalar UDF to be evaluated just once in a query? I have a query which needs to filter against the result of a scalar UDF. The query must be sent as a single statement (so I can't assign the UDF result to a local variable) and I cannot use a TVF. I am aware of the performance issues caused by scalar UDFs, which include forcing the entire plan to be run serially, excessive memory grants, cardinality estimation problems, and lack of inlining. For this question please assume that I need to use a scalar UDF.

The UDF itself is pretty expensive to call but in theory the queries can be logically implemented by the optimizer in such a way that the function only needs to be calculated once. I have mocked up a greatly simplified example for this question. The following query takes 6152 ms to execute on my machine:

SELECT x1.ID
FROM dbo.X_100_INTEGERS x1
WHERE x1.ID >= dbo.EXPENSIVE_UDF();

The filter operator in the query plan suggests that the function was evaluated once for each row:

DDL and data prep:

CREATE OR ALTER FUNCTION dbo.EXPENSIVE_UDF () RETURNS INT
AS
BEGIN
    DECLARE @tbl TABLE (VAL VARCHAR(5));

    -- make the function expensive to call
    INSERT INTO @tbl
    SELECT [VALUE]
    FROM STRING_SPLIT(REPLICATE(CAST('Z ' AS VARCHAR(MAX)), 20000), ' ');

    RETURN 1;
END;

GO

DROP TABLE IF EXISTS dbo.X_100_INTEGERS;

CREATE TABLE dbo.X_100_INTEGERS (ID INT NOT NULL);

-- insert 100 integers from 1 - 100
WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO dbo.X_100_INTEGERS WITH (TABLOCK)
SELECT n FROM Nums WHERE n <= 100;

Here is a db fiddle link for the above example, although the code takes about 18 seconds to execute there.

In some cases I may not be able to edit the function's code because it is supplied by a vendor. In other cases I am able to make changes. How can I force a scalar UDF to be evaluated just once in a query?

Ultimately, it is not possible to force SQL Server to evaluate a scalar UDF just once in a query. However, there are some steps which can be taken to encourage it. With testing I believe that you can get something that works with the current version of SQL Server, but it's possible that future changes will require you to revisit your code.

If it's possible to edit the code a good first thing to try is to make the function deterministic if possible. Paul White points out here that the function must be created with the SCHEMABINDING option and the function code itself must be deterministic.

After making the following change:

CREATE OR ALTER FUNCTION dbo.EXPENSIVE_UDF () RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @tbl TABLE (VAL VARCHAR(5));

    -- make the function expensive to call
    INSERT INTO @tbl
    SELECT [VALUE]
    FROM STRING_SPLIT(REPLICATE(CAST('Z ' AS VARCHAR(MAX)), 20000), ' ');

    RETURN 1;
END;

The query from the question is executed in 64 ms:

SELECT x1.ID
FROM dbo.X_100_INTEGERS x1
WHERE x1.ID >= dbo.EXPENSIVE_UDF();

The query plan no longer has the filter operator:

To be sure that it executed only once we can use the new sys.dm_exec_function_stats DMV released in SQL Server 2016:

SELECT execution_count
FROM sys.dm_exec_function_stats
WHERE object_id = OBJECT_ID('EXPENSIVE_UDF', 'FN');

Issuing an ALTER against the function will reset the execution_count for that object. The above query returns 1 which means the function was only executed once.

Note that just because the function is deterministic does not mean that it will be evaluated only once for any query. In fact, for some queries adding SCHEMABINDING can degrade performance. Consider the following query:

WITH cte (UDF_VALUE) AS
(
    SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;

The superfluous DISTINCT was added to get rid of a Filter operator. The plan looks promising:

Based on that, one would expect the UDF to be evaluated once and to be used as the outer table in the nested loop join. However, the query takes 6446 ms to run on my machine. According to sys.dm_exec_function_stats the function was executed 100 times. How that is that possible? In "Compute Scalars, Expressions and Execution Plan Performance", Paul White points out that the Compute Scalar operator can be deferred:

  More often than not, a Compute Scalar simply defines an expression; the actual computation is deferred until something later in the execution plan needs the result.

For this query it looks like the UDF call was deferred until it was needed, at which point it was evaluated 100 times.

Interestingly, the CTE example executes in 71 ms on my machine when the UDF is not defined with SCHEMABINDING, as in the original question. The function is only executed once when the query is run. Here is the query plan for that:

It's not clear why the Compute Scalar isn't deferred. It could be because the nondeterminism of the function limits the rearranging of operators that the query optimizer can do.

An alternative approach is to add a small table to the CTE and to query the only row in that table. Any small table will do, but let's use the following:

CREATE TABLE dbo.X_ONE_ROW_TABLE (ID INT NOT NULL);

INSERT INTO dbo.X_ONE_ROW_TABLE VALUES (1);

The query then becomes:

WITH cte (UDF_VALUE) AS
(
    SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
    FROM dbo.X_ONE_ROW_TABLE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;

The addition of the dbo.X_ONE_ROW_TABLE adds uncertainty for the optimizer. If the table has zero rows then the CTE will return 0 rows. In any case, the optimizer cannot guarantee that the CTE will return one row if the UDF is not deterministic, so it seems likely that the UDF will be evaluated before the join. I would expect the optimizer to scan dbo.X_ONE_ROW_TABLE, use a stream aggregate to get the maximum value of the one row returned (which requires the function to be evaluated), and to use that as the outer table for a nested loop join to dbo.X_100_INTEGERS in the main query. This appears to be what happens:

The query executes in about 110 ms on my machine and the UDF is evaluated only once according to sys.dm_exec_function_stats. It would be incorrect to say that the query optimizer is forced to evaluate the UDF only once. However, it is hard to imagine an optimizer rewrite that would lead to a lower cost query, even with the limitations around UDF and compute scalar costing.

In summary, for deterministic functions (which must include the SCHEMABINDING option) try writing the query in as simple of a way as possible. If on SQL Server 2016 or a later version, confirm that the function was only executed once using sys.dm_exec_function_stats. Execution plans can be misleading in that regard.

For functions not considered by SQL Server to be deterministic, including anything lacking the SCHEMABINDING option, one approach is to put the UDF in a carefully crafted CTE or derived table. This requires a little care but the same CTE can work for both deterministic and nondeterministic functions.


rows are rendered not in serial order as appeared in database an wondering why my data though in serial order by id when i look at the records in the database but when rendered, they are not in order of id?

for example:
database

id animal
1  dog
2  lion
3  tiger

html View on the browser

id  animal
2   lion
1   dog
3   tiger

As @a_horse_with_no_name already said: the data may not be stored in the sequential order.

You have some choices:

Use a ORDER BY id[1] clause and force the database to sort the data for you.
Force a CLUSTER[2] on the table to rewrite the data and sort based on a index as you want. I don't recommend that as a solution for your question but it is a alternative. IMPORTANT: CLUSTER command only will physically sort the data on the disk, rewriting the data files based on the index. There is no guarantee of the data will be returned on the same order.

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/queries-order.html
https://www.postgresql.org/docs/current/static/sql-cluster.html


How to delete list of email addresses from MySQL database? I have a list of invalid email addresses parsed in the following format;

There are roughly 1500 invalid addresses, and I need to remove them from my myBB users.

What SQL query can I run to remove the list from my database?

If you have a main table like this

CREATE TABLE users
(
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255),
    ....
    PRIMARY KEY (id)
);

You can built the table that contains the ids of the users with bad email addresses

CREATE TABLE users_invalid_emails
SELECT id.email FROM users WHERE email LIKE 'email%' and email LIKE '%@domain.com';
ALTER TABLE users_invalid_emails ADD PRIMARY KEY (id);

Look over the content of users_invalid_emails. If the emails in that table are to be deleted, you can now do a DELETE JOIN:

DELETE A.* FROM users A INNER JOIN users_invalid_emails B USING (id);

Before you do this, make sure you have a backup of the table.

In your case, the code will be

CREATE TABLE myBBusers_invalid_emails
SELECT id.email FROM my_BBusers WHERE email LIKE 'email%' and email LIKE '%@domain.com';
ALTER TABLE myBBusers_invalid_emails ADD PRIMARY KEY (id);
DELETE A.* FROM my_BBusers A INNER JOIN myBBusers_invalid_emails B USING (id);
DROP TABLE myBBusers_invalid_emails;

Give it a Try !!!


Transact-SQL script equivalent to the option: Apply Tracked Changes Could anybody tell me what is the transact equivalent script of using the: Apply Tracked Changes menu?

There's an interesting discussion about this here:
https://www.sqlservercentral.com/Forums/Topic1480807-2799-1.aspx

and I reckon that you will find everything you need here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-2017

Basically it seems to be something like:

ALTER FULLTEXT INDEX ON [table_name] START UPDATE POPULATION


How to prevent duplicate VARCHAR without a key limit? I'd like to store URLs in a database column, and enforce a constraint that values must be unique. Unfortunately, MySQL has a limit on the length of index keys which means that only the first X characters of the URL gets checked for uniqueness. Thus, I've run into false positives where two different URLs triggered a constraint integration violation because the first X characters just-so-happened to be identical.

Is there a way to enforce uniqueness on a VARCHAR column without any limit on its length?

Is it possible to, say, create a non-UNIQUE index over the first X characters and then have a trigger block INSERTs if the remaining characters are identical?

We keep giving you answers that do not directly answer the question, because that is how we solve this problem.  An index of unlimited length is impractical and inefficient, but a unique hash provides a solution that sufficient to the task because of the astronomically low likelihood of a meaningful collision.

Similar to the other offered solutions, my standard approach does not check for duplicates up front -- it is optimistic in that sense: it relies on constraint checking by the database, with the assumption that most inserts are not duplicates, so there's no point in wasting time trying to determine if they are.

Working, tested example (5.7.16, backwards compatible to 5.6; previous versions do not have a built-in TO_BASE64() function):

CREATE TABLE web_page (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  url LONGTEXT NOT NULL,
  url_hash CHAR(24) COLLATE ascii_bin,
  PRIMARY KEY(id),
  UNIQUE KEY(url_hash),
  KEY(url(16))
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Note that I am storing the base64 version of the hash.  This is a 4:3 size tradeoff compared to storing it in binary form because it makes the table contents and the error message human readable, and the inefficiency is partially offset by the table compression.  The hash column has a unique constraint.  The data type is CHAR, not VARCHAR, since this eliminates the byte needed to store the size -- the hash is always a fixed size.  The column uses the ascii character set with ascii_bin (case-sensitive) collation, keeping the column and the unique index as small as practical.

The url_hash is set by a trigger, below, but the trigger does not check for a collision -- there is no need to check, because of the unique constraint on url_hash.  The database will block a duplicate insert.

Note that url_hash should have been declared NOT NULL but MySQL incorrectly enforces this before the BEFORE INSERT trigger fires, instead of after, so we are limited by that.  The trigger does prevent it from being null.

The url column has a prefix index length of 16, which was chosen arbitrarily.  This isn't a unique constraint, just an index for lookups, and it is probably shorter than you might want it to be, but its length has no operational impact on the problem we are solving, here.

Here's the trigger to set the url_hash.  We don't need to include this value in an INSERT statement when we insert rows.

DELIMITER $$
DROP TRIGGER IF EXISTS web_page_bi $$
CREATE TRIGGER web_page_bi BEFORE INSERT ON web_page FOR EACH ROW
BEGIN
  SET NEW.url_hash = TO_BASE64(UNHEX(MD5(NEW.url)));
END $$
DELIMITER ;

You need a trigger on update also, either to block updates if the table is supposed to be immutable, or to update the hash if the URL changes.  We also need this trigger to ensure that the url_hash column can't be inappropriately set to NULL since the limitation in MySQL doesn't allow us to actually declare it that way, as we should.

Now, to test.

mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
+----+---------------------+--------------------------+
1 row in set (0.00 sec)

So far, so good.  Now, a different URL:

mysql> INSERT INTO web_page (url) VALUES ('http://example.net/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
|  2 | http://example.net/ | ZVk/eLfvBI6tHN0Luj3NnQ== |
+----+---------------------+--------------------------+
2 rows in set (0.00 sec)

Still works.  Now, a duplicate.

mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
ERROR 1062 (23000): Duplicate entry 'pr8XV//wV/JmtpffnPF2/Q==' for key 'url_hash'

Perfect.  If you want an even lower risk of hash collisions than MD5 provides, use a SHA variant, increasing the length of data_hash to CHAR_LENGTH(TO_BASE64(UNHEX( /* your hash function */ ))) to accommodate the values generated by the hash algorithm in use.