Replication SQL Server 2016 Good afternoon,

I am trying to perform a transactional replication in sql server 2016, and I have the following problem.

The scenario is as follows:

A publisher server and a subscriber server, the transactional replication process runs successfully,
the problem comes when, a failover occurs, the application goes from writing from the publisher server to
write to the subscriber server.

My question is, how should the subscriber server be configured so that when a failover of the publishing node occurs, the data written in the subscriber is replicated later to the publisher.

Should I return to the publisher subscriber, and subscriber to the publisher for this process?

Thank you for reading.
 I will be attentive to your opinions.

Transactional Replication is not intended as a High Availability/Disaster Recovery solution.  There is no client connectivity support for failover, and it's operationally complex.

You should usually use AlwaysOn Availability Groups instead.

  how should the subscriber server be configured so that when a failover of the publishing node occurs, the data written in the subscriber is replicated later to the publisher.

Either remove replication and re-create it with the subscriber as the new publisher, or use Bi-Directional Transactional Replication, or Peer-to-Peer Transactional Replication.


Read .sql file into a database I created a sql database and filled it with some good data. Now I'm trying to move all the stored procedure over to another database instance.
I exported the entire source database, including beloved stored procedures to a .sql file.
Now I'm looking for a way to read that data into the other computer's sql server installation.

So far, I've tried looking through the Visual Studio UI for hints, and using sqlcmd with an input file. After using sqlcmd with the .sql file, some rows were affected, but they didn't show up in the server explorer.

Do you want to move data as a one off exercise or do you want to add the data to source control?

SSMS Tools Pack includes a script generator for resultsets, tables or databases.
SSMS has table level data script generation built-in.
If the datasets are large, consider using BCP.
SSIS Import/Export wizard


How to query a table efficiently without affecting live transactions I have a high transaction table with millions of records with the structure below:

transaction
--------------
id             int
txn_status     varchar
amount         bigint
name           varchar
txn_time       datetime --Date and time of the transaction

The txn_status field value can be completed or Pending. I need to run a query that will retrieve the number of records with the following characteristics:

Transactions with Pending txn_status since the last 15 minutes i.e (current_time - txn_time) <= 15 minutes
Transactions with Pending txn_status between the last 16 and 25 minutes i.e (current_time - txn_time) >= 16 minutes and (current_time - txn_time) <= 25 minutes
Transactions with Pending txn_status between the last 26 and 30 minutes i.e (current_time - txn_time) >= 26 minutes and (current_time - txn_time) <= 30 minutes
Transactions with Pending txn_status more than 30 minutes i.e (current_time - txn_time) > 30 minutes

There are two solutions currently in my head currently:

Query the production database every minute using a case statement (the case statement will filter and group the records based on the time difference above) in the where clause of the query.
To avoid putting much load on the production database, have a separate database with a similar table and have an update and insert triggers update the table and execute the query in No. 1 above afterwards on the database.

Please if there is any optimal or better solution in achieving this without putting much load on the database, please share.

Typically, you don't want to be querying tables with high transaction volume at all; that's what a data warehouse is for.

But if you must, you can use SNAPSHOT ISOLATION. Which will query the table as of the last committed transaction before it started. This might give you dirty reads, as it may be out of date by the time the query finished running, but as you're looking at stuff 15-30 minutes in the past it should be ok.

In MySQL (assuming you're using innoDB), you can use START TRANSACTION WITH CONSISTENT SNAPSHOT; as there is no global setting for SNAPSHOT ISLOATION.

You can also look here for more info: https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html


Alter default privileges for a certain column I want a RoleGroup to have certain privileges on a specific column in a table ….the roleGroup not DB owner :

grant  SELECT, INSERT ,UPDATE  (colOne) on table schemaOne.tableOne
to roleGroup;

When I want alter default privileges of this column (to include future users), I get ERROR saying can’t alter default privileges of one column only :

alter default privileges for role DB_OWNER in schema schemaOne grant
select,insert,update(colOne) on table schemaOne.tableOne to
roleGroup;

which forced me to do this :

Alter Default priviliges for role DB_OWNER in schema schemOne grant
select,insert,update to roleGroup ;

what’s the point of the first grant then ?!! Or am I making some mistake ?

"alter default privileges" applies to newly (in the future) created tables, not newly created users/roles.  It generally doesn't make much sense to grant a column specific privilege on a table which does not yet exist, as it might not even have that column once the table is created.

If new users are granted the roleGroup role, then they will automatically have the desired permission on the existing column of the existing table.

I don't think there is a way to automatically grant a role to newly created users, you will have to remember to do it yourself, or script it.


Fixing parameter-sniffing of an embedded query via Recompile One of my multi-step queries makes use of optional filters.  Rather than using dynamic sql or if statements, I am attempting to use option(recompile) to make the filtered step choose appropriate indexes regardless of which optional filters are provided.  This did not work once I tried to store the results of the sub-expression.

I was able to reproduce this in SSMS as follows:

declare @UID bigint = 100

--Query A: Uses index on UID
select count(1) from Entries
where (@UID is null or UID = @UID)
option(recompile);

--Query B: Does an index scan (much slower)
select @cnt = count(1) from Entries
where (@UID is null or UID = @UID)
option(recompile);

--Query C: Uses index on UID
select @cnt = count(1) from Entries
where (UID = @UID);

My goal is to make query B use an index on UID if @UID is not null.  I could accomplish this by conditionally replacing it with Query C, but my preference is to avoid that, as it strikes me as overly verbose.  I can't use Query A, since I want to store the intermediate result for use in the next query within a stored procedure.

I did try selecting into a temp table and I tried using nullif, but these approaches still did a scan rather than a seek.

If @UID is NULL then the query cannot be executed with a seek. That means in order to get a seek you need the value of the variable to not be NULL and you need the parameter embedding optimization to be applied to the query. In other words, the optimizer needs to see the value of the variable before creating the plan. Usually this can be accomplished with a RECOMPILE hint but not always. The final example of the linked blog shows how the parameter embedding optimization cannot be applied when assigning a value to a variable.

We can also see hints of that behavior by running your example queries. For query A I get the following as a seek predicate:

Seek Keys[1]: Prefix: [SE_DB].[dbo].[Entries].UID = Scalar Operator((100))

However, for query B I get the following predicate:

[@UID] IS NULL OR [SE_DB].[dbo].[Entries].[UID]=[@UID]

The @UID reference was not replaced with 100 for query B so we must get a scan.

We can also see the difference in the query trees created for optimization with undocumented trace flag 8606:

Query A is on the left and query B is on the right. For query A, the query optimizer builds the query plan using the literal value of the variable. For query B, the query optimizer builds the plan only knowing that a variable was used.

There are plenty of workarounds available that don't require dynamic SQL or branching code. You can add up the results of two subqueries:

SELECT @cnt =
(
    select count(1) cnt
    from Entries
    where @UID is null
) +
(
    select count(1) cnt
    from Entries
    where @UID is not null and UID = @UID
);

You can use UNION ALL:

SELECT @cnt = SUM(cnt)
FROM
(
    select count(1) cnt
    from Entries
    where @UID is null

    UNION ALL

    select count(1) cnt
    from Entries
    where @UID is not null and UID = @UID
) t
option(recompile);

You can also insert into a temp table and grab the value from the temp table:

select count(1) cnt INTO #t
from Entries
where (@UID is null or UID = @UID)
option(recompile);

SELECT @cnt = MAX(cnt) FROM #t;

DROP TABLE #t;


SQL Server 2008 Cluster Across Two VMWARE Hosts Vmware ESX 5 from a Vcenter

Two Windows 2008 R2 VMs

I am attempting to setup Failover Clustering using two VMs from within a Vcenter. Each virtual machine resides on a seperate physical host following this schema:

The instructions for adding a disk to the first machine go as follows:

In the vSphere Client inventory, select the newly created virtual machine and select Edit Settings. The Virtual Machine Properties dialog box appears.
Click Add, select Hard Disk, and click Next.
Select Raw Device Mappings and click Next.
Select an unformatted LUN and click Next.
Select a datastore and click Next.

This datastore must be on a SAN because you need a single shared RDM file for each shared LUN on the SAN.

My question is as follows. When two Windows 2008R2 servers are hosted on two different physical VMWare servers within a VCenter, is it absolutely necessary to have an unformatted LUN for the shared storage of a Windows Failover Cluster?

If not, what would be an alternative?

iSCSI is not an option.
Both servers must be installed on two seperate physical hosts.

Here is a link to the documentation.

http://www.vmware.com/pdf/vsphere4/r41/vsp_41_mscs.pdf

Goto page 21.

As said in the comments, I can (almost) conclusively confirm that you will require shared raw LUNs with RDM, when you want to cluster 2 x virtual (vmware) SQL Server nodes that are on different hosts, instead of using VMFS.

The VMWare documentation also explicitly mentions the use of unformatted LUNs

*I guess (and I have never done touched on it before) the only possible exception that spring to mind may be in you use SQL Server 2012 AlwaysOn on SSDs. Oh, and possibly iSCSI, which is as you said, not an option. *


Simple confusion with DATALENGTH() DATALENGTH() function returns the length of the data in BYTES.

The table is like this:

create table tb3
(
id int,
id2 varchar(100),
nome varchar(200)
)

| id      | id2   | nome  |   |
|---------|-------|-------|---|
| 1       | 1     | r     |   |
| 1       | 11    | r1    |   |
| 1       | 111   | r11   |   |
| 1       | 1111  | r111  |   |
| 1       | 11111 | r1111 |   |
| 11      | 11111 | r1111 |   |
| 1111111 | 11111 | r1111 |   |

but when i run it like this:

select DATALENGTH(id), DATALENGTH(id2), DATALENGTH(nome) from tb3

| (No column name) | (No column name) | (No column name) |   |
|------------------|------------------|------------------|---|
| 4                | 1                | 1                |   |
| 4                | 2                | 2                |   |
| 4                | 3                | 3                |   |
| 4                | 4                | 4                |   |
| 4                | 5                | 5                |   |
| 4                | 5                | 5                |   |
| 4                | 5                | 5                |

Why it says each varchar character is using only 1 byte?
If i'm not mistaken, a varchar uses 2 bytes + number of characters.

The 'DATALENGTH()' function returns the length of just the data, those extra two bytes are considered "meta-data" - information about the data and/or its storage.

You get the same value if you pass the function a string literal (DATALENGTH('12345') returns 5) - this wouldn't be the case if the function accounted for storage overheads as well as the string's content.

To account for storage overhead the DATALENGTH function would need to know or be able to derive where a value has come from, and the structure of that location, if the data is compressed, etc., and accounting for all that would make the function far less efficient.


How to use insert and select simultaneously? I have table test and it has columns id,name and age.I want the id so I am querying like
it

select id from test where age=12

After I get the id I am updating the name using update query

Now lets say if age=13 not present then I will insert a row(insert into test(age) values(13)) and after it is inserted I will get the id(as id is autoincremented).So is it possible to do insert and select simultaneously?

So is it possible to do insert and select simultaneously?

Yes, you can use the output clause of the insert statement.

insert into dbo.Test(age)
output inserted.ID
values(13);


MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate? Just curious on resource efficiency.  If I have a list of inserts that need to be accomplished repeatedly which may contain duplicates (there is no way around this, the API I have to work with sucks), is it better for me to query for the primary, and only insert if it doesn't exist (ie. 2 queries), or just insert anyway with update on duplicate syntax?

Thanks!

At 250/hour, all possible techniques are plenty fast.  Anyway, I will throw my 2 cents in...

Multi-row INSERT of 100 rows will run 10 times as fast as 100 individual INSERTs.

Multi-row INSERT may lead to gaps in AUTO_INCREMENTs.  In general, the command will preallocate all the ids it might need, then 'burn' the ones id did not use.  (REPLACE burns lots, since it is DELETE + INSERT.)

For efficient "normalization" do something like:

INSERT IGNORE INTO HostNorm (host_name)
    SELECT DISTINCT s.host_name
        FROM Staging AS s
        LEFT JOIN HostNorm AS n  ON n.host_name = s.host_name
        WHERE n.host_id IS NULL;

A list of possibly new values is in Staging.
HostNorm is where 'new' rows need to be put.
Only 'new' rows are fed to INSERT (see LEFT JOIN).
IGNORE is to accommodate having multiple threads (connections) doing similar inserts that could collide.  (A rare collision would lead to a burned id.)
There is no cap on how many rows this can handle.
This works best with, say, 100 rows.
This is designed for very high speed input; it should work fine for your relatively slow ingestion rate.

More discussion in my blog.


MariaDB Roles and SHOW DATABASES I'm not sure if this is a feature, or a bug, so I figured I'd ask here. I'm using MariaDB 10.1.14 on Amazon RDS, and I found that a user with an assigned role that has SELECT privileges on database(s) can't actually see those databases when they run SHOW DATABASES on the command line, or when they use a GUI client.

I tested this on a MariaDB instance on RDS, and with Docker locally.

The Docker command:

docker run \
--name mariadb-10114-test \
--publish 3306:3306 \
--env MYSQL_ROOT_PASSWORD=root \
--detach \
mariadb:10.1.14

Creating the database, table, role, user, and assigning the default role to the user as root:

mysql> DROP DATABASE IF EXISTS `testdb`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP ROLE IF EXISTS `developer`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP USER IF EXISTS 'john'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> CREATE TABLE `testdb`.`t0`(`c0` INT);
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE OR REPLACE ROLE `developer`;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `testdb`.* TO `developer`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'john'@'%' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT 'developer' TO 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SET DEFAULT ROLE 'developer' FOR 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

And then logging in as the newly created john user in another window:

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| developer      |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> USE `testdb`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| t0               |
+------------------+
1 row in set (0.00 sec)

I can use the database, but that's only because I have explicit knowledge that I can use it. Is this normal for MariaDB roles? In MySQL / MariaDB with non-role based permissions, the user can see which databases they have access to. The fix for this is obviously to do GRANT SHOW DATABASES ON *.* TO 'developer' but this wouldn't be preferred.

It looks like this bug has already been reported, and is listed as "Stalled" at the moment.

https://jira.mariadb.org/browse/MDEV-10463


mysql ERROR 1248: Every derived table must have its own alias -- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_MissingBooks`(p_UsrID BIGINT ,
      p_Product VARCHAR(20))
BEGIN

        DECLARE p_LinkedBoard INT;
        DECLARE p_LinkedSubjects INT;
        DECLARE p_LinkedClasses INT;
        DECLARE p_LinkedBoards INT;
        DECLARE p_SchoolBooks INT;

        SELECT COUNT(*) INTO p_LinkedBoard
        FROM    userboards
        WHERE   userboards.usrid = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedClasses
        FROM    userclasses
        WHERE   userclasses.UsrID = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedSubjects
        FROM    usersubjects
        WHERE   usersubjects.usrid = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedBoards
        FROM    schoolboards;
        SELECT COUNT(*) INTO p_SchoolBooks
        FROM    SchoolBooks;
        Select * From temp_MatchList;

        CREATE TEMPORARY TABLE temp_MatchList(ID BIGINT ,
            Year INT ,
            Name NVARCHAR(100) ,
            SyllabusDisplayName NVARCHAR(100) ,
            BookType NVARCHAR(20) ,
            ClassName NVARCHAR(20) ,
            ClassID INT ,
            ClassNumber INT ,
            BookName NVARCHAR(100) ,
            Board NVARCHAR(100) ,
            Publisher NVARCHAR(100) ,
            SubjectName NVARCHAR(100) ,
            SubjectID INT ,
            BoardID INT ,
            STATUS CHAR(1) ,
            BoardStatus CHAR(1)
        );

    INSERT INTO temp_MatchList
     SELECT * FROM
     (
        SELECT vBooksDetails.*
        FROM
        (
           SELECT DISTINCT * FROM
           (
              SELECT
                classmaster.ClassID AS ClassID,
                CDisplayName,
                subjectmaster.id ,
                SubjectID,
                subjectmaster.DispName ,
                BookName
              FROM classmaster
              JOIN subjectclasslinkage ON
                 classmaster.ClassID = subjectclasslinkage.ClassNumber
             JOIN subjectmaster ON
                 subjectclasslinkage.SubjectId = subjectmaster.id
             LEFT JOIN
              (
                 SELECT * FROM vBooksDetails
                 WHERE
                 (
                   (
                     (p_LinkedBoard = 0 ) OR (BoardID IN (SELECT boardname
    FROM userboards WHERE userboards.usrid = p_UsrID )))
    AND ((p_LinkedBoards = 0 ) OR (BoardID IN (SELECT BoardID FROM schoolboards )))
    AND EXISTS (SELECT * FROM vBoardInfo WHERE ID = BoardID )AND vBooksDetails.Status = 'A'))
    lessonsyllabus ON subjectmaster.id = lessonsyllabus.SubjectID AND
    lessonsyllabus.ClassID = classmaster.ClassID ) TAB WHERE TAB.BookName IS NULL ) TAB_MISSING
    JOIN vBooksDetails ON TAB_MISSING.ClassID = vBooksDetails.ClassID
    AND TAB_MISSING.SubjectID = vBooksDetails.SubjectID AND vBooksDetails.BoardID = 3
    WHERE   vBooksDetails.Status = 'A' AND ((p_LinkedClasses = 0 )
    OR ( vBooksDetails.ClassID IN(SELECT classid FROM userclasses WHERE UsrID = p_UsrID )))
    AND (( p_LinkedSubjects = 0 ) OR (vBooksDetails.SubjectID IN (SELECT subjectid FROM usersubjects
    WHERE usrid = p_UsrID )))
        -- AND ((@LinkedBoard = 0) OR (BoardID IN (SELECT boardname FROM userboards WHERE usrid=@UsrID)) )
    AND ((p_SchoolBooks = 0 ) OR ( vBooksDetails.Id NOT IN ( SELECT BookID FROM SchoolBooks))));

END

As far as I know the part:

{...}
SELECT * FROM (SELECT vBooksDetails.* FROM
{...}
);

could have caused the error - the topmost select wasn't aliased.

Please try this version of your last big insert:

INSERT INTO
    temp_MatchList
SELECT
    vBooksDetails.*
FROM
(
    SELECT DISTINCT *
    FROM
    (
        SELECT
            classmaster.ClassID AS ClassID,
            CDisplayName,
            subjectmaster.id,
            SubjectID,
            subjectmaster.DispName,
            BookName
        FROM
            classmaster
            JOIN subjectclasslinkage
                ON classmaster.ClassID = subjectclasslinkage.ClassNumber
            JOIN subjectmaster
                ON subjectclasslinkage.SubjectId = subjectmaster.id
            LEFT JOIN
            (
                SELECT *
                FROM vBooksDetails
                WHERE
                (
                    ((p_LinkedBoard = 0 )
                      OR (BoardID IN (SELECT boardname FROM userboards WHERE userboards.usrid = p_UsrID )))
                    AND ((p_LinkedBoards = 0 ) OR (BoardID IN (SELECT BoardID FROM schoolboards )))
                    AND EXISTS (SELECT * FROM vBoardInfo WHERE ID = BoardID )
                    AND vBooksDetails.Status = 'A')
            ) lessonsyllabus
            ON subjectmaster.id = lessonsyllabus.SubjectID
               AND lessonsyllabus.ClassID = classmaster.ClassID
    ) TAB
    WHERE
        TAB.BookName IS NULL
) TAB_MISSING
    JOIN vBooksDetails
        ON TAB_MISSING.ClassID = vBooksDetails.ClassID
           AND TAB_MISSING.SubjectID = vBooksDetails.SubjectID
           AND vBooksDetails.BoardID = 3
WHERE
    vBooksDetails.Status = 'A'
    AND ((p_LinkedClasses = 0 )
         OR ( vBooksDetails.ClassID IN (SELECT classid FROM userclasses WHERE UsrID = p_UsrID )))
    AND (( p_LinkedSubjects = 0 )
         OR (vBooksDetails.SubjectID IN (SELECT subjectid FROM usersubjects WHERE usrid = p_UsrID )))
-- AND ((@LinkedBoard = 0) OR (BoardID IN (SELECT boardname FROM userboards WHERE usrid=@UsrID)) )
    AND ((p_SchoolBooks = 0 )
         OR ( vBooksDetails.Id NOT IN ( SELECT BookID FROM SchoolBooks)));


How to INSERT a row INTO a table that has an 'order' column without cursor I have a SQL Server 2016 table with an order/position column.  This column is an INT that indicates the preferred order/position of the rows in table for presentation purposes.

How do I INSERT a row in position 1 without using a cursor?  (i.e. new row has position 1, existing rows - assumed to be in correct order before insert - must be "moved down")

The order/position column is NOT NULL and enforced to be UNIQUE. I would also prefer not to use "blunt force" technique like set all rows' order/position value to some "never used" values and then just perform reorder on all rows.

I suppose you want something like this:

BEGIN TRANSACTION ;

    DECLARE @pos INT = 1 ;

    UPDATE tbl
    SET position = position + 1
    WHERE position >= @pos
      AND EXISTS
          (SELECT * FROM tbl WHERE position = @pos) ;

    INSERT INTO tbl
      (<column names>, position)
    VALUES
      (<values ...>, @pos) ;

COMMIT TRANSACTION ;

Note that this design will behave highly inefficiently, if you have often inserts. It will essentially update almost the entire table for every new insert!


SQL: Max Server Memory Allocation I have three SQL database instances on a cloud server: A, B and C and the Server Installed Physical memory (RAM) is 32GB. The size of A (data+log file) is 44GB and B (data+log file) is 41GB. We ignore C since it has no databases in it and max memory set to 500MB.

Both instances A & B has it's memory set to the default size: 2147483647 MB. Although we have had no issues this is definitely not practical.

After some research, based on database activity, i decided that instance A memory allocation should be set to 20GB and B set to 10GB. In total 30/32 of the max physical memory in the system. Below shows the actual memory consumption. I am afraid if i set the max memory it will slow down response time. Is this configuration optimal in setting the SQL Max Server Memory for such databases and how can i simulate before applying?

A general recommendation by Jonathan (from SQLSkills)

  reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

You can use powershell dbatools Test-DbaMaxMemory to calculate for you. The dbatools has set-DbaMaxMemory to set the max memory for you across all your servers.

I would go with Test-DbaMaxMemory to check the recommendations first and once you are comfortable, use set to set it across all your servers.


MariaDB 10.2: Why does Count(*) work, but not Count(t.*) I have a table containing transaction details which I am importing from several vendor-provided daily reports.. I want to be sure that I am not skipping any of the files (one file per day) as I do this process..

So I wanted to query the database to find the number of distinct dates it found in the result set.

At first I tried to execute:

SELECT COUNT(t.*) FROM (SELECT DISTINCT `Date of Survey`  from cc.voc_detail_old) t;

But that generates the error "* SQL Error (1064): You have an error in your SQL syntax;..."

Changing it to SELECT Count(*)... resolves the error and the query functions as expected. Why is it not permissible to use the alias of the derived table in the Count() function, but the derived table's alias is required per syntax?

In MariaDB, the COUNT() function expects an expression (that is to say, a single value). For each row where the expression is not NULL, that row will be counted. With DISTINCT, as you note, it will only count each distinct non-NULL value once.

COUNT(*) is a special case, returning the number of rows in your result set.

COUNT(t.*) would only work if there's only one column in table t.

Per the OP's testing and my own, COUNT(t.*) does not work even if the table only has one column. See this dbfiddle.uk link to confirm this for yourself.

(Note: You can also see that some other versions of SQL can handle this; if you switch the DB engine to a version of Postgres, the last statement runs successfully, instead of generating an error).


Full text index does not honour deleted words - They are still indexed I have a full text index defined as 'Automatic' radio button checked on in its Catalog. When I modify a word in the full text indexed column, the new word appears in the sys.dm_fts_index_keywords but the old,updated value does not disappear and still shows in the list. It is also true when a word is deleted from the column as well. When I rebuild the catalog though the old (or deleted) word drops off the list. It looks like full text indexes does not honour deleted words. Why would that be?

In real world environments the situations where a keyword is encountered and then removed and will never be encountered again is quite low.

The process for updating a record in the full text index process probably removes all previous references to the record then updates all new references. As such when the references to a keyword are removed it does not know that keyword will no longer exist because it has not yet indexed the new document.

It takes a trivial but non-zero amount of time to insert new keywords (locks and similar) and it would affect index update performance to remove and reinsert these keywords all the time (especially since in most cases there are seldom too many orphaned words). But most of all I suspect the biggest reason is the impact such a change would have on index fragmentation.

There may be a way to remove the keyword from the index when it no longer is referenced but in the real world you're not going to see any noticeable performance decrease by keeping these orphaned keywords around. The only situation where you would see a significant % of keywords appearing and going away on record updates is where you're populating content with lots of random content - in such a situation your index performance is going to be very bad anyway because your keywords will cover so very small % of all documents.


mysql - adding alias columns as a new column I have the following mysql query...
select x.timest,
    max(case when x.devloc='outside' then x.value end) as outside,
    max(case when x.devloc='hvac_main_return' then x.value end) as hvac_main_return,
    max(case when x.devloc='hvac_main_supply' then x.value end) as hvac_main_supply
from sample x where date(timest) = curdate()
group by timest
order by timest desc;

It gives me output like this:
+---------------------+---------+------------------+------------------+
| timest              | outside | hvac_main_return | hvac_main_supply |
+---------------------+---------+------------------+------------------+
| 2021-01-28 23:59:54 | 24.8000 |          67.4375 |          82.9625 |
| 2021-01-28 23:58:45 | 24.9125 |          67.1000 |          80.8250 |
| 2021-01-28 23:57:42 | 24.9125 |          66.0875 |          78.2375 |
| 2021-01-28 23:56:33 | 24.9125 |          64.9625 |          74.8625 |
| 2021-01-28 23:55:32 | 25.0250 |          62.3750 |          73.0625 |
| 2021-01-28 23:54:17 | 25.0250 |          62.8250 |          74.7500 |
+---------------------+---------+------------------+------------------+

Does anyone know how I can add the difference between hvac_main_return and hvac_main_supply as another column?
EDIT:  More info as requested:
mysql> show create table sample\G
*************************** 1. row ***************************
       Table: sample
Create Table: CREATE TABLE `sample` (
  `property` varchar(50) DEFAULT NULL,
  `devloc` varchar(50) DEFAULT NULL,
  `sensortype` varchar(50) DEFAULT NULL,
  `timest` datetime DEFAULT NULL,
  `value` decimal(8,4) NOT NULL,
  KEY `property` (`property`),
  KEY `devloc` (`devloc`),
  KEY `sensortype` (`sensortype`),
  KEY `timest` (`timest`),
  KEY `value` (`value`),
  KEY `timest_2` (`timest`,`devloc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Try this:
select x.timest,
    max(case when x.devloc='outside' then x.value end) as outside,
    max(case when x.devloc='hvac_main_return' then x.value end) as hvac_main_return,
    max(case when x.devloc='hvac_main_supply' then x.value end) as hvac_main_supply,
    ((max(case when x.devloc='hvac_main_return' then x.value end)) -
      (max(case when x.devloc='hvac_main_supply' then x.value end))) AS diff
from sample x where date(timest) = curdate()
group by timest
order by timest desc;

If you want a more detailled answer, please provide some sample data as in the fiddle I've done here. Also, please provide your desired result here - always provide everything you include in any fiddle here also (linkrot).


How create user with admin option? I have Teradata v16.10.0.
I want to create user with admin options: with possibility get to grant select any tables/views.

How to create such user right?

There's no global GRANT in Teradata, you can only grant on a database level.

That's one of the reasons why there's a superuser (usually named sysdba) who is the root of all databases:

dbc
 |...
 |-SYSDBA
 |...   |-myDB
 |...   |-myOtherDB
        |-anotherDB
        |-userA   |-subDB
                  |-anotherSubDB
                  |-UserB
                  |...

This user is an owner of all databases/users below him in the hierarchy and as owner you can Grant/Revoke any right on any owned object.


How to startup a oracle database with sql plus or with any developer tool without connecting to the Database How to startup a database without connecting to Database. Actually what I did is I shutdown my database and then my sql plus screen hanged and so closed unhandled.

then I tried to connect my database then I got ORA-12514 error.

I checked my database service in Services.msc -- it is working and I restarted my service as well. I check tnsname.ora file as well.

May be solution of
ORA-12514:
TNS:listener does not currently know of service requested in connect descriptor

normally you need a statically defined listener for this. So, in the listener.ora add the entries defining your instance. Next to that you need a remote passwordfile setup
remote_login_passwordfile = exclusive

to allow remote sys connects (don't forget the "as sysdba" part)


Are mime types only for file storage? If I have a plain-text field on a web form (not a file upload) inserting its content into my table, would it be wrong for me to set a mime type 'text' to it, or would it be safer?

This totally depends on your application. MIME is made for and is most common for E-mail, but it's also common for the web (http) and in some NoSQL databases.


Get top 5 students for each class in many-to-many relationship So I'm doing this in MySQL.
I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their columns.

students
student_id | name | email | GPA

classes
class_id | name | room

student_class_rel
student_id | class_id

I want to get a student_id of the 5 students for each class with the highest GPAs (along with the class info). How would I do this?
For example, this query gets 100 classes and then gets every student_id for all students in that class
SELECT *,
    JSON_ARRAYAGG(student_id)
FROM classes
    INNER JOIN student_class_rel
    USING(class_id)
GROUP BY class_id;
LIMIT 100

I want that query, expect the JSON_ARRAYAGG(student_id) only holds the ID to the top 5 students in terms of GPA for each class rather than every student.
Also, assume there could be upwards of millions of students and hundreds of thousands of classes and that there are around 50 to 1000 students per class. So performance is key for this query.
And I'm aware of hacky type solutions (like make multiple queries, once for each class) but so far those have been WAY too slow. I've spent too much time trying to figure this out, any help is greatly appreciated! (Also let me know if there's any additional info needed).

Student with GPAs is an aggregation
Class information is not an aggregation

Solution:

Use two queries
Query the GPAs (CTE and Window Function)
Query the class information

You will probably need two indices on student_id and class_id to boost the performance.
Example queries in MySQL 8.0.20:
WITH ordered_gpa AS(
    SELECT students.id,
        students.gpa,
        student_class_rel.class_id,
        rank() over (partition by student_class_rel.class_id order by students.gpa DESC) gpa_rank
    FROM student_class_rel
    INNER JOIN students
    ON student_class_rel.stu_id = students.id
)
SELECT *
FROM ordered_gpa
WHERE gpa_rank <= 5;

Here, we use Windows rather than GROUP BY, which is the PARTITION BY to split the rows.
SELECT class_id, name, room
FROM classes
WHERE class_id IN (
  SELECT DISTINCT class_id
  FROM student_class_rel
) tmp;

You could combine this query with the first one.


Count of distinct values of a table using sql name  number
=============
lin |   8
lin |   8
lin |   8
rob |   8
rob |   8
rob |   8
lin |   8
lin |   41
lin |   41
rob |   41

and I want the result like

name  |   Count of 8 |   count of 41
====================================
lin   |      4       |        2
rob   |      3       |       1

Can anyone help me please?

This is know as conditional counting and can be achieved using a case expression:

select name,
       count(case when number = 8 then 1 end) as count_of_8,
       count(case when number = 41 then 1 end) as count_of_41
from the_table
group by name
order by name;

Aggregate functions (like count()) ignore NULL values. The case expression inside the count() returns null for those values that we don't want to count, hence only the number of rows containing that value are counted. case when x then y end is equivalent to case when x then y else null end


Execute sp_update_jobstep to change SQL Agent job step type As I was going to deploy a change to our Ola Hallengren indexing scripts, I noticed on some SQL Servers we have indexing job running as an "Operating System (CmdExec)" step instead of being called as a "Transact-SQL script (T-SQL)" step.

I'm using CMS to deploy this using sp_update_jobstep and I'm wanting everything as "Transact-SQL script (T-SQL)".

It doesn't appear that there's a way to change the step (unless I'm getting the terminology confused in this Microsoft article)

use msdb
go

exec sp_update_jobstep @job_name = N'IndexOptimize_USER_DATABASES',
                       @step_id = 1,
                       @command = N'EXECUTE [dbo].[IndexOptimize], @Databases = ''USER_DATABASES'', @LogToTable = ''Y'', @LockTimeOut = 300'

Is there a way to change the step type?

You can use the @subsystem=N'TSQL' parameter of the sp_update_jobstep to alter the Step-Type.  Refer to sp_add_jobstep for a list of valid subsystem values.

Example:

EXEC msdb.dbo.sp_update_jobstep @job_name = N'test'
    ,@step_id = 1
    ,@subsystem = N'TSQL'
    ,@command = N'EXECUTE [dbo].[IndexOptimize], @Databases = ''USER_DATABASES'', @LogToTable = ''Y'', @LockTimeOut = 300'


What is the best way to design a database for this specific type of 'offer' that this restaurant chain offers? I am working on adding a few features in a mobile application of a restaurant chain and the tech stack used is Java for Android and PHP + SQL for backend.
Until now I have always used NoSQL, but since this app is already in production & uses SQL they require me to continue using the same tech stack.
This chain has 9 outlets in the district and in each of their outlets they have a 'Five in a box' meal where you can choose any five things from their menu and order them under the title of 'Five in a box' with a discounted price than if you order them separately.
Now the task is to keep a track of what 5 things each customer ordered and from which outlet did they order. Each night a Cron job will determine which combination of 5 things is the least ordered that day and then the users who ordered that particular combination get another discount coupon.
(They also need to keep all of the old records as well for analysis.)
What I have in mind is:
A restaurant1_offerorder table which will contain the following columns -
userid - ID of the user who made that order,
date - date of order,
item1, item2, item3, item4, item5 (five columns) which will contain the ids of the food item that the user bought.
And since each outlet is projected to have at least 700+ 'Five in a box' meal orders everyday a restaurant1_offerorder_old table with the same schema as restaurant1_offerorder which will contain all the old order data.
Every night the Cron Job will insert all records from restaurant1_offerorder to restaurant1_offerorder_old
This way the database will have 2 tables for each restaurant (18 tables in total which I feel is the wrong approach)
Now,
Question 1:
Is this the right database structure for this scenario? And should there be a separate database for each restaurant to store the old order data since soon there will be a lot of orders?
Or will it be better to keep all orders in one table and SELECT * with today's date? (Consider the volume of orders say a year from now)
Question 2:
(Not to be implemented. Just curious and want to future proof the db structure and keep scalability in consideration)
Say this system needs to be scaled up and turned into a food ordering application where any restaurant can list themselves on the application and can opt in to the 'Five in a box' meal deal (from their own menu), would creating a new table for each restaurant's 'Five in a box' meal orders and a table for their old orders be the right approach? (Seems like a bad idea considering a rise in the number of restaurants and number of users)

A restaurant1_offerorder table

That way you will need to have a separate table for every outlet of the restaurant, and this will make the entire system difficult to manage with a lot of tables. It doesn't make sense to have multiple tables with the exact same structure.
Read more - Is it better to have large tables or many tables
Instead you can consider adding the outlets to another table and add the ID of each outlet as another column into the offerorder table. That way you can have a single table for managing the Five in a box orders of all outlets.

Every night the Cron Job will insert all records from restaurant1_offerorder to restaurant1_offerorder_old

This isn't really necessary. Inserting less than 10000 rows per day (I say 10000 because you mentioned that there are 9 outlets with >700 entries/day) into a table having all integer columns of fixed size isn't going to slow down read operations noticeably (because apparently there aren't (m)any UPDATE or DELETE operations happening on this table, hence minimal fragmentation).
However since all the columns are foreign keys, if you ever change the PRIMARY KEY of any entity in another table, it will take some time to traverse all rows in the offerorder table and change the ID for all the occurrences. (I am not sure of this phenomenon, please correct me of I am wrong)
Though, this will happen only if the foreign key is set to ON UPDATE CASCADE. You can avoid this from happening by using ON UPDATE RESTRICT. (You can also set it to SET NULL or NO ACTION but that will make the old data useless )
Read more - How many rows are 'too many' for a MySQL table?, Database table with million of rows and Database that can handle >500 millions rows

Each night a Cron job will determine which combination of 5 things is the least ordered that day and the users who ordered that particular combination

An alternative to a cron job can be a TRIGGER that runs on every INSERT and keeps storing the set of five items and their order counters for the day and, optionally also the user IDs who ordered each set. At the end of the day you only have to SELECT the user IDs who ordered the set with the lowest count, and empty the counters table.
However, remember that using a trigger will add a slight overhead for every inserted order (this might be problematic when the server is under load when a lot of orders are being placed), but the computation at the end of the day will be very fast.
Read more - Mysql trigger/events vs Cronjob

They also need to keep all of the old records as well for analysis.

If the "analysis" to be performed is already defined, you might not need to store all the old data. You can calculate the results of the analysis every day and store only the daily results. Further, at the end of every month, you can calculate the analysis results for the entire month (using the daily results stored earlier) and get rid of the daily analysis data for that month. This can also be extended to quarterly, semi-annually, annually, all time etc. However, if the analysis computation method is changed in the future, you might not be left with much old data to recompute.

Say this system needs to be scaled up and turned into a food ordering application where any restaurant can list themselves on the application and can opt in to the 'Five in a box' meal deal (from their own menu)

If you continue using multiple tables as mentioned in the question, you will soon end up with a lot of tables to manage. You might want to go ahead with the single offerorder table option.
Also, the load on the server will increase substantially with increase in restaurants, their outlets and users. Make sure you prepare your database server for scalability (MySQL High Availability and Scalability) or use a solution provided by a cloud services provider.

P.S. I am not an expert in database design, so scrutiny is welcome and appreciated!


Multi-site Failover Cluster Instance for HA and DR to alwaysON Availability Groups In my company we have chosen to implement Multi-site SQL Server Failover cluster.
Basically Data center A  has 2 nodes (Active/Passive) and Data Center B geographically located acts as the DR. I believe there will be a SAN replication from  Data Center A Data Center B.

If in future we want to implement a Multi node  AlwaysOn and availability group HADR solution. By that i mean (Data center A  has 2 active node and 2xSANs, and DBs are part of a group. Data center B has an  Active node and its own SAN which act as a DR site).
Can you guide me on how to implement this process or articles that explain this in details.

How can I migrate from a geo-clustreing to an High Availability Disaster Recovery? and what should I consider?

The best practice for your scenario is: 2 nodes in Location A with their subnet say Network_A 192.168.1.x and one node in location B (DR) with subnet Network_B 192.168.2.x.

The weight in Location A is more so you don't need to add disk or folder to your cluster. Your failover Cluster will take 2 IPs one from Network_A and the other from Network_B and your Listener too. The Network_B IPs appears in your cluster offline unless you Failover the DR site, in normal cases you can use SQL database in your DR as read-only.

One important thing: if you have SAN replication don't make your Databases as part of this plan nor part of RDM (raw mapped drive).


PostgreSQL get first letter of each word (except...) I don't think I drank enough coffee today, but stuck on this for some reason.

Trying to get the first letter of each word, except a few stopwords.

Example "The United States of America" -> "USA"

select regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi')

works fine, but it returns setof text[] .

How to convert that to a simple string? Or is there a better way than regexp_matches?

Some options:

-- regexp_matches
SELECT string_agg(arr[1], '') AS string
FROM   regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi') arr;

-- regexp_split_to_table
SELECT string_agg(left(word, 1), '')
FROM   regexp_split_to_table('The United States of America', '\s+') t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));

-- Without regular expression
SELECT string_agg(left(word, 1), '')
FROM   unnest(string_to_array('The United States of America', ' ')) t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));

SQL Fiddle.

Option 2 splits on any amount of white space. Not exactly the same as your regex.
Option 3 splits on a single space character. If you can rely on that (?), the query is cheaper.


Using concat inside a stored procedure parameters I have a stored procedure and function that accepts a varchar as a parameter and i have this code where i am using concat function in the parameters of such function and stored procedure.

DELIMITER //
CREATE PROCEDURE ja_logan_proc()
BEGIN

SELECT id, dest_msisdn, text_message INTO @the_id, @the_number, @the_message FROM incoming_sms where job_status = 0 AND text_message LIKE '%logan%' order by rand() limit 1;

if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then

call register_lookup_r4(CONCAT(''','@the_id','''),CONCAT(''','@the_number','''));

elseif((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'false')then

select 'nothing to do here';

end if;

END //
DELIMITER ;

This line is identified as the one with an error

call register_lookup_r4(CONCAT(''','@the_id','''),CONCAT(''','@the_number','''));

and this other line

if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then

What am i doing wrong in the parameters field?.

The correct solution is not to try concatenating quotes around the argument.  That is a very naive approach, and leads to SQL injection vulnerabilities.

The QUOTE() function solves this, when it's needed.

However, you do not need these things to be quoted at all in the queries you're running.  Worse, adding literal quotes to these values is likely to get you wrong answers.

Additionally, you are doing unnecessary work by wrapping stored functions inside scalar subqueries.

IF 2 = (SELECT 2) is obviously a verbose way of saying IF 2 = 2 but in your examples, you're doing the same thing... IF(SELECT stored_function_call()) ... is an equivalent but less lightweight version of IF stored_function_call()  ...

Corrected and simplified examples:

IF search_number_in_r4(@the_number) = 'true' THEN
...
CALL register_lookup_r4(@the_id,@the_number);
...
ELSEIF search_number_in_r4(@the_number) THEN ...


SQL Server: Clustered index, sorting and pagination In my application, several times I have to show results that are paginated and sorted by some field.
For example a simple user list sorted by last name. Because of that and because I also have logical deletion and it's a multi-tenancy application, I generally use a CLUSTERED INDEX like this:
CREATE CLUSTERED INDEX [idx] ON [Users]
(
     IsDeleted ASC,
     [AccountId] ASC,
     [LastName] ASC
)

That means that a query like paginated like SELECT TOP(20) * FROM Users WHERE IsDeleted = 0 AND AccountId = xxx is sorted by LastName.  I know it's not guaranteed to be sorted, but in practice it always is.
However, reading here Kimberly Tripp blog post on clustered indexes, she says it's a horrible idea to do it like so. And it's even worse because the IsDeleted (BIT) field doesn't let me to set the
However, if I change the CLUSTERED INDEX to just the unique ID, I would need to start using ORDER BY LastName, which in practice is very slow.
My table has a few million records (tens of millions at most) and it's used in general as follows:

Querying data. Most of the time.
Bulk updates/inserts, where only the data modified is under the subset IsDeleted = 0, AccountId = xxxx (only non-deleted data for a single account is updated in bulk).

Question:
What would be the recommended index (and how to sort) for these kind of tables?
Another example for these kind of tables would be a survey results table that has the following columns IsDeleted (BIT), AccountId (FK GUID), UserId (FK GUID), QuestionKey (NVARCHAR), AnswerValue (TEXT), where my CLUSTERED KEY would be probably (IsDeleted, AccountId, UserId, QuestionKey) and 99% of time I would query the table or update it in bulk by the 3 first fields
WHERE IsDeleted = 0
AND AccountId = xxx
AND UserId = yyy

or even the 4 fields: ... AND QuestionKey = 'country'
EDIT:
One of the primary reasons I did this is because the bulk updates and the queries are always limited to 1 or a small number of pages. Having an Identity column would require the queries and the updates to read/write in most pages.
EDIT 2:
Following Joe Obbish's example:
This query:
SELECT TOP (20) *
FROM Users2
WHERE IsDeleted = 0
AND AccountId = '46FC5693-7446-415A-8626-8937365460D1'
ORDER BY [LastName];

Having 1 cluster index in (IsDeleted, AccountId, LastName) results to this:

CPU time = 3 ms, elapsed time = 3 ms.
Table 'Users2'. Scan count 1, logical reads 5, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Having one cluster index in a new PK ID (NEWID()) column (that way the data is randomly sorted internally) and one non-clustered (IsDeleted, AccountId, LastName) results to this:

CPU time = 16 ms,  elapsed time = 18 ms.
Table 'Users2'. Scan count 1, logical reads 533, physical reads 5, read-ahead reads 1240, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note the IO and time. It's way slower and requires more IO if the data is not stored together.  It might take more space, but the speed difference is notable. Am I wrong to do it like that?

First I need to reiterate what RDFozz said in his answer about including an explicit ORDER BY. If SQL Server does an allocation order scan you may get the wrong results. Including ORDER BY in the query doesn't cause a performance hit. Why not do it?

From a query performance perspective, the index that you want depends on how many rows you return at a time and how many columns are actually needed from the table.

First I'll throw around 6.5 million rows into a table with six tenants:

CREATE TABLE dbo.Users2 (
    IsDeleted Bit NOT NULL,
    [AccountId] UNIQUEIDENTIFIER NOT NULL,
    [LastName] NVARCHAR(50) NOT NULL,
    [UsefulColumn] NVARCHAR(20) NOT NULL,
    [OtherColumns] NVARCHAR(100) NOT NULL
);

CREATE CLUSTERED INDEX [idx] ON [Users2]
(
     IsDeleted ASC,
     [AccountId] ASC,
     [LastName] ASC
);

CREATE TABLE #ids (id INT NOT NULL IDENTITY (0, 1), [AccountId] UNIQUEIDENTIFIER NOT NULL);
INSERT INTO #ids
SELECT TOP 6 NEWID()
FROM master..spt_values;

INSERT INTO [Users2] WITH (TABLOCK)
SELECT
CASE WHEN t1.number % 10 = 1 THEN 1 ELSE 0 END
, #ids.[AccountId]
, LEFT(REPLACE(CONVERT(NVARCHAR(50), NEWID()), '-', ''), 12)
, REPLICATE(N'Z', 20)
, REPLICATE(N'Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
LEFT OUTER JOIN #ids ON ABS(t1.number % 6) = #ids.id;

DROP TABLE #ids;

-- get an ID: FFA7D6D8-63E8-422B-B5E7-F7020871CDB4
SELECT TOP 1 [AccountId] FROM Users2
WHERE IsDeleted = 0
ORDER BY [AccountId] DESC;

When running a query similar to yours:

SELECT TOP (20) *
FROM Users2
WHERE IsDeleted = 0
AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

I get a clustered index seek as expected:

Is using the clustered index the best option? It depends. If you don't need to select every column from the table then you can define a smaller covering index which returns the data you need without an explicit sort. Having a smaller covering index is good for performance as you page out further into the data. Suppose that you only need UsefulColumn and not the OtherColumns column. You could define the following index:

CREATE NONCLUSTERED INDEX [idx_1] ON [Users2]
(
     [AccountId] ASC,
     [LastName] ASC
)
INCLUDE ([UsefulColumn])
WHERE IsDeleted = 0;

It's pretty large. For my test case it's around 28% of the size of the data. For this index, it's important to note that changing the clustered key of the table will not have a big impact on its size. SQL Server stores the clustered key columns in the leaf node of the index unless they are already included in the index. This can be demonstrated with a simple test:

CREATE TABLE dbo.IX_TEST (
    COL1 BIGINT NOT NULL,
    COL2 BIGINT NOT NULL,
    FILLER VARCHAR(6) NOT NULL,
    PRIMARY KEY (COL1)
);

INSERT INTO dbo.IX_TEST WITH (TABLOCK)
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 6)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

EXEC sp_spaceused 'IX_TEST'; -- 96 KB

CREATE INDEX COL1 ON dbo.IX_TEST (COL1)
EXEC sp_spaceused 'IX_TEST'; -- 14032 KB

CREATE INDEX COL2 ON dbo.IX_TEST (COL2)
EXEC sp_spaceused 'IX_TEST'; --- 35920 KB

Going back to our table, if I created an index on just the UsefulColumn column there would be an overhead (with no compression) of approximately 1 byte for the IsDeleted column, 16 bytes for the AccountId, 2 * the average length of the LastName column, and 0 or 4 bytes for the internal uniquifier (only happens for duplicate last names). For my test data that's quite a bit of overhead:

  1 + 16 + 2 * 12 + 0 = 41 bytes

However for the idx_1 index I defined above it's around only 1 bytes (1 for IsDeleted and 0 for the uniquifier, assuming not very many duplicate last names). The index is mainly large because I'm using wide columns as key columns. Keep in mind that the indexes will be the same size with your current clustering key, but changing the clustering key of the table to a thinner set of columns will greatly decrease the size of the index only defined on UsefulColumn.

For any TOP value I should get a nice index seek on the covering index. This query:

SELECT TOP (200000) [LastName], [UsefulColumn]
FROM Users2
WHERE IsDeleted = 0 AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

Has the following plan:

Even if you need every single column from the table the index defined above can still give good enough performance. You'll get a key lookup for every row that you return. For a single end user, doing key lookup on 20 rows it shouldn't be noticeable. In your test you saw execution times of 3 ms and 18 ms. However, if you have a highly concurrent workload it could make a difference. Only you can evaluate that properly.

Even without the caveats above, when selecting many rows you may notice a large performance difference:

The first query has an index hint to force the use of the index. The query optimizer thinks that plan will be much more expensive than the plan that uses the clustered index.

If I had to guess, I would say that you probably don't need to use a clustered index to perform your paging. If you have a lot of indexes on the table you might benefit from creating a new covering index for your pagination queries and by defining the clustered index on a smaller, unique set of columns.


Getting the entire records from Two tables for Count purposes SQL I'm just wondering if anyone could help on how to bring two columns from two tables together in one table.

These table have id's but they are not linked although they have other columns that I can join them on. But for the sake of getting the count of individual ID's I'm not sure if inner or outer join will give me the result that I want.

Here are the tables:

TABLE A

ID
1
2
3
4
5

TABLE B

2
4
5
7
8
9
10
11
12

I should be able to retrieve:

select count(a.id) from the combined_table
select count(b.id) from the combined_table

If there's no relationship between them...

Table A is customers and Table B is employees, and you really want to know how many bodies were involved with a production of Spamalot! I would look at a query like

SELECT
    A.RC + B.RC AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
) A
CROSS JOIN
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) B;

Read that as Add the RC column from A to the same column in B. A and B are derived in line and will only ever have one value returned as we're computing the scalar count of all the rows.

I'm using a CROSS JOIN because I need everything from both sets. I could have done this as a UNION ALL between the counts. The ALL is important, otherwise UNION eliminates duplicate rows. A variant on B would be to use a COUNT in the outer query and just select all the IDs. Assuming the data types match, it'd work either way.

-- Option B

SELECT
    SUM(D.RC) AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
    UNION ALL
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) D(RC);

or just done it inline from the SELECT

-- Option C
SELECT (SELECT COUNT_BIG(*) AS RC FROM dbo.A A) + (SELECT COUNT_BIG(*) AS RC FROM dbo.B B) AS TotalBodies;

Some of that may be database dependent but works fine for SQL Server.

SQLFiddle


error code 3417 - after installing CU for SQL SERVER 2016 SP1 I am getting below error message after installing cumulative updates for SQL Server 2016 Sp1.

  The description for Event ID 7024 from source Service Control Manager
  cannot be found. Either the component that raises this event is not
  installed on your local computer or the installation is corrupted. You
  can install or repair the component on the local computer.

  If the event originated on another computer, the display information
  had to be saved with the event.

  The following information was included with the event:

  SQL Server (MY_INSTANCE_DEVELOPMENT) %%3417

  The locale specific resource for the desired message is not present

I have 2 instances on that server,
DBA16 AND DEVELOPMENT.

DBA16 started up nicely and no problem, but DEVELOPMENT is not starting.

I tried to start the service manually and get the same problem.

I have applied these same updates on several servers, including clustered ones, with alwaysOn and replication, and had no problems.

Replication distributor also worked fine.

This server in particular, I could find no reason for these problems.

I might have to re-install sql server or rebuild the instance, if it is related to corruption in some file, or the master database.

is there anything I should be aware of or might have not looked\considered?

There is a similar question here: Start service error code 3417 with SQL Server 2012

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 15173, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

I have started the sql server service from the command line using the trace flag /T902

FROM TRACE FLAGS

902

Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack. If you encounter an error during script upgrade mode, it is recommended to contact Microsoft SQL Customer Service and Support (CSS) for further guidance. For more information, see this Microsoft Support article.

WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance.

Scope: global only

this is also a good link:
Use Trace Flag 902 to Recover from a Cumulative Update Failure

After that I have looked at my error and it was about applying an update script.

I manually open that file and run it from management studio:

and the real error is:

Msg 15173, Level 16, State 1, Line 63894
Server principal '##MS_AgentSigningCertificate##' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
Msg 208, Level 16, State 1, Line 63932
Invalid object name 'dbo.sysssispackagefolders'.

Here is another wonderful contribution by Pinal Dave on the subject:

SQL SERVER – Script level upgrade for database master failed because upgrade step sqlagent100_msdb_upgrade.sql

I have just seen this one:

Failure during install of SQL Server 2016 sp2

after sorting this out,
I will start the sql service again, without the trace flag 902.


Deploying SQL Server database changes from test to production server I have a high availability production server that runs SQL Server. It is constantly being read/write to by hundreds of users.

I have an exact copy of the database on a testing server and do all the development in that environment. Every few weeks, there will be major changes made to the front-end application and the database itself (e.g. new stored procedures, new columns, new tables).

Because the data on the production server is always the latest, I don't want to lose that data when I want to make changes to the database schema. The current process for deploying changes is:

backup the production server and pause it
restore it into the testing server
make all database changes in the testing server
Backup the testing server
Restore the new version to the production server and un-pause it

The steps seem convoluted and is causing unnecessary downtime on the production server. What I would like to do is update the production server database in the background while still allowing users to continue reading/writing the database.

So far I can see that you can use copy database wizard or use scripts to make changes, but they seem to want to drop/create the database on the production server to make changes. How could I achieve a less severe way of updating the production server's database?

There was excellent tool Adept SQL Diff, lightning fast, but it's not supported anymore and does not work on Windows 2008 onwards. So Redgate is the choice.

Maybe it's a good time to consider Continuous Integration (CI) and Agile methodology. You will need SSDT (SQL Server Data Tool) and a build software -Team Foundation Server, Bamboo, Teamcity etc. We use TeamCity, it allows you to have up to 20 projects for free. And of course, you will ned source control software, like Git, SVN or TFS.

It's a bit tricky to setup that kind of environment but once you are done and all is tested and works, your deployment will be flawless and literally "at one click".

First of all you will need to do reverse engineering and covert your production database into SSDT project. If you have many databases, you need to convert each one separately. After that you create your CI projects using your chosen build software.

You will need 3 or 4 separate environments:

Integration or test. If you do no want to run unit tests you don't need it. But most people in modern world have it to reduce regression testing. So here fresh sparkling database is created at each build and there you automated tests are run with some predictable results. So you need to fill that database with some test data. We usually do it by SQL scripts or SSIS packages. If you have small project you can do build every hour, if there are commutes (changes). Or run it on nights, if you have 20K unit tests to do.
UAT or Staging. This is where you test your deployment on live data. Before making deployment you restore production backup to the UAT database and run your deployment there. You may want to distort your sensitive customer data somehow. All errors of the deployment are noted and fixed. QA team does user acceptance testing there, if necessary. You run your deployments until you have green tick against run results.
Production. After all acceptance stuff and testing is done you can roll out your changes to production. Since you tested everything before all should go smooth. At the time of deployment you cut access to the website or database, but usually deployment the schema and small data changes take seconds.

Your typical deployment project comprises many steps, so you can deploy your website(s) and database(s) altogether and the best thing out there is project templates for centralized setup.

I did not mention 4th type of environment which is ‘Development’. Every developer has his own copy of everything (it is actually Integration environment) so can he can run/check deployment and unit tests on local workstation before he commits code to the central repository.

You don’t need 3 physical servers to have all above. You can either use virtual machines, SQL sever instances or just name your databases differently for each environments using project parameters.


MIN(column) with subquery How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe?

SQL> DESC STUDENTS;
 Name                    Null?    Type
 STUDENT_NO                       VARCHAR2(10)
 SURNAME                          VARCHAR2(20)
 FORENAME                         VARCHAR2(20)

SQL> DESC MODULES;
 Name                    Null?    Type
 MODULE_CODE                      VARCHAR2(8)
 MODULE_NAME                      VARCHAR2(20)

SQL> DESC MARKS;
 Name                    Null?    Type
 STUDENT_NO                       VARCHAR2(10)
 MODULE_CODE                      VARCHAR2(8)
 MARK                             NUMBER(38)

I have:
SQL> SELECT MOD.MODULE_NAME, MA.MARK
    FROM MODULES MOD
        INNER JOIN MARKS MA ON(MOD.MODULE_CODE = MA.MODULE_CODE)
    WHERE MA.STUDENT_NO = (SELECT S.STUDENT_NO FROM STUDENTS S WHERE S.SURNAME = 'Doe');

MODULE_NAME                MARK
Databases                    80
Programming                  65
Operating Systems            50
 but cannot get it to display the lowest value.
Trying to put a MIN(MA.MARKS) in the SELECT clause or after the WHERE clause causes an error.

Why would this not be sufficient?

SELECT * FROM (
  SELECT MOD.MODULE_NAME, MA.MARK
  FROM MODULES MOD
  JOIN MARKS MA ON (MOD.MODULE_CODE = MA.MODULE_CODE)
  JOIN STUDENTS S ON (S.STUDENT_NO = MA.STUDENT_NO)
  WHERE S.SURNAME = 'Doe'
  ORDER BY 2) x
WHERE ROWNUM = 1;


Mongo DB Replica set - not able to read also We have deployed mongodb replica set:

mongod --port 27017   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

mongod --port 27018   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

mongod --port 27019   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

What I understand from mongdb documentation; even if your two nodes goes down, we still should be able to read from remaining working nodes (Single working node in this case). However, write is not possible.

But even reads are not working on the available node. I also try to add one arbiter but still no luck. Remaining node still stay in secondary stage.

I am using mongodb 3.0.

The reason

The answer is quite simple: when two voting nodes of three are down, the remaining one reverts to secondary, to which – by definition – one can't write. The default read preference is to read from and write to the primary of a replica set only. Hence, when only a secondary remains in the replica set, you can not read.

tl;dr

You have to change your read preference at least to primaryPreferred, allowing reads from secondaries.


How to find XXth day of previous month in SQL server? I have 2 parameters,

an input date (DATETIME)
input day of month (TINYINT)

If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the output DATETIME.

In other words, I would like to select the Xth day of the previous calendar month.
I am then going to use DATEDIFF to find the current fiscal day of month.

DECLARE @d DATETIME = '20140311', @dm TINYINT = 26;

SELECT DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d)));

Result:

2014-02-26 00:00:00.000


Index Fragmentation - Am I interpreting the results correctly? I'm not a DBA, but I'm responsible for a database that currently has hundreds of tables and ~5TB of data. I recently ran the following query, in hopes of determining index fragmentation:

Declare @DatabaseId Int = DB_ID('ODS')

SELECT
    OBJECT_NAME(T.OBJECT_ID) as TableName,
    T2.Name as IndexName,
    T.index_id as IndexId,
    index_type_desc as IndexType,
    index_level as IndexLevel,
    avg_fragmentation_in_percent as AverageFragmentationPercent,
    avg_page_space_used_in_percent as AveragePageSpaceUsedPercent,
    page_count as PageCount
FROM sys.dm_db_index_physical_stats (@DatabaseId, NULL, NULL, NULL, 'DETAILED') T
    INNER JOIN [sys].[indexes] T2 ON T.index_id = T2.index_id And T.object_id = T2.object_id
ORDER BY avg_fragmentation_in_percent DESC

The first 30-40 rows of the result set looks as follows (after importing into Excel):

This was very startling to me. Am I reading this correctly, that I have all these indexes, actually a lot more, that are 100% fragmented? Is my query correct?

Yes, that's what it looks like. Unless there is some error happening with the data after it got moved to Excel.

However, these are all tiny, tiny tables. Stop caring about fragmentation on tables with less than, say, 1,000 pages.* And even then you probably shouldn't care too much until another order or two of magnitude, and even less if you are using SSD and/or your database fits into memory even with the fragmentation.

The work that you'll spend reorganizing them will not have the impact you expect, and the benefit from any changes you actually see, temporarily, will not justify it at all. I would simply filter such small tables out of your queries right from the start.

* 1,000 pages is just my ballpark, pull a number out of your you-know-what suggestion. There is no magic number for this. But seriously, for small tables, focus your efforts elsewhere.


sql querying for a pattern I was hoping someone could steer me in the right direction with this one. I have a table that has student grades.

Create Table StudentGrades
(
  StudentID references Student(StudentID) int not null,
  GradeIssued varchar(1) not null,
  WhenIssued datetime not null default(getdate())
)

I'd like to write a query that does something like: Find students who have grades (in sequence) of a D, then a C, then another C, then an A. I know the direction (because of the date the grades are issued) that the pattern should be in, but I can't think of how to write this without using a cursor.

In my experience with querying I'm filtering through via WHERE and HAVING clauses, but I'm not sure where to go when I want to find a particular pattern.

One way of doing it would be to use LEAD.

WITH T AS
(
SELECT  StudentID,
        GradeIssued AS g1,
        LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2,
        LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3,
        LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4
FROM StudentGrades
)
SELECT DISTINCT StudentID
FROM   T
WHERE  g1 = 'D'
       AND g2 = 'C'
       AND g3 = 'C'
       AND g4 = 'A'

Another way would be to use XML PATH to concatenate all grades in order and LIKE.

WITH sg1 AS
(
SELECT DISTINCT StudentID
FROM StudentGrades
)
SELECT *
FROM sg1
CROSS APPLY
(
    SELECT GradeIssued + ''
    FROM StudentGrades sg2
    WHERE sg2.StudentID = sg1.StudentID
    ORDER BY WhenIssued
    FOR XML PATH('')
) CA(grades)
WHERE grades LIKE '%DCCA%'


I want to know which table the value comes from in inner join I have a simple query where i do 2 inner joins to combine data from 3 tables. within the table i would like to know which table each value comes from. i attempted the following but the syntax is wrong.

select e.subscriberkey, Source
CASE
    WHEN FROM Currently_in_Welcome THEN Source = "E"
    WHEN FROM Sell_Engage_Unknown THEN Source = "S"
    WHEN FROM At_Risk_NonEngagement THEN Source = "R"
END
from Currently_in_Welcome e
inner join Sell_Engage_Unknown s on e.subscriberkey = s.subscriberkey
inner join At_Risk_NonEngagement r on r.subscriberkey = e.subscriberkey

thanks in advance for all your help.

Pattern

SELECT COALESCE(table1.value, table2.value, ...) AS value,
       CASE WHEN table1.value IS NOT NULL
            THEN 'table1'
            WHEN table2.value IS NOT NULL
            THEN 'table2'
            ...
            ELSE 'None'
       END AS source
FROM maintable
LEFT JOIN table1 ON maintable.field=table1.field
LEFT JOIN table2 ON maintable.field=table2.field
...


Can I rename the values in a MySQL ENUM column in one query? Suppose I have a Database table with an ENUM('value_one','value_two').
I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows:

ALTER TABLE `table` MODIFY `column` ENUM('value_one','value_two','First value','Second value');
UPDATE `table` SET `column`='First Value' WHERE `column`='value_one';
UPDATE `table` SET `column`='Second Value' WHERE `column`='value_two';
ALTER TABLE `table` MODIFY `column` ENUM('First value','Second value');

Is there a more efficient way to do this, E.G. a way to accomplish this with a  single ALTER TABLE statement?

The follolwing technique I am about to show you will require guts of steel.

Given the following criteria

datadir is /var/lib/mysql
table is mydb.mytb
enum column called is called enum_col
engine is MyISAM

Here is a death-defying crack at it:

CREATE TABLE mydb.mybt LIKE mydb.mytb;
ALTER TABLE mydb.mybt MODIFY enum_col ENUM('First value','Second value');
SET wait_timeout=86400; SET interactive_timeout=86400;
FLUSH TABLES WITH READ LOCK;
In a separate OS/SSH Session, swap the .frm files

$ mv /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/myxx.frm
$ mv /var/lib/mysql/mydb/mybt.frm /var/lib/mysql/mydb/mytb.frm
$ mv /var/lib/mysql/mydb/myxx.frm /var/lib/mysql/mydb/mybt.frm

UNLOCK TABLES;
DROP TABLE mydb.mybt;

That's it !!!

CAVEAT : I CANNOT TAKE CREDIT FOR THIS !

This technique comes from "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

  The technique we are about to demonstrate is unsupported,
  undocumented, and may not work. Use it at your risk. We advise you to
  back up you data first!

Give it a try ! (Please let us know how it turned out)

UPDATE 2011-10-05 17:49 EDT

If the table is MyISAM and you have enough space in production and a straight-up downtime window, try this:

service mysql restart --skip-networking
In a separate OS/SSH Session, make a copy of the table

cp /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/mytbplay.frm
cp /var/lib/mysql/mydb/mytb.MYD /var/lib/mysql/mydb/mytbplay.MYD
cp /var/lib/mysql/mydb/mytb.MYI /var/lib/mysql/mydb/mytbplay.MYI

INFORMATION_SCHEMA.TABLES will automatically detect the presence of the new table called mydb.mytbplay.

Perform the guts-of-steel algorithm on mydb.mytbplay
You test the integrity of mydb.mytbplay

If you are satisfied

ALTER TABLE mydb.mytb RENAME mydb.mytb_backup;
ALTER TABLE mydb.mytbplay RENAME mydb.mytb;
service mysql restart

Give it a try!


How to store x,y graphs: (a) Using x,y columns for each point, or (b) to store the set of points as comma separated strings? We are designing a database to store the X,Y graph data of electronic devices apart from other entities.
Business domain description

We have a collection of objects A, each object A has a collection of objects B.

Each object B has a collection of X,Y points forming a graph.

For example:

Object A has a collection of ~30,000 to 50,000 object B instances.
Each object B instance has X,Y graph data of about 200 to 250 points of double values.

Expected data size:

~ 100,000 A instances
~ 50,000 B Instances in each A
~ 200 graph points

Number of rows:

100,000 * 50,000 * 200 = 1,000,000,000,000 = 1 trillion.

Requirements

The users are interested in fetching the entire data set of an object B instance, not of a single point.

The users are interested in getting the X,Y points of all the B object instances for a given object instance A.

Users rarely fetch, probably like one or two queries in a day for a given object instance of A.

Considerations
Design 1
Standard-approach design in the simpler form to design the database to retain point data:

Pros: The data is stored "directly" so retrieval doesn't need any conversion.

Cons: The table ends up with trillion of rows, we need to do table partition to get quicker access of data.

Design 2
To store as comma separated values:

Pros: Data table size is confined to the instance counts of Object B.

Cons: Involves using string split to convert the string to rows or to give the data in the comma separated format to the user.

There's a third option: store column 2 and 3 together, so like

X1,Y1,X2,Y2,X3,Y3

If you're absolutely sure you never need to sort or filter on these coordinates, and rely on application logic to create, update and process them, it's fine to store them as a 'blob' in a single column.


Entity ID field that can refer to multiple tables? In designing both audit tables and notification tables, I've run into an issue where I need a row to refer to an entity in another table, but which table that entity is isn't always the same. For example:

Each time a user makes a post or uploads a new photo to their album, a notification is created. If the notification is for a post, the notification entry must refer to the posts table. If it is for a photo, the notification entry must refer to the photos table.
Moderators may edit posts, delete posts, or block users. The audit_actions entry must refer to the entity that was acted upon in the corresponding table.

My original approach looked like this (this is heavily simplified, not the actual table structure):

TABLE notification_types:
     id         INT
     type_id    INT
     entity_id  INT

If type_id was 1, entity_id would refer to an entry in the posts table
If type_id was 2, entity_id would refer to an entry in the photos table
etc

I was starting to use a similar scheme for audit tables. My plan was to make an audit_history table for each auditable table that mirrored the original table (e.g. posts and post_audit_history), and maintain a single audit_actions table that linked an actor and action to each history item

TABLE audit_actions
     admin_id        INT
     action_id       INT
     entity_id       INT
     audit_entry_id  INT

If action_id was 1 ("edited post"), entity_id would refer to an entry in posts, audit_entry_id would refer to an entry in posts_audit_history, etc

Of course, this approach is terrible. entity_id can refer to multiple tables, which is confusing and prevents it from being a foreign key. There is no way to enforce integrity without complicated triggers.

I've been trying researching and trying to find solutions but I am stuck. Some approaches I've considered:

Maintain an entity_ids table. Each entity in every table then has a foreign key to an entry in the entity_ids table; in other words, every single entity in any table has a globally unique ID. This maintains integrity, but when we looked up an item by its entity_id we'd have to search multiple tables to find the entity with that ID
Maintain separate notification tables for each type of notification, and separate audit tables for each type of auditable table. E.g. post_notifications, photo_notifications, post_audit_actions, profile_audit_actions, etc. This would require running messy and expensive joins for what were formerly simple queries like 'find all notifications for user X' or 'get all audit records for user Y' or 'get the 100 most recent audit records'

Which approach is best? Is there a better strategy that I haven't thought of here?

One idea comes from extending the analogy of interfaces in object-oriented programming. You can have an IGeneratesNotification "interface" entity that the Posts and Photos entities "implement", as well as an IAuditable "interface" entity that the Posts and Users entities "implement". It would be possible for an entity to implement multiple interface entities, such as in the case of Posts. Here's a diagram of how this can be done.

Note that the fields containing references to the interface entities would be unique.

Advantages:

You can enforce referential integrity to the interface entity that can be implemented by one of several entities.
If there are fields common to all implementing entities, they can be abstracted out to the interface entity.

Disadvantages:

It is possible for the id of an interface entity to be present in 0, or more than one, implementing entities.
The service layer would be required to determine which entity the notification or audit action applies to before knowing which table to query.


Query tuning help needed (Hash Match and Table Scans) I need help optimizing the following query (returning ~8k rows):

SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
                                FROM (
                                SELECT A.sys_id, AppSvr.name
                                FROM GTS_DataStage.SN.cmdb_ci_appl A
                                LEFT JOIN GTS_DataStage.SN.cmdb_rel_ci X
                                    ON A.sys_id = X.parent
                                LEFT JOIN GTS_DataStage.SN.cmdb_ci AppSvr
                                    ON X.child = AppSvr.sys_id
                                    AND AppSvr.dv_sys_class_name
                                      IN ('UNIX Server', 'Windows Server',
                                      'IBM Mainframe', 'Application Server')
                                WHERE A.dv_sys_class_name = 'Application'
                                 AND A.dv_u_used_for = 'Production'
                                 AND A.dv_operational_status = 'Deployed') T
                                WHERE A.sys_id = T.sys_id
                                FOR XML PATH('')), 3, 4000) AS NVARCHAR(4000))
FROM GTS_DataStage.SN.cmdb_ci_appl A
WHERE A.dv_sys_class_name = 'Application' AND A.dv_u_used_for = 'Production'
    AND A.dv_operational_status = 'Deployed'

These tables are on the DataStage and I am not allowed to use indexes. Total rowcounts are:
cmdb_ci_appl: ~20k; cmdb_rel_ci: ~1200k; cmdb_ci: ~800k

The following image shows a section of the execution plan (I can upload the sqlplan file if needed but there's not much else to see):

Any help is appreciated.

Show the DBA those Eager Index Spools in the query plan. SQL Server is so desperate for supporting indexes here, it is creating its own temporary ones on every execution!

There is always more to see in the sqlplan file, so please do upload it. A statistics-only copy of the tables in question (or table and index definitions at the very least) would also be useful if you want more than general advice).

This answer was originally a comment on your question. I'll update it if a query plan or DDL is provided.


SQL Server function invocation syntax question The following excerpt is extract from this page.

  CURRENT_USER (Transact-SQL)

  Returns the name of the current user. This function is equivalent to
  USER_NAME().

When I execute the two function with the following TSQL scripts (the questionable ones were commented), I'm confused about the invocation syntax. I want to know when to use the parenthesis and when not. Thanks!

SELECT CURRENT_USER
-- SELECT CURRENT_USER() -- Error!
SELECT USER_NAME()
-- SELECT USER_NAME -- Error!

The ones without parentheses such as CURRENT_USER and CURRENT_TIMESTAMP are there for ANSI 92 compliance. There are only five such Niladic Functions and the list hasn't changed since SQL Server 6.0.

CURRENT_USER
CURRENT_TIMESTAMP
SESSION_USER
SYSTEM_USER
USER

Most other SQL Server system functions require parentheses.

The exception to this are those functions that used to be known as global variables that are prefixed with @@ such as @@SPID.

This last group of functions can be passed directly in a parameter list to a stored procedure. Neither of the other two variants can.


Is it possible to target a specific instance in a clustered environment with group policy? Not sure where to ask this, but here goes:

I'm in a clustered environment, not the primary DBA (and he's not very AD/GP savvy either) and I'm being asked to set security permissions for an AD Service Account on a specific instance on a cluster that hosts multiple instances. Is that even a possibility?

Community Wiki answer generated from a comment on the question by Steve Mangiameli

It is, but you'll need to be a little creative.  Remember, you will be setting two different permissions sets - OS and SQL Server.  Hopefully, each instance has install and DBs set in different directories, for each instance.  OS level permissions should be directed for the disks/directories for that instance.

SQL server permissions are relatively simple at that point.


How to clear alert.log without ADRCI? I know there is ADRCI purge command since 11g, but how can I delete part of the alertlog without ADRCI for a specified time period, I want delete everything older than 15 days.

I would rotatelogs command to periodically copy the logs into a new one, and then delete the old log after a point in time. You can put it into a cron job.

However, during troubleshooting it is VERY beneficial to have the entire alert logs - and in one file. Be sure to keep it somewhere close, when trouble comes by.


MySQL replication process parallel with user process? I have process which executes query like below on Mysql(InnoDB)  slave

INSERT ignore INTO   user_profile_temp (some_fields)
select some_fields from user_profile

user_profile is bigtable  of 8GB  which will take 1 to 2 hour with HDD.

Because of this query , replication(master to slave) is waiting in queue to get above query getting complete. There can be lag of 1 to 2 hours.

Lag is happening because of select query.As select query will acquire shared lock only at row level which mean replication records not impacting current read in process , should be able to update the record parallely.

My question is does replication process waits if any user process running at that instant or it can run in parallel if it is not impacting same rows/tables?

It depends.

Older versions of MySQL run one replication query at a time.  Newer versions can do some level of parallelism.

Meanwhile, queries against the Slave can run in parallel just the same as reads and writes can go in parallel on a single server.  If the write (from replication) and the read (from a user on the slave) do not touch the same rows of the same tables, then they can go in parallel.  But if a significant lock is taken out by one, the other must wait.  Or get into a deadlock.

It's not clear where your INSERT is coming from.  It should not be coming from the Slave, since Slaves should be readonly.  If it is coming from the replication stream, then what version of MySQL is running and how is it configured?


Disk Crashed, NDF(Index) file lost I have database with :

-two filegroups (PRIMARY and INDEX)

-three files .mdf(data), .ndf(index) and .ldf(log)

every file is on different disk, yesterday disk where was NDF file crashed so I lost that file.
Is there any way to fix database, without restore?

TNX!

Is there any way to fix database, without restore?

No, you will have to do a restore.  Please see this reference on how to do file and filegroup restores.


SQL Connection Pooling I have a .net website connecting to SQL. Just wondering if connection pooling is  enabled by default or is it something I have to activate myself?

Essentially this is enabled by default when using the System.Data.SqlClient data provider. There is a very good article about the semantics which can be found here:

SQL Server Connection Pooling (ADO.NET)

Also, you can view the ConnectionString property reference of the SqlConnection class here:

SqlConnection.ConnectionString Property

This contains a lot of information about the options that are available when configuring a connection string for Sql Server.

You should also note that the actual connection pool itself is kept within the Application Pool assigned to the website/web application, so be aware that if you have multiple sites/web applications using the same Application Pool they will also share the same connection pool which may not be desirable to you.

As such, I would recommend assigning a new application pool for each website and each web application underneath the site itself. You can find information about application pools at the link below:

Application Pools


MySQL high availability, failover and replication with Latency We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of MySQL we will run ... either Community or Enterprise, but that is a TBD. Looks like we will be running the InnoDB engine, OS is going to be RedHat EL 5.5 The primary servers are going to be active, while the secondary will be passive or hot stand-bys.

I would like to implement replication, high availability and automated failover in MySQL across the two data centers.

After a failover to the secondary servers, when we fail-back to the primary servers, we'd like to have data synched from the secondary DB to the primary DB quickly and completely so that we can continue to serve content from the primary servers.

I am interested in knowing what technologies/tools/best practices can be used to solve/address these issues. Also, any gotchas or ah-ha moments would be much appreciated as well. I've read up on MySQL replication, clustering, and at some 3rd party tools like Tungsten and Dolphinics, but I a unsure what the is best course of action.

Thank you for your time!

KM

For the sake of simplicity, I recommend MySQL Circular Replication only. Here is why:

There are many technologies and topologies that are far superior to MySQL Circular Replication. My favorite, hands down, is DRBD (Distributed Replicated Block Device). However, DRBD works great when the Server Pair is in the same bulding, data center, and rack. It's even better when using a crossover cable in the 192.168.x.x. subnet between the DRBD Primary and DRBD Secondary. Unfortunately, DRBD has horrible performance over a distance between two locations, although DRBD can still work. There are no network topologies around to give you the satisfactory DRBD performance needed between two datacenters.

Once you setup MySQL Circular Replication between the two DB servers in two different data centers, the only tuning needed is for the network. In essence, the replication performance is a function of network settings (speed/latency of binary log transmission in MySQL Replication Setup) and disk I/O (DRBD).

An alternative you may want for better redundancy is the following for the sake of example:

Setup a DRBD Pair in both locations
DRBD Pair in Site #1 with VIP 111.111.111.111
DRBD Pair in Site #2 with VIP 222.222.222.222

Setup MySQL Circular Replication between the DRBD Primary Servers under these conditions:
For site #1, use 222.222.222.222 as the Master_Host in MySQL
For site #2, use 111.111.111.111 as the Master_Host in MySQL

Although introducing a level of complexity, now you have two levels of redundancy: DRBD within each site and MySQL Circular Replication between sites. You have the additional benefits of running backups via mysqldump on the DRBD Primary of the hot standby server.

As for failover, DRBD provides automatic failover at any one site.

Only in the event that a datacenter is totally unavailble would you using the DB VIP at the hot standby site.

UPDATE

I just did a double take and noticed that you are using Drupal6. I am glad you will be converting all the drupal tables to InnoDB. This will remove any chance of MyISAM table updates causing table locks to freeze DB Connections that are simply reading MyISAM tables. Any DML update (INSERTs, UPDATEs, DELETEs) against a MyISAM table WILL ALWAYS DO A FULL TABLE LOCK !!! Using InnoDB will introduce row-level locking, which eliminates full table locks.

In addition, DRBD becomes your friend when everything is InnoDB because crash recovery will be consistent between the DRBD Pair. Contrawise, DRBD with MyISAM buys you nothing because a crashed MyISAM table on the DRBD Primary is simply duplicated to the DRBD Secondary as, you guessed it, a crashed MyISAM table.

UPDATE #2

You should use two levels of redundancy

Level 1 : At each database center, use DRBD.
http://dev.mysql.com/doc/refman/5.1/en/ha-drbd.html

Set up a pair of DB Servers
Startup DRBD
Startup MySQL on the DRBD Primary

This creates redundant data at the disk level.

Level 2 : You should setup MySQL Circular Replication between
the DRBD Primary of DataCenter#1 and the DRBD Primary of DataCenter#2

Each DRBD Primary will be running MySQL and will act
as both Master and Slave to Each Other

I have setup for clients topologies like this and I consider it quite stable.


Why can't an owner create a view? There is a user as HRS. I am creating a view in HRS as

CREATE VIEW HRS.RNS.....

I am getting

  ORA-01031: insufficient privileges

Edit

As i remember, i only execute following commands for the user and then my user can create tables

GRANT UNLIMITED TABLESPACE TO THE_USER;
GRANT CONNECT, RESOURCE TO THE_USER;
GRANT CREATE SESSION TO THE_USER;

How does my user automatically get rights to create table but not view?

You can create tables because the RESOURCE role has the CREATE TABLE privilege granted. To create a view, you need to have granted the CREATE VIEW privilege additionally, because, by default that privilege is not granted, not even to RESOURCE.

SQL> select privilege from dba_sys_privs where grantee='RESOURCE';

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

So you need to do the below:

grant create view to hrs;


save 14 columns in one table or different table? We are giving option to customer to enter his name, email, telephone like this in 14 textfields he will enter information. for all customers we wil create userid as one column in table.

so in mysql db, is it better to store all these information in one table or in different tables.

if we store in one table , later can we save same information in different tables and viceversa ?

Two tables that are in 1:1 relationship should, in almost all cases, be combined into a single table.  Here are some exceptions of where it may be beneficial to have multiple, parallel, vertically partitioned, tables.  Note: you probably do not have any of these cases.

MyISAM table with lots of rows and one or more big TEXT or BLOB.  The layout of MyISAM leads to clumsy access for queries that don't need the bulky columns.  (This does not apply for InnoDB.)  Even so, there is a workaround ("lazy eval").
You have a very rapidly changing column, such as "Likes".  Separate it into a separate table (plus an id for JOINing) so that the frequent UPDATEs are not competing with the more mundane operations.
Optional columns.  If many of the rows do not have a certain column(s), move them to another table and use LEFT JOIN to reconstruct.  This is almost not worth mentioning, since NULL is likely to be better for an optional column.
A new column being added to a huge table.  If you already have a table with millions of rows, and it is very actively used, and you don't want the downtime to add a new column, put it in another table.