This is a common issue with systems that need to retain large/indefinite amounts of historical data online. You have a few options depending on your predilections/restrictions.

Stretch Database in SQL Server 2016 is designed specifically to
solve this type of problem. Historical data gets moved the Azure so the local dataset is much smaller plus computations on the historical data is done in Azure, not your local server. However, it requires Azure. It can
be a secure as you need it to be but if you are subject to
regulations that prevent you from using public clouds, then this
won't work for you.
Partition the large tables and place cold data in different files/filegroups. That helps with some operations but it does add sizeable complexity to your regular maintenance tasks. Lots of good guidance published by Microsoft, MVPs and others on designing and managing partitioned tables for cold data so you're not breaking new ground here.
Put everything in solid state storage devices. This does not solve
the problem but will sufficiently mask the performance problems so
you'll breathe easy for a while. If SSDs become bottlenecked, switch
to Fusion IO. When that gets pegged, there'll probably be some newer tech.
Btw, this is not a recommended approach but if you need a bandaid
until you can implement a real solution, this does work. The other minor issue here is the high cost.

Highly recommend you look at #1 first followed by #2.


The SQL Server security model allows you to grant access to a view without granting access to the underlying tables.
Since example code is a great way to show a concept, consider the following, with a LoginDetails table, and corresponding view:
CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE VIEW dbo.LoginDetailsView
AS
SELECT ld.Username
    , ld.EmailAddress
    , ld.LastLoggedInAt
FROM dbo.LoginDetails ld
WHERE ld.LastLoggedInAt IS NOT NULL;
GO

We'll create a login, and a user, then assign that user the rights to select rows from the view, without having any rights to view the table itself.
CREATE LOGIN RemoteUser
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetailsView TO RemoteUser;

Now, we'll insert two test rows:
INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

This tests the security model.  The first SELECT statement succeeds, since it is selecting from the view, whereas the second SELECT statement fails because the user does not have direct access to the table.
EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetailsView;

╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 07:36:54.490 ║
╚══════════╩══════════════╩═════════════════════════╝

SELECT *
FROM dbo.LoginDetails;

REVERT

Note the results from the view exclude the row where the LastLoggedInAt value is NULL, as required in your question.
The second SELECT statement against the underlying table returns an error:

Msg 229, Level 14, State 5, Line 28
The SELECT permission was denied on the object 'LoginDetails', database 'tempdb', schema 'dbo'.

Cleanup:
DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP VIEW dbo.LoginDetailsView;
DROP TABLE dbo.LoginDetails;

Alternately, if you have SQL Server 2016 or newer, you could use a row-level-security-predicate to prevent certain users seeing rows with a NULL LastLoggedInAt value.  The Microsoft Docs for Row Level Security is here.
First, we create the table, a login, a user for that login, and we grant access to the table:
CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE LOGIN RemoteUser
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetails TO RemoteUser;

Next, we insert a couple of sample rows.  One row with a null LastLoggedInAt, and one with a non-null value for that column.
INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

Here, we're creating a schema-bound table-valued-function that returns a row with either 0 or 1 depending on the value of the @LastLoggedInAt and @username variables that are passed into the function.  This function will be used by a filter-predicate to eliminate the rows we want to hide from certain users.
CREATE FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate
(
    @LastLoggedInAt datetime
    , @username sysname
)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE (@username = N'RemoteUser' AND @LastLoggedInAt IS NOT NULL)
        OR @username <> N'RemoteUser';
GO

This is the security filter that eliminates rows from SELECT statements ran against the dbo.LoginDetails table:
CREATE SECURITY POLICY LoginDetailsRemoteUserPolicy
ADD FILTER PREDICATE dbo.fn_LoginDetailsRemoteUserPredicate(LastLoggedInAt, USER_NAME())
ON dbo.LoginDetails
WITH (STATE=ON);

The filter above uses the dbo.fn_LoginDetailsRemoteUserPredicate function by passing in the name of the current user, along with the values from each row for the LastLoggedInAt column from the dbo.LoginDetails table.
If we query the table as a normal user:
SELECT *
FROM dbo.LoginDetails

we see all rows:
╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user x   ║ x@y.com      ║ NULL                    ║
║ user y   ║ y@y.com      ║ 2018-02-15 13:53:42.577 ║
╚══════════╩══════════════╩═════════════════════════╝
However, if we test as the RemoteUser:
EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetails

REVERT

we only see "valid" rows:
╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 13:42:02.023 ║
╚══════════╩══════════════╩═════════════════════════╝
And, we clean up:
DROP SECURITY POLICY LoginDetailsRemoteUserPolicy;
DROP FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate;
DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP TABLE dbo.LoginDetails;

Be aware that schema-binding a function to the table in this way does make it impossible to modify the definition of the table without first dropping the filter predicate, and the dbo.fn_LoginDetailsRemoteUserPredicate function.


SELECT records that contains just ENGLISH A - Z 1 -9 charachters

For MySQL you can use REGEXP

Like

 SELECT * FROM table WHERE column REGEXP '^[[:alnum:]]+$';

Or if you need to exclude some letters

 SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$';

SQL Server has a similar Expression like2

SELECT * FROM table WHERE column NOT LIKE '%[^ ,.-~]%' collate Latin1_General_BIN

DEMO für ,ysql

CREATE TABLE Demo (ID INT NOT NULL AUTO_INCREMENT
, Name VARCHAR(50) NOT NULL,
     PRIMARY KEY (ID));

INSERT INTO Demo (Name)

SELECT '0123456789'

UNION ALL

SELECT 'abcdefghijklmnopqrstuvwxyz'

UNION ALL

SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

UNION ALL

SELECT '0.a'

UNION ALL

SELECT '0,a'

UNION ALL

SELECT '0éa'

UNION ALL

SELECT '0!a'

UNION ALL

SELECT '0?a'

UNION ALL

SELECT '0''a'

UNION ALL

SELECT '0#a'

UNION ALL

SELECT '0+a'

✓

✓

SELECT * FROM Demo  WHERE Name REGEXP '^[A-Za-z0-9]+$';

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ

SELECT * FROM Demo

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a
 5 | 0,a
 6 | 0éa
 7 | 0!a
 8 | 0?a
 9 | 0'a
10 | 0#a
11 | 0+a

db<>fiddle here

SQL SERVEr Example

CREATE TABLE #Demo (ID INT NOT NULL IDENTITY(1,1), Name VARCHAR(50) NOT NULL)
INSERT INTO #Demo (Name)

SELECT '0123456789'

UNION ALL

SELECT 'abcdefghijklmnopqrstuvwxyz'

UNION ALL

SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

UNION ALL

SELECT '0.a'

UNION ALL

SELECT '0,a'

UNION ALL

SELECT '0éa'

UNION ALL

SELECT '0!a'

UNION ALL

SELECT '0?a'

UNION ALL

SELECT '0''a'

UNION ALL

SELECT '0#a'

UNION ALL

SELECT '0+a'
GO

11 rows affected

SELECT * FROM #Demo WHERE Name NOT LIKE '%[^ ,.-~]%' collate Latin1_General_BIN
GO

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a
 5 | 0,a
 8 | 0?a

SELECT * FROM #Demo
GO

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a
 5 | 0,a
 6 | 0éa
 7 | 0!a
 8 | 0?a
 9 | 0'a
10 | 0#a
11 | 0+a

db<>fiddle here


SQL Server 2012 - Can't detach database on mirror server

To remove database mirroring

Connect to the Database Engine of either mirroring partner.
From the Standard bar, click New Query.
Issue the following Transact-SQL statement:

ALTER DATABASE database_name SET PARTNER OFF

where database_name is the mirrored database whose session you want to remove.

The following example removes database mirroring from the AdventureWorks2012 sample database.

ALTER DATABASE AdventureWorks2012 SET PARTNER OFF;


Paul White explains in his answer to the question below amongst other things that automatic indexed view matching won't even be considered if a low enough plan is found first.

What factors go into an Indexed View's Clustered Index being selected?

also
Consider having a look at what statistics you have in each table
and keeping them updated

and
when using indexed views WITH(NOEXPAND) query hint

From Query Hints (Transact SQL) on MSDN:

  The indexed view is not expanded only if the view is directly
  referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH
  (NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.


Specify Values for Template Parameters -- command

Right-click the template in the Template Explorer and click Edit.

EDIT: If you inspect the path to the template files, they are stored buried in your user profile folder. If your AD profiles are out of sync, or the original machine you installed the management tools had different settings, this could account for the difference. You still should be able to edit the templates, though.


Conversion failed when converting the nvarchar value '0.000000' to data type int

In your code I see a CONVERT(NUMERIC,...) but not a CONVERT(INT,...).  So where is the converting to data type int happening?

SELECT CONVERT(INT, N'0.0000')    -- Conversion failed message
SELECT CONVERT(NUMERIC, N'0.0000')-- Converts to 0

For what it is worth, if you do the following, you also get a '0'.

SELECT CONVERT(INT, CONVERT(NUMERIC, N'0.0000'))

It seems that FLEXFIELD4 is NVARCHAR. If there is some other conversion involved that your code does not show, then that might clarify the issue.


What is the ideal composite index for this query?

INDEX(ColB, ColC, ColD)

with the columns in any order, is good.  This takes care of all of the WHERE clause.

Potentially even better is a "covering" index:

INDEX(ColB, ColC, ColD, ColA)

with ColA last.

"Covering means that all the columns in the SELECT are in the index.  That way, the query can be performed in the BTree for the index, and not have to jump over to the data for anything else.

If the table is InnoDB, then the first index will contain ColA.  This is because any secondary index implicitly contains the PRIMARY KEY.

Even in a covering index, the order is important.

Index Merge Intersect is almost never faster than a Composite index that handles the correct columns.  (I think of Index Merge as a clue that you should add a Composite index.)

More on building optimal indexes.

Do not use DESC, it is not as descriptive as SHOW CREATE TABLE !

Drop KEY ColB (ColB) -- it is now redundant.


Does MSSQL Log shipping truncate the transaction log?

The LSBackup job in a log shipping configuration performs a transaction log backup, so this will help maintain your log file size the same as a normal log backup job does.

Note that this will not truncate the file (reclaim space), it simply marks the portions of the log that have been backed up as available for re-use and this helps prevent log file growth by reusing existing space in the file. For performance reason, you don't want to be shrinking and regrowing your transaction log file constantly.

Typically if you're using log shipping there is no need to run another copy-only log backup job, you're better off simply copying the log backups that LSBackup creates into a suitable storage location. You will also run into problems if you're running LSBackup and a non-copy only log backup job as the restore chain will be broken on the secondary, as you've found.


Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor

Going from 2008 to 2016 is jumping too far.
I suggest you to go move to 2012 first and then to 2016.

First of all download Microsoft Migration Assistant and try to spot incompatibilities.
Then download Microsoft Assessment and Planning Toolkit and scan for all sort of incompatibilities following a guide.
Spot the incompatible queries using the Query Tuning Assistant

Now that you are on 2012 repeat the steps 1, 2 and 3 and move to 2016.


I spend the day dealing with the same problem in vain, my temporal solution would be to run the SSIS package with a CMD command.

here is my example:

dtexec /ISSERVER "\"\SSISDB\project_____name\Main_Package.dtsx\"" /SERVER server___name /X86 /Par "\"$Project::var___name\"";"\"value___value\""


I use XML when I need more flexible structure than a database table can offer.

When you have data that you do not know exactly the structure beforehand, it could be profitable to store it as XML. You can store such XML in a relational database field, as many e-learning systems do.

In fact e-learning course structure is a good example because individual courses could be quite different from each other.

So XML is somewhere between a document and a relational-table. Imagine a database table where each record would have a very different structure, that would be good use for XML.

Also XML is very good for data interchange, meanwhile files like .DBF, .XLS etc. are not so good.


Why does my hard-drive fill up when executing a query?

Three suspects

tempdb
A big query may use tempdb
It may use it for spool, hash match, and spool
Can cap the size of tempdb
Can find this in SSMS
log
If you are in full recovery mode then a select may use the transaction log.
You can cap the size the transaction log but then the query just does not run.
Can find this in SSMS
Can change the recovery model in SSMS
swap file
As SQL asks for memory and the server is out of physical memory then the OS will use the swap file.  Not optimal. Cap the amount of memory available to SQL at like physical memory minus 1-4 gb.
Can cap memory in SSMS
Can cap page file at the OS
I think it is in Control Panel


Find a row by primary key, when you don't know in which table it is?

All tables will use bigint as primary key, and all (indirectly) inherit from the same base table

I am not sure I like this "all tables inherit from the base table", but given that, it sounds doable with Postgres:

To generate the primary key for all tables, create one sequence:

create sequence one_for_all as bigint;

Create the base table using that sequence to generate the values:

create table base (id integer primary key default nextval('one_for_all'));

Note that the primary key will not be enforced for the child tables!

Then create the child tables:

create table t1 (t1_data integer, primary key (id)) inherits (base);
create table t2 (t2_data integer, primary key (id)) inherits (base);

If you now insert into the child tables, the sequence will be used to generate the ID:

insert into t1 (t1_data) values (100);
insert into t2 (t2_data) values (200);

To find in which table a row is located, select from the base table and include the tableoid column, that identifies the actual table in which the row is located:

select b.*, tableoid::regclass as actual_table
from base b
where id = 42;

You will still need another query to return the full row of the child table.

Online example: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=4305e86b996e7a94c24faed733257232

Another approach that does not need inheritance is to generate ID values that encode the table name (shudder).

Something along the lines:

create sequence one_for_all as bigint;
create table lookup (table_name text primary key, code serial unique);
insert into lookup (table_name) values ('base'), ('t1'), ('t2');

create function get_id(p_tablename text)
  returns bigint
as
$$
  select nextval('one_for_all') * 1000 + code
  from lookup
  where table_name = p_tablename;
$$
language sql;

create function get_tablename(p_id bigint)
  returns text
as
$$
  select table_name
  from lookup
  where code = p_id % 1000;
$$
language sql;

By multiplying the value from the sequence with 1000 we essentially have the lower 3 digits available to encode a unique number for each table. In order to be able to lookup those numbers, we need that additional table.

Note that this will fail miserably if the lookup table does not contain all tables!

Then instead of using nextval() use, the get_id() function:

create table base (id integer primary key default get_id('base'));
create table t1 (id integer primary key default get_id('t1'), some_value integer);
create table t2 (id integer primary key default get_id('t2'), some_data text);

Then you can do:

insert into t1 (some_value) values (42);
insert into t2 (some_data) values ('foo');
insert into t1 (some_value) values (117);
insert into t2 (some_data) values ('bar');

So the rows in t2 now go the IDs 2003 and 4003. The function get_tablename() can be used to retrieve the table name to which an ID belongs:

select get_tablename(2003);

get_tablename
-------------
t2

This is certainly faster in terms of looking up the table name and does not carry the baggage of a huge inheritance tree with it. So performance wise it's probably faster. It will however become a maintenance nightmare.

Populating of the lookup table could perhaps be done through event triggers whenever a table is created or dropped.

If you can change your application to support varchar primary keys, instead of bigint (and you can live with the slightly higher storage requirements), you could also generate the ID as a string that simply contains the tablename:

create function get_id(p_tablename text)
  returns text
as
$$
  select concat(nextval('one_for_all'), '_', p_tablename)
$$
language sql;

Things like sorting and range queries will be complicated though.


I don't know mysql syntax, but it seems like the following would give you the same results.

SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, a.lang1
FROM xwrh9_betty_dictionary_ids a
LEFT JOIN xwrh9_betty_dictionary_words b ON b.id=a.lang1 OR b.id=a.lang2
WHERE b.id<>19
GROUP BY b.id;


Proper way to store a value that could be multiple different types

Based on what you have said I would use the following general schema:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
)
CREATE TABLE [dbo].[PollOption]
(
    [PollOptionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollQuestionId] INT NOT NULL,  -- Link to the question here because options aren't shared across questions
    [OptionText] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL  -- Remove this if you don't need to hide options

    CONSTRAINT [FK_PollOption_PollQuestionId_to_PollQuestion_PollQuestionId] FOREIGN KEY ([PollQuestionId]) REFERENCES [dbo].[PollQuestion]([PollQuestionId])
)
CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

You don't really care if the answer is a number, date, word, etc. because the data is an answer to a question not something you need to operate on directly.  Furthermore the data only has meaning in context to the question. As such an nvarchar is the most versatile human readable mechanism for storing the data.

The question and the potential answers would be gathered from the first user and inserted into the PollQuestion and PollOption tables.  The second user who answers the questions would select from a list of answers (true/false = list of 2).  You can also expand the PollQuestion table to include the creator's user id if appropriate in order to track the questions they create.

On your UI the answer the user selects can be tied to the PollOptionId value.  Together with the PollQuestionId you can verify that the answer is valid for the question quickly.  Their response if valid would be entered in the PollResponse table.

There are a couple potential problems depending on the details of your use case. If the first user wants to use a math question, and you don't want to offer multiple possible answers.  Another situation is if the options the initial user provides aren't the only options the second user can choose.  You could rework this schema as follows to support these additional use cases.

CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NULL,
    [PollQuestionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [AlternateResponse] NVARCHAR(50) NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

I would also probably add a check constraint to make sure that either an option is provided or an alternate response, but not both (option and alternate response), depending on your needs.

Edit: Communicating datatype for AlternateResponse.

In a perfect world we could use the concept of generics to handle various datatypes for the AlternateReponse.  Alas we don't live in a perfect world.  The best compromise I can think of is to specify what the AlternateResponse datatype should be in the PollQuestion table, and store the AlternateReponse in the database as an nvarchar. Below is the updated question schema, and the new datatype table:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [QuestionDataTypeId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
    -- Insert FK here for QuestionDataTypeId
)
CREATE TABLE [dbo].[QuestionDataType]
(
    [QuestionDataTypeId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Description] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
)

You can list all available data types for question creators by selecting from this QuestionDataType table.  Your UI can reference the QuestionDataTypeId to select the proper format for the alternate response field.  You aren't limited to TSQL data types, so "Phone Number" can be a data type and you will get appropriate formatting/masking on the UI.  Also if required you can cast your data to the appropriate types via a simple case statement in order to do any kind of processing (select, validation, etc.) on the alternate answers.


Cannot remotely connect to SQL Server 2012 VM on Azure

basic things to check:

new firewall inbound rule inside the VM enabling sqlservr.exe. You can make it as restrictive as you want but start with allowing all inbound connections first to verify it works then lockdown one thing at a time
new firewall inbound rule inside the VM enabling sqlbrowser.exe. Needed if running named instance.
new endpoint for the Azure VM (via Azure portal) to enable SQL
Server port. Some users specify some arbitrary unused port for public
and SQL Server's default 1433 for private to reduce noise from port
scanners that roam the internet looking for common targets. There are pros/cons to this or using default for both. You can
decide what's appropriate or talk to your security folks for
guidance.
TCP/IP enabled for SQL Server, should be enabled by default but takes 30s
to verify via Configuration Manager
SQL Server enabled for remote access, yes another obvious one but never hurts to verify


Is it OK to duplicate parent field in child table for performance?

What you are doing is called denormalization.

The rule of thumb is "normalize till it hurts, denormalize till it works."  Denormalization should follow the access pattern of your queries.  If you have a common query that goes from joining on 10 tables to just a few with just a small bit of duplication, go for it!


SQL Server 2008 R2 - how to repair System Views

AFAIK, your best bet is to copy all the tables to another database.  If you're using SQL Server express, this is something you have to do by hand, using SQL Queries or scripts.

It's a good idea to check for possible causes too: run a memory diagnostic, and a disk check.


Both Primary and Foreign Key Needed on Table?

That part did not make sense to me either. The OP (of that question) states:

"One deal can have many deal categories."

I assume that there can also be many deals in a deal category. So, the "proper" way to would be a DealCategory table:

DealCategory
--------------
DealCategoryId (PK)

and a DealInCategory many-to-many table, with:

DealInCategory
--------------
DealId (FK to Deal)
DealCategoryId (FK to DealCategory)
PK: (DealId, DealCategoryId)


How replication happens:

DMLs on Master are written to Binary logs.
Slave connects to master from it's IO thread and reads the binary logs. Save them as Relay log.
Slave's SQL Thread execute the Relay log SQLs to apply the changes.

Now, if you need slave's changes to be reflected on master, you need your master to be replicating from your slave as well. That's known as master-master replication. You can find plenty of posts around that.

By default, all the databases are replicated. If you want to replicate only few of the objects (DB / tables) there are replication filters for that.


ERROR: ORA-01031: insufficient privileges

since I'm the only one using the computer I'm assuming it's the same as "Administrator"

That's not enough to be an Administrator.

Make sure that your user belongs to ORA_DBA OR ORA_<%ORACLE_SID%>_DBA OS group on Windows.

As well check the value set for SQLNET.AUTHENTICATION_SERVICES parameter on sqlnet.ora(%ORACLE_HOME%\network\admin) file. NTS should be included in the value.

SQLNET.AUTHENTICATION_SERVICES = (NTS)


Recovery of a corrupted database using only the .frm in Windows

unfortunately it is impossible, maximum possible restore structure
InnoDB data stored in *.ibd files if set innodb_file_per_table=1 or in idbata1 if it 0.

for success repair even when innodb_file_per_table = 1, ibdata1 need as well.

if You have only from file = no chances restore data at all


Hierarchical permissions in a table stored hierarchy

Using this model, I have come up with a way to query the Pages table in the following manner:

SELECT
  p.*
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, @PermissionName) AS ps
WHERE
  ps.IsAllowed = 1
;

The GetPermissionStatus inline table-valued function's result can be either an empty set or one single-column row. When the result set is empty, that means that there are no non-NULL entries for the specified page/user/permission combination. The corresponding Pages row is automatically filtered out.

If the function does return a row, then its only column (IsAllowed) will contain either 1 (meaning true) or 0 (meaning false). The WHERE filter additionally checks that the value must be 1 for the row to be included in the output.

What the function does:

walks the Pages table up the hierarchy to collect the specified page and all its parents into one row set;
builds another row set containing all the roles the specified user is included in, along with one of the permission columns (but only non-NULL values) – specifically the one corresponding to the permission specified as the third argument;
finally, joins the first and second set via the RolePages table to find the complete set of explicit permissions matching either the specified page or any of its parents.

The resulting row set is sorted in the ascending order of permission values and the topmost value is returned as the result of the function. Since nulls are filtered out at an earlier stage, the list can contain just 0s and 1s. Thus, if there is at least one "deny" (0) in the list of permissions, that will be the result of the function. Otherwise the topmost result will be 1, unless the roles corresponding to the selected pages happen to have no explicit "allows" either or there are just no matching entries for the specified page and user at all, in which case the result will be an empty row set.

This is the function:

CREATE FUNCTION dbo.GetPermissionStatus
(
  @PageId int,
  @UserId int,
  @PermissionName varchar(50)
)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        x.IsAllowed
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
        CROSS APPLY
        (
          SELECT
            CASE @PermissionName
              WHEN 'Create' THEN [Create]
              WHEN 'Read'   THEN [Read]
              WHEN 'Update' THEN [Update]
              WHEN 'Delete' THEN [Delete]
            END
        ) AS x (IsAllowed)
      WHERE
        ur.User_Id = @UserId AND
        x.IsAllowed IS NOT NULL
    )
  SELECT TOP (1)
    perm.IsAllowed
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
  ORDER BY
    perm.IsAllowed ASC
);

Test case

DDL:

CREATE TABLE dbo.Users (
  Id       int          PRIMARY KEY,
  Name     varchar(50)  NOT NULL,
  Email    varchar(100)
);

CREATE TABLE dbo.Roles (
  Id       int          PRIMARY KEY,
  Name     varchar(50)  NOT NULL,
  [Create] bit,
  [Read]   bit,
  [Update] bit,
  [Delete] bit
);

CREATE TABLE dbo.Pages (
  Id       int          PRIMARY KEY,
  ParentId int          FOREIGN KEY REFERENCES dbo.Pages (Id),
  Name     varchar(50)  NOT NULL
);

CREATE TABLE dbo.UserRoles (
  User_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Users (Id),
  Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
  PRIMARY KEY (User_Id, Role_Id)
);

CREATE TABLE dbo.RolePages (
  Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
  Page_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Pages (Id),
  PRIMARY KEY (Role_Id, Page_Id)
);
GO

Data inserts:

INSERT INTO
  dbo.Users (ID, Name)
VALUES
  (1, 'User A')
;
INSERT INTO
  dbo.Roles (ID, Name, [Create], [Read], [Update], [Delete])
VALUES
  (1, 'Role R', NULL, 1, 1, NULL),
  (2, 'Role S', 1   , 1, 0, NULL)
;
INSERT INTO
  dbo.Pages (Id, ParentId, Name)
VALUES
  (1, NULL, 'Page 1'),
  (2, 1, 'Page 1.1'),
  (3, 1, 'Page 1.2')
;
INSERT INTO
  dbo.UserRoles (User_Id, Role_Id)
VALUES
  (1, 1),
  (1, 2)
;
INSERT INTO
  dbo.RolePages (Role_Id, Page_Id)
VALUES
  (1, 1),
  (2, 3)
;
GO

So, just one user is used but it is assigned to two roles, with various combinations of permission values between the two roles to test the blending logic on child objects.

The page hierarchy is very simple: one parent, two children. The parent is associated with one role, one of the children with the other role.
Test script:

DECLARE @CurrentUserId int = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Create') AS perm WHERE perm.IsAllowed = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Read'  ) AS perm WHERE perm.IsAllowed = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Update') AS perm WHERE perm.IsAllowed = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Delete') AS perm WHERE perm.IsAllowed = 1;

Cleanup:

DROP FUNCTION dbo.GetPermissionStatus;
GO
DROP TABLE dbo.UserRoles, dbo.RolePages, dbo.Users, dbo.Roles, dbo.Pages;
GO

Results

for Create:

Id  ParentId  Name
--  --------  --------
2   1         Page 1.1

There was an explicit true for Page 1.1 only. The page was returned according to the "true + not defined" logic. The others were "not defined" and "not defined + not defined" – hence excluded.
for Read:

Id  ParentId  Name
--  --------  --------
1   NULL      Page 1
2   1         Page 1.1
3   1         Page 1.2

An explicit true was found in the settings for Page 1 and for Page 1.1. Thus, for the former it was just a single "true" while for the latter "true + true". There were no explicit read permissions for Page 1.2, so it was another "true + not defined" case. So, all three pages were returned.
for Update:

Id  ParentId  Name
--  --------  --------
1   NULL      Page 1
3   1         Page 1.2

From the settings, an explicit true was returned for Page 1 and a false for Page 1.1. For the pages that made it into the output the logic was the same as in case of Read. For the excluded row both false and true were found and so the "false + anything" logic worked.
for Delete there were no rows returned. The parent and one of the children had explicit nulls in the settings and the other child did not have anything.

Get all permissions

Now if you want just to return all effective permissions, you can adapt the GetPermissionStatus function:

CREATE FUNCTION dbo.GetPermissions(@PageId int, @UserId int)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        r.[Create],
        r.[Read],
        r.[Update],
        r.[Delete]
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
      WHERE
        ur.User_Id = @UserId
    )
  SELECT
    [Create] = ISNULL(CAST(MIN(CAST([Create] AS int)) AS bit), 0),
    [Read]   = ISNULL(CAST(MIN(CAST([Read]   AS int)) AS bit), 0),
    [Update] = ISNULL(CAST(MIN(CAST([Update] AS int)) AS bit), 0),
    [Delete] = ISNULL(CAST(MIN(CAST([Delete] AS int)) AS bit), 0)
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
);

The function returns four columns – the effective permissions for the specified page and user. Usage example:

DECLARE @CurrentUserId int = 1;
SELECT
  *
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissions(p.Id, @CurrentUserId) AS perm
;

Output:

Id  ParentId  Name      Create Read  Update Delete
--  --------  --------  ------ ----- ------ ------
1   NULL      Page 1    0      1     1      0
2   1         Page 1.1  1      1     0      0
3   1         Page 1.2  0      1     1      0


Why do I keep getting ORA-01033: ORACLE initialization or shutdown in progress when my database is open? (12c)

I can't tell you why this happens, but I get such errors regularly with the pluggable database. What I do as a quick fix is I connect using sqlplus "/ as sysdba", then I run the following code for that specific database:

alter pluggable database orclpdb open;

Or the following for all databases:

alter pluggable database all open;

And I stop getting the errors for a while... until the next time it happens. :)
At least I have a quick procedure as a fix.


Data Warehouse indexing approach - to verify

You mention that you added single-column indexes for each foreign key in your fact table. Often at least some of the foreign keys have low cardinality so they are likely not useful in an index on their own.
https://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

They may be more useful as part of a multi-column index which you can design based on the way that you expect users to query the table.

If your workload suits it, then non-clustered columnstore index should be considered on large dimension tables and fact tables. They are ideally suited for data warehouse workloads. http://www.nikoport.com/2016/03/07/columnstore-indexes-part-79-loading-data-into-non-updatable-nonclustered-columnstore/

Since you are using 2014 then non-clustered is the only option if you want to keep constraints and other indexes.


MyISAM is especially bad about garbage collection.  That is one of several reasons you really should move to InnoDB.  MyISAM will be removed in the next release.

MyISAM, more so than InnoDB, can sometimes benefit from OPTIMIZE TABLE.  That is the command for freeing up wasted space and otherwise defragmenting a table.  It is tedious and takes a long time (depending on size of table).

As already pointed out, InnoDB, with FOREIGN KEYs, could take care of the deleting.

I would prefer to have an API that handles the delete -- plus any cleanup that might be needed.  Built into that API could be

Cascading delete, or
Application code to do the delete(s), or
A stored procedure to do the delete(s).
A cron job "because it could take a long time to do all the deleted".

A cron job?  -- Suggest you have the app copy the deleted userid into a ToBeDeleted table.  This could be done with a simple TRIGGER.  Then the cron job would select one row from that table, proceed to do deletes from various other tables (COMMITting as it goes), and finally delete from ToBeDeleted.  Rinse and Repeat.

Better yet, don't make it a cron job, make it a continually running job.  (OK, add a "keep-alive" cron job to make sure it does not vanish, especially after a reboot.)  When this continual job finds nothing to do, it sleeps for, say, a minute.  Otherwise, it runs full-tilt.


Is it safe to change the name of a database?

If you have queries that reference the full database name (which you likely will have if you have cross database queries), they will break. If you have Imports or reports that reference the full database name those will break. If you have linked servers (SQL server), those will break.  If you have other applications that hit the same database backend, those will likely break.

If you have none of those situations, you may be OK. However, you will still want to do this only if you have a full test suite to be able to verify that nothing broke. Keeping the database name and changing how the application connects is a far safer choice if you don't have a good test suite.


What is the correct model for related tables with millions of rows?

You're right. Your tables would look something like this then ( oracle ddl ) :

create table Users (id int,
                    name varchar2(1000),
                    constraint pk_users primary key (id)
);

create table Questions (id int,
                        user_id int,
                        content CLOB,
                        question_date date,
                        constraint pk_questions primary key (id),
                        constraint fk_questions_user_id foreign key (user_id) references users(id)
                        );

create table Answers (id int,
                      question_id int,
                      user_id int,
                      content CLOB,
                      answer_date date,
                      constraint pk_answers primary key (id),
                      constraint fk_answers_questions foreign key (question_id) references Questions(id),
                      constraint fk_answers_user_id foreign key (user_id) references users(id)
);


Create phone call with Mockaroo for Neo4j

In that guide, START_DATE and END_DATE are expressed in UNIX epoch time, which is the number of seconds that have elapsed since the beginning of 1970 (in the UTC time zone).

By the way, you will get much better support for neo4j questions at stackoverflow.com.


Where to find the installer for SQL Server Native Client 11.0.7462?

Here are the steps.

Download patch Description of the security update for SQL Server 2012 SP4 GDR: January 12, 2018 which is version 11.0.7464.
Only update native client (ignore other component) following these instructions.


Forcing a hash join on a temporary table generates error

What you're running into isn't because of the temp table, it's because of the type of join you've chosen with the hint you're forcing.

Both Merge and Hash joins require at least one equality predicate to run.

For more information, check out these old (but awesome) blog posts from Craig Freedman.

Introduction to Joins

Nested Loops Join

Merge Join

Hash Join


What are all the ways that a SELECT statement could be made to not terminate or take a *very* long time?

MariaDB-10.1.1 has max_statement_time to abort long-running statements.  I don't think MySQL has anything equivalent.
(Of course, you need at least 10.2 to get Recursive CTEs.)


Can't find source code for building mysql

Funny - I'm just doing this myself now (assuming Linux (as per Alex Kuzminsky - I use Xubuntu - no heavy GUI desktop).

Get the source (all source downloads here (select from dropdown), Linux source here).

Run (depends on your Linux distro)

sudo apt-get install cmake
sudo apt-get install bison flex
sudo apt-get install libaio1
sudo apt-get install g++ (Ooops!)
sudo apt-get install libncurses5-dev

This was all I needed to do after a fresh (Oracle Virtual Box) install of Xubuntu 14.04 LTS.

Then, what I do is follow the instructions here.

Create a directory "sandbox" beside mysql-5.6.16.

cmake ../mysql-5.6.19
make
make package

then install the mysql-5.6.19-linux_64.tar.gz package (or whatever it's called)

Works nicely for those of use who want to control things and even run several MySQL servers on the same machine using different ports for example.


Is GUID(uniqueidentifier) not suitable for a clustered index?

It will slow down inserts rather than queries. That's because it is essentially inserting a record somewhere randomly inside the existing records, instead of just whacking it on the end (as it would if you were using an identity or sequential dates).

You can somewhat reduce this by using NEWSEQUENTIALID instead of NEWID (assuming the GUID is generated by the database rather than your application).


enable the data access for the current server - is this operation logged somewhere?

No, calls to sp_serveroption are not logged to the default trace, the system_health XE session, or the SQL Server error log. If you want to audit these, you may be able to do it with a server-level audit. Or don't give everyone permissions to do it...


Is it possible to ignore query hints in SQL Server 2012?

I spent a while messing with this. And while there's not a way to get SQL to ignore a forced join type at the table level (see Kendra Little's excellent video and related links), there is something kinda sneaky you might be able to do to get them to change the code.

In SQL Server 2012 (SP3+), there are hints to toggle Memory Grants. Since Hash Joins are memory consuming operators, you could use a Plan Guide to severely limit the memory your query acquires, slowing it down immensely.

Take this simple example query:

SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;

The plan looks pretty normal.

The Hash Join stays in memory, because the query acquires a ~2.1GB memory grant.

Creating a bad plan:

If we create a plan guide and use the MAX_GRANT_PERCENT hint to cap the memory at 0...

EXEC sp_create_plan_guide
@name = N'brimful of hash joins',
@stmt =  N'SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(MAX_GRANT_PERCENT = 0)'
GO

Our query gets a similar looking plan...

But now the Hash Join spills dismally to disk.

Because it got a nonsense memory grant of around ~8mb.

Further evidence of how awful this query got is in SET STATISTICS TIME, IO ON

No Guide:
Table 'Users'. Scan count 7, logical reads 113953,
Table 'Posts'. Scan count 7, logical reads 9777604,
Table 'Workfile'. Scan count 0, logical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0,

 SQL Server Execution Times:
   CPU time = 40001 ms,  elapsed time = 6992 ms.

Guided By Voices:
Table 'Users'. Scan count 7, logical reads 113953,
Table 'Posts'. Scan count 7, logical reads 9776298,
Table 'Workfile'. Scan count 3312, logical reads 362880, physical reads 56144, read-ahead reads 446000,
Table 'Worktable'. Scan count 0, logical reads 0,

 SQL Server Execution Times:
   CPU time = 66593 ms,  elapsed time = 34270 ms.

The query with the rotten plan guide is in decidedly worse shape.

Is this a good idea?

Well, no, but it doesn't seem like that join hint is so hot, either.

For what it's worth, I tried some other stuff to get the hint to ignored, but they didn't seem to work (no plan change):

@hints = N'OPTION(LOOP JOIN, MERGE JOIN, HASH JOIN, TABLE HINT(p, FORCESEEK))'

Or resulted in an error:

@hints = N'OPTION(LOOP JOIN, MERGE JOIN, TABLE HINT(p, FORCESEEK))'

Msg 1042, Level 16, State 1, Line 64
Conflicting JOIN optimizer hints specified.

Msg 10516, Level 16, State 1, Procedure sp_create_plan_guide, Line 20 [Batch Start Line 59]
Cannot create plan guide 'brimful of hash joins' because @module_or_batch can not be compiled.

I don't expect you to do this, but it was an interesting enough question for me to want to come up with some response (note: not answer!) to.

And, you know, I thought the results were funny.


TSQL Solution to backup ALL databases on ALL instances of a server?

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among other things).

The question is though: Is there a reason why you want to back up all databases on all instances from one server? Having all your backup eggs in one basket means that none of your backups get done for as long as that master server (or at least SQL Server Agent) is offline. Having backup jobs on each server is the standard for good reason (regardless of how they're administered).

Rather than writing your own backup statements, I highly recommend using Ola Hallengrens Maintenance Solution (the whole maintenance package is worth using). It just works, and works very, very well. It gives you options you won't have thought of, but will come to need in the future.

Armed with that script, you can iterate through a list of your instances, and execute MaintenanceSolution.sql (which you downloaded from Olas site) against each server. I've done this using a batch file that uses SQLCMD with a trusted connection to each server, and outputs results to a CSV for each server (assuming you've got privileges on each instance). If you use the Registered Servers view in SSMS, you can also execute that script against all servers in a given group at once.

Outside of that, you're looking at something like redgate SQL Backup Pro (I've no affiliation with them), Idera SQL Safe Backup (again, no affiliation) to do what you want, but those products do cost money outside of their respective trial periods.

Either of these solutions will give you far greater safety than scheduling backups for all databases on all servers from one instance.


How to get identical values for different customers

First, a simple test-bed setup:

USE tempdb;

IF OBJECT_ID(N'tempdb..#vals', N'U') IS NOT NULL
DROP TABLE #vals
CREATE TABLE #vals
(
    CustomerID int NOT NULL
    , AccountName varchar(25) NOT NULL
);

INSERT INTO #vals (CustomerID, AccountName)
VALUES (1, 'Test Insurance 1')
    , (1, 'Test Insurance 2')
    , (1, 'Test Insurance 3')
    , (2, 'Test Insurance 4')
    , (3, 'Test Insurance 5')

Assuming you want separate columns for each account name, you could use a PIVOT transform, combined with CROSS APPLY to do that:

SELECT *
FROM (
SELECT *
FROM #vals v
    CROSS APPLY (VALUES (AccountName))dummy(val)
) src
PIVOT (
    MAX(val)
    FOR AccountName IN (
        [Test Insurance 1]
        , [Test Insurance 2]
        , [Test Insurance 3]
        , [Test Insurance 4]
        , [Test Insurance 5]
    )
) pvt;

The CROSS APPLY creates a third column that we can use in the aggregate part of the PIVOT statement.

The results:

╔════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╗
║ CustomerID ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║ Test Insurance 4 ║ Test Insurance 5 ║
╠════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╣
║ 1          ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║ NULL             ║ NULL             ║
║ 2          ║ NULL             ║ NULL             ║ NULL             ║ Test Insurance 4 ║ NULL             ║
║ 3          ║ NULL             ║ NULL             ║ NULL             ║ NULL             ║ Test Insurance 5 ║
╚════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╝

Unfortunately, the above solution creates more columns than your question asks for.  You might be interested in a solution that limits the number of columns to some predetermined number, as in:

;WITH src AS (
    SELECT v.CustomerID
        , v.AccountName
        , rn = ROW_NUMBER() OVER (PARTITION BY v.CustomerID ORDER BY v.AccountName)
    FROM #vals v
)
, s2 AS (
    SELECT src.CustomerID
        , Acct1 = CASE WHEN src.rn = 1 THEN src.AccountName ELSE NULL END
        , Acct2 = CASE WHEN src.rn = 2 THEN src.AccountName ELSE NULL END
        , Acct3 = CASE WHEN src.rn = 3 THEN src.AccountName ELSE NULL END
    FROM src
)
SELECT s2.CustomerID
    , Acct1 = MAX(s2.Acct1)
    , Acct2 = MAX(s2.Acct2)
    , Acct3 = MAX(s2.Acct3)
FROM s2
GROUP BY s2.CustomerID

The solution above requires you to predetermine the maximum number of columns you need to support.

The results:

╔════════════╦══════════════════╦══════════════════╦══════════════════╗
║ CustomerID ║      Acct1       ║      Acct2       ║      Acct3       ║
╠════════════╬══════════════════╬══════════════════╬══════════════════╣
║          1 ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║
║          2 ║ Test Insurance 4 ║ NULL             ║ NULL             ║
║          3 ║ Test Insurance 5 ║ NULL             ║ NULL             ║
╚════════════╩══════════════════╩══════════════════╩══════════════════╝

If you are looking for the accounts to be included in a single column, you could use a subquery to obtain the list of accounts for each customer, something like this:

SELECT v.CustomerID
    , STUFF((SELECT ', ' + v1.AccountName FROM #vals v1 WHERE v1.CustomerID = v.CustomerID FOR XML PATH('')), 1, 2, '')
FROM #vals v
GROUP BY v.CustomerID
ORDER BY v.CustomerID;

╔════════════╦══════════════════════════════════════════════════════╗
║ CustomerID ║ (No column name)                                     ║
╠════════════╬══════════════════════════════════════════════════════╣
║ 1          ║ Test Insurance 1, Test Insurance 2, Test Insurance 3 ║
║ 2          ║ Test Insurance 4                                     ║
║ 3          ║ Test Insurance 5                                     ║
╚════════════╩══════════════════════════════════════════════════════╝

If you are using SQL Server 2017, you can take advantage of the STRING_AGG functionality to accomplish the same thing in a more elegant manner:

SELECT v.CustomerID
    , STRING_AGG(v.AccountName, ', ')
FROM #vals v
GROUP BY v.CustomerID

+------------+------------------------------------------------------+
| CustomerID |                   (No column name)                   |
+------------+------------------------------------------------------+
|          1 | Test Insurance 1, Test Insurance 2, Test Insurance 3 |
|          2 | Test Insurance 4                                     |
|          3 | Test Insurance 5                                     |
+------------+------------------------------------------------------+


What version of Postgres are you using?

If I understand correctly, you would like to match records with similar words. There are several ways to approach this. I'd suggest taking a look at a few features and seeing if one sounds like a good match for you.

The pg_trgrm extension included with standard distributions is worth considering:
https://www.postgresql.org/docs/current/pgtrgm.html

This tool breaks words into a chunks of three letters, and then (with the help of an index) quickly compares two strings to see how similar they are, based on the three letter chunks.

Trigrams are a good general-purpose approach, but you sound like you are matching on real words, not just random strings. What you describe sounds like the process used to prepare words for a "lexical" Full Text Search in Postgres:

https://www.postgresql.org/docs/current/textsearch.html

The idea with a "lexical" analysis is that strings are converted into a common set of base words. For example, in English, singular, plural, and different tenses are converted into a common word to simplify certain kinds of matches. Sometimes the "word" isn't even a real word, it's just a common root-like word used for this purpose.

In your case, I believe that 'Botol-botol' and 'Botol' would both be converted to 'botol'. Likewise, "merdeka-merdeka" and "merdeka" could be translated to "merdeka" for comparison. It's even possible to have a thesaurus, so perhaps "mandiri" could also be translated to "merdeka". For all of this to work automaticall requires an Indonesian dictionary for Postgres. I just checked my installation, and do not find Malay or Indonesian:

SELECT dictname FROM pg_catalog.pg_ts_dict;

Perhaps someone knows how to add Indonesian to Postgres?

If there is no suitable dictionary for your language available, you may be able to make progress with one of the installed dictionaries. There you will get a list of words parsed. There's only one text parser in Postgres, and I do not believe that it will break words on the - character, which would be useful in your case. To work around this, I've used the replace function to change hyphens into spaces so that the Postgres parser will see each part as a distinct word. Perhaps this is a terrible idea, but it should help you see how the system can work.

/* Toli-toli, Bagan Siapi-api, Fakfak, Oro-oro Ombo */
select to_tsvector('simple', replace('Toli','-',' ')) union all
select to_tsvector('simple', replace('Toli-toli','-',' ')) union all
select to_tsvector('simple', replace('Bagan Siapi-api','-',' ')) union all
select to_tsvector('simple', replace('Fakfak','-',' ')) union all
select to_tsvector('simple', replace('Oro-oro Ombo','-',' '))

Below are the results:

to_tsvector
'toli':1
'toli':1,2
'api':3 'bagan':1 'siapi':2
'fakfak':1
'ombo':3 'oro':1,2

Notice that "Toli" and "Toli-toli" both become "toli", which is what I think you are trying to accomplish.

Best would be a language adapted to your language, of course.


MS Access: Issue with Autonumbers in the export

You will need to write a query or report that joins between the tables so you can select the description.

The Image below shows the MS Access query designer. It displays both tables and the join between them. Below that it shows the fields I want to return in my query.

You can also view the SQL Query, in this example it would look like this:

SELECT Inventory.Item_id, Inventory.Item_name, Media.Description
FROM Inventory INNER JOIN Media ON Inventory.Media = Media.Media_id;

The query result look like this:


Star join query optimization - Change partitioning, use columnstore?

Thanks for adding the query plan; it is very informative.  I have a number of recommendations based on the query plan, but first a caveat: don't just take what I say and assume it's correct, try it out (ideally in your testing environment) first and make sure you understand why the changes do or don't improve your query!

The query plan: an overview

From this query plan (as well as the corresponding XML), we can immediately see a few useful pieces of information:

You are on SQL 2012
This is a classic star join query and you are getting the benefit of the in-row bitmap filter optimization that was added in SQL 2008 for such plans
The fact table contains about 1.5 billion rows, and just over 500 million of those rows match the dimension filters
The query requests 72GB of memory, but is only granted 12GB of memory (presumably, 12GB is the max that will be granted to any given query, meaning your machine likely has ~64GB of memory)
SQL Server is performing a sort-stream aggregate that takes 500 million rows down to just 600,000 rows.  The sort is exceeding it's memory grant and spilling to tempdb
We have warnings for plan-affecting converts due to either explicit and implicit conversions in your query
The query uses 32 threads, but the initial seek into your fact table has an enormous thread skew; just 2 of the 32 threads do all of the work.  (At subsequent steps in the query plan, however, the work is more balanced.)

Optimization: columnstore or not

This is a tough question, but on balance I would not recommend columnstore for you in this case.  The primary reason is that you are on SQL 2012, so if you are able to upgrade to SQL 2014 I think it might be worth trying out columnstore.

In general, your query is the type that columnstore was designed for and could benefit greatly from the reduced I/O of columnstore and the greater CPU efficiency of batch mode.

However, the limitations of columnstore in SQL 2012 are just too great, and the tempdb spill behavior, where any spill will cause SQL Server to abandon batch mode entirely, can be a devastating penalty that might come into play with the large volumes of rows you are working with.  If you do go with columnstore on SQL 2012, be prepared to baby-sit all of your queries very closely and ensure that batch mode can always be used.

Optimization: more partitions?

I don't think that more partitions will help this particular query.  You are welcome to try it, of course, but keep in mind that partitioning is primarily a data management feature (the ability to swap in new data in your ETL processes via SWITCH PARTITION and not a performance feature. It can obviously help performance in some cases, but similarly it can hurt performance in others (e.g., lots of singleton seeks that now have to be performed once per partition).

If you do go with columnstore, I think that loading your data for better segment elimination will be more important than partitioning; ideally you will probably want as many rows in each partition as possible in order to have full columnstore segments and great compression rates.

Optimization: improving cardinality estimates

Because you have a huge fact table and a handful of very small (hundreds or thousands of rows) set of rows from each dimension table, I would recommend an approach where you explicitly create a temporary table containing only the dimension rows that you plan to use.  For example, rather than join to Dim_Date with a complicated logic like cast(right(ALHDWH.dwh.Dim_Date.Financial_Year,4) as int)  IN  ( 2015, 2014, 2013, 2012, 2011  ), you should write a pre-proccessing query to extract only the rows from Dim_Date that you care about and add the appropriate PK to those rows.

This will allow SQL Server to create statistics on just the rows you are actually using, which may yield better cardinality estimates throughout the plan.  Because this pre-processing would be such a trivial amount of work compared to the overall query complexity, I would highly recommend this option.

Optimization: reducing thread skew

It's likely that extracting the data from Dim_Date into it's own table and adding a primary key to that table would also help to reduce thread skew (an imbalance of work across threads).  Here's a picture that helps show why:

In this case, the Dim_Date table has 22,000 rows, SQL Server estimated that you are going to use 7,700 of those rows, and you actually only used 1,827 of those rows.

Because SQL Server uses statistics in order to allocate ranges of rows to threads, the poor cardinality estimates in this case are likely the root cause of the very poor distribution of rows.

Thread skew on 1,872 rows may not matter much, but the painful point is that this then cascades down to the seek into your 1.5 billion row fact table, where we have 30 threads sitting idle while 600 million rows are being processed by 2 threads.

Optimization: getting rid of the sort spill

Another area I would focus on is the sort spill. I think that the primary problem in this case is poor cardinality estimates.  As we can see below, SQL Server thinks that the grouping operation being performed by the combination of a Sort and Stream Aggregate will yield 324 million rows.  However, it actually yields just 643,000 rows.

If SQL Server knew that so few rows would come out of this grouping, it would almost certainly use a HASH GROUP (Hash Aggregate) rather than a SORT GROUP (Sort-Stream) in order to implement your GROUP BY clause.

It's possible that this may fix itself if you make some of the other changes above in order to improve cardinality estimates.  However, if it doesn't you could try to use the OPTION (HASH GROUP) query hint in order to force SQL Server to do so. This would let you evaluate the magnitude of the improvement and decide whether or not to use the query hint in production.  I would generally be cautious about query hints, but specifying just HASH GROUP is a much lighter touch than something like using a join hint, using FORCE ORDER,  or otherwise taking too much of the control out of the query optimizer's hands.

Optimization: memory grants

One last potential problem was that SQL Server estimated that the query would want to use 72GB of memory, but your server was not able to provision this much memory to the query.  While it's technically true that adding more memory to the server would help, I think there are at least a couple other ways to attack this problem:

Get rid of the Sort operator (as described above); it's really the only operator consuming any substantial memory grant in your query
Split your query up into multiple batches; it may be the case that you can run the query once per partition, for example.  This could reduce the size of the sort, keep it in memory, and potentially improve performance significantly. A side benefit could be that you might get better utilization of threads if you access just one partition since this does impact the way that SQL Server allocates threads to partitions in some cases.


exp and expdp are OS command-line programs.  That's why SQL*Plus doesn't recognise them.  You need to fire up a console (shell) window.

Note that you can run these wherever makes more sense, your local PC or on the database server itself, provided the Oracle client software is installed.

There is an in-database API for Datapump.   It's the PL/SQL package, DBMS_DATAPUMP.  Find out more.  However, for what your want to do, expdp makes more sense.

  " i'm quite amazazed that nowhere in the command i have specified anything regarding oracle databse" (sic)

Welcome to the magic of paths and scope.  Oracle exp is the first program of that name your computer found in your current direction or by traversing the command path.  So that's what it executed.

exp is an Oracle proprietary utility for exporting data from an Oracle database. It is never going to export anything else.  You didn't have to specify a target database because your environment is XE with only one in scope.  In an enterprise environment you would probably have to specify a database name in the connection string, because there could be several databases on your network.


Is partial dependency possible In Data Base for Foreign key relationship

Something I would be worried about with this approach is what will keep me from having a Student_id of 27 for Kevin, and a Teacher_ID of 27 for Mr. Jones? If you absolutely want to avoid this you could use a single base table for Person, then everyone get's their own number.

In keeping the current plan, I would pull the user_id field from the user table, and add a user_id field to the student and teacher tables. The student table would now have a foreign key to the Primary key of the user table, essentially going the opposite way you have it going here. Now a person's User_ID is part of their record.

CREATE TABLE Teacher( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL, user_id INT NOT NULL);
INSERT INTO [dbo].[Student]([id],[name], [user_id]) VALUES(1,'Raj', 356),(2,'Rahul',222);

CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL, user_id INT NOT NULL);
INSERT INTO [dbo].[Student]([id],[name], [user_id]) VALUES(4,'Ram',123),(5,'Raman',232);

CREATE TABLE User(User_ID INT NOT NULL PRIMARY KEY, User_Type(Char(1) NOT NULL, User_Name NVARCHAR(25) NOT NULL, User_Password NVARCHER(255) NOT NULL)

ALTER TABLE Student
ADD CONSTRAINT FK_Student FOREIGN KEY (User_ID) REFERENCES User (User_ID)
ALTER TABLE Teacher
ADD CONSTRAINT FK_Teacher FOREIGN KEY (User_ID) REFERENCES User (User_ID)


PostgreSQL: How to backup only One Schema from a database and restore it on another server

You can select which schemas to dump with the -n option of pg_dump.  Create a dump of schema B:

pg_dump ...other...options... -Fc -n B >dump.dmp

Restore the dump file:

pg_restore -d somedb dump.dmp

The target database does not have to have the same name as the original one.

Note that you will have problems if schema B has dependencies on schema C.  Then you won't be able to restore it separately.


how to query for rows by specifying the range of row numbers in postgresql

1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?

No.  You can create one by using TIMESTAMP WITH TIME ZONE (timestamptz for short) and setting a default using an appropriate function.  (You can choose the timestamp of the start of database transaction, the start of execution of the INSERT statement, or the moment the row is processed -- whichever best suits your needs.)  Don't be tempted to use TIMESTAMP WITHOUT TIME ZONE (the default if you just say TIMESTAMP) -- you would regret that the first time you hit a daylight saving time boundary.

  2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.

This one is a bit tricky.

You can use non-transactional cursors, but that prevents database maintenance operations for as long as the cursor is open, so you want to make sure that the time is not unbounded.
You can use OFFSET and LIMIT, but that gets slow if you need a big OFFSET, since it has to read through all the preceding rows each time.  It also gets weird if the data is being modified while you are paging around.
You can save values for the ends of what is being displayed and run a query with LIMIT to get the rows just outside that.  This one performs well; the only real down side is programming complexity.
You can run the whole list to storage somewhere outside the database when they first request it, and do your scrolling from that.  It may even be possible to use a temporary table for this, depending on other details.

This is a problem that people are always sorting out, and I don't think that any one answer is the best for all situations.


Entity-Relationship Diagrams: Identifying which way a relationship flows

The flow of ERD relationships go in both directions. Cardinality expresses the rules governing the relationships between entities.  The 'one and only one' of classroom provides you with a verbal logic of - 'A classroom must contain..'.  The 'zero to many' furniture cardinality provides 'zero to many pieces of furniture.'

Don't think of the furniture physically being in the classroom, just that a connection exists between the two.


Log Shipping copy job doesn't delete old files on a secondary node that is part of AlwaysOn configuration

Wouldn't you want to ensure the secondary logs are restored to the secondary server at certain intervals? With log shipping, I thought you ship the logs over to your secondary DB and then restore from those to keep the secondary up-to-date as close as possible to your primary.

Do you see any reason why you would not want to automate the log shipping restore on your secondary DB in the log shipping configuration?

Once those log files are applied to secondary, then the job will purge the logs that are not needed any longer since those are reflected in the secondary DB at that time.

Test it out and see how it goes.

EDIT: @nulldotzero

Okay, since you cannot run restore operations on primary or secondary in an AlwaysOn configuration, and because you are also copying the TRN log files to the seconary as well as the DR for redundancy, then you could just setup a SQL Agent job on the secondary instance to do the below for whatever hours you feel need to be purged.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1

Since the log backup files are purged from the locations which the LSRestore for DR runs and grabs those, then that job will take care of those once the restore job completes successfully.


Add the N prefix to the INSERT statement to preserve the XML as Unicode, eg

    insert into #test (Document) values
(N'<FormDocument xmlns="http://mynamespace.org/Schemas">
  <Fields>
    <Field Id="a" Value="qwerty" />
    <Field Id="ą" Value="123" />
  </Fields>
</FormDocument>')


I think your best bet would be to set in place a mean to monitor the stats of relevant tables and indexes counters:
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW
Specially the:
   seq_scan bigint

   Number of sequential scans initiated on this table

   seq_tup_read bigint

   Number of live rows fetched by sequential scans

   iseq_scan bigint

   Number of sequential scans initiated on this table

   seq_tup_read bigint

   Number of live rows fetched by sequential scans

   idx_scan bigint

   Number of index scans initiated on this table

   idx_tup_fetch bigint

   Number of live rows fetched by index scansdx_scan bigint

   Number of index scans initiated on this table

   idx_tup_fetch bigint

   Number of live rows fetched by index scans

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW
idx_scan bigint

Number of index scans initiated on this index

idx_tup_read bigint

Number of index entries returned by scans on this index

idx_tup_fetch bigint

Number of live table rows fetched by simple index scans using this index


8000 character limit on OPENQUERY against a linked server

You can bypass the 8000 character limit of OPENQUERY by utilizing EXECUTE AT, as follows:

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'SELECT * FROM TABLE WHERE CHARACTERS.... ' -- Imagine that's longer than 8000 characters

EXECUTE (@myStatement) AT LinkedServerName

In order to make sure this doesn't throw an error, you need to enable the RPC OUT option on the linked server, by issuing the following command:

EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'rpc out', @optvalue=N'true'

Or enabling it within the GUI:


This is a shot in the dark, but since I've seen a number of scenarios where this has fixed odd errors in SSMS 2016, close SSMS, clear out all temporary files using the Disk Cleanup utility (cleanmgr.exe), start up SSMS, and then try this operation again.

Worst case you're no better off than you are now, but you've got a little more free disk space.


1236 On Slave After Master Master Failure

I'll bet sync_binlog was turned off.

With it off, the binlog entries just before the crash may not have been flushed to the binlog file, even though they have been sent to the Slave.

Sending replication data from Master to Slave:

Write to the table on the Master.
Buffer up the write to the binlog.
Optionally flush the buffer to the binlog. -- Controlled by sync_binlog
Send the query to the Slave(s).

When sync_binlog=OFF, there is a big chance that the binlog will be shorter than what the slave thinks it should be.

When the Slave-Master connection is reestablished, the Slave picks up where it left off. With sync_binlog=ON, that would be at the exact end of some binlog, and it would decide to move to the next binlog. The manual CHANGE MASTER simulates that.

The CHANGE MASTER to position 0 (or 4) of the next binlog (bump the number by 1).

(I have never used RESET SLAVE; I see not reason for it.)


Remove while loop from query inserting x values

I believe I have solved it with the following cte and cross apply. Thanks to Aaron for letting me think it was simpler than I had it.

;WITH cte AS
(
SELECT
    D.Id, D.WidgetTypeId
FROM
    @Widgets R
    CROSS APPLY
    (
        SELECT
            TOP(R.Required) *
        FROM
            WidgetOrder A
        WHERE
        A.OrderId IS NULL AND A.WidgetTypeId = r.WidgetTypeId) D
)
UPDATE A SET OrderId = 100 FROM WidgetOrder A JOIN cte ON A.Id = cte.Id


Options for Data Encryption in SQL Server 2008 R2 Standard Edition?

Since you are using Standard Edition, you cant use TDE. So other options are

Using encryption keys at instance/database level :

SQL Server has two kinds of keys: symmetric and asymmetric. Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.

Also, you can have encryption at column level by creating a MASTER KEY ENCRYPTION along with CREATE CERTIFICATE and then CREATE SYMMETRIC KEY.

An example of how this can be done is described at Encrypt a Column of Data

Reference : SQL Server and Database Encryption Keys (Database Engine)

At Drive level :

Using BitLocker as it is a Drive Encryption data protection feature available Windows Server 2008 R2. Refer to : BitLocker Drive Encryption Overview There are many opensource or third party software to do the same job but at additional cost.

Note: The most important bit is ALWAYS backup your encryption keys.

You can use third party software like Redgate's sql backup which allows you to encrypt backups using passwords.

Depending on what level you need encryption will determine if it is worth upgrading to enterprise edition or not. You have to evaluate native TDE encryption vs encryption keys and certificates vs open source vs disk encryption.


Any range (eg month>8 or month BETWEEN 8 AND 12) leads to punting on partition pruning, hence 77 partitions are selected.  That is, PARTITION BY HASH is useless for ranges.  Meanwhile, non-partitioning is excellent for the other type of queries -- "point queries".  So don't bother using BY HASH!

Where does id come from?  Typically it is an AUTO_INCREMENT, but apparently not for your table.

select count(*) from emp where gen_year=1984 and month=8;

can be significantly sped up by

Storing date1 as either a TIMESTAMP or DATETIME.  With fractional seconds if needed.
Adding INDEX(date1)
Writing the query thus:

SELECT COUNT(*) FROM EMP
     WHERE date1 >= '1984-08-01'
       AND date1  < '1984-08-01' + INTERVAL 1 MONTH

If you build and maintain a Summary table, then the query becomes 'instantaneous', and even the other query will be faster than

     WHERE date1 >= '1984-09-01'
       AND date1  < '1985-01-01'

InnoDB really needs a PRIMARY KEY.  Promote the UNIQUE key to primary.  (Though I have serious doubts about that index.)

These are all "range" tests:  BETWEEN, >=, <.  All may work well with non-partitioned tables, but fail miserably on PARTITION BY HASH tables (when the range involves the partition-key).


This turns up a lot in the Telco space (where I work).  There are several approaches you can use to get reasonable performance while still remaining entirely in the database.

In all cases, you need to drop the "%" from your DIGITS column, that extra character isn't helping you at all.

Option 1. Iterative function.

DECLARE
    p VARCHAR(30);
BEGIN
    p := :full_number;

    WHILE (LENGTH (p) > 0)
    LOOP
        BEGIN
            SELECT [whatever]
            INTO :whatever
            FROM numbers t
            WHERE digits = p;

            RETURN;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                p := SUBSTR (p, 0, LENGTH (p) - 1);
        END;
    END LOOP;
END;

This kind of function performs reasonably well, assuming you have a nice index on DIGITS.

Option 1. START/END range keys.  Create two secondary columns (BOTH INDEXED).

DIGITS_RANGE_START
DIGITS_RANGE_END

These are derived values that you set by an insert/update trigger.

DIGITS_RANGE_START should be automatically set to DIGITS || CHAR(0)
DIGITS_RANGE_END should be automatically set to DIGITS || CHAR(255)

Now your query can be

SELECT ... FROM numbers t
WHERE (number_to_match || CHR(1) > digits_range_start)
  AND (number_to_match || CHR(1) < digits_range_end) AND ... [other conditions]
ORDER BY LENGTH (digits) DESC;

This will hit the indexes with a nice comparison match, and Oracle should be able to do a decent job.  It may return multiple matches, you need to take the first one.

More Options

See also this famous thread for fancy options with Index Organized Tables if you want to get real clever and real fast.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4246230700346756268


You can use JSON functions to do that for you:

select x.*
from mytest, jsonb_each_text(to_jsonb(mytest)) as x("key",value);

With your sample data, this returns:

key   | value
------+------
col_1 | a1
col_2 | b1
col_1 | a2
col_2 | b2
col_1 | a3
col_2 | b3

to_jsonb(mytest) converts the whole row into a JSON value using the column names as keys and jsonb_each_text() is the unnesting of that JSON value.

Online example: https://rextester.com/DSNOIU44927


SELECT name, COUNT(item_id)
FROM ( SELECT c.name, i.item_id
       FROM categories c
       LEFT JOIN item_category ic ON ic.category_id = c.category_id
       LEFT JOIN items i ON ic.item_id = i.item_id

     UNION ALL

       SELECT c.name, i.item_id
       FROM categories c
       LEFT JOIN subcategories sc ON sc.category_id = c.category_id
       LEFT JOIN item_subcategory isc ON isc.subcategory_id = sc.subcategory_id
       LEFT JOIN items i ON isc.item_id = i.item_id
     ) total
GROUP BY name /* WITH ROLLUP */
;

or

SELECT name, SUM(cnt)
FROM ( SELECT c.name, COUNT(i.item_id) cnt
       FROM categories c
       LEFT JOIN item_category ic ON ic.category_id = c.category_id
       LEFT JOIN items i ON ic.item_id = i.item_id
       GROUP BY c.name

     UNION ALL

       SELECT c.name, COUNT(i.item_id)
       FROM categories c
       LEFT JOIN subcategories sc ON sc.category_id = c.category_id
       LEFT JOIN item_subcategory isc ON isc.subcategory_id = sc.subcategory_id
       LEFT JOIN items i ON isc.item_id = i.item_id
       GROUP BY c.name
     ) total
GROUP BY name /* WITH ROLLUP */
;


Question regarding PSQLException: User was holding shared buffer pin for too long

If the replay process is already behind by more than max_standby_streaming_delay (or max_standby_archive_delay), then it will immediately terminate anything which is in its way.  If it is behind by less, then it is willing to wait only until it reaches that delay.  In other words, the max_standby_streaming_delay sets a grace period for the entire system, not for each individual action within the system.

So if max_standby_streaming_delay is set to 30 seconds, but the replay process is already 28 seconds behind, then "too long" to hold a buffer pin could be 2 seconds.  If is already more than 30 seconds behind, then a few nanoseconds could be "too long"--just long enough to detect the blockage and then send, receive, and process a signal.


2 Identical Servers/SQL Servers Processing Transactions w/ Merge Replication

Have you considered Peer-to-peer transactional replication?
You can find out more comparison here.

  Peer-to-peer transactional replication:  Use When...

  Replication is used to improve scalability and availability.
  Minimal latency is required.
  Data is not partitioned among Subscribers.
  Conflicts typically do not occur, but they must be detected if they do.

  Merge replication:  Use When...

  There are a large number of Subscribers.
  Data is replicated to mobile users.
  Replicated data is frequently updated at the Subscriber.
  Data filtering is needed so that Subscribers receive different    partitions of data.


Get last full backup and transaction log backup for each database

Something like this should get you the output that you're looking for.  The below query pulls the most recent backup of type full, differential, or log backup for each database.

;with backup_cte as
(
    select
        database_name,
        backup_type =
            case type
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        backup_finish_date,
        rownum =
            row_number() over
            (
                partition by database_name, type
                order by backup_finish_date desc
            )
    from msdb.dbo.backupset
)
select
    database_name,
    backup_type,
    backup_finish_date
from backup_cte
where rownum = 1
order by database_name;


EXECUTE ... USING requires host variable names, not expressions, while SUBSTRING(whatever) is an expression. You will need to declare four host variables, assign results of the SUBSTRING(...) expressions to them, then use those variables in the EXECUTE statement.

Alternatively, you could move expressions to the dynamic statement itself:

set insert_into_info = 'INSERT INTO MYLIB.' || info || ' (
      CICUST, CIINF1, CIINF2, CIINF3, CIINF4, CISAD1, CISAD2,
      CISCTY, CISSTA, CISZIP, CISCRY
    ) VALUES (' || NEW_ID || ', substring(?, 1, 40), ' ||
    'substring(?, 41, 80)' || ...
...
EXECUTE s1 using paddedComments, paddedComments, ...


Queries are performing bad after SQL Server 2008 SP4 upgrade

We opened a support ticket with Microsoft and they gave us a tool to run to gather metrics when issue was in progress. I couldn't run the tool during business hours since I didn't want it cause problems for working processes. Then in the evening, update statistics job ran for almost twice long than before. Probably that produced better query plans for queries running on next morning i.e. on Tuesday. It was way better than Monday. I observed the performance today. It's back to normal. I didn't have to use Microsoft tool at all. Go figure! Thank you.


Populating an empty table using loops, conditionals, and accessing multiple tables

I might be misunderstanding your question; however this should work.  (The code is written for SQL Server since I don't have MySQL experience.)

CREATE TABLE TableA (PersonID INT, Points decimal(10,2));
CREATE TABLE TableB (PersonID INT, Points decimal(10,2));
CREATE TABLE TableC (UID INT, Seas INT, Wk INT, V char(3), H char(3));
CREATE TABLE TableD (PersonID INT, Grp char(3));
CREATE TABLE NewTable (PersonID INT, Points decimal(10,2),
        Points_Alt decimal(10,2), Pattern int);

INSERT INTO TableA VALUES (1, 10);
INSERT INTO TableB VALUES (1, 23);
INSERT INTO TableA VALUES (2, 11);
INSERT INTO TableB VALUES (2, 24);
INSERT INTO TableC VALUES (3, 2011, 10, 3, 4)
INSERT INTO TableC VALUES (4, 2011, 10, 5, 6)
INSERT INTO TableD VALUES (1, 3);
INSERT INTO TableD VALUES (2, 6);

INSERT INTO NewTable (PersonID, Points, Points_Alt)
SELECT TableA.PersonID, TableA.Points, TableB.Points AS Points_Alt
FROM TableA
    INNER JOIN TableB ON TableA.PersonID = TableB.PersonID;

UPDATE NewTable
SET Pattern = 3
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011
    AND TableC.Wk = 10
    AND TableC.V = TableD.Grp;

UPDATE NewTable
SET Pattern = 6
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011
    AND TableC.Wk = 10
    AND TableC.H = TableD.Grp;

SELECT * FROM NewTable

The first statement creates the data in NewTable and the 2 remaining statements set Pattern to the particular value based on either H or V as in your pseudo-code.


How to Store text of 15 to 20 KB file data in blob or as text?

If it's the textual content of the files you are concerned with and you need to search that content, store it as [n]varvchar(max) and consider using a full-text index on the column.

If the files are XML, consider storing the content using the xml data type.

If the files are some format other than plain text and you want to preserve that format, use BLOB storage. For SQL Server Server 2012 or newer, you can use FileTables.


Oracle: Trigger to store username after INSERT into column of same table

First of all, in the row-level trigger body you cannot perform DML against the table which fires the trigger. You will get "mutating table" error .
Secondly, you can't modify the row inside AFTER trigger body, only inside BEFORE .   Assuming CREATE_USER field has proper type and size, the trigger can look like

CREATE OR REPLACE TRIGGER BI_TABLE_NAME
BEFORE INSERT ON table_name FOR EACH ROW

BEGIN
    :new.CREATE_USER:= USER;
END;

Another option would be not to use trigger at all, but have a column with default value USER :

create table test_table (test_table_id int not null primary key,
 ...,
created_by varchar2(30) default user);


PostgreSQL UPSERT issue with NULL values

Clarify ON CONFLICT DO UPDATE behavior

Consider the manual here:

  For each individual row proposed for insertion, either the insertion
  proceeds, or, if an arbiter constraint or index specified by
  conflict_target is violated, the alternative conflict_action is taken.

Bold emphasis mine. So you do not have to repeat predicates for columns included in the unique index in the WHERE clause to the UPDATE (the conflict_action):

INSERT INTO test_upsert AS tu
       (name   , status, test_field  , identifier, count)
VALUES ('shaun', 1     , 'test value', 'ident'   , 1)
ON CONFLICT (name, status, test_field) DO UPDATE
SET count = tu.count + 1;
WHERE tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value'

The unique violation already establishes what your added WHERE clause would enforce redundantly.

Clarify partial index

Add a WHERE clause to make it an actual partial index like you mentioned yourself (but with inverted logic):

CREATE UNIQUE INDEX test_upsert_partial_idx
ON public.test_upsert (name, status)
WHERE test_field IS NULL;  -- not: "is not null"

To use this partial index in your UPSERT you need a matching conflict_target like @ypercube demonstrates:

ON CONFLICT (name, status) WHERE test_field IS NULL

Now the above partial index is inferred. However, as the manual also notes:

  [...] a non-partial unique index (a unique index without a predicate) will
  be inferred (and thus used by ON CONFLICT) if such an index satisfying
  every other criteria is available.

If you have an additional (or only) index on just (name, status) it will (also) be used. An index on (name, status, test_field) would explicitly not be inferred. This doesn't explain your problem, but may have added to the confusion while testing.

Solution

AIUI, none of the above solves your problem, yet. With the partial index, only special cases with matching NULL values would be caught. And other duplicate rows would either be inserted if you have no other matching unique indexes / constraints, or raise an exception if you do. I suppose that's not what you want. You write:

  The composite key is made up of 20 columns, 10 of which can be nullable.

What exactly do you consider a duplicate? Postgres (according to the SQL standard) does not consider two NULL values to be equal. The manual:

  In general, a unique constraint is violated if there is more than one
  row in the table where the values of all of the columns included in
  the constraint are equal. However, two null values are never
  considered equal in this comparison. That means even in the presence
  of a unique constraint it is possible to store duplicate rows that
  contain a null value in at least one of the constrained columns. This
  behavior conforms to the SQL standard, but we have heard that other
  SQL databases might not follow this rule. So be careful when
  developing applications that are intended to be portable.

Related:

Allow null in unique column

I assume you want NULL values in all 10 nullable columns to be considered equal. It is elegant & practical to cover a single nullable column with an additional partial index like demonstrated here:

PostgreSQL multi-column unique constraint and NULL values

But this gets out of hand quickly for more nullable columns. You'd need a partial index for every distinct combination of nullable columns. For just 2 of those that's 3 partial indexes for (a), (b) and (a,b). The number is growing exponentially with 2^n - 1. For your 10 nullable columns, to cover all possible combinations of NULL values, you'd already need 1023 partial indexes. No go.

The simple solution: replace NULL values and define involved columns NOT NULL, and everything would work just fine with a simple UNIQUE constraint.

If that's not an option I suggest an expression index with COALESCE to replace NULL in the index:

CREATE UNIQUE INDEX test_upsert_solution_idx
    ON test_upsert (name, status, COALESCE(test_field, ''));

The empty string ('') is an obvious candidate for character types, but you can use any legal value that either never appears or can be folded with NULL according to your definition of "unique".

Then use this statement:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count)
VALUES ('shaun', 1, null        , 'ident', 11)  -- works with
     , ('bob'  , 2, 'test value', 'ident', 22)  -- and without NULL
ON     CONFLICT (name, status, COALESCE(test_field, '')) DO UPDATE  -- match expr. index
SET    count = COALESCE(tu.count + EXCLUDED.count, EXCLUDED.count, tu.count);

Like @ypercube I assume you actually want to add count to the existing count. Since the column can be NULL, adding NULL would set the column NULL. If you define count NOT NULL, you can simplify.

Another idea would be to just drop the conflict_target from the statement to cover all unique violations. Then you could define various unique indexes for a more sophisticated definition of what's supposed to be "unique". But that won't fly with ON CONFLICT DO UPDATE. The manual once more:

  For ON CONFLICT DO NOTHING, it is optional to specify a
  conflict_target; when omitted, conflicts with all usable constraints
  (and unique indexes) are handled. For ON CONFLICT DO UPDATE, a
  conflict_target must be provided.


how to use the same timestamp along several tables?

Edit: an alternative approach

-- remove first- last- seen from device
CREATE TABLE device
( deviceID varchar(12) NOT NULL primary key
, snmpSet varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

-- add p.k. and f.k.
CREATE TABLE snmpData
( deviceID varchar(12)  NOT NULL
, ts datetime NOT NULL
, oid varchar(45)  NOT NULL
, value varchar(30)  NOT NULL
,    constraint pk_snmpData primary key (deviceID, ts) -- assumed
,    constraint fk_snmpData foreign key (deviceID)
                            references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

Now, what is first- and last- seen? To me, this seems like a kind of aggregation or summary of a device. This kind of construction is sometimes named Incremental Evaluation System

CREATE TABLE device_summary
( deviceID varchar(12) NOT NULL
, firstSeen datetime NOT NULL
, lastSeen datetime NOT NULL
,   constraint pk_device_summary primary key (deviceID)
,   constraint fk_device_summary foreign key (deviceID)
                                 references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

The logic for updating device_summary is easily expressed in a trigger:

DELIMITER $$
CREATE TRIGGER after_snmpData_insert
AFTER INSERT ON snmpData
FOR EACH ROW
BEGIN
    -- if device exists, insert will be ignored
    INSERT IGNORE INTO device_summary
        (deviceID, firstSeen, lastSeen)
    VALUES (NEW.deviceID, NEW.ts, NEW.ts);

    -- update lastSeen
    UPDATE device_summary
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;
END$$
DELIMITER ;

Since a device most likely exists in device_summary, it is more efficient to update first and check for number of updated rows (if 0, insert)

CREATE TRIGGER after_snmpData_insert
AFTER INSERT ON snmpData
FOR EACH ROW
BEGIN
    -- update lastSeen
    UPDATE device_summary
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;

    IF ( ROW_COUNT() = 0 ) THEN
        -- No rows updated, i.e. the device does not exist
        INSERT INTO device_summary
            (deviceID, firstSeen, lastSeen)
        VALUES (NEW.deviceID, NEW.ts, NEW.ts);
    END IF;
END

Edit: added a fiddle, extended extended fiddle

To get the latest data for each device:

select x.oid, x.value
from snmpData x
join device_summary y
    on x.deviceId = y.deviceId
   and x.ts = y.lastSeen;


Accelerated Database Recovery with AlwaysON -- long recovery time for secondary unexpeccted

I belive that Everything in the persisten version store would be transaction-log-causing activity, right?
This means that everything affected by that would receive a performance hit:
– log size/backups
– checkdb (gets hit by db size too)
– ALWAYS ON – meaning it could get DELAYS FOR SYNCHRONOUS AG


Why does an AS cube rebuild use so much TempDB?

While the cube builds, you can run Adam Machanic's sp_WhoIsActive diagnostic tool to see which queries are allocating space in TempDB.  I recorded an sp_WhoIsActive tutorial video to show how it works.  Include the @get_plans = 1 parameter when you call it, and you'll also get the execution plans.  That way you can see exactly what's using TempDB and why.


Something like:

select uniqid,
       min(case when visit_seq = 1 then whenseenlast end) as whenseenfirst,
       min(case when visit_seq = 1 then whereseenlast end) as whereseenfirst,
       min(case when visit_seq = visits_thisuser then whenseenlast end) as whenseenlast,
       min(case when visit_seq = visits_thisuser then whereseenlast end) as whereseenlast
from (
  select uniqid,
         row_number() over(w) as visit_seq,
         count(*) over(w range between unbounded preceding and unbounded following) as visits_thisuser,
         whenseenlast, whereseenlast
  from history
  window w as (partition by uniqid order by whenseenlast)
) visit
group by visit.uniqid

http://sqlfiddle.com/#!11/fb6f8/14/0


ISO 27001 - resetting passwords for SQL Server accounts

The ISO/IEC 27001:2013 paper Information technology -- Security techniques -- Information security management systems -- Requirements is a standard that:

  ...specifies the requirements for establishing, implementing, maintaining and continually improving an information security management system within the context of the organization. It also includes requirements for the assessment and treatment of information security risks tailored to the needs of the organization. The requirements set out in ISO/IEC 27001:2013 are generic and are intended to be applicable to all organizations, regardless of type, size or nature.

If this standard defines that you should change the passwords every so often to ensure security, then by all means go ahead and do this.

Regarding SQL Server

Changing the password of an account is as simple as logging in to the instance and setting off the command:

ALTER LOGIN [sa] SET PASSWORD = 'ThisIsMyN3wSecurePassw0rd'
GO

Answering Your Question

  Is is standard to reset the passwords for these SQL Server authentication accounts?

There are no standards, regarding resetting the [sa] login's (or for that matter any other SQL Server authenticated account's) password.

But if you want to abide by the standards laid out in ISO/IEC 27001:2013 then you probably should change the SQL Server authenticated account's passwords at regular intervals.

  regular: existing or happening repeatedly in a fixed pattern, with equal or similar amounts of space or time between one and the next; even:

Reference: regular (Cambridge Dictionary)

That means that once a year can be considered regular.