Upgrading from SQLOLEDB to MSOLEDBSQL Trying to get a handle on upgrade implications. When I try and install the new MSOLEDBSQL driver, Windows says:

  A lower version of this product has been detected on your system.
  Would you like to upgrade...

I am presuming that refers to the existing SQLOLEDB driver, and that updating will break my ability to use all of the applications that use PROVIDER=SQLOLEDB.1?
I'd like to use the new driver for the current project and moving forward, but wasn't planning to upgrade every application (and every user's installed driver, etc) just yet.

Is the new driver backward compatible without updating all the
connection strings?
Can I roll back the old driver anytime? (I didn't see a download for
it anywhere since it's deprecated.)

I am presuming that refers to the existing SQLOLEDB

That is not the case.

The installer is not trying to upgrade SQLOLEDB.  The old driver is part of Windows and won’t be affected by the installation or upgrade of MSOLEDBSQL.


Detecting and fixing database corruption on SQL Server mirrored database I’m new to my current company and we have a System Administrator but no DBA.  In the past I’ve taken care of DBA related issues at other companies.  I’ve run into and corrected database corruption in other databases (Foxpro and Access) and was able to at least document the data corrupted so it could be reentered.   I’ve never encountered or suspected corruption with a SQL Server database.  I suspect that I’m running into this with a SQL Server 2005 database that is being mirrored.

The situation is that simple delete commands passed to the database are not deleting data in certain tables.  I literally have to go in through SQL Server Management Studio and issue the delete command to clear out these tables.  There are anywhere from 144 thousand to 32 Million records in temp tables and this data is taking up more than 90% of the database (as determined after deleting the records and shrinking the database).  After I do this clean up, then the delete commands passed to the database on the test server work.

My first line of attack would be to run DBCC CHECKDB against copies of the database.  I ran it against a backup and there was no corruption found.  We can’t get exact copies of the database as my System administrator has had issues in the past where the database server has taken up to 6 hours to get online and he does not want to disable both the live and mirrored SQL servers.

My first question is if I should trust a database backup to include corrupted data that would be found by DBCC CHECKDB.  If the backup does not store the corruption, what is the best way to stop and then bring back both the live and mirrored database to get copies of the LDF and MDF files?

Finally if I do find corruption in testing how would I determine the contents of the page that could show the corruption?

It is highly unlikely what you are seeing is corruption.

The far more likely cause of this issue is the "app" is not issuing the DELETE FROM command correctly, or in the way that you think it is.  Perhaps the DELETE is being rolled back for some reason, or perhaps the WHERE statement is preventing rows being deleted that you think should be deleted.  If you can view the code in the app, add the code that is performing (or not performing) the DELETE to your question via the edit link.   If you don't have access to the code, you could use SQL Server Profiler to run a trace so you can "see" the statements your app is issuing to the server to determine for certain what the app is doing.

It's possible there are some application-level data problems preventing the application from performing the delete.  For instance, if there are no primary/foreign key relationships enforcing referential integrity, it's possible a DELETE FROM statement is using a join in the delete that is effectively excluding rows from being deleted as a result of those rows being orphaned.  By way of an example, take a look at this:

USE tempdb;

IF OBJECT_ID(N'dbo.Child', N'U') IS NOT NULL
DROP TABLE dbo.Child;
IF OBJECT_ID(N'dbo.Parent', N'U') IS NOT NULL
DROP TABLE dbo.Parent;

CREATE TABLE dbo.Parent
(
    parent_id int NOT NULL
        CONSTRAINT Parent_pk
        PRIMARY KEY CLUSTERED
    , parent_data varchar(50) NOT NULL
);

CREATE TABLE dbo.Child
(
    child_id int NOT NULL
        CONSTRAINT Child_pk
        PRIMARY KEY CLUSTERED
    , parent_id int NOT NULL
    , child_data varchar(10) NOT NULL
);

INSERT INTO dbo.Parent (parent_id, parent_data)
VALUES (1, 'A')
    , (2, 'B');

INSERT INTO dbo.Child (child_id, parent_id, child_data)
VALUES (1, 1, 'C')
    , (2, 1, 'D')
    , (3, 2, 'E')
    , (4, 2, 'F')
    , (5, 3, 'G')
    , (6, 3, 'H');

SELECT *
FROM dbo.Child;

╔══════════╦═══════════╦════════════╗
║ child_id ║ parent_id ║ child_data ║
╠══════════╬═══════════╬════════════╣
║        1 ║         1 ║ C          ║
║        2 ║         1 ║ D          ║
║        3 ║         2 ║ E          ║
║        4 ║         2 ║ F          ║
║        5 ║         3 ║ G          ║
║        6 ║         3 ║ H          ║
╚══════════╩═══════════╩════════════╝

Now, if we want to delete all rows from dbo.Child, we might use this delete statement that deletes all child rows that have a parent in the parent table:

DELETE FROM dbo.Child
FROM dbo.Child c
INNER JOIN dbo.Parent p ON c.parent_id = p.parent_id;

If we had a properly defined foreign-key in the dbo.Child table, the 5th and 6th rows would never be able to exist, however since we don't have good relational integrity, rows can exist in the child table that have no parent, as you can see from this SELECT statement ran after the above delete runs:

SELECT *
FROM dbo.Child;

╔══════════╦═══════════╦════════════╗
║ child_id ║ parent_id ║ child_data ║
╠══════════╬═══════════╬════════════╣
║        5 ║         3 ║ G          ║
║        6 ║         3 ║ H          ║
╚══════════╩═══════════╩════════════╝

When SQL Server detects corruption in the primary database (the one that is currently on-line and serving clients), it will transfer the page that is corrupted from the mirror database, via Automatic Page Repair, in an attempt to fix the database automatically.  Automatic Page Repair has been available since SQL Server 2005 Service Pack 1, when mirroring officially became a supported option.

Since you've ran DBCC CHECKDB with no corruption reported, I'd say with near 100% certainty that you're not experiencing corruption.


Auto increment form field I am working with a database, with  some forms. What I am trying to do is an auto increment field in my form. For example ID -- every time when I type some new record, this field ID should increment automatically like this: ID0001, ID0002 etc. I need a formula to put in before update to do that. Thanks.

Where My_ID = ID0001, ID0002, ID0003…
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<10,"ID000" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<100,"ID00" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<1000,"ID0" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
"ID" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1
)))

With this, you will be good for 9,999 unique ID numbers before going from a 6 character ID to 7 characters.

The formula display has extra carrage returns for visual effect which can be removed to provide a continuous string.

If you wish to use VBA, you might consider the following:

    Dim IDNum as Integer
    Dim NewID as String

    IDNum = Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1

    IF IDNum< 10 then
        NewID  = "ID000" & IDNum
    ElseIF IDNum< 100 then
        NewID  = "ID00" & IDNum
    ElseIF IDNum< 1000 then
        NewID  = "ID0" & IDNum
    Else
        NewID  = "ID" & IDNum
    EndIf

    Me.My_ID = NewID

I hope that this is helpful.


After Rackspace server creation, PostgreSQL query planner doesn't work as expected We created an image of one of our database servers in Rackspace.  Then, we created a new server using that image, expecting things to work.  However, the index performance we have seen seems to be bad, and even the query plans, when comparing the explain analyze outputs from the first server to the second one, have been different.  Basically, the second server - the one that was restored from a saved image - does not seem to rely on indexes as it should.

Is there something related to image restore that would cause this behavior?  If so, what may need to be done to fix the issue?  Should I look somewhere else?

Thanks very much!  I can provide more information if needed, so let me know if that would be useful.

I work for Rackspace and would like to help. Can you please post some details on exactly how the image was created? Are these both listed in your control panel as "next-gen servers" or is one "first-gen"? Differences here may explain some of the performance discrepancies you're experiencing.

It would be extremely helpful if you could provide query plans from both servers (http://www.postgresql.org/docs/current/static/performance-tips.html#using-explain).

A tool like explain.depesz (http://explain.depesz.com/help) may also help you to identify whether the queries are performing the same way on both machines.

Feel free to post your answers here for the community to help (if you are comfortable) - or drop me a line at shaggy@rackspace.com with details and I'll be happy to coordinate with your support team.


How to prevent concurrency issues in a ssis package? Have a SQL Server 2017 Server.

Have several SQL Server jobs that call a single SSIS package using the SYNCHRONIZED flag.

In the package there is a data flow that runs a stored proc (please see below) that returns a Data Source that checks to see if there are any labels to be printed.  If there are any labels to be printed then it selects those labels into a table variable, selects the labels, and updates the labels IsProcessed flag to say that they have been processed.  The problem is that if more than one job calls the package then it prints out more than one label even thought there is only one label to be processed.  This situation does not consistently occur.  So, my question is:  How do I prevent this from happening?

CREATE PROC [dbo].[spCreateOutput_LabelDataSet]

AS

BEGIN
    BEGIN TRY
        BEGIN TRAN

        DECLARE @Intermediate TABLE(
            PrintLabelsID INT NULL,
            Labels XML NULL
        )

        INSERT INTO @Intermediate(PrintLabelsID, Labels)
        SELECT
            pl.PrintLabelsID,
            pl.Label
        FROM
            Staging.dbo.PrintLabels pl
        WHERE
            pl.IsProcessed = 0

        SELECT
            CAST(Labels AS VARCHAR(MAX)) 'Labels'
        FROM
            @Intermediate i

        UPDATE pl
        SET IsProcessed = 1,
            ProcessedDate = GETDATE()
        FROM
            Staging.dbo.PrintLabels pl
            INNER JOIN @Intermediate i
                ON i.PrintLabelsID = pl.PrintLabelsID

        COMMIT TRAN
    END TRY
    -- Error Logging
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION;
                DECLARE @ErrorNumber INT = ERROR_NUMBER();
                DECLARE @ErrorLine INT = ERROR_LINE();
                DECLARE @ErrorMessage NVARCHAR(4000) = LEFT(ERROR_MESSAGE(), 4000);
                DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
                DECLARE @ErrorState INT = ERROR_STATE();
                DECLARE @Error_Procedure VARCHAR(200) = LEFT(ERROR_PROCEDURE(), 200);

                EXEC Logs.dbo.spTrackErrors @ErrorNumber, @ErrorSeverity, @ErrorState, @Error_Procedure, @ErrorLine, @ErrorMessage;
                THROW;
            END;
    END CATCH

END

Since it seems that any concurrent execution of this package is going to cause this problem for you, I'd recommend putting controls into the package that prevent itself from running while another job is running it.

As far as I know, there is no mechanism within SSIS to set a package as 'one execute at a time only', so you would need to have a solution to this implemented in your SQL database. Create a table that records each instance of the package being executed (this is useful to do anyway for auditing/debugging purposes) and record the start/end times of package execution, and maybe a column for status. If there is a job still running, the end time will be null, so your package should check for this and quit if it exists.

The risk with this approach is that a fatal error might cause an unfinished row to remain  in the table, blocking any further execution. You should put something into your package to update it under all error conditions, and perhaps some logic to finish a row for a job if it has gone on too long (figuring out this duration is a business question)


Unresolvable Ambiguity problem on Join Tables I have searched for all answers, but nothing works. Somehow this query gives me the ambiguity error or 'ID':

SELECT wpp.ID AS theid, s.ID AS supplierid, post_title, wp_terms.name AS category
FROM wp_posts AS wpp
LEFT JOIN wp_term_relationships ON wpp.ID = object_id
LEFT JOIN wp_terms ON term_taxonomy_id = term_id
LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE post_type = 'post'
AND (post_title LIKE '%bag%'
OR wp_terms.name LIKE '%bag%'
OR post_content LIKE '%bag%')
GROUP BY ID

the problem lies when I added this line:

LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID

But I don't see what I'm doing wrong... Any hints?

GROUP BY ID. That's where the problem is. MySQL can't guess which table this column ID belongs to. There is an ID column in both the added wp_teleapo_supplier table and in one of the 3 others (I guess the wp_posts one).

All your column references should be prefixed with the table name (or alias): (object_id, term_id, post_type, post_title, post_content, ID). The only reason that these other references did not produce this error is because you were lucky and they only appeared in a single table, from the 4 tables in the FROM clause.

(some of the prefixes may be wrong. But if they are, you'll get an error and you can correct them:)

SELECT wpp.ID AS theid, s.ID AS supplierid, wpp.post_title, t.name AS category
FROM wp_posts AS wpp
  LEFT JOIN wp_term_relationships AS r ON wpp.ID = r.object_id
  LEFT JOIN wp_terms AS t ON term_taxonomy_id = t.term_id
  LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE wpp.post_type = 'post'
  AND (wpp.post_title LIKE '%bag%'
    OR t.name LIKE '%bag%'
    OR wpp.post_content LIKE '%bag%')
GROUP BY wpp.ID ;


Transaction Log file .ldf is too small Current database is almost 200GB however the transaction log file is only 4.1GB.
I am worried if this influences the performance of databse and how could it be solved.

When I do this query

DECLARE @DatabaseName VARCHAR(50) ;
  SET @DatabaseName = 'dbname'
  SELECT name ,
    recovery_model_desc ,
    log_reuse_wait_desc
  FROM  sys.databases
  WHERE name = @DatabaseName

Show Recovery Model = Simple, Log Reuse Wait = Nothing.

Anyone has a knowledge why the log file is so small, and does this influences on the performance?

The file size of the transaction log is in no way related to the size of the data files, so the question you've asked is really a non-starter to troubleshoot performance.

The transaction log stores the physical operations that happen to the database. Since the database is in SIMPLE recovery, the log only holds on to the operations until a CHECKPOINT occurs -- at this time, the transaction log that's no longer required is internally cleared and can be reused. The physical file size would not need to change unless the active transactions take up more space than is physically available. (Yes, I've simplified things a bit in this paragraph.)

That said, your transaction log could suffer from performance issues if there are too many VLFs, likely from the result of small autogrowth increments -- see my answer here for a bit of information about how the transaction log works internally with regards to this. It also depends what the workload is for this database (many small transactions, or few large transactions).


Put in the wrong data type for pk, cannot change because it is FK for another table I'm trying to change the PK Department.DeptId to char(8), however it is an FK in employee. Doing two alters in 1 query throws an error for the first statement. Changing it in the edit window does the same. Any idea how to change it in both places so that it does not throw an error?

You first need to drop the foreign key constraint between the two tables. After that you can alter the column the employee table. You may need to drop the primary key constraint as well as any other indexes on the Department table that has DeptId in it before you update the column, but you can try altering the column before you drop the indexes/pk. Once you have the column altered you can reapply the primary key constraint and the other indexes you removed from the Department table as well as the foreign key constraint between the Department table and the Employee table.

alter table employee drop constraint <put constraint name here>

If you use SMSS to generate the drop and create scripts this will save you time and ensure that you keep the names as well as the columns that are used in the index

Drop index <index name here> on Department

You will need to repeat the above for each index

alter table Department drop constraint <put primary key name here>

alter table Department alter column DeptId char(8) not null

alter table Department add constraint [your pk name] primary key(DeptId)

alter table employee add constraint [foreign key name] foreign key DeptId references Department (DeptId)

Run the create portion of the index scripts that you created


CentOS 7, PostgreSQL failed to read permission data directory in mounted partition I've a problem with start PostgreSQL 9.4 on Centos 7 Server. Because the partition /var/lib/pgsql/9.4/data/ (which contains PostgreSQL data) is full so I want to change to another partition.

/dev/sdb1               154685904  322500 146482752   1% /run/media/root/Data

I've used this command to copy all the old data to new data folder:

cp -a /var/lib/pgsql/9.4/data/ /run/media/root/Data/pgsql_data

Then I changed the directory to new data directory in postgresql.conf (/var/lib/pgsql/9.4/data)

data_directory = '/run/media/root/Data/pgsql_data/'     # use data in another directory

And then I try to restart PostgreSQL, here are errors:

postgresql-9.4.service - PostgreSQL 9.4 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled)
Active: failed (Result: exit-code) since Wed 2015-03-11 09:17:21 ICT; 4s ago
Process: 54975 ExecStop=/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
Process: 56133 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=1/FAILURE)
Process: 56127 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 54580 (code=exited, status=0/SUCCESS)

Mar 11 09:17:16 localhost.localdomain pg_ctl[56133]: LOG:  skipping missing configuration file "/run/media/root/Data/pgsql_data/postgresql.auto.conf"
Mar 11 09:17:16 localhost.localdomain pg_ctl[56133]: < 2015-03-10 22:17:16.187 EDT FATAL:  could not read permissions of directory "/run/media/root/Data/pgsql_data": Permission denied
Mar 11 09:17:21 localhost.localdomain pg_ctl[56133]: pg_ctl: could not start server
Mar 11 09:17:21 localhost.localdomain pg_ctl[56133]: Examine the log output.
Mar 11 09:17:21 localhost.localdomain systemd[1]: postgresql-9.4.service: control process exited, code=exited status=1
Mar 11 09:17:21 localhost.localdomain systemd[1]: Failed to start PostgreSQL 9.4 database server.
Mar 11 09:17:21 localhost.localdomain systemd[1]: Unit postgresql-9.4.service entered failed state.

Please help!

You are obviously missing some permissions in the new location.

I have tried to reproduce your error on my local test cluster.  I could get several different errors along the way but never the one you got.  However, here are the permissions you need on your directories to run the cluster:

the data_directory (in your case, pgsql_data) must be owned by postgres (the group seems making no difference)
it must have 0700 as permissions (no group and world level permissions are allowed)
all directories under this need 07XX, where XX refers to the group and world permissions - I haven't found any restrictions to these (but obviously didn't try all possibilities)

Based on my experiments, these should be enough for starting PostgreSQL.


Unable to store query result inside a variable I am trying to query user by email and save the result inside a variable. I tried using await and async, callback and promise but none of them actually work.
const checkEmailUsed = (email) => {
  let sql = "SELECT * FROM users_signup WHERE user_email = ? LIMIT 1";
  return new Promise((resolve, reject) => {
    conn.query(sql, email, (err, res) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(res[0]);
    });
    conn.end();
  });
};

var user = [];
checkEmailUsed("mikias@email.com")
  .then((res) => user.push(res))
  .catch((err) => console.log(err));

console.log(user); // Still []

The console.log is executed before the promise is fulfilled. This should work:
const checkEmailUsed = (email) => {
  let sql = "SELECT * FROM users_signup WHERE user_email = ? LIMIT 1";
  return new Promise((resolve, reject) => {
    conn.query(sql, email, (err, res) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(res[0]);
    });
    conn.end();
  });
};

var user = [];
checkEmailUsed("mikias@email.com")
  .then((res) => user.push(res))
  .then(() => console.log(user))
  .catch((err) => console.log(err));


How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file? SQL Server 2019 introduced a new (and undocumented) system stored procedure:

sys.xp_delete_files

What, exactly, does this new extended stored procedure do? How does it differ from xp_delete_file that has been around for years? Does it replace xp_delete_file?

To start with, the older xp_delete_file (also undocumented) is quite limited:

It can only delete either backup (.bak / .trn) or report (??) files (I believe it actually scans the first part of them to verify the file "type"
It only deletes by file extension (i.e. not specific files, or whatever matches based on wildcards) and based on being older than the supplied date
It's picky and requires a trailing slash on the directory / folder
It can only delete files, not folders
It can recurse down through subfolders (ok, so this one is not a limitation)

For more info on it, please see:

How to Use xp_delete_file to Purge Old Backup Files
SQL Server xp_delete_file not deleting files

The newer sys.xp_delete_files has the following syntax:
EXEC sys.xp_delete_files 'fileSpec.01' [, 'fileSpec.02' [, ...] ] ;

Notes:

File type / extension does not matter
Works with standard DOS wildcard characters:

* = zero or more of any character
? = exactly one of any character

Does NOT recurse through subdirectories (for deleting files) (this is the only "limitation")
Can specify multiple, fully-qualified path specifications, each being able to handle wildcards
Can remove entire non-empty subfolder structure!! (might need to run a few times to delete everything)
Must be a member of the sysadmin fixed Server Role in order to execute (use Module Signing instead of adding application Login to sysadmin fixed Server Role; please see: Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level )
Backported to SQL Server 2017 (possibly in CU18)

For complete details, please see my post:
sys.xp_delete_files and ‘allow filesystem enumeration’: two new undocumented items in SQL Server 2019


Solutions to 4-byte utf-8 characters in mysql 5.1 I'm running into a problem that seems to be an issue w/ mysql 5.1s limitations on UTF8 characters.

From http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-utf8.html

Currently, MySQL support for UTF-8 does not include 4-byte sequences.

Our app is throwing periodic exceptions like

java.sql.SQLException: Incorrect string value: '\xF4\x80\x82\x8C  ...'

The ultimate solution looks to be migrating to 5.5 which supports UTF-8 > 3 bytes

Unfortunately this wouldn't be as simple as bouncing the instances under newer binaries.  Being a major version dump we'd need to do a full dump reload which will require some scheduled downtime.

Has anyone else had to deal this situation before?  Are there any good work arounds?

The naive approach seems to be have the app search and replace multi-byte sequences with question marks or � .  This seems pretty hacky and not a very palatable option to me or the developers.

Opinion

It's probably better to throw an exception during your applications input checking and not pass the buck to the database.

Workaround

There is a "workaround" but your mileage may vary:
http://forge.mysql.com/worklog/task.php?id=3780

Brute Force?

You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the current problem. ...and of course that invites a host of other problems by using BLOBs instead of VARCHAR.

Upgrading

UTF32 may solve the problem and upgrading to 5.5.x isn't has hard as you'd think. Create a replicated slave (farm), sync up and promote it to master.


"Warnings: Operation caused residual I/O" versus key lookups I've seen this warning in SQL Server 2017 execution plans:

  Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.

Here is a snippet from SQLSentry PlanExplorer:

In order to improve the code, I've added a non-clustered index, so SQL Server can get to the relevant rows. It works fine, but normally there would be too many (big) columns to include in the index. It looks like this:

If I only add the index, without include columns, it looks like this, if I force the use of the index:

Obviously, SQL Server thinks the key lookup is much more expensive than residual I/O. I have a test setup without much test data (yet), but when the code goes into production, it needs to work with much more data, so I'm fairly sure that some sort of NonClustered index is needed.

Are key lookups really that expensive, when you run on SSDs, that I have to create full-fat indexes (with a lot of include columns)?

Execution plan: https://www.brentozar.com/pastetheplan/?id=SJtiRte2X It is part of a long stored procedure. Look for IX_BatchNo_DeviceNo_CreatedUTC.

The cost model used by the optimizer is exactly that: a model. It produces generally good results over a wide range of workloads, on a wide range of database designs, on a wide range of hardware.

You should generally not assume that individual cost estimates will strongly correlate with runtime performance on a particular hardware configuration. The point of costing is to allow the optimizer to make an educated choice between candidate physical alternatives for the same logical operation.

When you really get into the details, a skilled database professional (with the time to spare on tuning an important query) can often do better. To that extent, you can think of the optimizer's plan selection as a good starting point. In most cases, that starting point will also be the ending point, since the solution found is good enough.

In my experience (and opinion) the SQL Server query optimizer costs lookups higher than I would prefer. This is largely a hangover from the days when random physical I/O was much more expensive compared to sequential access than is often the case today.

Still, lookups can be expensive even on SSDs, or ultimately even when reading exclusively from memory. Traversing b-tree structures is not for free. Obviously the cost mounts as you do more of them.

Included columns are great for read-heavy OLTP workloads, where the trade-off between index space usage and update cost versus runtime read performance makes sense. There is also a trade-off to consider around plan stability. A fully covering index avoids the question of when exactly the optimizer's cost model might transition from one alternative to the other.

Only you can decide if the trade-offs are worth it in your case. Test both alternatives on a representative data sample, and make an informed choice.

In a question comment you added:

  Are you telling me that SQL Server does not know the cost of the residual IO?

No, the optimizer does consider the cost of residual I/O. Indeed, as far as the optimizer is concerned, non-SARGable predicates are evaluated in a separate Filter. This filter is pushed into the seek or scan as a residual during post-optimization rewrites.


How to version control PostgreSQL schema with comments? I version control most of my work with Git: code, documentation, system configuration.
I am able to do that because all my valuable work is stored as text files.

I have also been writing and dealing with lot of SQL schema for our Postgres database. The schema includes views, SQL functions, and we will be writing Postgres functions in R programing language (via PL/R).

I was trying to copy and past the chunks schema that I and my collaborators write but I forget to do that. The copy and past action is repetitive and error prone.

The pg_dump / pg_restore method will not work because it looses comments.

Ideally I would like to have some way to extract my current schema into a file or files and preserve the comments so that I can do version control.

What is the best practice to version control schema with comments?

Why don't you COMMENT ON the various SCHEMA components, that way your comments are in the schema, and will get dumped.

COMMENT stores a comment about a database object.

To modify a comment, issue a new COMMENT command for the same object. Only one comment string is stored for each object. To remove a comment, write NULL in place of the text string. Comments are automatically dropped when the object is dropped.


Is there a routine in MySQL similar to fn_dblog in SQL-server? I already search about fn_dblog and it is about transaction log and it is used in SQL SERVER. if there any way to do transaction log in MySQL like fn_dblog? i can't find one

Yes, you can use mysqlbinlog. Here are a few tutorials:

15 mysqlbinlog Command Examples for MySQL Binary Log Files
Identifying useful info from MySQL row-based binary logs
mysqlbinlog documentation for your version (going by the mysql-5.6 tag you used)


SQL Server Security Hierarchy I have been working as a developer with SQL Server for years without asking the question, "what is dbo", a shame. Recently I have been studying the security model of SQL Server 2008. I have been overwhelmed by lots of concepts. Please clarify some concepts for me, and correct me where I am wrong. I don't need a thorough explanation on everything, just an idea what does what and the relationships involved:

LOGIN:  Server level (SQL Server login or Windows login).
USER:   Database level (a user is mapped to a login).
ROLE:   Users belong to a role. Can be at server level or at database level
        with permissions attached.
SCHEMA: Database objects belong to a schema (or dbo by default)
PERMISSION:
        For database objects or schema. A user or a role has permissions for
        an object or a schema.
OWNER:  Is it the user who created a object or schema? What does it do?
        How does it work? Where I can see the owner of a object in SSMS?
        I see only schema.object

An owner is a database principal that has ownership of a particular object, giving that principal full rights to the object(including ALTER, SELECT, INSERT, UPDATE, and DELETE).  This principal can either be a database user or schema, depending on the context in which the object was created and the permissions of the principal that created the object.  In the case of no explicit object ownership, the object is owned by the related schema owner.

There is also the db_owner role which exists in each database.  Members of this role are considered to have ownership of the database and all objects within that database.  Essentially, members of the db_owner role have "sysadmin/sa" rights in that specific database.

'dbo' is a default database user that is made a member of the db_owner role.  Additionally, the 'dbo' schema is also created and used as default schema to create objects within.  This schema is owned by the 'dbo' user.

To view object ownership, you can use the following query against the DMVs:

select
    SCHEMA_NAME(s.schema_id)+'.'+OBJECT_name(o.object_id) [ObjectName]
    ,u.name [ObjectOwner]
    ,so.name [SchemaOwner]
from
    sys.objects o
    left join sys.schemas s on (o.schema_id = s.schema_id)
    left join sys.database_principals so on (s.principal_id = so.principal_id)
    left join sys.database_principals u on (o.principal_id = u.principal_id)

For more detail, you can read Aaron's link or this article from Kathi Kellenberger.


Index Reorganize/ Rebuild Time I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. I used this approach to improve performance and it worked perfectly for a long time. However, recently this approach has started to take too much time.

So, instead of using the above, I execute index rebuild. It completed with one-six of the time taken in the first approach.

Why did the first approach become slow over time, and why is the full index rebuild better?

EXECUTE dbo.IndexOptimize @Databases = 'MyDBname'
    , @FragmentationLow = NULL
    , @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
    , @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
    , @FragmentationLevel1 = 5
    , @FragmentationLevel2 = 30
    , @UpdateStatistics = 'ALL'
    , @OnlyModifiedStatistics = 'Y'

You don't state the steps you take in each case so it is difficult to give specific analysis but three generic explanations for some of the difference spring to mind:

Rebuilding is an offline operation unless you are using Enterprise Edition, so it takes a table lock and does all the work before letting any other processes touch it. This means the process can optimise for itself and not care about concurrency or otherwise dealing with changes that happen during the process.
Reorganise is an online operation so it needs to be concerned with possible concurrent actions: optimising for concurrency often means doing more work than you otherwise would and if there is any concurrent activity it will contend with the process for IO bandwidth & throughput.
Rebuilding just throws away the existing index and rebuilds from the table data, reorganise is much more "clever" and moves & compacts existing pages which depending on the state of the page distribution, your I/O subsystem, how much of the index's pages are in RAM right now, and how much of the cluster/heap is in RAM, how fragmented the cluster/heap is on disk, if another index can be used to aid the rebuild, and a number of other factors, could make reorganise slower.
If you are not checking first (and just rebuilding) then you are skipping a chunk of the work that the first job does: you just rebuild instead of scan then rebuild or reorganise.

https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/ has a few other notes that you might find useful in understanding the difference more.

For an answer more specific to your particular case you'll need to update your question with more details of the maintenance plans that you are comparing.


Horizontal schema vs Vertical schema I am but a modest SQL DBA. I would like to request your advice on the design of the central part of our new proposed database.

Brent Ozar had something to say about this:
Building A SQL table horizontally VS vertically.
Please can either Brent or someone with a good deal of experience give me their feedback or experience in this matter?

Some details about the central part of our database:

Will be replicated using Transactional Replication.
Is CDC enabled.
Is fact based (not transactional based) i.e. like the concept of Products.
We want to be able to add new items easily as and when the requirement arises. If we go with the horizontal approach, then there will always be the requirement to add either additional tables or columns to existing tables.
We could partition all of our Entity Attribute Value tables based on a certain criteria to reduce the workload on the accessing of data?

I have worked with a Metadata based system for only one part of the (ever changing) system. The advantage of this was such that the configuration of data and workflows did not require extra development every time we needed to configure certain details required. Also, I had ensured that the data was easily accessible through clever use of indexes and partitions.

I really appreciate and respect any advice you have to offer regarding this issue (and regarding any misconceptions I might have).

Your choice does not have to either/or - you can have both.

At a "physical level" is generally better to use horizontal designs for performance, maintenance and reliability reasons. But the greater rule is: "Don't mix data and metadata". There is nothing intrinsically wrong with building an application that knows how to do CREATE TABLE and ALTER TABLE Foo ADD Col1 by reading metadata table and then allow the app itself to modify the data model. That way, you get the best of both worlds. Think of adding tables and columns the same way you think of DML. Of course, if you do this, you need to make sure you reconfigure the replication when the schema is changed.

Note that the above design guidance doesn't rule out having tables containing the metadata describing what the other tables do (so the app can configure itself to work with it). When building a data warehouse, this is typically an excellent idea. The metadata can even be used to generate and compile new application code.


Replication fails because of a non-existent foreign key I have been trying to get a previously working replication to start working.

I have tried many settings, but I can't seem to make it work.

Here is the scenario.  I dropped all the FK constraints from the destination database (call it DestDB).

I then re-initalize my replication and when I Runs I get this (de-identified) error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  "FK_TableOnlyInDestDB_MyReplicatedTable". The conflict occurred in database
  "DestDB", table "SomeSchema.MyReplicatedTable",
  column 'MyReplicatedTableId'. (Source: MSSQLServer, Error number: 547)

It is driving me nuts because the FK indicated does not exist in that database.

Now, I did copy this database (backup and restore) from another database.  So the only thing I can think of is that it is somehow crossing the streams.

But that seems unlikely.

Any ideas would be great!

NOTE: This is the query I ran to ensure that I have no FKs (It returned no rows):

use DestDB

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

I also checked to ensure that the FK in question was not on the source table (in the database I am replicating from).

I have also tried: EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Also tried Dropping MyReplicatedTable.  Dropped just fine and replication re-created it.  (Still gave me the error though.)

I tried creating an empty database as a subscriber.  That worked fine, so it is something in the DestDB that is causing the issue, but the FK in the ERROR does not exist.

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive.
Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my problem. Of course, there may be more "best practice" ways of cleaning up after failed replications.


SQL AlwaysON. keeping Agent Job in sync, but with same JobID? Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists?

I have a software from a vendor that uses the Job ID to reference jobs instead of using the Job Name. As far as I know (and in my testing) - when a job is created the Job ID is random. The vendor has a solution to rerun their Job Creation Tool when you failover. Is there a way to skip this step and make the Job ID the same between AG replicas?

I'm fairly certain this is a Very Bad Idea™, but... yes.

Using the UI as normal, go ahead and create the job you want. Before you hit Okay in the bottom right, stop; instead click  Script in the top left hand corner of the dialogue window.

One of the batches that you will see in the resulting session will have sp_add_job, one of the parameters of which will be @job_id = @jobId OUTPUT (depending on your SQL Server version & SSMS user scripting preferences).

  Awesome! Problem solved! You can just manually supply @job_id, super duper!

Actually...

  Hey, jerk! I got an error!

  Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

Yea, if you take a look at ~line 130 of object_definition(object_id('sp_add_job')), you will see something like...

IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
BEGIN
  RAISERROR(14274, -1, -1)
  RETURN(1) -- Failure
END

If only there were some way to pretend to be the SQL Agent...

Or if you're feeling less fancy, you can always...

delete dbo.sysjobactivity
where job_id = '11111111-1111-1111-1111-111111111111';

update dbo.sysjobs set
    job_id = '22222222-2222-2222-2222-222222222222'
where job_id = '11111111-1111-1111-1111-111111111111';

...since sysjobs is a user table.

Obviously... it seems like the MS devs who wrote this may have been trying to protect you from yourself... So... maybe keep an eye out for weird, exotic breakage if you try this approach.


SQL Server Service Accounts We have a new Windows 2016 server and I have to install SQL Server 2016 standard edition. Once I install and configure the SQL server I will move my existing production database to the new db server.

I have remote desktop access to the server to install the SQL server, but I am not sure if I need to have domain service accounts? I was of the impression that the default services SQL server assigns during the installation is enough.

Question 1:

The DB Admin team will remote desktop to that server and connect to SQL server using SSMS,not from their local SSMS. In that case do I have to have domain service accounts?

Question 2:

The application which DB support will have few hundred users connecting over network? In that case the default service account will be enough or do I have to have domain service accounts?

I am new to the database administration world, so I am still struggling with few concepts.

To first address your questions:

  The DB Admin team will remote desktop to that server and connect to
  SQL server using SSMS,not from their local SSMS. In that case do I
  have to have domain service accounts?

While I would not advise that a production database is administrated by running SSMS directly on the production server, these two items are completely unrelated.

  The application which DB support will have few hundred users
  connecting over network? In that case the default service account will
  be enough or do I have to have domain service accounts?

Again, these two items are completely unrelated.

You should check out Guidelines on choosing Service Accounts for SQL Server Services. The key parts of that, for you, are:

  During a new installation, SQL Server setup does not default SQL
  Server engine Service and SQL Server Agent service to any account. The
  account specification is required step for these services. For details
  on recommended secure accounts, refer to Books Online Topic Setting Up
  Windows Service Accounts.

and

  When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported. For more details please refer to Books Online Topic Setting Up Windows Service Accounts.

So based on that, you will need to specific accounts for the engine and agent, and I would recommend having discrete logins for all services which adhere to the principle of least privilege as per the linked articles from the quotes.


Mapping between each bit in a bitmap index and tuple-pointers Bitmap Indexes are described here, but I don't understand some part of it.

  A plain indexscan fetches one tuple-pointer at a time from the index,
  and immediately visits that tuple in the table.  A bitmap scan fetches
  all the tuple-pointers from the index in one go, sorts them using an
  in-memory "bitmap" data structure, and then visits the table tuples in
  physical tuple-location order.  The bitmap scan improves locality of
  reference to the table at the cost of more bookkeeping overhead to
  manage the "bitmap" data structure --- and at the cost that the data
  is no longer retrieved in index order, which doesn't matter for your
  query but would matter if you said ORDER BY.

To visit the table tuples, you need to know the tuple-pointers. Then there must be a mapping between each bit in a bitmap and tuple-pointers. How is this mapping maintained?

I also don't understand what he meant by "sorts them using an in-memory "bitmap" data structure".

First of all, there is no bitmap index type. The Bitmap Index Scan is just a fast mechanism to look up and sort values from a bitmap.

The Backend Process scans the Index for all tuples, and stores them in a structure called TIDBitmap. The pointers to the tuples are sorted so they could be accessed sequential on disk.

The source of the Bitmap structure could be found in src/backend/nodes/tidbitmap.c


Access is denied for SQLDumper.exe in MS SQL Server I want to run SQLDumper.exe with process ID 6392 which is the ID of my Express edition. While running I get this error from the OS

access is denied

I've given full control to the SQL Express account name but still get the error. What access should I grant in order to make it work properly?
Thanks for your time!

Try running CMD using administrative Privileges.

Start->CMD->right Click on it and click run as administrator


Using views with no insert table VS Audit tables I(not a DBA) am considering designing Audit tables for an application that needs to be really fast as well fully audited (undo is not necessary). I referred to some from so and I found variations of the below

Create an insert only table with _to and _from and create a
view from these.
Create an Insert only table with just last_updated.
Create a separate Audit table with both old and new values
Create a separate Audit header table and Audit Detail table

I am choosing the following option and reasoning:

3 . Separate table but a single per table audit table - and just have created_by, created_date, updated_by and updated_date columns

because I think insert only table would create too many records and might slow down quickly considering it will be a hotspot data for my application.
Views will still be slower since they are just stored queries?
Separate Audit table would help be separate functionality and feels right.
Separate Header/Details is too much of an overhead

My question is considering the characteristics of application please provide if this makes sense.

what database is this, make sure to check your DB if it has some inbuilt native solution.
But still if you want to develop Audit part do consider

Insert and Select are difference and independent in relational databases.
Try to trigger only the important columns you want to observer / monitor.
Make sure you have a purge algorithm ready for stale data to keep the tables size in check.
Make sure you properly partition the tables so that the selects are always efficient.


Why are bulk multi-column key queries so slow in MySQL? (For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance)

Consider the following schema:

Table T:
    col0: the usual autoincrement primary key
    col1: varchar
    col2: varchar
    col3: varchar
    col4...N: various data

Consider that there is a unique index on:

<col1, col2, col3>

And a non-unique index on:

<col1, col2>

And consider the following query:

SELECT * FROM T
WHERE
    (col1 = 'val1' AND col2 = 'id1') OR
    (col1 = 'val2' AND col2 = 'id2') OR
    ...
    (col1 = 'valN' AND col2 = 'idN');

I would (perhaps naively) expected MySQL to figure out that each element of the OR set matched the (non-unique) index, and performed the query in the way it would have if I had said:

WHERE col0 in (v1, v2, ... , vN)

But it doesn't seem to do that: the timing for these two queries is WAY OFF, on the order of 10x slower for the "or of ands" query. EVEN WITH the secondary key lookup, and the fact that it's a string column lookup, 10x seems a bit severe. Note that EXPLAIN claims to be using the correct/expected index whether I specify (col1, col2) or (col1, col2, col3)

Please note also that:

SELECT * from T
WHERE
    col1 in (list1)
AND
    col2 in (list2);

Is also slow when there are a lot of different values in list1 and list2. Doing an "and" for the three columns is almost intractably slow.

Perhaps not surprisingly, this query works better than the "or of ands" when list1 is of length 1.

With "row constructors", you might get an optimization:

WHERE (col1, col2) IN (('v1', 'id1'), ('v2', 'id2'), ...)

But...  In old versions, that would work, but lead to a table scan.  I can't say specifically about the version you are running.

When you have this pair of indexes:

UNIQUE(col1, col2, col3)  -- (or plain INDEX)
INDEX(col1, col2)

there is no need for the latter, since the former can handle any queries that need it.

Perhaps the optimal way to write your query is

WHERE col1 in ('v1', 'v2', ...)
  AND (col1, col2) IN (('v1', 'id1'), ('v2', 'id2'), ...)

With that, it will use any index starting with col1 as a crude filter, then use the other part for the rest of the filtering.

Re "convert to an in method" --  MySQL started out as a clean and mean database; it did most of what anyone needed and did it reasonably well.  That was 90% of the development.  We are now into the other 90% of the development -- the "long tail".  Quite possibly some list somewhere includes "convert to an in method".  If so, it is being prioritized along with the thousands of other rare and obscure optimizations.  Feel free to file a 'feature request' at bugs.mysql.com; that is the way to add it to the list, or bump it up in priority.


Stored procedure executes too long We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management Studio takes 2 seconds. SQL Server is under load when I catch those queries with
memory and CPU at the 20 percent.

What I can do?

Take care when comparing stored procedure execution times to inline SQL execution times.

A stored procedure gets compiled into ONE execution plan to fit all possible parameters.  Depending on your query this may not be a good plan for Any parameters, but instead the least-worst for all parameters.

When executing Inline, the optimiser can see both the query AND the parameters.  It can then compile a plan specifically for that case.  It can take conditions such as OR @param IS NULL and turn it into OR TRUE and then be able to pick indexes and approaches that fit that specifically.

In short, Inline and stored procedures can and do generate different plans with different performance characteristics.

For your particular case, can you show us the definition of the stored procedure?


MySQL Triggers Different Database Different Server I have two databases, each on a separate server. Each database has a number of similar but not identical tables. I need to combine the records from these similar tables into a new database, which is on one of the two servers.

SERVER A - Database 1

SERVER B - Database 2, Database 3 (new)

My thinking (as a relative novice re MySQL) is to first Insert existing records into the tables of the combined database, then use Insert|Update|Delete Triggers to maintain those records in the new db.

This would allow me to start with one roughly matching table from each of the two original databases, and gradually go through all the rest of the tables.

Is it possible to create Triggers on Database 1, which updates a table in Database 3 (new), which resides on a different server?

Any suggestions would be most welcome on how to accomplish this.

There's the FEDERATED storage engine, which makes for a way to create a "proxy" table in your database server, which points to a real table on another server.

FEDERATED is disabled by default. To enable it, add this to your my.cnf configuration file, under the [mysqld] section:

federated = 1

So your trigger on some table T would then INSERT into your FEDERATED table FT, which would connect to the remote server, pass credentials, and perform the operation.

Do note the overhead: triggers are by themselves performance killers. Add to that the connection to the remote machine, and you now also have latency to worry about.


Make a boolean column unique per group I want to ensure consistency and make sure only one phone line is the default for a house.

Schema: PhoneLine
ID | isDefault | HouseID

I want to add a trigger so that when you add/update a row, and set isDefault to True, all other rows in same table with the same HouseID set isDefault to False.

I can't figure out a way to do this without creating a recursive mess.

What's the best way of approaching it?

I tried:

CREATE OR REPLACE FUNCTION unsetCurrentDefault()
RETURNS trigger AS $unsetCurrentDefault$
    BEGIN
        IF NEW.isdefault AND NEW.houseid IS NOT NULL THEN
            -- Current ones get reset.
            UPDATE phoneline SET isdefault = false
            WHERE houseid = NEW.houseid AND id <> NEW.id AND isdefault;
        END IF;
        RETURN NEW;
    END;
$unsetCurrentDefault$ LANGUAGE plpgsql;

CREATE TRIGGER updateDefaultValue AFTER INSERT OR UPDATE ON PhoneLine
    FOR EACH ROW EXECUTE PROCEDURE unsetCurrentDefault();

But the behaviour seems recursive.

The superior implementation is to save the default choice with the row in the parent table. Then a new default replaces the old one automatically. No trigger required, no chance to break referential integrity
Data model based on fragments in the question:
CREATE TABLE house (
   house_id         serial PRIMARY KEY
 , default_phone_id int
 -- , other columns
);

CREATE TABLE phoneline (
   phone_id serial PRIMARY KEY
 , house_id int  NOT NULL REFERENCES house
 , number   text NOT NULL
 , UNIQUE (house_id, phone_id)  -- seems redundant, but needed for FK below
);

ALTER TABLE house ADD CONSTRAINT house_default_phone_id_fkey
FOREIGN KEY (house_id, default_phone_id) REFERENCES phoneline(house_id, phone_id);

The circular FK references may seem like a problem. But, really, they are not.
I made the FK constraint house_default_phone_id_fkey include house_id, so that setting the default to a phone number of a different house is impossible. The FK requires a UNIQUE (house_id, phone_id) in phoneline, which seems a bit redundant since phone_id is the PK. But you typically need an index with house_id as leading column for performance anyway.
Since the default_phone_id can be NULL, you can choose the default later, after inserting one or more phone numbers. But it even works with the column set to NOT NULL; just use data-modifying CTEs to insert & update in a single statement. Reference implementation:

How to deal with mutually recursive inserts

Related:

Constraint to enforce "at least one" or "exactly one" in a database
Enforcing constraints "two tables away"


Why is this stream aggregate necessary? Check out this query.  It's pretty simple (see the end of the post for table and index definitions, and a repro script):

SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1 AND 1 = (SELECT 1);

  Note: the "AND 1 = (SELECT 1) is just to keep this query from being auto-parameterized, which I felt like was confusing the issue - it actually gets the same plan with or without that clause though

And here's the plan (paste the plan link):

Since there is a "top 1" there, I was surprised to see the stream aggregate operator.  It doesn't seem necessary to me, since there is guaranteed to only be one row.

To test that theory, I tried out this logically equivalent query:

SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1
GROUP BY Id;

Here's the plan for that one (paste the plan link):

Sure enough, the group by plan is able to get by without the stream aggregate operator.

Notice that both queries read "backwards" from the end of the index and do a "top 1" to get the max revision.

What am I missing here?  Is the stream aggregate actually doing work in the first query, or should it be able to be eliminated (and it's just a limitation of the optimizer that it's not)?

By the way, I realize this is not an incredibly practical problem (both queries report 0 ms of CPU and elapsed time), I'm just curious about the internals / behavior being exhibited here.

Here's the setup code I ran before running the two queries above:

DROP TABLE IF EXISTS dbo.TheOneders;
GO

CREATE TABLE dbo.TheOneders
(
    Id INT NOT NULL,
    Revision SMALLINT NOT NULL,
    Something NVARCHAR(23),

    CONSTRAINT PK_TheOneders PRIMARY KEY NONCLUSTERED (Id, Revision)
);
GO

INSERT INTO dbo.TheOneders
    (Id, Revision, Something)
SELECT DISTINCT TOP 1000
    1, m.message_id, 'Do...'
FROM sys.messages m
ORDER BY m.message_id
OPTION (MAXDOP 1);

INSERT INTO dbo.TheOneders
    (Id, Revision, Something)
SELECT DISTINCT TOP 100
    2, m.message_id, 'Do that thing you do...'
FROM sys.messages m
ORDER BY m.message_id
OPTION (MAXDOP 1);
GO

You can see the role of this aggregate if no rows match the WHERE clause.

SELECT MAX(Revision)
FROM   dbo.TheOneders
WHERE  Id = 1
       AND 1 = 1 /*To avoid auto parameterisation*/
       AND Id%3 = 4  /*always false*/

In that case zero rows go into the aggregate but it still emits one as the correct semantics are to return NULL in this case.

This is a scalar aggregate as opposed to a vector one.

Your "logically equivalent" query is not equivalent. Adding GROUP BY Id would make it a vector aggregate and then the correct behaviour would be to return no rows.

See Fun with Scalar and Vector Aggregates for more about this.


Should "Teachers" and "Students" be kept in a common "Person" table? I've already read these posts:
Storing whether a user is a student or teacher? Is a lookup table necessary?
Creating a database for a scenario involving students, teachers and courses
In a school database, do I need to have repeating data when a student can also be a teacher?
All of them were close, but none are exactly what I want. A few points:

This app will be used by Grade School teachers.
A student will never be a teacher, and a teacher will never be a student.
In addition to Students and Teachers, there will also be contact info for Admins who are neither students nor teachers.
Teachers will always be Users. Admins may or may not be users. (We may store an Admin's name and contact info, but that admin may never have a reason or need to log into the app.)

My thought is to create a "Person" table:

PersonId
FirstName
LastName
Email (NULL if student)
PersonType (Enum: 1 = Admin/Teacher, 2 = Student)
Other Columns as needed

And another table "Passwords":

PasswordId
PersonId
PasswordHash
Other Columns as needed

If a Person is also a user, they will have a value for 'Email' and also a record in the Password table.
Am I on the right track?

Yes, but the primary key should also include PersonType.
Then in each sub-table (Teachers, Students etc) you can create the same primary key which is also a foreign key on Person. This represents a 1 -> 0..1 relationship. The PersonType field in this table should be constrained to a single value


Azure SQL Database Pricing: Standard vs Premium tier I was checking pricing for Standard tier and Premium tier concerning Azure SQL Database and I am a bit confused:
Knowing Standard tier has 1-4 IOPS per DTU and Premium tier has 25 IOPS per DTU (doc), why should I choose a Standard 6 (S6) plan with 400 DTU instead of a Premium 1 (P1) with 125 DTU?
S6 costs 506 euro/month, while P1 costs 392 euro/month and doing calculations:
S6: 506 euro/month | 400 DTU * 4 IOPS = 1600 IOPS
P1: 392 euro/month | 125 DTU * 25 IOPS = 3125 IOPS
Moreover P1 has other interesting features such as Columnstore indexing, In memory OLTP and 2ms IO latency.
Apparently it seems that P1 is way better than S6, even though costing less.

In the example you only refer to IO metric and forget about CPU and memory factors which are also a part of the DTU model. In the example you provided database engine with 400 DTU will give you more CPU and memory capabilities than 125 DTU.
The factor which also could be important while choosing between Premium and Standard tier is Max concurrent workers (requests). For S6 it is 800 and for P1 it is 200, so if you have a lot of concurrent queries, you would like to go with Standard which has better worker/EUR ratio.
Just to clarify, Columnstore Indexes are also available in Standard tier starting from S3 (100 DTU).
Generally speaking Premium Tiers are best suited for IO intensive workloads, while Standard tiers could be beneficial in terms of costs perspective for high concurrent systems based on intensive CPU queries.
Materials:

Service tiers in the DTU-based purchase model
Resource limits for single databases using the DTU purchasing model - Azure SQL Database
New Azure SQL Database Standard Tier Sizes


SQLite: trigger for multiple tables and/or multiple actions I am looking for a way to write SQLite trigger which will affect multiple actions and multiple tables (in fact I want it executed on a particular table each time any query is executed on any table in the database).

Here is an example, to explain what I mean.
I have 3 tables: Students, Courses, Professors.
Each student has activation_deadline field, which is set to NULL upon activation.
I want each time any query is executed, to check if there are students with expired activation_deadline and if there are -- remove them.
Currently I have three separate triggers on Students table for this.
One for UPDATE:
CREATE TRIGGER Remove_Unactivated_Students_Update
AFTER UPDATE
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

One for INSERT:
CREATE TRIGGER Remove_Unactivated_Students_Insert
BEFORE INSERT
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

And one for DELETE:
CREATE TRIGGER Remove_Unactivated_Students_Delete
AFTER DELETE
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

What I would like, is something like this:
CREATE TRIGGER Remove_Unactivated_Students
AFTER INSERT, UPDATE, DELETE
ON Students, Courses, Professors
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

Which, of course, gives me:
Error: near line 1: near ",": syntax error

Given what is said above, here are the questions:

Is it possible to write SQLite trigger for multiple tables, at once (if so how)?
Is it possible to write SQLite trigger for multiple actions (e.i. UPDATE, INSERT, DELETE), at once (if so how)?
Maybe there is a better way to achieve what I am aiming for?

No, SQLite doesn't appear to have database or server level triggers (like other database systems). Instead you'd have to create the same trigger on each table.

Unfortunately this is also a limitation in SQLite and not possible either.

You might be better off letting the app that connects to the SQLite database (or a secondary process / job depending on if your database is stored on a mobile device vs a computer) control what records get deleted and when. For example, if you were hosting your SQLite database on a Windows machine, you could schedule a Task that routinely cleans up the database. Otherwise you can build it into the framework of your application that whenever a database call is made, the clean up script executes first.

Here is the syntax tree for the CREATE TRIGGER statement on SQLite. It may help clarify what you can and can't do.


Index on Multiple Dimensions I've recently started reading the book Designing Data-Intensive Applications.
This book has revealed that I have been using indexes incorrectly, in that I didn't realize that even if I have an index on two columns on a single table, my query can only use one of those indexes!
For the past 3 years, I've been using indexes as if they are composable, when they are very much not so! (This is a limitation of the B-Tree index - I won't go into the details).
One solution to this problem is to use a concatenated index, in which we take all the columns we want to search / filter by, combine the data into one piece of data, and then search on that combined piece of data.
This likely works great in practice, but again, the indexes are not composable. If I have a concatenated index (on my persons table) on the first name and last name column, and now I want to search / filter on age as well, I have to create a new index.
Are there any general solutions to this composability problem with indexes?

Indexes can be combined, an "index intersection". (I'm from SQL Server side, hence that terminology etc.)
For example, you search for lastname = Smith and firstname = Joe, and neither is by itself very selective. So, go and find the keys for lastname and go find the keys for firstname and then join those two sets of keys to find the data you are after.


How to correlate data between 2 tables? I have 2 tables that I am dealing with, table 1 has identity information (II), columns ID and Name. Table 2 has data collection (DC) information, InfoId, DCId, data, timestamp. The DC table has time series data for each element in the II Table.

II Table

    id    Name
    0      A
    1      B
    2      C

DC Table

    id  DCId  data  timestamp
     0    0     5      TS3
     1    0     7      TS1
     0    1     8      TS10
     2    1     3      TS5
     1    1     5      TS4
     0    2     6      TS2

Where TS10 is later than TS1, etc

Currently I do a select on the II table to get all of the data, and then use a while loop to get the data in the second table for a matching Id and the max DCid.

Which results in

    Name      Data    TS
      A        8     TS10
      B        5     TS4
      C        3     TS5

I would like to do this with one select statement because I would like the result ordered by timestamp, but I am unsure how to get what I want.

Name      Data    TS
  A        8     TS10
  C        3     TS5
  B        5     TS4

The current code looks similar to this:

Select * from II ;
for ( results )
{
   Select Max(DC.DCId) as MaxDCId from DC where DCId=II.sensorInfoId ;
   Select Data, timestamp from DC where DCId=:MaxDCId AND DC.ID =II.ID
   (Stick results for name, value, and timestamp in HTML table)
}

I am not sure if I need a sub-query join or combinations of both to get what I want (or if it is possible with a single SQL query). I did it this way for expediency...I wanted to get the application running. Now that I want the result table ordered by timestamp, I could cache all the values, sort em and then display the HTML table, but if I could do it with 1 query and let SQL do the work I would rather do that.

SELECT
   Name,
   SUM(DC.data) AS Data,
   MAX(DC.TS) AS TS
FROM II
INNER JOIN DC ON DC.id = II.id
GROUP BY II.Name
ORDER BY 3 DESC

In stead of ORDER BY 3 DESC, you could also write ORDER BY MAX(DC.TS) DESC.


MySQL adds broken comment script after each line Every time I try to create a table using MySQL Workbench 6.3 CE it adds "broken" comments at the end of the script.

Example:

I am trying to create a table called "user". This is what the table should look like:

However when I click the apply button, the script generates incorrectly:

CREATE TABLE `user` (
  `userid` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `record_status` INT NOT NULL DEFAULT 1 COMMENT '',
  `createdby` VARCHAR(75) NOT NULL COMMENT '',
  `createddate` DATETIME NOT NULL COMMENT '',
  `updatedby` VARCHAR(75) NULL COMMENT '',
  `updateddate` DATETIME NULL COMMENT '',
  `username` INT NOT NULL COMMENT '',
  `password` VARCHAR(75) NULL COMMENT '',
  `firstname` VARCHAR(75) NULL COMMENT '',
  `lastname` VARCHAR(75) NULL COMMENT '',
  `email` VARCHAR(75) NULL COMMENT '',
  `facebook_userid` VARCHAR(75) NULL COMMENT '',
  `email_verified` VARCHAR(75) NULL COMMENT '',
  PRIMARY KEY (`userid`, `username`)  COMMENT '');

The comment in this line should not be there, so the code should be:

PRIMARY KEY (`userid`, `username`));

and the code executes when I paste it into an editor and remove this comment. However I create massive databases daily. (Each with +- 15 tables). I can't keep doing this for each table as it is time consuming.

How can I disable this feature that adds the comments to the script?
I don't need them in my databases

This is a bug on MySQL Workbench >=6.3.* reported in these cases #76578 and HERE.

This is what ALTER TABLE do:

It adds a COMMENT '' on every line of the SQL Script to apply:

I recommended you to downgrade your MySQL Workbench to 6.2.* until they fix this.

Tested in Ubuntu LTS 14.04 and MySQL Workbench 6.3.4.0.


What is the best way to have full text search where data in database is frequently updated I am working on an application where the database is continuously and periodically updated, and the frequency of updating of data is daily, weekly, monthly, and yearly based on their type. I want to implement full text search, I am using MySQL and applied full text indexes, and the search is working fine, but the job working for addition/updating of data was adversely impacted.

Does anyone have a solution that can be implemented for full text search for a continuously updated dataset?

I presume that update frequency in the worst case, would be a day, in your case. You could try Sphinx for this, as it is really fast and works well with MySql. And you could think of ElasticSearch also if the data is huge.

https://stackoverflow.com/questions/2271600/elasticsearch-sphinx-lucene-solr-xapian-which-fits-for-which-usage


Is there a way to find the least recently used tables in a schema? Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and INFORMATION_SCHEMA is always NULL.

Given the following:

Database mydb
datadir is /var/lib/mysql

Database with all MyISAM Tables

SELECT * FROM
(
    SELECT
        table_name,
        IFNULL(update_time,create_time) LastTimeTouched
    FROM
        information_schema.tables
    WHERE
        table_schema = 'mydb'
) A
ORDER BY LastTimeTouched LIMIT 20;

Database with all or some InnoDB Tables

This is impossible to do with innodb_file_per_table disabled because all the files reside in a single system tablespace (ibdata1).

Besides data and index pages, there are other things written in the system tablespace:

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of the system tablespace ibdata1

With innodb_file_per_table disabled, there is no way to tell. As far as I know, the InnoDB Storage Engine does not update the UPDATE_TIME column at all.

With innodb_file_per_table enabled, once an InnoDB table exists outside of ibdata1, you can only check the timestamp of the .ibd from the OS.

NEEDED SCRIPT

The following is a script that can tell you the LastUpdated Time for MyISAM and InnoDB tables together in one Database (provided you are using innodb_file_per_table)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
FILEDAT=/tmp/GetFileTimestamps.txt
FILESQL=/tmp/GetFileTimestamps.sql
DATABASE_TO_SEARCH=${1}
cd /var/lib/mysql/${DATABASE_TO_SEARCH}
ls -l --time-style=+%s *.[iM][bY][dDI] | awk '{print $7,$6}' > ${FILEDAT}
NUM=1
SELECT_TAG="SELECT"
HEADER1=" Filename"
HEADER2=" LastUpdated"
echo "SELECT * FROM (" > ${FILESQL}
echo "SELECT TableName,MAX(LastUpdated) LastUpdated" >> ${FILESQL}
echo "FROM (SELECT REPLACE(REPLACE(REPLACE(FileName,'.ibd','')" >> ${FILESQL}
echo ",'.MYD',''),'.MYI','') " >> ${FILESQL}
echo "TableName,LastUpdated FROM (" >> ${FILESQL}
for TAG in `cat ${FILEDAT}`
do
    (( NUM = 1 - NUM ))
    if [ ${NUM} -eq 0 ] ; then FILENAME=${TAG} ; fi
    if [ ${NUM} -eq 1 ]
    then
        UNIXTIME=${TAG}
        SQLSTMT="${SELECT_TAG} '${FILENAME}'${HEADER1},"
        SQLSTMT="${SQLSTMT} FROM_UNIXTIME(${UNIXTIME})${HEADER2}"
        echo ${SQLSTMT} >> ${FILESQL}
        SELECT_TAG="UNION SELECT"
        HEADER1=""
        HEADER2=""
    fi
done
echo ") AAA) AA GROUP BY TableName) A ORDER BY LastUpdated" >> ${FILESQL}
mysql ${MYSQL_CONN} --table < ${FILESQL}

This script is designed to check the timestamp of every table. The only parameter the table needs is the database.

For MyISAM, it uses the more recent timestamp of one of the following:

.MYD file
.MYI file

For InnoDB, it uses the .ibd timestamp


Why is this blob saving when used as a concatenated string but not as a prepared statement I have a PHP mysqli prepared statement that looks like this:

$editSQL = "UPDATE tblClientReminders
              SET Header = ?, Notes = ?, DepartmentID = ?,
                  TierID = ?, RemindType = ?, RemindDate = ?, DateAdded = ?
              WHERE ReminderID = ?";
     $stmt = $conn->prepare($editSQL);
     $stmt->bind_param('sbiiissi', $input[1], $input[2], $input[3],
             $input[4], $input[5], $input[6],
             $DateAdded, $_SESSION[$thisTracker]);
     $DateAdded = date("Y-m-d h:i:s");
     $stmt->execute();
     $stmt->close();

When I run this, it executes without throwing any errors and runs everything else as expected; however, the "Notes" field is cleared even though $input[2] has content.  But when I do this using concatenation:

$editSQL = "UPDATE tblClientReminders
         SET Header = '".$input[1]."', Notes = '".$input[2]."',
             DepartmentID = '".$input[3]."', TierID = '".$input[4]."',
             RemindType = '".$input[5]."', RemindDate = '".$input[6]."',
             DateAdded = '".date("Y-m-d h:i:s")."'
         WHERE ReminderID = '".$_SESSION[$thisTracker]."'";
     $stmt = $conn->prepare($editSQL);
     $stmt->execute();
     $stmt->close();

$input[2] is properly saved along with everything else.  I have verified that the column "Notes" is a blob in the mySQL database, and that $input[2] actually contains the intended data when I try the prepared statement.  So, why is $input[2] not saving in the first example, and how would I fix that.

The only other factor here I can think of is that $input[2] is passing an AES string, but I can't think of why that would be a problem since other queries elsewhere in my code are saving AES strings just fine, but I think this is the only one that does it to a blob.

An example of inputs:

$input[1] = 'test';
$input[2] = '8L2G1urlEQEjZdyMrSQeow==::88f32b3584167b1d7286a57f861d4fd6';
$input[3] = 1;
$input[4] = 3;
$input[5] = 0;
$input[6] = '2018-11-20';
$_SESSION[$thisTracker] = 15;

The bind_param page mentions (emphasis mine) for the types parameter that

  corresponding variable is a blob and will be sent in packets

which is done in this manner:

$null = NULL;
$stmt->bind_param('sbiiissi', $input[1], $null, $input[3],
         $input[4], $input[5], $input[6],
         $DateAdded, $_SESSION[$thisTracker]);
$stmt->send_long_data(1, $input[2]);

If your BLOB data exceeds the max_allowed_packet size you will need to call send_long_data multiple times, sending a smaller fragment of it each time.

More info in this Oracle blog.


Audit use of CLR Table-valued function I have written some CLR table-valued functions that invoke a web service and I would like to audit their use.  Does anyone know of any way this can be achieved in the CLR code itself?  I would be nice to keep the auditing code together with the CLR code rather than having to write an explicit INSERT statement every time they are used.

Since the TVF calls a Web Service, the Assembly had to be marked as WITH PERMISSION_SET = EXTERNAL_ACCESS (hopefully you chose to sign the Assembly, create an Asymmetric Key, and create a Login from that Key, as opposed to setting TRUSTWORTHY ON for the Database). This fact gives you two options:

Use something like File.AppendAllText to log the info into a file and keep it out of the database. This method creates the file if it doesn't exist. NOTE: This option needs to be tested with concurrent actions that would fire the Trigger as there might be some blocking / additional wait-time incurred if the OS does not allow concurrent write activity on the file.
Log to a table by creating an regular / external SqlConnection and issuing an INSERT statement. Be sure to specify enlist=false; in the Connection String so that it doesn't attempt to bind to the current Transaction. This will allow the INSERT (which occurs in a separate connection / Session) to happen whether or not there is an exception raised in the SQLCLR TVF (unless, of course, the error is with the INSERT operation ;-).


Database stalls on innodb_buffer_pool_wait_free I have 4 identical MariaDB 10.0.33 databases that are regularly experiencing database stalls in different situations. I'm trying to understand how to tune the innodb parameters to prevent these stalls. I am aware I can/should add more ram/more disks to better support this workload. However, I really want to understand the implications innodb_lru_scan_depth has on the innodb_buffer_pool_pages_free metric, and what is causing frequent stalls as measured by the innodb_buffer_pool_wait_free metric.

First, some config:

Raid10 x4 SSD, 64gb ram, zero swap
innodb_buffer_pool_size = 48G (yes i know this can be increased a little)
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_log_file_size = 4G
innodb_log_buffer_size = 8M
transaction-isolation = READ-COMMITTED
innodb_flush_neighbors = 0
innodb_io_capacity = 2000 # default 200
innodb_io_capacity_max = 4000 # default 2000
innodb_max_dirty_pages_pct_lwm = 50
innodb_max_dirty_pages_pct = 75
innodb_flushing_avg_loops=90
innodb_lru_scan_depth = 16384 (this is the most important)

Second, some stats:

Disk reads are fairly stable throughout the day at ~3k - 5k, peaks to 10k
Disk writes are fairly stable throughout the day at ~1k peaks to 2k
Disk read latency less than ~0.5ms on average, some spikes to 1ms
Disk write latency less than 3ms on average, some spikes to 10ms
Disk utilization averaging ~80% some spikes to 95-100%
Between 1% and 2% buffer pool cache miss ratio
Consistent 1k innodb pool pages flushed per sec
Consistent 13-18% nnodb pool bytes dirty
Consistent 350k - 420k pages dirty
Frequent drops of innodb_buffer_pool_pages_free from 130k down to 0k
Frequent spikes of innodb_buffer_pool_wait_free to as high as 25k

Increasing the innodb_lru_scan_depth from the default of 1024 improved the situation. innodb_buffer_pool_pages_free immediately grows every time I increase it.  In the attached dashboard, I changed from a value of 16384 to 32768 innodb_lru_scan_depth at 11:40 on two of the four servers, and the pages free jumped up to ~260k from ~130k. There seems to be strong correlation between a lack of free pages, lru depth, and wait free.

My primary question is, what is the relationship between this LRU parameter and the database stalls I am experiencing?

Second question, other than adding more ram/storage bandwidth, from the database perspective, what can I tune to throttle expensive workloads that may be running in parallel, and saturating my disks? There may be a correlation
with the innodb_buffer_pool_pages_old metric increasing when I'm seeing periods of database stalls.  This seems to indicate something is doing a large full table scan, and making the buffer pool less optimal.

what is the relationship between this LRU parameter and the database stalls I am experiencing?

The relationship is quite straightforward. The background flush thread every second will scan innodb_lru_scan_depth * innodb_buffer_pool_instances LRU list entries in an attempt to find dirty pages that it can write out to disk. Flushed pages become free and add to innodb_buffer_pool_pages_free.

If the DML thrown at your database produces dirty pages faster than the flush thread can write them out, at some point innodb_buffer_pool_pages_free will go down to zero, and the next attempt to read a page to the buffer pool will cause another innodb_buffer_pool_wait_free while the server flushes dirty pages to disk synchronously.

Ideally you would want the background flushing thread to write dirty pages to disk at the same rate (on average) as they are dirtied by the DML activities. You do that by increasing innodb_lru_scan_depth, which increases the probability of dirty pages being written to disk before the server needs more free pages.

An alternative, as mentioned in another answer, is to enable adaptive flushing and set the appropriate innodb_max_dirty_pages_pct and/or innodb_max_dirty_pages_pct_lwm values.

It should be noted that overly aggressive background flushing could saturate the I/O bandwidth on its own, which would be just as bad as excessive innodb_buffer_pool_wait_free events. At some point you might find that you will have to allocate more memory for buffer pools to satisfy your workload demands, or live with occasional free page waits.


Oracle equivalent to MS-Access character range/set In MS-Access, I can use character ranges/sets in the where clause:

SELECT
    table_name
FROM
    all_tab_columns
WHERE
        table_name NOT LIKE 'A[0-9]*'
    AND table_name NOT LIKE 'D[0-9]*'
    AND table_name NOT LIKE 'S[0-9]*'

The character range/set is the range of numbers between 0-9. All tables that start with A, D or S, followed by a number, are eliminated from the query.

How can I do this in Oracle?

Use regular expressions:

SELECT
    table_name
FROM
    all_tab_columns
WHERE
    NOT REGEXP_LIKE(table_name,'^(A|D|S)[0-9]')


Do we have to run auto update statistics after index rebuild? I want to rebuild one of my indexes because fragmentation has reached 97 percent.

I want to know if after I rebuild my index, do I have to update the statistics as well? My auto update statistics feature is on.

And is it necessary to do the auto update?

You should not update statistics after you rebuilt the index as rebuilding it gives you up-to-date statistics. See Does rebuild index update statistics? by Jack Li for more info.

You get updated stats for the specific index you just rebuilt. Other stats for other indexes on the same table (and statistics not associated with an index at all) do not get updated. You may or may not choose to update those stats separately.

Auto-update is recommended, though I would also recommend a scheduled job too as the threshold for when auto-update kicks in can be too high for many systems. I usually have an update stats job run daily or even more frequently, but it depends on the system and its needs.


8000 character limit on OPENQUERY against a linked server I have a query that I'm trying to run through OPENQUERY on SSRS/SQL Server 2014, but I keep getting an error of:

  The character string that starts with [...] is too long. Maximum length is 8000.

Is there any way to work around this limitation?

For reference, I'm trying to run a query from SSRS through a linked MySQL Server.

You can bypass the 8000 character limit of OPENQUERY by utilizing EXECUTE AT, as follows:

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'SELECT * FROM TABLE WHERE CHARACTERS.... ' -- Imagine that's longer than 8000 characters

EXECUTE (@myStatement) AT LinkedServerName

In order to make sure this doesn't throw an error, you need to enable the RPC OUT option on the linked server, by issuing the following command:

EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'rpc out', @optvalue=N'true'

Or enabling it within the GUI:


"Unable to Open BCP host data-file" using xp_cmdshell from SSMS but command line works I have a stored procedure that runs on a job every once in a while to BCP some files left by an application I've written.

I noticed the files were piling up and BCP wasn't picking them up so I tested in Management Studio using the following:

DECLARE @sql VARCHAR(MAX)
DECLARE @path VARCHAR(512) = 'C:\BCPFiles\'

--Use BCP to copy files in character format from the target directly into the table.
SET @sql = 'bcp [MyDB].[dbo].[MyTable] in ' + @path + 'bcpFile.dat -c -T'

EXEC master..xp_cmdshell @sql

And got this error message

Executing from the command line on the same server using the same command works no problem.

Here's a few other observations:

I can run EXEC master..xp_cmdshell 'dir C:\*.*' and the results return as expected.
If I copy and rename the file to something different, BCP picks it up
The file isn't being left open by my application: I can move it, rename it, edit and save it, delete it etc... without any complaints from Windows.
The internal format of the file is obviously okay (see 2.)
NT SERVICE\MSSQLSERVER has Full Control permissions to the directory.
The Maple Leafs traded Dion Phaneuf yesterday

Any help is appreciated.

I just got your code to work on my end.

Try this:

Option A

Assign Full Control NTFS permissions to 'C:\BCPFiles\bcpFile.dat to
the user you will use in SQL Server Management Studio to run the
query
Re-run your query in SQL Server Management Studio

Option B

Or, just to get past the following error try:

Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file

Delete/move your bcpfile.dat file
on the command line type (to create a dummy file--yes there will be import errors, but just to get past opening the file...)

echo .> c:\bcpfiles\bcpfile.dat
Re-run your query in SQL Server Management Studio

If option A works, then it's a permissions issue. If option B works (getting past the original error) then you have an open file--even though it doesn't appear to be open. There are utilities that help out with finding these open files, but a cheap and easy way to test is just to reboot the server--hopefully it's a dev/test box.


Grouping data into 5 minute intervals within a time range I am trying to prepare data for a candle stick chart. I have some records on MySQL database as shown below:

| Rate     | Date                |
|----------|---------------------|
| 0.001111 | 2017-11-12 19:13:05 |
| 0.001214 | 2017-11-12 19:14:11 |
| 0.001612 | 2017-11-12 19:15:42 |
| 0.001231 | 2017-11-12 19:16:23 |
| 0.001514 | 2017-11-12 19:17:23 |
| 0.001611 | 2017-11-12 19:26:33 |
| 0.001421 | 2017-11-12 19:27:25 |
| 0.001214 | 2017-11-12 19:28:27 |
| 0.001611 | 2017-11-12 19:29:55 |
|----------|---------------------|

I want to group below data as shown below:

| Rate     | Date                |
|----------|---------------------|
| 0.002325 | 2017-11-12 19:10:00 |
| 0.004357 | 2017-11-12 19:15:00 |
| 0.004357 | 2017-11-12 19:20:00 |  # Auto generated record of missing record.
| 0.005857 | 2017-11-12 19:25:00 |
|----------|---------------------|

I was trying to understand calendar table, but I wasn't able to do with that.

I also followed this link https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range but still failed.

UPDATE:

This is my sample query that I am using currently to get data with date sorting and grouping.

SELECT
SUM(exchange_rat) AS totalAmount,
DATE_FORMAT(from_unixtime(unix_timestamp(created_at) - unix_timestamp(created_at) mod 300), '%Y-%m-%d %H:%i:00') as createdAt
from payments group by createdAt


T-SQL sp_msforeachdb problem I am trying to compare the database name using the ? operator in the sp_msforeachdb function.  Here is a snippet:

EXEC master..sp_msforeachdb '

USE [?];
IF NOT EXISTS (
    SELECT db_name(database_id) as [database]
    FROM sys.dm_database_encryption_keys
    WHERE [Database] = ''?'')
BEGIN
...
'

Basically, I want to be able to find the databases that do not have an encryption key and begin/end a transaction ELSE begin/end a different transaction.

? stands for the datbase name and sys.dm_database_encryption_keys holds database_id.

exec master..sp_msforeachdb
'
IF NOT EXISTS
(
    SELECT  null
    FROM    [?].sys.dm_database_encryption_keys
    WHERE   database_id = db_id(''?'')
)
BEGIN
    print ''?''
END
'


Update many-to-many relationship table I have three tables: task, taskmembers, members.  Each task may have up to five people associated to it.  Currently, when someone updates a task I delete all the current members and insert them as a new record.  Otherwise, I have to figure out if the person is still a member of the task and, if not, delete them.  Also, I have to delete the people no longer associated with the task and update the people still associated to the task.

My concern is that I'm currently using an int as my primary key within the members table.  I know MS SQL int values can go up to 2,147,483,647 before overflowing, but it's still a concern of mine.  Also, is deleting all the records a good practice?  Thank you in advance.

With an average of 5 relationship rows per task per update if you have 1000 tasks created or updated each hour, you will have enough space in an unsigned integer for 49 years.

You could use a BITINT instead (a 64-bit numeric rather than a 32-bit one), though if you are passing the value into any other code that treats it as a numeric type rather than a string or similar you need to ensure that your target language(s) can handle larger numbers natively.

Or as Melvin suggests, use a UUID type (128 bit keyspace, and generally (if you always generate them properly) portable between systems without keyspace conflict).

As a further option: do you actually need a surrogate key on that table? You could not use task+member as a composite key instead if a person will not be linked to the same task twice?

Edit: As for the delete-before-insert (instead of check-and-insert-or-delete-as-needed): there is no significant problem with this if it makes your code simpler and therefore easier to maintain.

It is less efficient as you are removing some rows that will then immediately get put back as they were, so there will be more I/O activity generally and more actions against rows being logged (relevant if you are using differential backups, log shipping, or so forth) and potentially greater fragmentation within the data files (though this latter effect should be small enough to be safely ignored unless there are many updates per second), and you are using more of your keyspace in that INT PK, but often having simpler code is valuable enough to more than be worth these side effects.

Do make sure that your actions are wrapped in a transaction so that if something goes wrong any changes that were made before the problem are all rolled out together instead of leaving your data in an inconsistent state - though you should do this for the other methods of updating multiple rows in different tables anyway.


For Delete trigger with condition on a joined table I am needing to create a trigger to load deleted data records into a history table. However I want to only insert those deleted records based off a condition of a field on another table.

I've created a trigger like this

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
AFTER DELETE AS
BEGIN
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
            SELECT 'Borrower' AS TableName ,ssn, GETDATE()
FROM deleted
END

This works fine however, I am needing to add a condition so the trigger only inserts those records based on a field condition located in a joined table

Ideally if it were possible I would want to do something like this:

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
FOR DELETE AS
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
SELECT 'Borrower' AS TableName ,
       i.ssn,
       GETDATE()
FROM deleted i
join borrower bwr on i.borrowerid=bwr.borrowerid
join filedata fd on (bwr.FileDataID = fd.FileDataID )
INNER JOIN (select OrganizationID from Organization where code not like '26%') org on (fd.OrganizationID = org.OrganizationId)
where fd.filename not like '26%'
and fd.filename not like '%.copy' and fd.filename not like '%+'

Apparently this wont work since the record on the deleted table doesn't really exist.

My question is, what would be another way to achieve this?
Is there perhaps a BEFORE DELETE trigger I should use? Should I maybe use two triggers to get the information I need so that the deleted record can find the fields I am looking for?

Have you tried:

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
AFTER DELETE AS
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
SELECT 'Borrower' AS TableName ,
       i.ssn,
       GETDATE()
FROM deleted i
join filedata fd on (i.FileDataID = fd.FileDataID )
INNER JOIN (select OrganizationID from Organization where code not like '26%') org on (fd.OrganizationID = org.OrganizationId)
where fd.filename not like '26%'
and fd.filename not like '%.copy' and fd.filename not like '%+'

You shouldn't need to tie back to the Borrower table - the deleted table should hold all the same columns as Borrower did.


Using UNION (ALL) in a WHILE loop with SELECTto merge the result set for each iteration When I am looping with WHILE in SQL Server 2012, if I use a SELECT statement it prints the column name for each iteration. Can I UNION all?

Here is my sample query:

CREATE PROC spGetChar
    @start int ,@end int
AS
BEGIN
WHILE (@start<@end)
BEGIN
--Print CHAR(@start);

SELECT CHAR(@start) AS Character;

SET @start=@start+ 1;
END
END;

Depending on your real requirements, something like this might work:

CREATE PROC spGetChar @start INT ,@end INT
AS
BEGIN
    DECLARE @Out varchar(255);
    SET @Out ='';
    WHILE (@start<@end)
    BEGIN
        SELECT @Out = @Out + CHAR(@start);
        SET @start = @start + 1;
    END
    SELECT @Out AS Character;
END;

This will append each char to a variable, then output the content of the variable.  If you need each character on  a separate line, you could add CHAR(13) + CHAR(10) to the end of the SELECT @Out line.

if you require each character to be returned in its own row, you could do:

CREATE PROC spGetChar @start INT ,@end INT
AS
BEGIN
    DECLARE @Out TABLE (Character varchar(255), RowNum IDENTITY(1,1) );
    WHILE (@start<@end)
    BEGIN
        INSERT INTO @Out (Character) VALUES ( CHAR(@start) );
        SET @start = @start + 1;
    END
    SELECT * FROM @Out ORDER BY RowNum;
END

This stores the character data in a table variable during each iteration of the while loop, then selects the contents of the table variable.  The ORDER BY clause ensures the character data is outputted in the correct order since the RowNum field is an automatically incrementing IDENTITY column.


SSIS: Import Excel file to SQL Server Is there a Excel version for this article? I have been searching for this past days but there is no luck.

https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/

If this is a simple Excel file load, you can just use the Import Wizard to create an SSIS package for you (available by right clicking the Database, and selecting Tasks -> Import Data) and even save that package for future use.

If this is a process where you need to take data from an Excel file, transform it, then load it, then yes a custom SSIS package will work better.  You should be able to add a new connection and indicate that it is an excel file using the "new Connection Manager" wizard in your SSIS package.


SQL Server 2008 ODBC My SQL Server 2008 is located on a remote computer so I am trying to create an ODBC connection using SQL Server Authentication.

My problem is that the way i normally connect to my SQL Server is by doing a remote desktop connection and then I just connect using windows authentication so I don't know what my SQL Server Username and Password are.

Is there anyway I can find out what my Sql Server Authentication username is so I can maybe reset my password?

All I am trying to do is to create an ODBC connection usnig SQL Server Authentication since I can't use Windows Authentication since the SQL Server is on a remote Windows Server computer (or can I?)

You can ask the Database Administrator for the environment what your SQL Server Login details are for using SQL Server Authentication.

Alternatively, you could connect to the SQL Server Instance via RDP and Windows Authentication and then create a new SQL Server Login for you to use remotely.

To confirm, you cannot use Windows authentication remotely (from outside of the domain the instance resides in) unless you are connecting from a domain with a trust relationship defined.