Problem creating a transaction in an SSIS package I'm working on a package that needs to use a transaction but I'm currently getting the following error:

SSIS package "CATS-Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001401A at CATS-Package: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
SSIS package "CATS-Package.dtsx" finished: Failure.

Here is what I know so far:

2012 package
I'm running in package deployment
The TransactionOption property is set to Required
Running against a 2008 R2 instance
I can create a distributed transaction manually on the instance and acording to the event viewer MSDTC is running.
MSDTC has the following settings

Can anyone point in a direction to find additional information.

Edit: One of the steps I read said to run DTC on the local machine running the package as well as the server holding the instance.  Once I did that I got a new error:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

With the important part being The transaction manager has disabled its support for remote/network transactions.  Am I missing something in the permissions?  Note: the Allow Remote Clients has also been checked.

I've seen MSDTC ping mentioned (including in the comments below) but when I looked at it I'm not quite sure what to do with it.

I solved the problem.  I had read in several places that DTC needs to be started on the source machine as well as the destination.  So in my place my workstation as well as the server the instance is on.

Once I had turned MSDTC on I received a new error:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

With the important part being The transaction manager has disabled its support for remote/network transactions.

Once there I did additional research and finally realized I hadn't configured my local DTC to allow Network Access etc.  Once I configured it to allow Network DTC Access, Allow Remote Connections and Allow Output connections everything worked smoothly.

I'm not sure this is the minimum permissions required for the local connection but it appears to be.


Looks like you just need to add a WHERE filter to your query:

SELECT
  A, B, C, ...
FROM
  YourTable
WHERE
  D IN ('IA1', 'IA2', 'IA3')
GROUP BY
  A, B, C
;

That way those A, B, C groups that do not have 'IA1', 'IA2' or 'IA3' in column D will be excluded from the output.


MongoDB - sharding an collection with data already in it I'm using Mongo (shell version 2.6.12) in a clustered configuration.  Right now I have a few collections sharded, and was looking to shard another collection.  This collection already has data in it.  Once I run the command to shard the collection and give it it's key, will the cluster take the existing data and spread it across the multiple systems in my cluster, or will the existing data stay put on one server and any new data will then be spread across the other systems?

When you shard an existing collection, MongoDB will split the values of the shard key into chunk ranges based on data size and start rebalancing across shards based on the chunk distribution. Rebalancing a large collection can be very resource intensive so you should consider the timing and impact on your production deployment.

In MongoDB 2.6 (which reached end of life in October, 2016) the balancer will only perform one chunk migration at a time. In MongoDB 3.4 or newer the balancer can perform parallel chunk migrations with the restriction that each shard can participate in at most one migration at a time. As at MongoDB 3.6, that means that a sharded cluster with n shards can perform at most n/2 (rounded down) simultaneous chunk migrations.

A faster approach to sharding a large existing collection would be to pre-split chunks in an empty collection based on the current data distribution for your shard key, and to then dump & restore your existing data into this new sharded collection. The pre-split approach minimizes rebalancing activities by creating appropriate chunk ranges so data is distributed on insert.

There have been significant performance improvements since MongoDB 2.6, so I would strongly recommend upgrading to a supported version of MongoDB (ideally MongoDB 3.4 or newer).


What is Diagnostic.data file in MongoDb data folder? I'm trying to create a backup server for my mongodb using the strategy of copying files.

I wonder what is Diagnostic.data folder? Should I copy that too? or I can simply ignore it?

My Diagnostic.data folder size is around 300MB that is bigger than my other database files.

What about journal folder? Can I ignore it too?

The diagnostic.data file contains the result of db.serverStatus() command in binary format. This will be used by the MongoDB engineers to analyze the behavior of the server if any error happens.

It is not necessary to take a backup of this file.


Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it From this Microsoft doc,+

  n defines the string length and can be a value from 1 through 8,000.
  max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
  The storage size is the actual length of the data entered + 2 bytes.

Please help me understand this.

The max characters for varchar seems to be 8000, which is way less than 2GB worth of data.

I see that there are records in this varchar(max) column of a specific table that have len(mycolumn) > 100 000.
Thus I know I can get way more than 8000 characters into a varchar(max) column.

Question 1: How does the 8000 characters come into play and where should I be aware of it?

Question 2 : will a .net datareader query to this column always return the full result with 100 000+ character?

I can see why you're misunderstanding this - it's a little tricky. These are all valid:

VARCHAR(1) - one character string
VARCHAR(4000) - 4,000 characters
VARCHAR(8000) - 8,000 characters - and if you use a number for this field's definition, that's the highest NUMBER you can use, but watch this:
VARCHAR(MAX) - that one holds up to 2GB.

And yes, if you try to get data out of a VARCHAR(MAX) field, and somebody stored 2GB in there, buckle up.


Can one have Temporary Rows? I understand that Temporary Tables in Postgres and MS SQL Server persist until the end of the session. When the user’s session ends, any temp tables created by that user are dropped automatically. The temp table is not visible between users.

I need both the visibility across user sessions and the temporary nature where the data disappears when the user session ends (either closes gracefully or breaks).

Is there any way to have a permanent table visible to all but temporary rows? Is there any automatic way to have a user’s inserted rows disappear at the end of their session, yet be visible to other users during that session?

You can make a procedure/job that cleans up the tables.  It would:

Check if the user is active
If so, ignore and move on to the next user or complete
If not, truncate/delete the table (after moving the records to a historical table if you desire)

Rinse/repeat every xx minutes.

We have used some variation of this in production. I usually do the coding in a stored procedure, and the "repeat" part comes from a SQL Server Agent job, which executes the procedure on a schedule.  I try to keep it as modular as I can.


Unique combination key MySQL Lets say I have a table with 3 fields:

id1
id2
mapTypeId

What I want is a primary/unique key that is any combination of id1 and id2.  For example if I have this record already in the database:

1, 2, 3

I should not be able to insert a record like:

2, 1, 3

Right now I have id1 and id2 as my composite primary key however I am able to insert the data above, is there anyway to prevent this on the database level?

Here is before trigger you can use to guarantee that id1 and id2 are inserted in min/max order

DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;

Here is some Sample Data:

DROP DATABASE IF EXISTS ryanzec;
CREATE DATABASE ryanzec;
USE ryanzec
CREATE TABLE mytable
(
    id1 INT NOT NULL,
    id2 INT NOT NULL,
    mapTypeId INT NOT NULL,
    primary key (id1,id2)
);
DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;
INSERT INTO mytable VALUES (1,2,3);
INSERT INTO mytable VALUES (2,1,3);
INSERT INTO mytable VALUES (5,4,6);
INSERT INTO mytable VALUES (4,5,6);
SELECT * FROM mytable;

Here it is the Sample Data Loaded

mysql> DROP DATABASE IF EXISTS ryanzec;
Query OK, 1 row affected (0.03 sec)

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

mysql> USE ryanzec
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id1 INT NOT NULL,
    ->     id2 INT NOT NULL,
    ->     mapTypeId INT NOT NULL,
    ->     primary key (id1,id2)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE sum INT;
    ->     SET sum = NEW.id1 + NEW.id2;
    ->     SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    ->     SET NEW.id2 = sum - NEW.id1;
    -> END $$
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;

Watch What Happens When I Insert the Rows:

mysql> INSERT INTO mytable VALUES (1,2,3);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO mytable VALUES (2,1,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO mytable VALUES (5,4,6);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO mytable VALUES (4,5,6);
ERROR 1062 (23000): Duplicate entry '4-5' for key 'PRIMARY'
mysql> SELECT * FROM mytable;
+-----+-----+-----------+
| id1 | id2 | mapTypeId |
+-----+-----+-----------+
|   1 |   2 |         3 |
|   4 |   5 |         6 |
+-----+-----+-----------+
2 rows in set (0.00 sec)

mysql>

Now, no matter what order id1 and id2 are entered, id1 will always be less than or equal to id2

This may not be contextually what you want because id1 and id2 are unique identifiers.


What is this CASE statement doing exactly with the NVL function? case when nvl(Q_IND, 0) = 1 then 1 else 0 end Q_IND

I'm having a hard time understanding what is happening in the above statement. Could someone explain?

From the documentation:

  NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

So for your specific example, if Q_IND is NULL then 0 is returned to the CASE statement. For such scenarios your CASE statement becomes this:

CASE WHEN 0 = 1 THEN 1 ELSE 0 END Q_IND

We all know that 0 is not equal to 1 and thus 0 is returned as Q_IND.


"Alter Table" modify multiple columns vs multiple Alter table single column in INNODB? I am updating collations of my database tables and columns from utf8bin to utf8mb4_bin.
Engine is INNODB with file per table enabled.
I used a query to generate identify all utf8 columns from information_schema database and generated corresponding queries for each.
The generated queries contains "ALTER TABLE" queries for each column separately.
Example:
ALTER TABLE mydb.`cwd_group`
MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
ALTER TABLE mydb.`cwd_group`
MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Is it a recommened to join these two as a single statement? What will be the drawbacks?
ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

I am guessing by joining them as single statement, disk IO is reduced as it not required to write the whole table data multiple times (i.e., to temporary ibd file #sql-*) or does it write only the column data to temporary file? Or how much extra space may it use for the temporary file if I merge the statements?
In the end, I want to run these alter tables in parallel that my server can handle.

You must do it all in a single ALTER TABLE command. Think of it : You are changing the collation of a column, so ALGORITHM=COPY must be done. This requires a full table lock. It is better to execute the collation change of multiple columns in a single ALTER TABLE command than to do it multiple time
There is no drawback in this instance. Why ? Back in May 12, 2011, I wrote about MySQL 5.0's old behavior in executing multiple column modifications (Does MySQL still handle indexes in this way?). Under the hood, MySQL used to execute multiple column changes as individual ALTER TABLE commands. I pointed this out to MySQL back on Oct 10, 2006. They had fixed it since.
Now that ALTER TABLE handles multiple column changes as a single operation, there is no need to change the columns one by one.
Let's say it takes 5 min to change one column. That's 5 minutes the table is locked and no INSERTs, UPDATEs, or DELETEs can happen. You would basically double the time changing two columns to 10 min, triple the time changing three columns to 15 min, and so forth.
Just run the one ALTER TABLE command. MySQL handles it correctly now.
ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local`  char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

GIVE IT A TRY !!!
P.S. You need to specify a length for char. That's why I put (25).
If you are not sure of the length, then use VARCHAR(25).


how to create custom id in sql server with sequence How can I create a table with custom ID like BK-0001001 with a sequence. Sorry I'm a newbie with databases. All help will be appreciated thank you!

You can use a mix between a SEQUENCE and formatting a DEFAULT CONSTRAINT:

CREATE SEQUENCE MySequence
AS int
START WITH 1
INCREMENT BY 1;
GO

CREATE TABLE MyTable
(
    MySeq varchar(20) CONSTRAINT [DF_MyTable_MySeq]
                      DEFAULT FORMAT((NEXT VALUE FOR MySequence), 'BK-000000#'),
    Foo   int,
    Bar   int,

    CONSTRAINT [PK_MyTable] PRIMARY KEY (MySeq)
);
GO

INSERT INTO MyTable (Foo, Bar)
VALUES (1,2),(2,3),(3,4),(5,6);
GO

SELECT * FROM MyTable;
GO

MySeq      | Foo | Bar
:--------- | --: | --:
BK-0000001 |   1 |   2
BK-0000002 |   2 |   3
BK-0000003 |   3 |   4
BK-0000004 |   5 |   6

db<>fiddle here


How to repair mongodb collection when index is corrupt? Mongo version: 2.6 I think one or more of my MongoDB index is corrupt which is not letting me delete the corrupt collection or make a backup with mongodump

Below is the error i get when i try to execute drop

>db.notifications.drop()
    "$err" : "no NamespaceDetails for index: { name: \"_id_\", ns: \"Bnk.notifications\", key: { _id: 1 }, v: 0 }",
            "code" : 17329

What can be done to get this collection removed or repaired if possible ?

Try to repair it by using repairDatabase.

More information:
https://docs.mongodb.com/manual/reference/command/repairDatabase/


What is wrong with this syntax of a multi-table DELETE...ORDER BY? I have the following query:

DELETE proxies, pxpl FROM proxies
JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID
WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR)
ORDER BY pxpl.ProxyListID, proxies.ID

The ordering is needed to avoid DB deadlocks.
However, it yields an error:

  You have an error in your SQL syntax; check the manual that
  corresponds to your MariaDB server version for the right syntax to use
  near 'ORDER BY pxpl.ProxyListID, proxies.ID' at line 4

proxy_xref_proxy_list is a many-to-many relation association table.

(Serge self-Answered the title question; this Answer tries to look beyond that.)

If the deadlock is caused by the same DELETE being run from multiple threads, then simply ignore the deadlock; the other thread will do the work.

If there are other concerns, there are ways to use subqueries instead of a JOIN.  There is even a kludge where an extra level of subquery is a workaround for another error you may encounter.

If you have massive deletes, then see this for tips: http://mysql.rjweb.org/doc.php/deletebig


MySQL performance on Mac vs Linux I know that MySQL is tested more on Linux (Oracle has it's own linux distribution and you canot have LAMP wit MySQL (meaning MySQL has always focused more on Linux)

My intuition says that MySQL has a better performance on Linux than on any other OS (including FreeBSD based MacOS). But I don't have facts to back it up.

Can you point me to any references  which can help me determine whether moving MySQL from MacOS to Linux would give a performance boost= What has been your own experience?

I guess the only other way is to measure difference KPIs on both Linux and MacOS and then try to compare them. But I am trying to put more justifications for purchase of Linux ready hardware and cannot compare the two at the moment.

As seen in the PDF, there's no clear winner when we compare MySql performance between different Kernel.

Since all databases are different, you will have to do benchmark to be sure. I think the hardware spec will affect the performance more than the OS.


find total money of parent node of tree according to a particular pattern actually i am working on a project in which members are added in a tree pattern, and get the payment accordingly.

My tbltestingtree structure is ass follow:

        Id         ParentId        IsLeft           IsRight
         1          Null             Null             Null
         2           1                1               Null
         3           1               Null               1
         4           2                1               Null
         5           2               Null               1
         6           3                1               Null
         7           3               Null               1
         8           4                1               Null
         9           4               Null               1
         10          5                1               Null

the problem is that initially 1500$ are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500$ for each pair.

My problem is to find the query which can return the total income of any given  node.

According to figure node 1 must get 2500$ (1500+500+500) first 500$ is for node 4 and second 500$ is for node 3.

According to figure node 2 must get 1500$ because it has two nodes to its left and one node to its right this means a ratio of (2:1). and has  no pairs

According to figure node 3 must get 0$ because it does not have any nodes in ratio(2:1)

one thing has to be kept in mind that 1500$ will be the first payment and then only the other pairs will be counted, and 1500$ will be given when node has ratio 2:1(two nodes on left and one on right) but no money when ratio is 1:2(one node on left and two on right)

I have found the query which will count all the pairs below a particular node and give receiving amount according to 500$,  but the query has not been able to consider the first condition that is the 2:1 condition

        declare @ParentId as int
         set @ParentId=1

       create table #temp_table_name
        (
          ParentId varchar(30) null,
         )

      ;with Child as
       (
    select id,ParentId from tblTestingTree where id=@ParentId
    union all
    Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree
    inner join Child
    on tblTestingTree.ParentId=Child.Id
       )

  insert into #temp_table_name
  select c.ParentId  from tblTestingTree T join Child c
  on c.Id=t.Id
  WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
  group by c.ParentId
  having COUNT(c.ParentId)>1

  select COUNT(*)*500 as totalmoney from #temp_table_name

  drop table #temp_table_name

Please help

Test Data:

memberid    MemberID    joiningposition packagetype
   RPM00000     NULL         Root   free
   RPM71572     RPM00000    Left    Royal
   RPM323768    RPM00000    Right   Royal
   RPM715790    RPM71572    Left    free
   RPM323769    RPM71572    Right   free
   RPM715987    RPM323768   Left    free
   RPM323985    RPM323768   Right   free
   RPM733333    RPM323985   Right   free
   RPM324444    RPM715987   Right   Royal

create a table to store final value

CREATE TABLE [dbo].[Wallatpayout]
(
    [childid] [varchar](50) NULL,
    [joiningposition] [varchar](50) NULL,
    [DateofJoing] [varchar](50)    NULL,
    [packagetype] [varchar](50) NULL,
    [Total] [int] NULL,
    [FirstPayoutstatus] [varchar](30) NULL,
    [DateofPayout] [datetime]    NULL
)

create procedure [dbo].[sunnypro] as
DECLARE @pId varchar(40) = 'RPM00000';
Declare @Id int set @Id=(select id from registration where hildid=@pId)
begin

-- Recursive
CTE WITH R AS (
    -- Anchor
    SELECT
        BU.DateofJoing,
        BU.childid,
        BU.joiningposition,
        BU.packagetype
        FROM registration AS BU
        WHERE
        BU.MemberID = @pId and
       BU.joiningposition IN ('Left', 'Right')
        or BU.packagetype in('Royal','Platinum','Majestic')
         and BU.Id>@id
   UNION All
    -- Recursive part
    SELECT
         BU.DateofJoing,
         BU.childid,
         R.joiningposition,
        BU.packagetype
    FROM R
    JOIN registration AS BU
        ON BU.MemberID = R.childid
    WHERE
        BU.joiningposition IN ('Left', 'Right') and
      BU.packagetype in('Royal','Platinum','Majestic')
     and BU.Id>@id )
INSERT INTO Wallatpayout
           (childid
           ,packagetype
           ,joiningposition
           ,DateofJoing
           ,Total)
-- Final groups of nodes found
SELECT top 3
R.childid,    R.packagetype,
    R.joiningposition,
    R.DateofJoing,
    Total = COUNT_BIG(*) FROM R where R.packagetype in('Royal','Platinum','Majestic')
GROUP BY
R.childid,
    R.joiningposition,
    R.DateofJoing,
    R.packagetype
    OPTION (MAXRECURSION 0);
end

This code is helpful for multi-level marketing, to find all left node and right node by passing particular id or parentid.


How can I assign different random values to each row in a SELECT statement? Please look at this code:

create table #t1(
  id int identity (1,1),
  val varchar(10)
);

insert into #t1 values ('a');
insert into #t1 values ('b');
insert into #t1 values ('c');
insert into #t1 values ('d');

Now, whenever you execute this

select *,
    ( select top 1 val from #t1 order by NEWID()) rnd
from #t1 order by 1;

you will get a result with where all rows have the same random value. e.g.

id          val        rnd
----------- ---------- ----------
1           a          b
2           b          b
3           c          b
4           d          b

I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.

A clever solution to this is

select t1.id, t1.val, t2.val
from #t1 t1
    join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd

But I simplified the query. The real query looks more like

select *,
    ( select top 1 val from t2 where t2.x <> t1.y order by NEWID()) rnd
from t1 order by 1;

and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of

( select top 1 val from #t1 order by NEWID()) rnd

without the use of cursors.

Edit:
Wanted output:

perhaps 1 call

id          val        rnd
----------- ---------- ----------
1           a          c
2           b          c
3           c          b
4           d          a

and a second call

id          val        rnd
----------- ---------- ----------
1           a          a
2           b          d
3           c          d
4           d          b

The value for each row just should be a random value independent from the other rows

Here is the cursor version of the code:

CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));

DECLARE @id INT
DECLARE @val VARCHAR(10)
DECLARE c CURSOR FOR
SELECT id, val
FROM #t1
OPEN c
FETCH NEXT FROM c INTO @id, @val
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #res
    SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd
    FETCH NEXT FROM c INTO @id, @val
END
CLOSE c
DEALLOCATE c

SELECT * FROM #res

A subquery is evaluated once if possible. I can't recall what the "feature" is called (folding?) sorry.

The same applies to GETDATE and RAND functions. NEWID is evaluated row by row because it in intrinsically a random value and should never generate the same value twice.

The usual techniques are to use use NEWID as input to CHECKSUM or as a seed to RAND

For random values per row:

SELECT
   co1l, col2,
   ABS(CHECKSUM(NEWID())) AS Random1,
   RAND(CHECKSUM(NEWID())) AS Random2
FROM
   MyTable

If you want random order:

SELECT
   co1l, col2
FROM
   MyTable
ORDER BY
   NEWID()

If you want random order with a row order too. ActualOrder order here is preserved regardless of the order of the resultset

SELECT
   id, val,
   ROWNUMBER() OVER (ORDER BY id) AS id
FROM
   #t1
ORDER BY
   NEWID()

Edit:

In this case, we can state the requirement as:

  return any random value from the set for each row in the set
  the random value will be different from the actual value in any row

This is different to what I offered above which simply re-orders rows in various ways

So, I'd consider CROSS APPLY. The WHERE clause force row by row evaluation and avoids the "folding" issue and ensures that val and rnd are always different. CROSS APPLY can scale quite well too

SELECT
   id, val, R.rnd
FROM
   #t1 t1
   CROSS APPLY
   (SELECT TOP 1 val as rnd FROM #t1 t2 WHERE t1.val <> t2.val ORDER BY NEWID()) R
ORDER BY
   id


sql org chart hierarchical table to xml json I have a table of employees and managers:

create table emp_mgr (emp varchar(99), mgr varchar(99))

insert into emp_mgr select 'Adam',NULL; insert into emp_mgr select 'Bob','Adam'; insert into emp_mgr select 'Charles','Bob'; ; insert into emp_mgr select 'David','Bob'; insert into emp_mgr select 'Ethan','Bob'; insert into emp_mgr select 'Frank','Charles'; insert into emp_mgr select 'George','Charles'; insert into emp_mgr select 'Henry','Charles'; insert into emp_mgr select 'Ian','David'; insert into emp_mgr select 'James','Ethan'; insert into emp_mgr select 'Ken','Ethan'; insert into emp_mgr select 'Larry','Ethan'; insert into emp_mgr select 'Mike','Frank'; insert into emp_mgr select 'Ned','Frank'; insert into emp_mgr select 'Oliver','Frank'; insert into emp_mgr select 'Peter','Frank'; insert into emp_mgr select 'Quinn','George'; insert into emp_mgr select 'Robert','George'; insert into emp_mgr select 'Scott','George'; insert into emp_mgr select 'Ted','George'; insert into emp_mgr select 'Urban','George'; insert into emp_mgr select 'Victor','Henry'; insert into emp_mgr select 'Wes','Ian'; insert into emp_mgr select 'Xavier','James'; insert into emp_mgr select 'Young','James'; insert into emp_mgr select 'Zach','James';

and I want to generate this json file from it :

{ name:"Adam" , children:[   { name:"Bob" , children:[     { name:"Charles" , children:[       { name:"Frank" , children:[         { name:"Mike" },         { name:"Ned" },         { name:"Oliver" },         { name:"Peter" }       ]},       { name:"George" , children:[         { name:"Quinn" },         { name:"Robert" },         { name:"Scott" },         { name:"Ted" },         { name:"Urban" }       ]},       { name:"Henry" , children:[         { name:"Victor" }       ]}     ]},     { name:"David" , children:[       { name:"Ian" , children:[         { name:"Wes" }       ]}     ]},     { name:"Ethan" , children:[       { name:"James" , children:[         { name:"Xavier" },         { name:"Young" },         { name:"Zach" }       ]},       { name:"Ken" },       { name:"Larry" }     ]}   ]} ]}

Here's what I tried - With a recursive xml query..

CREATE function [dbo].[SelectChild](@name as varchar(99))
returns xml
begin
    return (
        select
            employee as "@name",
            dbo.SelectChild(employee)
        from emp_mgr
        where manager = @name
        for xml path('record'), elements
    )
end

select
    employee as "@name",
    dbo.SelectChild(employee)
from emp_mgr
where manager is null
for xml path ('record')

..I was able to generate this xml :

<record name="Adam">   <record name="Bob">     <record name="Charles">       <record name="Frank">         <record name="Mike" />         <record name="Ned" />         <record name="Oliver" />         <record name="Peter" />       </record>       <record name="George">         <record name="Quinn" />         <record name="Robert" />         <record name="Scott" />         <record name="Ted" />         <record name="Urban" />       </record>       <record name="Henry">         <record name="Victor" />       </record>     </record>     <record name="David">       <record name="Ian">         <record name="Wes" />       </record>     </record>     <record name="Ethan">       <record name="James">         <record name="Xavier" />         <record name="Young" />         <record name="Zach" />       </record>       <record name="Ken" />       <record name="Larry" />     </record>   </record> </record>

but don't know how to get the 'children' tag in there, and then how to convert it to json format ?

How do i generate a json file from hierarchical organization data using SQL ?

You can use the for xml path('') trick to concatenate the strings you need directly in the recursive function.

Something like this should do it for you:

create function dbo.SelectChild(@name as varchar(99)) returns varchar(max)
begin
return
(
  select ',"children":[' +
           stuff((
                 select ',{"name":"' +
                          E.employee + '"'+
                          isnull(dbo.SelectChild(E.employee), '')+'}'
                 from dbo.emp_mgr as E
                 where E.manager = @name
                 for xml path(''), type
                 ).value('text()[1]', 'varchar(max)'), 1, 1, '')+']'
)
end

go

select '{"name":"' + E.employee + '"'+isnull(dbo.SelectChild(E.employee), '')+'}'
from dbo.emp_mgr as E
where E.manager is null

Result:

{
   "name":"Adam",
   "children":[
      {
         "name":"Bob",
         "children":[
            {
               "name":"Charles",
               "children":[
                  {
                     "name":"Frank",
                     "children":[
                        {
                           "name":"Mike"
                        },
                        {
                           "name":"Ned"
                        },
                        {
                           "name":"Oliver"
                        },
                        {
                           "name":"Peter"
                        }
                     ]
                  },
                  {
                     "name":"George",
                     "children":[
                        {
                           "name":"Quinn"
                        },
                        {
                           "name":"Robert"
                        },
                        {
                           "name":"Scott"
                        },
                        {
                           "name":"Ted"
                        },
                        {
                           "name":"Urban"
                        }
                     ]
                  },
                  {
                     "name":"Henry",
                     "children":[
                        {
                           "name":"Victor"
                        }
                     ]
                  }
               ]
            },
            {
               "name":"David",
               "children":[
                  {
                     "name":"Ian",
                     "children":[
                        {
                           "name":"Wes"
                        }
                     ]
                  }
               ]
            },
            {
               "name":"Ethan",
               "children":[
                  {
                     "name":"James",
                     "children":[
                        {
                           "name":"Xavier"
                        },
                        {
                           "name":"Young"
                        },
                        {
                           "name":"Zach"
                        }
                     ]
                  },
                  {
                     "name":"Ken"
                  },
                  {
                     "name":"Larry"
                  }
               ]
            }
         ]
      }
   ]
}


Oracle - Auditing for only some top-level statements I know that we can turn on auditing for all top-level statements using the following
audit all statements

I would like to continue logging only top-levels statements, but not all of them. For example, I dont want to audit alter system, create cluster etc.

Is this possible?

Oracle does not support negative audit rules.

The only exception is the EXCEPT clause for Unified Audit policies which excludes the given users from auditing, but not statements.


Need to understand parallel query execution error Today we experienced a degradation in performance on our production sql server.  Durring the time this occurred we logged several "The query processor could not start the necessary thread resources for parallel query execution" errors.  The reading that I've done suggests that this has to do with how many CPUs to use when executing a complex query.  However when I checked during the outage our CPU Utilization was only at 7%.  Is there something else this could be referring too that I haven't come across yet?  Is this a likely culprit of the performance degradation or am I chasing a red herring?

My sp_configure values for this are as follows:

name                                minimum maximum config_value run_value
cost threshold for parallelism      0       32767   5            5

Few months ago, I faced similar situation wherein the MAXDOP setting was default and a run away query exhausted all the worker threads.

As Remus pointed out this is called worker thread starvation.

There will be a memory dump created on your server when this condition occured.

If you are on 2008R2+SP1 and up then sys.dm_server_memory_dumps will give you the dump file location as well.

Now back to the problem :

There is 1 scheduler monitor thread per NUMA node and since you have 2 NUMA nodes there will be 2 scheduler monitor threads which are responsible for health checking of all the schedulers every 60 secs for that particular NUMA node while making sure that the scheduler is stuck or not.

Each time a new work request is pulled from the schedulers worker queue, the work processes counter is incremented. So if the scheduler has work request queued and has not processes one of the work requests in 60 sec the scheduler is consider stuck.

Due to a run-away query or extensive parallelism, there arises a condition of worker threads begin exhausted as all the threads are occupied by that single run-away query or excessive prolonged blocking and no work can be done unless that offending process gets killed.

Your best bet is to first tune your Max Degree of Parallelism setting. Default of 0  means SQL Server can use all available CPU’s for parallel processing and there by exhausting all the worker threads.

There are many reasons that can lead to exhaustion of worker threads :

Extensive long blocking chains causing SQL Server to run out of worker threads
Extensive parallelism also leading to exhaustion of worker threads
Extensive wait for any type of "lock" - spinlocks, latches. An orphaned spinlock is an example.

Refer to my answer here that will show you how you can calculate MAXDOP value for your server instance.

Also, highly recommend you to start collecting Wait stats information about your database server instance.


Tightly coupling tables in postgresql Our web-application is something like - users can create groups(request for membership of a group also), ask questions, answer etc. It is still in the development stage. What I want to do is tightly couple some of the tables.
For example - Whenever a new member joins a group(i.e when that table is being updated) it should be checked whether he/she is a valid user or Whenever someone asks a question in a group, before inserting the question in the table, it should be checked whether that user is a a member of that group etc.
I cannot do these checks using foreign keys because, the group mmbers are store in this way

group_id    members

mygroup     member1,member2,member3
group001    member2,member10,member2,..<br>

As you can see they are stored by seperating commas. So I cannot put the foreign key constraint.

I do not want to do these checks in each and every servlet. I would like to do them in the database itself on insertion. Please tell me how to proceed with this? Thank you.

The only sensible solution is to normalize your model and get rid of those dreaded comma separated values. Then you can use a proper foreign key.

Something like:

create table users
(
    user_id   serial not null primary key,
    user_name text not null
);

create table groups
(
    group_id   serial not null primary key,
    group_name text not null
);

create table members
(
    user_id  integer not null,
    group_id integer not null,
    primary key (user_id, group_id),
    foreign key (user_id) references users (user_id),
    foreign key (group_id) references groups (group_id)
);

If for some reason you would like to have the comma separated lists (for display convenience) you can always create a view that returns this:

create view v_members
as
select g.group_id,
       g.group_name,
       string_agg(u.user_name) as group_members
from members m
  join users u on u.user_id = m.user_id
  join groups g on g.group_id = m.group_id
group by g.group_id, g.group_name
;


NOT (a=1 AND b=1) vs (a1 AND b1) In the WHERE clause of a SQL query I would expect these two conditions to have the same behavior:

NOT (a=1 AND b=1)

vs

a<>1 AND b<>1

The first condition behaves as expected, and while I epxect the second condition to do the same thing, it does not.

This is very basic stuff, but ashamedly I can't see what I'm doing wrong.

They are not equivalent.

NOT (a=1 AND b=1)

is equivalent with:

(NOT a=1 OR NOT b=1) <=> (a<>1 OR b<>1)

This equivalence is known as De Morgan's Law. See for example:

https://en.wikipedia.org/wiki/De_Morgan%27s_laws

A nice technique for proving/disproving equivalences for boolean algebra expressions is to use a cte for the domains, and compare the expressions side by side:

with T(a) as ( values 0,1 )
   , U(a,b) as (select t1.a, t2.a as b
               from t as t1
               cross join t as t2
)
select a,b
    , case when not (a=1 and b=1) then 1 else 0 end
    , case when a<>1 and b<>1 then 1 else 0 end
from U

A           B           3           4
----------- ----------- ----------- -----------
          0           0           1           1
          0           1           1           0
          1           0           1           0
          1           1           0           0

Edit: Since DB2 does not support Boolean datatype I expanded the example at:

http://sqlfiddle.com/#!15/25e1a/19

The rewritten query looks like:

with T(a) as ( values (0),(1),(null) )
   , U(a,b) as (select t1.a, t2.a as b
                from t as t1
                cross join t as t2
)
select a,b
     , not (a=1 and b=1) as exp1
     , a<>1 or b<>1 as exp2
from U;

The result of the query is:

a       b       exp1        exp2
--------------------------------
0       0       true        true
0       1       true        true
0       (null)  true        true
1       0       true        true
1       1       false       false
1       (null)  (null)      (null)
(null)  0       true        true
(null)  1       (null)      (null)
(null)  (null)  (null)      (null)

As shown exp1 and exp2 are equivalent.


Accepting only SSL connections to MS SQL server I have followed http://support.microsoft.com/kb/316898 to force encryption on my MS SQL server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right track?

Here is my detailed steps :

makecert -r -pe -n "CN=slc02xla.company.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\my.cer
I imported the same certificate into the trusted Root Certification Authorities Store
In SQL Server Configuration Manager, expandrf SQL Server Network Configuration, right-clicked Protocols for , and then selected Properties.
On the Certificate tab, selected the desired certificate from the Certificate drop-down menu, and then clicked OK.
On the Flags tab, selected Yes in the ForceEncryption box, and then clicked OK to close the dialog box.
Restarted the SQL Server service.

Anything missing?

Assuming you are using the Force Protocol Encryption option in SQL Server Configuration Manager for the server then nothing but SSL will work (assuming you have a certificate configured).

Clearly, the only way to know is to test.  Use SSMS or SQLCMD to attempt a connection that does NOT use SSL.  If it connects, you have done something wrong.


Mysql multi-master active-active replication : replication doesn't happen for all slaves so i created a multi-master replication with bellow servers two for master and two for slave.

hostname : master-1
ip : 192.168.120.107

hostname : master-2
ip : 192.168.120.111

hostname : slave-1
ip : 192.168.120.117

hostname : slave-2
ip : 192.168.120.118

the two masters (master-1,master-2) are deployed as active-active replication and works well and bellow is the configuration files /etc/mysql/my.cnf :

master-1 :

[mysqld]
#
# * Basic Settings
#
server-id       = 1
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

master-2 :

[mysqld]
#
# * Basic Settings
#
server-id       = 2
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = relay-log-bin
relay-log-index = relay-log-bin.index
skip-external-locking

masters work well. the two slaves are connected to the masters like this :
slave-1 is the master-1 's slave and slave-2 is the master-2 's slave. and these are the configuration files :

slave-1 :

[mysqld]
#
# * Basic Settings
#
server-id       = 3
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

slave-2 :

[mysqld]
#
# * Basic Settings
#
server-id       = 4
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

when a master make changes to the database his slaves changes two, it mean works well for example when master-1 changes the database the changes will be replicated to master-2 and slave-1 and changes will be done in those as well. my problem is i need the changes happen in all slaves for example when master-1 changes the database ,slave-2 change his database like master-2 and slave-1. how can i make this happen?

Looking at you config, it seems you are missing log-slave-updates in your my.cnf files.

You will need to add this in to both masters my.cnf files and restart them.

This tells the slave database to take anything it receives from it's master, and copy from the relay log to the binary log so it can be replicated onwards.

e.g.

Master 1 Updates a table. It gets written to the binary log on Master1

This is replicated to Master 1's slaves (which are slave 1 and Master2)

In order for it to proceed to slave 2, master 2 has to be told to add it to it's binary log. This is what log-slave-updates does.


How properly call stored procedure from EXEC(SQL)? I have engine is SQL which uses EXEC(SQLString) to run stored procedures. I do that because I need dynamically determine which stored procedure to call and to prepare parameters for that procedure. And I need that logic to be present.

So is this a good way to call stored procedure or I have to use sp_executesql or there is another way?

I ask this question because I have two timings - 1st is to prepare SQLString and to call procedure and call another small procedure and 2nd is the execution of the called procedure itself. It works fine almost all the time - the timings are very close. But from time to time I got huge difference between them - the 2nd timing is the same, but 1st is several times bigger (for example 3ms vs 90ms). I suppose some compilation takes place, but this stored procedure is executed very often.

REMARK:

I call sp_ from EXEC(SQL), not from EXEC sp_.

Here is the code how inner SP is called:

BEGIN
    -- DECLARE results table (results from SP are get via select and insert into from execution)
    DECLARE @table AS TABLE (
        ErrorTxt NVARCHAR(MAX) NOT NULL,
        XMLOut XML NULL);

    -- Prepare SQL for calling SP
    SET @sSQL = 'EXEC dbo.sproc_Method_0000129' +
        CASE
            WHEN (@iRes = 0) THEN '9'
            WHEN (@IsOut = 1) THEN '8'
        END + '_' +
        RIGHT('000' + CAST(@IDParam3 AS VARCHAR(3)), 3) + ' ' +
        '''' + REPLACE(CAST(@XMLIn AS VARCHAR(MAX)), '''', '''''') + ''', ' +
        ISNULL(CAST(@IDSrv AS VARCHAR(10)), 'NULL') + ', 0';

    -- EXEC SP and get results
    INSERT @table EXEC(@sSQL);
    SELECT TOP 1 @sErrorText = ErrorTxt, @XMLOutput = XMLOut FROM @table;

    -- Add log for timing
    EXEC dbo.sproc_AddTiming @XMLExtraParam OUT, 'SlowOperation', @dtOperation OUT;
END;

You can use it either way, however there is a one thing you should keep in mind
EXEC() function is SQL injection prone

Take a look at this:

DECLARE @Data varchar(50)
SET @Data= ''''';TRUNCATE TABLE corruptTable'
execute (N'select * from corruptTable where Data = ' + @Data)

Executing this command, will give you no results (since we added that our data equals empty string) however, if you query the table ,table will be empty.

Where as in sp_executesql you are explicitly declaring parameters
and

@Param = ''''';TRUNCATE TABLE corruptTable' will be compared as it is

So in the example:

DECLARE @Param varchar(50)
SET @Param= ''''';TRUNCATE TABLE corruptTable'
execute sp_executesql N'select * from corruptTable where Data = @Data',N'@Data varchar(50)',@Data = @Param

It wont yield any results, but it wont execute truncate table, because like i said, it is only treated as a parameters.
Other than that, performance vise, there is no difference.

Note that you should call procedure with schema name included, and should be aware that dynamic SQL executions will create a new plan individually from stored procedure each time you call it.

Update

Since you are testing it on production,and the tables are busy, there are many reasons why "sometimes" its slow. I cannot give you exact details because i dont know logic and environment in the first procedure, but here are some things to watch for:

Statistics Update in process: before plan is created, query optimizer checks whether statistics are up to date - since table is busy, statistics will be updated automatically at certain insert/update threshold and it can happen that it waits for statistics to be updated first before it can proceed, OR it can update statistics by itself so again you have to wait
Locks : I assume your first procedure is retrieving records from a certain table. It can happen that while it tried to get certain records, these records/table are already locked by some update/insert/delete statement. So again you had that slight delay before it actually executed.

Also if you are getting a lot of records from first procedure, consider using temp table instead, table variable is not the best choice.

And lastly you can always diagnose procedure execution using Extended Events, and log it to a table/file if it exceeds certain threshold, along with execution plan,and waits so you can compare it with the regular executions.


What is the optimum way to store, update, and analyze integer data for 100+ attributes? I need to store the count of all individual characters (A-Z, a-z, 0-9, spaces, punctuation marks, all symbols...) that occur in different texts which will be frequently changing.

I will need to update all counts and analyze all of the counts frequently.

Is one table with close to 100 integer columns (one for the count of each character) the most optimum way to store and retrieve this data?

Assumed business rules
As I currently understand your specifications, the following assertions are of prime relevance:

A User inscribes zero-to-many Texts.
A User registers zero-to-many Characters.
A Character is counted in zero-to-many Sums.
A Text contextualizes one-to-many Sums.
A User computes zero-to-many Sums.

Logical data model
And then, from the above enumerated assertions, I have derived the IDEF1X logical data model shown in Figure 1.

As you can see, with this structure you could have a series of Sums related to a specific Character in the context of a particular Text. Instead of performing an UPDATE each time that a new Sum is computed, you just have to INSERT a new row, which would hold the exact point in time that such Sum was calculated.
In this way, you do not need to have a table with about 100 columns, instead, as stated in comments, I suggest using a Character table that stores each character occurrence in an individual row.
I have also added a User entity in order to depict a more complete scenario, since I assume that all the sums (or counts) are computed by a determined user, probably with the aid of an automatic method.
This structure allows you to analyze with ease the trends of the sums that a given Character presents in relation to a particular Text by comparing the set of values contained in the aforementioned Sum series.


MySQL: How to match data with regular expression string I have a postcode column in my table with values like AB45*, RD4*, ADB567 etc.
Now I want to match my data with this column. The * means that a string starting with AB45 should match with this column. If I use AB45 45D it should fetch a row containing AB45*
If I use RD489 on my field, it should match with RD4* column value.
What can I use to achieve this requirement?

CREATE TABLE patterns (pattern VARCHAR(16));
INSERT INTO patterns VALUES ('ABC4*'), ('RDTU22*');
SELECT * FROM patterns;

| pattern |
| :------ |
| ABC4*   |
| RDTU22* |

CREATE TABLE values_to_check (val VARCHAR(255));
INSERT INTO values_to_check VALUES ('ABC41'), ('ABC49'), ('RDTU225'), ('RDTU229');
SELECT * FROM values_to_check;

| val     |
| :------ |
| ABC41   |
| ABC49   |
| RDTU225 |
| RDTU229 |

SELECT values_to_check.val, patterns.pattern
FROM values_to_check
JOIN patterns ON values_to_check.val LIKE REPLACE(patterns.pattern, '*', '%');

val     | pattern
:------ | :------
ABC41   | ABC4*
ABC49   | ABC4*
RDTU225 | RDTU22*
RDTU229 | RDTU22*

SELECT values_to_check.val, patterns.pattern,
       values_to_check.val LIKE REPLACE(patterns.pattern, '*', '%') does_they_match
FROM values_to_check
JOIN patterns;

val     | pattern | does_they_match
:------ | :------ | --------------:
ABC41   | ABC4*   |               1
ABC41   | RDTU22* |               0
ABC49   | ABC4*   |               1
ABC49   | RDTU22* |               0
RDTU225 | ABC4*   |               0
RDTU225 | RDTU22* |               1
RDTU229 | ABC4*   |               0
RDTU229 | RDTU22* |               1

db<>fiddle here
PS. REPLACE() converts the pattern to the format clear for LIKE (replaces the asterisk with the percent sign).


What is more efficient, a where clause or a join with million plus row tables? We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows.

Sample structures:

MasterTable (Id, UserId, Created, Updated...) -- 15MM Rows
DetailsTable (Id, MasterId, SomeColumn...) -- 250MM Rows
UserTable (Id, Role, Created, UserName...) -- 12K Rows

We regularly have to do a few queries against all these tables. One is grabbing statistics for free users (~10k free users).

Select Count(1) from DetailsTable dt
join MasterTable mt on mt.Id = dt.MasterId
join UserTable ut on ut.Id = mt.UserId
where ut.Role is null and mt.created between @date1 and @date2

Problem is this query will some times run a long damn time due to the fact that the joins happens long before the where.

In this case would it be wiser to use wheres instead of joins or possibly where column in(...)?

For modern RDBMS there is no difference between "explicit JOIN" and "JOIN-in-the-WHERE" (if all JOINS are INNER) regards performance and query plan.

The explicit JOIN syntax is clearer and less ambiguous (see links below)

Now, the JOIN-before-WHERE is logical processing not actual processing and the modern optimisers are clever enough to realise this.

Your problem here is most likely indexing.

Please show us all indexes and keys on these tables. And the query plans

Note: this question would have been close on StackOverflow for being a duplicate by now... COUNT(1) vs COUNT(*) is another busted myth too.

https://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338
https://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct/3003533#3003533
https://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649


Monitor Oracle query plans for PARTITION LIST (ALL) Is there any way I can monitor all the queries running on an Oracle instance and report which  queries are using PARTITION LIST (ALL)?

What I'm essentially asking for is a way to automatically EXPLAIN PLAN on every query, then search the plan results for queries that are not eliminating partitions properly.

I would write a job procedure which would get all running queries:

SELECT sql_id, sql_child_number FROM V$SESSION WHERE SQL_ID IS NOT NULL AND TYPE='USER';

and then loop through that cursor and get explain plan

select * from table(dbms_xplan.display_cursor(v_sql_id, v_sql_child_number));


Query the Owner of SQL Server 2008 R2 Jobs Is there a way to query the owner of all jobs in SQL Server 2008 R2? I discovered when maintenance plans are edited the owner gets changed, so I want to make sure they are all owned by sa.

This will generate the commands to change all non-sa-owned jobs to sa. You just need to copy and paste from the results pane to the top pane (or a new query window), and execute.

SELECT 'EXEC msdb.dbo.sp_update_job @job_id = '''
        + CONVERT(VARCHAR(36), job_id)
        + ''', @owner_login_name = ''sa'';'
    FROM msdb.dbo.sysjobs
    WHERE owner_sid <> 0x01;


What should I set max_connections to for MySQL? I have MySQL 5.1.35 on Linux Centos.  The Linux server has 2GB RAM with 14GB of disk space.

I have created some web services using the Restlet framework in Java that has 1,000+ users.

What should I set max_connections to for maximum concurrent connections?

There's no formula here. You should limit your database according to what you think is reasonable according to your application needs.

Typically, servers with applications using a connection pool shouldn't need more than a few hundred concurrent connections. Small-Medium sized websites may suffice with 100 - 200.

I usually setup a new server with some 500 - 800 value of max_connections and see how it goes. You can always change dynamically via

set global max_connections := 567;

Make sure, though, you set up a proper open_files_limit. On linux, your process is limited to 1024 files, by default. This is very low, since every thread, connection, and, of course, file -- make for a file handle in linux. So set open_files_limit to some generous number (say 8192) to clear up your many connections with the operating system.

I should note I have worked with MySQL servers with thousands of open connections - it's cool. But, most of the time, the vast majority of these connections would sit and do nothing (be idle).

To sum up, I would use what appears to be normal application needs + some threshold for spike events.


Are there any (functional or nuanced) differences between rebuilding an index and a full drop/create? If so, what are they? I have been working on index fragmentation for indexes that are heavily used and causing performance decreases. Our standard job checks fragmentation levels and reorgs if >5% and <30%, and rebuilds if >30%. As far as I have found these don't seem to be functionally different but I'm being asked if a drop/create is more effective as a client tried this on their own and believes they saw greater improvement. Is this truly possible?

This is comparing a full DROP and CREATE vs ALTER INDEX REBUILD on a non-clustered index.

From Microsoft SQL Server 2012 Internals, page 366

  You can completely rebuild an index in several ways. You can use a simple combinination of DROP INDEX followed by CREATE INDEX, but this method is probably the least preferable.

But appeals to authority are boring, so let's test it ourselves!

Performance:

Set up a table with a non-clustered index, load it with junk, and build a table for logging time.

CREATE TABLE IndexTest
(ID int identity(1,1) primary key clustered,
Junk uniqueidentifier)

CREATE TABLE IndexTestLogging
(Operation char(10),
TimeInms int)
GO

INSERT IndexTest
SELECT TOP 1000000 NEWID() AS Junk
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

CREATE INDEX IX_IndexTest ON IndexTest(Junk)
GO

Now let's build some procs that will DROP/CREATE or REBUILD, plus log (approximately) the time taken:

CREATE PROC usp_DropCreate
AS
DECLARE @t1 datetime,
@t2 datetime

--scramble the table for fragmentation
UPDATE IndexTest
SET Junk = NEWID()

SET @t1 = GETDATE()
DROP INDEX IX_IndexTest ON IndexTest
CREATE INDEX IX_IndexTest ON IndexTest(Junk)
SET @t2 = GETDATE()

INSERT IndexTestLogging
SELECT 'DropCreate' AS Operation, DATEDIFF(millisecond,@t1,@t2) AS TimeInms
GO

CREATE PROC usp_Rebuild
AS
DECLARE @t1 datetime,
@t2 datetime

--scramble the table for fragmentation
UPDATE IndexTest
SET Junk = NEWID()

SET @t1 = GETDATE()
ALTER INDEX IX_IndexTest ON IndexTest REBUILD
SET @t2 = GETDATE()

INSERT IndexTestLogging
SELECT 'Rebuild' AS Operation, DATEDIFF(millisecond,@t1,@t2) AS TimeInms

OK, now we can run each procedure multiple times and gather data

EXEC usp_DropCreate
GO 10

EXEC usp_Rebuild
GO 10

SELECT AVG(
         CASE
         WHEN Operation = 'DropCreate' THEN TimeInms
         END) AS AvgDropCreate,
       AVG(
         CASE
         WHEN Operation = 'Rebuild' THEN TimeInms
         END) AS AvgRebuild
FROM IndexTestLogging

Here are my own results: average time in ms for DROP/CREATE: 2547, for REBULD: 1314

It looks like in my contrived example, the winner is REBUILD.

Constraints

What if the index was created to support a constraint? In this case, a simple DROP and CREATE will fail because you have to drop the constraint first. Let's look at the clustered index from the previous example.

--Find out what the clustered index is named - it will be different for you
SELECT name
FROM sys.indexes
WHERE object_id = OBJECT_ID('IndexTest')

--enjoy the error message
DROP INDEX PK__IndexTes__3215078707C12930 ON IndexTest

Note that the other two methods succeed:

CREATE UNIQUE CLUSTERED INDEX PK__IndexTes__3215078707C12930 ON IndexTest(ID)
WITH DROP_EXISTING

ALTER INDEX PK__IndexTes__3215078707C12930 ON IndexTest REBUILD

Of course, like Erik alluded to, your actual problems probably have nothing to do with fragmentation, but hey, this was fun.


Is there a way to throw an error on transaction commit, if a specific stored procedure was not called as part of the transaction? I want a transaction commit to fail if this special stored procedure was not executed before the commit.

I have achieved this in the past using SQL Server (I forget how), and it this special stored procedure wrote various meta data about the transaction commit (eg, the commit time) to an audit table. This was enforced at the database level so that if a user or an application did not call this special stored procedure before the transaction commit, an error was thrown.

We added an additional control to enforce that changes to specific tables could not be made outside of a transaction.

This allowed us to track changes to data even if applications did not behave as intended.

I want to achieve the same thing with PostgreSQL.

There are several ways to approach this.

The first way is to just use triggers -- which operate inside the same transaction. When the transaction commits, the changes across all tables will be visible to others.

If you really don't want to use triggers, then you could use LISTEN / NOTIFY. You need one process that runs all the time that executes a LISTEN.

Then inside your transaction (maybe from a trigger) you call NOTIFY. The listning party won't be notified until after the transaction commits. It can receive a payload (maybe the IDs of the changed records?) and if it queries for that data it will have been committed and therefore be visible. Yes, the syncing that you want to be done will be done asynchronously so there will be a slight delay before everything is in sync again -- how long this takes depends on the complexity of your logic.


Data Migration: Unload/load of GENERATED ALWAYS TIMESTAMP While migrating data from one Db2 database to another we need to retain the ROW CHANGE TIMESTAMP of the migrating data in the target table CFFOLIO.FOLIO

We have a ROW CHANGE TIMESTAMP attribute in the target tables. The constraint is like in the table shown below:

CREATE TABLE "CFFOLIO"."FOLIO" (
"FOLIO_ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
"FOLIO_STATE_ID" BIGINT NOT NULL,
"TIMESTAMP" TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
"ASSIGNEE" VARCHAR(50 OCTETS),
"LOCKED_BY" VARCHAR(50 OCTETS),
"LOCKED_TIMESTAMP" TIMESTAMP,
"FOLIO_PRIORITY_TYPE_ID" BIGINT NOT NULL,
"CONTEXT_ID" BIGINT NOT NULL,
"NAME" VARCHAR(100 OCTETS),
"DESCRIPTION" VARCHAR(2000 OCTETS),
"FOLIO_KEY" VARCHAR(50 OCTETS),
"SCORE" DECIMAL(6 , 2) NOT NULL,
"FRAUD_ASSESSMENT_VALUE_ID" BIGINT,
"DUE_TIMESTAMP" TIMESTAMP,
"INBASKET_ID" BIGINT NOT NULL,
"TZ_OFFSET" BIGINT,
"IS_CONFIDENTIAL" INTEGER NOT NULL DEFAULT 0
)

The data being migrated already has the TIMESTAMP values and these need to be retained in the target table.

In order to retain the TIMESTAMP of existing data,
we had to remove the constraint first:

ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN TIMESTAMP DROP GENERATED;

load the data  -- we used a Java program to load the values from CSV files -- then put the constraint back, and we have a challenge here.

We are able to do step 1 and 2 but cannot find the correct syntax to put the constraint back. We tried the following:

SET INTEGRITY FOR CFFOLIO.FOLIO OFF;
ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN TIMESTAMP
  SET GENERATED ALWAYS AS (FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP);
SET INTEGRITY FOR CFFOLIO.FOLIO IMMEDIATE CHECKED FORCE GENERATED;

ALTER TABLE above gives a syntax error.

What is the correct syntax for altering the table? Is there any other way to do this?

I'm not sure you can alter a column with that clause. The knowledge center suggests that this syntax should do it,

ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN "TIMESTAMP"
SET GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;

but it does not appear to work.

One option would be to use LOAD. E.g. https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/DB2_Preserving_existing_timestamp_data_when_moving_tables_which_contain_a_ROW_CHANGE_TIMESTAMP_column?lang=en


How to calculate or track monthly database growth? Using oracle how can i track database growth monthly? The following  is a query apart of a query i used to stored information using sql server. Is there a way to replicate the same using oracle?

SELECT
  t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t

below query using DBA_HIST_SEG_STAT and base on awr SNAPSHOTS and their priod gives the database growth per snapshot. you can change it for monthly output.

    set serveroutput on
    execute dbms_output.enable(buffer_size => NULL);**

    Declare
          v_BaselineSize  number(20);
          v_CurrentSize   number(20);
          v_TotalGrowth   number(20);
          v_Space     number(20);
          cursor usageHist is
                  select a.snap_id,
                  SNAP_TIME,
                  sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
              from
                  (select SNAP_ID,
                      sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
                  from DBA_HIST_SEG_STAT
                  group by SNAP_ID
                  having sum(SPACE_ALLOCATED_TOTAL) <> 0
                  order by 1 ) a,
                  (select distinct SNAP_ID,
                      to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
                  from DBA_HIST_SNAPSHOT) b
              where a.snap_id=b.snap_id;
      Begin
          select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
          select sum(bytes) into v_CurrentSize from dba_segments;
          v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

          dbms_output.put_line('SNAP_TIME           Database Size(MB)');

          for row in usageHist loop
                  v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
              dbms_output.put_line(row.SNAP_TIME || '           ' || to_char(v_Space) );
          end loop;
      end;
    /


Change LEFT JOIN to JOIN unless you really need LEFT.
Clean up things like ( sr.supplier_id = su.supplier_id AND sr.supplier_id = su.supplier_id )
Change the OR into a UNION (with a lot of rewriting)
Provide SHOW CREATE TABLE (so we can see if you have suitable indexes)
Don't leave out the rest of the conditions -- they may be part of the problem.
Improve the prettyprinting, perhaps

like this:

SELECT  *
    FROM  supplier_responses sr
    LEFT JOIN
        (
        SELECT  *
            FROM  supplier_updates su
            LEFT JOIN
                (
                SELECT  supplier_id supplierId,
                        coalesce(internal_id, supplier_id) latest_id,
                        Max(timestamp) latest_timestamp
                    FROM  supplier_updates
                    GROUP BY  supplier_id, supplier_id
                ) suLatest  ON suLatest.supplierId = su.supplier_id
                         AND  suLatest.latest_timestamp = su.timestamp
                         AND  suLatest.latest_id = coalesce(su.internal_id, supplier_id)
        ) su  ON ( sr.supplier_id = su.supplier_id
              AND  sr.internal_id = su.internal_id
                 )
      OR  (        sr.supplier_id = su.supplier_id
              AND  sr.supplier_id = su.supplier_id
          ) --
    WHERE  some clause...
    ORDER BY  su.timestamp_at ASC;

Perhaps a composite index like INDEX(supplier_id, internal_id) would be beneficial.


Length of varchar column - is there an advantage to using 255? I often come across columns that are varchar(255), so presumably this is a convention of sorts. 255 is 2^8 - 1. So is there something 'magical' about lengths that are 2^n - 1? Or just specifically 255? Is there a performance or storage optimisation advantage to certain specific varchar lengths? Or is this just old wisdom which is no longer applicable to current versions of MariaDB and MySQL?

I rant against 255 occasionally.  Sure, there used to be some reasons for '255', but many are no longer valid, and even counter-productive.
In MySQL, there are reasons to stop at 191, 255, 767, 3071, 64K, and probably other values.  Some depend on Engine, some on CHARACTER SET, etc.
A VARCHAR is stored as a 1- or 2-byte length plus enough bytes for the current text in whatever charset you have specified.  However, the choice of 1 or 2 is not driven only by the individual column; it is driven by the total row size.  That is, this is not a valid excuse for using 255.
Use a length that is

Big enough to conservatively never be exceeded, yet
As small as seems reasonable.

While I am ranting...  CHAR (fixed length) is rarely advised.  And almost always it should be CHARACTER SET ascii -- country_code, postal_code, Y/N, M/F, MD5, UUID, base64, etc.  (MD5 and UUID should be taken a step further, but that is another rant.)
Potential negative impacts of blindly using '255':

If you have a lot of columns, you could hit a max row size limit and CREATE TABLE will fail.
You could overflow a limit on index size.
Complex SELECTs may need a temp table, and may use MEMORY for it.  In this case, VARCHAR(255) becomes CHAR(255) for the temp table.  And, if using utf8, that is 755 bytes for every row!  (8.0 fixes this design flaw?)

Related:  Don't blindly use BIGINT for all numbers.  It takes 8 bytes.  Even INT is overkill, at 4 bytes.  See MEDIUMINT, SMALLINT, and TINYINT.  Be aware that the (2) on INT(2) means nothing; it still takes 4 bytes.


SQL Job Archival Process Clarifications I have an archival job in my SQL Server which is part of AOAG. I can see the same job in all replicas which are part of AOAG and it's scheduled at the same time.

It is running at the same time on all servers for around 30 seconds successfully.

Can anyone let me know the logic behind the same job same schedule in all replicas successfully running.

It is connecting through AOAG listener to servers and the first step will be copying a file to another location and running the archival job, on job success the source location will be deleted. This is what I can see in job properties-->steps.

I need to create a new customized archival job.

What will be the problem if I create the job with 5 minutes difference in each replica?
Will there be any problem if I schedule this job at same time in each replica?

Please advise.

They are meant to do a check on each node to see whether it's the primary or not before continuing to run the rest of the logic but they didn't bother.

It sounds like someone set it up under the belief that it will never interfere with each other concurrently (maybe because it has file locks or something else).

No biggie. If you're redesigning it I would add the primary replica check anyway. The question about 30s to 5m is impossible to say though. It's not going to break the AG but what it does to the software is another question.


Failed SQL Login - Source server same as destination cannot locate SPID SQL Server 2014 Bit of a confusing one here.

We are getting repeated messages in the SQL Server error log as below for a SQL Server instance:

  NT AUTHORITY\SYSTEM
  Reason: Failed to open the explicitly specified database XXX [CLIENT: xx.xx.xxx.xx]

SQL Server Profiler shows nothing nor does sp_who2 or sys.dm_exec_requests.

The client IP Is actually the same SQL Server instance, with the source in the Windows Application Log being MSSQLSERVER - so its trying to connect to itself but failing.

However I cannot find what exactly is occurring. The start of these messages is recorded in the SQL Log. As you can see the database was started up due to a CHECKDB. The CHECKDB completed then the login errors have been there since.

  2015-07-13 22:11:27.790   spid30s Starting up database XXX.
  2015-07-13 22:11:28.480   spid30s CHECKDB for database XXX finished without errors on 2015-07-11 03:36:12.720 (local time). This is an informational message only; no user action is required.
  2015-07-13 22:11:33.780   Logon   Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database XXX. [CLIENT: XX.XX.XXX.XX]
  2015-07-13 22:11:38.750   Logon   Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database XXX. [CLIENT: XX.XX.XXX.XX]

Any info on this would be appreciated!

I've seen this before.  I believe that it was due to autoclose.  But I could not recall from memory.  Credit here goes to Aaron Bertrand.

  Usually this means the login's default database is offline (perhaps due to AutoClose) or no longer exists.  Resolve by fixing the missing database, or changing the login's default database using ALTER LOGIN (for older versions, use sp_defaultdb, which is now deprecated).  This is reported as state 16 prior to SQL

  Server 2008. Error: 18456, Severity: 14, State: 40. Login failed for
  user ''.  Reason: Failed to open the explicitly specified database.

Blog post regarding SQL login errors


Postgres: Database architecture and materialized views I have a database with a huge table, that gathers the ranking history of mobile applications. The table is quite big, around 120 Go.
In order for my DB queries not to be too slow, I implemented several materialized views.
One of these materialized calculates the average rankings of apps over the last 30 days. It is refreshed everyday.

I now want to be able to tell at any point in time what that average was on a particular date. I.e, to have an history of averages.

Would it make sense to add the results of my materialized view everyday to a table ? Or should I partition that big table and do it another way ?

Edit : Main table structure (738,681,765 rows)

+----+--------+---------+------+-------+---------------+------------+-------------+
| id | app_id | ranking | date | price | collection_id | country_id | category_id |
+----+--------+---------+------+-------+---------------+------------+-------------+
|  1 |  1426  |   30    |  t1  |   0   |      12451    |   1658     |   2564      |
|  2 |  1427  |   15    |  t2  |   0   |      23562    |   1485     |   3256      |
|  3 |  1428  |   22    |  t3  |   0   |      14564    |   1320     |   4521      |
|  4 |  1429  |   11    |  t4  |   0   |      12468    |   1578     |   5015      |
|  5 |  1430  |   10    |  t5  |   0   |      18712    |   1100     |   6012      |
+----+--------+---------+------+-------+---------------+------------+-------------+

Would it make sense to add the results of my materialized view
  everyday to a table ?

Yes, it makes sense to materialize.

The analysis of large datasets (see: OLAP, dimensional modeling) includes the concept of aggregations - which can be implemented as materialized views. You should design what aggregates will you keep. In my opinion you need at least two:

by (app_id) - full-time history of single app. all-time ranking, trends, etc etc.
by (app_id, day) - daily information for every single app. This can include average daily rankings and all other information that's relevant on this level. can also include trend analysis and al other daily info you can imagine.

You can always calculate higher-level information from lower-lever aggregate. For example if you had an aggregate on (app_id, day, collection_id) you could use it instead of (app_id, day).

You can materialize your aggregates with MATERIALIZED VIEW feature. But this is not an only way. If old data is static, it could be enough to insert new rows daily, with something similar to

INSERT INTO data_by_app_id_day (app_id, day, avg_ranking)
SELECT app_id, tscol::date, avg(ranking)
FROM main_table
WHERE tscol::date = current_date - 1
GROUP BY 1,2;

  Or should I partition that big table and do it another way ?

There is a lot of good sources on to partition or not to partition.

If you are going to store 2 years and more, daily partitions could be optimal.  But remember that very large number of partitions will make query planning longer. Threshold  depends on CPU speed / queries used.

PS. I assume that you ran out of normal ways to optimize:

indexes on all columns used in WHERE clause
partial indexes if needed
expression indexes if needed
optimal data types used
avoiding unnecessary data detail that explodes your dataset size


can I use many-to-many relation with weak entity I have an entity, for example employee(key employee ID), and every employee can have multiple responsibilities. Every responsibility can be tasked to multiple employees.

I have 3 options:

1) Create weak entity -  responsibility when the key is responsibility name and employee ID from employee entity.

And those entities will a have many-to-many relation  .

2) Make an employee-employee relation which will hold attribute responsibility.

3) Add attribute to employee as a key(ID, responsibility)

Is first option even possible!!! And what is the correct option.

I'd say that this is pretty standard stuff ;

Create table employees (id int primary key,
                        name varchar(100)
                        -- Plus some additional columns
                        )

Create table responsibilities (id int primary key,
                               description varchar(100)
                               --Plus some additional columns;
                               )

Create table employee_responsibilities ( employee_id int foreign key references employees(id),
                                         responsibility_id int foreign key references responsibilities(id)
                                         --Plus some additional columns;
                                       )

You might want to have a surrogate primary key on the relationship table ;

Create table employee_responsibilities (id int primary key,
                                        employee_id int foreign key references employees(id),
                                        responsibility_id int foreign key references responsibilities(id)
                                        --Plus some additional columns;
                                        )


What is the usage of sys.sysrowsets in SQL Server? I just wonder what is the usage of sys.sysrowsets in SQL Server? Microsoft said it “Exists in every database. Contains a row for each partition rowset for an index or a heap.” But what is a partition?

Also what is the meaning of idmajor and idminor for this table? I try to find the object ID of a table(246486340) and then execute the query:

Select * from sys.sysrowsets where idmajor = 246486340

The results are two records:

rowsetid           ownertype       idmajor idminor  numpart status  fgidfs  rcrows

72057603504865280   1              246486340     1     1        6     0     2582
72057603612213248   1              246486340     6     1        2     0     2582

But I think there should only be one record with that major id.

Its an underlying table for sys.partitions, which returns the same information as

SELECT * FROM sys.partitions

  But what is a partition?

Refer to Partitioned Tables and Indexes on MSDN.

idmajor is the column name that's commonly known as object_id

idminor is the index_id.

Lets do some testing now:  I have used AdventureWorks 2012 from CodePlex.

USE AdventureWorks2012_Data;
GO
DECLARE @MyID int;
SET @MyID = (SELECT OBJECT_ID('HumanResources.Employee',
'U'));
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(@MyID);

Output:

name        object_id   type_desc

Employee    1237579447  USER_TABLE

Lets query the sys.partitions view now:

SELECT partion_id, object_id, index_id FROM sys.partitions
WHERE object_id = '1237579447'

Output:

partition_id      Object_id      index_id

72057594045136896   1237579447  1

72057594050510848   1237579447  2

72057594050576384   1237579447  3

72057594050641920   1237579447  5

72057594050707456   1237579447  6

72057594050772992   1237579447  7

If we look at the sys.indexes view:

SELECT object_id, name, index_id from sys.indexes where object_id = '1237579447'

Output:

Object_id           name                                index_id

1237579447  PK_Employee_BusinessEntityID                    1

1237579447  IX_Employee_OrganizationNode                    2

1237579447  IX_Employee_OrganizationLevel_OrganizationNode  3

1237579447  AK_Employee_LoginID                             5

1237579447  AK_Employee_NationalIDNumber                    6

1237579447  AK_Employee_rowguid                             7

In the above output, index_id is just an id for the indexes. 1 for Clustered index and the others (2-7) for other non clustered indexes.

  The object_id for all indexes under a table is same as the object_id
  for that table.

Lets take a look at the sys.sysrowsets table:

USE AdventureWorks2012_data
GO
SELECT rowsetid, idmajor, idminor from sys.sysrowsets
WHERE idmajor = '1237579447'

I used the object_id for the HumanResources.Employee table to filter data.

Output:

rowsetid               idmajor         idminor
-------------------- ----------- -----------
   72057594045136896  1237579447           1

   72057594050510848  1237579447           2

   72057594050576384  1237579447           3

   72057594050641920  1237579447           5

   72057594050707456  1237579447           6

   72057594050772992  1237579447           7

From this its clear that object_id or idmajor is the same for the table and all the indexes under it and idminor is nothing but the index_id for an index.


Auto update stats turned off for a user database on SQL Server While trying to improve the performance of few queries, we found via execution plan that there were lot of index / clustered index seeks.

Therefore:

First thing we did, was to check our Re-indexing and update stats job which runs weekly for this user DB ( Around 400 GB in size and is used 24*5). The job was running fine.

Later when we ran SP_Blitz, we came to know that auto-update-stats is disabled for this user DB. We expected this to be a possible cause and change it from false to TRUE (Auto update stats)

Also, per SP_blitz there are user-created statistics for this DB. When ran the query to check how many, we saw around 7K user stats out there.

So my questions would be

setting the Auto update stats to TRUE would require a reboot or once changed I need to track the performance
Should we consider dropping those user created stats or manually look into them one by one?

How should we proceed on this, please suggest, thanks!

You have two questions:

1. Do you have to restart after setting auto update stats on? No.

2. Should you consider dropping the user created stats? If your statistics updates jobs (typically done with a maintenance plan or with Ola Hallengren's maintenance scripts) are taking longer than your maintenance window allows, then yes. SQL Server has to read the entire index (or table) to update each statistic, so you can end up with really long stats update jobs. If your maintenance window isn't a problem for you, then you can leave 'em as is.


What should my SPN entries look like for each SQL instance? I'm finding contradictory information for how exactly to format SPNs (Service Principle Names) to get the proper Kerberos connections, and how many I need for each SQL instance.
This 2017 MS document contains the following:

Beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication on TCP/IP, named pipes, and shared memory. The supported SPN formats for named and default instances are as follows.

Named instance: MSSQLSvc/FQDN:[port|instancename]
Default instance: MSSQLSvc/FQDN:port|MSSQLSvc/FQDN

The new SPN format does not require a port number. This means that a multiple-port server or a protocol that does not use port numbers can use Kerberos authentication.

I took this last paragraph to mean that I only need a single entry, one of the following:

Named instance: MSSQLSvc/sqlbox1.mydomain.org/instance2
Default instance: MSSQLSvc/sqlbox1.mydomain.org

That seems to contradict this older (2011) MS document, not just about the port number, but also concerning what name to use:

To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, you must create an SPN for both the NetBIOS name and the FQDN.

When I look at the SPNs that already exist in my environment, I see a wide variety of combinations, some servers have up to 4 entries:

MSSQLSvc/sqlbox1
MSSQLSvc/sqlbox1:1433
MSSQLSvc/sqlbox1.mydomain.org
MSSQLSvc/sqlbox1.mydomain.org:1433

Even MS's own Kerberos configuration manager seems to want to generate the last two versions (with appropriate obfuscation):

Similarly for existing named instances, I see a weird mix, some of them almost certainly invalid:

MSSQLSvc/sqlbox1:1522
MSSQLSvc/sqlbox1:instance2
MSSQLSvc/sqlbox1.mydomain.org:1522
MSSQLSvc/sqlbox1.mydomain.org:instance2
MSSQLSvc/sqlbox1.mydomain.org/instance2
MSSQLSvc/sqlbox1.mydomain.org:1522:instance2

So what should my DSNs actually look like, for both default and named instances, if I just use TCP in my environment?
Should I include the port, or not? Or include one with the port and one without?
Use the FQDN only, or do I need the entries with just the Netbios name? Or would that only be if we were using named pipes (which we are not)?
(For context, we run SQL 2005 through 2014, some clustered, others standalone. Connectivity is via TCP only, named pipes is disabled in config manager. We will be fixing/creating these manually instead of allowing the SQL service account to create them upon server start.)

If you're only using TCP/IP to connect to your instances, you only need the ports specified.  The Instance Names are used when connecting to the SQL Instances via the Named Pipes protocols.  Sadly the MS article doesn't come right out say which format is required for which protocol, but it is derived from (many tests in my environment) and the following MS article sentance:

  For named pipes and shared memory connections, an SPN in the format MSSQLSvc/FQDN:instancename is used for a named instance and MSSQLSvc/FQDN is used for the default instance.

Regarding FQDNs vs NETBIOS names, I will recommend FQDNs as they aren't as prone to problems if you run up against random DNS server issues.

Lifted from my blog post on the matter, formats should look as follows:

The source reference from MS can be found here.

Now to make your Network Admin's Day (e.g OU configuration that allows for Self Registering SPNs)

Your Network Admin can create an OU on the domain which contains all your SQL Server Service accounts that can be configured in such a manner that the Service Account can create an SPN for itself and itself alone.  The method is mainly following Ryan Reis's blog, but has some slight tweaks so that over-grants are not performed.

This process describes the creation of an OU on the domain that allows accounts within it to self-register their own SPNs:

As an account with elevated rights on the domain, open ADSI Edit (adsiedit from the command prompt)
Right-Click on ADSI Edit -> Connect to...
Connect to Default naming context
Navigate to/Create the OU container holding the service accounts you wish to grant SPN rights to
Right-Click on the OU -> Properties
Click on the Security Tab
Click the Advanced button
Highlight SELF and click Edit... or if the SELF special user does not show up in the list of Group or User names, click Add... and enter SELF for the object name
Click the Properties tab
Select Descendant User objects from the drop down list next to Apply to:
Note: This is the slight adjustment to the steps outlined in Ryan's blog post for the reasons better outlined by this ServerFault/StackExchange post.
Check the Allow box next to the following:

Read servicePrincipalName
Write servicePrincipalName

Click Ok (on permissions entry window)
Click Ok (on Advanced Security Setings window)
Click Ok (on OU properties window)
Add service accounts running SQL Server services to the OU
(Optionally) Restart SQL Server Services running under said account(s)
Enjoy Treats

After following the above steps, the OU container in question is now configured such that any account added to it will be able to register and delete SPNs for itself and itself alone.  This is exactly the right amount of permissions as these accounts will be unable to trample over SPNs registered by other accounts.

The purpose of restarting SQL Server in step 16 is to ensure the SPNs are registered as expected.  SQL will try to remove any registered SPNs on shutdown and add them on Startup, so the restart is really only needed if no SPNs currently exist for said SQL Server service.

The final note on this approach is that if you are running SQL Server in a traditional Failover Clustered Instance (FCI) configuration, it is NOT Recommended to add this instance's service account to this OU, per KB 2443457.

I really need to post Part 2 of my Kerberos series...


If Exists before Update or Delete? Before I write an update statement, should I check if it exists?

Someone told me to do this

So it avoids uselessly writing to the transaction log for value that already exists,
And only takes a Shared lock allowing other reads versus an Exclusive lock.
They said imagine a transaction running like this multiple times for different values, you would be taking exclusive locks all the time rather than shared locks.
Coworker mentioned, looking at lock compatibility chart, there cannot be two update locks when searching, however there can be two shared locks. So when "searching for rows to update" when they're no values to update, a fake update lock can block a true update. Can someone invalidate this claim?

if not exists
(
    select FavoriteColor
    from dbo.Person
    where Name = 'Bob'
    and FavoriteColor = 'Green'
)
update dbo.Person
set FavoriteColor = 'Green'
where Name = 'Bob'

Same question, except for delete now, should I check if it exists?

if exists
(
    select FavoriteColor
    from dbo.Person
    where Name = 'Bob'
    and FavoriteColor = 'Green'
)
delete dbo.Person
where Name = 'Bob' and FavoriteColor = 'Green'

We use SQL Server 2016.

Generally, this pattern is more efficient and far less likely to lead to deadlocks or other concurrency issues:

UPDATE dbo.Person
  SET FavoriteColor = 'Green'
  WHERE Name = 'Bob'
  AND COALESCE(FavoriteColor, '') <> 'Green';

DELETE dbo.Person
  WHERE Name = 'Bob'
  AND FavoriteColor = 'Green';

...simply because you only have to check for the row once. You might also write the UPDATE as:

UPDATE dbo.Person
  SET FavoriteColor = 'Green'
  WHERE Name = 'Bob'
  AND (FavoriteColor IS NULL OR FavoriteColor <> 'Green');

...which may be more index-friendly in some cases.

Exclusive locks for write operations are not taken until just before a row to modify is actually updated. While SQL Server is searching for rows to update, it uses update locks, which do not conflict with concurrent reads. The update lock is released immediately if SQL Server determines that the row being checked does not qualify for the update.

The only reason I can think of using the if exists method is if there are UPDATE/DELETE triggers in the table that you want to avoid being fired, especially if you have INSTEAD OF triggers which can take some action before any update or delete is actually attempted.

It is normally best to write DML such that only rows that require a change are affected.


sql statement in access that group items and count their sum I would like to write an sql statement in microsoft access that group items and count their sum

My table is like below :

id : Auto-Increment
Items : String
Quantity : Number

Eg :

id | Items | Quantity
1 | A | 10
2 | B | 5
3 | A | 10
4 | C | 20
5 | C | 20
6 | A | 10
7 | C | 20
8 | B | 5
9 | B | 5

Expected result is

id | Items | Quantity
1 | A | 30
2 | B | 15
3 | C | 60

I'm guessing the id column is actually not important in your expected result. In which case, you just need to do a grouping and a summation:

select Items, sum(Quantity) as TotalQty
from MyTable
group by Items


How to use Weekday() function inside join in MySQL? I have 2 tables from which one is a parent & other one is child.For 1 order we have multiple records in child table.
For e.g: Order & order_frequency are 2 tables.
Order table data:
+-----+---------+---------------------+
| id  | product | orderDate           |
+-----+---------+---------------------+
| 100 | abc     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+
| 200 | xyz     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+

order_frequency table data:
+----+-------+-----------+
| id | ordID | frequency |
+----+-------+-----------+
| 1  | 100   | 111       |
+----+-------+-----------+
| 2  | 100   | 234       |
+----+-------+-----------+
| 3  | 200   | 111       |
+----+-------+-----------+
| 4  | 200   | 234       |
+----+-------+-----------+

I want to fetch matching frequency from order_frequency table based on order date(Which are falling on monday[111] & Tue,wed,thru[234]).
I have tried below query:
SELECT * FROM order LEFT JOIN order_frequency ON (order.id=order_frequency.ordID AND order_frequency.frequency like "%1%");

when i tried above query by providing hard coded value for frequency in column it provide me expected result.
But i want to fetch it dynamically by providing orderDate.
something like below:
SELECT (weekday(DATE(orderDate))+1) AS weekday,* from order LEFT JOIN order_frequency ON (order_frequency.Day like CONCAT('%',@weekday,'%'))

Thanks in advance.

SELECT ...
FROM order o
JOIN order_frequency of ON CASE o.frequency
                           WHEN 111 THEN WEEKDAY(of.orderDate)=0
                           WHEN 234 THEN WEEKDAY(of.orderDate) BETWEN 1 AND 3
                           END
...


Highest value of a row in MySQL I searched and found many results using "Grep highest value of a row in MySQL" but I don't understand the results.

I have a row which primary key value is 4 and there are another 8 values.
Those are 45  78 89 54 98  14  19  70. I want to select the highest value, ie 98 from this row. How do I do this ?

My database table name is class6A

Use the GREATEST() function on all the columns

SELECT
GREATEST
(bangla_1st,bangla_2nd,english_1st,english_2nd,math,social_science,religious,science)
HighestValue
FROM class6A
WHERE roll = 4;

If any of the columns are NULL, then make the NULL the value 0

SELECT
GREATEST
(
    IFNULL(bangla_1st,0),
    IFNULL(bangla_2nd,0),
    IFNULL(english_1st,0),
    IFNULL(english_2nd,0),
    IFNULL(math,0),
    IFNULL(social_science,0),
    IFNULL(religious,0),
    IFNULL(science,0)
) HighestValue
FROM class6A
WHERE roll = 4;

GIVE IT A TRY !!!

In the future, you may add another subject to or remove a subject from the student record.

Here is another method using Dynamic SQL that figures out the subjects present in the table

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('IFNULL(',column_name,',0)')) INTO @grades
FROM information_schema.columns
WHERE table_schema=DATABASE() AND table_name='class6A'
AND column_name NOT IN ('roll','student_name');
SET @sql = CONCAT('SELECT GREATEST(',@grades,') HighestValue FROM class6A WHERE roll=4');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


How to handle deadlock issue sql server 2008? I have a simple application.This application update data of a table (B) from another table(A) which is changed every 60 sec. User may read data at any time so creating deadlock. How is this sort of problem solved in real world. Considering maximum 100 users how could I avoid deadlock. How would web application handling thousand of request avoid deadlock?

Edit:

Select Query : Select * from tableB
Update Query : update table tableB set tableB.someCOl = tableA.someCol where tableA.col1 = tableB.col3

The simplest way to reduce deadlocks - proper indexing. Make sure, that client's read requests not involving table/clustered index scans - if any - this is the open gates to deadlocks

And make sure, that your index has its allow_page_lock and allow_row_lock options ON