MySQL Create Table with Partition Slow on server machine Following create table statement with Partition

runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk
runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz , 615MB Ram, single disk
While takes 100 Seconds on my server machine Windows server 2008 R2 Standard, xeon 2.4 2 Processors, 32 GB Ram, ATA SCSI Mirror drives

I am not able to understand why there is so much performance difference ..

CREATE TABLE IF NOT EXISTS `TestSpeed`.`CTM` (
  `ServerID` BIGINT NOT NULL,
  `ClientID` BIGINT NOT NULL,
  `CommunityID` INT NOT NULL,
  PRIMARY KEY (`ClientID`,`CommunityID`),
  UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
  INDEX `IX_CTM_ClientID` (`ClientID` ASC))
PARTITION BY HASH (CommunityID) PARTITIONS 300;

ANALYTICAL EXPERIMENT

Looking at CREATE TABLE and seeing that you are creating 300 partitions, I need you to perform an experiment. Please run the following script:

DROP DATABASE IF EXISTS rolandos_test;
CREATE DATABASE rolandos_test;
USE rolandos_test
SET @T1 = UNIX_TIMESTAMP(NOW());
CREATE TABLE CTM_MyISAM (
  `ServerID` BIGINT NOT NULL,
  `ClientID` BIGINT NOT NULL,
  `CommunityID` INT NOT NULL,
  PRIMARY KEY (`ClientID`,`CommunityID`),
  UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
  INDEX `IX_CTM_ClientID` (`ClientID` ASC))
ENGINE=MyISAM
PARTITION BY HASH (CommunityID) PARTITIONS 300;
SET @T2 = UNIX_TIMESTAMP(NOW());
CREATE TABLE CTM_InnoDB (
  `ServerID` BIGINT NOT NULL,
  `ClientID` BIGINT NOT NULL,
  `CommunityID` INT NOT NULL,
  PRIMARY KEY (`ClientID`,`CommunityID`),
  UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
  INDEX `IX_CTM_ClientID` (`ClientID` ASC))
ENGINE=InnoDB
PARTITION BY HASH (CommunityID) PARTITIONS 300;
SET @T3 = UNIX_TIMESTAMP(NOW());
SET @MyISAM_TIME = @T2 - @T1;
SET @InnoDB_TIME = @T3 - @T2;
SET @CTM_Ratio = FORMAT(@InnoDB_TIME/@MyISAM_TIME,2);
SELECT @InnoDB_TIME,@MyISAM_TIME,@CTM_Ratio;

I have the following on my laptop at home

6 GB RAM
650 GB Disk
Windows 8.1
MySQL 5.6.21

I got the following results

mysql> DROP DATABASE IF EXISTS rolandos_test;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> CREATE DATABASE rolandos_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE rolandos_test
Database changed
mysql> SET @T1 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE CTM_MyISAM (
    ->   `ServerID` BIGINT NOT NULL,
    ->   `ClientID` BIGINT NOT NULL,
    ->   `CommunityID` INT NOT NULL,
    ->   PRIMARY KEY (`ClientID`,`CommunityID`),
    ->   UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
    ->   INDEX `IX_CTM_ClientID` (`ClientID` ASC))
    -> ENGINE=MyISAM
    -> PARTITION BY HASH (CommunityID) PARTITIONS 300;
Query OK, 0 rows affected (1.09 sec)

mysql> SET @T2 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE CTM_InnoDB (
    ->   `ServerID` BIGINT NOT NULL,
    ->   `ClientID` BIGINT NOT NULL,
    ->   `CommunityID` INT NOT NULL,
    ->   PRIMARY KEY (`ClientID`,`CommunityID`),
    ->   UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
    ->   INDEX `IX_CTM_ClientID` (`ClientID` ASC))
    -> ENGINE=InnoDB
    -> PARTITION BY HASH (CommunityID) PARTITIONS 300;
Query OK, 0 rows affected (2 min 49.83 sec)

mysql> SET @T3 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MyISAM_TIME = @T2 - @T1;
Query OK, 0 rows affected (0.02 sec)

mysql> SET @InnoDB_TIME = @T3 - @T2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @CTM_Ratio = FORMAT(@InnoDB_TIME/@MyISAM_TIME,2);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT @InnoDB_TIME,@MyISAM_TIME,@CTM_Ratio;
+--------------+--------------+------------+
| @InnoDB_TIME | @MyISAM_TIME | @CTM_Ratio |
+--------------+--------------+------------+
|          170 |            1 | 170.00     |
+--------------+--------------+------------+
1 row in set (0.00 sec)

mysql>

COMPARISON OF STORAGE ENGINES

MyISAM

It took 1.09 seconds to make 300 partitions using MyISAM. . It create the following:

.frm file (Manage the Table)
.par file (Manage Partitions)
300 .MYD files (Partition Data)
300 .MYI files (Partition Indexes)

File Listing from Windows Command Line

C:\MySQL_5.6.21\data\rolandos_test>dir ctm_myisam*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.21\data\rolandos_test

02/15/2015  10:02 PM                 0 ctm_myisam#P#p0.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p0.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p1.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p1.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p10.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p10.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p100.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p100.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p101.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p101.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p102.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p102.MYI
.
.
.
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p95.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p96.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p96.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p97.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p97.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p98.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p98.MYI
02/15/2015  10:02 PM                 0 ctm_myisam#P#p99.MYD
02/15/2015  10:02 PM             1,024 ctm_myisam#P#p99.MYI
02/15/2015  10:02 PM             8,650 ctm_myisam.frm
02/15/2015  10:02 PM             1,708 ctm_myisam.par
             602 File(s)        317,558 bytes
               0 Dir(s)  674,767,982,592 bytes free

InnoDB

It took 2 min 49.83 seconds to make 300 partitions using InnoDB. It create the following:

.frm file (Manage the Table)
.par file (Manage Partitions)
300 .ibd files:

File Listing from Windows Command Line

C:\MySQL_5.6.21\data\rolandos_test>dir ctm_innodb*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.21\data\rolandos_test

02/15/2015  10:02 PM           131,072 ctm_innodb#p#p0.ibd
02/15/2015  10:02 PM           131,072 ctm_innodb#p#p1.ibd
02/15/2015  10:02 PM           131,072 ctm_innodb#p#p10.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p100.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p101.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p102.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p103.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p104.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p105.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p106.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p107.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p108.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p109.ibd
02/15/2015  10:02 PM           131,072 ctm_innodb#p#p11.ibd
.
.
.
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p90.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p91.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p92.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p93.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p94.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p95.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p96.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p97.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p98.ibd
02/15/2015  10:05 PM           131,072 ctm_innodb#p#p99.ibd
02/15/2015  10:02 PM             8,650 ctm_innodb.frm
02/15/2015  10:02 PM             1,708 ctm_innodb.par
             302 File(s)     39,331,958 bytes
               0 Dir(s)  674,766,995,456 bytes free

C:\MySQL_5.6.21\data\rolandos_test>

CONCLUSION OF THE EXPERIMENT

InnoDB's storage engine management is a definite bottleneck. This is not such much the number of files. Why ? If the number of files per partition was the sole factor, it should have taken 0.545 seconds to create half the number of file. It took 2 min 49.83 sec. How ?

MySQL's partition management is essentially the same. Once the storage engine layer is reached, InnoDB's data dictionary is evidently monitoring each partition's registration.

YOUR ORIGINAL QUESTION

Now that you know how InnoDB is functioning while files are being created, now look at your disks.

You have Disks on 3 other Windows OS platforms

Windows 7 (10 seconds)
Windows 2012 VM (16 seconds)
Windows 2008 (1 min 40 seconds (100 seconds))

I can confidently say the following

All three of your servers have better disk performance than my laptop's disk
ATA SCSI is not your Friend

RECOMMENDATION

GET FASTER DISKS
Please don't use RAID5 : See my post Is raid 5 suitable for a mysql installation?
Make sure you have Battery-Backed Cache
Check the Batteries on the Disk Controller

GIVE IT A TRY !!!


How to script a table with dynamic data masking via SSMS For a table which has dynamic data masking enabled if I try generating (create table) scripts from SSMS (13.0.1711.0) it is not scripting the MASKED function details. Is there any other way to do it?

Sample table

CREATE TABLE TestTable
(
    Sno INT IDENTITY(1, 1) PRIMARY KEY
    ,FirstName VARCHAR(50) NOT NULL
)
GO

ALTER TABLE TestTable ALTER COLUMN FirstName varchar(50)
MASKED WITH (FUNCTION = 'default()');
    GO

Currently this is the output I get via SSMS - Generate Scripts

CREATE TABLE [dbo].[TestTable](
    [Sno] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [Sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Update:

Applied CU2 for Microsoft SQL Server 2016 RTM (KB3182270) and now SQL Server 2016 build number is 13.0.2164.0
SSMS Versions is shown as 13.0.15900.1 (Even have tried downloading the SSMS once again as suggested by Aaron Bertrand and reinstalling it)

Still the original problem is not resolved - Scripting the table gives the same result as original posted.

You can use Powershell and SMO.

Create a new folder ('Scripts') on your C-Drive for use in the following example

In SSMS, right-click on the 'Tables' node and select 'Start Powershell'.  Paste the following code into the Powershell window

Remove-Item C:\scripts\z.sql
$scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
#$scriptingOptions.ScriptDrops = $true
$scriptingOptions.NoCollation=$True;
$scriptingOptions.ScriptBatchTerminator = $true
$scriptingOptions.NoCommandTerminator=$false
$ScriptingOptions.FileName = "C:\scripts\z.sql"
$ScriptingOptions.ToFileOnly=$true
$ScriptingOptions.AppendToFile=$true
dir | Where-Object {$_.name -like "*test*"} | foreach {$_.Script($scriptingOptions)}

Check for the z.sql file in the C:\Scripts folder

If it's any consolation - I too cannot get SSMS to natively script the masking details and I'm on the latest release.


SSRS and Informix connection I have the IBM drivers installed, created a data source that successfully tests from SSRS front end.

Create a report from my machine which also has the drivers installed and the ODBC testing correctly.

I get the following error

ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Unspecified System Error =  -23101.

I run

Select * from sysmaster:informix.sysdbslocale

which returns en_US.819 for the database i want.

Run Setnet32 and set CLIENT_LOCALE and DB_LOCALE to en_US.819

The client/server vars in the ODBC setting are set the same also.

I'm not sure what else to try.

When I use an Informix command finderr, I get the output shown:

$ finderr -23101
-23101  Unable to load locale categories.

An invalid locale name was supplied for the locale initialization.
The environment variable specifying the locale category has a wrong
value.

Check the value of the corresponding environment variable,
CLIENT_LOCALE or DB_LOCALE. If this error was encountered when using
the "SET COLLATION" statement, check that the value passed to the
statement is a valid locale. If this error was encountered when using
the "SET NO COLLATION" statement, check that the session's default
locale file is still available. Also check if INFORMIXDIR points to
a valid directory where IBM Informix products are installed.

If a client application receives this error when it requests a database
server connection, check that the client and database locales are
compatible. They are compatible if a valid locale exists on the server
computer with the name lg_tr.codeset@mod, where lg_tr (language and
territory) and mod (locale modifier) are from the CLIENT_LOCALE and
codeset is from the database locale (from the DB_LOCALE, if it is set,
or as stored in the database).

$

That's probably clear as mud, but 'unable to load locale categories' probably means that $INFORMIXDIR, which defaults to /usr/informix on Unix-like systems (and something like C:\Informix on Windows systems IIRC) is either not set correctly or is pointing somewhere invalid.  In particular, locale categories are stored in $INFORMIXDIR/gls.

That's the easy part; the hard part is knowing what you need to set.  Have you checked the SETNET32 setting?  Have you checked the OBDC connection configuration?  I believe SSRS is a Windows-based system, and I'm by no means a Windows expert, so there's a limit to what I can do to point you in the right direction.  But having more detailed information about the error than 'unknown system error' may help you.

You could also look at the Informix manuals on the IBM Informix Information Center 12.10 web site.


What is the most effective SELECTIVE XML index? I am trying to determine the most effective selective XML index for an XML column within a table of 100 million rows. My question is similar to :

Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

but the specific benefits of the selective index specified in the answer are not discussed in detail.

Below is a simplified version of the query:

;WITH CTE AS
( SELECT 1 AS ID,
              CONVERT(XML, '<Root>
                              <ParentTag ParentTagID="Sample Text">
                                <ChildTag1>5</ChildTag1>
                                <ChildTag1>6</ChildTag1>
                                <ChildTag1>7</ChildTag1>
                                <ChildTag2>8</ChildTag2>
                                <ChildTag2>9</ChildTag2>
                                <ChildTag2>10</ChildTag2>
                                <OtherTag>LargeIrrelevantData</OtherTag>
                              </ParentTag>
                            </Root>'
                      ) AS SampleXML
)
SELECT * INTO dbo.CTE FROM CTE

SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(MAX)') AS ParentTagID,
       RootParentTag1.ChildTag1.value('(text())[1]', 'NVARCHAR(MAX)') AS ChildTag1,
       NULL
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag1') as RootParentTag1(ChildTag1)
UNION
SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(MAX)') AS ParentTagID,
       NULL,
       RootParentTag2.ChildTag2.value('(text())[1]', 'NVARCHAR(MAX)') AS ChildTag2
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag2') as RootParentTag2(ChildTag2)

Is the most efficient index:

EXEC sp_db_selective_xml_index [DATABASE], TRUE
ALTER TABLE CTE ADD CONSTRAINT PK_CTE PRIMARY KEY CLUSTERED (ID)

CREATE SELECTIVE XML INDEX SIX_CTE ON CTE(SampleXML)
FOR
( ParentTag = '/Root/ParentTag' AS XQUERY 'node()',
  ParentTagChildTag1 = '/Root/ParentTag/ChildTag1' AS XQUERY 'node()',
  ParentTagChildTag2 = '/Root/ParentTag/ChildTag2' AS XQUERY 'node()',
  ParentTagID = '/Root/ParentTag/@ParentTagID' AS XQUERY 'xs:string' MAXLENGTH(255),
      ChildTag1 = '/Root/ParentTag/ChildTag1/text()' AS XQUERY 'xs:double',
      ChildTag2 = '/Root/ParentTag/ChildTag2/text()' AS XQUERY 'xs:double'
)

I do not want the LargeIrrelevantData, inside the <OtherTag>, to be indexed.

I'm using the following query to track indexed columns inside the XML

SELECT
a.name AS column_name,
c.name AS data_type
FROM sys.columns a
INNER JOIN sys.indexes b
ON a.object_id = b.object_id
INNER JOIN sys.types c
ON a.system_type_id = c.user_type_id
WHERE b.name = 'SIX_CTE'
AND b.type = 1
ORDER BY column_id

A few related questions:

How else can I determine the performance increase of my selective index besides reviewing query execution times?

Is it preferred to use XQUERY's xs:double or SQL's INT when specifying data types?

Thank you.

How else can I determine the performance increase of my selective
  index besides reviewing query execution times?

You could also look at IO utilization and CPU. Perhaps most interesting when dealing with selective XML indexes is to figure out if the index is actually used by the query. To do that you can have a look at the execution plan.

If you see one of the Table-valued functions responsible for parsing the XML then you know that you have not covered your XML query with a XML index.

What you should have instead is a scan or seek operator on the internal table used for the XML index.

  Is it preferred to use XQUERY's xs:double or SQL's INT when specifying
  data types?

If you want the index to be used for the value() function you should match the data type in the index with the data type used in the query. You have nvarchar(max) in the query and xs:double in the index so ChildTag1 and ChildTag2 is not used in your query. It is instead parsing the XML data using the table valued functions.

To make your query use only the indexes (no xml parsing functions) you can change the query and the index to this.

create selective xml index SIX_CTE on CTE(SampleXML) for
(
  ParentTag = '/Root/ParentTag' as xquery 'node()',
  ParentTagID = '/Root/ParentTag/@ParentTagID' as sql nvarchar(255),
  ChildTag1 = '/Root/ParentTag/ChildTag1' as sql int,
  ChildTag2 = '/Root/ParentTag/ChildTag2' as sql int
);

Note that the node() indexes on ChildTag1 and ChildTag2 is not used by your query so not needed in the index.

SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(255)') AS ParentTagID,
       RootParentTag1.ChildTag1.value('.', 'INT') AS ChildTag1,
       NULL
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag1') as RootParentTag1(ChildTag1)
UNION
SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(255)') AS ParentTagID,
       NULL,
       RootParentTag2.ChildTag2.value('.', 'INT') AS ChildTag2
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag2') as RootParentTag2(ChildTag2);

  What is the most effective SELECTIVE XML index?

Depends entirely on your use case and what the data looks like that you have. This answer is in no way an attempt to optimize your query, only a way to show you how to make use of the indexes. The result of testing on your data could show that you get the best performance by not using any indexes at all, or perhaps use the index only on some parts of the query. As a general rule it is always good to keep an index as small as possible so using int instead of float is a good thing if you know that you are dealing with integers.


version controll application/system for mysql databases Possible Duplicate:
  Keeping track of schema changes

Hello,

How do you keep your mysql database schema under version control.

Liquibase is probably the most popular version control system for MySQL.  It's free and open-source.


Two kinds of products and one order table I have  the following tables: tblCustomer, tblProductA and tblProductB. I have also an order table
tblOrder. Each order consists of a customer and one or more products from tblProductA and one or more products tblProductB.

The standard case is when we have only one table for products, and we usually use a join table to connect orders and products.

However, in the case I am bringing, it is mandatory to have two tables for products (tblProductA and tblProductB). How do I model this case?

There are 2 options:

If your productType has same attribute:

If your ProductType has different attribute:


Is it possible to run an arbiter on a machine that also hosts a secondary member? I have two machines (it is a restriction) configured as a replica set with MongoDB 3.6.5.

Is it possible to run an arbiter on a machine that also hosts a secondary member?

Even if it is not advisable, but if it is possible, how can I install an arbiter on the secondary server for when the primary goes down and the secondary becomes the primary?

Once installed, how can I configure the replica set for when the original primary returns and make a copy of the secondary (do not use the replication)?

Is it possible to run an arbiter on system that also hosts a secondary member?

There is no technical restriction on running multiple processes on the same host, as long as they are listening to different port numbers. For example, you could start an arbiter listening to port 27018 (instead of the default 27017). However, you do need to consider the operational implications of colocating processes.

  Even if it is not advisable, but if it is possible, how can I install an arbiter on the secondary server for when the primary go down the secondary become the primary?

This is the standard replica set behaviour. Assuming you have a preferred primary (which will have a higher priority value), your replica set configuration would look similar to:

{
   _id : "myreplset",
   members: [
      { _id: 0, host: "machine1.example.net:27017", priority: 2 },
      { _id: 1, host: "machine2.example.net:27017" },
      { _id: 2, host: "machine2.example.net:27018" }
   ]
}

With three voting members in your replica set, the strict majority required to elect (or maintain) a primary is  two votes. If machine1 is unavailable, the members on machine2 can still elect a primary.

However, because machine2 has the majority of the voting members there are a few operational consequences:

If machine2 is unavailable, machine1 cannot elect or sustain a primary without manual intervention to force reconfigure the replica set.
In the event of a network split, machine2 will become primary.

If the arbiter (or another voting member) was on a third machine, both of these scenarios would be avoided.

You may want to consider colocating the arbiter on machine1 , so in the event of a network split the primary will remain on machine1 (reversing the operational scenarios noted above so that failover to machine2 would be a manual reconfiguration).

For a production deployment I would also recommend using a third secondary (instead of an arbiter). When your Primary-Secondary-Arbiter deployment is down a member (so effectively Primary-Arbiter) there is no longer any active replication and your deployment will not be able to acknowledge majority writes. It is definitely not ideal to have multiple data-bearing members competing for resources on the same host machine, but data redundancy and availability of majority writes is often a more important factor.

  Once installed, how can I configure the replica set for when the original primary returns and make a copy of the secondary?

Assuming you have configured the member on machine1 with a higher priority as per the above example, the expected outcome is that when machine1 is available again it will automatically resume syncing from the current primary on machine2 (as a secondary). Once the member on  machine1 catches up and is eligible to be primary, it will initiate an election based on a higher priority member being available (referred to as a priority takeover election).


Passing values to variables defined in SSIS package at run time I need to pass values to the variables in a SSIS package at run time. Obviously it is not practical to open BIDS and set a variable every time the package needs to be run. We run it on SQL Server 2008 R2.

What is the best way to do that?

For example we have 3 variables used in the package. 1 of them are populated in the packages on fly, the other 2 needs to be specified from begining:

Let's say we have these variables defined in the package:

@Rec_Count (No need to set a value at run time - it will be populated
by a dataflow automatically)
@Fetch_Size (Should be set a value when the package is kicked off)
@File_Path (Should be set a value when the package is kicked off)

How can we set the last two variables in run time by the user?

Thank you in advance.

You can have the values specified in a .dtsconfig file, or you can set them using the /SET option of dtexec.exec


Retrieve Data faster from a huge Oracle Table I have a huge Oracle table ( ttdinv700 ) which contains almost 20 million rows.

I am trying to execute the below query, on the table:

   select max(ttdinv700.t$trdt)
   from ttdinv700, ttiitm001
   where ttdinv700.t$item = rpad(ttiitm001.t$item,16,' ')
   and ttdinv700.t$koor = 1
   and ttdinv700.t$kost in (2,7);

The execution is taking a lot of time. Could you please suggest a way to retrieve the data faster?

I cannot partition the table as this is a production database.

When I recall correctly this is a query on BaaN version 4 or so probably. The official BaaN policy is that the Oracle drivers require ALL text fields to be VARCHAR which has some different semantics than VARCHAR2. I guess that your rpad was introduced by someone to compensate for that but it should not be there.

Also, you should try to avoid adding indexes etc yourself. The BaaN middleware takes care of that and although it is possible to add indexes things can go wrong during upgrades etc.

I don't have a BaaN system on Oracle present at this moment, but I think you are missing some leading columns of the index in your query, something like company/warehouse since you seem to be determing possibly the last inventory transaction per logistic item. Oracle version 9 and higher sometimes can compensate for that.

My advice is to press F4 in TOAD or Invantive Query Tool on ttdinv700 and check the columns listed in the index. Add missing entries to the where clause. Or check contents of user_ind_columns.

Then do the same for ttditm700 (the items). The rpad effectively disables index use but depending on your statistics it should be the driving table for the query (most companies have relatively few items compared to their stocks). Try to enable the index on ttditm700.

IMHO this question belongs on stackoverflow and not here.


What methods are available to create a snapshot of an oracle database for testing purposes? I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done.

I'm a bit confused as to what my options are.

I've looked into creating a standby. It seems like that may be overkill for what we need and the pages describing how to create one are confusing to one with as little experience as I.

Questions

Is a standby the best fit or is import/export a viable option?
Are there other methods?.

Thanks!

If with "export/import" you mean the "traditional" exp and imp commands, than I'd say there is a better solution:

expdp and impdp are much faster and much more flexible when copying a snapshot of a database (or single schema).

If the database is not huge (i.e. Terabytes) then expdp and impdp is the easiest way to achieve this. They are easily scriptable and can be started using SQL also. So no commandline access to the servers is needed (provided the dump files are stored on a shared storage).


Can I get some rationale from the Optimzer why it chose a certain explain plan? This is not about reading an explain plan.

This is not about adding any hints or measuring performance of a specific query.

I have always been wondering whether it's possible for a given explain plan for a query (or an autotrace of the query) to "ask" the Optimizer why it chose this plan vs. other plans available to it.

Is there a way to ask the database to display a comparison of different plans that it considered for the same query?
Is there a way to view what calculations went into the cost displayed for a plan? (For examples, what the Optimizer assumed wrt. disk access vs. in memory stuff, etc.)

Rationale for this question: With the EXPLAIN PLAN as I know it, when the database chooses a certain execution plan, and this is insufficient/too slow/... at the first moment a DBA or developer does not ad hoc know: Whether there were any alternatives to this plan at all and furthermore doesn't know why, if the developer knows of alternatives (e.g. an unused index), the alternatives weren't used.

Yes, you can.

This is called tracing the Oracle optimizer. Doing so creates a trace file in which the optimizer dumps all reasoning done when composing a plan.

An example of generating a trace for a specific SQL can be found here how to trace optimizer for specific SQL system wide - 10053 trace event

You might need a little time to read it, given the amount of data found in the trace, it is amazing that the Oracle even finds time to do this and return in a decent time.


Update Ranking Points Based on Date Range Currently, I am building a personal project.

A site that stores Racing Information and grants point to weekly individual winners.

These racers have groups and points earned by the group will be ranked on a monthly basis.

The query below returns Top racing information of the current week where the current week starts every Tuesday.
SELECT *
FROM timeatk
WHERE DATE(posted)
    BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
ORDER BY timer ASC LIMIT 0, 10

But, I am working on a CRON, where a PHP script should be triggered depending on the date on a for-loop. Coz' every Tuesday, I need to add points for the weekly winners.
For the weekly results:
I would like to UPDATE the weekly top ten racers based on the lowest time on the "timer" column from the last week's Tuesday until Monday of the current week.
Weekly Winners:

1st = 10points
2nd = 9points
3rd = 8points
.. until
10th = 1point

for ($score = 10; $score > 0; $score--) {
        $data = [
            'score' => $score
        ];
        $sql = "update ometimeatk.timeatk_has_list set weekly_score = :score where DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY) AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)";
        $stmt= $conn->prepare($sql);
        $stmt->execute($data);
}

Here's the table structure:
CREATE TABLE `timeatk_has_list` (
  `id` int(11) NOT NULL,
  `group_name` varchar(80) NOT NULL,
  `racer_has_name` varchar(255) NOT NULL,
  `racer_has_mobile` varchar(255) NOT NULL,
  `racer_has_nationality` varchar(255) NOT NULL,
  `racer_has_email` varchar(255) NOT NULL,
  `motor` varchar(255) NOT NULL,
  `gear_ratio` varchar(255) NOT NULL,
  `chassis` varchar(255) NOT NULL,
  `timer` varchar(255) NOT NULL,
  `posted` datetime DEFAULT NULL,
  `weekly_score` smallint(6) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here's a simple SELECT query to check the result.
The UPDATE query above updates ALL rows weekly_score with 1.
And here's the working SQL query with the help of J.D:
UPDATE timeatk_has_list
INNER JOIN
(
        SELECT *, ROW_NUMBER() OVER (order by timer desc) AS RacerRank
        FROM timeatk_has_list
        WHERE DATE(posted)
            BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
            AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        ORDER BY timer ASC LIMIT 0, 10
) c
    ON ometimeatk.timeatk_has_list.id = c.id
SET ometimeatk.timeatk_has_list.weekly_score = 11 - c.RacerRank

You should always include your schema and sample before and after data, otherwise it's difficult to conceptualize what we don't know.
You can solve this pretty easily without even using a loop in PHP, rather by using a window function in MySQL like ROW_NUMBER to generate the ranks and then use those ranks to update your scores. Here's a partial example (if you update your question with the missing items I mentioned above, I can update this example):
UPDATE ometimeatk.timeatk_has_list
INNER JOIN
(
        SELECT *, ROW_NUMBER() OVER (ORDER BY timer DESC) AS RacerRank -- Generates a unique ID (assuming timer is unique) for each racer row ordered by timer
        FROM timeatk
        WHERE DATE(posted)
            BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
            AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        ORDER BY timer ASC LIMIT 0, 10
) c -- Gets the racer rank
    ON ometimeatk.timeatk_has_list.id = c.id -- Fill in your actual key fields here
SET ometimeatk.timeatk_has_list.weekly_score = 11 - c.RacerRank -- Sets the weekly score to 10 for 1st place, 9 for 2nd place, etc


What's the best way to order a list of tables based on their foreign key constraints I'm working on an etl task that takes tables from one schema (with no constraints) and inserts them into the same tables (plus constraints).

I don't want to type out a bazillion insert statements, so I wrote some dynamic sql that looks like this:

declare @table varchar(255),
@stgtable varchar(255),
@columns varchar(max),
@sql nvarchar(max)

declare c cursor for

select table_name from INFORMATION_SCHEMA.tables

open c

fetch from c into @table

while @@FETCH_STATUS = 0
begin

set @stgtable = replace(right(@table, len(@table) - 1), 'LZSTG','STG')

--print @stgtable

select @columns = stuff( (
    select ', ' + Column_Name from information_schema.columns
    where Table_Name = @table
    for xml path ('')),1,2,'')
--print @columns

begin try
set @sql = 'truncate table ' + @stgtable
exec sp_executesql @sql
set @sql = 'insert into ' +  @stgtable  + ' (' + @columns + ') select ' + @columns + ' from ' + @table

print @sql
exec sp_executesql @sql

set @sql = 'select * from ' + @stgtable
exec sp_executesql @sql
end try

begin catch print 'womp womp ' + @stgtable end catch

fetch next from c into @table
end

close c
deallocate c

Problem: If you go through information_schema.tables in its default order, the insert will often fail because of a foreign key violation.

If I want this solution to work, I have to insert into parent tables first and only then insert into child tables.

I've got this, but it is hardly an ironclad solution:

with fks as
(SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table_name],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id)

select t.table_name, fks.* from
information_schema.tables t
left join fks on t.table_name = fks.table_name
order by referenced_table

Question What is the best way to order information_schema so that the parent tables are on top and the child tables are on the bottom?

One problem is that you could have circular references. You may prefer to disable the constraints, do your ETL, and reenable them.

This could also be a matter of timing, such that it may be impossible to populate some data because of when you got the data from the source. For example, you get data from table P and then from table C. But by the time you’re getting the data from table C, you have a row that refers to a row in C that refers to a row in P that wasn’t there a few milliseconds earlier. Or if you do it in the other order, the row in P is deleted before you grab it.

So by all means use a clever script. But you might find you still have problems.

My recommended solution is to question whether you actually want FKs here, or whether you should do some more complex lookups with inferred members instead.


Is it possible to use Oracle XMLTABLE and %type column type selector? I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE.

I want to know how can i specify a field type according to a table column type when passing columns in XMLTABLE. (using something like %type).

Here is my simplified code:

SELECT tmp.*
FROM (SELECT XMLTYPE(xml_in) AS xml FROM dual),
      XMLTable('for $i in /rowset/row return $i'
                PASSING xml COLUMNS
                  name MYTABLE.name%TYPE path 'name', // %TYPE just doesnt work here :(
                  family MYTABLE.family%TYPE path 'family'
              ) tmp

You can't use the %TYPE construct in the COLUMNS clause.

However, depending on what you do with your data, it may be a good idea to not be too strict with it in this query. For instance, if we take the data from your previous question, note that the following doesn't result in an error:

SQL> SELECT x.*
  2    FROM XMLTable('for $i in /rowset/row return $i'
  3                   passing XMLTYPE(:xml)
  4                   columns
  5                      name   varchar2(1) path 'name',
  6                      family varchar2(1) path 'family'
  7                 ) x;

NAME FAMILY
---- ------
t    m
m    s

Whereas in most case if you have an XML file as input and a column is larger than specified in the table, this would be considered an anomaly.

This is why you could, if you intend to perform validation on the input file, load all columns as text (VARCHAR2(4000)) and then insert into your staging table. This will result into an appropriate error.


PL/SQL Script Line with Only a Dot...What is the purpose/function of the dot? I have inherited a pl/sql script from a previous dba that worked at my company. It is used to "clean up" failed distributed transactions. The script is easy enough to understand, except for one line that only contains a dot, "." I have not seen this before...what is the purpose/function of the dot line?

set define on
set scan on
set verify off
set serveroutput on

undefine IncidentNumber

spool &&IncidentNumber

connect / as sysdba
whenever sqlerror continue
set serveroutput on size 1000000
set linesize 200
DECLARE
   /* &&IncidentNumber */
   pragma autonomous_transaction;
   l_sql_statement       VARCHAR2(255);
   l_fdist_found         BOOLEAN := FALSE;
   --
   CURSOR cfdist IS
     SELECT local_tran_id tranid, upper(state) state
     FROM dba_2pc_pending;
   --
   BEGIN
      --
      FOR rfdist IN cfdist LOOP
          l_fdist_found := TRUE;
          if rfdist.state = 'PREPARED' then
              l_sql_statement := 'ROLLBACK FORCE '''||rfdist.tranid||''''||'/*&&IncidentNumber*/';
              DBMS_OUTPUT.PUT_LINE(l_sql_statement);
              EXECUTE IMMEDIATE l_sql_statement;
              COMMIT;
          end if;
          DBMS_OUTPUT.PUT_LINE('Executing DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('||rfdist.tranid||')');
          DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(rfdist.tranid)/*&&IncidentNumber*/;
          COMMIT;
          IF l_fdist_found = TRUE THEN
              DBMS_OUTPUT.PUT_LINE('Rolled back distributed transaction id '|| rfdist.tranid);
          END IF;
      END LOOP;
      --
      IF l_fdist_found = FALSE THEN
          DBMS_OUTPUT.PUT_LINE('No failed distributed transactions were found.');
      END IF;
      --
     EXCEPTION
       WHEN OTHERS THEN
           RAISE_APPLICATION_ERROR(-20001, 'Error:'||SQLCODE||':'||SQLERRM);
   END;
.
/

A line with only a dot, ".", appears to be a SQL*Plus command to end the input and save it in the buffer without executing it.

SQL>
SQL>
SQL> DECLARE
  2     qty1 number;
  3  BEGIN
  4     select count(*) into qty1 from dba_roles;
  5     dbms_output.put_line('Number of roles: '||to_char(qty1));
  6  END;
  7  .
SQL>
SQL>
SQL> LIST
  1  DECLARE
  2     qty1 number;
  3  BEGIN
  4     select count(*) into qty1 from dba_roles;
  5     dbms_output.put_line('Number of roles: '||to_char(qty1));
  6* END;
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2     qty2 number;
  3  BEGIN
  4     select count(*) into qty2 from dba_procedures;
  5     dbms_output.put_line('Number of procedures: '||to_char(qty2));
  6  END;
  7  .
SQL>
SQL>
SQL>
SQL> LIST
  1  DECLARE
  2     qty2 number;
  3  BEGIN
  4     select count(*) into qty2 from dba_procedures;
  5     dbms_output.put_line('Number of procedures: '||to_char(qty2));
  6* END;
SQL>
SQL>
SQL>
SQL> /
Number of procedures: 26744

PL/SQL procedure successfully completed.

SQL>
SQL>


Pivot functionwith multiple conditions (concatenated in one header) I have this query written in Microsoft Access, but I need to convert it to run on SQL Server.  I read multiple answers on dynamic pivoting in SQL Server, but can not apply it to my particular code, since I don't know how to pivot multiple conditions that are concatenated in one header.

Here is the code in Access:

TRANSFORM SUM(Sales.Tons) AS SumOfTons
SELECT     Sales.ID_SaleDest
FROM       (QualPack
INNER JOIN (Groups
INNER JOIN (Customers
INNER JOIN SaleDest
ON         Customers.ID_Customer = SaleDest.ID_Customer)
ON         Groups.ID_Group = SaleDest.ID_Group)
ON         QualPack.ID_QualPack = SaleDest.ID_QualPack)
INNER JOIN Sales
ON         SaleDest.ID_SaleDest = Sales.ID_SaleDest
WHERE      (((
                                            Year([Date_shipped]))=2017)
           AND        ((
                                            SaleDest.ID_Group)<>10)
           AND        ((
                                            QualPack.Quality_Second)<>'NO')
           AND        ((
                                            Sales.ID_Dataset)="AC"
                      OR         (
                                            Sales.ID_Dataset)="BU"))
OR         (((
                                            Year([Date_shipped]))=2016)
           AND        ((
                                            SaleDest.ID_Group)<>10)
           AND        ((
                                            QualPack.Quality_Second)<>'NO')
           AND        ((
                                            Sales.ID_Dataset)='AC'))
GROUP BY   Sales.ID_SaleDest,
           SaleDest.ID_Group
ORDER BY   Sales.ID_SaleDest,
           [Sales]![ID_Dataset] & '-' & Year([Sales]![Date_shipped]) & '-M' & Format(Month([Sales]![Date_shipped]),'00')
PIVOT [Sales]![ID_Dataset] & '-' & YEAR([Sales]![Date_shipped]) & '-M' & Format(MONTH([Sales]![Date_shipped]),'00')
WITH OWNERACCESS OPTION;

The problem is this:

PIVOT [Sales]![ID_Dataset] & '-' & YEAR([Sales]![Date_shipped]) & '-M'
    & Format(MONTH([Sales]![Date_shipped]),'00')

How can I pivot this in mssql?

Idea is to summarize Tons for a particular SaleDest, by using Dataset, Year of Date_shipped and Month od date_shipped, but in one header (concatenated). For example, output for AC Dataset, Year 2017 and Month 1:

SaleDest    AC-2017-M01
2311        29,4
2313        2,8
2314        49,56
2320        24

CREATE TABLE Sales(SaleDest int, Tons decimal(18,2), DateShipped datetime, DataSet char(2));
INSERT INTO Sales VALUES
(2311, 20.4,  '20170401', 'AC'),
(2311, 9,     '20170402', 'AC'),
(2313, 1.4,   '20170405', 'AC'),
(2313, 1.4,   '20170501', 'BU'),
(2314, 29.56, '20170401', 'AC'),
(2314, 20.0,  '20170505', 'BU'),
(2320, 12,    '20170404', 'BU'),
(2320, 12,    '20170505', 'BU');
GO

I'd suggest you to use a temp table or a table variable to store the data source for the pivot solution.

I've simulated it in this way:

SELECT SaleDest,
       Tons,
       DataSet
       + '-' + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
       + '-M' + Format(MONTH([Sales].[DateShipped]),'00') as Caption
FROM   Sales;
GO

SaleDest | Tons  | Caption
-------: | :---- | :----------
    2311 | 20.40 | AC-2017-M04
    2311 | 9.00  | AC-2017-M04
    2313 | 1.40  | AC-2017-M04
    2313 | 1.40  | BU-2017-M05
    2314 | 29.56 | AC-2017-M04
    2314 | 20.00 | BU-2017-M05
    2320 | 12.00 | BU-2017-M04
    2320 | 12.00 | BU-2017-M05

Now you should build columns names:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ','
                                   + QUOTENAME(DataSet
                                   + '-'
                                   + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
                                   + '-M'
                                   + Format(MONTH([Sales].[DateShipped]),'00'))
                   FROM Sales
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SELECT @cols;
GO

| (No column name)                          |
| :---------------------------------------- |
| [AC-2017-M04],[BU-2017-M04],[BU-2017-M05] |

And finally the dynamic pivot query:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ','
                                   + QUOTENAME(DataSet
                                   + '-'
                                   + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
                                   + '-M'
                                   + Format(MONTH([Sales].[DateShipped]),'00'))
                   FROM Sales
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @CMD =
'SELECT SaleDest, ' + @cols + ' FROM
(
SELECT SaleDest,
       Tons,
       DataSet
       + ''-'' + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
       + ''-M'' + Format(MONTH([Sales].[DateShipped]),''00'') as Caption
FROM   Sales) SRC
PIVOT
(
    SUM(Tons) FOR Caption IN (' + @cols + ')
) PVT'

EXEC(@cmd);
GO

SaleDest | AC-2017-M04 | BU-2017-M04 | BU-2017-M05
-------: | :---------- | :---------- | :----------
    2311 | 29.40       | null        | null
    2313 | 1.40        | null        | 1.40
    2314 | 29.56       | null        | 20.00
    2320 | null        | 12.00       | 12.00

dbfiddle here


Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables Is it possible to configure an Oracle user in Enterprise Manager, so that he can later create views on any future, yet unknown table in his schema?

I am currently running into "ORA-01031 insufficient privileges" error, because no individual SELECT or data modification rights have been granted (only through role, plus the CONNECT role including CREATE VIEW - EDIT: Wrong! Does no longer!).

However, I run a DB creation script, executed from SQL Developer, which creates tables and is now supposed to create a view based on the previously created tables (just like another table). The script does not "know" the user account who is executing it, and is basically doing no rights/privileges settings at all.

It's basically like:

CREATE TABLE Members (
    ID        number(10),
    Name      varchar2(100),
    Street_No varchar2(100),
    ZIP       number(5),
    City      varchar2(100)
);

CREATE VIEW MembersNamesOnly AS
    SELECT ID, Name FROM Members;

Detecting the current user (executing the script) and grant himself the rights does not work, and it would result in annoying code overhead, granting rights for many, individual tables. And problems again, when other users shall use the view, but get their rights only through roles, without tedious user-table-right assignments for each user and table.

Is such a setting possible? Create tables and then views based on them, in a single script? I'm currently afraid I can't use any views at all, without heavy re-writing of all the existing database creation code.

To query data from the tables owned by an user, the user does not need any additinoal privileges. Your script fails because the user does not have the privilege to create a view.

GRANT CREATE VIEW TO user;

Note: privileges granted through a role have no effect in a PL/SQL script.


Understanding non clustered index locking during an update The setup script

CREATE TABLE t2 (   [col1] INT, [col2] INT  );

DECLARE @int INT;
SET @int = 1;

WHILE (@int <= 1000)
BEGIN
    INSERT INTO t2
        ([col1], [col2])
    VALUES (@int*2, @int*2);
    SET @int = @int + 1;
END
GO
create clustered index cl on t2(col1)
create  index ncl on t2(col2)

I run an simple update and keep the transaction open in read committed isolation level.

begin tran
update t2 set [col2]=[col2]+1  where col1=6

If I check sp_lock in another session I get the below results

What I am trying to understand are the key locks on the non-clustered index(indid 2).  Why are there two key lock on non-clustered index?

If I check dbcc page on page id 248, I could locate the obvious one((1bfceb831cd9)) which is the lock for the entry for the record 6 which got changed to 7.  Output of DBCC PAGE below

What I am trying to understanding is what the purpose of the other key lock(5ebca7ef4e2c) is and what its locking.

If you try the following...

CHECKPOINT;

GO

BEGIN TRAN

UPDATE t2
SET    [col2] = [col2] + 1
WHERE  col1 = 6

SELECT Operation,
       AllocUnitName
FROM   sys.fn_dblog(NULL, NULL)
WHERE  AllocUnitName IS NOT NULL

ROLLBACK

You will see

+-----------------+---------------+
|    Operation    | AllocUnitName |
+-----------------+---------------+
| LOP_MODIFY_ROW  | dbo.t2.cl     |
| LOP_DELETE_ROWS | dbo.t2.ncl    |
| LOP_SET_BITS    | dbo.t2.ncl    |
| LOP_INSERT_ROWS | dbo.t2.ncl    |
+-----------------+---------------+

Showing that the UPDATE against the non clustered index is implemented as a delete/insert pair.

(5ebca7ef4e2c) is the hash for the initial record 6,6
(1bfceb831cd9) is the value after update 6,7.
Look also at slot number

select
    *
    ,%%lockres%%
    ,%%physloc%%
    ,sys.fn_PhysLocFormatter(%%physloc%%)
from t2 with (nolock,index = ncl)
where
    %%lockres%% = '(5ebca7ef4e2c)'
    --OR
    --%%lockres%% ='(1bfceb831cd9)'

begin tran
update t2 set [col2]=[col2]+1  where col1=6
commit tran

select
    *
    ,%%lockres%%
    ,%%physloc%%
    ,sys.fn_PhysLocFormatter(%%physloc%%)
from t2 with (nolock,index = ncl)
where
    --%%lockres%% = '(5ebca7ef4e2c)'
    --OR
    %%lockres%% ='(1bfceb831cd9)'

update t2 set [col2]=[col2]-1  where col1=6

col1    col2    HashValue   bPhysicLocation crackedLocation
6       6       (5ebca7ef4e2c)  0xB401000001000200  (1:436:2)

col1    col2    HashValue   bPhysicLocation crackedLocation
6       7      (1bfceb831cd9)   0xB401000001000300  (1:436:3)


How to find the nearest lat and lang in postgresql I am trying find the nearest lang and lat from the specified lat and lang
i tried the below mentioned query, in my query i have mentioned the place muscat's lat and lang ,so i want to display the nearest cities of muscat with limit 3 along with muscat

CREATE TABLE IF NOT EXISTS tablename(_id SERIAL NOT NULL UNIQUE PRIMARY KEY,city VARCHAR, Lat NUMERIC, Lang NUMERIC);

select * from (
SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lang ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance
FROM tablename
) al
where lat >  23.61  OR lang >  58.54
ORDER BY distance
LIMIT 1;

here is my table (Assumed data not exact lat and lang)data

| id | City      |  lat      | lang    |
----------------------------------------
|  1 | muscat    | 23.61     | 58.54   |
|  2 | sur       | 22.566    |59.52    |
|  3 | Muhafazat |23.585     |58.40    |
|  4 | ZZZ       | 5.8       | 7       |
|  5 | AAA       | 9.22      | 5       |
|  6 | Barka     |23.613     |58.592   |

Expected out put:

    | id | City      |  lat      | lang    |
    ----------------------------------------
    |  1 | muscat    | 23.61     | 58.54   |
    |  2 | sur       | 22.566    |59.52    |
    |  3 | Muhafazat |23.585     |58.40    |
    |  6 | Barka     |23.613     |58.592   |

yes i got the output with this query, and thank you to all who tried to help me:)

select * from (SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lang ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance FROM table name) al where lat >= 22.566 OR lat >= 59.52 AND lat <= 22.566 OR lat <= 59.52 ORDER BY distance LIMIT 3;


Can I edit single timestamp so it recalculate all the following timestamps in a table? If I have a table with a list of tasks with time stamps (start_time and end_time) and minutes of duration, can I change the minutes in one records so it effect and recalculate all the other timestamps in the other records?

For example:

id      task                  start_time      end_time      minutes
1      Send email       12:00            12:10            10
2      Design banner  12:11            12:36            25

3      Call Jake           12:37            12:42            5

Let say I need more time for sending the email and I add 5 minutes so it becomes 15... can I make the whole table recalculate so the end_time for "Call Jake" would be 12:47

Is this possible?

I wouldn't recommend to create a trigger for this, as it can have unwanted side-effects.
A stored procedure just for this purpose seems more appropriate to me. Something like this:

DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks
    (`id` int, `task` varchar(13), `start_time` time, `end_time` time, `minutes` int)
;

INSERT INTO tasks
    (`id`, `task`, `start_time`, `end_time`, `minutes`)
VALUES
    (1, 'Send email', '12:00', '12:10', 10),
    (2, 'Design banner', '12:11', '12:36', 25),
    (3, 'Call Jake', '12:37', '12:42', 5)
;

DROP PROCEDURE IF EXISTS adjust_task_times;
DELIMITER $$
CREATE PROCEDURE adjust_task_times(IN p_new_time int, IN p_which_row int)
BEGIN
DECLARE diff_times int;

SET diff_times = (SELECT p_new_time - minutes FROM tasks WHERE id = p_which_row LIMIT 1);

UPDATE tasks SET minutes = p_new_time, end_time = ADDTIME(end_time, diff_times * 100) WHERE id = p_which_row;
UPDATE tasks SET start_time = ADDTIME(start_time, diff_times * 100), end_time = ADDTIME(end_time, diff_times * 100) WHERE id > p_which_row;

END $$
DELIMITER ;

CALL adjust_task_times(15, 1);

SELECT * FROM tasks ORDER BY id;

+------+---------------+------------+----------+---------+
| id   | task          | start_time | end_time | minutes |
+------+---------------+------------+----------+---------+
|    1 | Send email    | 12:00:00   | 12:15:00 |      15 |
|    2 | Design banner | 12:16:00   | 12:41:00 |      25 |
|    3 | Call Jake     | 12:42:00   | 12:47:00 |       5 |
+------+---------------+------------+----------+---------+

In case you're wondering, this

ADDTIME(start_time, diff_times * 100)

looks a little odd. Why the 100? In this example the diff_times variable has a value of 5. 5 times 100 equals 500, which is in this case short for 00:05:00

For more details see the manual entry for ADDTIME().


MySQL Loop in Union and select * I am using redash for mySQL data source and trying to create a dashboard.
I have many table with the name as tablename_2021_01_01, tablename_2021_01_02, tablename_2021_01_03, tablename_2021_01_04 and so on. All these tables contain data in the same format. Now i want to pull data from 30 tables in one shot. How to do it?

Hopefully, today you learned that the "Table-per-Day" model for storing your data is a Bad Idea.
Generally speaking, "Table-per-anything" is a poor choice these days.
With proper indexing, there is absolutely no reason why all of this data couldn't live in just one table.
Also, I'm pretty sure MySQL still has the hard limit for the number of tables in a query (including those in any Views used) of [just] 61 tables, which is only double what you're currently asking for.
As to your immediate question, "union all" is your friend.
select * from tablename_2021_01_01
union all
select * from tablename_2021_01_02
union all
select * from tablename_2021_01_03
. . .
where ...


Can Oracle row-level security work with user session credentials? Can row-level security in Oracle be controlled by a user's session credentials or user context? Here's an example to illustrate what I'm looking for:

Our database contains a bunch of sensitive company information, including salaries. HR administrators should be able to see salaries in their search results, but facilities management staff shouldn't, even if they use the same search parameters. The catch is that our app only has a single database user, and all requests go through it, so the security can't be set up to simply check the database user's ID. We'd have to pass the info in as, say, a user context.

We're considering moving to Oracle Access Manager/WebLogic, if it makes a difference.

From the documentation:

  Oracle database server provides a
  built-in application context namespace
  (USERENV) that provides access to
  predefined attributes. These
  attributes are session primitives,
  which is information that the database
  captures regarding a user session.
  Examples include the user name, the IP
  address from which the user connected,
  and a proxy user name if the user
  connection is proxied through a middle
  tier.

  Predefined attributes are useful for
  access control. For example, a
  three-tier application creating
  lightweight user sessions through OCI
  or thick JDBC can access the
  PROXY_USER attribute in USERENV.

So yes, if your middle tier is handling the proxy username correctly.


SQL server agent unable to establish connection I have recently installed sql server 2014 enterprise version on windows 2016 server. For some reason sql server agent is not starting but sql server service is up and running. I am not really sure what is happening. From the sql server agent log file , I could see below information. Do you think I should repair the sql server ? I have read else where it might be due to sql server native client installation problem ? Also, in the event log I noticed the permission related issue. I did some research and I tried to provide proper permissions to that APPID and CLSID but still I could not bring that sql agent up. Thanks

========================================================

             2019-10-09 00:54:15 - ? [100] Microsoft SQLServerAgent
           version
           12.0.6024.0 (X64 unicode retail build) : Process ID 55496
           2019-10-09 00:54:15 - ? [495] The SQL Server Agent startup
           service account is WORKGROUP\D03XXX
           2019-10-09 00:54:15 - ! [150] SQL Server does not accept the
           connection
          (error: 0). Waiting for Sql Server to allow connections.
          Operation attempted was: Verify Connection On Start.
           2019-10-09 00:54:15 - ! [000] Unable to connect to server.
            '(local)';     SQLServerAgent cannot start
          2019-10-09 00:54:15 - ! [298] SQLServer Error: 27, Registry
           information is corrupt or missing. Make sure the provider is
           installed and  registered correctly. [SQLSTATE 08001]
           2019-10-09 00:54:15 - ! [298] SQLServer Error: 27, Client
           unable  to establish connection [SQLSTATE 08001]
           2019-10-09 00:54:15 - ! [165] ODBC Error: 0, A network-
            related or instance-specific error has occurred while.
            establishing a connection to SQL Server. Server is not found
             or not accessible.
             Check if instance name is correct and if SQL Server is.
            configured to allow remote connections.

           For more information see SQL Server Books Online.
           [SQLSTATE. 08001]
           2019-10-09 00:54:15 - ! [382] Logon to server '(local)' failed
           (DisableAgentXPs)
          2019-10-09 00:54:15 - ? [098] SQLServerAgent terminated
           (normally)

========================================================

      EVENT LOG snippet

    The application-specific permission settings do not grant Local.
     Activation permission for the COM Server application with CLSID
     {D63B10C5-BB46-4990-A94F-E40B9D520160}
      and APPID {9CA88EE3-ACB7-47C8-AFC4-AB702511C276}
     to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-
      344959196-2060754871-
       2302487193-2804545603-1466107430) from address.
      LocalHost (Using LRPC)
      running in the application container Unavailable SID
       (Unavailable). This security permission can be modified using
      the Component Services administrative tool.

I have re-installed the sql server 2014 and it fixed this issue. I have realized the sql server native client was not properly installed. Thanks your time


Joining subquery from same table on 1=1 in Oracle I have a table with zip codes, latitude and longitude. I have a working query that takes a radius and latitude and longitude and returns all the zip codes within the required radius:

        SELECT zip.zip,
                ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(:clng, :clat, NULL), NULL, NULL)), (
                            SELECT geom
                            FROM standard_zip
                            WHERE zip = zip.zip
                            ), 0.005) / 1609.34, 2) AS dist
            FROM standard_zip zip
            WHERE zip.zip IN (
                    SELECT DISTINCT (zip) AS zip
                    FROM standard_zip c
                    WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5)) = 'TRUE'
                        AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
                    )
            ORDER BY DIST

Since the lat and long are already in the table I would like to provide a zip code as input for the query instead of having to do a separate lookup first, but this just seems to churn forever..

SELECT
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
LEFT JOIN (SELECT x as lng, y as lat from standard_zip where zip = :zip) inp on 1=1
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST

What am I doing wrong?

MguerraTorres suggested using a CTE, which sounds great. Here's what I tried:

WITH inp as (SELECT x as lng, y as lat from standard_zip where zip = :zip)
SELECT
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST

Problem is, Oracle says inp.lng is an invalid identifier. Why?

I think it should be this:

SELECT
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
   CROSS JOIN (SELECT x as lng, y as lat from standard_zip where zip = :zip) inp
WHERE zip.zip IN (
        SELECT zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST;


Using PHP form to insert geometry from WKT and SRID into SQL Server 2017 I have a PHP 7.3 form that asks the user for a Name, a WKT and an SRID.  I would like to upload the WKT and SRID to SQL Server 2017 so that it creates a geometric object in Upload_WKT_Test using STGeomFromText:

<?php
    if ($_SERVER['REQUEST_METHOD']=="POST") {
        $wkt = $_POST['wkt'];
        $srid = $_POST['srid'];
        $name = $_POST['name'];
        try {
            $wktQuoted = $pdo->quote ($wkt);
            //$wktQuoted = "'$wkt'";
            $sql = "INSERT INTO Upload_WKT_Test (Name, GeomCol1) VALUES (:name, :wktGeom)";
            $wktGeom1 = "geometry::STGeomFromText(";
            $wktGeom = $wktGeom1."".$wktQuoted.", ".$srid.")";
            //echo $wktGeom."

".$name."

";
            $stmnt = $pdo->prepare($sql);
            $theData = [':name'=>$name, ':wktGeom'=>$wktGeom];
            $stmnt->execute($theData);
        } catch(PDOException $e) {
            echo "Error: ".$e->getMessage();
        }
    } else {
        $wkt="";
        $alignment="";
        $srid="";
    }
?>

My init.php:

<?php
    ob_start();
    session_start();
     try {
        $pdo = new PDO( "sqlsrv:Server=localhost\SQLEXPRESS;Database=devdb", "", "");
        $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $pdo->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );
        //$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, true );
    }

    catch( PDOException $e ) {
        //die( "Error connecting to SQL Server" );
        //die(print_r($stmnt->errorInfo(), true));
        echo "Error: ".$e->getMessage();
    }
    $root_directory = "testwkt";
    $from_email = "admin@somewhere.com";
    $reply_email = "admin@somewhere.com";
    include "php_functions.php";
?>

I understand that the preferred method to accept user input via forms is to use a parameterized query to prevent SQL injection.  I believe the source of the error message has to do with the quotes around the WKT are not making it into the INSERT INTO statement.  Is it possible to surround text values from an input form with quotes (WKT) and use it to build a geometry object?

Example WKT entered into form POINT(100 10)

Example SRID 0

Example Name Test

Geometry from WKT (quotes are required):

geometry::STPointFromText('POINT (100 10)', 0)

Resulting error message:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
A .NET Framework error occurred during execution of user-defined routine or
aggregate "geometry": System.FormatException: 24114: The label
geometry::STGeomFrom in the input well-known text (WKT) is not valid.
Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING,
MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE,
CURVEPOLYGON and FULLGLOBE (geography Data Type only).
System.FormatException: at
Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input) at
Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type) at
Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid) at
Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid) at
Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s) .

Upload_WKT_Test table:

CREATE TABLE dbo.Upload_WKT_Test
    ( id int IDENTITY (1,1),
    Name varchar(50),
    GeomCol1 geometry );
GO

WKT - Well Known Text - a way to represent a geometric object (point, line, polygon for example) in text format.  More info here.

SRID - Spatial Reference System Identifier - an integer that represents a coordinate system.  More info here.

More info about working with Geometry Instances in SQL Server and Azure SQL Database can be found here.

More info about STGeomFromText

The answer was provided by Zhorov in Stack Overflow here

Reposting the answer here in the hopes it will benefit someone:

You should include geometry::STGeomFromText in your T-SQL statement and bind the value for $wkt parameter without using PDO::quote:

<?php
    if ($_SERVER['REQUEST_METHOD']=="POST") {
        $wkt = $_POST['wkt'];
        $srid = $_POST['srid'];
        $name = $_POST['name'];
        try {
            $sql = "
               INSERT INTO Upload_WKT_Test (Name, GeomCol1)
               VALUES (:name, geometry::STGeomFromText(:wkt, :srid))";
            $stmnt = $pdo->prepare($sql);
            $theData = [':name'=>$name, ':wkt'=>$wkt, ':srid'=>$srid];
            $stmnt->execute($theData);
        } catch(PDOException $e) {
            echo "Error: ".$e->getMessage();
        }
    } else {
        $wkt="";
        $alignment="";
        $srid="";
    }
?>


How to write a wrapper for a heterogeneous VARIADIC function like jsonb_build_object()? I am just wondering how to write a wrapper function in PostgreSQL (as of 12.x) for a heterogeneous VARIADIC function such as jsonb_build_object?

Most SQL examples of VARIADIC functions I can find are not heterogeneous. The VARIADIC part is essentially an array of elements of the same type. But I need to write a wrapper for jsonb_build_object to do some post-processing. Here, the parameter types in the variadic part (key-value list) are not the same.

Currently, I write:

my_func(jsonb_build_object(k1, v1, k2, v2, ...))

But this boilerplate is becoming boring ...

jsonb_build_object() takes VARIADIC "any" as input. The pseudo-type "any" is a placeholder for any input type. Currently, that only works for a C function. The manual:

  Functions coded in C (whether built-in or dynamically loaded) can be
  declared to accept or return any of these pseudo data types. It is up
  to the function author to ensure that the function will behave safely
  when a pseudo-type is used as an argument type.

  Functions coded in procedural languages can use pseudo-types only as
  allowed by their implementation languages. At present most procedural
  languages forbid use of a pseudo-type as an argument type, and allow
  only void and record as a result type (plus trigger or event_trigger
  when the function is used as a trigger or event trigger). Some also
  support polymorphic functions using the types anyelement, anyarray,
  anynonarray, anyenum, and anyrange.

There is no one-to-one translation for an implementation with another procedural language. There may be workarounds, depending on your exact objective.


How to get currently inserted rows and sum them within Mysql trigger before insert I have a mysql trigger that fires BEFORE INSERT.
My php application is inserting multiple rows at once like so

INSERT INTO table1(id,name,price) VALUES(1,'LH32400', 1990),(2,LH32401,2000);

The idea is that before the data is inserted i want to sum all of the prices, and insert them into another table. I know after insert would work,  but the rest of my logic requires this to be a before insert trigger.

The main problem is that within the trigger body New.price returns null (or i think it does, because the trigger throws an error: total cannot be null in my final insert)

If i try

CREATE TRIGGER triggerName
BEFORE INSERT
ON table1 FOR EACH ROW

BEGIN
DECLARE vSum DOUBLE(10,2);
SET vSum = vSum + NEW.price; // because of FOR EACH ROW i thought that this would happen multiple times

INSERT INTO table2
(
 id,
 date,
 total,
 status
)
VALUES
(
 1,
 SYSDATE(),
 vSum,
 'closed'
);

END

If  on the other hand i try to use a cursor i get stuck

DECLARE cur1 CURSOR FOR SELECT id,name, price FROM ? (i obviously cant use table1 since this is a before insert trigger and no data has been actually inserted into table1)

There is probably a very simple answer to this but my brain is pretty down with concentration and i desperately need help. Any suggestion will be appreciated, thanks.

BEFORE inserting, use OLD.

Is this building something like a "Summary table" for a Data Warehousing application?  If so, consider doing:

INSERT INTO tmp VALUES ...;
INSERT INTO summary_table  SELECT SUM(...),... FROM tmp GROUP BY ...;
INSERT INTO real SELECT * FROM tmp;

There are many variants on that flow.  But note that you get a chance to transform data, do multiple inserts, etc.  And, quite probably the GROUP BY will be faster than doing one the rows one at a time, as you are doing with the TRIGGER.


Managing features for a real estate agency I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table.

The data collected by the agency always uses the same form; a 7 page form structured in different categories.

For example, you have categories like USAGE, INSPECTION INFORMATION, KITCHEN APPLIANCES etc., and within those categories you have several options for example renovation_year.  Sometimes that option is a single field but other times it's a multiple checkbox or a dropdown or something else.  The form is always the same for every piece of Real Estate.

How would I go about saving that information in the database as normalized as possible?

Should I have a table with real_estate_feature_categories and a table real_estate_feature that has a many-to-one relationship?

How would I link a specific real estate property to a field with a value?

Best to avoid entity-attribute-value structures if possible as they complicate the model and are unnecessarily fiddly to work with.  It sounds like the fields are going to be the same or substatnailly the same across all the houses, so you're probably just better adding them onto the property record.

The exception being if you really have a genuine 1:M relationship.  An example might be if you wanted to record (say) dimensions and room type (living room, bedroom, bathroom etc.) by room for a variable number of rooms.


SQL Server Database Diagram I am using SSMS v18, I need to create an ER diagram from my database. As Microsoft removed diagram from new version of SSMS (v18) so now How can I get a diagram for my database??

Database diagrams have been re-added as of 18.1 in SSMS.


USERS Tablespace Best Practices for Application Schema When creating a user/schema to hold external application data, what are best practices for the tablespaces that the user is defaulted to?

Should the user created be defaulted to the USER tablespace for the default tablespace?  Does this hold true for the TEMP tablespace?  Or should this user have a separate default and temp tablespaces created apart from the SYSTEM user tablespaces to hold the application data?

I tried looking for any indication of this in the Oracle documentation, but wasn't able to find anything close to what I was looking for - apologies if this is subjective in nature.

This is a best practice, and the answer is "it depends". For example, if the schema has low accesses and few data, using a different tablespace different to USERS/TEMP isn't really necessary,because you can assign a quota to user's data (in this case, creation of separated tablespaces is a merely question about organization). Conversely, if the schema has an high i/o or has a lot of data, the uses of different tablespaces is a good choice, because you can create them to a different pool of disks (for example, Oracle is installed in magnetic hard disk, and tablespace data is created on a pool of SSD). Note that: in this case, you want also a different tablespace for saving indexes


Indexed View Slows Replication I have setup SQL Server transactional replication, and then added indexed views at the subscriber, but the indexed views appear to really slow down the subscriber and almost bringing it to a halt.

I have tried to reinitialize the subscription, and even recreate the publication, still the problem remains and as soon as I am adding an indexed view it almost brings down replication.

The interesting part is that I already many indexed views on the subscriber and it just worked well, but recently I have tried to add more indexed views and then it all started, and now I am not even able to have even a single indexed view.

Any advice?

OK I found the solution, and I am writing it here not just to help others that encounter a similar problem, but it is also something worth for everyone that works with indexed views.

First some points on indexed views.

Indexed Views

An index view although it is also an "index" it is also a "view", thus it might benefit when the system is updating the indexed view from non-clustered indexes on the base table (and even covering indexes and statistics).

(To prove that point I have added indexes on base tables in a database that wasn't used besides getting replication data, and still the dm_db_index_usage_stats system view showed user seeks, and the only one use it was the indexed view.)

And it appears that the best indexes to add for the benefit of an index view would be for seeking a single value or a small range of values, since this is what the indexed view is basically doing when being used locally or on a subscriber to transactional replication.
Although conditional joins are not prohibited by the indexed view restrictions, still it is a good idea to avoid them (just as in regular queries).

Consider the following join:

FROM a
JOIN b ON b.id = a.id
JOIN c ON c.id = a.id
JOIN final ON final.id = b.FinalID OR (b.finalID IS NULL AND final.id = c.FinalID)

This can cause performance bottlenecks, instead it is better split it up into two extra indexed views and then have a view that combines both using "UNION ALL".
While and indexed view is also a "view" it is also an "index" which in other words that inserts/updates/deletes also has to update the indexed view, thus if the view has performance problems the update will take longer, and in the case of replication if each transaction takes to long then they might pile up after each other, and eventually bring replication to an halt

Please also take in mind when working with replication, that reinitializing the subscriber causes all of the indexes and statistics that have been added at the client to be dropped (if pre_creation_cmd has been set to drop which is the default), and only indexes that are part of the replication itself will be recreated, and one has to recreate all of the subscriber specific indexes manually.

So in my situation I have first added new indexed views that turned out to need better non-clustered indexes, and also some that used conditional joins, but after I reinitialized I didn't bother recreating first the base tables non-clustered indexes and first tried to create the indexed views which caused bottlenecks for the replication process.


Redundant fields in ORDER BY for efficient sorting? Suppose I have a table SortTest with fields Data1, Data2, Sort1, and Sort2, Key1 and Key2 need to perform the following query:

SELECT TOP 1
    Data1,
    Data2
FROM SortTest
WHERE Key1 = @key1 AND
      Key2 = @key2
ORDER BY Sort1, Sort2

In order to opimise it, I created an index for the following sequence

Key1, Key2, Sort1, Sort2

But the execution plan still shows an index scan instead of a seek, for it cannot sort effectively on a field sequence that does not start an index. In order, therefore, to optimise the query, I had to add they keys to the ORDER BY clause, which are, of course, redundant:

SELECT TOP 1
    Data1,
    Data2
FROM SortTest
WHERE Key1 = @key1 AND
      Key2 = @key2
ORDER BY Key1, Key2, Sort1, Sort2

The query now works as expected, but I should like to know whether it can be optimised in a more elegant way.

Correction:

I realized that when I simplified the queries above, there was a grave mistake. One of the condition had an IN, not =, so the real queries (that produce the different plans) have this instead:

WHERE Key1 = @key1 AND
      Key2 = IN (@key2a, @key2b, ...)

which explains (that the key2 values in the result are not fixed and thus the different ORDER BY can result in different output) and the plan difference.

Let thank every body for their help and apologies for the confusion.

This is actually pretty straightforward. When performing a query, SQL Server first looks to identify the rows to be returned. If there are WHERE clause elements, those are checked before the system even considers using an index used for the ORDER BY.

This makes perfect sense if you consider the possibilities.

No index on anything in the WHERE clause -- must perform table or clustered index scan
Index on one element in the WHERE clause -- Scan all rows as filtered by the one element for other matches
Index on all elements in the WHERE clause -- Select all rows based on the index. Must look up data in record.
Index on all elements in the WHERE clause, and INCLUDE of the data elements -- Select all rows based on the index. Use the data elements in the INCLUDE statement.
Index on all elements in the WHERE clause plus all data elements -- Select all rows based on the index, and use the data elements embedded in the index.
Index on the data elements only -- If there is a clustered index, looking up data on that index will be most efficient. Unknown if the query optimizer would scan the data element index to try to make the data retrieval more efficiend, but it is doubtful.

Basically, your best index would be:

CREATE INDEX MyIndex ON MyTable (Key1, Key2, Sort1, Sort2)

The data would be searched on the Key information (which is what an index is designed for), then use the additional information, which is already sorted in the index, for the output.

Edited


Trying to update a table with a customer sales averaging column - Oracle SQL Trying to to write a query to add a column to a sales table in order to average sales amounts for individual customers. I have tried quite a bit, ultimately populating the column with a single value over all the rows (not what I need). I have about 75 customers with a total of hundreds of invoices. Coming up blank. Thanks for any advice or help.

ALTER TABLE t1
ADD AVG_AMOUNT NUMBER(9,2);
UPDATE
  SET AVG_AMOUNT =
    (SELECT CAST(AVG(col1)AS FLOAT)
    FROM t1
    GROUP BY col2;

I tried this but get the same value for the whole column.

ALTER TABLE t1
ADD AVG_AMOUNT NUMBER(9,2);
UPDATE t1
SET AVG_AMOUNT =
  (SELECT CAST(AVG(INVOICE_TOTAL)AS FLOAT)
FROM t1
INNER JOIN t2
ON t1.customer_ID=t2.customer_ID
WHERE t1.customer_ID = t2.customer_ID
);

In the UPDATE statement you do not calculate the AVG per customer but an AVG on all customers. Put the customer identification in the where clause. The GROUP BY option is not needed since the AVG column is the only column in the SELECT statement (see other reply by @_horse_with_no_name):

UPDATE t1
SET AVG_AMOUNT=(SELECT AVG(INVOICE_TOTAL)
                FROM   t1 a1
                where  t1.customer_ID = a1.customer_ID);


Queries from different tables locking each other (LCK_M_X) Background: Was hired to work with JMeter for performance testing but I'm also getting more and more into SQL performance (because of time, and because I like it).  I still suck, and will for a long time.  I will try to explain the best I can.

I have a table (NUMERATION) that is updated by many transactions.  It contains most unique identifiers, so it gets a lot of updates.

This query

UPDATE Numeration WITH (ROWLOCK) SET NumCounter = NumCounter + 1 WHERE Tid = @p0

...is sometimes being locked by queries who have no relationship to this one, and I have no idea why.

There are no triggers in the tables. There is a Foreign Key that references a Unique identifer in another table, I don't know how to map all relationships but I think both queries have FKs on this Identifier

I used sp_WhoIsActive and sp_BlitzFirst to see the session IDs and type of lock (LCK_M_X).  The query plans are below:

https://www.brentozar.com/pastetheplan/?id=r1ebGh7Lr

Hope you can help me with not my homework.

Uncommitted Transactions

As Dan Guzman mentioned, check for uncommitted transactions (e.g. using DBCC OPENTRAN) and look at the locks held by the blocking session. It may be a query timed out, wasn't rolled back properly, and the pooled connection with the open transaction reused. You need to remember locks are held by the session of that 'unrelated' query and it is quite likely that in that session the identifier update was done before and still locked (mentioned by eckes).

Foreign Keys

Aaron Bertrand mentioned foreign keys might be involved in your blocking issue.  Since you confirmed there are foreign keys defined on these tables, I'd like to briefly expand on that.

Take the second update in your execution plan as an example:

UPDATE Entity WITH (ROWLOCK)
SET EntityTypeCid = 2, EntityNumber = 49634989, NumberIssued = 1 WHERE Did = @p10

And this subset of the execution plan for that statement:

As expected, since you're updating the Entity table, there's a "Clustered Index Update" operator performing that update*.  Notice, though, that there's also a seek into the EntityType table, followed by an Assert to make sure that the value being set in Entity.EntityTypeCid (2 in this case) has a corresponding match in EntityType.Cid).

All that to say, the update query looks like it would only acquire locks on the Entity table, when in fact it will also briefly need locks from the EntityType table as well (to validate the foreign key constraint).  So other queries that acquire locks on the EntityType table could block this one.

I don't see any evidence of that happening on the update of Numeration.NumCounter, but if other tables reference NumCounter as a foreign key then the same thing could be happening there (in reverse).

Triggers

Another possibility is that the table has Triggers defined on it, which perform data access to other tables, causing blocking.  You already clarified that there are no triggers involved in these specific tables, so I just mention this for completeness.

Long-Running Transactions

You shared a screenshot of profiler in the comments:

I've highlighted session ID 423 because it looks like the following actions all occur inside a transaction:

The Numeration table is updated
The NumCounter column is selected (presumably from the Numeration table)
Another statement runs - based on your comments and the query plan, I imagine this is updating the Entity table
Then the transaction is committed

The locks on Numeration and Entity will be held for the duration of the transaction when using the default READ COMMITTED isolation level, which could block other sessions trying to read from or update these rows.

In this specific screenshot, all of that happened very quickly.  But if the 3rd statement takes longer than expected, then the lock on Numeration is also held longer, creating a blocking chain like the one shown in your other screenshot:

Notice that loads of sessions are waiting on access to the Numeration table, all of them blocked by session 227 (which is updating the Numeration table).  Session 227 is being blocked by session 222, which isn't shown in your screenshot.

It's hard to make suggestions on what to do about this without knowing what session 222 is doing.  If that's a read query, you might have better luck with an optimistic isolation level like READ COMMITTED SNAPSHOT isolation (RCSI), since in that case readers wouldn't block writers.

The problem might be a higher-level, architectural issue - where this Numeration table is a bottleneck that needs to be scaled out somehow.

* this likely isn't relevant to your question, but the update is also updating 3 nonclustered indexes on that table which contain one or more of the columns being updated.  This is not really obvious in SSMS, but SentryOne Plan Explorer calls it out nicely


Is there a reason to store currency amounts as fractions instead of decimals? I've been reviewing GnuCash's DB design and I found that they are storing amounts as two 64-bit integers, the numerator and denominator of a fraction.

Is there some advantage from a DB perspective to storing financial data this way?  I always learned (in MS SQL Server) to use decimal(18,4) for currencies values.

There's no advantage from a DBMS perspective, but it may allow better data management, if you are dealing with some unusual form of currency.  For example, if you had a currency where the basic unit is the pound, and the pound is composed of 20 shillings, and each shilling is composed of 12 pennies, it would sometimes be impossible to represent an exact number of pennies as a decimal fraction of some number of pounds.

If penny amounts were represented as approximations, round off error would begin to creep in when arithmetic operations were performed.

Such a currency may seem unbelievably quirky, but this is the way British currency worked when Elizabeth II ascended to the throne.  There may be currencies in the world today that still work in a similar way.


You can get the result by using a combination of a WHERE, GROUP BY, and HAVING to return the products that have both the Book and Audio values.

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
where t.key = 'Format'
  and t.value in ('Book', 'Audio')
group by p.id, p.name
having count(distinct t.value) = 2;

See SQL Fiddle with Demo.  Basically, you'll place the values that you want in the WHERE clause - this would be the Book and Audio.  Then you'll GROUP BY the product.id and product.name these are unique to each product. Finally you will use the HAVING clause to count the distinct properties.value that are returned.  This should equal the number of items that you have in your WHERE clause.

This could also be written using conditional aggregation inside of the HAVING:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0

See SQL Fiddle with Demo

If you want to include the Language key, then you can use conditional aggregation in the HAVING clause:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having
  sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0
  and sum(case when t.key = 'Language' and t.value = 'English' then 1 else 0 end) > 0

See Demo


MS SQL Server Cumulative Updates - Best Practices I'm trying to get an idea of what recommended best practices are for the SQL Server Cumulative Updates.

Currently, we run on the idea of "do nothing unless an issue fixed by the CU is one we experience".  That works from an "if it ain't broke, don't fix it" approach, but I'm wondering if that's really a good idea since so many of the CUs have performance enhancements.  We're looking at perhaps adding the CU to the patches applied during our periodic maintenance cycles a month or two after the CU is released.

What do others do, and why?

As an update to the question that affects the answers below, on March 24, 2016, Microsoft's SQL Server team announced they were updating their servicing model.  Microsoft is recommending that all users install all CUs released after January 2016:

  As of January CU releases, these caution messages have been updated,
  we now recommend ongoing, proactive installation of CU’s as they
  become available. You should plan to install a CU with the same level
  of confidence you plan to install SPs (Service Packs) as they are
  released. This is because CU’s are certified and tested to the level
  of SP’s. Also, Microsoft CSS data indicates that a significant
  percentage of customer issues are often previously addressed in a
  released CU, but not applied proactively.  More so, CU’s contain added
  value over and above hotfixes. These also may contain supportability,
  logging, and reliability updates enhancing the overall experience.

  In addition to messaging and guidance updates, we have made updates to
  the CU acquisition model.

  Acquisition changes:

  CUs, of course, have traditionally been made available on the “Hotfix” server (accompanied by the “cautionary language” associated
  with a ‘QFE’ or ‘Hotfix’). The inconsistency here is that CUs are not
  really simple quick hotfixes anymore.  The encompassed updates are
  well tested at individual as well as full system integration levels
  today.
  Therefore, we are now placing the latest CU per mainstream supported baseline (2012 SP2/SP3 and 2014 RTM/SP1 today) on
  microsoft.com/downloads, just as is done for Service Packs today
  Additionally, we will soon release, and maintain, all CUs into the Windows Update Catalog to facilitate acquisition and distribution
  Only interim CU ‘On-Demand’ fixes will be placed on the hotfix server moving forward
  To reduce friction, downloading CUs from the microsoft.com/downloads will not require providing/receiving an email and URL
  We are also evaluating offering the latest CU as an Optional update on Microsoft Update, just like Service Packs today

I am a big advocate of keeping up to the date with the most recent cumulative update, but only if your testing/QA cycle can ensure full and proper regression testing against it. Glenn Berry of SQLskills is also a proponent of this approach.

Microsoft's own recommendation is to only apply CUs that fix issues that affect you, though they have loosened that stance recently. The problem is that you may be affected by one or more of those issues and not know it, or you might be affected tomorrow even if it hasn't hit you yet. Are you going to go through and try to reproduce the issue behind every single fix in every single CU for your branch? Are you going to do this continuously to make sure you still aren't affected?

I'll be quite honest: I've never had an issue applying any CU to my instances. And in fact their CU release process has been a lot more reliable than the service pack release cycle, and in many cases (including most recently with SQL Server 2012 Service Pack 2), you don't want to apply the service pack until the first CU for that branch has been released anyway. In this case there is an interim hotfix to resolve the issue that wasn't fixed in time to make the Service Pack code, but that isn't always true.


MongoDB deployment across cloud and On Premises We are planning to implement MongoDB as a stretch cluster which includes Primary secondary in DC 1, Secondary secondary in DC 2, Arbiter in cloud(DC 3) where DC 1 and DC 2 are on premise servers and DC 3 in cloud

My questions are :

1) Should we need to keep same OS in AWS as our On-premise server or we can keep Amazon Linux also ?
In our case it is RHEL 7.2 in On premise servers

2) If No, will there be a performance impact if we use different OS between replica nodes ?

3) If yes, Do we need to do patch upgrades as frequent as we do in On Prem servers ?

4) How the failover works here in case of write concern as Majority ?

5) What are the other trade-offs in this approach if any ?

1) Should we need to keep same OS in AWS as our On-premise server or we can keep Amazon Linux also ? In our case it is RHEL 7.2 in On premise servers

There is no strict requirement to have the same O/S for all members of a replica set, but in general it is a good idea to have consistent O/S so you have similar configuration and performance tuning across replica set members.

However, since your DC3 (cloud) instance appears to be an arbiter (which only participates in voting) any O/S differences should be irrelevant to performance.

  2) If No, will there be a performance impact if we use different OS between replica nodes ?

Amazon Linux evolved from RHEL, so isn't an entirely different O/S (for example, like Linux vs Windows). However, there may be different configuration or tuning between Linux distributions. I wouldn't expect dramatically different performance between Linux distros, but this is something you'd have to test with your own use case and workload.

  3) If yes, Do we need to do patch upgrades as frequent as we do in On Prem servers ?

This is up to your own security policy, but I would expect patch upgrades to be applied similarly for On-Premise versus cloud servers.

  4) How the failover works here in case of write concern as Majority ?

Assuming you have an equal number of instances in DC1 vs DC2, an arbiter can be useful to ensure a primary can be elected in the event either DC is unreachable. An arbiter cannot acknowledge writes (since it is a voting-only node), so if you have a Primary-Secondary-Arbiter (PSA) configuration you will not be able to acknowledge majority writes if one of your data bearing nodes is unavailable.

I would strongly recommend using PSS (i.e. no arbiter) to support consistent failover with both elections and majority write concern.

  5) What are the other trade-offs in this approach if any ?

As noted above, arbiters cannot acknowledge write so are not recommendable if you want to support fault tolerance with majority write concern. With a PSA configuration degraded to PsA (one secondary down), you have write availability (since a primary can still be maintained) but no longer have replication or data redundancy (since there is only one data-bearing node writing data).


PgAdmin4 unusable when database has lots of schemas I have a production database with 60K database schemas. PgAdmin4 is unusable, I cannot even open "Query Tool" window. I suppose that PgAdmin tries to load database metadata, so that's the reason why it's so slow. Is that true and can it be disabled? Can I do anything to speed it up?

I tried to use other DB clients like DBeaver or TablePlus and I have the same problem.

Yes, it will be because some of the system views will be getting extremely slow. No, switching tools is unlikely to help. There have been updates to how the system views are generated over the years to improve performance with complex databases. But 60,000 schemas? Not even tables, but schemas? That is so far beyond what 99.9% of users run that you are unlikely to find much help.

If you are committed to this approach, you should probably subscribe to the pg mailing lists where you can have an extended discussion about the beat way to manage your database.