Considering that the question is labeled database-design I will offer a solution without that CSV field. Even if you decide to keep it, this may offer some ideas.

Question contains all possible questions.
Answer has all possible answers (to any question)
QuestionAnswer contains all QA combinations offered, note that there must exist a default answer for "I do not know".
UserQA contains questions user answered.
Note that rule "There can be one and only one correct answer for each question" (IsCorrect = 'Y'), has to be implemented on the application level or as a stored procedure/trigger.

To get questions that a user with specific_UserId has not yet answered

select  qq.QuestionID
from Question as qq
where not exists (select 1
                    from UserQA as xx
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );

Or, better if there may be some new questions which do not have defined answers yet

select distinct qq.QuestionID
from QuestionAnswer as qq
where not exists (select 1
                    from UserQA as xx
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );


Why can't I script out CLR assemblies with SMO in Powershell?

The SqlAssembly class doesn't have a schema property - so this filter is silently removing any potential results:

$assemblies     = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema  }

I'm not sure whether you need to use the owner property in the filter instead or ignore ownership entirely.


You may be able to use the command line options documentation to review your different configuration options:

https://msdn.microsoft.com/en-us/library/ms144259.aspx

Whether they all translate exactly over to what's in the configuration file (https://msdn.microsoft.com/en-us/library/dd239405.aspx)?  That I couldn't answer.


VS 2019 to SQL 2019 deployment issue - SELECT permission was denied on the object 'server_principals'

Thanks Mustaccio, reading through that did give me an idea of where to look for the solution. Adding the SELECT permission for sys.server_principals on database master for database role "Public", seems to have resolved the issue


How to determine how much disk-space postgresql 9.1 requires as temp_space to run a query?

Knowing how much will be used is hard.  How much has been used, you there are logging options for that.

set log_temp_files=0;
set client_min_messages TO log;
select count(distinct x) from generate_series(1,10000000) x;

  LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25126.2", size 120127488.

  LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25126.1", size 140000000


Can we create multiple users in one login in SQL Server 2008?

If you are using SQL Authentication, then the short answer to your first question (do I have to create 4 logins, one for each user?) is, Yes. You do.

It sounds like you're looking to group individual users under a single entity.

If you are able to use Windows Authentication then you can create an Active Directory Group, let's call it GP-DatabaseUsers. Add the Windows logins as members of that group in AD, and in SQL add the group as a user and attribute the desired credentials to that group. You can still track individual login usage, but you'll be controlling security of a single entity.


SQLite, ASCII Characters 0x20 to 0x7E Check Constraint

In SQL, \ is not a special character.

You have to insert the characters directly:

CREATE TABLE test
(
    id INTEGER NOT NULL,
    name TEXT NOT NULL CHECK(name NOT GLOB '*[^ -~]*'),
    PRIMARY KEY(id)
)


How to determine effective permissions and how granted for a local or domain account

In the Server you can look for

exec sp_helpsrvrolemember sysadmin exec sp_helpsrvrolemember dbcreator

In the databases that you want

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

and all direct permissions

exec sp_helprotect null,null


One can attempt to shrink a log file at any time, regardless of recovery model.

The amount it shrinks will only go back to just after the last in-use virtual log file (VLF). VLFs are logical regions of a physical log file. You can use the DBCC LOGINFO command to dump a list of all the VLFs for the database-in-context. In the Status column, 0 means unused, and 2 means in-use. A shrink operation will only reclaim contiguous VLFs at the end of the physical file which have status 0.

The difference between recovery models is the mechanism that maintains the status of each VLF. In FULL recovery, VLFs can't be overwritten at least until they've been backed up in a log backup.

Just a note that switching to SIMPLE and then back to FULL usually isn't a good idea, and should only be used as a very last resort in exceptional circumstances. Doing this will break the log chain (which can be restarted with a full or differential backup) and lose the ability to recover to a point-in-time within the log records that were dropped on the floor.


Why would CREATE INDEX with "online" mode suddenly slow my database down?

The OIB is causing your DB to run over some SLO limit (most likely IO or Log IO), causing resource governor to kick in. See Azure SQL Database resource limits.


How to improve performance of SELECT query to same tables in Linked Server

The query can be rewritten with a crosstab, in order to avoid hitting TableA multiple times:

DECLARE @TableA TABLE (col1 int, col2 int);
DECLARE @TableB TABLE (col3 int, col4 int);

INSERT INTO @TableA
VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4);

INSERT INTO @TableB
VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4);

DECLARE @Var1 int, @Var2 int, @Var3 int;

SELECT     @Var1 = CA.var1,
           @Var2 = CA.var2,
           @Var3 = CA.var3
FROM @TableB AS TB
CROSS APPLY (
    SELECT
        SUM(CASE WHEN col2 = 1 THEN col1 END) AS var1,
              -- ^^ This is <Condition1>
        SUM(CASE WHEN col2 = 2 THEN col1 END) AS var2,
              -- ^^ This is <Condition2>
        SUM(CASE WHEN col2 = 3 THEN col1 END) AS var3
              -- ^^ This is <Condition3>
    FROM @TableA AS TA
) AS CA(var1, var2, var3)
WHERE TB.col3 > 2 -- <Condition4>

SELECT @Var1, @Var2, @var3

This should make it faster. If it doesn't, please post the execution plan, so that we can investigate it further.


Executing a stored procedure inside another stored procedure from a linked server

I used the function that @Scott Hodgin suggested and created the table with that information. Thanks man!

Note: if your Stored Procedure is using a Temporary table this won't work as specified in the page:

  The result could not be determined because the batch contains a
  temporary table and is not supported by sp_describe_first_result_set.


Stored procedure query plan different in Always on server

They are not identical:
on primary you have probably different cached data.

A longer time can be consequence of more time spent on disk.
Also statistics are different: you have in both database the statistics created on primary. But on secondary you also have statistics stored in tempdb.

You can go deeper comparing the execution plans.


To find matching rows, the database must first search in the index. If the WHERE clause also uses columns not in the index, the database also has to read candidate rows from the actual table to be able to check those other columns.
Therefore, you would get the largest further speedup by adding all columns from the WHERE clause to the index.
You can also create a covering index by adding all columns from the SELECT clause to the index. Then no rows have to be read from the table. This might halve the query time.
Adding columns makes the index larger, which might slow down updates, which is probably not a problem for you.


Identical subquery optimisation in an update

MySQL lacks common table expressions but you can achieve the same goal here with the multiple-table update syntax:

update activitybooking
       cross join ( select sum(pool1_count) p1, sum(pool2_count) p2, sum(pool3_count) p3
                    from `activitybooking`
                    where `abt`='12' and (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8') ) z
set `submitted`='1'
where id='958' and p1

(SQLFiddle here)


Some RDBMS (SQL Server) allow you to get this information easily. Especially for a direct DB connection from code running in the user's session on Windows, using Windows Auth to connect to SQL Server.

It can be done if the user connects to a website, which then connects to SQL Server too.

However, you're on MySQL.

Your simplest option is to have the client code send it in, derived from the OS. Ypu can then pass this in to the trigger as per this SO answer https://stackoverflow.com/questions/324605/mysql-trigger-storing-a-select-in-a-variable


With noofreplicas=2 and two nodes, you will have 2/2 = 1 nodegroup, meaning that you have no sharding at all (or one shard, as you prefer). All your partitions will be in both nodes. Your cluster will provide you high availability and better read throughup, but not write scaling.

What you show are the statistics for the table users, if you have not defined a custom partitioned schema, NDB will manage the partitioning for you using a hash on the primary key, in this case 4 partitions are created. Out of those 4 partitions, probably 2 will be "primary" on one node and "backup" in the other, and vice versa. You can define your own partitioning schema with standard MySQL Syntax (although having less partitions may reduce concurrency -MaxNoOfExecutionThreads- and future scaling -making the adding nodes more painful). You can know which partition each individual row is with EXPLAIN PARTITIONS.

Think of NDB as an implementation of RAID 10, but as you only have 2 nodes, you only get a RAID1. Your "block level" is a partition, each the default partition selection uses the hashing used for KEY() partitioning, which I believe it is based on the PASSWORD() function.

I believe the number of partitions by default, as you say, should be equal to the number of nodes (2), but I believe that it can create it with a larger number if you setup a larger MAX_ROWS, so it is not fixed.

Edit thanks to extra information: The other reason why partitions can be set higher by default is because, to allow for higher concurrency -in case you are using ndbmtd-, the number of partitions get multiplied by the Local Data Manager instances (in a nutshell, how many threads can potential read and write to a data node in concurrency). When you set MaxNoOfExecutionThreads to 4, as in your case, 2 of them are dedicated to that in the multithreaded server.

I must apologize for not thinking about this before, but there are tons of small changes from version to version, and the number of partitions are usually 4 in shard-enabled 4-node setups.

Do not worry too much, as even if you had extra partitions of that size (as you can force manually, to some extent) is not a bad thing at all. Partitions (or fragments) are just the arbitrary unit that is indivisible at node level. As I commented earlier extra partitions can provide additional performance by pruning non-desired rows (assuming you are partitioning correctly and using mostly key-value reads), and make easier the addition of extra nodes.


SQL Server hangs on index rebuild, works well after SQL service restart

I've come across this issue recently. In my case, Paul Williams' comment was spot on and it's to do with a complex nvarchar(max) column I had that stores JSON and HTML:

Is this a table with a large varbinary or nvarchar(max) column?  I've seen unusual maintenance behaviors with these kinds tables.

I use the following query to narrow it down to the offending table, which is a slight variation to Ola's script at https://ola.hallengren.com/scripts/misc/CommandLogSelect.sql
The index with a null EndTime is where the crash is occurring, most likely in the last row returned.
use [master];
SELECT DatabaseName,
       SchemaName,
       ObjectName,
       CASE WHEN ObjectType = 'U' THEN 'USER_TABLE' WHEN ObjectType = 'V' THEN 'VIEW' END AS ObjectType,
       IndexName,
       CASE WHEN IndexType = 1 THEN 'CLUSTERED' WHEN IndexType = 2 THEN 'NONCLUSTERED' WHEN IndexType = 3 THEN 'XML' WHEN IndexType = 4 THEN 'SPATIAL' END AS IndexType,
       PartitionNumber,
       ExtendedInfo.value('(ExtendedInfo/PageCount)[1]','int') AS [PageCount],
       ExtendedInfo.value('(ExtendedInfo/Fragmentation)[1]','float') AS Fragmentation,
       CommandType,
       Command,
       StartTime,
       EndTime,
       DATEDIFF(ss,StartTime, (CASE WHEN EndTime IS NULL THEN StartTime ELSE EndTime END)) AS Duration,
       ErrorNumber,
       ErrorMessage
FROM dbo.CommandLog
WHERE CommandType = 'ALTER_INDEX'
ORDER BY StartTime ASC;


Is there a value that I can use in a SELECT TOP that will return all rows?

Well, it looks like TOP is a BIGINT if you aren't using a PERCENT.  That means you could pass in the max value of BIGINT,

SELECT TOP (9223372036854775807) * FROM table1

I seriously doubt you will ever see a table that large.  I'm not sure what kind of effect that would have on the query plan though.


Multi-table sum of cost breakdown by pricing category

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query.

Doing a simple row-based group of your sample dataset:

MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)

You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*),
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;

With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)

You can use the same approach to get your date-based rollups.


Always On - Restore a node from a Veeam backup

It does not look like WITH NORECOVERY functionality will be included with Veeam until a future update, per this thread found over at the official Veeam forums.  It looks like you'll need to use native features (as you've already identified) until then to get your AG back in sync.


How to install SQL Server 2012 on Windows 10

Turns out all I was doing was installing just the management studio, and not the sql server service, which is why it wasn't showing up.


Connection to Amazon RDS MySQL instance fails with `init_connect command failed`

This is because the user does not have execute grant global, to fix it

GRANT EXECUTE ON *.* TO user_name;


Why do I have different usernames from mysql.user and select user()?

What you should run is this

Please run this query:

SELECT USER(),CURRENT_USER();

What do these functions return ???

USER() reports how you attempted to authenticate in mysqld
CURRENT_USER() reports how you were allowed to authenticate by mysqld

Please note that CURRENT_USER() should return something from mysql.user. You said that USER() returns user_Name@localhost@localhost. If CURRENT_USER() comes back with the same response as USER(), then MariaDB has a bug.

Since USER() reports how you attempted to authenticate in mysqld, my guess would be that's probably how you typed it.

mysql -u user_Name@localhost

You should login to MariaDB like this

mysql -u user_Name -hlocalhost

Try it again and see. I discussed this before in this forum : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)


How Commit works in a transactional push replication with updatable subscribers

To paraphrase the quoted block, with immediate update the only guarantee is that the commit is immediately updated to the publisher.  The changes will be sent to the other subscribers in due course.

Without immediate updating, your changes are only committed to the local subscriber, and queued for distribution back to the publisher, and following that, the other subscribers.

In both cases there is a period of time when a stale record is still at the other subscribers, and one of those subscribers may update the stale record and cause a conflict.


How to deny a user to access (select, insert, delete, update) to tables but grant (select) a few

The "secret" here is that your login test is sysadmin and he's mapped to dbo in every database. Test user that is on your picture is NOT the user corresponding to login "test".

If you want to execute under user test you should write the code

exec as login = 'test'
select *
from customer
revert

And this user will have the deny on customer table.

But now all the code you executed you executed it under dbo user (that corresponds to test login), this can be proved by executing

select system_user, user

that will show test as system_user and dbo as user.

This situation can happen if the login (test) was mapped to this database when he was not sysadmin, so test user was created. But then someone made him sysadmin and now he is mapped to dbo and test is just orphaned.

Another situation is when database was restored and that user (still orphaned) has no corresponding login, the new test login is not related to it and is mapped to dbo

  how can i correct it ? it should be a just a user

exec sp_dropsrvrolemember 'test', 'sysadmin'

This way you make him an ordinary login.

Then you should check if your user test is his corresponding login or just re-map it:

alter user test with login = test;

UPDATE

Assuming that the first screenshot is shown "as is" it seems that the code to test "Check if it still works" was executed under the same account that denied the select permission, i.e. under sa login as there is no code impersonating test login. This explains because the select code was executed without errors and also the result of select system_user, user confirms this.


The correct syntax for Connecting to a default instance by using Windows Authentication to interactively run Transact-SQL statements

sqlcmd -S <ComputerName>

Connecting to a named instance by using Windows Authentication to interactively run Transact-SQL statements:

sqlcmd -S <ComputerName>\<InstanceName>

Connecting to a named instance by using Windows Authentication and specifying input and output files:

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

Connecting to the default instance on the local computer by using Windows Authentication, executing a query, and having sqlcmd remain running after the query has finished running:

sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person"


Best practice for working with materialized views in Postgres

While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns).

One such idea is to put them all in the same schema, for instance foo_matviews or foo_cache, and then to create a script that drops the schema entirely (with CASCADE), and then reinitializes the schema and all the internal mat-views back.

To be honest, it's always worked so well I've been kind of confused at why this is an issue for anyone. I always have a few SQL scripts in my code repo that do something similar to this. I've never even needed anything more complex like schema migration tools like flyway.

That all said, after you have them all in your schema you can create an extension if you wish.


Using COALESCE when joining multiple tables


I couldn't see Conflict tables in replication monitor SQL Server 2008 R2 in Merge Replication

You cannot see the conflict tables from within Replication Monitor.

However, you can view the Conflict Viewer by right-clicking your Merge Publication in Object Explorer -> View Conflicts.

Alternatively, you can directly query MSmerge_conflicts_info.

For more information, see View and Resolve Data Conflicts for Merge Publication (SQL Server Management Studio) and Microsoft Replication Conflict Viewer (Merge Replication).


Something like this would work (in Oracle).

select c.* from (select b.*, row_number() over(partition by b.bic order by b.rnum) rnk from (select rownum rnum, a.* from test a) b) c where rnk = 1;


Misleading ERROR: relation [“schema_name.table_name”] does not exist

You're creating a table with a dot identifier.

CREATE TABLE "public.t1" ( a int );
\d public.t1
Did not find any relation named "public.t1".

But, then if you quote it again there..

\d "public.t1"
   Table "public.public.t1"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |

SELECT table_catalog, table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE '%t1%';
 table_catalog | table_schema | table_name
---------------+--------------+------------
 test          | public       | public.t1

So instead use this if you want to be goofy,

SELECT * FROM "public.t1";

Or, simply never quote identifiers.

  Quoted identifiers can contain any character other than a double quote itself. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

And, also periods.

This is agianst the convention of PostgreSQL. Unless you're doing something like an automated migration that needs to avoid collisions, I would higly suggest you avoid quoted identifiers entirely.

Always create things unquoted.
Always references things unquoted.

It's not just special characters either. Quoted identifiers make your schema caps-sensitive. This can make your life needlessly more difficult.


Why is InnoDB auto_increment column skipping over large blocks of unused values?

This would be expected behavior in a few scenarios that come to mind:

If you you ROLLBACK transactions where you have inserted records into the table, the auto_increment values transiently assigned to those rows you ultimately did not COMMIT the insertion of will not be reused.
If you are using INSERT IGNORE and you have another unique key (in addition to the primary key).  For every row you attempt to INSERT without explicitly specifying the primary key value in the VALUES, the auto_increment gets incremented, and if that row happens to be a duplicate of an existing unique key value, and therefore discarded because of INSERT IGNORE, the auto_increment counter will still have been incremented and those values will never be used.
Similarly, if you are using INSERT ... ON DUPLICATE KEY UPDATE and you have another unique key, the same behavior is observed.  For those rows where a duplicate key value is found and the query is performed as an update, the auto_increment value still increments if the primary key column is not part of the original INSERT and the duplicate key detection results from a conflict against a unique key on another column.


Mongo exception: connect failed on reboot

mongod is actual mongodb daemon what "is" the database. mongo is just client program what connects to mongod process (in this case; with sharded cluster it connects to mongos -process).

So, mongod process must be running before mongo can connect it.

You wrote that sudo systemctl enable mongod.service don't work anymore? Check if that service has (some how) changed its name. Just give command sudo systemctl|grep -i mongo and you can see if it is there and it is enabled. IF still your mongod process don't start automatically after reboot, check /var/log/mongodb/mongodb.log (you can check that lock file name with sudo grep path /etc/mongod.conf -command) says. What is the error? Fix that error (maybe chown of directory and files?) and try again with sudo systemctl restart mongod.service (or what ever is services name)


How to trace steps in SQL profiler for only particular application

The ApplicationName will be the value specified in the connection string, or if none is specified, a default value that varies depending on the client API. For example, SqlClient will use the generic ".Net SqlClient Data Provider'" value when Application Name is not specified.


MS Access 2007: Combining data from tables caused combinations of data

If there are duplicate values do you want two(+) results or just one?

If you want ALL results:

SELECT Time FROM a
UNION ALL
SELECT Time FROM b
UNION ALL
SELECT Time FROM c

If you want just UNIQUE results:

SELECT Time FROM a
UNION
SELECT Time FROM b
UNION
SELECT Time FROM c


Partial backup restore of read-only file group on new database

I think that this one covers it for you (SE Link)

He answers very succinctly, but as he says the short answer is no.   He also gives you some options that can work but none as simple as it would be if you could just restore that filegroup to a different database.


create a stored procedure if it doesnt exist using a ddl in db2

IBM Added The OR REPLACE option in version 7.1

So your code should work like this :

CREATE OR REPLACE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL
BEGIN

......
......
update/select statements
......

END


In a WAMP environment, a developer is probably more focused on

Coping PHP/Perl/Python
Configuring Apache to get the WebServer going
Using MySQL to store data, far less concern for the MySQL Utilities and other Bells and Whistles
Getting Windows to coordinate the use of Apache, PHP, and MySQL (If you use IIS instead of Apache, that means the environment is a WIMP).

WAMP/LAMP environments was set up with this mind. MySQL would come with the bare essentials for installing MySQL and storing and backing up data. Such bare essential utilities may include:

mysql : Command-Line Client Program
mysqldump : For taking Textual Backups

MySQL comes with utilities for doing

Replication
Table Repair
Load Testing (such as mysqlslap)
Reporting on Stats
things like these (See all the Major Utilities for MySQL)

If a serious Developer wants to be just as serious at prepping and optimizing the database as a Developer/DBA, then MySQL has everything a DBA could want in that bigger download. Downloading both would be just fine in that instance.


How to ensure that two or more simultaneous requests to a MySQL table don't interfere

Transactions are the way to go.

... I found out that while a row is being processed by a transaction, other transactions can still come along and modify that row ...

Cite your source because, frankly, it's just plain wrong.
The whole point of Transactions is that they give you exactly the kind of "atomicity" of change that you're looking for.
Table locking is far too broad a brush to use here.
Locking many rows just update one is not efficient, creates more contention, more waiting and just slows everything down.  To draw a parallel, it would be like trying to deliver a letter to one house by blocking up every letterbox on every other house in the city.


how to check if a procedure is running, and what permissions are required?

I create my PROCEDURE_TO_TEST in a very simple way for testing purposes:

    USE MY_DATABASE
    GO

    CREATE PROCEDURE DBO.PROCEDURE_TO_TEST
        AS BEGIN

        SELECT 'BEGIN'
        WAITFOR DELAY '00:00:15'
        SELECT 'AFTER'

        END
GO

I execute the stored procedure PROCEDURE_TO_TEST and on a separate window I
use the script below to check if it is running. (if it shows up, then it is running)

declare @object int
SELECT @object=OBJECT_ID('DBO.PROCEDURE_TO_TEST')
SELECT @OBJECT

    Select *
                from        (

                                SELECT *
                                FROM sys.dm_exec_requests
                                where sql_handle is not null

                ) r
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                where t.objectid = @object

I grant the required permissions to MY_USER:

use master
go
GRANT VIEW SERVER STATE TO MY_LOGIN
GO
use MY_DATABASE
go
GRANT VIEW DEFINITION ON [PROCEDURE_TO_TEST] TO MY_LOGIN
GO

Because I use OBJECT_ID (Transact-SQL)

  A user can only view the metadata of securables that the user owns or
  on which the user has been granted permission. This means that
  metadata-emitting, built-in functions such as OBJECT_ID may return
  NULL if the user does not have any permission on the object. For more
  information, see Metadata Visibility Configuration.

And sys.dm_exec_sql_text (Transact-SQL)

  Permissions Requires VIEW SERVER STATE permission on the server.

Now for testing purposes I will run the procedure PROCEDURE_TO_TEST and on a different window I will check if it is running, IMPERSONATING MY_LOGIN.

to do this I use:

EXECUTE AS LOGIN='MY_LOGIN'

AND

--===================================================================================
-- check who is the user running the show, and the original login
-- this is to test the 'execute as login' below
--===================================================================================

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(),
                         CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

And when I have done the tests, and found out the user can run the script then I come back to be me:

REVERT

now I wrap the script into a procedure that I create called isSPRunning that receives as parameter @ProcedureName and check if @ProcedureName is running:

USE MY_DATABASE
GO

GO
CREATE procedure issprunning
@ProcedureName VARCHAR(108)
AS
/*
=======================================================================
Script  :   procedure issprunning

Desc    :

  Take as parameter a procedure name and returns 1 if it is running
   and 0 if it is not running or the procedure has not been found
   or the user does not have required permissions.

Required Permissions:

   The required permissions to make use of this procedure are:

   1) procedure to run this procedure
   2) VIEW SERVER state
   3) view definition on the @ProcedureName procedure

   the granting of permissions:
   (please replace the username and database name accordingly)

    use master
    go
    GRANT VIEW SERVER STATE TO MY_LOGIN
    GO
    use MY_DATABASE
    go
    GRANT VIEW DEFINITION ON [PROCEDURE_TO_TEST] TO MY_LOGIN
    GO
    GRANT EXECUTE ON isSpRunning TO [MY_LOGIN]
    GO

Usage   :

         DECLARE @I INT
         EXEC @I = issprunning 'PROCEDURE_TO_TEST'
         SELECT @I

Links   :

=======================================================================
History
Date             Action        User                Desc
-----------------------------------------------------------------------
03-Mar-2017     created       Marcelo Miorelli

=======================================================================
*/
BEGIN
SET NOCOUNT ON

declare @object int
SELECT @object=OBJECT_ID(@ProcedureName)
--SELECT @OBJECT

RETURN CASE WHEN
EXISTS(

                Select *
                from        (

                                SELECT *
                                FROM sys.dm_exec_requests
                                where sql_handle is not null

                ) r
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                where t.objectid = @object

) THEN 1 ELSE 0 END

END
GO
GRANT EXECUTE ON dbo.issprunning to [MY_LOGIN] as dbo
GO

In order to make it easier, so that I can pass any procedure name that exists on MY_DATABASE I have extended a the permissions granted, but consider the implications of this in your own environment.

    use MY_DATABASE
    go
    GRANT VIEW DEFINITION TO [MY_LOGIN]
    GO


The breakdown of your code by by SQL server is checked by the engine for any issues.  If it does not find them it generates a parse tree and passes it to the algebrizer.  It is another way to represent the query.


This worked for me Mark

EXEC [OtherDatabase].[sys].[sp_ExecuteSQL] N'CREATE VIEW [SM].[vMyView] AS SELECT ...'

I ran this from the context of the master database and successfully created a new view in the OtherDatabase.


Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014

Your main issue is you're using an INNER JOIN between Machines and Orders which means only return the rows that match (in other words, only the rows where Orders exist). Rather you should use an OUTER JOIN, in this case a LEFT OUTER JOIN (which can simply be written as LEFT JOIN) so you get all the rows of Models and Machines, and the ones that don't have Orders will show a NULL value for the columns in the Orders table. Then you can use the WHERE clause to filter out anything but the rows without Orders by saying WHERE Orders.SerialNo IS NULL like so:
DELETE FROM Model
WHERE ID IN
(
    SELECT Model.ID
    FROM Model
    INNER JOIN Machines
        ON Model.ID = Machines.ModelNo
    LEFT JOIN Orders
        ON Machines.SerialNo = Orders.SerialNo
        AND Orders.OrderDate >= '2015-01-01'
    WHERE Orders.SerialNo IS NULL
    GROUP BY Model.ID
);

The other issue you had was your OrderDate field is likely storing more than just the year (I'm assuming), so you need to specify the whole date (not just the year) when you do a direct comparison. I fixed that in the above query as well.
Please read the resource I linked above on LEFT JOINS along with the other resources I provided you on your last post, specifically understanding the different join types and when to use them are very important concepts.


use different odbc conector to connect mysql server

My employer, CData Software makes an ODBC Driver for MySQL: http://www.cdata.com/drivers/mysql/odbc/

Since you're working in C#, you might also want to take a look at our ADO.NET Provider for MySQL: http://www.cdata.com/drivers/mysql/ado/


Mariadb database with more than 50 million rows

I'm working on a fairly simple 140mm record table in MariaDB installation (granted it's a fairly underpowered machine)... it crawls. Hardware doesn't seem relevant since CPU nor memory is close to its limit.


What's the most efficient way to load a csv file into SQL Server 2016?

My preferred approach to pass in multiple rows of multiple fields (i.e. not a simple delimited list) is to use Table-Valued Parameters (TVPs). The idea is that you will read the file line by line in your .NET code but stream the data either all at once, or if that is too much for one transaction, broken up into batches, into SQL Server using a TVP. The TVP is essentially a table variable that is an input parameter to a stored procedure, hence this will be a set-based operation (as far as SQL Server is concerned), not row-by-row. Technically a stored procedure is not required since a TVP can be sent as a parameter to an ad hoc query, but working with a stored procedure is just a better approach anyway.

There are two main patterns for using a TVP while reading from a file (and each of them relies upon passing in a method that returns IEnumerable<SqlDataRecord> rather than a DataTable):

Execute the "import" Stored Procedure, which initiates the file open and reading. All rows are read (and can be validated at this point) and streamed into the Stored Procedure. In this approach, the Stored Procedure is executed only once, and all rows are sent in as a single set. This is the easier approach, but for larger data sets (i.e. millions of rows) it might not perform the best if the operation is to merge the data directly into a live table rather than simply loading into a staging table. The memory required for this approach is the size of 1 record.
Create a variable for int _BatchSize, open the file, and either:

Create a collection to hold the batch of records

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Store valid entry in the collection

Execute the Stored Procedure at the end of each loop, streaming in the collection.
The memory required for this approach is the size of 1 record * _BatchSize.
The benefit is that the transaction in the DB does not depend on any disk I/O latency or business logic latency.

Loop to execute the Stored Procedure until no more lines to read from the file

Execute the Stored Procedure

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Stream record into SQL Server

The memory required for this approach is the size of 1 record.
The downside is that the transaction in the DB depends on disk I/O latency and/or business logic latency, hence could be open longer, hence more potential for blocking.

I have a full example of pattern #1 over on StackOverflow in the following answer: How can I insert 10 million records in the shortest time possible?

For pattern #2.1 (a highly scalable approach), I have a partial example below:

Required database objects (using a contrived structure):

First, you need a User-Defined Table Type (UDTT).

Please note the use of UNIQUE, DEFAULT, and CHECK Constraints to enforce data integrity before the records even hit SQL Server. The Unique Constraint is also how you create an index on a Table Variable :).

CREATE TYPE [ImportStructure] AS TABLE
(
    BatchRecordID INT IDENTITY(1, 1) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    SKU VARCHAR(50) NOT NULL UNIQUE,
    LaunchDate DATETIME NULL,
    Quantity INT NOT NULL DEFAULT (0),
    CHECK ([Quantity] >= 0)
);
GO

Next, use the UDTT as an input param to an import stored procedure (hence "Tabled-Valued Parameter").

CREATE PROCEDURE dbo.ImportData (
   @CustomerID     INT,
   @ImportTable    dbo.ImportStructure READONLY
)
AS
SET NOCOUNT ON;

UPDATE prod
SET    prod.[Name] = imp.[Name],
       prod.[LaunchDate] = imp.[LaunchDate],
       prod.[Quantity] = imp.[Quantity]
FROM   [Inventory].[Products] prod
INNER JOIN @ImportTable imp
        ON imp.[SKU] = prod.[SKU]
WHERE  prod.CustomerID = @CustomerID;

INSERT INTO [Inventory].[Products] ([CustomerID], [SKU], [Name], [LaunchDate], [Quantity])
    SELECT  @CustomerID, [SKU], [Name], [LaunchDate], [Quantity]
    FROM    @ImportTable imp
    WHERE   NOT EXISTS (SELECT prod.[SKU]
                        FROM   [Inventory].[Products] prod
                        WHERE  prod.[SKU] = imp.[SKU]
                       );
GO

App Code:

First we will define the class that will be used to store the batch of records:

using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

private class ImportBatch
{
  string Name;
  string SKU;
  DateTime LaunchDate;
  int Quantity;
}

Next we define the method used to stream the data from the collection into SQL Server. Please note:

The SqlMetaData entry for BatchRecordID is defined even though it is an IDENTITY field. However, it is defined in such a way as to indicate that the value is server-generated.
The yield return returns the record but then control comes right back to the next line (which just goes back to the top of the loop).

private static IEnumerable<SqlDataRecord> SendImportBatch(List<ImportBatch> RecordsToSend)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("BatchRecordID", SqlDbType.Int, true, false,
                      SortOrder.Unspecified, -1),
      new SqlMetaData("Name", SqlDbType.NVarChar, 200),
      new SqlMetaData("SKU", SqlDbType.VarChar, 50),
      new SqlMetaData("LaunchDate", SqlDbType.DateTime),
      new SqlMetaData("Quantity", SqlDbType.Int)
   };

   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   // Stream the collection into SQL Server without first
   // copying it into a DataTable.
   foreach (ImportBatch _RecordToSend in RecordsToSend)
   {
      // we don't set field 0 as that is the IDENTITY field
      _DataRecord.SetString(1, _RecordToSend.Name);
      _DataRecord.SetString(2, _RecordToSend.SKU);
      _DataRecord.SetDateTime(3, _RecordToSend.LaunchDate);
      _DataRecord.SetInt32(4, _RecordToSend.Quantity);

      yield return _DataRecord;
   }
}

Finally, we define the overall import processing operation. It opens the Connection to SQL Server and the File, then it cycles through the file reading and validating the _BatchSize number of records for each cycle. The stored procedure parameters are only defined once as they do not change: the CustomerID value doesn't change, and the TVP parameter value is merely a reference to a method -- SendImportBatch -- that only gets invoked when the stored procedure is executed via ExecuteNonQuery. The input parameter to the method passed in as the TVP value is a reference type so it should always reflect the current value of that variable / object.

public static void ProcessImport(int CustomerID)
{
   int _BatchSize = GetBatchSize();
   string _ImportFilePath = GetImportFileForCustomer(CustomerID);

   List<ImportBatch> _CurrentBatch = new List<ImportBatch>();
   ImportBatch _CurrentRecord;

   SqlConnection _Connection = new SqlConnection("{connection string}");
   SqlCommand _Command = new SqlCommand("ImportData", _Connection);
   _Command.CommandType = CommandType.StoredProcedure;

   // Parameters do not require leading "@" when using CommandType.StoredProcedure
   SqlParameter _ParamCustomerID = new SqlParameter("CustomerID", SqlDbType.Int);
   _ParamCustomerID.Value = CustomerID;
   _Command.Parameters.Add(_ParamCustomerID);

   SqlParameter _ParamImportTbl = new SqlParameter("ImportTable", SqlDbType.Structured);
   // TypeName is not needed when using CommandType.StoredProcedure
   //_ParamImportTbl.TypeName = "dbo.ImportStructure";
   // Parameter value is method that returns streamed data (IEnumerable)
   _ParamImportTbl.Value = SendImportBatch(_CurrentBatch);
   _Command.Parameters.Add(_ParamImportTbl);

   StreamReader _FileReader = null;

   try
   {
      int _RecordCount;
      string[] _InputLine = new string[4];

      _Connection.Open();

      _FileReader = new StreamReader(_ImportFilePath);

       // process the file
       while (!_FileReader.EndOfStream)
       {
          _RecordCount = 1;

          // process a batch
          while (_RecordCount <= _BatchSize
                  && !_FileReader.EndOfStream)
          {
             _CurrentRecord = new ImportBatch();

             _InputLine = _FileReader.ReadLine().Split(new char[]{','});

             _CurrentRecord.Name = _InputLine[0];
             _CurrentRecord.SKU = _InputLine[1];
             _CurrentRecord.LaunchDate = DateTime.Parse(_InputLine[2]);
             _CurrentRecord.Quantity = Int32.Parse(_InputLine[3]);

             // Do validations, transformations, etc
             if (record is not valid)
             {
                _CurrentRecord = null;
                continue; // skip to next line in the file
             }

             _CurrentBatch.Add(_CurrentRecord);
             _RecordCount++; // only increment for valid records
          }

          _Command.ExecuteNonQuery(); // send batch to SQL Server

          _CurrentBatch.Clear();
       }
   }
   finally
   {
      _FileReader.Close();

      _Connection.Close();
   }

   return;
}

If the READONLY nature of the TVP prohibits some validation and/or transformation that is needed before being merged into the destination table, then the data in the TVP can be easily transferred to a local temporary table at the beginning of the Stored Procedure.


There are several techniques for that:

Using column permissions, grant UPDATE only on the other columns.

Create an UPDATE trigger that throws an exception when OLD.id <> NEW.id.

Note that all these measures can never deny the table owner access, since the owner can always change permissionsand drop triggers.


Is B-Tree re-balanced during deletion of data from SQL Server table with a clustered index?

To answer the question in the title, whether the B-tree rebalanced during a delete, the answer appears to be no, at least in the following minimal test case.

The following demo runs commands that are best left for a test environment.

--create table and fill it
DROP TABLE IF EXISTS bunchesofints
CREATE TABLE bunchesofints (
thisisanint INT PRIMARY KEY CLUSTERED,
junkrow CHAR(1000) NOT NULL
)

INSERT dbo.bunchesofints
SELECT TOP 5000
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS thisisanint,
REPLICATE('a',1000) AS junkrow
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2

--with this query we can see all the non-leaf pages of the b-tree, plus the IAM
SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type != 1
GO

--Ok, let's delete most of the rows
;WITH CTE AS (
    SELECT TOP (4500) *
    FROM dbo.bunchesofints
    ORDER BY thisisanint DESC
)

DELETE
FROM CTE
GO

--Hmm, still have 3 non-leaf index pages
SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type != 1

--So, where are the rows?
--please note the assumption that your test database has a single file.
DECLARE @firstindexpage INT, @lastindexpage INT, @db INT = DB_ID()
SELECT @firstindexpage = MIN(previous_page_page_id), @lastindexpage = MAX(next_page_page_id)
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type = 2 AND page_level = 1

DBCC PAGE(@db,1,@firstindexpage,3) WITH TABLERESULTS
DBCC PAGE(@db,1,@lastindexpage,3) WITH TABLERESULTS

This demo shows that a delete can produce a very unbalanced b-tree, with practically all data on one side.


Optimal table design for prizes that can be int or str

Make a prize_money column with the proper currency/float/decimal type and a prize_other column that is text/varchar. Depending on the type of prize, use one or the other.


how to use index to speed up sorting in postgres

Query
This query should be substantially faster in any case:
SELECT parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NULL  -- match index condition
UNION ALL
(
SELECT DISTINCT ON(parent_id)
       parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NOT NULL  -- match index condition
ORDER  BY parent_id, posted_at DESC NULLS LAST
)
ORDER  BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;

The CTE does nothing here that a plain subquery could not deliver also. And a CTE introduces an optimization barrier since it is executed separately and its result is materialized.

You have one more subquery-level than you actually need.

The expression (COALESCE(parent_id, message_id) is not compatible with a plain index, you would need an index on that expression. But that may not be very useful either, depending on data distribution. Follow my links below for detailed information.

Splitting the simple case of parent_id IS NULL into a separate SELECT may or may not deliver the optimum. Especially not, if that's a rare case anyway, in which case a combined query with an index on (COALESCE(parent_id, message_id) may perform better. Other considerations apply ...

Indices
Especially when supported with these indices:
CREATE INDEX messages_idx_null ON messages (
  feed_id
, posted_at DESC NULLS LAST
, share_count DESC NULLS LAST
, parent_id, message_id
)
WHERE parent_id IS NULL;

CREATE INDEX messages_idx_notnull ON messages (
  feed_id
, posted_at DESC NULLS LAST
, share_count DESC NULLS LAST
, parent_id, message_id
)
WHERE parent_id IS NOT NULL;

The two partial indices cover the whole table together and are about the same size together as a single total index.
The last two columns parent_id, message_id only make sense if you get index-only scans out of it. Else remove them from both indices.
SQL Fiddle.
Depending on missing details, DISTINCT ON may or may not be the best query technique for the purpose. Reade detailed explanation here:

Select first row in each GROUP BY group?

And possibly faster alternatives here:

Optimize GROUP BY query to retrieve latest record per user


Transaction Log for database 'myDatabase' is full due to ACTIVE_TRANSACTION when creating a Non Clustered Index

We've changed over the log file disk location to a local disk and that has fixed the problem.

It is still not entirely clear why the transaction log was not expanding in the network location as there didn't seem to be a logical reason why it wouldn't but its something to bear in mind in the future


The correct answer is:  it depends.  Here are factors you should take into account:

is there any possibility of the figures changing?  Accounts are sometimes restated.  If you had a summary table you would have to change that as well if there was a restatement.
you say you are trying to create a database.  Does this mean you already have an OLTP (online transaction processing) database and the new one would a data warehouse?  If so then you need to research data warehouse schemas like the snowflake design and, more importantly, decide the key values you are tracking.  One way of describing a data warehouse is one huge summary table.  Reporting and analysis are often easier with summarized data.
or maybe you just want to add some tables to summarize the data and keep it in the same database as the source material.  If so there are many other tools like views, materialized views that could be used that are dynamic.
there is a trade off between summarizing data.  Once you take a snapshot you have to make sure it stays in sync with the real data.  Or you can build a structure on top of the original data with views.
if the amount of data is small or can be broken into smaller parts you could fill a summary table with a procedure that marks each entry when it was created.  Then if fiscal 2011 is restated due to whatever you just delete all fiscal 2011 entries from the summary table and rebuild with your procedure.  This may or may not pass auditor standards depending on your location, your industry and your companies practices.


Instead of depending on IODKU to update rows, do the messy work in the SELECT:

INSERT INTO `ballots` (voter_id, election_id, status)
    SELECT t.voter_id, e.election_id, MAX(t.status)
        FROM elections AS e
        JOIN temp_ballots AS t  USING(date)
        WHERE e.whose = 'state'
        GROUP BY t.voter_id, e.election_id

Or...

INSERT INTO `ballots` (voter_id, election_id, status)
    SELECT  voter_id,
            ( SELECT election_id FROM elections
                     WHERE date = t.date
                       AND whose = 'state' ) AS election_id,
            MAX(status)
        FROM temp_ballots AS t
        GROUP BY voter_id, election_id

Notes:

I think the first one might be better.
The first one designed to produce the rows in PK order.
I picked MAX to choose among the status values, since you did not have any way of picking among them.


TSQL for "Ignore databases were the state is not online"

I would suggest to use Ola's backup solution (and Index maintenance  solution as well). This is a well applauded solution that is smart enough to work with mirroring (in your case).

Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient.

For e.g Maintenance plans suffer from below drawbacks :

There is no CHECKSUM option when taking backup of databases or there is no option of verifying backups using CHECKSUM.
There is not much logic you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes... and many more :-)

To address your issue, you need to check sys.databases where state_desc = 'ONLINE' and adjust your scripts.

Also, you can have a script that detects failover and disables the jobs on secondary server.

Gentle Note: Extended support for SQL Server 2005 ends on April 12, 2016


Log Shipping "out of sync" but none of the jobs are failing

Well it didn't fixed. I think I will recreate the log shipping again

Before , you give a try with that, why don't you go for looking the most recent differential backup and restore it on the secondary.

We also had situations as mentioned above and found that:

This happened due to NW glitch due to which the folder shared(on primary as common backup location with secondary) was no more shared ( due to some issues on cluster resource) and as a result few log backups never went/copied to secondary and since there was a gap, even though restore job completed but LS kept saying out of sync.

Well, in our case we went ahead and restored the latest full back up to bring the missing LSN chains in sync and later the restore job picked the next log backup file and LS was back in sync.


You can use a derived table that picks the latest movie, then join to that:

SELECT
    Author.id,
    Author.name,
    Author.surname,
    Author.photoURL as photoURL,
    Movie.title as lastMovieTitle,
    Movie.publicationDate as lastMovieDate,
    Movie.languageID as lastMovieLanguage,
    Series.name as lastMovieSeriesName,
    UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' as favourited,
    count(*) filter (where MovieRating.ratingUp) as ratingUp,
    count(*) filter (where MovieRating.ratingDown) as ratingDown,
    count(DISTINCT Movie.id) as movieCount
FROM Author
  LEFT JOIN (
      SELECT DISTINCT ON (authorid) *
      FROM movie
      ORDER BY authorid, publicationDate DESC
    ) Movie ON Author.id = Movie.authorID
  LEFT JOIN Series ON Movie.seriesID = Series.id
  LEFT JOIN UserAuthorFavourited ON UserAuthorFavourited.authorID = Author.id AND UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727'
  LEFT JOIN MovieRating ON Movie.id = MovieRating.movieID AND Movie.authorID = Author.id
GROUP BY
    Author.id,
    Movie.id,
    Series.id,
    UserAuthorFavourited.userID
ORDER BY Author.surname ASC, Movie.publicationDate DESC

I also simplified the expression for the favourited column and the conditional counting.


How do I get a graph of request traffic history in MS SQL Server 2008 R2

SQL server doesn't track that information historically.

Going forward, you'll have to do one of the following:

Set up a PERFMON trace on the server with the appropriate counters
Set up a custom SQL agent job that queries the current values and stores them in a table
Use a 3rd-party SQL monitoring product, like Solarwinds DPA or something like that.


How to check foreign keys related to a table

Firstly, find out your FOREIGN KEY constraint name in this way:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- the one you want!
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'My_Table';

You can also add (to the WHERE clause) if you have more than one table called My_Table in different schemas.

AND TABLE_SCHEMA = 'My_Database';

And then you can remove the named constraint in the following way:

ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint;

References: 1 & 2.


Mysql replication, error 13 (permission denied) with slave_load_tmpdir

Assuming that the MySQL user is called "mysql" (check for "user" in my.cnf):

chown mysql.mysql -R /var/slavetmp
Edit /etc/apparmor.d/usr.sbin.mysqld.  Copy the lines for /var/lib/mysql and edit the copied lines to refer to /var/slavetmp
Restart AppArmor:  /etc/init.d/apparmor restart
Restart the mysql service


A much better way to monitor is using sp_whoisactive - from Adam Machanic. I use sp_whoisactive with below parameters :

You can even log the output to a physical table for future analysis. It allows you to capture query plan, blocking, etc as well.

EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 1,
    @get_plans = 1,
    @get_outer_command = 1,
    @get_transaction_info = 0,
    @get_task_info = 1,
    @get_locks = 0,
    @get_avg_time = 0,
    @get_additional_info = 0,
    @find_block_leaders = 0,
    @delta_interval = 0,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
    @sort_order = '[start_time] ASC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0


import CSV into mysql table via command prompt (MySQL 5.6.25)

Since this is Windows we are dealing with, either use the double backslash

LOAD DATA LOCAL INFILE 'C:\\Users\\userName\\Downloads\\tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

or the forward slash

LOAD DATA LOCAL INFILE 'C:/Users/userName/Downloads/tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

It says so in the MySQL Documentation on LOAD DATA INFILE

  The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes. The character_set_filesystem system variable controls the interpretation of the file name.


Retrieving users by their age efficiently

You want to move the computation from the column, to the other side of the inequality operators where you have literals rather than a column:

WHERE birthday >= current_date - interval '30 years' AND birthday <= current_date - interval '21 years';

Then a simple index on "birthday" will make it efficient.  It might need some fiddling depending on what datatype "birthday" is.  I tested it as a timestamp without time zone.


Mysql user accesable with out password from the host machine but not from the remote machine

It is the way MySQL authenticates and current users installed.

When you run this command

SELECT USER(),CURRENT_USER();

you quickly learn that USER() tells you how mysqld sees you trying to login and CURRENT_USER() tells you how mysqld allowed you to login. I discussed this 3 years ago in Why does OS login not exist in the user table, but I can still login to MySQL?

My guess is that whatever CURRENT_USER() is in mysql.user has no password.

You can run SELECT CONCAT(QUOTE(user),QUOTE(host)) userhost,password FROM mysql.user; to see your users. You might note that password is blank.

To solve your issue, you must create a remote username and password.


Combine date and time into one new column

Does the new column already exist? If it doesn't then you need to create it:

ALTER TABLE YourTable ADD [DateTime] DateTime2(0) NULL;

Then you go and "insert":

UPDATE YourTable
SET [DateTime] = CONVERT(DateTime2(0), (date + ' ' + time))


How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL?

That should be simple:
ALTER PROCEDURE myproc
   SET default_transaction_isolation = 'repeatable read';

The downside to that is that because of the limitations of transaction management inside procedures, you can no longer COMMIT or ROLLBACK inside such a procedure.
If you need that, you could start the procedure body like this:
COMMIT;  -- otherwise you get a silly error
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Transaction management inside procedures is pretty limited at the moment.


One to one mapping vs many to one mapping on data base design

Some observations on this data model based on some assumptions of your intent. Your cdr data is a tab-delimited text file. The cdr data becomes the central transaction table. A transaction table has a large number of rows that change often. It looks like there are other lookup tables surrounding the call table. A lookup table has a small number of records that do not change often. I think it is good that you have a separate field, a unique integer, as the key to each table. Integer keys are the best for indexing.

Calls

Create an autogenerated key on this table similar to the others. Then put a unique index on [calling party key], [date key], [time of day key]. It is important to avoid using dates and times as keys.
Add [card no] field. Is this a unique identifier of a cdr record? If so,
use it as the new primary key.
Remove foreign key to subscriber demographics.
Add a [call date] field.
Remove the foreign key to Date table.
Add a [holiday indicator] field. One character.
Does the [SQL Date-Time Stamp] mean [answer time]?
Add a [time interval] field.
Remove the foreign key to Time of Day.
Add [peak time indicator] field. One character.
Add [call length classifier] field.
Remove foreign key to Derived Call information.

Subscriber
The [subscriber key] and [subscriber id] look like either could be a key. Do you need both?

Subscriber Demographics
It looks like a subscriber demographic record depends only on the subscriber, not on the call detail.

Move the relationship from subscriber demographics to subscriber
instead.
Add foreign key to Subscriber.

Date
It is unusual to store all possible dates in a table this way. The individual components of a date (day, month, year, day of week, and weekend) can easily be computed. Generate them in a query. Maybe save the query or view. Do not store those attributes physically. The date table is not needed.

Store the date and holiday indicator in the Calls table.
The holiday indicator would be a 1 byte field.
Remove the Date table.

Time
It is unusual to store all possible time including seconds in a separate table.

Move the time and interval to the calls table.
Remove the time table.

Mobile Service
This looks like a lookup table.

Rate Plan
This looks like a lookup table.

Calling Party
This looks like this gets populated from the cdr records. And then the activation date is updated on the record.

Derived Call Information

Option 1 if derived call information is mostly filled. This looks like it depends only on the call.

Move the [peak time indicator] and [call length classifer] fields to
the calls table.
Then you can remove this table.

Option 2 if derived call information is mostly empty. You can save space in the Calls table by keeping the derived call table.

Add [card no] as the primary key. It is a 1 to zero-or-1 relationship. Every derived call must belong to a call. Not all calls have derived calls.

Edit: removed Calls.[clear time].


You need to select the most recent record that's before or on your date of interest.  A query similar to the following would do this.

declare @date datetime
select @date = '2012-02-01'

select t.StaffID
      ,t.Target
  from Targets t
  join (select StaffID
              ,max (EffectiveFrom)    as EffectiveFrom
          from targets
         where EffectiveFrom <= @date  -- Most recent record before this date
         group by StaffID) ct
    on ct.StaffID = t.StaffID
   and ct.EffectiveFrom = t.EffectiveFrom

Unfortunately I don't have a running instance of PostgreSQL to hand, so this was on SQL Server, but the same basic structure should work on PostgreSQL.


The first and easiest option is to select the "External Data" option on the top and select Excel from the Export Data options. This lets you customize the export location and if you want to keep the Access formatting. If you wanted a different format you would have to manually change the column order before exporting to achieve this. If your referring to it not looking the same as before in Excel because it was a table there is a quick fix. Use the keyboard shortcuts ctrl + Home > ctrl + A > ctrl + L to create a table object which formats it nicely.

You can save this manual export process and re run it manually or in a macro if you choose to in the future. This saves time with manually recreating the export each time.

A more advanced option would be creating a VBA module to do the exporting and formatting keyboard shortcut for you. I have the code you can copy and paste from Access to Excel Export Code. You could additionally customize this code to format the columns in the correct order. I would recommend looking into the fld.OrdinalPostion property to do this here Microsoft Field.OrdinalPostion


Is it possible to encapsulate a table in MySQL?

In MySQL Stored Procedures, you have the concept of SQL SECURITY.

It can be either DEFINER or INVOKER

When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.
When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

For more information, please read the MySQL Documentation on Access Control for Stored Programs and Views

To see the SQL SECURITY for the procedure or function named mydb.myproc, run this:

SELECT security_type FROM information_schema.routines
WHERE routine_schema='mydb' AND routine_name='myproc';


get max value occurred within every 10 minute of a day

What about:

SELECT max(SOLAR_ACTIVE_POWER_G_VAL0) AS Solar,
       max(LOAD_ACTIVE_POWER_G_VAL0) AS Load,
       DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0) AS [Minutes]
FROM pointdb

WHERE CONVERT(varchar,[timestamp],101) in('03/07/2014')

WHERE [timestamp] >= '20140307' AND [timestamp] < '20140308'
GROUP BY DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0)

The part

DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0)

should round down to the start of the 10 minute period.
If you leave out /10*10, you get it to the nearest 1 minute period.


I think your confusion may be arising from the fact that {Department, Subjects, Programme} really isn't a candidate key.  To be a candidate key, the attributes must guarantee uniqueness of each tuple, and must be minimal--that is, there is no subset of the attributes which guarantees uniqueness.  Since {Subject, Programme} alone guarantee uniqueness, including Department in the key disqualifies it as a candidate key.

Once you remove Department from the primary key, you see that the table is not in BCNF, which requires that for any dependency X -> Y, either Y is a subset of X, or X is a superkey.  In this case, Department is not a subset of Subjects, nor is Subjects a superkey (since alone it cannot uniquely identify a tuple).  Technically, the depicted relation is not even in 3NF, since Department, now identified as a non-prime attribute, does not provide a fact about "the key, the whole key, and nothing but the key".


Best type to use in a composite key when one of the values varies in type

I wouldn't even consider forcing data of different types into one field.

Another option:

synthetic post_id and
child tables for each 'type' of 'native_post_id'

There are various ways to go about enforcing the subset relationship between these child tables and the parent if necessary


Database system checksumming to prevent network corruption not caught by TCP checksumming

Oracle has a variety of encryption and integrity features that are a part of its network protocols.

See Configuring Encryption and Integrity Parameters Using Oracle Net Manager in section 13 Configuring Oracle Database Network Encryption and Data Integrity of the Oracle Database Security Guide.


check constraint where values must be a table name

What you want is not a check constraint but an ordinary foreign key.

Normally you would do:

alter table table_name add foreign key (src_table) references user_tables(table_name);

however Oracle won't allow this as user_tables is a view, not a table.

You could try to reference the underlying Oracle data-dictionary table, but that probably won't work either -- not least because you'd need two columns at your end -- table_name and schema -- but also you'd need to be granted the privilege to reference that table.

An approach that I would consider -- as recommended above -- is to create your own table listing which tables can be referred to. You could populate this table yourself, or you could create a DDL trigger to maintain it for you!

The other thing to consider is what to do when the table is dropped: will you on delete cascade delete the record in your table, or set its contents to null using on delete set null?


PostgreSQL : avoid SUM over same values multiple times

You could use a subquery but you don't need to. Just don't sum the bonus and add it in the GROUP BY list.
Notice that you have to also add the student.id, even in your original query, in case you have 2 students with same name.
You probably also need coalesce() for students without any scores:

SELECT st.name,
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id, st.name, st.bonus ;

In newer versions of Postgres, you could use only the primary key of the student table in the group by:

SELECT st.name,
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id ;

If you want a subquery, this is one way:

SELECT st.name,
       coalesce(sc.score, 0) + st.bonus AS total
FROM student st
LEFT JOIN
    ( SELECT student_id, sum(score1) + sum(score2) AS score
      FROM score
      GROUP BY student_id
    ) AS sc ON sc.student_id = st.id ;


"Unresolved reference to User" after importing as a VS DB project

The easiest way is to not manage users through ssdt (most people don't). So you can just strip them out and don't deploy logins or users.

There are three ways:

the new options to ignore users/logins
write a deployment contributor to pull them out
use my deployment contributor http://agilesqlclub.Codeplex.com

Ed


How to disable case sensitivity in SQL Server 2005 for object name (Table name)?

Check the collation on the database. Then consider (but not flippantly) if it should be a CI (Case Insensitive) rather than a CS one.

Also look at the system databases. You may need to do a system rebuild to get it working right if you have built a system to migrate the database to. If your tempdb collation is different from the old box to the new one, you might find yourself with collation errors before too long.


How can I set block size on Oracle 11 when creating a database instance

You need to choose "Custom Database" in the "Select Template" screen of the Database Creation Assistant, and not one of the templates that includes datafiles as in those the block size is fixed and cannot be changed.


Query Exceeding Query Timeout on Linked Server and Working

Query Timeout setting affects only when the query is kept in waiting state for resources. This option doesn't affect the maximum duration a query can run and as per my understanding without custom logic, there is no setting which times out a query if it exceeds specified value.

MSDN also states the same. If the value is ZERO, SQL Server takes the value from
Global query wait Option setting which can be configured through Sp_configure

Below is an excerpt from MSDN:

  In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait


Should you run DBCC CHECKCONSTRAINTS regularly?

From Microsoft documentation:

  Checks the integrity of a specified constraint or all constraints on a
  specified table in the current database.

You only urn this after repairing a database and against the objects with constraints to confirm those constraints are still valid.

Quote from The Accidental DBA (Day 13 of 30): Consistency Checking by Erin Stellato

  CHECKCONSTRAINTS is a command to verify that data in a column or table
  adheres to the defined constraints. This command should be run any
  time you run CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. Repair in
  DBCC will fix corruption, and it doesn’t take constraints into
  consideration; it just alters data structures as needed so that data
  can be read and modified. As such, after running repair, constraint
  violations can exist, and you need to run CHECKCONSTRAINTS for the
  entire database to find them.


No, it does't, and never did.

The post you link links to the blog http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx that explains that the 'instance going to sleep' is a RANU behavior (user instance), not an Express behavior. As you plan to move from Standard to Express, is obvious that that you do not plan to move to user instances (RANU).


How to configure read transactions to never cause other write serialization transactions to fail

A READ ONLY transaction cannot cause a write transaction (that does not perform DDL) to fail, unless it explicitly uses LOCK TABLE or advisory locks.

READ ONLY transactions cannot SELECT ... FOR SHARE or SELECT ... FOR UPDATE. As they can't do DML, the strongest lock they can take on a table is ACCESS SHARE, which conflicts only with the ACCESS EXCLUSIVE lock taken by DDL.

Nor can a read-only transaction cause serialization failures if the write transaction is SERIALIZABLE, because serialization failures require that both transactions perform writes. It is always possible to logically serialize a read only transaction either before or after a read/write transaction, as it is impossible for them to be mutually interdependent.

So: It should be fine to use READ COMMITTED or SERIALIZABLE transactions, with READ ONLY, so long as you do not explicitly LOCK TABLE.

You also need to make sure you don't use advisory locks that might interact between the two sets of transactions. Most likely you don't use advisory locks at all and can forget about this entirely.

Separately, though, an application must be prepared to deal with serialization failures or other transaction aborts. Any design that tries to avoid this is broken. Transactions can be aborted because of OS / host machine level issues, admin action, etc. Do not rely on transactions that "cannot fail". If you absolutely must do this, you need to use two-phase commit, where you PREPARE TRANSACTION (at which point it's guaranteed that the tx cannot fail to commit), do the other work that relies on the tx committing safely, then COMMIT PREPARED. If something goes wrong with the other work, you can ROLLBACK PREPARED. 2PC has significant overheads and is best avoided when you can, but sometimes there's just no choice.


Altering a columns position is going to require a full table rewrite. My suggestion is not to alter column positions. However, there is an abundance of people doing this and there are numerous ways to do it. In my experience,

one way is far better and easier, if you have the privileges, and
most DBAs have those privileges.

My suggestion is to use pg_dump.

Dump with --column-inserts

  Dump data as INSERT commands with explicit column names (INSERT
  INTO table (column, ...) VALUES ...). This will make restoration
  very slow; it is mainly useful for making dumps that can be loaded
  into non-PostgreSQL databases. However, since this option generates
  a separate command for each row, an error in reloading a row causes
  only that row to be lost rather than the entire table contents.

Remove from dump what you don't need.
Modify table definition or ordering.
In a transaction,

Drop the old tables
Execute the script creating the new tables.
Commit


Postgresql 9.3 replication: Preventing wal-files from building up, if receiver goes offline

Set wal_keep_segments to a low enough value that WAL will be discarded before pg_xlog fills up.

If you're also using WAL archiving with an archive_command, Pg will keep on trying until it successfully sends the WAL or runs out of space. Don't have your master copy the WAL directly to the replica; instead put it on some reliable high availability storage that the replica also has access to.


Check constraint only one of three columns is non-null

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO


Can multiple Oracle databases share a single parameter file?

No, you would have to use separate parameter files/spfiles for each database.  As you've pointed out, you'll need some settings to differ (locations of files, database name, etc.).  In many environments, you'd want to adjust memory and other performance settings differently because the development environments often don't have nearly as much RAM as the production environment.

Even if you could share a parameter file, though, you wouldn't want to.  Changing a parameter should involve some amount of development and testing just like changing code.  So you would want to make that change in dev, validate it, and promote it up just like a code change.  If you were sharing a parameter file, you'd have to make the change in all the environments simultaneously which somewhat defeats the purpose of having multiple environments.  If you make a change in dev that doesn't work out, you need to back that out just like you would back out a code change.

You could certainly take a backup of the production parameter file and copy that down with whatever changes are necessary to the lower environments.  You could use whatever mechanism you use today to manage code deploys (I'm assuming some sort of source control system and build process) to version those parameter files and to deploy them so that you know what the differences are and why (i.e. dev uses a lower memory_target in general, dev has global_names set to true because you are testing that out for project X but that hasn't been promoted to production yet).


Would SQL in a cluster failover when transaction log for database is full?

No, it shouldn't because that is not a server level event that the cluster would be aware of when it happens.

I seem to recall coming across an article that referenced specific events that could cause a failover but can't find it right now.


CASE statement with IS NULL and NOT NULL

Did you try:

CASE WHEN (ID IS NULL) THEN 'YES' ELSE 'NO' END AS ID_Value,

I only have access to 2008 right now, but I'd hope that this syntax would still work in 2005 (seems like something that would be part of the original definition of CASE).


Delete Query Issue in MySQL and PHPMyAdmin

Thanks All for your effort.

I found the solution.
CodeIgniter where_in() added single quotation automatically, which is a bug in codeigniter. I use where() in place of where_in(). Issue fixed.


Mysql database in a symlink-ed directory gives ERROR 1005 (errorno: 13)

I managed to solve this, basically by the advice given by Mat.

The first valueable tip was to use ls -Ll to show information  for the file the link references rather than for the link itself.

It is clear that the symlink should show the user (and group) mysql is using.

When mouting (in this case the cifs type) I first used

mount -t cifs //nas01/myshare /mnt/nas -o user=nasuser

where I should have used

mount -t cifs //nas01/myshare /mnt/nas -o user=nasuser,uid=mysql,gid=mysql

This resolved the errorno: 13 because ls -Ll now showed mysql as owner and group for the symlinked directory.


Data Modeling Problem (Tasks / Timerecords / Users)

This should be close enough.

-- User USR exists.
--
user {USR}
  PK {USR}

-- Task TSK exists.
--
task {TSK}
  PK {TSK}

-- Task TSK is assigned to user USR,
-- with time estimate of EST hours.
--
task_user {TSK, USR, EST}
       PK {TSK, USR}

FK1 {TSK} REFERENCES task {TSK}
FK2 {USR} REFERENCES user {USR}

-- On date DTE user USR spent HRW hours
-- working on task TSK.
--
time_record {TSK, USR, DTE, HRW}
         PK {TSK, USR, DTE}

FK {TSK, USR} REFERENCES task_user {TSK, USR}

-- PostgreSQL, SQL Server
--
WITH
q_1 AS (
    SELECT USR
         , sum(EST) AS total_est
    FROM task_user
    GROUP BY USR
),
q_2 AS (
    SELECT USR
         , sum(HRW) AS total_log
    FROM time_record
    GROUP BY USR
)
SELECT a.USR
     , coalesce(b.total_est, 0) AS total_estimate
     , coalesce(c.total_log, 0) AS total_logged
FROM      user AS a
LEFT JOIN q_1  AS b ON b.USR = a.USR
LEFT JOIN q_2  AS c ON c.USR = a.USR ;

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key


Database normalization, should I combine these two tables?

At the theoretical level, it does sound like these two tables can be combined.

At the practical level, unless you're rewriting everything that interacts with the database, no explicit significant harm should arise if you leave it as is.

If the only duplication of data is the user id, then the space to be gained from the change is minor, and probably not worth the cost of modifying your existing code to find the data in its new location.

If we knew more of the specifics of your situation, we might be able to provide a more detailed answer.


Does rebuilding a clustered index on an IDENTITY column do anything useful?

Since you don't delete rows, no, rebuilding the index will provide no real value outside of updating the statistics for said index, as they are implicitly updated with a rebuild operation.  A more efficient approach is to just run an UPDATE STATISTICS statement instead.

Per Benjamin Nevarez:

  Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN.

To answer your other questions:

  would the table be any different?

Other than located at a different page address, no, structurally it would likely be no different.

  Obviously this is not true if the table has non-clustered indexes as well.

Rebuilding the clustered index doesn't automatically rebuild any non-clustered indexes associated with the table.  To do this, you would need to specify the keyword ALL instead of the clustered index name.  Per MS:

  ...using ALTER INDEX…REBUILD to rebuild a clustered index does not rebuild its nonclustered indexes by default... You have to specify ALL for the index name in order to rebuild all indexes.

And the last question:

  if this table did have additional indexes it still doesn't NEED to rebuild the primary index first if like our table it was all just adds, right?

Correct.  If you never modify a record after it's added to the end of the table, you'd really only ever need to update statistics in order to avoid the Ascending Key Problem as identified by Scott Hodgin's comment below.


What features of Oracle make it an attractive choice for small projects?

I can only attempt to answer for Oracle and postgres. After using Oracle exclusively for years, and postgres only for the last two years or so, I love postgres. There are so many small ways it is more convenient to use than Oracle, and it shares many of the crucial benefits (such as MVCC). It is easier to administer, reliable, has excellent documentation, and of course, it is free.

However postgres is no match for Oracle in some areas, such as:

RAC - as far as I know there is no better clustering technology for any database
RMAN - far superior to postgres's basic backup and recovery features, especially with block change tracking and incremental backups (which you can apply to other backups to keep an up to date full backup)
Oracle support - exists. postgres support? Not so much
many other features such as IOTs, 'Secure Files' and compression technology that have no analogue in the postgres world

Interestingly almost all af these features are absent or crippled in XE. I think I'd choose postgres over XE, all else being equal, but...

...none of this addresses the two biggest reasons for choosing Oracle:

You are already using Oracle and you have a huge investment in it (database independence is a silly myth)
Your developers and DBAs know Oracle and use its features to the full (why wouldn't they as database independence is a silly myth anyway?)

EDIT:

The one situation where I'd choose postgres over XE every time is if security is a concern. If your database or any of it's applications are exposed to the public internet I am not sure if XE is a good idea at all.


How can I design a table for quick versioning of textual data?

You already have one option: keep it in one table
This is a "Slowly Changing Dimension".

The other options is to split "Current" and "Previous" versions into 2 tables. When you update the current table, a trigger can populate the "Previous" table. Or an UPDATE..OUTPUT.

You can query this data in 2 ways:

combined into one recordset, client code splits it up
two record sets, one current + one previous. This can be done in a stored procedure too.

I've implemented both: it depends on access patterns and usage.

If I'm reading all versions in one go or JOINING onto it, then one table. If "previous" data is only on demand then tables.

In your case, can you defer the previous versions until a user clicks to load it? Is it rare to actually need previous versions? If yes to either, then I'd suggest 2 tables. This way, you only shift and render less data only for current versions.


Can I limit SQL server one database resources

Since you are using Developer Edition (and is cheap as compared Standard or Enterprise), you are better off installing another instance with 5GB Max Memory. Put the databases that you need less memory on this instance.

The rest of databases you can put on the instance with more memory.

Read up Tibor's blog post on Restricting memory using Resource Governor which references the link that @shanky referenced.

  A misconception I see from time to time is that the memory setting is to restrict the Buffer Pool, BP, (mainly used for caching pages from disk). This seems difficult since a page in BP has a cost, but isn't owned by whoever brought it into cache or who referenced it last time. So, it seems difficult with current implementation of cache handling and ageing to govern the BP memory for RG. What RG does govern is "working memory" for a query. Think memory used for sorting, locks, hashing and such.

Read up on : Performance issues from wasted buffer pool memory from Paul Randal.


There is no such thing as "physical order" in the relational databases. Sometimes queries can produce the ordered results but that should be considered as "ordered by coincidence". There is absolutely no guarantee that you get the same ordered result each time.

If you need the ordered result the ORDER BY clause is a must.


We used Wireshark and SQL Profiler and were able to uncover an SP returning 700,000 records repetitively.

As far as we were able to tell then login and logout were part of connection pooling.


Error when using OPENROWSET from a View as a nonsysadmin

To respond to my own question, was able to get it working using a loopback linked server and using OPENQUERY instead of OPENROWSET. The non-sysadmin account now has the controlled access it needs.
Here is the new query the view uses:
SELECT *
FROM
OPENQUERY([LOOPBACKSERVERNAME],
           'SET FMTONLY OFF;
            SET NOCOUNT ON;
            EXEC [My Stored Procedure] @params = ''field1,field2''
            WITH RESULT SETS
            (
                (
                    field1 NVARCHAR(1000),
                    field2 NVARCHAR(1000)
                )
            )
        ')


Oracle Database Enterprise Users & Roles with Microsoft Active Directory?

The application I maintain does something similar that is database OS independent for around 100 users but in a much simpler fashion. Users are authenticated by the application and authorized from the database. Here are the details:

a table for users including their email and enabled/disabled status
a table of all AD groups that have a specific prefix to indicate that they are application groups  (eg:  APP_ADMINS, APP_USERS)
a table with group/user links

The next part is synchronizing the information.  In our organization if new users are added or permissions for existing users are changed IT does the job in the morning.  The application typically has a window of low usage overnight (ie: not 24 hours a day/usage).

On the application web server we have a windows service which syncs the groups and users.
On the database at night I run a scheduled job which truncates the group-user links and recreates them by a direct query to Active Directory using DBMS_LDAP.

For your purposes you would want to write a more sophisticated package that has a database package running three LDAP queries to synchronize groups, users and group users instead of my truncate solution.

I found DBMS_LDAP to be powerful but poorly documented.  If the IT department should happen to change the OU that users are in you don't get a lot of useful information from the Oracle end.

Tim's site was very useful with this article and here has the code I reused.

Edit: There are two parts to using an application:

who are you?  (authentication) if you do not supply a name and password listed in Active Directory you are not allowed past the log in page.
what are you allowed to do? (authorization) Here is where the granularity of permissions has a wide range. We have limited ourselves to a basic set of operations on a portion of a unit of work:  Read, Update, Assign other users, Unassign other users.  Our permissions are not so granular so they can be worded as "User X has select on this table".  They can be better said as "User X
can update this portion of the file if they are assigned".

In part this is driven by the application.  It creates  a pooled connection to the database as required.  Users log onto the application but connect to the database as a shared user who has all the permissions required.

To go into a more detailed example:
User X logs in.  They have a username and password that matches what is in Active Directory.
After authentication they are taken to the main page.  During that time the application queries the database for what permissions this user has and caches them in memory.
The main page loads each sub work area. Each area asks the cached permissions:
-  "Do you have authority to see this area?". If so show the work area.
- " Do you have permission to edit this area?" If so show an edit/create link.

Edit2:
The original poster comments that

  We are trying to replicate the user permissions onto application to DB
  permissions onto database objects.

This sounds like a square peg/round hole problem to me.  Your business logic may be defined as User X needs select on this table but if you ask the user they are working with the application.  They need to be able to work with whatever the application is displaying. It is common to group tables and views and custom filters to show what the user needs in the application.  This is why some applications find it easier to enforce authorization at the application level.
What is it about your application that requires database permissions to be replicated in Active Directory?


Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service.

That's a pretty standard use for a trigger.

  Before going all in with Postgres TRIGGER(s) we would like to know how they scale: how many triggers can we create on a single Postgres installation?

If you keep creating them, eventually you'll run out of disk space.

There's no specific limit for triggers.

PostgreSQL limits are documented on the about page.

  Does they affect query performance?

It depends on the trigger type, trigger language, and what the trigger does.

A simple PL/PgSQL BEFORE ... FOR EACH STATEMENT trigger that doesn't do anything has near-zero overhead.

FOR EACH ROW triggers have higher overhead than FOR EACH STATEMENT triggers. Scaling, obviously, with the affected row counts.

AFTER triggers are more expensive than BEFORE triggers because they must be queued up until the statement finishes doing its work, then executed. They aren't spilled to disk if the queue gets big (at least in 9.4 and below, may change in future) so huge AFTER trigger queues can cause available memory to overrun, resulting in the statement aborting.

A trigger that modifies the NEW row before insert/update is cheaper than a trigger that does DML.

The specific use case you want would perform better with an in-progress enhancement that might make it into PostgreSQL 9.5 (if we're lucky), where FOR EACH STATEMENT triggers can see virtual OLD and NEW tables. This isn't possible in current PostgreSQL versions, so you must use FOR EACH ROW triggers instead.

  Did anyone before tried this ?

Of course. It's a pretty standard use for triggers, along with auditing, sanity checking, etc.

You'll want to look into LISTEN and NOTIFY for a good way to wake up your worker when changes to the task table happen.

You're already doing the most important thing by avoiding talking to external systems directly from triggers. That tends to be problematic for performance and reliability. People often try to do things like send mail directly from a trigger, and that's bad news.


What's the disadvantage of encrypt mariadb?

Your question is a bit open-ended, but here are few things that might be considered disadvantages:

Encryption and decryption are CPU-intensive tasks, so you will see increased CPU utilization. If your server is CPU-bound, this may be a problem.
As a consequence of the above, encryption/decryption add certain latency to your I/O operations, so query performance might be negatively affected.
There is certain effort involved in managing encryption keys. Also, if you ever lose them, you lose all your data as well.


Export from Oracle 10g and import to Oracle 12c unable to create users

You have created a CDB (Container Database), and connected to the root container. There you are not allowed to create regular users, you can create common users whose name starts with C##.

If you do not care about or want to use pluggable databases, I suggest that you drop your empty 12c database, and create a new non-CDB with DBCA (Database Configuration Assistant).


Flip a flag from one row to another in a single query

Just flip it for both:

update the_table
   set is_active = not is_active
where device = 'd1'
  and id in (1,2);

For a more general approach see:
How can I swap two values from particular column in a table in Postgres?


PostgreSQL parallel scan with "greater" or "less" than

Your > query returns hugely more rows than =.  Each of the rows being returned from a parallel worker needs to get passed up from the parallel worker to the leader, and doing this is not free.  (This is the cost accounted for by parallel_tuple_cost).  A row filtered out by the parallel worker, on the other hand, does not suffer this expense as it is not passed up.  If most rows will be filtered out, it makes sense to do the filtering in parallel.  If few rows will be filtered out, it might not make sense to do the filtering in parallel but then do the extra work of shuffling the data around between processes.
What you have not shown us is whether doing the > query in parallel is actually faster than doing it in a single process.  Maybe you need to tune parallel_tuple_cost down some.  The default settings is quite conservative.


Hosting SQL Server and SSIS/SSRS in same VM

This totally depends how healthy the server is SQL1,2008R2.

You will need to analyse first the usage of Databases, SSIS or SSRS and other parameters hosted on the server.

Also, what resources you have in terms of RAM, CPU, Disk(Raid levels) etc. available on the server along with how they are being getting used.

It would be a good start if you separate SSIS/SSRS on different server, assuming you have performed a load analysis on this server which might prompted you to think,in terms of for performance and scalability. Databases like to have a room of their own in terms of RAM/CPU (again this depends upon the usage of applications attached to it), thus other services like SSRS seems not a good choice to be hosted on same.

Generally, monitoring CPU, memory, paging and I/O on a server will give you an good indication of whether a server is getting a little too busy. You may have to do various calculation here and there and come to a conclusion which should answer you're question accordingly.

Therefore, it totally depends how the server is behaving currently and how it is expected to grow in future.