The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution.

When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column.

Finally the query is executed as:

select a.*
from table_a  a
where a.name in (select a.name
                 from table_b  b
                );

As for the results the query would give, for every row of table_a, the subquery (select name from table_b) - or (select a.name from table_b  b) - is a table with a single column with the same a.name value and as many rows as table_b. So, if table_b has 1 or more rows, the query runs as:

select a.*
from table_a  a
where a.name in (a.name, a.name, ..., a.name) ;

or:

select a.*
from table_a  a
where a.name = a.name ;

or:

select a.*
from table_a  a
where a.name is not null ;

If table_b is empty, the query will return no rows (thnx to @ughai for pointing that possibility).

That (the fact that you don't get an error) is probably the best reason that all column references should be prefixed with the table name/alias. If the query was:

select a.* from table_a where a.name in (select b.name from table_b);

you would have got the error straight away. When table prefixes are omitted, it is not difficult for such mistakes to happen, especially in more complex queries, and even more important, go unnoticed.

Read also in Oracle docs: Resolution of Names in Static SQL Statements the similar example B-6 in Inner capture and the recommendations in the Avoiding Inner Capture in SELECT and DML Statements paragraphs:

  Qualify each column reference in the statement with the appropriate table alias.


data fetch with multiple filters in a paginated way

If you're not allowed to join, then why bother with an RDBMS?  Not that you could save money on the DB, but you could loose the DBA.  Even with things being the way they are, I'm not sure there is an efficient solution to fetch large amounts of data in a "paginated way".  Depending on your client, you could use a bidirectional cursor, allowing the user to "page" forward an backwards.  This "cursor" has to fit in memory somewhere.

Now, to respect the DBA's wishes, you would create your filter by fetching the id's from the "other" tables, and use those in your where clause with "=" or "in".  If you need the "in", you would lose the possibility of using bind variables, so you'd preferably stick to one item from each of the "other" tables.


Alter table on live production databases

When you issue an ALTER TABLE in PostgreSQL it will take an ACCESS EXCLUSIVE lock that blocks everything including SELECT. However, this lock can be quite brief if the table doesn't require re-writing, no new UNIQUE, CHECK or FOREIGN KEY constraints need expensive full-table scans to verify, etc.

If in doubt, you can generally just try it! All DDL in PostgreSQL is transactional, so it's quite fine to cancel an ALTER TABLE if it takes too long and starts holding up other queries. The lock levels required by various commands are documented in the locking page.

Some normally-slow operations can be sped up to be safe to perform without downtime. For example, if you have table t and you want to change column customercode integer NOT NULL to text because the customer has decided all customer codes must now begin with an X, you could write:

ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );

... but that would lock the whole table for the re-write. So does adding a column with a DEFAULT. It can be done in a couple of steps to avoid the long lock, but applications must be able to cope with the temporary duplication:

ALTER TABLE t ADD COLUMN customercode_new text;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;

This will only prevent writes to t during the process; the lock name EXCLUSIVE is somewhat deceptive in that it excludes everything except SELECT; the ACCESS EXCLUSIVE mode is the only one that excludes absolutely everyting. See lock modes. There's a risk that this operation could deadlock-rollback due to the lock upgrade required by the ALTER TABLE, but at worst you'll just have to do it again.

You can even avoid that lock and do the whole thing live by creating a trigger function on t that whenever an INSERT or UPDATE comes in, automatically populates customercode_new from customercode.

There are also built-in tools like CREATE INDEX CONCURRENTLY and ALTER TABLE ... ADD table_constraint_using_index that're designed to allow DBAs to reduce exclusive locking durations by doing work more slowly in a concurrency-friendly way.

The pg_reorg tool or its successor pg_repack can be used for some table restructuring operations as well.


Here you go, using a CTE to do the replace.  I used a global temp table instead, but the sample works.

CREATE TABLE ##MYRequests
    (
      [requestID] SMALLINT
    , [dateClosed] DATETIME
    , [closedby] VARCHAR(50)
    );

INSERT  INTO ##MYRequests
        ( [requestID], [dateClosed], [closedby] )
VALUES  ( 1, '20160612 10:34:09 AM', 'DOMAIN\Joe' ),
        ( 2, '20160612 11:35:09 PM', 'DOMAIN\Bill' ),
        ( 3, '20160612 08:02:09 AM', 'DOMAIN\Matt' ),
        ( 4, '20160613 09:46:09 PM', 'DOMAIN\Joe' ),
        ( 5, '20160613 05:34:09 PM', 'DOMAIN\Bill' ),
        ( 6, '20160614 04:39:09 PM', 'DOMAIN\Matt' ),
        ( 7, '20160614 12:38:09 PM', 'DOMAIN\Joe' ),
        ( 8, '20160614 12:37:09 PM', 'DOMAIN\Bill' ),
        ( 9, '20160615 11:36:09 AM', 'DOMAIN\Matt' ),
        ( 10, '20160616 10:35:09 AM', 'DOMAIN\Joe' ),
        ( 11, '20160613 10:35:09 AM', 'DOMAIN\Bill' ),
        ( 12, '20160615 09:46:09 PM', 'DOMAIN\Joe' ),
        ( 13, '20160615 09:47:09 PM', 'DOMAIN\Joe' ),
        ( 14, '20160615 09:48:09 PM', 'DOMAIN\Joe' );

--dynamic pivot
DECLARE @cols AS NVARCHAR(MAX)
  , @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT
                            ',' + QUOTENAME(REPLACE(r.closedby, 'DOMAIN\', ''))
                   FROM     ##MYRequests r
                   WHERE    r.dateClosed > GETDATE() - 30
    FOR           XML PATH('')
                    , TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = ';WITH CTE_Requests AS
(SELECT M.requestID, M.dateclosed, REPLACE(M.closedby, ' + QUOTENAME('DOMAIN\',
                                                              '''') + ', '
    + '''' + '''' + ') AS closedby
FROM ##MYRequests M)
SELECT * from (
    select
    DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) as [date],
    closedby
    from CTE_Requests
    where DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) > getdate()-30
    )
    as s
PIVOT
    (count(closedby) for [closedby] in
    (' + @cols + '))
    as PVT ';

execute(@query);


'mysql' database in fresh MySQL installation

The mysql.servers table is used only in conjunction with any table that uses the FEDERATED Storage Engine. It's in the MySQL Documentation:

  FEDERATED tables may be replicated to other slaves, but you must ensure that the slave servers are able to use the user/password combination that is defined in the CONNECTION string (or the row in the mysql.servers table) to connect to the remote server.

Therefore, a freshly installed MySQL instance will have mysql.servers empty. If you create a FEDERATED table, then the username/password for a remote table is populated in mysql.servers.

What you are looking for is the information_schema database. For example,

INFORMATION_SCHEMA.SCHEMATA has all the databases in the MySQL Instance
INFORMATION_SCHEMA.TABLES has all the tables in the MySQL Instance
Please Read the Following

MySQL Documentation on INFORMATION_SCHEMA
My 4-Year-Old Post How is INFORMATION_SCHEMA implemented in MySQL?


Sudden PRIMARY KEY violation on IDENTITY column

Since the question states SQL Server 2012 RTM (build 2100) is in use, it is likely this bug:

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 is under memory pressure

which says:

  Assume that you create a sequence object that has the CACHE option enabled in Microsoft SQL Server 2012 or SQL Server 2014. When the instance is under memory pressure, and multiple concurrent connections request sequence values from the same sequence object, duplicate sequence values may be generated. In addition, a unique or primary key (PK) violation error occurs when the duplicate sequence value is inserted into a table.

Note that IDENTITY uses the sequence object mechanism in SQL Server 2012 and later.

The issue was first fixed in:

Cumulative Update 6 for SQL Server 2014
Cumulative Update 4 for SQL Server 2012 SP2


Can I use a Synonym to point to an object in a schema using another schema?

Yes, of course. You just need to create synonym in S1 schema (not public synonym).


I agree with what others have said, it is most likely a problem with the statement terminator. Try adding:

--#SET TERMINATOR @
CREATE OR REPLACE TRIGGER B_TRIGGER
[...]
@
--#SET TERMINATOR ;

You might also consider using for example @ as your statement terminator globally. Another "undocumented" feature is to quote the ; in the trigger using to ending dashes. Example:

CREATE OR REPLACE TRIGGER B_TRIGGER
AFTER UPDATE OF A_ID ON B
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
    WHEN (N.A_ID IS NULL)
    BEGIN ATOMIC
        DECLARE NULL_ROW_ID INT; --
        SET NULL_ROW_ID = (SELECT A_ID FROM A WHERE A_CODE = 'Custom Null String'); --
        IF NULL_ROW_ID IS NULL THEN
            SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = 'Custom Null String row not found.'; --
        END IF; --
        SET N.A_ID = NULL_ROW_ID; --
    END ;

Personally I prefer to use - say - @ globally and change the environment to adjust for that


Mysqldump, exclude data from tables by query

From a single table, it is easy to add

--where='...'


How to get all the users on a database with SA access?

The simplest scenario (a user explicitly granted sysadmin directly) is covered by the following query:

SELECT p.name
  FROM sys.server_principals AS p
  INNER JOIN sys.server_role_members AS m
  ON p.principal_id = m.member_principal_id
  WHERE m.role_principal_id = 3;

But then you may also have users who are members of a Windows group (which SQL Server won't know about) and that group has been granted sysadmin. To get all of those users you may need to run some queries against Active Directory (or ask someone who can).


Performance issue with Top, Order By and Where condition with more than one million rows involved

Below are a few things to try, either separately or altogether.

OPTION(RECOMPILE)

Since you are using variables declared in the query text itself, you could use OPTION(RECOMPILE) to recreate the query plan on each execution. This way the optimizer 'sees' the values in the variables at runtime, and you should get a query plan better suited for these values.

Disable row goals

Since you have a row goal set due to the TOP() operator, you could try disabling that rowgoal with the traceflag: 4138.

Row goal example in your plan on the concatenation operator:

Disable row goals at the query level with: OPTION(QUERYTRACEON 4138);

More on row goals here

Testing these together with your query:

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID
        IS NULL
     OR  A.PermissionID IN
                ( SELECT PermissionID
                    FROM PermissionGroup (nolock)
                    WHERE GroupID in
                                    (SELECT GroupID FROM SiteUserGroup (nolock)
                                     WHERE SiteUserID = @SiteUserID)
                 )
    )
AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)
ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );

Removing the OR's and using UNION ALL

Something else, which does need you to rewrite the query, is separating the OR's with UNION ALL's.

This way indexing / operators could be used more efficiently at the cost of processing more data. This depends on the filters & indexes used.

An example for your query:

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy,SiteUserID

FROM
(
SELECT UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID , A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID
        IS NULL
    )
AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)
UNION ALL
SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID ,A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
 AND  A.PermissionID IN
                ( SELECT PermissionID
                    FROM PermissionGroup (nolock)
                    WHERE GroupID in
                                    (SELECT GroupID FROM SiteUserGroup (nolock)
                                     WHERE SiteUserID = @SiteUserID)
                 )

AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)) AS A;

ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );

Table variables / temporary tables

Another thing I would try is changing the table variable to a temporary table

CREATE TABLE #ColleageUsers(
ID int
)

To get statistics on the temporary object, since table variables have no statistics but temporary tables do.

Splitting the query up into multiple pieces with the use of temporary tables

Another thing to try to get a different execution plan is doing some of the work beforehand, and storing these results into a temporary table. When many tables get touched, it is harder to get correct estimates, splitting up the work makes it easier to calculate the estimates for each part.

An example for your query:

declare  @SiteUserID as int = 6484,
 @CompanyID as int = 34;

SELECT PermissionID
INTO #TEMP
FROM PermissionGroup (nolock)
WHERE GroupID in
(SELECT GroupID FROM SiteUserGroup (nolock)
 WHERE SiteUserID = @SiteUserID)

DECLARE @PermissionID INT
SELECT DISTINCT Top 1 @PermissionID=PermissionID
FROM PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock)
where SiteUserID = @SiteUserID) and PermissionID = 184

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy,SiteUserID

FROM
(
SELECT UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID , A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (@PermissionID)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID
        IS NULL
    )
AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)
UNION ALL
SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID ,A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
 AND  A.PermissionID IN
                ( SELECT PermissionID
                FROM #TEMP
                 )

AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)) AS A

ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );


How to permanently enable Read Access for MongoDB Secondary Node?

08:11:2018 16:01:00.552 - error: A hook (orm) failed to load!
  08:11:2018 16:01:00.558 - error: Error: Failed to connect to MongoDB.
  Are you sure your configured Mongo instance is running? Error details:
  MongoError: no valid seed servers in list

As per your it seems like that your mongod server has not started.

As per MongoDB documentation here All read preference modes except primary may return stale data because secondaries replicate operations from the primary with some delay. [1] Ensure that your application can tolerate stale data if you choose to use a non-primary mode.

MongoDB drivers support five read preference modes.

Read Preference Mode    Description

primary                 Default mode. All operations read from the current replica set primary.
                        Multi-document transactions that contain read operations must use read preference primary.

                        All operations in a given transaction must route to the same member.

primaryPreferred        In most situations, operations read from the primary but if it is unavailable, operations read from secondary members.

secondary               All operations read from the secondary members of the replica set.

secondaryPreferred      In most situations, operations read from secondary members but if no secondary members are available, operations read from the primary.

nearest                Operations read from member of the replica set with the least network latency, irrespective of the member’s type.

As MongoDB documented setSlaveOk()  This allows the current connection to allow read operations to run on secondary members. See the readPref() method for more fine-grained control over read preference in the mongo shell.

db.getMongo().setSlaveOk()

For further your ref here, here and here


Check your mongos, mongod and config servers NTP settings. The clocks on every member of the cluster must return the same datetime. Use the "date" command on linux to verify it.


Clustered vs Non Clustered Indexes for a Primary Key

Unless you switch to sequential GUID generation, you don't want the GUID as a clustered index key.  So your choices are

1) Make the ID column the clustered index key.  This will optimize joins and lookups on ID, but it sounds like those are rare.

2) For SQL 2016+ replace the Heap with a Clustered Columnstore index.  This compresses the data and optimizes table scans, perhaps to the extent that you can drop some of your other indexes.

3) Pick your most used non-clustered index and make that the Clustered index.

4) Leave it as a Heap.

In any case, the "child tables" that use the GUID as a foreign key might benefit from having that foreign key column as the leading column in their clustered primary key.


mysql import failed caused by overgrown general log file

All statements that hit the server are stored inside the general log. So general log is duplicating your dump. The general log should be used only for debugging only, and it should be activated for the time required to spot a problem. Try to disactivate general log.


Can MySQL 5.1 read a MySQL 5.5 MyISAM file?

What could go wrong? If the way MyISAM changed the format of how something is stored in their files, it will break on 5.1. This will render your stats database on the master corrupt and unusable.

Even if the initial load went fine, it doesn't mean something won't come along later with the result listed above...a corrupt database. It's a risk, and if it can be avoided, I personally would do so.

And in your case, it can be avoided by dumping the data using mysqldump quickly and then load into the 5.1 instance.


Performance issues with partial join query

Your query will always result in a scan of dbo.loggedURLs because of the leading wildcard in the LIKE statement.  There are a couple of possible solutions I can think of.

Add a column to LoggedURLs for a reference to the allowedURL and at the time of insert into LoggedURLs, determine which allowedURL applies, and supply that column (Then Index that column)
If you can come up with an algorithm that will allow you to parse out the allowedURL in a consistent manner, you could create a computed column with the stripped down url, then index that (Example below)

declare @str varchar(100)
set @str = 'www.othersite.net/search/query'
select substring(@str, charindex('www', @str, 1) + 4,(charindex('/', @str, 1)-5))

This example may not work for all potential situations, but works for the examples given.


Replication not updating XML Schema collection

According to Publish Data and Database Objects in the section Considerations for Publishing - Limitations on Publishing Objects:

  XML schema collections can be replicated but changes are not
  replicated after the initial snapshot.

I agree, this appears to be a deficiency.

As a workaround, you could perform the XML schema update on the Publisher and then post the XML schema update to Subscribers using sp_addscriptexec.


Oracle PDB broken after upgrade from 12.1 to 12.2

As suggested by Balazs Papp, opening the PDB in UPGRADE mode did the trick. After that I was able to set the STORAGE limit, resize the tablespaces and rebuild broken packages.


All INNER JOINs and UNIONs maybe

PROPOSED QUERY

select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
union select t1.id,t2.id,NULL,NULL from t1
    inner join t2 using (id)
union select t1.id,t2.id,t3.id,NULL from t1
    inner join t2 using (id)
    inner join t3 using (id)
union select t1.id,t2.id,t3.id,t4.id from t1
    inner join t2 using (id)
    inner join t3 using (id)
    inner join t4 using (id)
;

SAMPLE DATA

use test
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1
(
    id int not null,
    name varchar(20),
    primary key (id)
);
insert into t1 values (1,'name1');
create table t2 like t1; insert into t2 values (1,'name2');
create table t3 like t1; insert into t3 values (1,'name3');
create table t4 like t1; insert into t4 values (1,'name4');

PROPOSED QUERY EXECUTED

mysql> select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
    -> union select t1.id,t2.id,NULL,NULL from t1
    ->     inner join t2 using (id)
    -> union select t1.id,t2.id,t3.id,NULL from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    -> union select t1.id,t2.id,t3.id,t4.id from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    ->     inner join t4 using (id)
    -> ;
+-------+-------+-------+-------+
| t1_id | t2_id | t3_id | t4_id |
+-------+-------+-------+-------+
|     1 |  NULL |  NULL |  NULL |
|     1 |     1 |  NULL |  NULL |
|     1 |     1 |     1 |  NULL |
|     1 |     1 |     1 |     1 |
+-------+-------+-------+-------+
4 rows in set (0.00 sec)

mysql>

All the columns have id as a common name. If you want, I can adjust my answer with the id names


How do I design a database for continents, countries, regions, cities and POIs?

i suggest 2 options

Option 1: if you want a RBDMs, i think 2 tables will be sufficient

Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)

in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and maintenance is  easier, then 5 separate tables

the other table is the table to collect the data

Table 2 (Master)
-Tourist
-LocationID
-ExtraFields
-...

so in this table you collect info about tourists and all you have to do is store the locationid, which is your granularity, this way the Locationid, has the Landmark,city,region,country,continent
Since the Landmark is definetly in a city and the city is in a country and the country is in a region.. (no need for 5 tables to keep them separate, there will be a big chance of invalid inputs or data if not maintained properly)

Option 2:

i suggest graph database, check this post at SO , about using Neo4J for GIS and an implementation of GIS by Neo4J


generating scripts - Incorrect syntax near 'GO'

The problem is that you are mixing up which character is which in the variations of a newline: CRLF / LF. You have:

,[DropIndexScript] = 'DROP INDEX ' + QUOTENAME(SI.name) +
   + CHAR(13) + ' ON '  +
QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name)
 + CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

Here you use either CHAR(13) or CHAR(10) + CHAR(13). The problem is that CHAR(13) is a "Carriage Return" / "CR" while CHAR(10) is a "Line Feed" / "LF". You have reversed them and hence are using both "CR" and "LFCR" in various places. If you swap the "10"s for the "13"s it would work correctly.

Better yet would be having consistency across them. Either use just CHAR(10) everywhere, or CHAR(13) + CHAR(10) everywhere.


how to get back the modified INOUT parameter in postgres

These are the two working solutions:

NEW := func1(NEW);

or first cast the record to text:

SELECT * FROM func1(NEW::text::"My_Table") INTO NEW;


SSLServerHasCertificateAuthority is false but the certificate is signed by a trusted CA

As MongoDB BOL documentation here to connect to a mongod or mongos instance that requires encrypted communication, start mongo shell with --ssl and include the --sslCAFile to validate the server certificates.

mongo --ssl --host hostname.example.com --sslCAFile /etc/ssl/ca.pem

For example, given an TLS/SSL certificate located at /etc/ssl/mongodb.pem, configure mongod to use TLS/SSL encryption for all connections with the following command:

mongod --sslMode requireSSL --sslPEMKeyFile /etc/ssl/mongodb.pem <additional options>

Set Up mongod and mongos with Certificate Validation

To set up mongod or mongos for TLS/SSL encryption using an TLS/SSL certificate signed by a certificate authority, include the following run-time options during startup:

net.ssl.mode set to requireSSL. This setting restricts each server to
use only TLS/SSL encrypted connections. You can also specify either
the value allowSSL or preferSSL to set up the use of mixed TLS/SSL
modes on a port. See net.ssl.mode for details.
PEMKeyfile with the    name of the .pem file that contains the
signed TLS/SSL certificate    and key.
CAFile with the name of the .pem file that contains the root
certificate chain from the Certificate Authority.

  Note:

  Specify the  file and the  file with either the full path
  name or the relative path name.
  If the  is encrypted, specify
      the passphrase. See TLS/SSL
      Certificate Passphrase.

You may also specify these options in the  configuration file, as in the following examples:

If using the YAML configuration file format, include the following configuration in the file:

net:
   ssl:
      mode: requireSSL
      PEMKeyFile: /etc/ssl/mongodb.pem
      CAFile: /etc/ssl/ca.pem

Connect to MongoDB Instance that Requires Client Certificates

To connect to a mongod or mongos that requires CA-signed client certificates, start the mongo shell with --ssl, the --host option to specify the host to which to connect, the --sslPEMKeyFile option to specify the signed certificate-key file, and the --sslCAFile to validate the server certificates.

mongo --ssl --host hostname.example.com --sslPEMKeyFile /etc/ssl/client.pem --sslCAFile /etc/ssl/ca.pem


Procedure showing different duration / reads in Profiler vs. SSMS

The difference in logical reads leads me to believe that the two executions are getting different plans. This could be because:

there are different set settings between the app that is calling the procedure that profiler is picking up and your instance of SSMS.
you have a different default schema (you really should say exec dbo.SM_SP...).
see this post for more details

You can see if there are two or more different plans for the procedure using something like this, which will also identify if this is caused by either (or both) of the two most likely factors for different plan choice:

SELECT p.plan_handle, p.usecounts, p.size_in_bytes,
  set_options = MAX(CASE a.attribute WHEN N'set_options' THEN a.value END),
  dflt_schema = MAX(CASE a.attribute WHEN N'user_id'     THEN a.value END)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.objectid = OBJECT_ID(N'dbo.SM_SP_MAILEVENTNAMES')
AND a.attribute IN (N'user_id', N'set_options')
GROUP BY p.plan_handle, p.usecounts, p.size_in_bytes;


These say that it is replicating:

     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
           Last_Errno: 0
           Last_Error:

Seconds_Behind_Master: 0

Check my.cnf on both the Master and Slave -- look for replicate/binlog_do/ignore_...  Perhaps you are not allowing some databases or tables to go from (do) the Master or be received by (ignore) the Slave.

The various %read_only variables control whether or not you can connect to the server and write; replicated writes are always allowed on the Slave.


pg_total_relation_size is definitely the way to go. It gets the size of the involved files from the file system, which is fast and not invasive.

If my monitoring system would regularly run SELECT count(*) on by big tables, I would kill it. It does a sequential scan and uses lots of resources.

For monitoring, I would not use pg_size_pretty. That is good for human readers, but automatic processing will be easier in bytes.


Postgresql: create role, schema and change ownership but not as superuser

Per documentation,

  You must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, [...]

So in your case, you either need to be superuser or be a member of the role you are transferring the ownership to.

Ultimately, while I applaud your efforts, you're going to have a hard time managing this without some superuser use.  So I'd focus on locking down the superuser use, maybe wrap the steps in a few security-definer functions.


Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019  2:11PM" and output dependeds on language).
To make it deterministic use CONVERT and

  the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113. (from documentation below)

More documentation at: Deterministic and Nondeterministic Functions


Is a partial restore of data from backup possible?

No, you cannot do what you mentioned in your question.

I am sure there are many ways to recover the data you need.  One way is to:

Restore the backup as a different(name) database.
Reconcile data using this source and your current live database.  How you do that? Will entirely depend upon with what you have lost and your business logic.
You can surely take advantage of auto generated integer keys (at least for the tables that has this). You might have to do some plumbing here depending on where is the gap in your current live database.

I know it is a very generic answer. If you need specific help with individual tables, you will need to post table schema, sample data and details of gaps.

On a side note you probably want to reconsider using this hosting company going forward.


How is it possible for Hash Index not to be faster than Btree for equality lookups?

Disk based Btree indexes truly are O(log N), but that is pretty much irrelevant for disk arrays that fit in this solar system.  Due to caching, they are mostly O(1) with a very large constant plus O((log N)-1) with a small constant.  Formally, that is the same thing as O(log N), because constants don't matter in big O notation.  But they do matter in reality.

Much of the slow down in hash index lookups came from the need to protect against corruption or deadlocks caused by hash-table resizing concurrent with the lookups.  Until recent versions (every version you mention is comically out of date), this need led to even higher constants and to rather poor concurrency.  Vastly more man hours went into the optimization of BTree concurrency than hash concurrency.


SQL SERVER 2005 SP3

Your best bet first is to upgrade it to a supported SP - First Download SP4 then Download CU3 --> Build NO:    9.00.5266. Support for SQL server 2005 is ending next year (2016/04/12) even for the latest SP and CU - so better upgrade to a 2012/2014.

You can use my script to help you suggest a good MAXDOP value.

  Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?

SQL server is NUMA aware. This means that SQL server knows what NUMA node the processors are in and what NUMA node the memory is in. Due to this, SQL server will assign  worker thread on correct NUMA node for the data that you want to access.

Refer to :

How It Works: SQL Server 2008 NUMA and Foreign Pages
How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks)

Below are some queries that will help you find out if there is an imbalance of schedulers between the NUMA nodes - which can lead to significant performance problems under load :

SELECT
  parent_node_id,
  scheduler_id,
  [cpu_id],
  is_idle,
  current_tasks_count,
  runnable_tasks_count,
  active_workers_count,
  load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE';

--- check the current tasks, runnable tasks, active workers and avg load factor COUNT ...
SELECT
  parent_node_id,
  SUM(current_tasks_count) AS current_tasks_count,
  SUM(runnable_tasks_count) AS runnable_tasks_count,
  SUM(active_workers_count) AS active_workers_count,
  AVG(load_factor) AS avg_load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;

You should monitor SQL Server:Buffer Node and SQL Server, Memory Node as opposed to just PLE - monitor the individual Buffer Node:Page life expectancy counters (there will be one Buffer Node performance object for each NUMA node).

  exhausting worker threads

IF you set the MAXDOP setting as default (which is 0) then it could lead to worker thread starvation.


psql denies access to file when using COPY FROM

drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm

postgres@dexter:/home/peter/PyPacks$ cd phm

bash: cd: phm: Permission denied

Directories need to be executable to be able to cd into them (or use files within them). All the sub-directory from / to the /home/peter/PyPacks/phm need to be executable by the user you want to use for this to work.

Try at least chmod 711 phm (which should give you rwx--x--x), or perhaps chmod 755 phm (rwxr-xr-x). r for directories is the ability to list their content, not to go into them (or use files/directories within them).


For different columns

select DivisionName, TemplateName from division join tmplate;

If you want each row as a single column string

select concat(DivisionName,' ', TemplateName) from division join tmplate;


Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

Most of the information you will need will be found in this post by Erik Darling.

  ...SQL Server is sort of smart about some modifications.
  Why it isn’t smart about all of them is beyond me

Inserts are fine when we adapt and run your queries:

CREATE TABLE dbo.BoringTable(ID INT IDENTITY(1,1),DateColumn1 DATE,TextColumn1 varchar(50),TextColumn2 varchar (50))

INSERT INTO dbo.BoringTable(DateColumn1,TextColumn1,TextColumn2)
SELECT TOP(1000) CONVERT(date,DATEADD(day,(-ROW_NUMBER() OVER(ORDER BY (SELECT NULL))),GETDATE())),'val1','val2'
FROM master..spt_values s
CROSS APPLY master..spt_values

GO
CREATE VIEW FilteredIndexedView
WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2,DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 < CONVERT(DATETIME, '20190101', 101)
GO

CREATE UNIQUE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexedView (DateColumn1);

And perform the insert that does not match the filter:

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view

The execution plan shows that the indexed view was not added to the plan, and that the optimizer knows that it does not need to access it. There is no additional contention found.

If we do insert a date that matches the indexed view, we get this plan:

And then, when performing an update on rows that do not match the indexed view (before or after):

UPDATE dbo.BoringTable
SET DateColumn1=  '12/13/2019'
WHERE DateColumn1 = '12/11/2019';

Nothing gets updated in the indexed view, but it does bring some extra overhead as additional operators are added to the plan, to be able to update the indexed view if necessary:

The delete does not add the indexed view operators

DELETE FROM dbo.BoringTable
WHERE DateColumn1=  '12/13/2019';


I'm wondering if you can use the undocumented stored proc sp_MSforeachtable rather than use cursors?  Here's a little bit of code that brings back the number of rows in each table. Can you modify it to do what you need to do?

use pubs2014
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
order by tablename
drop table #rowcount

And yes, I've brought both the pubs and the Northwind databases forward into SQL 2014 AND 2016...I'm nostalgic, what can I say?


Calculating Open Days field and stopping with status change

Counting days without explicitly storing the start date would require some kind of scheduled task to count them.  This is not trivial to implement and not recommended.

I would suggest storing the date when opened and then doing a date diff between the start date and the current date to work out the number of days (including the appropriate rounding).  I would suggest that it might be a good idea to also store the closing date.

Edit:

When the item is closed you have two options to work out/read the days opened in the future:

You can store both the start date and the close date and do a datediff between them when you need to know the days opened.
You can store the days open explicitly when you mark the item as closed.

I would recommend 1 as it is more concise.

Edit2:

To fully explain point 1.  Basically you would use the datediff function and compare the start date to the current date, while specifying days (d) as the required unit.

In an SQL select query this would look something like:

select
    datediff('d',table.StartDate,date()) as OpenDays,
    table.Status
from table

The date() function returns the current date.

Microsoft Access Help on DateDiff


Postgres 9.4 won't accept remote connections

With your edit, you are probably running something like this, which will default to the user postgres:

psql -h 123.123.123 -d mydb

when you should be using the user flag:

psql -h 123.123.123 -d mydb -U whoever

you will be prompted for the password for user whoever.


Why am I able to create MV logs using PK — even though there is no PK?

A unique constraint on a NOT NULL column (which you have, as I checked this table in a database where Maximo was installed) is a good enough substitute for a primary key in this case.
SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create materialized view log on t1;
create materialized view log on t1
*
ERROR at line 1:
ORA-12014: table 'T1' does not contain a primary key constraint

SQL> alter table t1 add unique(c1);

Table altered.

SQL> create materialized view log on t1;
create materialized view log on t1
*
ERROR at line 1:
ORA-12014: table 'T1' does not contain a primary key constraint

SQL> alter table t1 modify c1 not null;

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.

SQL> select master, primary_key from user_mview_logs;

MASTER                         PRI
------------------------------ ---
T1                             YES


User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server

It seems like there was a problem during the restoration process.
This error has nothing to do with permission. A database in suspect mode cannot be used or altered.
As mentionned, you will have to fix the issue with the database first (before you can run a "USE" or an "Alter database" on it).
If you do have backup for those database, restore the database from your backup in order to fix the issue.
If you don't have backup, then it will be a bit harder and information about how your DBs went into suspect mode would be nice to have in order to see the best approch to bring them back online (potentially with data loss or corruption).
You may have to set the database into the Emergency state before running a checkdb with repair_allow_data_loss on the database (which does exactly as it said)


Here's one idea that you can evaluate:

CREATE TABLE last_transaction
(    last_id bigserial NOT NULL
,    cumulative_amount bigint NOT NULL
);

INSERT INTO last_transaction (last_id, cumulative_amount) VALUES (-1,0);

The current amount should be something like:

SELECT coalesce(SUM(t.amount),0) + coalesce(lt.cumulative_amount,0)
FROM transactions t
RIGHT JOIN last_transaction lt
    ON t.id > lt.last_id
GROUP BY lt.cumulative_amount;

On a regular basis you can refresh last_transaction similar to:

update last_transaction
    set last_id = (select max(id) from transactions)
      , cumulative_amount = (select sum(amount) from transactions);

The version of PostgreSQL in your fiddle does not support (perhaps no version does?)

set (last_id, cumulative_amount) = (select ...)

Just an idea, that may or may not fit your needs.

Edit: added type

If a type is to be included (consider naming it transaction_type or something similar) we can extend last_transaction:

CREATE TABLE last_transaction
(    type int not null
,    last_id bigserial NOT NULL
,    cumulative_amount bigint NOT NULL
,        constraint pk_last_transaction primary key (type)
);

INSERT INTO last_transaction (type, last_id, cumulative_amount)
SELECT distinct type, -1, 0
FROM transactions;

To get the current_amount we need to add type to the GROUP BY clause as well as to the ON clause.

SELECT lt.type
     , coalesce(SUM(t.amount),0) + coalesce(lt.cumulative_amount,0)
FROM transactions t
RIGHT JOIN last_transaction lt
    ON t.id > lt.last_id
   AND t.type = lt.type
GROUP BY lt.type, lt.cumulative_amount;

To do a full refresh (according to @Andriy M suggestion) of last_transaction:

UPDATE last_transaction AS lt
    SET last_id = t.last_id
      , cumulative_amount = t.cumulative_amount
FROM (
    SELECT TYPE
         , MAX(id)
         , SUM(amount)
    FROM transactions
    GROUP BY TYPE
) AS t (type, last_id, cumulative_amount)
WHERE t.type = lt.type;

I have yet to examine @YperSillyCubeᵀᴹ suggestion.

I added about a million rows to the transaction table and what I believe would be relevant indexes, but the plan in sqlfiddle looks kind of disappointing.


SQL Job for Monitoring Blockings on SQL Server

One way to do this is to set Blocked Process Report to 60 seconds, then an event will be raised by SQL.  This event can be picked up in a number of ways.

1) Via SQL Profiler, this isnt a great way as you have to run trace etc to find them

2) Via Extended Events, again not great you would have to have a job constantly polling the target.

3) Via Event Notifications.  This is a great way and I think the best way of doing it.  You can create an Event Notification and a Service to direct the notification into a Service Broker queue, then you can process the queue messages with a stored procedure.  It is a fantastic way of getting a free monitoring tool for yourself.

http://i.zdnet.com/whitepapers/Quest_How_to_Use_SQL_Server_Extended_Events_and_Notifications_to_Proactively_Resolve_Performance_Issues.pdf

Just follow page 24, but change the event from ERRLOG to BLOCKED_PROCESS_REPORT

By Default blocked process report is set to 0 (disabled) so to enable you will have to:

SP_CONFIGURE'blocked process threshold',60;
GO
RECONFIGURE;
GO


What will GRANT  ON ALL TABLES IN SCHEMA  TO  do?

so new tables will be affected in the future?

No, to affect new tables set DEFAULT PRIVILEGES:
PostgreSQL CREATE TABLE creates with incorrect owner

Note that default privileges are set per role.


how to clean some character from row in mysql?

Fairly easy with a couple of string functions ... change test to the name of your table of course.

update `test` set bfi_name = substr(bfi_name,instr(bfi_name,' ')+1)


I presume you mean that you get an error with this query, and that if you remove the where clause you get no error, but not the right answer.

You need a having clause, which is like where but can be applied to the aggregated results:

select
  sum(a)-sum(b) AS c1,
  sum(a) AS c2,
  sum(b) AS c3
from tbldoc
group by Cid
having sum(a)-sum(b) <> 0;

Alternatively, you can move the whole statement to a subquery and perform the where on the outside:

select *
from (
  select
    sum(a)-sum(b) AS c1,
    sum(a) AS c2,
    sum(b) AS c3
  from tbldoc
  group by Cid
) s
where c1 <> 0;


How Compare two columns of Same SQL table to find duplicate records

You did not specify a particular RDBMS (SQL Server, Oracle, etc.), but here is a quick solution that would probably work on most.

Assuming your table has some kind of unique key (primary key - like ID) to determine the difference between one row and another row, you could use a correlated exists check.

SELECT a.*
FROM table1 a
WHERE EXISTS (
        SELECT *
        FROM table1
        WHERE StartDateTime = a.StartDateTime
            AND EndDateTime = a.EndDateTime
            AND Id <> a.Id
        )


Copy scheduling information form local system to remote system  SSRS

Use the "Reporting Services Scripter" which uses the web service API of SSRS to migrate stuff. I'm not sure how up to date it is, last updated 2009 However, I've foundthis useful in previous jobs.

Copying at the database level won't be enough: scheduled tasks are SQL Server Agent jobs that also need migrated. For more, see "How to: Migrate a Reporting Services Installation"


Did you try to replace the insert..union statements with separate insert statements?

For example:

insert into @containsTable select [key],[rank] from ContainsTable(File2010,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File2000,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1990,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1980,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1970,Content,@keywords)

It could be as simple as the increased processing cost of union plus the increased memory usage from having 5 processes open at the same time to use them on that insert, slowing down your UDF.

(Also, since you're querying from - supposedly - unrelated data tables, the 'distinct' part of union should be unnecessary.)


How to make MYSQL smart enough to understand when it should take into account stop word and when it shouldn't?

I state you have two possibilities with MySQL:

Deactivate stop word list

You can deactivate the stop word list completely.

  To override the default stopword list, set the ft_stopword_file system
  variable. [...] The variable value should be the path name of the file
  containing the stopword list, or the empty string to disable stopword
  filtering.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-fine-tuning.html

(You might want to change ft_min_word_len too, since its default value is 4 and "go" is only 2 characters long.)

This would allow you to use the fulltext index even in case of presence of stop words, which offers by far more possibilities than any search with LIKE and is a lot faster.

Control the query

Your application can scan the search words before the query to actually change the query depending on "do the search words contain a stop word". If yes, query with LIKE, otherwise query with AGAINST. This way still leaves you with a very slow LIKE based query in the stop word case!

I do not know of further possibilities with MySQL as its capabilities concerning fulltext searches are rather limited.

Non-MySQL-Options (Take a look around)

There is other software around, which eases problems of search&find, for example the modified MySQL code Sphinx. Sphinx obviously has a different approach than MySQL since its default stop word list is empty. If there even is the possibility to add another software to your infrastructure, you might take a look at its possibilities or search for other software which might fulfill your needs (e.g. Apache Lucene).


Table with 4 million records - different size and performance in development and production

Never do shrink database as it increases fragmentation even more. And yes, it looks like sizes are different because of fragmentation, so you have lots of space that stores nothing (half filled pages). This is probably because of incorrect schema design and wrongly chosen clustered indexes. But fragmentation happens on production system and it's good idea to check for it and do index rebuild on a regular basis.

The error you get might appear of not enough memory so you could try SORT_IN_TEMPDB = ON option. Also consider REORGANIZE option as it is online and even if it fails, it will save all work done prior it fails. But REORGANIZE option just defragments leaf level of an index, not the entire tree.

If it doesn't help, the only way is to recreate database with scripts and fill it with production data but I hope it won't be necessary.


Sql Server doesn't really support regular expressions too well. If all you need is to pick a substring from the beginning to the second dot, combine LEFT() and CHARINDEX(), like so:
-- Test table with data
create table stringtest(data varchar(32), name char(1))
insert into stringtest values('10.01.02', 'a')
insert into stringtest values('100.2.03', 'b')
insert into stringtest values('1021.10.04', 'c')

select
  data
  , left(data, CHARINDEX('.', data, CHARINDEX('.', data, 1)+1)-1) as 'subdata'
  , name
from stringtest

-- Output:
10.01.02    10.01    a
100.2.03    100.2    b
1021.10.04  1021.10  c

The extraction
left(data, CHARINDEX('.', data, CHARINDEX('.', data, 0)+1)-1) as 'subdata'
works as follows. First, the inner CHARINDEX('.', data, 0) will find the 1st dot. As index is 0, it starts from the beginning of the string. +1 will point to next character after the dot  and pass that as the starting index to the outer CHARINDEX(). The outer will find index of the second dot. Finally, LEFT() will return a substring from zero to the 2nd dot.
Edit
As per ypercube's request, let's handle some errors. First, add some data with invalid values:
insert into stringtest values('1021', 'd')
insert into stringtest values('1021.10', 'e')

And let's tune the query to include nested case statements like so,
select
  data
  , case CHARINDEX('.', data, 1) -- find first dot
    when 0 then 'No dot'
    else
        case  CHARINDEX('.', data, CHARINDEX('.', data, 1)+1) -- find second dot
        when 0 then 'No two dots'
        else left(data, CHARINDEX('.', data, CHARINDEX('.', data, 1)+1)-1)
        end
  end
  , name
from stringtest


Automatic failover (Clustering/Always On AG)

Is this feature (automatic failover with clustered and separate server instance) supported in newer SQL versions (2016,2017)?

No it is not. SQL Server 2019 CTP has also been launched and I am not sure whether the functionality has changed in CTP version.

  So as to be able to use the automatic failover feature we have only 3 options:

For automatic failover to occur you should make sure the replicas are configured for "automatic failover" and the configuration is set to be "synchronous commit". You can select both options when configuring AG with GUI or you can use T-SQL and use options like AVAILABILITY_MODE = SYNCHRONOUS_COMMIT and FAILOVER_MODE = AUTOMATIC
For example( Code Source)

CREATE AVAILABILITY GROUP MyAg
   WITH (
      AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
      FAILURE_CONDITION_LEVEL  =  3,
      HEALTH_CHECK_TIMEOUT = 600000
       )

   FOR
      DATABASE  ThisDatabase, ThatDatabase
   REPLICA ON
      'COMPUTER01' WITH
         (
         ENDPOINT_URL = 'TCP://COMPUTER01:5022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  --mmode is set to sync commit
         FAILOVER_MODE = AUTOMATIC,  ----failover mode is set to automatic
         BACKUP_PRIORITY = 30,
         SECONDARY_ROLE (ALLOW_CONNECTIONS = NO,
            READ_ONLY_ROUTING_URL = 'TCP://COMPUTER01:1433' ),
         PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
            READ_ONLY_ROUTING_LIST = (COMPUTER03) ),
         SESSION_TIMEOUT = 10
         ),

I would highly recommend you to read Availability Groups:When Is My Secondary Failover ready

As for your question some are vague. You simply cannot have automatic failover in AG if you are deploying AG on FCI. This is because it will conflict with failover mechanism of FCI. As to why the MS has given option to configure AG in FCI this is simply because of complex environment people have and they wanted AG to be set in almost all HA scenarios. Configuring AG in FCI would give you both OS level protection and database level protection just like you used to have mirroring configured for database which was residing on FCI.


Recreated database or clear all data on import to it in MySQL

You need to add this mysql command in order to drop and create database in a dump script:

DROP DATABASE IF EXISTS `database_name`;
CREATE DATABASE `database_name`;


Extracting 'hot columns' into a separate table

The behaviour you're describing is called MVCC (Multi version concurrency control). Strictly saying it's not delete + insert. It is more like:

copy current version of row
update as requested
append new current version to a chained list of version

In the background this history is being cleaned up depending on how old your oldest transaction is. If you have long running transactions the history can grow pretty big.

  This is not postgresql specific. It's a very common method across
  many databases to handle conccurrency. To see more: MVCC in Postgresql

Therefore it does make sense to move such "hot column" to a separate table.  Especially if the row size is significantly bigger than the size of the "hot columns".

Be aware though it has some implications on the select performance:

If these columns are present in your queries for filtering or sorting query time will take a hit
To retrieve these columns you need to join the table which again has some impact on time

There is an alternative method of splitting where your main table contains only the small, filtering and sorting columns (including these columns too) and "data" is stored separately.

It's always best to try which works best for your dataset and query patterns. I did a benchmark about a year ago comparing one big table with two splitting strategy that can help you get started: http://charlesnagy.info/it/postgresql/split-or-leave-frequently-updated-column-in-postgresql


One reason for normalisation is to remove data modification anomalies
ORMs usually do not support this.

I have many examples of Hibernate-designed databases that break this principle:

bloated (string repeated over 100s of millions of rows)
no lookup tables (see above)
no DRI (constraints, keys)
varchar clustered indexes
unnecessary link tables (eg enforcing 1..0:1 when a nullable FK column would suffice)

The worst I've seen is a 1TB MySQL database that was perhaps 75-80% too big because of these

I'd also suggest that the statement "todays systems can handle it" is true for most Mickey Mouse systems. As you scale, today's systems won't.

In my example above, there was no traction to refactor or change keys or fix data: just complain about database growth rates and inability to build a meaningful DW on top of it


Merging Results while avoiding Key Constraint Error?

If I'm following you correctly, you can do what you need in a single MERGE statement, if you want to be cute about it.  I suspect you'd get better performance with separate small INSERT and DELETE operations.

CREATE TABLE #ManyMany
(
FK1 INT NOT NULL,
FK2 INT NOT NULL,
PRIMARY KEY (FK1, FK2),
UNIQUE (FK2, FK1)
)
INSERT INTO #ManyMany (FK1, FK2) VALUES (1, 1), (1, 2), (1, 3), (2, 3)

CREATE TABLE #Merges
(
OldPK   INT NOT NULL PRIMARY KEY,
NewPK   INT NOT NULL
)
INSERT INTO #Merges (OldPK, NewPK) VALUES (2, 3)

SELECT * FROM #ManyMany
MERGE INTO #ManyMany AS Existing
    USING
        (
        SELECT
            MM.FK1, COALESCE(M.NewPK, MM.FK2) AS FK2
        FROM
            #ManyMany AS MM
            LEFT JOIN #Merges AS M ON MM.FK2 = M.OldPK
        WHERE
            NOT EXISTS (SELECT * FROM #ManyMany AS Existing WHERE MM.FK1 = Existing.FK1 AND M.NewPK = Existing.FK2)
        ) AS New ON Existing.FK1 = New.FK1 AND Existing.FK2 = New.FK2
    WHEN MATCHED AND Existing.FK2 <> New.FK2 THEN UPDATE SET FK2 = New.FK2
    WHEN NOT MATCHED THEN INSERT (FK1, FK2) VALUES (FK1, FK2)
    WHEN NOT MATCHED BY SOURCE THEN DELETE;
SELECT * FROM #ManyMany


The most comprehensive way in my view would be to encrypt/decrypt the database with TDE.
This will ensure that each and every page will change in memory and will be flushed to disk.

I've tried this with success on 'legacy' dbs that were originally created in SQL2000, after I discovered that several pages didn't have actual checksums on them (0x...200) if you look at the header with dbcc page.

If you were to try this, I would recommend testing it on a restored version of the live db, just in case you have undetected corruption that could be caught and stall the encryption process. There are flags to deal with it, but better play it safe.

Obviously you'll want to backup the certificate used by the encryption, so you are covered for any eventuality during the time the db is encrypted.

If anyone has a better idea for writing checksums on all pages, I'd love to hear it :-)


Use name of table as input in procedure and store the output of SELECT statement in output variable SQL

First you're not using a valid syntax due your SELECT command has no FROM clause.

Then you should use sp_executesql just to take care of the output parameters.

create table xx (id int, column1 int);
create table yy (id int, column1 int);
insert into xx values (1, 1),(2,2),(3,3);
insert into yy values (1,10),(3,30),(4,40);
GO

6 rows affected

CREATE PROCEDURE sample_proc
(
    @tablename VARCHAR(MAX),
    @myoutput DECIMAL(20,5) OUTPUT
)
AS
    SET NOCOUNT ON;

    DECLARE @Cmd NVARCHAR(MAX);
    DECLARE @ParmDefinition NVARCHAR(500);

    SET @ParmDefinition = '@Total decimal(20,5) OUTPUT';
    SET @Cmd = 'SELECT @Total = (SUM(column1)/3.3) FROM [' + @tablename + ']';

    -- just to show SQL command.
    SELECT @Cmd;

    EXEC sp_executesql @Cmd, @ParmDefinition, @Total = @myoutput out;
GO

✓

DECLARE @op DECIMAL(20,5);
EXEC sample_proc 'xx', @op OUT
SELECT @op;
EXEC sample_proc 'yy', @op OUT
SELECT @op;
GO

| (No column name)                             |
| :------------------------------------------- |
| SELECT @Total = (SUM(column1)/3.3) FROM [xx] |

| (No column name) |
| :--------------- |
| 1.81818          |

| (No column name)                             |
| :------------------------------------------- |
| SELECT @Total = (SUM(column1)/3.3) FROM [yy] |

| (No column name) |
| :--------------- |
| 24.24242         |

dbfiddle here


Old ODBC app throws "cannot generate sspi context" on Windows 10

When you launch your app using "runas" command, you use some domain user, and that user has Windows login at your SQL server. Or that domain user is part of some AD group that has Windows login at your SQL Server

When you launch app without "runas" command, the app is running, but under different security context (under different windows user), right ?
In this case you have to create Windows login for this domain user at your SQL Server and grant it some permissions, so that different windows user can connect to SQL Server. That would resolve "Cannot generate SSPI context" error

Edit 1: after re-reading your question - its also possible that your windows 7 machine has login at your SQL Server (example "yourdomain/win7machine$") so it can connect, and when you move your app to windows 10 machine, that machine's computer account ("yourdomain/win10machine$") does not have login at your SQL Server... you just have to create this login

Edit 2: to find out what Windows login app is using to connect to SQL Server, while app on Win 7 machine is connected to SQL Server, run below command at SQL Server:

select * from sys.dm_exec_sessions

Look at columns host_name (it will be your Win 7 machine) and login_name (it will be the actual login that application is using to connect to SQL Server)

Then try to launch the app on Windows 10 machine. Make it throw "cannot generate SSPI context" error. Go to SQL Server machine, launch Event Viewer, go to Application logs. Is there a new Logon event from MSSQLSERVER ? if so, does it say "login failed for user...." ? if yes, it will show you what login app on Win 10 machine is using to connect to SQL Server


In general, this design will do the job without problems, specially that you only handle 400 rows per month, which is relatively small number.

However, I have few recommendations to improve the design of the table:

price is better to be decimal, unless you want to store the price in "cents". i.e. if the ticket price is $5.99. you either have to store it as 599 and let the application process it when doing calculation, or viewing it; or, you store it in decimal(5,2)
user field is better to be an integer, and the user ID in the users table is better to be an integer
Your primary key is good. Don't change it to ticket_id.
Maybe a used flag field would be more clear to know which tickets are already used (This is personal preference)
Depending on the queries you run against this table you may create the required indexes.


Get a truly RANDOM row from a PostgreSQL table quickly

Interesting question - which has many possibilities/permutations (this answer has been extensively revised).

Basically, this problem can be divided into two main streams.

A single random record
Multiple random records (not in the question - see reference and discussion at bottom)

Having researched this, I believe that the fastest solution to the single record problem is via the tsm_system_rows extension to PostgreSQL provided by Evan Carroll's answer.

If you're using a binary distribution, I'm not sure, but I think that the contrib modules (of which tsm_system_rows is one) are available by default - at least they were for the EnterpriseDB Windows version I used for my Windows testing (see below). My main testing was done on 12.1 compiled from source on Linux (make world and make install-world).

The reason why I feel that it is best for the single record use case is that the only problem mentioned concerning this extension is that:

  Like the built-in SYSTEM sampling method, SYSTEM_ROWS performs
  block-level sampling, so that the sample is not completely random but
  may be subject to clustering effects, especially if only a small
  number of rows are requested.

however, since you are only interested in selecting 1 row, the block-level clustering effect should not be an issue. This article from 2ndQuadrant shows why this shouldn't be a problem for a sample of one record! It is a major problem for small subsets (see end of post) - OR if you wish to generate a large sample of random records from one large table (again, see the discussion of tsm_system_rows and tsm_system_time below).

Then I created and populated a table like this:

CREATE TABLE rand AS SELECT generate_series(1, 100000000) AS seq, MD5(random()::text);

So, I now have a table with 100,000,000 (100 million) records. Then I added a PRIMARY KEY:

ALTER TABLE rand ADD PRIMARY KEY (seq);

So, now to SELECT random records:

SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);

Notice that I have used a slightly modified command so that I could "see" the randomness - I also set the \timing command so that I could get empirical measurements.

I used the LENGTH() function so that I could readily perceive the size of the PRIMARY KEY integer being returned. Here is a sample of records returned:

test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 970749.61 | bf18719016ff4f5d16ed54c5f4679e20
(1 row)

Time: 30.606 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 512101.21 | d27fbeea30b79d3e4eacdfea7a62b8ac
(1 row)

Time: 0.556 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 666476.41 | c7c0c34d59229bdc42d91d0d4d9d1403
(1 row)

Time: 0.650 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column? |               md5
--------+----------+----------------------------------
      5 | 49152.01 | 0a2ff4da00a2b81697e7e465bd67d85c
(1 row)

Time: 0.593 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column? |               md5
--------+----------+----------------------------------
      5 | 18061.21 | ee46adc96a6f8264a5c6614f8463667d
(1 row)

Time: 0.616 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 691962.01 | 4bac0d051490c47716f860f8afb8b24a
(1 row)

Time: 0.743 ms

So, as you can see, the LENGTH() function returns 6 most of the time - this is to be expected as most records will be between 10,000,000 and 100,000,000, but there are a couple which show a value of 5 (also have seen values of 3 & 4 - data not shown).

Now, notice the timings. The first is 30 milliseconds (ms) but the rest are sub millisecond (approx. 0.6 - 0.7ms). Most of the random samples are returned in this sub-millisecond range, but, there are results returned in 25 - 30 ms (1 in 3 or 4 on average).

From time to time, this multi-millisecond result can occur twice or even three times in a row, but, as I said, the majority of results (approx. 66 - 75%) are sub-millisecond. None of the response times for my solution that I have seen has been in excess of 75ms.

During my research I also discovered the tsm_system_time extension which is similar to tsm_system_rows. Now, I also benchmarked this extension as follows:

SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001) LIMIT 1;

Note that the time quantum is 1/1000th of a millisecond which is a microsecond - if any number lower than this is entered, no records are returned. However, interestingly, even this tiny quantum always returns 120 rows.

Quite why it's 120 is a bit above my pay grade - the PostgreSQL page size is 8192 (the default)

test=# SELECT current_setting('block_size');
 current_setting
-----------------
 8192
(1 row)

and the file system block size is 4096

[pol@UNKNOWN inst]$blockdev --getbsz /dev/mapper/fedora_localhost--live-home
4096

A record should be (1 INTEGER (4 bytes) + 1 UUID (16 bytes)) (= 20 bytes) + the index on the seq field (size?). 4096/120 = 34.1333... - I hardly think that each index entry for this table takes 14 bytes - so where the 120 comes from, I'm not sure.

I'm not quite sure if the LIMIT clause will always return the first tuple of the page or block - thereby introducing an element of non-randomness into the equation.

The performance of the tsm_system_time query is identical (AFAICS - data not shown) to that of the tsm_system_rows extension. The same caveat about not being sure whether there is an element of non-randomness introduced by how these extensions choose their first record also applies to the tsm_system_rows queries. See discussion and bench-testing of the (so-called) randomness of these two methods below.

With respect to performance, just for reference, I'm using a Dell Studio 1557 with a 1TB HDD (spinning rust) and 8GB of DDR3 RAM running Fedora 31). This is a 10 year old machine!

I also did the same thing on a machine (Packard Bell, EasyNote TM - also 10 years old, 8GB DDR3 RAM running Windows 2019 Server) that I have with an SSD (SSD not top of the range by any means!) and the response times are typically (strangely enough) a bit higher (~ 1.3 ms), but there are  fewer spikes and the values of these are lower (~ 5 - 7 ms).

There could well be a lot of stuff running in the background with 2019 Server - but if you have a modern laptop with a decent SSD, there's no reason that you can't expect sub-millisecond response times as a matter of course!

All tests were run using PostgreSQL 12.1.

To check out the true "randomness" of both methods, I created the following table:

CREATE TABLE rand_samp
(
  seq INT,
  md5 TEXT
);

and then ran (3 times each):

DO
$$
DECLARE
  i RECORD;
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO rand_samp (seq, md5)
    SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
  END LOOP;
END;
$$
;

and also using (in the inner loop of the above function)

SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001) LIMIT 1;

Then after each run, I queried my rand_samp table:

SELECT
  seq, COUNT(seq)
FROM rand_samp
GROUP BY seq
HAVING COUNT(seq) > 1;

And got the following counts:

For TABLESAMPLE SYSTEM_ROWS, I got 258, 63, 44 dupes, all with a count of 2. For TABLESAMPLE SYSTEM_TIME, I got 46, 54 and 62, again all with a count of 2.

Now, my stats are a bit rusty, but from a random sample of a table of 100M records,from a sample of 10,000, (1 ten-thousandth of the number of records in the rand table), I'd expect a couple of duplicates - maybe from time to time, but nothing like the numbers I obtained. Furthermore, if there was true randomness, I'd expect (a small number of) 3's and 4's also.

I ran two tests with 100,000 runs using TABLESAMPLE SYSTEM_ROWS and obtained 5540 dupes (~ 200 with 3 dupes and 6 with 4 dupes) on the first run, and 5465 dupes on the second (~ 200 with 3 and 6 with 4). The most interesting query was this however:

SELECT COUNT(s.seq)
FROM rand_samp s
WHERE s.seq IN (SELECT sb.seq FROM rand_samp_bis sb);

where I compare dupes in both runs of 100,000 with respect to each other - the answer is a whopping 11,250 (> 10%) are the same - which for a sample of 1 thousandth (1/1000) is WAY to much to be down to chance!

Results 100,000 runs for SYSTEM_TIME - 5467 dupes, 215 with 3, and 9 with 4 on the first group, 5472, 210 (3) and 12 (4) with the second. The number of matching records is 11,328 (again > 10%).

There's clearly (a LOT of) non-random behaviour going on. I'll leave it to the OP to decide if the speed/random trade-off is worth it or not!

Benchmark of other answers.

I decided to benchmark the other proposed solutions - using my 100 million record table from above. I ran all tests 5 times - ignoring any outliers at the beginning of any series of tests to eliminate cache/whatever effects. All the outlier values were higher than those reported below.

I'm using the machine with the HDD - will test with the SSD machine later. The .mmm reported means milliseconds - not significant for any answer but my own.

Daniel Vérité's answer:

SELECT * FROM
  (SELECT seq FROM rand TABLESAMPLE BERNOULLI(1)) AS s
 ORDER BY RANDOM() LIMIT 1;

Ran 5 times - all times were over a minute - typically 01:00.mmm (1 at 01:05.mmm).

Typical run:

test=# SELECT * FROM
  (SELECT seq FROM rand TABLESAMPLE BERNOULLI(1)) AS s
 ORDER BY RANDOM() LIMIT 1;
   seq
---------
 9529212
(1 row)

Time: 60789.988 ms (01:00.790)

Swav's answer:

SELECT md5 FROM rand OFFSET (
    SELECT floor(random() * (SELECT count(seq) from rand))::int
) limit 1;

Ran 5 times - all times were over a minute - from 01:03 to 01:29

Typical run:

test=# SELECT md5 FROM rand OFFSET (
    SELECT floor(random() * (SELECT count(seq) from rand))::int
) limit 1;
               md5
----------------------------------
 8004dfdfbaa9ac94243c33e9753e1f77
(1 row)

Time: 68558.096 ms (01:08.558)

Colin 't Hart's answer:

select * from rand where seq >= (
  select random()*(max(seq)-min(seq)) + min(seq) from rand
)
order by seq
limit 1;

Ran 5 times - times varied between 00:06.mmm and 00:14.mmm (Best of the Rest!)

Typical run:

test=# select * from rand where seq >= (
  select random()*(max(seq)-min(seq)) + min(seq) from rand
)
order by seq
limit 1;
   seq    |               md5
----------+----------------------------------
 29277339 | 2b27c594f65659c832f8a609c8cf8e78
(1 row)

Time: 6944.771 ms (00:06.945)

Colin 't Hart's 2nd answer (adapted by me):

WITH min_max AS MATERIALIZED -- or NOT, doesn't appear to make a difference
(
  SELECT MIN(seq) AS min_s, MAX(seq) AS max_s, (MAX(seq) - MIN(seq)) - MIN(seq) AS diff_s
  FROM rand
),
other  AS MATERIALIZED
(
  SELECT FLOOR(RANDOM() * (SELECT diff_s FROM min_max))::INT AS seq_val
)
SELECT seq, md5
FROM rand
WHERE seq = (SELECT seq_val FROM other);

Response time is between ~ 30 - 45ms with the odd outlier on either side of those times - it can even drop to 1.xxx ms from time to time. All I can really say is that it appears to be more consistent than either of the SYSTEM_TIME and SYSTEM_ROWS methods.

There is a major problem with this method however. If the underlying field that one is choosing for randomness is sparse, then this method won't return a value all of the time - this may or may not be acceptable to the OP? You can do something like (end of query):

SELECT seq, md5
FROM rand
WHERE seq >= (SELECT seq_val FROM other)
LIMIT 1;

(note >= and LIMIT 1). This can be very efficient, (1.xxx ms), but seems to vary more than just the seq =... formulation - but once the cache appears to be warmed up, it regularly gives response times of ~ 1.5ms.

Another advantage of this solution is that it doesn't require any special extensions which, depending on the context (consultants not being allowed install "special" tools, DBA rules...) may not be available.

One really WEIRD thing about the above solution is that if the ::INT CAST is removed, the query takes ~ 1 minute. This happens even though the FLOOR function should return an INTEGER. I only discovered that this was an issue by running EXPLAIN (ANALYZE BUFFERS).

With ::INT

   CTE other
     ->  Result  (cost=0.02..0.04 rows=1 width=4) (actual time=38.906..38.907 rows=1 loops=1)
           Buffers: shared hit=1 read=9
           InitPlan 4 (returns $3)
             ->  CTE Scan on min_max  (cost=0.00..0.02 rows=1 width=4) (actual time=38.900..38.902 rows=1 loops=1)
                   Buffers: shared hit=1 read=9
   InitPlan 6 (returns $5)
     ->  CTE Scan on other  (cost=0.00..0.02 rows=1 width=4) (actual time=38.909..38.910 rows=1 loops=1)
           Buffers: shared hit=1 read=9
 Planning Time: 0.329 ms
 Execution Time: 68.449 ms
(31 rows)

Time: 99.708 ms
test=#

Without ::INT

   CTE other
     ->  Result  (cost=0.02..0.04 rows=1 width=8) (actual time=0.082..0.082 rows=1 loops=1)
           Buffers: shared hit=10
           InitPlan 4 (returns $3)
             ->  CTE Scan on min_max  (cost=0.00..0.02 rows=1 width=4) (actual time=0.076..0.077 rows=1 loops=1)
                   Buffers: shared hit=10
   InitPlan 6 (returns $5)
     ->  CTE Scan on other  (cost=0.00..0.02 rows=1 width=8) (actual time=0.085..0.085 rows=1 loops=1)
           Buffers: shared hit=10
   ->  Parallel Seq Scan on rand  (cost=0.00..1458334.00 rows=208333 width=37) (actual time=52644.672..60025.906 rows=0 loops=3)
         Filter: ((seq)::double precision = $5)
         Rows Removed by Filter: 33333333
         Buffers: shared hit=14469 read=818865
 Planning Time: 0.378 ms
 Execution Time: 60259.401 ms
(37 rows)

Time: 60289.827 ms (01:00.290)
test=#

Note the (without ::INT)

   ->  Parallel Seq Scan on rand  (cost=0.00..1458334.00 rows=208333 width=37) (actual time=52644.672..60025.906 rows=0 loops=3)
         Filter: ((seq)::double precision = $5)

Parallel Seq Scan (with a high cost), filter on (seq)::double

(WHY double??).

And

Buffers: shared hit=14469 read=818865

compared to (with ::INT)

Buffers: shared hit=1 read=9

Finally, my own answer again (same machine, time & cache):

(this is now redundant in the light of the benchmarking performed above).

Ran my own benchmark again 15 times - typically times were sub-millisecond with the occasional (approx. 1 in 3/4) run taking approx. 25 milliseconds.

Typical run:

test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 401578.81 | 30ff7ecfedea088dab75932f0b1ea872
(1 row)

Time: 0.708 ms

So, it would appear that my solution's worst times are ~ 200 times faster than the fastest of the rest of the pack's answers (Colin 't Hart).

My analysis is that there is no perfect solution, but the best one appears to be the adaptation of Colin 't Hart's solution.

Finally, a GRAPHIC demonstration of the problem associated with using this solution for more than one record is shown below - taking a sample of 25 records (performed several times - typical run shown).

The tsm_system_rows method will produce 25 sequential records. This may be suitable for certain purposes where the fact that the random sample is a number of sequential records isn't a problem, but it's definitely worth keeping in mind.

test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(25);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 763140.01 | 7e84b36ab30d3d2038ebd832c241b54d
      6 | 763140.02 | a976e258f6047df18e8ed0559ff48c36
--
--    SEQUENTIAL values of seq!
--
      6 | 763140.23 | ad4f1b4362187d6a300aaa9aaef30170
      6 | 763140.24 | 0c7fcc4f07d27fbcece68b2503f28d88
      6 | 763140.25 | 64d4507b18b5481a724d8a5bb6ac59c8
(25 rows)

Time: 29.348 ms

A similar state of affairs pertains in the case of the SYSTEM_TIME method. As mentioned above, even with a minimum time of 1μs, it gives 120 records. Just as with SYSTEM_ROWS, these give sequential values of the PRIMARY KEY.

test=# SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001);

returns:

   seq    |               md5
----------+----------------------------------
 42392881 | e92f15cba600f0c7aa16db98c0183828
 42392882 | 93db51ea870e15202144d11810c8f40c
 42392883 | 7357bf0cf1fa23ab726e642832bb87b0
 42392884 | 1f5ce45fb17c8ba19b391f9b9c835242
 42392885 | f9922b502d4fd9ee84a904ac44d4e560
 ...
 ...  115 sequential values snipped for brevity!

Our sister site, StackOverflow, treated this very issue here. Good answers are provided by (yet again) Erwin Brandstetter here and Evan Carroll here. That whole thread is worth reading in detail - since there are different definitions of random (monotonically increasing/decreasing, Pseudorandom number generators...) and sampling (with or without replacement...).


Oracle:

The null literal does not have a type, but

null can be cast to any type, and this may be necessary when

calling overloaded procedures or functions
controlling the return type of the decode function, eg:

select decode('A','B',to_char(null),'A','1') from dual;
DECODE('A','B',TO_CHAR(NULL),'A','1')
-------------------------------------
1

select decode('A','B',to_number(null),'A','1') from dual;
DECODE('A','B',TO_NUMBER(NULL),'A','1')
---------------------------------------
                                      1

controlling column types of set operators like union when the first query block includes a null

null values stored in the database always have a type:

create table t(n integer, s varchar(10));
insert into t values(null, null);

select decode('A','B',n,'A','1') from t;
DECODE('A','B',N,'A','1')
-------------------------
                        1

select decode('A','B',s,'A','1') from t;
DECODE('A','B',S,'A','1')
-------------------------
1


Set Oracle Home (install Oracle Home) for Toad 12.6

Here's what worked, although I don't know if it's a good answer or not.  I had someone who could log in as an admin on my pc do the Oracle client install.  In other words, if you're not a full admin on your machine, it may fail, and not tell you why.

I had admin rights, I could edit the registry and path, but there were still some missing rights.  Logging on as administrator and doing the install worked fine.


If you're using SQL Server, You can use CROSS APPLY to return 1 Branch row for each Employee row:

SELECT e.ID AS EmployeeID,
  e.Name AS EmployeeName,
  b.BranchID,
  b.BranchName
FROM Employee e
CROSS APPLY (SELECT ID AS BranchID, BranchName FROM Branch) b
ORDER BY EmployeeID

See working db<>fiddle.

For Postgres, similar query but using LEFT JOIN LATERAL instead:

SELECT e.ID AS EmployeeID,
  e.Name AS EmployeeName,
  b.ID AS BranchID,
  b.BranchName
FROM Employee e
LEFT JOIN LATERAL (SELECT ID, BranchName FROM Branch) b ON true
ORDER BY EmployeeID

Working db<>fiddle here.


What are the possible performance implications of dropping a not used ‘surrogate key’ column?

I have a very large (>10m rows)

Tables with billions of rows in them are common — don't fixate on the size of the table, it is much more important to know how many blocks you scan to query it, which depends on the query and on indexes. You do not want to consider partitioning, or anything exotic, just because you have 10 million rows in a table.

  I essentially run two different types of queries on this table:

  "Give me all of the measurements between time start and time end of _reading_type 0 for meter_id X", or
  "Give me all of the measurements between time start and time end of _reading_type 0 for facility_id X" (I believe the facility_id
  column denormalizes the table, but saves what was an expensive join).

Then your indexes would be better off as:

"ix_measurement_timestamp_meter_id__reading_type" btree (_reading_type, meter_id, "timestamp")
"ix_measurement_timestamp_facility_id__reading_type" btree (_reading_type, facility_id, "timestamp")

… or better still make them partial indexes where _reading_type=0:

create index on measurement(meter_id,"timestamp") where _reading_type=0;

Calling a column "timestamp" is not a good idea, change it if you can.

  I am considering dropping the id column, which is a meaningless ‘surrogate key’, as I never, ever query it, but I'm curious what the performance implications would be.

Any impact on performance is going to be minimal compared to tailoring your indexes to make them more selective.


That's because full-text search treats hyphenated words specially:
SELECT to_tsvector('english', 'iDream  Ice cream  iScream');

               to_tsvector
------------------------------------------
 'cream':3 'ice':2 'idream':1 'iscream':4
(1 row)

The numbers behind the lexemes mark the position they had in the original text (cream is the third word, and so on). That is used for phrase search.
SELECT to_tsvector('english', 'iDream  Ice-cream  iScream');

                      to_tsvector
--------------------------------------------------------
 'cream':4 'ice':3 'ice-cream':2 'idream':1 'iscream':5
(1 row)

You see that the original hyphenated word is at the second position, and the parts are represented as following the hyphenated word.
So ice cream is not the same as ice-cream for PostgreSQL full text search.
In the first case, ice immediately follows idream, but not in the second case. That is why your query returns FALSE.
Look at what the parser does:
SELECT alias, token, lexemes FROM ts_debug('english', 'iDream  Ice-cream  iScream');

      alias      |   token   |   lexemes
-----------------+-----------+-------------
 asciiword       | iDream    | {idream}
 blank           |           |
 asciihword      | Ice-cream | {ice-cream}
 hword_asciipart | Ice       | {ice}
 blank           | -         |
 hword_asciipart | cream     | {cream}
 blank           |           |
 asciiword       | iScream   | {iscream}
(8 rows)

Perhaps the solution you are looking for would be to ignore hyphenated words and just keep their parts:
CREATE TEXT SEARCH CONFIGURATION en_no_hyphen
   (COPY = english);

ALTER TEXT SEARCH CONFIGURATION en_no_hyphen
   DROP MAPPING FOR asciihword, hword;

SELECT to_tsvector('en_no_hyphen', 'iDream  Ice-cream  iScream')
       @@ to_tsquery('en_no_hyphen', 'iDream<->Ice<->cream<->iScream');

 ?column?
----------
 t
(1 row)


NT SERVICE\MSSQLSERVER cannot be found to add as user for file permissions

What else has to be done to make this appear as a user account?

Nothing.  And it doesn't matter what your service account is.  The SQL Server service always has privileges assigned to the per-Service SID "NT Service\MSSQLSERVER".
If the files are on the SQL Server, just add permissions for this account:

And if the files are on a remote share, give the permissions to the machine account instead, eg <YourDomain>\,<YourServer>$.


You can't.

A view is a stored sub-query. MAXDOP is a query hint.


Restore msdb from SQL Server 2008 R2 to SQL Server 2012 instance

If you were trying to restore a user database - this would restore alright. The problem is that this is a system database. The system databases are designed for the version of SQL they were intended to work in. In order for SQL Server 2008 R2 MSDB to work in SQL Server 2012, you'd have to have no features different between the two versions. Make sense?

The Short Answer
To bring your jobs across, you'd script them out from the old server and apply the script on the new one. You can do this as simply as right clicking and scripting each job if you have a few. Or looking into a script with PowerShell or some other approach if you have a ton and don't want to one at a time.

To take the Logins you'd use the Sp_help_revlogin script I reference below and a script to copy server level roles and permissions from the old and copy them to the new..

Basically for 90% of what you'd bring across, I'm pretty sure the answer is "script it out" and then just apply that script on the new server and the logins, the jobs, etc. will all now live in the Master and MSDB databases designed for SQL Server 2012.

The slightly longer answer and a quick discussion on approaches to migration/upgrade

So if you are trying to transfer all of these objects to SQL Server 2012 you have two basic options on your approach.

Do an in place upgrade. Upgrade your instance from SQL Server 2008 R2 to SQL Server 2012..

Pros - you get all that 'stuff' (agent jobs, linked servers, logins, alerts, operators, mail profile, etc.) and you don't have to copy objects around..

Cons - it can be a bit messy, it works fine nowadays and is supported but I am paranoid and like to know for sure that I have success and a quick rollback option (if issues on new server, just revert back to your old server during a migration. With an in place upgrade, it is much more, well, final).

You can start here for an in place upgrade.

Migrate to a new server...

In this case you just flip the pros and cons from above.. The approach isn't that tough or strenuous. It just requires some good planning. Basically you:

On your old server, script out all of the objects you want to move according to the instructions and approach for each object type which can typically be found in Books Online (objects like Jobs, Linked Servers, SQL Agent Alerts, etc.)...
Use a tool like sp_help_revlogin to move your logins across and a script kind of like this to move the login permissions across..
Backup and restore your databases which already contain the in-DB users and permissions(I like doing this over detach attach because it helps preserve that rollback ability but I've seen and done this either way).. Change your compatibility mode if you are planning on supporting the DBs in 2012 mode and have tested them that way. Or keep them in 2008 mode if that was your plan.
Run those scripts for all of the objects like jobs, logins, etc. on the new server that you created above from the old server(good to do most of these after the DBs.. as logins will error on you if their databases that they default to aren't there or a T-SQL step in a job's database isn't there, etc.)

The other nice thing about this approach is you can do a trial run ahead of time during business hours.. Point a test or dev version of the app(s) used on that instance to the 2012 and see what breaks.. Fix it and test out your checklist, your rollback plan, etc. and do that prep for go live night.


A good way to find such information is to go to the index of the manual and look for "restartpoint". Which indeed is listed there and leads to WAL configuration

In archive recovery or standby mode, the server periodically performs restartpoints, which are similar to checkpoints in normal operation: the server forces all its state to disk, updates the pg_control file to indicate that the already-processed WAL data need not be scanned again, and then recycles any old log segment files in the pg_wal directory. Restartpoints can't be performed more frequently than checkpoints in the master because restartpoints can only be performed at checkpoint records. A restartpoint is triggered when a checkpoint record is reached if at least checkpoint_timeout seconds have passed since the last restartpoint, or if WAL size is about to exceed max_wal_size. However, because of limitations on when a restartpoint can be performed, max_wal_size is often exceeded during recovery, by up to one checkpoint cycle's worth of WAL. (max_wal_size is never a hard limit anyway, so you should always leave plenty of headroom to avoid running out of disk space.)


Setting up Always On Availability Groups with Windows Server Failover Cluster

If I understand your situation correctly, SQL Server isn't even in the picture right now and you're stuck at configuring Windows Server Failover Clustering?

If that's the case, then the best place to look for further clues (as this is quite the open ended question that could take a person many hours to investigate and depend upon settings in your specific environment) would be the cluster log. One of the easiest ways to gather the cluster log is to use the powershell command Get-ClusterLog it can take multiple inputs and I'd recommend using at least -UseLocalTime and -Directory to change the log from UTC to local and the directory that you'd like the logs to be output in, respectively.

Since this, currently, has no involvement with SQL Server the question may be closed to migrated (for future reference).


I don't think this question can be answered without further details but some general thoughts:

What kernel are you talking about? If it's the OS Kernel then there are a few different ways depending on your OS and/or your Linux distribution. And it's depends  on your job role - are you responsible for the database server's Os, too or are you just responsible for the database?
Do you have some kind of monitoring software like e.g. EnterpriseManger/GridControl? If so, a lot of baselining and auditig can be done with this tool, but beware that there a a lot of features that require special licenses. Read the most recent licensing documentaition for that. Even if you don't use the tool itself (e.g. for integration in another monitoring tool) but want to query the underlying views, you have to buy the license.
If database auditing is required have a look at the documentation for your database release. With 12c the unified auditing is the way to go.


Casting fraction number to decimal throws Arithmetic overflow error

From error message,

  Arithmetic overflow error converting varchar to data type numeric.

My first place where I could change is :

 ELSE a.Marks

and change it to

ELSE try_convert(int, a.Marks)

or convert to decimal , if you want

Later edit:
In case expression you have a few THEN with different data types: int, numeric, varchar. Due to data type precedence, when combines different data types, the lower data type is converted to the type with higher precedence
See here information about it.

you can see this , by this example:

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 1 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select -1 as col ) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0.5 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select '-+' as col) as a

Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10           0
Base Type   Precision   Scale
numeric     1           1
Base Type   Precision   Scale
varchar     0           0


Duplicating rows based on a count column SQL Server

Below is an example using a join to a tally CTE. It would be better to create a materialized tally/numbers table to facilitate this sort of task going forward and improve performance too.

WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,codes AS (SELECT Code, SUM(CodeCount) AS CodeCount FROM dbo.MyTable WHERE Code = '1234A' AND PlaceID = 1 GROUP BY Code)
SELECT Code
FROM codes
JOIN tally ON tally.num <= codes.CodeCount;


How much memory is needed to keep a table cached? (SQL Server 2008)

This is a two part answer.

The first part is buy as much RAM as you can afford (as long as it'll fit into the server).

The second part is that you'll always see reads on data.  The trick is to see if those reads are physical or logical.  Physical reads are going down to the disk.  Logical reads are read from the buffer pool in memory.  You can see this by putting "SET STATISTICS IO ON" at the top of the SQL Script then running it in SSMS.  On the messages tab you'll get specific IO information for each query that is being run, specifically you'll get the number of both logical and physical operations (reads in your case) that are happening on a table by table basis.  If the physical read numbers are pretty high (these numbers are basically the number of blocks that were read so multiple by 8 then divide by 1024 to get a number in Megs) and then you need more RAM.  If the logical read numbers are high, then you aren't actually going to disk but your query may need tuning, indexes created, etc.

(Note: For the purposes of this answer, I'm assuming SELECT statements only.  INSERT/UPDATE/DELETE operations are a little more complex, but the same basics apply.)


Primary keys is a key concept of the relational model. From a pure practical way, in general you have 2 options:

Choose as a primary key one of the candidate keys: that is, a column or combination of columns that unequivocally identifies the row. For example, in Twitter, we could think that the user name or the user email in a users tables is a candidate key, so both could be used for the primary key
Create an arbitrary identifier that is created on insertion time (MySQL's AUTO_INCREMENT is a paradigmatic example, which we could generate on the same users table)

There are some reasons to choose one over the other (some good and some bad): For example, setting meaningful columns as primary key makes later harder to change those values, as many other tables or logic could depend on it. That could be a reason for or against it. For example, most sites like Twitter actually use an internal id so that you can easily change your username and email. Some ORMs require, or make the logic easier if you all your tables have an auto_increment int or bigint called id. Numeric ids are shorter, which in some specific cases may make the foreign keys or other parts shorter, and maybe more efficient (that is the case for MySQL's InnoDB in many cases due to its internal structure). On the other side, having and arbitrary id as a primary key may not be adequate for your logic, as there is not direct connection between it and the actual row.

Having said that, in my opinion -and that is without knowing your internals- you should give a human-recognizable identifier to your constants and query though that column:

if (name == "kill_all_humans"))
  do something

Whether name should be a primary key or a secondary key (and having a SERIAL in addition) is probably going to be of lesser importance, assuming your table has just a few values (not millions). That way, you can set and read the value independently of it having an arbitrary identifier. That way your code is 100% portable.

As side notes:

If identifiers are important to you, make sure you import them correctly, those may be important in the event of a later update
I do not think int(5) means what you think it means