SQL Server ODBC Mirror/Failover Partner Background/Context

We are in the process of moving our SQL Server 2000 databases to a mirrored SQL Server 2008 R2 server with a witness and automatic failover.  We have both .Net and Access applications running off of these databases, and are testing client redirection on a server failover event.

The .Net apps respond correctly after I added a "Failover Partner" to the connection string. On a failover the app will fail on the next DB request, then move to the mirror server when we restart the app.

Question

For the Access applications going through ODBC we are trying to use the SQL Server Native Client 10.0 driver (it looks like the standard driver doesn't support mirroring?) and supplying a mirror server in the settings.

I'm specifying the database and the failover server name here.  On a failover event the Access application does not seem to be redirected to the mirror server even after an application restart.  Is there anything else I should be doing here to force ODBC to switch to the mirror server, or am I not specifying the mirror server name correctly?

I maintain an Access 2003 application that connects to a pair of SQL Server 2005 servers setup with mirroring.  I use the following connection string which allows the application to connect to the mirror after an application restart:

Dim ws As Workspace
Dim db As Database
Dim a As Long

Set ws = DBEngine.CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseJet)
Set db = ws.OpenDatabase("C:\[pathtodatabase]", True, False, "MS Access;")
For a = 0 To db.TableDefs.Count - 1
    Me.ProgressBar1.Value = a
    DoEvents
    If db.TableDefs(a).Connect  "" Then
        db.TableDefs(a).Attributes = TableDefAttributeEnum.dbAttachSavePWD
        db.TableDefs(a).Connect = "ODBC;DRIVER=SQL Native Client;SERVER=[PrimaryServerName];Trusted_Connection=Yes;APP=[MyAppName];WSID=[MyWorkstationName];DATABASE=[SQLServerDatabase];Failover_Partner=[MirrorSQLServerName];"
        db.TableDefs(a).RefreshLink
    End If
Next a
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing

I use this code in a VB launcher app I created that the user double-clicks to launch the Access MDB/MDE.

Alternatively, this is the content of a DSN file we sometimes use to accomplish the same thing:

[ODBC]
DRIVER=SQL Native Client
APP=Microsoft Data Access Components
Trusted_Connection=Yes
SERVER=[ServerFullyQualifiedDomainName]
FAILOVER_PARTNER=[MirrorFullyQualifiedDomainName]
DATABASE=[DatabaseName]


Write a query which add two fields of a document and compare their sum to third field in mongo I have one document, say:

{
        "_id" : ObjectId("538b7128cf16e3ea5a1ab23c"),
        "pgDownloadTime" : NumberLong(9776),
        "pgAccessTime" : NumberLong("1401647400058"),
        "expiredTime" : NumberLong("1401647468678"),
        "contentType" : 1,
        "articleId" : "35909002",
}

My query will be:

db.pageTrendLog.findOne({"expiredTime" : {$lte : "pgDownloadTime"+"pgAccessTime"}});

How can I write this query?

Here is one part of the answer; adding two fields together. Still trying to figure out how to compare the total of pgDownloadTime +pgAccessTime with expiredTime

// Add pgDownloadTime and pgAccessTime

db.pageTrendLog.aggregate([
    { $match: { "_id" : ObjectId("538c304b7446cf13431a2c5c") } },
      { $project :
        {
          'pgDownloadTime' : '$pgDownloadTime',
          'pgAccessTime' : '$pgAccessTime',
          'total' : {'$add' : [ '$pgDownloadTime', '$pgAccessTime' ]}
        }
      }]);


Invalid object name 'sys.dm_os_volume_stats' I am trying to select 15% of disk space available in a server.

 SELECT DISTINCT
            s.volume_mount_point [Drive],
            CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs],
           ((CAST(s.total_bytes / 1048576.0 as decimal(20,2)))*15)/100 AS [FifteenpercentAvailableMBs]

            FROM
                    sys.master_files f
                    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s

This query works well in Microsoft SQL Server 2008 R2 (SP2)

But it wont work in Microsoft SQL Server 2008 R2 (RTM)

How can I make this query work in 2008 R2 RTM? (I mean is there any alternative query that I can use to get 15% of available space?)

You can't use this DMV in RTM; it was introduced in SP1 (though the current version of the documentation does not specify that, the 2008 R2 version does). You should be on Service Pack 3 anyway (that will make those old servers easier to support, not harder).

In RTM you can do some really convoluted things, like this. He is using xp_cmdshell to call PowerShell to grab output from a WMI class (Win32_Volume), then dump those results into a #temp table, and compare them to data from an undocumented and unsupported extended procedure, xp_fixeddrives.

There is a similar solution here. It uses WMI (Win32_PhysicalDisk), VBScript, and PowerShell from a SQL Server Agent job - while they avoid xp_cmdshell here, the overall methodology feels similarly hobbled together.

If you were working in construction, these would be the equivalent of about eight rolls of duct tape and a half gallon of WD-40.

Maybe you should consider performing this task using PowerShell or similar (doesn't this seem a lot simpler?), instead of trying to bend T-SQL to do this. SQL Server isn't really the right tool to manage server disk space.


Need help with difficult table modeling situation Database newbie here with no formal training, trying to train myself.  I was looking for a more difficult case to model and came up with following.
Hypothetical ice cream shop where there are containers (small cone, large cone, small, medium and large cups) each with a price and a number of scoops for each.
There are flavors with a price per scoop, and multiple flavors can be used for containers that can have multiple scoops.
There are toppings where just for simplicity I am limiting to one topping per final product including none.
I cannot understand how to get from these product parts to the Order_Details table.  I am used to looking a single Products table that relates to an Order_Details table that relates to Orders Table, but I cannot comprehend how to get from the Containers, Flavors and Toppings tables I have designed to the Orders_Details table.
I am requesting some ideas on the best way to model from my product parts to the Order_Details table.

Please advise.

You probably need a linking table called ContainerFlavors (or IceCreamCombinations for something more sensible) for the many-to-many relationship between Containers and Flavors.
That linking table would have it's own unique ID column, and purely store ContainerID and FlavorID. The primary key on that table would be it's ID column and there would be a foreign key on ContainerID and foreign key on FlavorID (and basically would store every possibly combination of Containers and Flavors).
You can store this linking table's ID column in the your Order_Details table.
You can expand the linking table out to store the ToppingID as well (with a foreign key to Toppings) for every combination of Containers, Flavors, and Toppings.

So after some sitting down and modeling out your example, I realized your example is very similar to a manufacturing schema, which is why it's a litte more complex. Please see the schema I came up with below (please ignore any minor semantical differences such as casing, compared to yours). In this example schema the term "Combination" can be thought of as synonymous to "FinalProduct" (feel free to rename things on your end, as needed).

In this schema you have an Order which can contain multiple OrderDetails, where each OrderDetail has one Combination (final product), but the same Combination can be used on multiple OrderDetails (e.g. if two people order "a cone of chocolate ice cream with hot fudge" combination).
The Combinations table is a distinct list of final products. It has a one-to-many relationship with the CombinationsBom table. (BOM stands for Bill of Materials in the manufacturing industry, and is a type of mapping table that contains all the components that make up a final product. In this case all the container + flavors + topping combinations to make up the final product Combination.) Every row in the CombinationsBom table is the Container with it's Flavor or Topping selection. Multiple rows can exist for the same Combination (hence the one-to-many relationship). Therefor a Combination that is made up of a two scoop Container and has no Toppings should have two rows in the CombinationsBom table, one for each Flavor. (Note since BOM tables contain every possible combination of the final products, they are usually large tables.)
*This design actually allows for multiple Toppings in one Combination as well.
There are a few other ways to model this, or minor normalizations that can be added to my example schema, but I think this is a good start for what you're trying to achieve.
Using the above schema we can write an example query like such:
SELECT O.OrderNumber, O.OrderDate, O.CustomerId, C.CombinationName,
    CB.BomId, CB.CombinationID,
    CO.ContainerName, CB.ContainerID, CO.ContainerPrice, CO.NumberOfScoops,
    CB.IsTopping, CB.FlavorOrToppingID,
    CASE WHEN CB.IsTopping = 0 THEN F.FlavorName ELSE T.ToppingName END AS FlavorOrToppingName,
    CASE WHEN CB.IsTopping = 0 THEN F.PricePerScoop ELSE T.ToppingPrice END AS Price
FROM IceCream.Orders AS O
INNER JOIN IceCream.OrderDetails AS OD
    ON O.OrderNumber = OD.OrderNumber
INNER JOIN IceCream.Combinations AS C
    ON OD.CombinationID = C.CombinationID
INNER JOIN IceCream.CombinationsBom AS CB
    ON C.CombinationID = CB.CombinationID
INNER JOIN IceCream.Containers AS CO
    ON CB.ContainerID = CO.ContainerID
LEFT JOIN IceCream.Flavors AS F
    ON CB.FlavorOrToppingID = F.FlavorID
    AND CB.IsTopping = 0
LEFT JOIN IceCream.Toppings AS T
    ON CB.FlavorOrToppingID = T.ToppingID
    AND CB.IsTopping = 1

To produce these kind of results:

(Note I'm finding minor things that need to be improved in the schema design, and I'll continue to update my answer accordingly, but this should hopefully give you an idea of what's involved for modeling this kind of schema.)


How to determine effective permissions and how granted for a local or domain account With SQL Server - if you have a local or domain user (including accounts like LOCAL SYSTEM) how can you determine

What effective permissions they have on a database
How those permissions are granted (e.g. which roles, any direct etc)

In the Server you can look for

exec sp_helpsrvrolemember sysadmin exec sp_helpsrvrolemember dbcreator

In the databases that you want

exec sp_helprolemember db_datareader
exec sp_helprolemember db_datawriter
exec sp_helprolemember db_ddladmin
exec sp_helprolemember db_owner

and all direct permissions

exec sp_helprotect null,null


Patching after upgrade (windows 11gr2 -> 12cR2) I have a following scenario where I had a patched oracle 11gR2 DB (applied Windows Bundle and OJVM patches) and then it was upgrade to Oracle 12cR2. 12cR2 was a clean installation (no patches applied, just a download).

In the case above then do I need to apply “same” patches again to my upgrade 12cR2 DB?

I am talking about patchset bundles. I did apply to 11gR2 a Windows Bundle patches and OJVM PSU.

So just to be clear: To have a "same" level of patching then I need to install corresponding 12cR2 patch? For example I had a 2018 jul patch installed to 11c and now I would need to install "again" 2018 jul, but for 12c (I would install again Windows BP and OJVM PSU)? I understand that 11c patch might have fixed a bug, which is in 11c and 12c, but it does not fix a bug in 12c. As I was saying then I need to be sure..

For the future then I should apply patches before I would do the upgrade?

That would depend on what the "same" patches are.  At the very least, you'd need the patch specifically for the new version of the database.  But it could be that the the 11g patch fixed something in 11g that was inherently fixed in 12c.  You just have to look at each patch on a case-by-case basis.  Also, are you talking about individual patches or patchset bundles?  If the latter, simply apply the latest for your version of the database.


Column names - (created, modified) vs. (creation, modification) I have a database schema where all the tables are required to have a column to hold the timestamp for the initial INSERT and another column that holds the timestamp of the last UPDATE.
Initially this column will have the same value as the one used for the INSERT timestamp.

I can't decide if it's more appropriate to call them created and modified or maybe creation and modification.

Suggestions for different names are also welcome.

Of those two options creation/modification as that is less ambiguous.

Looking at some SQL or documentation for the structure that doesn't include column type information, created/modified could be mistaken for boolean values.

Furthermore, I'd be inclined to be more specific and use creationTime/modificationTime or createdTime/modifiedTime, probably the latter because it is vaguely quicker to type.


The following LOAD DATA sentence loads the CSV file, ignoring the first line and inserting into the desired fields, but changing the format of the Date and Time columns into a proper datetime type:

mysql> LOAD DATA INFILE '/tmp/alarms.csv'
       INTO TABLE alarms
       COLUMNS TERMINATED BY ','
       IGNORE 1 LINES
       (@Date, @Time, ALARM_01, PT001, TEMP_AMB)
       SET DateTime = STR_TO_DATE(CONCAT(@Date, ' ', @Time), '%d/%m/%Y %H:%i:%s');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM alarms;
+----+---------------------+----------+--------+----------+
| id | DateTime            | ALARM_01 | PT001  | TEMP_AMB |
+----+---------------------+----------+--------+----------+
|  1 | 2014-08-14 14:23:21 |        0 | 130.00 |    20.00 |
|  2 | 2014-08-14 14:23:25 |      101 | 128.09 |    20.10 |
+----+---------------------+----------+--------+----------+
2 rows in set (0.00 sec)

Your fields and datatypes may vary slightly, with no effect on the import query.


Could the creation of an index break table dependencies? I ran a query using the sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats to determine what are my options for missing indexes. After analyzing the data I determined that I needed to create a non-clustered index for Table A.

Looking at Table A, I noted that there are 55 dependencies (Tables, SPs, Views) and 23 objects that are dependent on this table. My question is, if i create this non-clustered index on this table does it affect the dependencies and dependents of this table?

Moreover, if yes it affects the dependencies, how do I even attempt fix the dependencies?

When implementing filtered indexes, certain connection settings must be correct:

Otherwise

  The Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.

So if any of the dependent code does not have the above SET options correctly configured, adding a filtered index will break them. I unfortunately have had to learn the hard way to double check this when working on old systems with questionable stored procedure code :)


How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT On IBM DB2 I try the following direct SQL:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   user_name VARCHAR(16) NOT NULL,
   email_address VARCHAR(60),
   PRIMARY KEY (user_id)
)

Then I get the following error:

  The table definition statement failed because some functionality was specified in the table definition that is not supported with the table type.  Unsupported functionality: "GENERATED".. SQLCODE=-1666, SQLSTATE=42613, DRIVER=3.63.123 SQL Code: -1666, SQL State: 42613

GENERATED BY DEFAULT is supposed to be supported according to IBM DB2 Documentation.

Is there something incorrect in my SQL?

EDIT:

I used sqlalchemy and ibm_db_sa with the following code:

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email')
)

which produces:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   user_name VARCHAR(16) NOT NULL,
   email_address VARCHAR(60),
   PRIMARY KEY (user_id)
)

However, by adding autoincrement=False the IDENTITY is removed and the SQL executes without problem:

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True, autoincrement=False),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email')
)

which produces:

CREATE TABLE users2 (
    user_id INT NOT NULL,
    user_name VARCHAR(16) NOT NULL,
    email_address VARCHAR(60),
    PRIMARY KEY (user_id)
)

Column-organized tables do not support some features, including identity columns. Apparently, in your database the default table organization (the DFT_TABLE_ORG parameter) is set to columns. Either change that or create the row-organized table explicitly:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   user_name VARCHAR(16) NOT NULL,
   email_address VARCHAR(60),
   PRIMARY KEY (user_id)
) ORGANIZE BY ROW


I have SQL Server Management Studio 18 - Where I can find SQLServerManager / SQL Server Configuration Manager I need to install SQL Server Configuration Manager in order to configure SQL Aliases, but I don't want to install whole SQL Server
I expected it to be installed with SSMS, but I'm unable to find anything on disk with name SQLServerManager
How can I get it as easily(without installing heavy components) as possible?
Thanks in advance

To configure SQL client aliases, there is no need for SQL Server Configuration Manager. The aliases are often for client-side usage, so installing the Configuration Manager would be extra hoop to jump through. There are two executables called cliconfg.exe in c:\windows\syswow64 and c:\windows\system32. These should be present after installing SQL Server Client tools or, in your case, SSMS.
The system32 version of cliconfg will create aliases for 64 bit applications, whilst syswow64 does the same for 32 bit. It is a good practice to create aliases for both environments, even if one uses mainly x64. If 32-bit aliases are omitted, legacy applications cannot connect to the database via alias.
For scripted solutions, one can edit registry keys directly. The keys are in paths
HKLM\Software\Microsoft\MSSQLServer\Client
HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client

There are several pages how to configure aliases (search for network utility on the latter link). Googling for sql server client alias provides a lot of hits about the subject too.


When does PostgreSQL move a WAL file to archive? I'm a complete newbie with PostgreSQL - please forgive my gaffs - and have inherited a service using a master-slave cluster at the backend.

Currently on the slave, the disk is filling up with WAL files in the archive directory.

On a slave (replica) I presume the archived WAL files been applied to the local instance of the database since there are files in pg_xlog with current filestamps.

Q1: Is this correct?

Q2: What is the criteria by which the system decides that a WAL file is eligible to be moved from pg_xlog to the archive?

The backup/restore model is predicated around filesystem snapshots - so a restore operation would rely on crash recovery. I infer from this that I should only need the artchived WAL files since the last file-system snapshot to be available (although keeping at least 2 generations would be a better idea).

Q3: Is this correct?

Who is writing to the replica's archive directory, the master (via scp or something like that) or the replica?

The replica only archives if archive_mode=always. If it just set to 'on', then only the master archives.

  What is the criteria by which the system decides that a WAL file is eligible to be moved from pg_xlog to the archive?

They are never moved.  They are copied to the archive.  And then at some later point (after at least a checkpoint), they are deleted/overwritten from pg_xlog/pg_wal.  The criteria for deletion are all of: a successful checkpoint/restartpoint, wal_keep_segments satisfied, all replication slots (if any) satisfied, archive_command (if in use) returns success.

  The backup/restore model is predicated around filesystem snapshots - so a restore operation would rely on crash recovery.

Who are you trying to restore here, the master or the replica?  I just recreate a replica if it gets lost, but some people find that too slow and want to be able to restore it.  And some people wouldn't recovery the master at all, they would just promote the replica.

  I infer from this that I should only need the artchived WAL files since the last file-system snapshot to be available (although keeping at least 2 generations would be a better idea).

Unless your file-system snapshot has itself been backed up somewhere, I would assume it is no longer available to be used after a major crash.  So you need all the WAL since the beginning of the most recent snapshot which has itself been backed up offsite.  If you want to retain the ability to do PITR, you need more than that.


Database design for an online babyShop ,Toy shop Hi i'm designing a database for a toy shop this shop offer many products with different categories. boystoys,girlstoys,puzzles,beds,stroller, etc
i designed my database as it is consists of 5 tables
(Customer,Order,order-details,product, product-category)

in the product category i added 2 columns one the primary key(ProductCategoryID) and the second for the (ProductCategoryName) i made it a unique so i can make relation between it and the product table.

My question : do you think that this design is True or not i'm still a beginner in designing a database and i want to know do you advice me to add another tables ?

note: i will not let the customer to pay here i just want from him to order his item and the shop will call him to confirm his order , the customer only will choose his products and then click Order and then a message come's to the shop owner with his details and order

i dont know why my client want to do this in his web site but im trying to develop it :D

Your design commonly looks good except few mistakes.

1) You had used a wrong field in order to establish a relation between ProductCategory and Product. You have to use ProductCategoryID in Product table instead of using ProductCategoryName. Howeever, some products may belong to more than one product category.
If this is a valid case for your project, you should add additional table something like "ProductVsProductCategory" which will allow you to establish a "many to many" relation between Product and ProductCategory tables if you intend to add some products into multiple categories.

Basic structure of ProductVsProductCategory might look like following:

ProductVsProductCategoryID (primary key)

ProductID (refers to Product table)

ProductCategoryID (refers to ProductCategory table)

2) OrderProductName field in the OrderDetail table is unnecessary. You have a relation to Produt table and you can access ProductName from there anyway. You can cancel it.

3) OrderNumber in the Order table is unnecessary if it is not a special value rather than unique identity number. You can use OrderID field as an identity of the order.

4) Similary, OrderQuantity and OrderTotalPrice fields might be unnecessary if you have not special reason to place them there. You can calculate order quantity and total price values by means of using basic sql statements that sums related OrderDetail rows.

There are many additional tables can be added depending on your project requirements. I also recommend you to follow MSDN articles and specially CodeProject in order to improve your abstraction skills.


Is there a -w equivalent for isql when already connected? The default output (text) width in sybase isql is 80. It can be changed with the -w flag when isql is started on a command line:

isql -w 200 -S... -U... -P...

Now, I am wondering if there is a possibility to change the text width when I am already connected. I would have expected something like

1> set columnwidth 200
2> go

or something similar. Yet, in the documentation I am unable to find something in this direction. So, does that mean that this is impossible or am I looking in the wrong place?

I think it is not possible to change dynamically this parameter.
I have found this article:

How do I pipe the output of one isql to another.

There, you can look at the other utilities to use in this scenario (sqsh, for example).
Luck.


How to name a constraint which was unnamed during the table creation? I had created the table by specifying the columns as primary key and not null without giving them any constraint names and I have populated the data. I want to give names to them now.

How do I do this in oracle or mysql ?

If I can't do this what's the easiest and next best solution?

When working with Oracle, you can find the constraint names (and other details about constraints) by querying USER_CONSTRAINTS.

Example (Oracle 12c)

-- table with PK and NN constraints
create table test (
  id number
    generated always as identity start with 2000
    primary key
, description varchar2( 100 ) not null
);

-- populate the table (optional)
insert into test ( description )
select 'description_' || to_char( level )
from dual
connect by level <= 5 ;

select * from test ;
ID    DESCRIPTION
2000  description_1
2001  description_2
2002  description_3
2003  description_4
2004  description_5

Find constraints

select table_name, constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'TEST' ;  -- table_name: use upper case!

TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE  SEARCH_CONDITION
TEST        SYS_C0021091     P                NULL
TEST        SYS_C0021089     C                "ID" IS NOT NULL
TEST        SYS_C0021090     C                "DESCRIPTION" IS NOT NULL

Rename the constraints

alter table test
rename constraint SYS_C0021091 to test_pk ;

alter table test
rename constraint SYS_C0021089 to id_nn ;

alter table test
rename  constraint SYS_C0021090 to description_nn ;

Do the constraints have new names now? Yes.

select table_name, constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'TEST' ;

TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE  SEARCH_CONDITION
TEST        TEST_PK          P                NULL
TEST        ID_NN            C                "ID" IS NOT NULL
TEST        DESCRIPTION_NN   C                "DESCRIPTION" IS NOT NULL

Data still intact? Yes.

SQL> select * from test;
ID    DESCRIPTION
2000  description_1
2001  description_2
2002  description_3
2003  description_4
2004  description_5


Build a table of all months.
LEFT JOIN to that table.  Viola, the zeros show up.

Well, you might get NULLs instead of zeros.  So use the IFNULL(application, 0) function.

BTW, MariaDB has a nifty way to generate sequences of numbers; see "seq".


Inner procedure is rolled back but outer procedure is still committed I have an outer procedure which calls 5 different inner procedures something like

Parent procedure(Tran/try/catch)
Inner sp1 (Tran/try/catch)
'
'
Inner sp5 (Tran/try/catch)

But in this case, if one of my inner procedures is rolled back , the procedure is still committed because there are no errors in the outer procedures. How can I overcome this problem ? Thanks !!

Sorry my bad. The code looks something like:
--INNER PROCEDURE

CREATE PROC spInner
AS
BEGIN

DECLARE @transactioncount INT
SET @transactioncount = @@TRANCOUNT

IF (@transactioncount > 0)
BEGIN

SAVE TRANSACTION savepoint1
END
ELSE
BEGIN
    BEGIN TRANSACTION

BEGIN TRY
    DROP TABLE IF EXISTS #temp
    CREATE TABLE #temp (id INT NOT NULL)
    INSERT INTO #temp (id)
    SELECT NULL
    INSERT INTO #temp (id)
    SELECT 1
    INSERT INTO #temp (id)
    SELECT 'A'

IF (@transactioncount = 0)
BEGIN
    COMMIT TRANSACTION
END

END TRY
BEGIN CATCH

IF (@transactioncount = 0)
BEGIN
    ROLLBACK TRANSACTION
END
ELSE IF (XACT_STATE() <> -1)
BEGIN
    ROLLBACK TRANSACTION savepoint1
END

    --logs errors into Error Log table
PRINT ERROR_MESSAGE()
END CATCH
END

--Outer procedure

CREATE PROC spOuter
AS
BEGIN

DECLARE @transactioncount INT
SET @transactioncount = @@TRANCOUNT

IF (@transactioncount > 0)
BEGIN
    SAVE TRANSACTION savepoint2
END
ELSE
BEGIN
    BEGIN TRANSACTION

BEGIN TRY

    Exec dbo.spInner

        IF (@transactioncount = 0)
    BEGIN
        COMMIT TRANSACTION
    END

END TRY
BEGIN CATCH

    IF (@transactioncount = 0)
    BEGIN
        ROLLBACK TRANSACTION
    END
    ELSE IF (XACT_STATE() <> -1)
    BEGIN
        ROLLBACK TRANSACTION savepoint2
    END

        --logs errors into Error Log table
    PRINT ERROR_MESSAGE()
END CATCH

END

In this case the errors caught in the inner procedure is not caught in the outer procedure and outer procedure is committed though there are errors in inner procedures

The problem you're facing is down to how the TRY...CATCH construct handles errors - it consumes them, eats them, and spits nothing back out except for a 0 return code. This is evidenced by a very innocuous line in the docs on TRY...CATCH that says

  Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

This line however appears to be out of date and doesn't give any information as to the THROW statement.

To resolve this problem, you can use the throw statement to re-throw the offending error after your rollback event, as seen in this fiddle (link) or in the abbreviated sample below:

BEGIN TRY
    BEGIN TRANSACTION

    --Your code goes here

    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    --Some other error handling code here

    ROLLBACK TRANSACTION;

    THROW;

END CATCH

The key is knowing what the CATCH block is doing, given the fact that when an error is raised in the TRY block, it is consumed (IE not passed back to the caller) allowing you to handle the error with your own code.

In your case, the CATCH block simply performs a rollback, meaning the error is never raised back to the calling procedure - adding THROW resolves that by manually throwing the error back to the caller (in your case the outer procedure)

If you don't want to use throw, you can also use RAISERROR for some more granular control over the error that is raised.


SQL Trigger Question I have a trigger from a co-worker that isn't working as desired.  When an INSERT is done the insert never completes and the following two lines keep appearing in SQL Profiler over and over.  What is being done wrong?

  SP:StmtStarting   WHILE (SELECT count(*) from INSERTED ) > 0
    22  FINANCE_RPT 773577794   SENDTXT_MSG DLLEMSSQL08     76  2017-03-20
  09:18:26.707   SP:StmtStarting    WITH CTE AS           (     SELECT
  c.PHONE_NUM,c.PROVIDER,b.app_group,b.app_name
        FROM       SENDTXT_GRP b, sendtxt_emp c, sendtxt_emp_grp d
        WHERE c.emp = d.emp
        and (
        b.app_group = d.GRP_NAME or
        b.app_name = d.GRP_NAME
        )     ) SELECT @appname = c.app_name FROM INSERTED i   INNER JOIN CTE c ON i.object_name = c.app_name  where i.MSG_TYPE = 'ERROR';
  --EXEC msdb.dbo.sp_send_dbmail  --
          --@profile_name = 'AdventureWorks2012 Administrator',
          --@recipients = 'danw@Adventure-Works.com',
          --@body = 'Don''t forget to print a report for the sales force.',
          --@subject = 'Reminder';

  22  FINANCE_RPT 773577794   SENDTXT_MSG DLLEMSSQL08     76  2017-03-20
  09:18:26.707

Trigger script:

USE [DATABASE_NAME]
GO

DROP TRIGGER [dbo].[SENDTXT_MSG]
GO

CREATE TRIGGER [dbo].[SENDTXT_MSG]
ON [dbo].[DB_LOG]
AFTER INSERT
AS
DECLARE
@appname varchar(50)
BEGIN
WHILE (SELECT count(*) from INSERTED ) > 0
WITH CTE AS                  (
       SELECT c.PHONE_NUM,c.PROVIDER,b.app_group,b.app_name
      FROM       SENDTXT_GRP b, sendtxt_emp c, sendtxt_emp_grp d
      WHERE c.emp = d.emp
      and (
      b.app_group = d.GRP_NAME or
      b.app_name = d.GRP_NAME
      )
         )
SELECT @appname = c.app_name FROM INSERTED i   INNER JOIN CTE c ON i.object_name = c.app_name  where i.MSG_TYPE = ‘ERROR’;
   --EXEC msdb.dbo.sp_send_dbmail  --
        --@profile_name = 'AdventureWorks2012 Administrator',
        --@recipients = 'danw@Adventure-Works.com',
        --@body = 'Don''t forget to print a report for the sales force.',
        --@subject = 'Reminder';

insert into test (test) values (@appname);
END
GO

The whole code of your trigger is weird. Why checking if SELECT COUNT(*) FROM INSERTED?, I mean, if the trigger is running is because you inserted some row on the table. Then, you are trying to assign posibly multiple values on a variable, and joining with a CTE when it really doesn't seem necessary.

The code seems that it can be replaced with just:

CREATE TRIGGER [dbo].[SENDTXT_MSG] ON [dbo].[db_log] after
INSERT AS
BEGIN

INSERT INTO test(test)
SELECT object_name
FROM INSERTED
WHERE msg_type = 'error';

END


Legacy data migration to Always on Availability group database In our application there is a data conversion process that brings all the legacy data to the production database.

Since our database is on availability group and the migration might bring 100GB + worth of data, my plan was to remove all the secondary replica from the availability group and then set the primary database offline.

Restore the production database into a staging server, then complete the migration/conversion and then restore it back to the Production and syn all the secondary replicas

Please advise if there is a better way to do the migration.

my plan was to remove all the secondary replica from the availability group and then set the primary database offline.
  Restore the production database into a staging server, then complete the migration/conversion

This seems like a really reasonable plan.  You'd likely have lots of issues trying to load 100 GB+ of data to the AG quickly, especially if they are sync replicas.  So this avoids the network and redo bottlenecks you would have likely encountered.

  and then restore it back to the Production and syn all the secondary replicas

It looks like you're on SQL Server 2014, so this also makes sense - as your only option at this point would be to do "manual seeding" (restore the database and log on the secondary replica).

If you were on SQL Server 2016 or higher, I warn against using "automatic seeding" with such a large amount of data.


DB Layout for List of aliases Use Case

In a ticket tracking application, there are ticket-types. A ticket type can have a (primary) slug. Sometimes, a ticket type can have old/legacy aliases for the slug.

It should be straight forward to get the matching ticket-type from a slug.

DB Model

My database model looks like this: A ticket type with N aliases for the slug.

DROP TABLE IF EXISTS ticket_type_slug_alias;
DROP TABLE IF EXISTS ticket_type;

CREATE TABLE ticket_type(
  id INTEGER PRIMARY KEY,
  slug CHARACTER VARYING(50) UNIQUE,
  verbose_name CHARACTER VARYING(64) NOT NULL
);

CREATE TABLE ticket_type_slug_alias(
  id             INTEGER PRIMARY KEY,
  ticket_type_id INTEGER,
  FOREIGN KEY(ticket_type_id) REFERENCES ticket_type(id),
  slug CHARACTER VARYING(50) UNIQUE
);

Problem 1

the slug of ticket_type and ticket_type_alias need to be unique

Problem 2

Fetching the ticket_type by slug needs to access both tables. This is not really a problem, but accessing only one table would be simpler.

Current solution

I think I am going to store the primary slug of the ticket-type in the alias table, too.

This solves problem1 and problem2. But on the other hand it is redundant.

Up to now I try to avoid redundancy.....

And I need a way to ensure that this redundancy does not get out of sync.

Better solutions?

Do you have a better solution (or comment)? Please let me know.

DB: PostgreSQL

I use PostgreSQL. But I think this should not matter for this question.

I think I am going to store the primary slug of the ticket-type in the alias table, too.

Yes. And to avoid redundancy, remove it from the table ticket_type and add a prim boolean column to ticket_type_slug_alias, to identify which of the slug aliases is the primary one. In this way, when looking for the ticket_type id by slug you have only to access one table, while when looking for the primary slug knowing the id you’d need to select FROM ticket_type t JOIN ticket_type_slug_alias a on (a.prim = true and a.id = t.id)


select table and replace column fields by value from multiple tables in mysql I am not more familiar with MySQL.i have been working in mssql. currently, we have a requirement to select table column fields replaced by values from multiple tables.
I tried out with some queries but not getting the proper result since it has some inner options.
please refer to the fiddle.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=15c6f66c363ab16a3437a3911c266bde

Please advise me on this

Thanks in advance

EDIT:
The code (and text) from dbfille:

1:

CREATE TABLE TableData_400
 (id INT,
 created_date DATETIME,
 sample_1 TEXT,
 sample_2 TEXT,
 sample_3 TEXT,
 sample_4_1 TINYINT,
 sample_4_2 TINYINT,
 sample_4_3 TINYINT
 );
INSERT INTO TableData_400 VALUES
('1','2019-05-17 06:19:13','aaa', 'bbb','ccc',0,0,1);

select * from TableData_400

2:

DROP table TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id INT,
 sample_title TEXT
 );

3:

INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4','checkbox');

select * from TableData_elements where Dataid=400

4:

CREATE TABLE TableData_elements_options
 (Dataid INT,
 sample_id INT,
 option_id INT,
 optionss TEXT
                   );
 INSERT INTO TableData_elements_options VALUES
('400',4,1,'First'),
('400',4,2,'second'),
('400',4,3,'Third');

select * from TableData_elements_options where Dataid=400

expected result like

select datas from TableData_400 like the following way ,

 id  created_date            fname Sname Lname first second third
 1    2019-05-17 06:19:13    aaa    bbb   ccc   0     0       1

5:

SELECT  CONCAT( 'SELECT ', GROUP_CONCAT(CONCAT('sample_', sample_id, ' ', sample_title)),
               ' FROM TableData_400'
               )
INTO @sql FROM TableData_elements;
SELECT @sql;

PREPARE myStatement FROM @sql;
EXECUTE myStatement ;
DROP PREPARE myStatement;

You could change sample_id, like below:

DROP TABLE TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id CHAR(10),
 sample_title TEXT
 );
INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4_1','first'),
('400','4_2','second'),
('400','4_3','third');

OR you could change query at the end:

SELECT
  CONCAT( 'SELECT ',
          GROUP_CONCAT(CONCAT('sample_',
                              te.sample_id,
                              CASE WHEN teo.option_id>0 THEN
                                    CONCAT('_',teo.option_id)
                                    ELSE '' END,
                              ' ',
                              sample_title)),
          ' FROM TableData_400' )
FROM TableData_elements te
LEFT JOIN TableData_elements_options teo ON
        teo.Dataid=te.Dataid and
        teo.sample_id=te.sample_id ;

EDIT: (also tak not of optionss):

SELECT
  CONCAT( 'SELECT ',
          GROUP_CONCAT(CONCAT('sample_',
                              te.sample_id,
                              CASE WHEN teo.option_id>0 THEN
                                    CONCAT('_',teo.option_id)
                                    ELSE '' END,
                              ' ',
                              CASE WHEN teo.option_id>0 THEN
                                 teo.optionss
                              ELSE sample_title END)),
          ' FROM TableData_400' )
FROM TableData_elements te
LEFT JOIN TableData_elements_options teo ON
        teo.Dataid=te.Dataid and
        teo.sample_id=te.sample_id ;


Sql Profiler: what does Duration measure for exec sp_execute If I capture an "exec sp_execute" command in SQL Profiler (2012), what exactly does the duration measure?  I'm trying to understand whether or not factors outside of SQL Server (network or the client) can effect the duration.  So if the duration of one call is higher than that of a similar call, is it correct to say "SQL Server took more time to process that call".  Conversely if the duration includes the amount of time it takes the client to read the data, then I can not assume that the problem is on SQL Server (it could have been network or a problem with the client program).

I'm attempting to track down some performance problems, and the answer to this question will hopefully help me determine whether or not the problem is in SQL Server or some other component (network/client/etc.)

In case it makes a difference, I'm talking about executing a very simple select statement like:

SELECT * FROM Customer WHERE Id=? and Product='ABC'

Thanks in advance.

The short answer is that the unit description is mentioned in the answer to this question:

https://stackoverflow.com/questions/2590304/unit-of-measurement-for-duration-column-in-sql-profiler

Also, just to add on to Aaron Bertrand's comment, here is some information on why SQL Profiler should be avoided, and what to use instead (at least through SQL Server 2012):

Performance Impact: Profiler Tracing vs. Server Side SQL Tracing, by Linchi Shea

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

Basically, SQL Profiler can have a large impact on the remote server whereas SQL Trace on the server side has much less impact on the remove server.
Google the following phrases for more info:

Server-Side Tracing and Collection

SQL Server Performance Statistics Using a Server Side Trace

Automating Server Side Tracing in SQL Server

How to identify SQL Server performance issues, by analyzing Profiler output?

Good luck.


Database model about libraries I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books in categories (note: another library doesnt need to have same as categories of books as library where that moderator is working, every other library can have different categories of books .. ).

This picture represents how did I make it. But something is missing, think this is not good solution. I have also another tought.. To link  tables "book" and "category_book" in N:M and than link "book" and "libraries" which from I can get everything I need. But, think it's also not the best solution because every library will in that moment has same categories of books.

Am I missing something or? Haven't met with this kind of problem yet so if you could help me I will be grateful

Whole design ...Note: it's not over, I can't move further beacuse of this problem

Description

I'm synthesising that these are your constraints:

One Book can be in MANY Category's, but only in ONE Category per Library.
Each Library can have ZERO or more Category's, which they do not have to "share" with other Library's.

Solution

This should all be achievable via the following structure (ignoring your wider solution space with users, borrowing, etc):

Note:  The link between library_has_category and book_in_library_category is a composite key of the library AND category keys together.


Multiple concurrent sql dumps in mysql My question is whether it is safe to perform multiple dumps at the same time in mysql, i.e. running:

mysql -u root -p database1 < database1.sql

mysql -u root -p database2 < database2.sql

on the same host and database server of course;

The databases will be empty when the process starts; (just created from cmd)

These are huge dumps if it matters, one file is 10G and the other 100G.

In general terms is safe as long as the databases are "independent". With this I mean each database has references only to itself.

For MySQL in particular VIEWs to missing tables are a problem.

Let me illustrate with an example:

create schema database1;
create table database1.mytable (id int);
create schema database2;
create view database2.myview as select * from database1.mytable;

We take separate dumps: database1.sql and database2.sql

If you try to restore them on a different server, we must pay attention to the restore order, let's say we are starting with empty schemas database1 and database2

This will work:

mysql -u root database1 < database1.sql
mysql -u root database2 < database2.sql

But this will fail:

mysql -u root -p database2 < database2.sql
mysql -u root -p database1 < database1.sql

Because the database2.myview references a table that does not exist.

TLDR: if you are running database restores in parallel and there are references between them you could get into trouble.


Is it possible to make an Update query right after Insert returned new Id's I am using MS Sql Server 2008R2.

For example I need to insert many rows inside TableA and I want to make an Update another TableB with newly inserted Ids from TableA.

Is it possible to do in one SQL query?

OUTPUT Clause

DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason (Name, ModifiedDate)
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

you can then use table for other inserts / updates


Application Access details I have a view and I need to remove a column in the view. Before doing that modification I need to determine which are all the applications that access the view. I need that information so as to make sure that removing the column would not break any application code that access the view.

Is there any DMV or system stored procedure that contains such information? Or is there any system tables that store such information?

If you do not have access to the code from the applications accessing your database, using the Profiler over a reasonable period of time, as commented by @nelson-casanova, is your best option.

If you do have access to the application code, check there.  Also check the definition of any stored procedures, functions, or other views.

I presume you need the column removed for security reasons?  If so, perhaps you can modify your view to use IS_SRVROLEMEMBER and return those columns only when authorized? Copy your view and name it 'original_view', copy your view and edit your columns then and name it 'modified_view.  Now edit your view and call the new views:

IF IS_SRVROLEMEMBER ('role_view_all') = 1
   select * from original_view;
ELSE IF IS_SRVROLEMEMBER ('role_view_all') = 0
   select * from modified_view;

If there is an application that you missed in your Profiler run, you can temporarily add that application's user(s) to the 'role_view_all' role, while developers sort out a workaround or fix their queries.


MySql Replicate different tables to different database I have Database1 on server1 and Database2,Database3 on server2.
Database1 has two tables T1, T2. I want to replicate T1 on Database2 and T2 on Database3.
How can I accomplish it?

I googled but only found examples where replication was performed to only one DB.

You could make server2 as slave of server1. Server2 contains a replica of db1. Into db2 create a view that point to db1.t1, and int db3 create a view that point to db1.t2. I have attached an image to clarify.


Tools to use SQL Server is down I have to go to multiple locations like SQL error log file, Event Log, run sp_whoisactive whenever I am have escalations from customers that SQL Server is slow.
Is there any tool that will quickly give me a snapshot of all 3 in one(SQL Error logfile/Event(app log)/sp_whoisactive)

The BrentOzar team's new PowerBI dashboard functionality for the First Responder kit is pretty awesome.  I use it every day to monitor/diagnose issues and it's super popular as an iPad/Surface dashboard.
https://www.brentozar.com/first-aid/first-responder-kit-power-bi-dashboard/

**I should elaborate that I use their tools to gather data from many different servers into one central reporting server, and that's the one that runs the PowerBI Dashboard.  So I can monitor all of my servers at once and from the same dashboard using these features.  This means that if the offending server crashes, I can still monitor all details until (at most 15 minutes) right before it crashed.

Other than that, I've found Dell/Quest's Spotlight on SQL Server useful too.
https://www.quest.com/products/spotlight-on-sql-server-enterprise/


cant order NULL value properly (mysql) So I got this view in mysql :

CREATE VIEW vote_question_totals
AS
SELECT question_id
, SUM((vote_question_type)='1') question_total_plus
, SUM((vote_question_type)='-1') question_total_minus
, count(*) question_total_mixed
,(SUM((vote_question_type)='1') - SUM((vote_question_type)='-1')) question_total_score
FROM vote_question
GROUP BY question_id;

and I'm doing a RIGHT JOIN with the question table using the question_id

but then I wanna ORDER by question_total_score and this is where it get problematical.
The question with no vote get a question_total_score which is NULL and cant get properly  ordered.
(I would like my NULL row to be considered like a 0)

What should I do ?

SELECT
   `vote_question_totals`.`question_id`,
    COALESCE(question_total_score,0) AS `score`,
   `question`.*
FROM
   `vote_question_totals` RIGHT JOIN `question`
        ON vote_question_totals.question_id = question.question_id
ORDER BY
   `score` DESC


How to unite 2 subqueries to reduce retrieving data from cumbersome tables? There are 3 tables: user, conversation, message.

Table user:

id,
login,
last_login_time (time of last user's login),
...

Table conversation:

id,
user1_id,
user2_id,
message_last_read_id_user1 - id of last read message by user user1_id,
message_last_write_id_user1 - id of last written message by user user1_id,
message_last_read_id_user2 - id of last read message by user user2_id
message_last_write_id_user2 - id  of last written message by user user2_id,
...

Table message:

id,
conversation_id,
poster_id - user's id, who send that message,
msg,
...

The aim to select users who logged during the last hour and select for each of them amount of unread messages:

SELECT u.id, u.login, u.last_login_time,
(
  SELECT count ( * ) FROM conversation c, message m
  WHERE c.user1_id = u.id AND m.conversation_id = c.id AND
        COALESCE(c.message_last_read_id_user1, 0) < COALESCE(message_last_write_id_user2, 0) AND
        m.id > COALESCE(message_last_read_id_user1, 0) AND m.id <= COALESCE(message_last_write_id_user2, 0) AND
        m.poster_id = c.user2_id
) AS unread_user1,
(
  SELECT count ( * ) FROM conversation c, message m
  WHERE user2_id = u.id AND m.conversation_id = c.id AND
        COALESCE(message_last_read_id_user2, 0) < COALESCE(message_last_write_id_user1, 0) AND
    c.user1_id > 0 AND m.id > COALESCE(message_last_read_id_user2, 0) AND m.id <= COALESCE(message_last_write_id_user1, 0) AND
        m.poster_id != c.user2_id
) AS unread_user2
FROM user u WHERE u.last_login_time >= 1452504418;

One of unread_user1 or unread_user2 always will be 0.
Query plan is here http://explain.depesz.com/s/VT6

In that query two subqueries have cross join conversation c, message m and it's very heavy operation. So maybe someone have idea how to unite these subqueries into one?

One idea is to count using an expression:

SELECT u.id, u.login, u.last_login_time
     , COUNT(CASE WHEN COALESCE(c.message_last_read_id_user1, 0)
                     < COALESCE(message_last_write_id_user2, 0)
                   AND m.id > COALESCE(message_last_read_id_user1, 0)
                   AND m.id <= COALESCE(message_last_write_id_user2, 0)
                   AND m.poster_id = c.user2_id
                  THEN 1
             END) as unread_user1
     , COUNT(CASE WHEN COALESCE(message_last_read_id_user2, 0)
                     < COALESCE(message_last_write_id_user1, 0)
                   AND c.user1_id > 0
                   AND m.id > COALESCE(message_last_read_id_user2, 0)
                   AND m.id <= COALESCE(message_last_write_id_user1, 0)
                   AND m.poster_id <> c.user2_id
                  THEN 1
             END) as unread_user2
FROM user u
JOIN conversation c
    ON c.user1_id = u.id
JOIN message m
    ON m.conversation_id = c.id
WHERE u.last_login_time >= 1452504418
GROUP BY u.id, u.login, u.last_login_time;

I might have missed some parts when rewriting, but hopefully you will get some ideas.


What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database? I have a mysql database that has 250 tables.
I would like to run a query to retrieve:
tableName ; FieldName  ; ConstaintName (Primary or foreign key).

I could use information_schema.TABLE_CONSTRAINTS to retrieve the constarint type
but for the inner join I have no idea what should I use.

What is the best approach to achieve this task?

EDIT:
not Constraint name. Should be CONSTRAINT_TYPE instead of ConstaintName. So we should be able to see (Primary or foreign key) only.

You can use INFORMATION_SCHEMA KEY_COLUMN_USAGE table.

SELECT
table_name AS TableName,
column_name AS ColumnName,
Constraint_name as ConstraintName
FROM
information_schema.key_column_usage
WHERE table_schema = 'database_name'

Updated code based on OP's requirement:

SELECT DISTINCT(TC.table_name) AS TableName, KC.column_name as ColumnName,
TC. CONSTRAINT_TYPE AS ConstraintType
FROM
information_schema.table_constraints TC,
information_schema.key_column_usage KC
WHERE
TC.CONSTRAINT_TYPE IN ('FOREIGN KEY', 'PRIMARY KEY')
and KC.table_name = TC.table_name and KC.TABLE_SCHEMA = TC.TABLE_SCHEMA
and KC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
and KC.table_schema = DATABASE();


Directory lookup failed when adding sample database to SQL Server 2012 I am trying to add the AdventureWorks sample database to SQL Server 2012, but I get an error message:

  Msg 5133, Level 16, State 1, Line 1
  Directory lookup for the file "C:\Users\A\Documents\SQL Server Management Studio\Data\AdventureWorks2012_Data.mdf" failed with the operating system error 5(Access is denied.).

The error message seems pretty straightforward: Access is denied. You need to make sure the SQL Server service account has adequate permissions on C:\Users\A\Documents\SQL Server Management Studio\Data\, or better yet, move the MDF (and LDF, if it exists) to the default data/log folders for the instance (where this shouldn't be a problem, and which is a better home for your database files anyway).

You can find the default data/log folders by inspecting:

SELECT physical_name FROM sys.master_files;

Any of the paths presented here should be a valid location to use...


How to swap primary key between records in postgres I can't seem to find a way to achieve this transactionally (or not)

What I need to achieve is non-standard, hence my difficulty finding a solution.

I need to code a data migration tool to "swap" old records with new records in a table, but I have the following requirements / constraints:

Don't lose the old records
Make all references to old records point to new records (that's not just db foreign keys, it's references out of my control in external services, caches, emails, historic data, bookmarks, you name it)
The migration code needs to be schema agnostic, i.e. shall not need to be updated if a new column is added to the table and independent of which other tables reference it.
I can't lock the table for more than the acceptable amount of time it takes to update two records.

So my ideal solution is, well, brute swap their primary keys...

Question is how can I swap the primary keys between two records in postgresql. I am having difficulty finding an approach that does not fail with duplicate key exception, i.e. an approach that runs the "validation" for the update transactionally.

I have tried

UPDATE table
SET id = (CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE id END)

UPDATE table
SET id = CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE id IN (1, 2);

Both failing on duplicate key constraint

I am using PostgreSQL 11.6

SOLVED

Solution for bulk updates for numeric primary keys, thanks to input from the nice people below:

-- disable foreign key constraint validation
BEGIN;
SET session_replication_role='replica';

-- update the pairs of ids to their negative counterparts
WITH query AS ('query to get pairs of ids to swap')
UPDATE table
SET id = -id
WHERE id in (query.id1, query.id2);

-- update the pairs of negated ids to their positive counterparts swapped
WITH query AS ('query to get pairs of ids to swap')
UPDATE table
SET id = CASE id WHEN query.id1 THEN -query.id2
                 WHEN query.id2 THEN -query.id1
                 END
WHERE id in (query.id1, query.id2);

-- enable foreign key constraint validation
SET session_replication_role='original';
COMMIT;

You could use a deferred constraint. For that you need to drop and re-create the primary key:

CREATE UNIQUE INDEX mytable_primkey ON mytable (id);
ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE mytable ADD PRIMARY KEY USING INDEX mytable_primkey
   DEFERRABLE INITIALLY DEFERRED;

The update itself could then be done like that:

UPDATE mytable SET id = 3 - id WHERE id IN (1, 2);

Here 1 and 2 are used as examples, you can do that with any numbers.

If you cannot afford the down time required for adding a deferred primary key, you could do it with one more update like this;

BEGIN;
UPDATE mytable SET id = 0 WHERE id = 1;
UPDATE mytable SET id = 1 WHERE id = 2;
UPDATE mytable SET id = 2 WHERE id = 0;
COMMIT;

Here 0 is an arbitrary value that is not used as a value for id.


If MAX(id) != COUNT(id), does that mean rows have been deleted? The question pretty much says it all. We're talking MySQL here, version 5.1.

What I'm wondering is whether this would strictly means rows have been deleted, or if there are other possible scenarios that could cause this: weird locking or race conditions, etc. that force the table's ID counter to increment multiple times for a single insertion.

I assume you are asking about columns with the AUTO_INCREMENT property.

No, deleted rows is not the only reason, there are several others:

inserts that did not succeed (due to duplicate unique or primary key violations or other reasons).
inserts inside transactions that did not commit and were rolled back.
inserts where the id was explicitely defined (and id numbers were skipped or negative values added). In the second case, you can have MAX(id) less than COUNT(id) as a result.
variation of the previous case: column was not AUTO_INCREMENT when the table was created - so there were explicit inserts for some period - and then modified.
updates of the auto-incrementing column.
the id was set to start from a value different than the default 1.
the auto_increment step setting (system variable: auto_increment_increment) of the server is not 1 (or had been changed to something else than 1 for some period).
and (something that is usually neglected because the id is usually the primary key), the column was not defined as the PRIMARY or UNIQUE key. That allows duplicates and one more case where the MAX can be lower than the COUNT.
a variation of the above is (with MyISAM engine) an auto-incrementing column that is part of a composite primary key.


SELECT very slow when no results and a LIMIT is specified I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified.

If the number of results is greater than 0 then Postgres uses the index and the result return in ~ 1ms. This seems to always be true as far as I can find.

If the number of results is 0 and no LIMIT is used, Postgres uses the index and the results return in ~ 1ms

If the number of results is 0 and a LIMIT is specified Postgres does a sequential scan and the results take ~ 13,000 ms.

Why doesn't PostgreSQL use the index in the last case?

Cardinalities:

~ 21 million rows total.
~ 3 million rows WHERE related_id=1
~ 3 million rows WHERE related_id=1 AND platform=p1
2 rows WHERE related_id=1 AND platform=p2
0 rows WHERE related_id=1 AND platform=p3
~ 8 million rows WHERE platform=p2

Postgres Version: 9.4.6

Table Schema:

CREATE TYPE platforms AS ENUM ('p1', 'p2', 'p3');

CREATE TABLE mytable (
    id bigint NOT NULL DEFAULT nextval('mytable_sq'::regclass),
    related_id integer NOT NULL,
    platform platforms NOT NULL DEFAULT 'default'::platforms,
    name character varying(200) NOT NULL,
    CONSTRAINT mytable_pkey PRIMARY KEY (id),
    CONSTRAINT mytable_related_id_fkey FOREIGN KEY (related_id)
         REFERENCES related (id)
);

CREATE INDEX related_id__platform__index ON mytable (related_id, platform);
CREATE UNIQUE INDEX some_other_index ON mytable (related_id, lower(name::text));

Queries and plans:

This query returns 0 rows:

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p2'::platforms
LIMIT 20;

 Limit  (cost=0.00..14.07 rows=20 width=737) (actual time=12863.465..12863.465 rows=0 loops=1)
    ->  Seq Scan on mytable  (cost=0.00..1492790.47 rows=2122653 width=737) (actual time=12863.464..12863.464 rows=0 loops=1)
          Filter: ((related_id = 1) AND (platform = 'p2'::platforms))
          Rows Removed by Filter: 21076656
 Planning time: 3.540 ms
 Execution time: 12868.190 ms

This query also returns 0 rows:

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p2'::platforms;

 Bitmap Heap Scan on mytable  (cost=60533.63..1295799.94 rows=2122653 width=737) (actual time=0.890..0.890 rows=0 loops=1)
 Recheck Cond: ((related_id = 1) AND (platform = 'p2'::platforms))
  ->  Bitmap Index Scan on related_id__platform__index  (cost=0.00..60002.97 rows=2122653 width=0) (actual time=0.888..0.888 rows=0 loops=1)
         Index Cond: ((related_id = 1) AND (platform = 'p2'::platforms))
 Planning time: 0.827 ms
 Execution time: 1.104 ms

This query returns 20 rows (without LIMIT it would be upwards of 2 million rows):

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p1'::platforms
LIMIT 20;

 Limit  (cost=0.44..70.95 rows=20 width=737) (actual time=0.759..0.995 rows=20 loops=1)
   ->  Index Scan using related_id__platform__index on mytable  (cost=0.44..1217669.26 rows=345388 width=737) (actual time=0.759..0.993 rows=20 loops=1)
         Index Cond: ((related_id = 1) AND (platform = 'p1'::platforms))
 Planning time: 5.776 ms
 Execution time: 2.476 ms

This query returns 2 rows:

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p3'::platforms LIMIT 20;

 Limit  (cost=0.44..80.37 rows=20 width=737) (actual time=0.014..0.016 rows=2 loops=1)
   ->  Index Scan using related_id__platform__index on mytable  (cost=0.44..99497.62 rows=24894 width=737) (actual time=0.014..0.015 rows=2 loops=1)
         Index Cond: ((related_id = 1) AND (platform = 'p3'::platforms))
 Planning time: 0.972 ms
 Execution time: 0.123 ms

Postgres does a bad job estimating the frequency of the combination of predicates in your query:

SELECT * FROM tbl
WHERE  related_id = 1 AND platform = 'p2'::platforms
LIMIT  20;

Each of your predicates is not very selective on its own - that information is available to Postgres ("most common values") - assuming your statistics are up to date:

  ~ 21 million rows total.
  ~ 3 million rows WHERE related_id=1
  ~ 8 million rows WHERE platform=p2

IOW, ~ every 7th row passes the 1st filter and ~ every 3rd row passes the 2nd. Postgres does the (naive) math and expects that roughly every 20th row qualifies. Since there is no ORDER BY, any 20 qualifying rows will do. The fastest way should be to scan the table sequentially and be done after ~ 400 rows - only a few data pages, very cheap.

Using any indexes adds some extra cost, Postgres needs to scan index and table. (Exception: index-only scans, which are not possible here for your SELECT *). That will only pay if Postgres would otherwise have to read enough additional pages to estimate that to be more expensive. That's how I would explain that you see a sequential scan for a small LIMIT, but a bitmap index scan for a big (or no) LIMIT.

Unfortunately, the combination of your predicates turns out to be unexpectedly rare. Postgres has to scan the whole table just to find only 2 qualifying rows. (The index would actually be much cheaper in any case.)

  2 rows WHERE related_id=1 AND platform=p2

The combined frequency of values in multiple columns is not available to Postgres. Think about it: gathering statistics like that would quickly get out of hand.

A very simple and efficient solution for this particular case: Create a partial index:

CREATE INDEX related_id_1_platform_2_idx ON tbl (id)
WHERE  related_id = 1 AND platform = 'p2'::platforms;

Not only will this super-tiny index (2 rows) be a perfect match for your query, it will also make a count estimate available for your particular combination (entry in pg_class.reltuples). The actual index column is mostly irrelevant for this one, pick a small column, typically best to make it the PK.

If one of the two predicates can change, there is a more generic approach. Say, if related_id = 1 is the stable condition, then create:

CREATE INDEX related_id_1_idx ON tbl (platform)
WHERE  related_id = 1;

The index column is relevant again. This may not be enough to tip the scales because Postgres only collects complete statistics for index columns of functional indexes (else it relies on the statistics of the underlying table). I propose:

CREATE INDEX related_id_1_func_idx ON tbl ((platform::text::platforms))  -- double parens!
WHERE  related_id = 1;

Note the extra pair of parentheses - a syntactical necessity for the cast shorthand.
The expression platform::text::platforms doesn't actually change anything - it casts your enum to text and back. But it makes Postgres collect full statistics on the (presumed) new values.

Now, (after ANALYZE tbl) we have full statistics including most common values of platform for related_id = 1.

Check with:

SELECT *
FROM   pg_stats
WHERE  schemaname = 'public'  -- actual schema
AND    tablename  = 'related_id_1_func_idx';  -- actual idx name

And Postgres should choose the index for your case - if you repeat the same expression in your query. So:

SELECT ...
WHERE related_id = 1 AND platform::text::platforms = 'p2'::platforms;

Related:

Index that is not used, yet influences query

About most common values in the Postgres statistics:

Speeding up a GROUP BY, HAVING COUNT query


Choosing MyISAM over InnoDB for these project requirements; and long term options Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.

My project's aim is to let users search a (huge) database of variety of products.

Each product is present under a category.
Each product will have 10 to 100 'specs' or 'features' by which the users will search.

The most common usecase is:

User clicks on a category; then clicks various sub-categories if required.
User starts off with 1 or 2 criteria and searches for products.
User then keeps adding more criteria to the search to narrow down on the product.

I have three main tables 'products', 'features_enum' and 'features'. It is very important to let the data-entry users, create new 'features' on the fly, for the products - hence I am using EAV (anti)pattern.

Here are the structures of the tables:

'products'
    ID(PK), TITLE, CATEGORY
    (Indexed by CATEGORY)
'features_enum'
    ID(PK), TITLE
'features'
    P_ID, F_ID, VAL
    (Indexed by P_ID and then F_ID)

A sample format of my main search query:

SELECT
  p.ID,
  p.TITLE PROD_TITLE,
  fe.TITLE FEATURE_TITLE,
  f.VAL
FROM
  products p, features f, features_enum fe
WHERE
  p.CATEGORY = 57 AND
  p.ID = f.P_ID AND
  f.F_ID = fe.ID AND
  (
    (f.F_ID  = 1 AND f.VAL = 'Val1') AND
    (f.F_ID  = 2 AND f.VAL = 'Val2') AND
    ...
    (f.F_ID  = N AND f.VAL = 'ValN') AND
  )

My Experimentation So Far:

Due to my limited knowledge and experience in DBs, I hit a wall with theoretical planning. So, I generated a large set of test data to simply see what will work. All three tables had 500,000 test rows. Here are the avg. run times of the main search query:

InnoDB without indexing: 90s.
InnoDB with indexing: 15s. 0.3s after buffer pool size increase
MyISAM without indexing: 9s.
MyISAM with indexing: 0.7s.
MyISAM with indexing + FIXED row type: 0.16s.

Test machine - Pentium 4 1.9GHz, 1.5GB RAM, IDE HDD, Win7.

I have basically not done anything to optimize other than indexing. So there might be a ton of things that I missed, that could have made InnoDB run faster. InnoDB buffer pool size was set to 16M (!!); I incremented it to 128M. Now, InnoDB is really fast. So one big reason for me leaning toward MyISAM is now gone. Maybe there is more that I can do.

Some points and long term usage estimates about the project:

20 new products added daily, at roughly 20 x 100'specs' = 2000 record writes per day.
1,000,000 page visits, and in worst case - same amount of search query runs per day.
Total record count for the tables is expected to reach 5,000,000 each.
Writes will be made by a semi-controlled group of people, where as read is public.
There are no complex 'transaction' type writes. The most complex write I can think of right now is - [one product row + 100 feature rows max] - at one shot
Require only a couple of constraints, but if necessitated by choice of MyISAM, I can enforce them at the application level itself.
DB access from other parts of the app - user registration, authentication etc.. will be few and far in between, I don't think they will have much effect.

Given all of that, I am biased towards MyISAM. But I need input from people already experienced in MySQL.

Questions:

If the InnoDB run times are wrong/surprising, what have I missed in
testing? Increasing buffer pool size dramatically increased performance. See above.
If not, considering all of the above, is MyISAM really good
choice in the long run?
If MyISAM too turns out to be a bad choice
later, how easily can I restructure the database? What options do I
have?

On a side note:

If choosing EAV was bad, what other architecture can I use for this project?

InnoDB and MyISAM each have their strengths and weaknesses.

May 03, 2012 : Which is faster, InnoDB or MyISAM?
Sep 20, 2011 : Best of MyISAM and InnoDB

If you have enough RAM, I would choose InnoDB because it caches data and index pages in the Buffer Pool. MyISAM only caches index pages in the Key Cache.

MyISAM tables experience full table locks for each INSERT, UPDATE, and DELETE. MyISAM tables always require disk access for data.

InnoDB tables always incur disk I/O in the following areas:

Double Write Buffer : Changes are posted in ibdata1 to avoid OS caching
Insert Buffer : Changes to Secondary (non-Unique) Indexes as posted in ibdata1
Data and Indexes

With innodb_file_per_table = 0, changes are written to ibdata1
With innodb_file_per_table = 1, changes are written to .ibd tablespace file. Read I/O against ibdata1 still necessary to crosscheck table metadata

SUMMARY

In an environment with the following:

heavy writes
heavy reads
tons of RAM
heavy connections

I would always choose InnoDB. Please check out my other post about InnoDB over MyISAM : When to switch from MyISAM to InnoDB?

When would I every choose MyISAM?

Under the following scenario

Using MySQL Replication
Master with all InnoDB
Slave with all tables converted to MyISAM
ALTER TABLE ... ROW_FORMAT=Fixed for all tables on the Slave

Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT-Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR is treated as CHAR this shortening access time for data retrieval.

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

On the other hand, InnoDB has to interact with multiple files (ibdata1, serveral read/write threads upon the .ibd of the InnoDB table).


how to use substring with a delimiter Employee_M

City        Emp_Num_ID
Cleveland   2164445437/FTTD/JAMES, TYLER/139561151
Tokyo       1261120379/FTTD/BOYD, ADAMS/14468140
Marakech    4049838896/FTTD/SMITH, TULY E/13956144
Houston     7980151429/FTTD/NEARY, HARTMAN/14215411

I'm trying to extract all digits after the third / with substring

select substr(Emp_Num_ID,/,10) as Emp_ID
from Employee_M

sometimes we can have between 4 to 10 charterers after the third / so I chose 10 for the length.

any idea how to fix that.

you can use instr function to get the position of last /
-1 = you are looking from the end of the string
than you add 1, so you start position will be the first number after /
you do not need the third parameter in substr if you need substring till the end of a string

select substr(Emp_Num_ID,instr(Emp_Num_ID,'/',-1)+1) as Emp_ID from Employee_M


Migrate database from one server to another without downtime I want to migrate a MySQL database to another MySQL database (different server and only terminal access). I believe I can do a dump on it, but then I won't have the latest data. How do I make sure that I have the latest data if I am not allowed to have downtime? Is it possible?

Please excuse my ignorance if any (newbie in dba). I have read all the suggestions and duplicate questions before creating this new one. But none of them seems to be the one I am looking for.

In general I don't think it's possible to move a database without any downtime with MySQL standard tools.

However you can try to use mysqldump and mysqlbinlog to minimize "data loss": see section "Example: mysqldump + mysqlbinlog for Backup and Restore" in https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html


How to SUM DISTINCT by other columns? I have a CTE that resulting something like this:
i x y
A 4 ...
A 4 ...
A 4 ...
A 4 ...
B 2 ...
B 2 ...
C 3 ...

And already have a query that uses most of them:
WITH cte AS ( ... ) -- above is the example result of the cte
SELECT
    COUNT(DISTINCT (CASE WHEN ... THEN i END)) bla_count1
  , COUNT(DISTINCT (CASE WHEN ... THEN i END)) bla_count2
  , SUM(x)/COUNT(*) bla_average
  -- and much more similar to above
  , -- how to get sum of A, B, C (4+2+3)
FROM cte

How to get the SUM of x DISTINCT by i (4+2+3)?
Modified Fiddle

Nevermind, found a way, by adding one more CTE
WITH cte AS ( ... )
, sum1 AS (
  SELECT i, MAX(x) tot -- or AVERAGE/MIN
  FROM cte
  GROUP BY 1
)
SELECT ...
   , (SELECT SUM(tot) FROM sum1) AS sum1
FROM cte


Does UPDLOCK guarantee concurrency I have a table called tblOrderNumber which has 1 row and 1 column.  This table stores what the next order number will be for my ecommerce website.  It is ABSOLUTELY VITAL that the same order number is not used more than once.  Currently the team are using this stored procedure and it seems to work fine:

My question is does UPDLOCK guarantee this? I would have thought a Read Lock is required on the SELECT too (in the unlikely case that 2 orders are placed within a millisecond of each other and the first one has not done the UPDATE before the second one had done a SELECT, as I understand there is no read lock in this procedure)?

DECLARE @NextOrderNumber INT

BEGIN TRANSACTION

SELECT @NextOrderNumber = NextOrderNumber
FROM  tblOrderNumber (UPDLOCK)

UPDATE tblOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1

COMMIT

SELECT @NextOrderNumber

--Customer Implementation (Would UPDLOCK OR SERIALIZABLE be better here as I don't think we need to lock the full table?)

UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE)
SET @NextOrderNumber = NextOrderNumber,
    NextOrderNumber = NextOrderNumber + 1;
WHERE CustomerId=@CustomerId

I am using SQL Server 2014 but I will be changing to SQL Azure soon.

Short Answer
The update lock is sufficient, but you can achieve what you want more simply with:
UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE)
SET @NextOrderNumber = NextOrderNumber,
    NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID;

The WITH (SERIALIZABLE) hint is not strictly required if there is a unique index on CustomerID.
Longer Answer
The update lock hint is sufficient in the code provided. Only one transaction can obtain an update (U) lock on a resource at a time, and update locks are held to the end of the transaction. The update lock is converted to an exclusive (X) lock just before the change is made. The exclusive lock is also held to the end of the transaction.
Taking an update lock when reading therefore provides the concurrency guarantees you are looking for. To be clear: Once the update lock is acquired (by the select), no other transaction can acquire an update lock on the same resource until the first transaction has committed or aborted.
You could also run the transaction (or single update statement) at the SERIALIZABLE isolation level without hints. After all, the guarantee you are looking for is that the transaction should execute according to a serializable schedule. If you are uncomfortable relying on knowledge of locking internals, specifying the desired isolation level, and letting SQL Server handle the details, is probably simpler.
Obviously you would need code to handle errors or possible deadlocks as well. I assume you omitted this from your example. A belt-and-braces approach would also SET XACT_ABORT ON for the procedure to ensure almost all possible errors will abort the transaction instead of silently continuing.
It would also be possible to write a robust locking implementation manually using application locks (with sp_getapplock and sp_releaseapplock), but in all honesty using the built-in serializable isolation level is probably simplest.
For more information, see my series of articles:

SQL Server Isolation Levels


Milliseconds to datetime I have this statement

EXEC [spProject_Update] @ProjectID = 496
,@Name = 'Hangman Bollywood iPhone'
,@Description = ''
,@EstimateTime = 2674800000

I will get milliseconds in @EstimateTime.
How can I convert this to datetime in SQL Server?

DATETIME is used to store an instant ("point in time") at a particular granularity (approx 300 milliseconds IIRC).

The value you wish to store is an interval (or is it a duration?) in milliseconds. SQL Server lacks an interval data type.

While both are temporal data they have quite different requirements. I suggest you use an numeric column (e.g. INTEGER) to store the interval and use metadata (e.g. an appropriate name) to convey the fact the granularity is one millisecond.


Shrink data file corruption risks? Are there any risks of data file corruption resulting from shrinking sql data files?

No, shrink is safe. Unless you hit some bug in SQL Server of course, but that can happen whatever you do. The shrinkage is done as a series of mini transactions and it is also safe to terminate the operation.
There are of course other disadvantages of doing shrink, but I take it that you are aware of those.


SQL SERVER availability groups and virtualized HA vSphere and Hyper-v are meant to complement AGs by adding additional HA. However, if I lose a VM hosting a primary sync  replica, wouldn't vSphere/Hyper-v both migrate the failed VM to another host at the sane time as SQL Server automatically fails over to the Sync secondary replica?

Questions
Who would alway win?
Can this screw up SQL?
Wouldn't it be easier to just use SQL for HA (and DR) and migrate the failed VM over to a surviving host  after SQL has switched over to a secondary replica?

Any comments/experience appreciated.

It depends on what you mean by "lose a VM."  If you have a hardware failure and the VM is effectively powered off, a failover will occur if the failed VM was the primary.  When the failed server is moved and rebooted, it will join the cluster before trying to become primary again, and when it does it will discover that the other node is primary, and it then resolves its own state to secondary.

If there is no power off situation, but the VM disappears from the network for several seconds--this can be bad as it can cause failover to occur, but since the VM that is moving is effectively paused, it does rejoin the cluster to resolve its role--it just picks up where it left off.  The cluster will quickly detect that there are two primaries and the VM that moved will be forced into a secondary role, but if there was any data committed from the transaction logs, you can end up with the databases out of sync.  If this happens, you have to remove the replica database that's out of sync, restore from backup, etc., and then join it back in.  See How to remove a secondary database from an availability group and rejoin it.

Another thing with VMs that can result in this mess is VM snapshot backups, or VMotioning--anything that can cause the server to be paused.  For servers with 100 GB storage--usually not a problem.  But when you start getting into terrabytes, the server can be paused for several seconds to do these operations.  So you may need to adjust your backup strategy to accomodate--see this for more.


T-SQL: How create job that run monthly I already know how to schedule monthly jobs using SQL Server Agent > Jobs, I now would like to learn how to schedule such job using T-SQL.

I know how to schedule a job daily at a specific time:

CREATE PROCEDURE MyTask
 AS
BEGIN
    SET NOCOUNT ON;
    -- Now is set to run evry day at 23:00
    -- But I need to run it every 1th of the month
    declare @delayTime nvarchar(50)
    set @delayTime = '23:00'

    while 1 = 1
    begin
        waitfor time @delayTime
        begin
            -- Here the query I want to run every month
            select * from AdventureWorks2012.dbo.AWBuildVersion;
        end
    end
END

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'MyTask',
                @OptionName = 'startup',
                @OptionValue = 'on'

On this Microsoft guide I see I can use @freq_type = 16 but I don't know how to set it and an example would be much appreciated

To schedule a job to run once a month, in this case midnight on the first day:

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get Date',
    @enabled=1,
    @notify_level_eventlog=0,
    @notify_level_email=0,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get today''s date',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_success_step_id=0,
    @on_fail_action=2,
    @on_fail_step_id=0,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'Select getdate()',
    @database_name=N'master',
    @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'First day of month',
    @enabled=1,
    @freq_type=16,
    @freq_interval=1,
    @freq_subday_type=1,
    @freq_subday_interval=0,
    @freq_relative_interval=0,
    @freq_recurrence_factor=1,
    @active_start_date=20180312,
    @active_end_date=99991231,
    @active_start_time=0,
    @active_end_time=235959,
    @schedule_uid=N'7e73cf5c-8036-41a2-af0d-3b2151862684'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO


Why do we need to backup Oracle archive logs along with datafiles Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that

  Archived redo logs are the key to
  successful media recovery. Back them
  up regularly.

But, I wonder, why backups of archive logs is so important.
Would it be possible to do point-in-time recovery just using regular RMAN full and
incremental datafile backups.

No, you still need archived redo logs. An RMAN backup is not the same as a cold backup; when you restore it you need to apply all the redo generated from the time you started the backup until the time the backup completed in order to get the SCN consistent across all the datafiles and the controlfile.

Let us consider the old fashioned hot backup. In this mode, entire changed database blocks are written to the redo log stream, and the file is copied in the OS. This causes fractured blocks, i.e. the block has changed during the read by the backup. So to recover the database, we restore the DBFs, then the recovery process overlays the changed blocks from the archived redo logs onto the fractured blocks in the file, and we get back to a consistent database.

In an RMAN backup the problem of fractured blocks is eliminated because the blocks are read by Oracle via the SGA not by a Unix tool like cp. However the DBF changes between the time the first block is read and the last block, so it is the same problem, just at a larger scale. That is to say, the SCN changes between the first and last blocks in a backup. The archived redo logs cover this as well.

RMAN gives you the manageability of the catalog, and a means to do hot backups without needing to generate redo at a much higher rate (which puts its own stress on the disks, the backup system etc). However BACKUP DATABASE will not give you a consistent database in and of itself; the only way to do that in Oracle is the cold backup.


Sudden High CPU Usage On SQL Server 2008R2 Our SQL Server is experiencing sudden high CPU usage since 12:30am today. This server is used internally so nobody should be accessing the server at that time.

I'm not sure why 12 hours later the CPU usage is still at 100%. Looking at the resource monitor on the server it is sqlservr.exe that is using 99% of the CPU.

We have re-index jobs that kick off at 12:30am every other morning, but those jobs were completed. These jobs also update the statistics via DBCC DBREINDEX.

I've been researching for the past 3 hours, but most of the answers have been on tuning queries or troubleshooting a single query that is hogging the CPU. ALL of our queries have been slow today, which is very abnormal.. I've checked multiple times for a single query, but haven't found anything.

I've run an sp_who2 'Active', but there are no processes in the suspended state.

I have tried several different DMV and other queries to pinpoint the issue, but haven't been able to find anything..

It seems like SQL Server is hung up and needs a restart, but I'd like to avoid that at all costs...

Answer converted from comment:

sp_whoisactive shows the following output:

Restarting the SQL Server Service resolved the issue. I think I know why thanks to sp_whoisactive. My theory is we have a query that was running in a loop (the top 8 rows) and that got stuck during the DBCC DBREINDEX that occurred at 12:30AM. This query was using up all of the CPU since it was stuck in that query... Once the server was rebooted, there are hardly any records in the sp_whoisactive. MUCH BETTER!!


How to find out the content differences between 2 SQL tables and produce sync SQL How do I find out the differences in data between the two tables that have exact schema, and how to produce synchronisation SQL to get the union results (without duplicates) ?

These are the 2 tables:

SOURCE01.dbo.Customers (31,022 rows)

TARGET01.dbo.Customers (29,300 rows)

The schema of each table is :

[CustomerId] : nvarchar(255)
[CustomerSerializedProfile]: nvarchar(max)
[CreatedDatetime] : DateTime

Other than tablediff and powershell mentioned in the previous answers, you can also use SQL with the UNION ALL statement to find the records that don’t match in 2 identical tables:

SELECT MIN(TableName) AS TableName
   ,ID
   ,NAME
   ,lastname
   ,Address
   ,City
FROM (
SELECT 'Table A' AS TableName
    ,Customers.id
    ,Customers.NAME
    ,Customers.lastname
    ,Customers.Address
    ,Customers.City
FROM Customers

UNION ALL

SELECT 'Table B' AS TableName
    ,CustomersOld.id
    ,CustomersOld.NAME
    ,CustomersOld.lastname
    ,CustomersOld.Address
    ,CustomersOld.City
FROM CustomersOld
) tmp
GROUP BY ID
   ,NAME
   ,lastname
   ,Address
   ,City
HAVING COUNT(*) = 1
ORDER BY id;

Another option you can try is using Data Compare in Visual Studio itself. It compares data in the source database and the target database and creates a synchronization script for the tables you’ve selected for synchronization.

And last, but not least, you can use SQL data comparison tool - ApexSQL Data Diff, to set all synchronization options, map the tables and columns with different names, create your own keys for comparison in the GUI.
You can schedule it to run unattended and all you have to do is check SQL Server job history in the morning.
If you need more details regarding these options, I recommend reading this article: http://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/


Filtering down specific criteria for all rows and return only one row for each column with the same value I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time.

Table Definitions:

CREATE TABLE `house_leases` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `house_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `house_lease_terms` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `house_lease_id` int(10) unsigned NOT NULL,
  `date_start` datetime NOT NULL,
  `date_end` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `house_lease_terms_house_lease_id_foreign` (`house_lease_id`),
  CONSTRAINT `house_lease_terms_house_lease_id_foreign` FOREIGN KEY (`house_lease_id`) REFERENCES `house_leases` (`id`) ON DELETE CASCADE
);

As you can see the house_lease_terms.house_lease_id corresponds to a specific house_lease, however there can be multiple rows with the same house_lease_id.

The rules for determining the "active" terms are:

date_start <= NOW() AND date_end > NOW() OR date_end IS NULL

If no rows are returned then the "active" terms must be in the future, so then the rules change to be:

date_start > NOW()

We order by date_start DESC if the terms are not in the future since multiple rows could be returned we want the latest date_start at the top of the results. Otherwise we sort by date_start ASC because we want the closest date_start to now to be at the top.

I then limit by 1 to get only one result and that row is considered the "active" terms. If no results come back, then there are no "active" terms.

I have a SQL statement that has this logic for getting a specific house_lease_id. That looks like this:

SELECT * FROM house_lease_terms
WHERE
CASE
    WHEN
        (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1)
    THEN
        date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL)
    ELSE
        date_start > NOW()
END
AND house_lease_id = 1
ORDER BY
    IF(
        (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1),
        unix_timestamp(date_start),
        -unix_timestamp(date_start)
    ) DESC
LIMIT 1;

This statement works, but I wish there was a better way (more efficient) of fetching the "active" terms for a specific house_lease_id (If you know a better solution please share).

Now I need to have a query that will fetch the "active" terms for all the different house_lease_id's.

I don't want any type of custom MySQL function or stored procedure to do this. I don't know where to start to create this query. I figure I can use the query from above in some sub select or join, but am not sure how I would do so.

Any help will be appreciated!

SQLFiddle with data: http://sqlfiddle.com/#!9/cab159/2/0

Must be missing something, but isn't this simply what you want?

SELECT house_lease_id, MIN(date_start)
FROM house_lease_terms
WHERE date_end > NOW() OR date_end IS NULL
GROUP BY house_lease_id

The earliest term that hasn't ended yet.


Understanding optimisation of complex SELECT queries I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table.

I want records that match ((criterion 1 or criterion 2) and criterion 3).
I want the criteria statements to add columns to the results.
I need a standard way to assemble the queries because I'm making a 'search builder'; so I can't optimise every combination. It can be assumed that each criterion will return zero or one row.

EG1: I first tried

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       (criterion3 SELECT) c3
 WHERE people.id=c3.pid
   AND ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

This was far too slow - it never actually returned records in the minute I allowed it!

EG2:Next I tried

SELECT people.*
  FROM people,
       (       (criterion1 SELECT)
         UNION (criterion2 SELECT) ) c12
 WHERE people.id=c12.pid
   AND people.id IN (criterion3 SELECT)

This one returns 2000+ rows in 0.6s, which is acceptable. But you can't add any columns from criterion 3, and can't access columns from c1 and c2 either, really, since one will mask another.

EG3:Out of interest, I moved the c12 into the WHERE clause:

SELECT people.*
  FROM people
 WHERE id IN (criterion3 SELECT)
   AND id IN ( (criterion1 SELECT)
               UNION (criterion2 SELECT) )

but again, this threw it for well over a minute before I killed the query.

EG4: So I returned to the EG2 code, and did this ugly thing:

SELECT *
  FROM ( all the EG2 SQL ) src
       LEFT JOIN (criterion1 SELECT) c1 ON src.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON src.id=c2.pid

So the c1 and c2 queries are added in twice! This returns the (same number of) results, with the extra fields, in 6s. Too slow, really, but at least it runs. And really ugly!

Can anyone give me any pointers to explain any of this? Why is EG3 massively faster than EG1/EG4 when they're all doing the same thing? Why should running the queries twice (EG4) be faster than running them once (EG1)?!

EDIT: Using MySQL 5.1, InnoDB tables.

Why there are massive differences in performance between the queries?
(assuming that you have at least defined indexes for the columns used in the subquery criteria and in the joining conditions)

Basically because MySQL optimizer is not smart enough to figure out that all these queries are equivalent. So, it probably produces different execution plans for the different queries. If you do not have properly defined FOREIGN KEY constraints, the optimizer may be actually right, there is no guarantee that the queries return identical results.

So, what to do to increase performance and have a standard way of writing this type of query variants?

Several things that affect MySQL queries performance:

Do not use id IN (SELECT subquery) if you can avoid it. It's not very well optimized in most MySQL versions (see point 6 below). Use joins if you can.
Replace UNION with UNION ALL if that doesn't change the result set (you could do that in EG3 query).
Don't use implicit joins (with commas in the FROM clause and the joining condition in the WHERE clause). Use explicit JOIN syntax. As an example, your EG1 is actually the same as (this is not for performance but for consistence):

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       JOIN (criterion3 SELECT) c3 ON people.id=c3.pid
 WHERE ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

The above query has an OR condition that is relevant to 2 tables (and their joins to the people, so 3 tables actually). This is usually not very good perfomance-wise.
You can try rewriting using EXISTS. This will make your queries easier to write and it may help with performance, too:

SELECT people.*
  FROM people
 WHERE EXISTS
         (criterion3 SELECT modified with `people.id = some_table.pid`)
   AND ( EXISTS
           (criterion1 SELECT modified with `people.id = some_table.pid`)
      OR EXISTS
           (criterion2 SELECT modified with `people.id = some_table.pid`)
       ) ;

If your host allows it, try/test MariaDB (it's a MySQL fork-replacement) that has introduced several improvements in queries execution in its latest versions. The optimizer will be a bit smarter identifying equivalent queries and certainly smarter as it has some new algorithms implemented that affect queries will joins, subqueries among other things.

MySQL 5.6 has also a few improvements in the optimizer but it's not available yet as a stable release.