Native replication from Percona XtraDB Cluster to a standard stand-alone slave

The binlog doesn't contain different structures when the storage engine is XtraDB vs InnoDB.

In the sense of interoperability, XtraDB is not an "extension" of InnoDB -- it's a fully-compatible replacement, that handles some internal operations differently but is still very much "InnoDB" wherever it is exposed outside the server core.

For what it's worth,  I've taken "datadir" -- including ibdata1 and the ibd files from a stopped MySQL server, put them on a MariaDB server (which also uses XtraDB instead if InnoDB) and had zero compatibility issues.

The only caveat that comes to mind is the same as any MySQL async replication setup: the slave must be running the same or newer version of MySQL as its master (which, by the way, can be any one of thr PXC machines).

The reason for this "slave version the same or newer" rule is that the binlog format is forward-extensible only: when new capabilities are added to the binlog format, a newer master cam break an older slave, since the slave may not always understand what it is reading from the binlog, and replication will hit a hard stop (not a soft fail).  A newer slave should never fail to understand what an older master writes to its binlog, since, officially, replication is supported across major releases (e.g., 5.1 to 5.5) but in practice, 5.1 to 5.6 also seems completely compatible.

The parallel minor version or newer, of MySQL Server, MariaDB, or -- seemingly the most foolproof -- Percona Server... should work fine as an async slave.

If you start out the slave with Identical data, and you replicate every table and schema, missing transactions should never happen. Don't even try to filter what replicates and try to replicate a subserlt of your schemata/tables unless and until you really understand the issues this can encounter.  Replicating everything is the default behavior.

Yes... it is very much worth it to set up async slaves for reporting.  The cluster members should already have the config they need for this to work (othet than the user account the slave will use to connect).


TLDR: Final query with comments is at the end, but the following answer goes through the steps of building it out.
So what you likely want is a recursive CTE (I'm not sure STUFF can help you with this, at least not easily).
You should have an Item table that is a distinct list of all your assemblies and component item IDs, similar to this example:

If I recall correctly from your last question, you have a BOM table that should have a similar structure as this example (with other fields too I'm sure):

A recursive CTE will help you flatten this so all child Components at all levels roll up with the main end item (e.g. Component 2003 rolls up with 2001 into the end item parent 1000).
Here's an example of a recursive CTE:
WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId
FROM CTE

In my example dataset, this produces the following output (every level of every component rolled back up to the original end item):

Notice end item "1000" on rows 16 through 18. Rows 16 and 17 are "level 1" and row 18 is "level 2" because the component "2004" on row 17 has a component below it, therefor it's the parent on row 18 to component "2005".
Now let's bring in the Component Price column so we can later group by End Item and sum up the total cost like so:
WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId, ComponentPrice
FROM CTE

Finally, if you add a GROUP BY on the End Item column and add a SUM(ComponentPrice) column then you can get the total exploded BOM price of the End Item like so:
WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice
FROM CTE
GROUP BY EndItemId

End Items' Total Prices:

TLDR:
Your final step would be to join these rolled up End Item prices to your Sales Line table by ItemId to EndItemId like so:
WITH CTE AS -- This is our recursive CTE
(
    SELECT I.ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- These are the fields you'll need for recursion and final grouping later on
    FROM #Item AS I -- Items is your starting point in the recursion (i.e. this defines your base case)
    INNER JOIN #BOM AS B -- Join to the BOMs table to get the child Components
        ON I.ItemId = B.ParentItemId -- Gets all BOM lines with this parent Item

    UNION ALL -- This is how we start to call the CTE recursively

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- Must select the same fields as before for the UNION ALL
    FROM CTE AS C -- This is our recursive case, a CTE referencing itself
    INNER JOIN #BOM AS B -- Joining to the BOM table again to get all child components but notice the difference in join clause
        ON C.ComponentItemId = B.ParentItemId -- This time the previous call's child becomes the parent (i.e. we're joining the BOM table back in where the child component item is the parent item in the BOM table)
),
RolledUpCte AS -- This rolls up the results of the previous recursive CTE by our end assembly item
(
    SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice -- Summing up the price of all the components on the end item
    FROM CTE
    GROUP BY EndItemId -- Group by end item
)

SELECT SL.SalesLineId, SL.ItemId, RUC.EndItemTotalPrice -- Final select of the fields we need from our Sales Line table and our rolled up prices of our end items
FROM RolledUpCte AS RUC
INNER JOIN #SalesLine AS SL
    ON RUC.EndItemId = SL.ItemId

Sales Lines w/ their summed up component prices:

Your table schema will obviously vary from my example, but this is the concept and example code of what you'd need to do. Instead of ItemIds it looks like you have StockCodes (or whatever your item's identifier is called in your Item and BOM tables).
The only other important thing to be aware of is if you have a BOM that is more than 2 levels deep (e.g. Parent -> Child -> Grandchild -> Great Grandchild) then you'll only want to sum up the lowest level component items otherwise you end up double counting. E.g. if Item A has component B, and component B has components C and D on it, then you want to only sum the prices of C and D for A. If you sum it all then you'll end up double counting by adding B + C + D for A's price. In my past experience we had a column called ItemType that identified which items are the lowest level component items so that when we did the roll up we could add a WHERE ItemType = 'LowestLevelComponent' but it sounds like in your case, all items that have a child don't have a price so then you probably don't have to worry about double counting.
Finding the total quantity of the lowest level components is the same idea too, but instead you'll need a column in the recursive CTE for the ParentQuantity and another one that multiplies the ParentQuantity by the Child's quantity (in the recursive part of the query) like so:
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, B.ComponentQuantity AS ComponentQuantityMultiplied
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, (B.ComponentQuantity * C.ComponentQuantityMultiplied) AS ComponentQuantityMultiplied
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId

If you have any questions feel free to let me know and I'll explain as best as I can. If this feels like information overload, maybe just start with the TLDR final full query above and just read the comments to try to understand what it does.


How to get SQLCMD to output errors and warnings only?

By default, SQLCMD sends all non-error messages and error messages to stdout. Hence, attempting to redirect output won't help.

The first thing you need to do in order to get only one or the other type of messages (error or non-error) is to tell SQLCMD to separate them by sending the error messages (severity level 11 or higher) to stderr. You do this by using the -r command-line switch, specifying 0 as the option for that switch (i.e. -r0). At this point there is no visible difference in terms of running SQLCMD and seeing messages of any type displayed on the screen.

The next part is to filter out the regular messages going to stdout.  This can be done by redirecting the stdout messages to somewhere via >, but where to? You could do > file.txt, but I doubt you want a file of the messages that you didn't want to begin with. Fortunately, DOS has the NUL keyword (that is not a typeo: it has one L, not two) that works like /dev/null in Unix. Meaning you can use the following to redirect output to nowhere: > NUL.

The following will execute the PRINT command and show no output as no errors are generated, and no file is created that would contain the output of the PRINT command:

CD %TEMP%

SQLCMD -E -Q "print 1;" -r0 > NUL

But the following displays an error message as those are not being redirected to NUL:

CD %TEMP%

SQLCMD -E -Q "print a;" -r0 > NUL

Returns:

Msg 128, Level 15, State 1, Server DALI, Line 1
The name "a" is not permitted in this context. Valid expressions are constants,
constant expressions, and (in some contexts) variables. Column names are not permitted.


SQL - Return most recent batch of sequential rows

WITH
cte1 AS
(
SELECT series,
       id,
       `date`,
       DATEDIFF(LEAD(STR_TO_DATE(`date`, '%Y%m%d')) OVER (PARTITION BY series
                                                          ORDER BY `date`),
                STR_TO_DATE(`date`, '%Y%m%d')) diff
FROM test
),
cte2 AS
(
SELECT series,
       id,
       `date`,
       COALESCE(SUM(diff > 40) OVER (PARTITION BY series
                                     ORDER BY `date` DESC), 0) grp
FROM cte1
)
SELECT series,
       id,
       `date`
FROM cte2
WHERE grp = 0
ORDER BY 1,3

fiddle


Building a branched versioning model for relational databases

IMO this problem lacks sufficient generality to build standards or reusable solutions.  It's really just a problem you solve with data modeling,

Something along the lines of:

CREATE TABLE DOC
(
  DOC_ID INT PRIMARY KEY,
  CURRENT_VERSION_ID INT,
  MODIFIED_AT DATETIME,
  MODIFIED_BY INT,
  CONSTRAINT FK_CURRENT_VERSION
    FOREIGN KEY (DOC_ID, CURRENT_VERSION)
    REFERENCES DOC_VERSION(DOC_ID,VERSION_ID)

)

CREATE TABLE DOC_VERSION
(
  DOC_ID INT REFERENCES DOC,
  VERSION_ID INT,
  VERSION_OWNER INT,
  SOURCE_VERSION_ID INT,
  DOC_CONTENT NVARCHAR(MAX),
  CONSTRAINT PK_DOC_VERSION PRIMARY KEY (DOC_ID,VERSION_ID)
)

If the content being versioned is not stored as a blob, but is in separate tables, the pattern still holds.  All the tables that store the versioned data need the VERSION_ID.

It would certainly be an interesting research project to explore all the different ways this can be done, and discuss options for conflict resolution, merging changes and sketch out what a general solution might look like.


Query databases that have MYISAM tables that do not have only fulltext index tables

MyISAM tables with FULLTEXT index and other index(es).

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM  information_schema.statistics AS s1
    JOIN  information_schema.statistics AS s2  USING (TABLE_SCHEMA, TABLE_NAME)
    JOIN  information_schema.TABLES AS t       USING (TABLE_SCHEMA, TABLE_NAME)
    WHERE  s1.index_type = 'FULLTEXT'
      AND  s2.index_type != 'FULLTEXT'
      AND  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA  NOT IN ('information_schema', 'mysql', 'performance_schema');

# MyISAM tables with no FT index:

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM  information_schema.TABLES AS t
    WHERE  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA NOT IN ('information_schema', 'mysql',
                'performance_schema'
                          )
      AND  NOT EXISTS
      ( SELECT  *
            FROM  information_schema.statistics
            WHERE  index_type = 'FULLTEXT'
              AND  TABLE_SCHEMA = t.TABLE_SCHEMA
              AND  TABLE_NAME = t.TABLE_NAME
      );

# MyISAM tables, counting index_types:

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME,
        SUM(index_type = 'FULLTEXT') AS FT_indexes,
        SUM(index_type != 'FULLTEXT') AS others
    FROM  information_schema.TABLES AS t
    JOIN  information_schema.statistics USING(TABLE_SCHEMA, TABLE_NAME)
    WHERE  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA NOT IN ('information_schema', 'mysql',
                'performance_schema'
                          )
    GROUP BY  1,2;

Tack on a HAVING FT_indexes = 0 to get just those with no FT index.

Etc.


Generate backwards compatible CREATE DATABASE script with all objects?

You're using the correct method, you're just not setting all the right options.

Go through the Tasks > Generate scripts wizard again, there are steps in there where you can choose the all object types, and other places where you can choose what properties of those objects to include (permissions, indexes, etc).

There's also a 'Script for Server Version' option, which allows you to choose which version of SQL you're scripting for.

If your database is small enough, you can even include data in that script. If not, you'll have to create the empty database container, and then using SSIS or something to pipe all the data over.

One more option: you can create an SSIS package and use the "Transfer SQL Server Objects Task" type to do the same thing, it should have all the same options:


Postgres constraint function on a single column

CHECK constraints should always evaluate to the same result. If your function is declared IMMUTABLE it's a perfect candidate, else there is more to discuss here. A NOT VALID constraint might be an option. Consider:

Disable all constraints and table checks while restoring a dump

However, a CHECK constraint is run unconditionally. If you want to run the check only if a specific column has been changed, you need a different tool. One obvious candidate would be a trigger, like @a_vlad commented:

CREATE TRIGGER employee_upbef
BEFORE UPDATE OF pin ON public.employee
FOR EACH ROW EXECUTE PROCEDURE fn_pin_is_unique_in_company();

In Postgres 9.1 or later you can restrict the trigger to the change of specific columns.
You can't pass parameters per row to a trigger function, though. Instead reference the special row variable NEW inside the trigger function ...

Related example:

Non-overlapping rectangles constrained to a boundary

However, What you are trying to do sounds like a case for a multicolumn UNIQUE constraint. There is not enough information to tell.


I think you problem is in the parameters file of standby database, may be you have defined something as:

LOG_FILE_NAME_CONVERT='/u03/app/oracle/oradata/TEST1','/u03/app/oracle/oradata/TEST1_SDY'

This parameter change the location on dbfiles for standby version. Here a link of documentation:

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm#REFRN10098

If you remove this line in parameter file of standby database and restart should be work, with the same control file standby version created before.


Create Linked Server using Active Directory account

The two most common scenarios for security via a linked-server are:

Create a SQL login on the target SQL Server, and use that login in the definition of the linked server.  You then GRANT and DENY access to that login on the target server objects as necessary, using the principle of least privilege.

Define the SQL Server to allow "pass through" authentication by configuring security to "be made using the login's current security context".   This allows security to vary amongst users and is far more flexible.  From MSDN:

Instead of having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows security credentials (Windows login name and password) of a user issuing the query to connect to a linked server when all the following conditions exist:

A user is connected to SQL Server by using Windows Authentication Mode.

Security account delegation is available on the client and sending server.

The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.

In order to use option #2 above, in a multi-hop authentication scenario, you'll need to enable several things to support Kerberos Authentication.  MSDN has a great blog on the requirements, available here
You may also want to look at this question.


How to rearrange columns in Oracle SQL Developer script output

This is not an Oracle or SQL Developer problem.

The problem is the font you use. It is variable-width and there is just no way to properly align output with that. Use a fixed-width (monospaced) font and alignment just works out of the box.

https://en.wikipedia.org/wiki/List_of_monospaced_typefaces


100x less insert performance with Galera multi-master

I've hit the same issue.
The first thing to do is to check the following variables:

select @@innodb_flush_log_at_trx_commit;

select @@sync_binlog;

Setting innodb_flush_log_at_trx_commit to 2 and sync_binlog to 0 will increase the performance dramatically.

The reason is in certification mechanism and the nature of multi master behaviour. Inserts will be very slow because in order to commit the transaction cluster will ensure that all logs are flushed at the time of commit at every node.

Please be sure that your caches fit RAM with the following query as well:

select ((@@thread_stack +@@binlog_cache_size +@@read_buffer_size + @@read_rnd_buffer_size +@@sort_buffer_size +@@join_buffer_size + @@global.net_buffer_length +@@global.query_prealloc_size + @@binlog_stmt_cache_size) * @@max_connections + (@@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size))/(1024*1024);


How do you grant permissions to a MySQL user using a shell script with variables?

I did the following. I wrote a script (testfile) as below and it worked. The big difference AFAICS is that with my script I export my variables before trying to use them. BTW, I wouldn't call it $USER - I'd go with $MY_USER - there's already a Linux system variable called $USER. I also didn't have these lines

#!/usr/bin/env bash
# ...

I just chmod'ed the file - $> chmod 755 testfile and ran it thus ./testfile.

[pol@localhost inst]$ more testfile
export MY_USER=puser
export MY_PASS=ppass
export MY_HOST=localhost
export MY_DB=ptest
echo "CREATE USER '$MY_USER'@'$MY_HOST' IDENTIFIED BY '$MY_PASS'" | ./bin/mysql -S ./mysql.sock -u root -pdba
echo "CREATE DATABASE $MY_DB" | ./bin/mysql -S ./mysql.sock -uroot -pdba
echo "GRANT ALL ON $MY_DB.* TO '$MY_USER'@'$MY_HOST'" | ./bin/mysql -S ./mysql.sock -u root -pdba
[pol@localhost inst]$

I have since added the line (to test the running of an sql script file following the user and database creation - with grants).

./bin/mysql -S ./mysql.sock -u $MY_USER -p$MY_PASS $MY_DB < test.sql

to the end of testfile. This has the advantage of not using root, but rather the user you have just created. It is always good practice to run commands as root as rarely as possible. Keeping the root password in a script file is rather insecure. You can keep it in your .bashrc - check out the MySQL environment variables here, in this case $MYSQL_PWD.

[EDIT] - here is an alternative method. The -e stands for execute (mysql --help) - and is rather more conventional than the echo construct that you used.

./bin/mysql -S ./mysql.sock -u root -pdba -e "CREATE USER '$MY_USER'@'$MY_HOST' IDENTIFIED BY '$MY_PASS'"
./bin/mysql -S ./mysql.sock -u root -pdba -e "CREATE DATABASE $MY_DB"
./bin/mysql -S ./mysql.sock -u root -pdba -e "GRANT ALL ON $MY_DB.* TO '$MY_USER'@'$MY_HOST'"
./bin/mysql -S ./mysql.sock -D $MY_DB -u $MY_USER -p$MY_PASS -e "SOURCE test.sql"

(This worked on 5.6 - the previous example worked on a milestone release).

You can see the result below. Don't worry about the ./bin/mysql, nor the -S ./mysql.sock - I have a non-standard source install. For a normal install, just use mysql.

You can get rid of the annoying "password ... insecure" warnings by issuing the command this way ./testfile &>/dev/null

[pol@localhost inst]$ ./testfile
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[pol@localhost inst]$
[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u puser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.7.7-rc-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ptest              |
+--------------------+
2 rows in set (0.00 sec)

mysql> exit;
Bye
[pol@localhost inst]$


-- Please try this query

create table #tablea (id int, fieldA char(1), fieldB char(1), fieldc int)
insert into #tablea values
(1,'A','X', 2)
,(2,'A','Y',3)
,(3,'B','X',3)
,(4,'B','X',2)
,(5,'C','X',2)

select t1.*
from #tablea t1
inner join (
    select fieldA, fieldB, max(fieldc) fieldc
    from #tablea
    group by fieldA, fieldB
)t2 on t1.fieldA = t2.fieldA
and t1.fieldB = t2.fieldB
and t1.fieldc = t2.fieldc
Order by Id


SQL Query to select rows based on specific condition

It is possible to solve by adding one more copy of a table for to check there is no 'Play' activity after.

But it's better to use a subquery for to select last 'Play' activity, and get all records after it:

SELECT t1.*
FROM `table` t1, (SELECT id, MAX(`timestamp`) `timestamp`
                  FROM `table`
                  WHERE activity = 'Play'
                  GROUP BY id) t2
WHERE t1.id = t2.id
  AND t1.`timestamp` > t2.`timestamp`


Unable to drop the table since its showing waiting for meta data lock

If an InnoDB table is being accessed at all via SELECT or DML (INSERT, UPDATE, DELETE), you should rightly expect a metadata lock.
According to the MySQL Documentation on MetaData Locking:

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

That being said, you should determine whether or not another DB Session is holding locks on the table. If such a session is an uncompleted transaction, there is where the hold up may be.
If you simply need the diskspace back quickly, you could run TRUNCATE TABLE.
SUGGESTION
Try renaming the table before attempting to drop it as following:
ALTER TABLE mydb.mytable RENAME mydb.mytabletodrop;
TRUNCATE TABLE mydb.mytabletodrop;
DROP TABLE mydb.mytabletodrop;


How to make UUID() function, statement-based replication safe?

Not sure I understand the question, but if you select the uuid from the parent, Whats the problem?

Insert into t (c1, c2,..., cn)
Select 36, 'abc', c3,... FROM parent


Script to estimate row sizes for any table

Script selected as an asnwer has some drawbacks:

Does not take into account some SQL Server datatypes:

image, text, uniqueidentifier, sql_variant, ntext, hierarchyid, geometry, geography, xml, sysname

and (max) data structures structures such as varchar(max) to name a few.

Takes into account computed columns which should not affect record's size.
Does not take into account the fact table has NULLable columns.
Does not take into account the fact that some datatypes have different size depending on specified scale
(for example time(0) and time(7) should have different column length)

The following script shows the size of the record as it is on a data page.
It shows maximum size that may be taken by the record, minimum size
(in case one all values in the record are NULL if permitted by table structure).
It shows table type (CLUSTERED/NONCLUSTERED), total number of data columns and schema where table belongs.

Note this script does not take into account indexes.

Update: Script counts each bit column as 1/8th of a byte. Minimum and maximum sizes are adjusted to ensure that records cannot be smaller than the forwarding stub size.

  SELECT
     -- record cannot be smaller than the forwarding stub size =9 Bytes
     CASE WHEN [Max Size]>=9
     THEN [Max Size]
     ELSE 9
     END AS [Max Size]
     -- record cannot be smaller than the forwarding stub size =9 Bytes
    , CASE WHEN [Min Size]>=9
     THEN [Min Size]
     ELSE 9
     END AS [Min Size]
    , [Table Name]
    , [Table Type]
    , [Total Number of Columns]
    , [Schema]
    FROM
    (
SELECT
    DISTINCT
    -- Overhead for row header of a data row
    4
    +
    -- Overhead for NULL bitmap
    2+cast(([Total Number of Columns]+7)/8 AS BIGINT)+
    -- overhead for variable length
    CASE WHEN [IsVariableLength]>0
    THEN
    2
    ELSE
    0
    END
    +
    --- Sum is on record level
    SUM(
    a1.[max_length]
    +
    -- Overhead for variable-length columns
    CASE WHEN
    -- varchar
    [System Type]='varchar'
    --(([system_type_id]=167) AND ([user_type_id]=167))
    OR
    -- nvarchar
    [System Type]='nvarchar'
    --(([system_type_id]=231) AND ([user_type_id]=231))
    OR
    -- IMAGE
    (([system_type_id]=34) OR ([user_type_id]=34))
    OR
    -- TEXT
    (([system_type_id]=35) OR ([user_type_id]=35))
    OR
    --  NTEXT
    (([system_type_id]=99) OR ([user_type_id]=99))
    OR
    --  SQLVARIANT
    (([system_type_id]=98) OR ([user_type_id]=98))
    OR
    -- hierarchyid geometry geography
    (([system_type_id]=240))
    THEN 2
    ELSE 0
    END
    )
    OVER (PARTITION BY a1.[Schema], a1.[Table Name]) AS [Max Size]

    , -- Overhead for row header of a data row
    4
    +
    -- Overhead for NULL bitmap
    2+cast(([Total Number of Columns]+7)/8 AS BIGINT)+
    -- overhead for variable length
    CASE WHEN ([IsVariableLength]>0) AND ([AnyFixedColumn]=0)
    THEN
    2
    ELSE
    0
    END
    +
    --- Sum is on record level
    SUM(
    -- overhead for variable length depending on number of variable columns
    CASE WHEN
    -- varchar
    --[System Type]='varchar'
    (([system_type_id]=167) OR ([user_type_id]=167))
    OR
    -- nvarchar
    --[System Type]='nvarchar'
    (([system_type_id]=231) OR ([user_type_id]=231))
    OR
    -- IMAGE
    (([system_type_id]=34) OR ([user_type_id]=34))
    OR
    -- TEXT
    (([system_type_id]=35) OR ([user_type_id]=35))
    OR
    --  NTEXT
    (([system_type_id]=99) OR ([user_type_id]=99))
    --  VARBINARY
    OR
    (([system_type_id]=165) OR ([user_type_id]=165))
    OR
    --  SQLVARIANT
    (([system_type_id]=98) OR ([user_type_id]=98))
    OR
    -- hierarchyid geometry geography
    (([system_type_id]=240))
    OR
    -- xml
    (([system_type_id]=241))
    THEN
        CASE WHEN [Is Nullable]=1
        THEN 0
        ELSE
        1
        END
    ELSE
        CASE
        WHEN
        -- bit
        (([system_type_id]=104) OR ([user_type_id]=104))
        and [Is Nullable]=1
        THEN 0
        ELSE
        a1.[max_length]
        END
    END
    --

    )
    OVER (PARTITION BY a1.[Schema], a1.[Table Name]) AS [Min Size]
    , a1.[Table Name]
    , [Table Type]
    , [Total Number of Columns]
    , a1.[Schema]
    FROM
    -- Start a1
    (   SELECT
        (SELECT [name] FROM [sys].[schemas]
        WHERE [sys].[schemas].[schema_id]=[sys].[objects].[schema_id])
        AS [Schema]
        , [sys].[objects].[name] AS [Table Name]
        , [sys].[all_columns].[name] AS [Column Name]
        , [sys].[all_columns].[system_type_id]
        , (
            SELECT name FROM [sys].[types]
            WHERE [sys].[types].[system_type_id]=[sys].[all_columns].[system_type_id]
            AND
                    [sys].[types].[user_type_id]=[sys].[all_columns].[user_type_id]
            ) AS [System Type]
        , [sys].[all_columns].[user_type_id]
        ,
        CASE
        WHEN
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        THEN 2147483647
        -- TEXT
        WHEN (([system_type_id]=35) OR ([user_type_id]=35))
        THEN 2147483647
        --  NTEXT
        WHEN (([system_type_id]=99) OR ([user_type_id]=99))
        THEN 1073741823
        -- varchar(max)
        WHEN (([system_type_id]=167) OR ([user_type_id]=167)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 2147483647
        -- nvarchar(max)
        WHEN (([system_type_id]=231) OR ([user_type_id]=231)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 1073741823
        -- varbinary(max)
        WHEN (([system_type_id]=165) OR ([user_type_id]=165)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 2147483647
        -- hierarchyid geometry geography
        WHEN (([system_type_id]=240))
        THEN 2147483647
        -- xml
        WHEN (([system_type_id]=241) AND ([sys].[all_columns].[max_length]=-1))
        THEN 2147483647
        -- bit
        WHEN (([system_type_id]=104) OR ([user_type_id]=104))
        THEN 1/8
        ELSE
        CAST([sys].[all_columns].[max_length] AS BIGINT)
        END [max_length]
        , [sys].[all_columns].[is_nullable] AS [Is Nullable]
        ,
        CASE
        WHEN EXISTS
            (
                SELECT type_desc FROM sys.indexes
                WHERE type_desc='CLUSTERED'
                AND [sys].[objects].[object_id]=[sys].[indexes].[object_id]
            )
        THEN 'CLUSTERED'
        ELSE 'HEAP'
        END AS [Table Type]
        , COUNT([sys].[all_columns].[name]) OVER (PARTITION BY [sys].[objects].[object_id]) AS [Total Number of Columns]
        ,SUM (CASE WHEN
        -- varchar
        (
        (([system_type_id]=167) AND ([user_type_id]=167))
        OR
        -- nvarchar
        (([system_type_id]=231) AND ([user_type_id]=231))
        )
        AND [sys].[all_columns].[is_nullable]=0
        THEN 1
        ELSE 0
        END) OVER (PARTITION BY [sys].[objects].[name]) AS [IsNonNullableVariableLength]
        ,SUM (
        CASE WHEN
        -- varchar
        (([system_type_id]=167) OR ([user_type_id]=167))
        OR
        -- nvarchar
        (([system_type_id]=231) OR ([user_type_id]=231))
        OR
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        OR
        -- TEXT
        (([system_type_id]=35) OR ([user_type_id]=35))
        OR
        --  NTEXT
        (([system_type_id]=99) OR ([user_type_id]=99))
        --  VARBINARY
        OR
        (([system_type_id]=165) OR ([user_type_id]=165))
        OR
        --  SQLVARIANT
        (([system_type_id]=98) OR ([user_type_id]=98))
        OR
        -- hierarchyid geometry geography
        (([system_type_id]=240))
        OR
        -- xml
        (([system_type_id]=241))
        THEN 1
        ELSE 0
        END) OVER (PARTITION BY [sys].[objects].[name])
        AS [IsVariableLength]
        ,SUM (
        CASE WHEN
        -- varchar
        (([system_type_id]=167) OR ([user_type_id]=167))
        OR
        -- nvarchar
        (([system_type_id]=231) OR ([user_type_id]=231))
        OR
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        OR
        -- TEXT
        (([system_type_id]=35) OR ([user_type_id]=35))
        OR
        --  NTEXT
        (([system_type_id]=99) OR ([user_type_id]=99))
        --  VARBINARY
        OR
        (([system_type_id]=165) OR ([user_type_id]=165))
        OR
        --  SQLVARIANT
        (([system_type_id]=98) OR ([user_type_id]=98))
        OR
        -- hierarchyid geometry geography
        (([system_type_id]=240))
        OR
        -- xml
        (([system_type_id]=241))
        THEN 0
        ELSE 1
        END) OVER (PARTITION BY [sys].[objects].[name])
        AS [AnyFixedColumn]
    FROM [sys].[objects]
    INNER JOIN sys.all_columns
    ON [sys].[objects].[object_id]=[sys].[all_columns].[object_id]
    WHERE type_desc='USER_TABLE'
    ) a1
    ) a2


Student and Course Relationship - Grade and Attendance

As the student's grade is logically tied to both the student and the course, Student_Course is the best place to put it.

For attendance, you would indeed use a query. You can't generate the attendance sheet from one table. You're starting from a professor and/or course name, and need to get data out of the Student table. I suppose you'd need to have some way to connect the professor to the students. In the query, you'd need to find a way to tie each of the tables together. I'd check the documentation for your database, and see if it mentions any way to join tables together in a query.


If I understand it right, these queries should work:

DELETE X
From BigTableX X
Left join TempTableX T on X.PostingDate = T.PostingDate
    and X.Store = T.PostingStore and X.SKU_id = T.SKU_id
Where T.Sku_id is null
AND EXISTS (Select 1 from TempTableX WHERE postingDate = X.PostingDate)
AND EXISTS (Select 1 from TempTableX WHERE PostingStore = X.Store)

DELETE X
From BigTableX X
CROSS APPLY (Select TOP(1) p = 1 from TempTableX WHERE postingDate = X.PostingDate) P
CROSS APPLY (Select TOP(1) s = 1 from TempTableX WHERE postingStore = X.Store) S
Left join TempTableX T on X.PostingDate = T.PostingDate
    and X.Store = T.PostingStore and X.SKU_id = T.SKU_id
Where T.Sku_id is null

Either of these 2 queries will delete the same rows.

On my test sample they are more efficient but my test tables don't match your data and you will have to test it.
They all do 4 table scan but the new query does not sort data (no DISTINCT).

Can you please provide both execution plans (old and new query) for further analysis.

It may help to add an index on postingDate and on postingStore or on postingDate/postingStore/sku_id. If CLUSTERED INDEX on TempTable (Date/Store/SKU_id) is not narrow enough (3 varchar...), it may be better to remove it.


User Defined Aggregate Functions without DLL

No. User-Defined Aggregates (UDAs) can only exist as SQLCLR objects. There is no T-SQL equivalent. The same holds true for User-Defined Types (UDTs).

That answer assumes the question is actually asking if UDAs require an Assembly as created via CREATE ASSEMBLY.

However, if one interprets the question as asking if a DLL file is required by the CREATE ASSEMBLY statement, then no: you can create an Assembly from either a DLL on the file system or from a VARBINARY literal or variable containing the same bytes that would be in the DLL:

CREATE ASSEMBLY [AssemblyName] FROM 0x1234A6B3......;


To enhance the performance:

First of all, add an index on calldate. It will avoid you scanning the whole table.

In the query:

Not sure what is unique in the query.
LIMIT 39780720,39790720 means, "Retrieve 39790720 rows starting from the row number 39790720 in the result". I think you want to revise it.

About the table structure:

It is recommended to have primary key


Why my indexes monitoring status change to 'no'?

One possible reason:

Bug 16311211 : REBUILDING INDEX ALTER SETTING THE MONITORING TO OFF

The bug is said to be fixed on version 12.2 and there are one-off patches for 11.2.0.3 and 11.2.0.4 as well.


Importing data from MS Access database to an existing Postgres database

I'd tend to export each table as CSV from Access, something it's quite good at. Then import it into PostgreSQL using COPY, possibly via a preprocessor like a Python script with psycopg2 if there's lots of mangled / invalid data.

If there are lots of tables, VBA (Visual Basic for Applications) or similar can be used on the Access side to automate the export, and your scripting language of choice can automate the import.

Alternately, you might be able to find an ETL tool that can connect to access and load into PostgreSQL directly. See if Pentaho Kettle, Talend Studio, CloverETL, etc can do it - possibly over ODBC if nothing else.


A DBMS is an implementation of a datamodel - a structure for how data will be represented. The relational model represents data as tables and columns, graph model as nodes and edges, document model as nested key-value pairs.
If your objective is to persist a skip list using an existing DBMS you must map its parts to the model's corresponding affordances.

If the objective is to implement this feature within a DBMS you're no longer bound by that DBMS's external model. A simple implementation would be to replace the memory pointers with the corresponding disk pointers (file_id, offset). By careful arrangement random / sequential reads can be optimised.

I remember some of the MySQL clones have implemented this. Their source is available. These would also include BTree implementations.


Records in table not showing on form Access 2007

If you import data and it seemingly disappears, you need to check the indexes on that table. You should do the following:

Switch the table into Design View
In the Table Tools Section, under the Design Tab, click on Indexes
You will see a list of Indexes pop up

If any of the Indexes have one or more columns that are unique, those rows may have vanished upon import. You may need to either remove all indexes (do not touch the PrimaryKey) or Change the Unique Property on all Indexes from Yes to No.

Then, just reimport the data.

If all 235 rows are in the table already, go to your form's Property sheet. Click the Data Tab. Look at the Record Source and see if it is a table name or query.

You need to also go back to the Property Sheet, Click the Format Tab. Look over the Default View and set it to Single Form. This will view the table rows one row at a time. Click the Last Record Navigation Button. It had better say 235 of 235.


I used HDTune to test the disks and they had an abysmal 10MB/s read speed. Diagnostics show a faulty RAID controller.


transaction log in RAM or physical file?

You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been hardened to disk. Specifically, once it has been hardened to the transaction log file, control can be returned.

The data, at this point, may not be hardened into the data file, it may still be in the data buffer cache, but because it has been hardened into the transaction log then database recovery, in the event of a failure, can recover this transaction and persist the changes safely.

There is a log buffer cache in memory used to reduce the performance impacts of the sequential writes to transaction logs. The buffer is flushed to disk on several conditions, but one of them is a transaction commit. Until this data has been hardened, control is not returned to the caller, so even if you have a failure during this buffer flush the transactional consistency is maintained because this transaction is not yet considered committed. You will lose the data changes in that transaction, but as it was not committed, your application would already consider those changes lost as the commit was never completed.


Relationship between two tables so their value changes together

You could create a trigger on both tables to update the other table when changes are made to the data.

I've create a sample schema to test this, and to show results:

USE tempdb;

GO
CREATE TABLE dbo.Products
(
    ProductID INT NOT NULL CONSTRAINT PK_Products PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Active BIT NOT NULL CONSTRAINT DF_Products_Active DEFAULT ((0))
);
GO
INSERT INTO dbo.Products(Active)
VALUES (0);
GO
CREATE TABLE dbo.Customers
(
    CustomerID INT NOT NULL CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , ProductID INT NOT NULL CONSTRAINT FK_Customers_ProductID FOREIGN KEY REFERENCES dbo.Products(ProductID)
    , SaidProduct BIT NOT NULL CONSTRAINT DF_Customers_SaidProduct DEFAULT ((0))
);
GO
CREATE TRIGGER dbo.CustomerTrigger ON dbo.Customers
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE dbo.Products
    SET Active = SaidProduct
    FROM dbo.Products p
        INNER JOIN inserted i ON i.ProductID = p.ProductID;
END
GO
INSERT INTO dbo.Customers(ProductID, SaidProduct)
VALUES (1, 1);
GO
SELECT *
FROM dbo.Products;

This inserts a row into the Products table.  The product is inserted with an Active status of False. A row is then inserted into the Customers table, where SaidProduct is set to True, which triggers an update on the Products table.  The result is the row in the Products table is now set to Active = True.

Trigger recursion may be an issue if you create a trigger on both tables, such that updating the Customers table would update the Products table, which might then try to update the Customers table, thereby creating a loop.  There are several ways to avoid recursion, but that is slightly beyond the scope of your question.


How can I model this problem in a graph database?

How can I model this in a graph database like Neo4j?

Modeling neo4j graphs from relational data is quite simple:

Decide your vertexes (nodes, objects) and edges (relationships).
Convert relational data to cypher, declaring all items and all relationships explicit.

Note: Mapping from relational to graph could take only selected entities from relational model, and single table rows can explode into multiple vertexes and multiple edges.

  Is this the right way to structure this data in a graph database?

Yes, it looks OK. Assuming that file, author, company, user, and image are nodes, and date is only an attribute, this

file: 11425646.pdf
  author: bob
  company: abc co
  date: 1/1/2011
  mentioned_users: [alice,sue,mike,sally]
  images: [1958.jpg,535.jpg,35735.jpg]

should convert to this

MERGE (f :File {name:'11425646.pdf', date:'1/1/2011'})
MERGE (a :Author {name:'bob'})
MERGE (c :Company {name:'abc co'})
MERGE (u1 :User {name:'alice'})
MERGE (u2 :User {name:'sue'})
MERGE (u3 :User {name:'mike'})
MERGE (u4 :User {name:'sally'})
MERGE (i1 :Image {name:'1958.jpg'})
MERGE (i2 :Image {name:'535.jpg'})
MERGE (i3 :Image {name:'35735.jpg'})
MERGE (f)-[:WRITTEN_BY]->(a)
MERGE (f)-[:FROM_COMPANY]->(c)
MERGE (f)-[:MENTIONS]->(u1)
MERGE (f)-[:MENTIONS]->(u2)
MERGE (f)-[:MENTIONS]->(u3)
MERGE (f)-[:MENTIONS]->(u4)
MERGE (f)-[:HAS_IMAGE]->(i1)
MERGE (f)-[:HAS_IMAGE]->(i2)
MERGE (f)-[:HAS_IMAGE]->(i3)

Useful links: data modeling guide and  Cypher reference


SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?

Not for any case.
But I have some way:

It is designed for only for the case in the message table sysjobhistory, appears string:

  "Execution ID: xxxxxxx".

In the following query, in the last column I extract the ExecutionId.

In the case ExecutionId has a value, it can join:

SELECT *
FROM (
    SELECT
        h.step_name,
        h.message,
        h.run_status,
        h.run_date,
        h.run_time,
        SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,PATINDEX('%[^0-9]%',SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,20))-1) ExecutionId
    FROM MSDB.DBO.SYSJOBHISTORY h) history
LEFT JOIN
SSISDB.CATALOG.EXECUTIONS ex on ex.execution_id = history.ExecutionId

Note that can also attach these tables:

LEFT JOIN SSISDB.CATALOG.OPERATION_MESSAGES om on history.ExecutionId = om.operation_id
LEFT JOIN SSISDB.CATALOG.EVENT_MESSAGES em on em.operation_id =history.ExecutionId


How do I refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?)

You might want to run sp_refreshview, it is recommended to run this if the underlying tables change at all (this case seems to be a good candidate for that case).

I found the underlying cause. The parse trees of views are generated and cached:

select * from sys.syscacheobjects
where objtype = 'View'

This is the output of the tokenizer, so an attach detach should invalidate cached tokens. An sp_refreshview rebuilds the parse tree.

From the documentation for sp_refreshview:

  Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.


(Ola Hallengren) Deleting log backups older than last FULL backup

Part II of II (had to split my answer)

Doing it better

Considering your RPO and RTO defined by your business, you now might want to alter the parameter of the @CleanupTime to something higher than 0. Why? Because the value 0 will only work together with the built-in fail-safe mechanism.

Let's use the following timeline:

08:00 BACKUP LOG
09:00 BACKUP LOG
10:00 BACKUP LOG
...
20:00 BACKUP DATABASE
21:00 BACKUP LOG
...

At some point-in-time your Transaction Log backups (and Full backups?) are copied to a network drive and backed up from there by means of a backup solution, possibly combined with some kind of tape and/or disk storage. As soon as the first BACKUP LOG ... occurs after the last BACKUP DATABASE ... your previous BACKUP LOG ... files are gone...

Questions to ask yourself

What happens if your network backup fails?
When does this (tape) backup occur? Guaranteed?
When does the full database backup occur?
What do you want to be able to restore?
What RTO do you have?
What RPO does your business require?

Looking at the above questions consider using a different cleanup time (e.g. @CleanupTime=48) to keep additional hours worth of Transaction Log backups on your database server's disks.

Benefits

You are no longer relying on Ola's fail-safe mechanism
Your data is still on disk, even if you create a COPY_ONLY backup
Your data is still on disk, even if the network goes down and ...

... you create a BACKUP DATABASE ...
... a COPY_ONLY backup

Building a solid foundation

At any given point-in-time something will fail. You have to ensure that you can accommodate for any failures down the line and still guarantee to the stake-holders that your solution will be 99,.....% fool proof.

How I do it

Working with Ola's solution is an absolute breeze, IF you make one or two thoughts on how you want to recover a database and based on your businesses RPO and RTO.

My personal implementation is to have a the following schedules/retention policies:

Production systems

Backup TLog

Hourly @ xx:05  (non-SAP systems)
Quarter Hourly @ xx:10, xx:25, xx:40, xx:55 (SAP systems)
Retention : 48 hours

Daily Differential Backup

Monday - Saturday @ 20:00 (non-SAP systems)
Monday - Saturday @ 22:00 (SAP systems)
Retention : 168 hours

Weekly Full Backup

Sunday @ 20:00 (non-SAP systems)
Sunday @ 22:00 (SAP systems)
Retention : 336 hours

Test systems

The test system will be backed up during production hours. This can be 10:00 in the morning or at 14:00 in the afternoon for full and xx:15 for the Transaction Log backups.

Why I do it this way

...or my thoughts behind these decisions ...

By distributing the backup times to different slots, I am evenly distributing the disk I/O on the storage systems. No lumping of massive I/O on the disk at the full hours (FULL) or quarterly hours (TLOG).

I differentiate between SAP and non-SAP because of the sizes of the databases.

Test systems are allowed to thrash the storage during the day. No high performance impacts.

The DIFF and FULL backups occur before the tape backups start and are normally finished before the tape backups start.

The retention policies allow me to reach the RTO and RPO laid out by the business even if the (tape) backup solution is down for a day.

Backups will still work and be compliant with RTO and RPO even if the network (as a whole or only partially) is down for a day.

Your thought process

Your @CleanupTime setting was probably based on a wrong understanding of Ola's script.


Upgrading Greenplum DB that includes PostGIS extension

My bet is that the postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg package has a bug and it does not have implemented the postupdate as defined in the Gppkg class. The TAG is optional (SPECFILE_OPTIONAL_TAGS = ... 'postupdate'] ) so it looks like it is calling

Check if the upgrade script ($GPHOME/share/postgresql/contrib/postgis-2.0/postgis_upgrade_20_minor.sql) exists in that location. Package details.

From that specification:

PostUpdate:
10  - Master: "echo 'Please run psql -d mydatabase -f
$GPHOME/share/postgresql/contrib/postgis-2.0/postgis_upgrade_20_minor.sql
to finish the update.'"

Also, you may want to try the --clean option


check history along with dates for queries in Postgres

PostgreSQL doesn't remember the times when queries are run.
If you need that information, set log_statement = 'all' and include %m in log_line_prefix. Then the queries and their time are written to the log file.


how to select records from two columns where one column consist word from second column?

Do you need it to be case sensitive? If not try using LIKE
Create Table #Test
(
ColA Varchar(100)
,ColB Varchar(100)
)

Insert Into #Test
Values ('TEST', 'THIS IS A TEST')
,('String', 'This Also has word string in it')
,('ABC','DEF')
,('123','456')

Select      *
From        #Test
Where       ColB Like '%' + ColA + '%'


There something that I have for a long time, to transfer logins with password:
 You should first create the procedure, and then run it and just copy and paste ( or for sure you can run  sp_BlitzErik's script, so it will do it automatically, just don't forget to create a linked server ):

--EXEC sp_help_revlogin
--The output script that the sp_help_revlogin stored procedure generates is the login script.
--This login script creates the logins that have the original Security Identifier (SID) and the original password.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO


Database Mail has incorrect encoding of CSV attachment

I would suggest you to generate the attachment with powershell and then send the attachment:
$reportQuery = "select * from ##Report"
$filePath = "\\aaaa\bbbb\attach.csv"
$mailQuery = "
  EXEC msdb..sp_send_dbmail
  @profile_name = '..',
  @recipients = '..',
  @subject = '..',
  @file_attachments = '" + $filePath + "'",
    ..... (others parameters)

Invoke-Sqlcmd -query $reportQuery | export-csv $filePath -encoding "unicode"
$result = Invoke-Sqlcmd -query $mailQuery


Dropped connections, corrupted schema ... is MySQL sick?

Someone fetch me a big spoon, I've got a huge slice of humble pie to get stuck into.

I have no formal database training, but as a developer I have had to interact with databases (mainly via PHP). I've never been the person to create the schema. In debugging this, I was having a really hard time, reading many many SE posts (thanks Rolando, I've lost track of how many of your posts I've read this last week) and the MySQL manual. The trouble is that the errors were all so dang abstract - lost connection error, table doesn't exist error, etc.

So I just tried to run the schema on a machine that had never hosted that database. The server wouldn't take it, and so I knew it couldn't have been all the crazy silly things I had done with the data dictionary. SQLfiddle didn't like it either, and it was something to do with foreign keys because it balked at the tables with them (as did the server).

Turns out that it was just by luck that most of my FKs were uniquely named! I did not know you had to do that. All of the tutorials and lessons I had read never had a complicated-enough example that it became an issue. So after a quick lesson I changed the keys and presto everything works!

SQL's simple syntax and experience with other abstract "tables" of data (spreadsheets, R/Matlab) fools developers into thinking DBs are simple. Doing an enterprise-size schema by youself ought to be compulsory at least once in every developer's life. You guys deserve serious respect.


Why colname as input param of function = not good idea?

There are two questions here,

What is the difference between EXECUTE .. USING and EXECUTE FORMAT()
Why is wrapping or generating simple SQL statements in a procedural function a bad idea?

The difference between EXECUTE .. USING and EXECUTE FORMAT()
From the docs,

The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USING clause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
  INTO c
  USING checked_user, checked_date;

Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:
EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

A cleaner approach is to use format()'s %I specification for table or column names (strings separated by a newline are concatenated):
EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

An EXECUTE with a simple constant command string and some USING parameters, as in the first example above, is functionally equivalent to just writing the command directly in PL/pgSQL and allowing replacement of PL/pgSQL variables to happen automatically. The important difference is that EXECUTE will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereas PL/pgSQL may otherwise create a generic plan and cache it for re-use. In situations where the best plan depends strongly on the parameter values, it can be helpful to use EXECUTE to positively ensure that a generic plan is not selected.

So you have a few arguments here.

You can use both EXECUTE FORMAT() ... USING
USING allows the plan to be cached.
USING allows symbols to stay symbols and stops them from having to be converted to text and re-escaped.
USING can not be used with identifiers, only literals.

Wrapping and generating simple SQL statements in a procedural function is a bad idea.
As for the other part of the question,

So I'd like to know why is it not advised? (Wrapping simple SQL statements in plpgsql.)

There are a lot of reasons for that,

functions as such obscure the costs (plural) to the planner, and require the user to explicitly

set the execution cost (or use a rather silly estimate)
9.6+ establish if it is parallel-safe safe/restricted/unsafe
9.6+ establish if it has side effects strict/immutable/volatile

they obscure the internals to the user.
they prevent predicate-pushdown.
they complicate permissions (now you need access to the function too).
they raise the barrier for maintenance, now you have to define the result set the function returns TABLE (id int)
they present all kinds of problems with ORMs.

And, it's not SQL. You're building a new language on top of a DBMS. Why?
As for the dynamic component, there are other ways to engineer around the problem. Take for instance the exact statement provided, the worst case scenario is where ever you see that
SELECT * FROM getItemsOrderBy($col);

You have to explicitly write out the order-by. As bad as it, it's a better solution in my opinion.
SELECT id FROM items ORDER BY col1
SELECT id FROM items ORDER BY col2
SELECT id FROM items ORDER BY col3

A step even further would be to use a library which provides some kind of assistance for generating , like pg-promise
let args = { orderBy: "col1" };
if ( args.orderBy !== 'col1' ) {
  throw new Error "invalid orderBy Column";
}
db.manyOrNone( 'SELECT id FROM items ORDER BY ${orderBy~}', args );

Or, DBIx::Abstract, or an ORM like DBIx::Class.


Does the week day number changes according to localization in SQL Server?

The weekday number is controlled by the SET DATEFIRST setting.

http://msdn.microsoft.com/en-us/library/ms181598.aspx

This setting is changed when the SET LANGUAGE setting is changed.

SET LANGUAGE English;
GO

SELECT
  @@DATEFIRST as DateFirst,
  CAST('1999-1-1' AS datetime2) AS SelectDate,
  DATEPART(dw, '1999-1-1') AS DayOfWeek;

Returns

DATEFIRST   SELECTDATE  DAYOFWEEK
7   1999-01-01 00:00:00.0000000 6

And

SET LANGUAGE German;
GO

SELECT
  @@DATEFIRST as DateFist,
  CAST('1999-1-1' AS datetime2) AS SelectDate,
  DATEPART(dw, '1999-1-1') AS DayOfWeek;

Returns

DATEFIRST   SELECTDATE  DAYOFWEEK
1   1999-01-01 00:00:00.0000000 5

You can find the DATEFIRST setting for each language by querying
sys.syslanguages view and referencing the datefist column.

http://msdn.microsoft.com/en-us/library/ms190303.aspx


This is explained in The Read Ahead that doesn’t count as Read Ahead

  what I wanted to talk about in this post, was another prefetching
  mechanism that can be triggered by any query when SQL is running any
  of the editions considered Enterprise (i.e. Developer, Evaluation, and
  Enterprise itself). The aim of this optimization is to warm up the
  cache as quickly as possible. To do so, the buffer pool converts any
  request to read a single page from disk into a request that will read
  the whole extent containing the page initially requested.

In your case you ran this after clearing the buffer cache with DBCC DROPCLEANBUFFERS. So SQL Server is in the state that it would try and warm up the cache quickly.

You needed to read three pages to perform the seek (one at each level of the index). These were pages 1111, 1382 and 1061.

So you ended up bringing in three entire extents with page numbers 1056 - 1063, 1104 - 1111 and 1376 - 1383.

If you do the same experiment against some other SKU than mentioned in the quote (and traceflag 840 is not enabled) you should see your expected three pages (below was against Express LocalDB)


Replicating on-premise SQL Server instance with hundreds of databases to an Azure SQL Managed Instance

I shared the case scenario with the Azure SQL Database team at Microsoft via an Exchange Mailing list for Azure SQL Databases contributors/MVPs. The answer provided by one of the managers of the team is the following:  "Yes, transactional replication will be the best way at this point if they want to keep the replication running after the migration to Managed Instances."

Hope this helps.


join vs virtual table in query performance

It depends on a number of things. You list postgres in one of your tags, so it should be noted that the CTE will be materialised in full as CTEs are an optimisation fence that block predicate push-down. I'm not sure about sqlite, the other DB you list in the tags, but this is not the case for all databases, SQL Server for one can optimise across CTEs.

So for postgres currently†, all other things being equal and appropriate indexes being present, I would expect query 2 to be faster if there is enough data in assets matching asset_symbol in ('a', 'b')) for the difference to be noticeable.

In either case what indexes you have defined will also make a difference. Two things to note in particular are that marking raw_collection.asset_id as a foreign key does not  automatically create an index for that column which might limit the query planner's choices, and if you don't have one already then an index covering assets.asset_symbol is likely to be useful unless that table has very few rows.

† Note: this is due to change in version 12, which is slated for release soon, though as you state you are using v11 this is not relevant to you yet


why do SSRS reports run longer for a particular user

Converted to answer, per OP.

Check the SET settings for her session (sys.dm_exec_sessions). I wonder if she has different SET ARITHABORT or other settings compared to the other users, in which case she might be getting a different plan for the same underlying query.


At first glance, I see lots of inconsistent naming--Person_ID, but PasswordHash (or worse, SchoolYear_ID). Pick a naming convention, and stick with it. I promise you that your future self with thank you!

On your Classes table, you have a Student_ID column. As far as school goes, many students take one class, so class should have a 1:n relationship with students, not a 1:1 relationship. I'd make a Class_Registration table that contains a class id and a student id.

Also, unless your score can be different that what it ought to be based on your performance (say, a professor giving you a boost), you shouldn't have a StudentScores table. That should be derived from the number of questions you answered correctly.

I don't see any worrisome circular references, so I think you're good on that front.


After moving a DB to a new disk, the log file isn't dumping data to the DB

However, two days later, the .mdf file is the same size as
  pre-transfer (I took it offline and only the timestamp changed).

There was enough free space in your data file for any new data that was written. When that spare/free space is consumed the file will grow, not before. You can confirm the size and free space in your data file with:

SELECT
    [File Name] = name
    , [Physical Name] = physical_name
    , [Total Size in MB] = size/128.0
    , [Available Space In MB] = size/128.0
        - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0
FROM
    sys.database_files;

  I checked the log, which is usually under 1GB, and found that it has
  now reached 30GB.

Your database is in full recovery mode and you aren't backing up the log. For this and all manner of transaction log growth explanations, see @MikeWalsh's canonical answer on the topic at Why Does the Transaction Log Keep Growing or Run Out of Space?


-- This seems to be close to I think what you are needing
-- shows dbs not in a maint plan (run pointing to msdb)
select
t1.name as 'db_name',
--t2.database_name,
t1.crdate as 'date_created'
FROM [master].[dbo].[sysdatabases] as t1
left outer join
dbo.sysdbmaintplan_databases as t2
on t1.name = t2.database_name
where t2.database_name is null
and t1.name not in ('tempdb','msdb','model','master')
order by crdate desc


Do stored procedures have a "signature", and how is that defined?

Your 'tool', whatever it is, is not worth using. It does not follow the TDS protocol. Basically what happens it gets back a result from the server and stops short when parsing it, assuming the first entry is also the last entry. Whatever this tool does, I'm sure there is a decent replacement out there.

As to answer your question: the two procedures have different results, they are not identical from outside. The second incarnation creates additional 'results', in the form of the 'xxx rows affected' messages from the SELECT ... INTO... statement. This breaks your tool. NHibernate is notorious for similar problems, it breaks if the 'xx rows affected' is missing after an update (ie. if you do the update using a stored procedure that has SET NOCOUNT ON).

Pass along to the tool developers that they need to read the ODBC specifications about Processing Results:

  Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification. This count is made available when application calls SQLRowCount. ODBC 3.x applications must either call SQLRowCount to retrieve the result set or SQLMoreResults to cancel it. When an application executes a batch or stored procedure containing multiple INSERT, UPDATE, or DELETE statements, the result set from each modification statement must be processed using SQLRowCount or cancelled using SQLMoreResults. These counts can be cancelled by including a SET NOCOUNT ON statement in the batch or stored procedure.


One problem with using a boolean column for each tag is that you would be limited to two tags only.  If you ever want to support tags for breaststroke, butterfly, sidestroke, doggy style, … those would all require schema changes.  You would also have to alter your queries to support those columns.  As a result, your application code won't support tags in general; each tag will feel like a new feature that you have to add code for.

(MySQL has no true boolean type.  The closest approximation would be numeric types TINYINT(1) or BIT(1).)


If you are only running one mysqld instance and want to physically store databases on different disks, here is an approach:

Let's say you

datadir is /data
Two databases

database1
database2

you have a Disk Volume mounted on /data1
you have a Disk Volume mounted on /data2

All MYISAM

cp /data/database1/* /data1
cp /data/database2/* /data2
chown -R mysql:mysql /data1
chown -R mysql:mysql /data2
mv /data/database1 /data/database1_old
mv /data/database2 /data/database2_old
ln -s /data1 /data/database1
ln -s /data2 /data/database2
chown -R mysql:mysql /data/database1
chown -R mysql:mysql /data/database2

After this, when you run SHOW DATABASES; make sure the tables in database1 and database2 are fully accessible. If they are, you could then do

DROP TABLE database1_old;
DROP TABLE database2_old;

All InnoDB or Mixture of InnoDB/MyISAM

MYSQL_CONN="-uroot -ppassword"
MYSQLDUMP_OPTIONS="${MYSQL_CONN} --single-transaction --routines -triggers"
mysqldump ${MYSQLDUMP_OPTIONS} database1 > database1.sql
mysqldump ${MYSQLDUMP_OPTIONS} database2 > database2.sql
mysql ${MYSQL_CONN} -ANe"DROP DATABASE database1; DROP DATABASE database2;"
chown -R mysql:mysql /data1
chown -R mysql:mysql /data2
ln -s /data1 /data/database1
ln -s /data2 /data/database2
chown -R mysql:mysql /data/database1
chown -R mysql:mysql /data/database2
mysql ${MYSQL_CONN} -Ddatabase1 < database1.sql
mysql ${MYSQL_CONN} -Ddatabase2 < database2.sql

CAVEATS

Please test this on a staging server
Please make backups before doing this
Please test full accessibility (SELECT queries)
Heads up on InnoDB: This may not produce better performance for .ibd files sitting on a disk volume apart from ibdata1. I wrote about this before

Feb 06, 2012 : MySQL tables/databases into different disks
Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?


Running a simple logging program, how to diagnose spikes mongod's CPU activity?

There is no one simple answer here, it will very much depend on your usage.  CPU can be consumed in many ways.  I'll list the most common (in my experience with MongoDB) and how you can try to determine if this is the case for your spikes.

Before I do though, I will say that a CPU spike is not necessarily a problem - people get obsessed about spikes in CPU, increases in the load metric, but what really matters is the performance of your application - is it actually suffering during these spikes?  If not, then it's probably not that important - by all means monitor it and be aware in case things get worse and you do see an impact, but spikes happen on busy systems without it being a problem per se.

Common Causes (and how to narrow it down):

1 - A periodic, intensive database operation

Table scans (non-indexed queries), massive index updates, in-memory sorts and probably a few others can all tax a database process from the CPU side of things.  To determine if that is the cause here, there are a few tools that you should be using:

MMS Monitoring

This is going to come up in this list more than once, but specifically you will want to look at the number of ops being executed at a given time, btree hits, and the hardware stats (munin-node required).  These will give you an insight into the work that the database is doing.  User CPU is what you have listed above, but is there a system CPU component too?  MMS will graph them over time and let you correlate the spikes in CPU with other events in general.

Examine the mongod logs

If you know the times that you are seeing the spikes, take a look in the logs - are there any slow operations around that time? Any index builds?  If the system is being generally slowed down there might be a lot of false positives in here, so look for trigger events, common themes etc.

The excellent mtools by Thomas Rueckstiess can make this analysis significantly easier, allowing you to graph events, look at specific slices of time etc.

mongotop

This will tell you what databases and collections MongoDB is spending time on.  Now, if you only have one collection and no replication, then this will not be terribly helpful, but if you have a multitude to pick from, then it can be very effective at narrowing things down, especially if you combine it with the data from MMS to get you specific time frames to analyze.  Unlike MMS though, you have to run this in advance of the issue so that the timeframe of your suspicious spike is covered.

mongostat

This tool provides data similar to that in MMS, but at a per-second granularity (MMS Monitoring is 60 seconds), so if the spikes are short lived, this can be a valuable piece of additional data.  Similar to mongotop, it must be run manually beforehand to cover an event and then the data gathered/examined.

2 - An IO Bottleneck

Slow IO can cause CPU usage to spike.  Again, MMS Monitoring with munin-node installed will give you an idea as to the nature of the CPU usage - if IOWait is high then you will want to pay attention to the whether the traffic is read or write bound (or both) and looking at the page fault levels can also be enlightening (high levels can mean that data is not fitting into memory, or "cold" data is being paged in at that time).

Essentially, all of the same tools are in play here as in the first scenario, but this time you can add:

IOStat

IOStat deserves an essay in and of itself, so I will be brief - you will want to look at %util as a broad first measure, then look for further information to determine the source of the slowness.  The await metric can tell you if queueing is an issue, svctime can tell you if the device is being slow, high reads or writes at the time of the spikes can tell you what particular IO limit you are running into.  Again, similar to the mongostat/mongotop tools, you can run this command to a much finer granularity than you will see in MMS.

3 - Connection Related Churn

This is somewhat more rare, but a badly configured driver, or an application that gets out of control can sometimes cause a massive amount of connections to be opened/closed in a small time frame while leaving the concurrent connection level fairly constant.  You can use a combination of MMS (connection graph) and analysis of the log files (as long as you are not running with --quiet) to track this down.

There are quite a few other things to look at here, but those are where I would start, and if you master the tools listed you will be well on your way to troubleshooting those other possibilities.


Sum of two marks divided by 2 for each student

"I want the better mark from task 1 and task 2 and add the "pen paper" mark divided by 2 for each student."

Let's work backwards from the answer that you want. Suppose that you had a derived table or CTE with the following columns:

StudentName
Task1Mark
Task2Mark
PenPaperMark

With that data structure it would be fairly straightforward to calculate what you need, right? Something like this:

SELECT
  StudentName
, 0.5 * (PenPaperMark + CASE WHEN Task1Mark >= Task2Mark THEN Task1Mark ELSE Task2Mark END)
FROM
(
...
) t;

You may need to account for NULL values or do some kind of rounding, but if you have the columns that I listed before you can perform the necessary calculation. So how can you get one row per student with the column values that you need?

It looks like your source data has one row per student and per mark. You need a way to combine the multiple rows from each student into one summarized row per student. PIVOT can turn rows into columns. You can also code it as a GROUP BY query. Since you want one row per student you would group by that column. So something like this could work for a definition of t:

SELECT
StudentName
, MAX(CASE WHEN AssesmentTool = 'Task 1' THEN Mark ELSE NULL END) Task1Mark
, MAX(CASE WHEN AssesmentTool = 'Task 2' THEN Mark ELSE NULL END) Task2Mark
, MAX(CASE WHEN AssesmentTool = 'Pen Papper Test 1' THEN Mark ELSE NULL END) PenPaperMark
FROM
...
GROUP BY StudentName

All that remains is to complete the FROM clause with the appropriate tables from your data model.


You can absolutely add a 3rd node to a DR site and have it be an asynchronous replica. It'll be a multi-subnet configuration, so be sure you add the MultiSubnetFailover=True to the connection strings to get a successful connection, that is if the drivers you are using support it.

You may want to disable the 3rd node's quorum vote in your configuration. I'm assuming you already have quorum setup correctly with a fileshare witness or something else (if you don't, then get that fixed). You don't want a network glitch to take down the cluster if you also have an issue on one of the nodes at the primary site. Be sure you understand quorum and votes and what's needed to keep production up.


I want to Create a brand new slave for my MySql Percona server 5.7

Based on your comments- using Linux with a non-trivially small sized database, I will recommend you to use Percona XtraBackup ( https://www.percona.com/software/mysql-database/percona-xtrabackup ) to generate a copy of your current active database in a fast way and in an almost 100% hot way (no read or write blocking except to gather the binlog position, which normally should only take less than 2 seconds).

You can follow the steps at: https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting_up_replication.html (How to setup a slave for replication in 6 simple steps with Percona XtraBackup).

I asked those questions because the alternative is mysqldump/mydumper, but that will take more to recover for your data size (also XtraBackup didn't use to support non-linux). Here is how to do it ina hot way just in case: https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

A common mistake in both cases is thinking you need to stop writes or shutdown your master- you don't, just make sure you start replicating from the coordinates corresponding to the moment you took your backup (the end of XtraBackup run or the start of mysqldump/mydumper).

To prevent data drifts in the future consider performing regular consistency checks with tools such as pt-table-checksum or migrating to row based replication.


How to Fix log_reuse_wait_desc=LOG_BACKUP? LOG Backup not possible

Now that you've changed the recovery model on the database from full to simple and back to full again, the database is running in 'pseudo-simple' mode. Taking a database backup is required now before a log backup will do any meaningful work. See here for some further details. To summarize:

In order for a log backup to be useful for a restore, there needs to be a database backup along with log backups that cover all the LSNs since the time of that database backup.
When you change the recovery model to simple, SQL Server breaks the log backup chain. Then when you change it back to full, the database continues to act like it is in simple mode until someone creates a new log backup chain by taking a database backup. This is known as pseudo-simple mode. The reason this is implemented this way is to prevent someone from taking log backups that could never be used for recovery because they aren't part of a log backup chain that begins with a database backup.
Once a database backup is taken, a new log backup chain begins and the database begins functioning in the full recovery model. At this point your log backups should work, or at least fail for another reason with a more descriptive error.

This will be at least one reason why your log backups aren't functioning now.


MariaDB Out-of-Order Parallel Replication and OPTIMIZE TABLE

Anti-OPTIMIZE
If you are using InnoDB (which you should be using), then OPTIMIZE TABLE is almost totally useless.  Stop running that.
If you are DELETEing huge chunks of a table, and that led you to want to OPTIMIZE, then let's discuss alternatives.
Replication issues
It is possible to avoid replicating any command(s):
 SET SESSION binlog = OFF;   -- I forget the details
 OPTIMIZE TABLE ...;
 SET ...   -- restore replication

But it seems like you would want to OPTIMIZE on the Slaves, where you are doing SELECTs?
If you have a graceful way to take a Slave out of rotation, you could do a "rolling" OPTIMIZE across the slaves, one at a time.
ANALYZE
If the goal is to refresh the "statistics", then ANALYZE TABLE is much faster, hence less intrusive.
PARTITION
If "old" data is being purged, then Partitioning is much faster than DELETE, and has no need for OPTIMIZE; should we discuss that?


Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
Reference
https://docs.microsoft.com/sql/t-sql/language-elements/case-transact-sql

This is standard SQL behaviour:

A CASE expression evaluates to the first true condition.

If there is no true condition, it evaluates to the ELSE part.

If there is no true condition and no ELSE part, it evaluates to NULL.


Does this set of locks imply table lock and if so is it avoidable?

Reference:

Lock Modes

See above reference for more details.

Intent = Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

  Intent Locks

  The Database Engine uses intent locks to protect placing a shared (S)
  lock or exclusive (X) lock on a resource lower in the lock hierarchy.
  Intent locks are named intent locks because they are acquired before a
  lock at the lower level, and therefore signal intent to place locks at
  a lower level.

  Intent locks serve two purposes:

  To prevent other transactions from modifying the higher-level    resource in a way that would invalidate the lock at the lower level.
  To improve the efficiency of the Database Engine in detecting lock    conflicts at the higher level of granularity.

  For example, a shared intent lock is requested at the table level
  before shared (S) locks are requested on pages or rows within that
  table. Setting an intent lock at the table level prevents another
  transaction from subsequently acquiring an exclusive (X) lock on the
  table containing that page. Intent locks improve performance because
  the Database Engine examines intent locks only at the table level to
  determine if a transaction can safely acquire a lock on that table.
  This removes the requirement to examine every row or page lock on the
  table to determine if a transaction can lock the entire table.


When _id is present in the projection, is the index on _id used for the query?

No, there is no need (in that query) to use _id index. There is no sort nor selection what need that index. Second, index (a,b) is not needed, it takes space and extra effort to keep updated. Index (a,b,c) answers all questions when keys a and b is queried.


How to move a Daily Partition to a Monthly Partitioned Table

Community wiki answer:

  Is there a way, using the Partitioned Table, to merge the SwitchOut table that is on the filegroup Daily_2018_06_18_FG into the partition on Archive_Monthly_2018_06_01_FG?

No, you'll need to use INSERT...SELECT for row processing. MERGE/SPLIT/SWITCH DDL are partition level.


Compare timestamp type columns in SQL Server

TIMESTAMP is easily the worst naming decision Microsoft has made, at least in SQL Server. Not only does the data not have any date or time information, using the name actually violated the ANSI standard.

I asked them long ago to deprecate the keyword and always refer to this as ROWVERSION. But still the tools and metadata show and script out TIMESTAMP even when you explicitly created the table using ROWVERSION. It's horribly misleading.

In any case, there is no way to derive date/time information from this column, or to tell how much time has passed when the column information changes by n. And I'm not sure how you're going to know what recordstamp to base it off of anyway, if you're trying to compare. In an isolated scenario you can do something like this:

SELECT *, CONVERT(int, recordstamp)
  FROM dbo.Cars;

DECLARE @ts int;
SELECT @ts = MAX(convert(int, recordstamp)) FROM dbo.Cars;

UPDATE Cars SET model += 'x' WHERE id = 1;

SELECT *, CONVERT(int, recordstamp)
  FROM dbo.Cars
  WHERE recordstamp > @ts;

But how are you going to do this when it's another session updating the table? And outside of a very controlled environment, you have no idea what's going to happen if this database is backed up and restored elsewhere, fails over, or even when the instance is restarted. The documentation states that it is increasing, but doesn't explicitly state that this is forever, and also warns against using it for tracking date/time in any way.

If modification date is important to you, do it right: add a datetime2 column to the table and keep it maintained with a trigger.


You could use a simple integer column as your hourly key constraint. The unique hour for each possible time can be determined by

select floor(now()/10000)

which will return something like

2015090511

for the current date 5.9.2015 at  11.25h


Debug stored procedure that times out from year to year

If you are using parameters, then it sounds like a classic case of parameter sniffing. This article gives a better explanation that I could do right now: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
Another thing to check is, in the execution plan, the estimated number of rows vs. the actual number of rows. If you find they are mismatching then you can do an update statistics on that table (or index) which will do 2 things: update the statistics (obviously) + clear out the bad plan so that a new and hopefully good one will get created. This is the official documentation for the update statistics command: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver15
If you find this is what is causing the problem then for a long term fix take a look at what and when is causing your statistics to be out of date and why SQL Server is not updating them automatically. In my experience it's either somebody messing around with the settings and turning off the auto-update stats, or that table has grown very big and the threshold for the auto-update of stats is not reached anymore (This is a nice article about that: https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/ )


mysql - find duplicate sets of data grouped by foreign key

In a subquery, fetch all the attributes (I cringe at EAV, too).
GROUP_CONCAT(... ORDER BY ...) as foo to combine them into a single string.
In the outer query, SELECT GROUP_CONCAT(ky) ... GROUP BY foo HAVING COUNT(*) > 1 to get the duplicate kys.  (Please don't use the keyword "KEY".)

Give that a try.  If you get in trouble, show us SHOW CREATE TABLE and your failed attempt.  (Or abandon EAV.)

Some code (not quite mimicking the above prose):

SELECT `key`, CONCAT(`name`, '=', `value`) AS kv FROM tbl

should give you

key kv
87  Color=Red
87  Size=Big
...

Then...

SELECT `key`,
       GROUP_CONCAT(kv SEPARATOR '|' ORDER BY kv) AS kvs
    FROM ( -- the above query -- ) AS t1

should give you

key kvs
87  Color=Red|Size=Big|...
85  ...

Now...

SELECT kvs,
       GROUP_CONCAT(`key`) AS keys
    FROM ( -- second select -- ) AS t2
    GROUP BY kvs
    HAVING COUNT(*) > 1

should give you

kvs                           keys
Color=Red|Size=Big|Weight=6   87,85
...  (perhaps not including 96)

I suggest you debug my code the way I wrote it -- test the first query to see if it gives you the first set of output, then second, then third.


Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

There is the DO command for ad-hoc plpgsql execution.
But you cannot pass parameters or return rows directly to / from a DO command.
BTW, strictly speaking, Postgres does not have "stored procedures" before Postgres 11. Just functions providing very similar functionality. See:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?


Your example looks quite simple and does not need sp_executesql statement (as long as your query returns only one row)...

You can set your variable in your select :

Create Table #Test1 (id int,name varchar(100))
Insert Into #Test1 Values (1, 'Blue'), (2, 'Red'), (3, 'Pink'), (4, 'Orange')

Declare @Color varchar(100), @returned_id varchar(100)
Set @Color = 'Pink'

Select @returned_id  = id
from #Test1
WHERE name = @Color

if @returned_id is null
    print 'Error - no rows returned for ' + @Color
else
    print @returned_id

Drop Table #Test1


COPY TO statement in postgres outputting double backslashes

It doesn't matter how you got the string into COPY TO.  The output of COPY TO is defined on its own terms.

Read the "Text Format" section of the docs for the command you are using.

The output of COPY TO is not inside the dollar quotes strings, so the rules of those strings does not matter.

You can use with (format csv); option to COPY.  It will consider a different set of characters to be special, and will escape those special characters in a different way.  It will not double the backslashes, but might do something else you don't like instead.


Is my JOIN syntax not valid? And have I understood JOIN correctly?

The = sign represents the symmetric equal operator. It does not matter in which order the to sides are mentioned, they are still equal.

As for the LEFT and RIGHT JOIN, the table order is important here. As you stated correctly, it is:

...FROM LeftTable LEFT JOIN RightTable ON...

...FROM LeftTable RIGHT JOIN RightTable ON...

The LEFT JOIN returns all rows of the LeftTable regardless of a match, the RIGHT JOIN returns all rows of the RightTable regardless of a match.

For more information on how JOINs work, check out my article series here: http://sqlity.net/en/1146/a-join-a-day-introduction/

It is written for Microsoft SQL Server but most of the concepts are RDBMS independent.


checking the normal form of the current table

You are correct, the relation is in 2NF, 3NF, BCNF.

The reason is that the relation has two keys, A and CD.

So the relation is in BCNF (which is a property stronger than 3NF and 2NF) since each determinant of the minimal conver of R1 is a key. Here is one minimal cover:

A → C
A → D
C D → B
C D → A


I don't think these are issued by PGPool.  I think they are instigated by your application (which I am guessing is written in PHP and running over some sort of ORM).

The first entry is DEALLOCATE which means effectively to tell the server to free up memory from a prepared statement.  These look like they are issued through some PDO module.

The second and third queries look like they are ORM-related mapping queries.  Your application is probably unware that these queries are being issued by the framework it is running on, but these queries only make sense really in an ORM or similar environment.


Adding indexes to very large tables in MySQL

MySQL-8.0 has a number of ALTER TABLE variations in innodb than can be done online.

Recommend testing the table structure in a test environment using ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE; Do this one index at a time.

Other reference documentation has examples.

Alternatives include gh-ost which require more spac.e


Remove single quotes from db2 column name

As you found, you have to use '' to represent a single quote (').

You do not need to use the SPACE() function:

select replace(character_column, '''', '')
  from your_table

Note: Using SPACE() could actually create an issue as it returns VARCHAR(4000).


Why can I use named pipes to connect to SQL Server even if it's been disabled?

Check the SQL Server error log for the named pipes provider messages.  With named pipes enabled, you should see messages like those below and be able to connect both locally and remotely over named pipes:

Server local connection provider is ready to accept connection on (named pipe)
Server named pipe provider is ready to accept connection on (named pipe)

With named pipes disabled, you should see only local connection provider message(s).  You'll still be able to connect locally via named pipes (over shared memory) but not remotely via named pipes.


Best Way To Migrate Existing Production Database To New Physical Storage Structure

I am not a SQL Server DBA, I use MySQL for my jobs. But some truths are universal. :-)

  1) Should I expect any gains in performance or reliability breaking up the main data file in the manner I described?

I have observed that the benefit of any scalability optimization depends on the queries you run.

It's practically the definition of optimization that any method of optimization will improve one type of query workload as it worsens some other types of workloads.

  2) How do I make these changes on a production database server where I can schedule (at most) a 30 minute downtime window per day?

I would set up a replica server (I gather in SQL Server land these are called Subscribers). Restructure the physical tablespaces of the replica. Then it doesn't matter how long it takes, as long as it can catch up to the master after the restructure is done.

I'm assuming SQL Server replication is asynchronous and also agnostic about physical layout, as MySQL replication is. But I don't know that SQL Server supports this.

Once you're done restructuring the replica, and it catches up to be in sync with its master, you can "cut over" probably by changing DNS entries for the respective server instances. Then the downtime is minimal, and brief, regardless of the size of the databases. The downtime should include a few moments to make sure all changes from the master have "drained" and been applied fully to the replica.

The tradeoff being that you need an extra set of DB server(s), at least temporarily. This is a case where using virtual servers or cloud servers can really save you!


MySQL warnings while loading integer data into table (SOLVED)

Based on the error messages provided.  It would appear that MySQL is reading the values as an empty string '', in other words NULL which is why you are seeing the 0 values.

I would look closely at the data format in the file.  Providing a small sample of the actual data would be helpful.