Could anyone please help me understand MSSQL Compatibility Level?

Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?

If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.

  Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?

When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance.  In this case, 90 is still functional on the 2012 instance, so it was left as-is.

  Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?

  Upgrade is as much simple as "ALTER DB"?? like

Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.

You can remain at the lower compatibility level as long as you like.  However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels.  You can see a list of some of the changes here:

Differences Between Lower Compatibility Levels and Levels 110 and 120

Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available.  So the next time you upgrade, your database will automatically be updated to compatibility level 100.  You can see a table of supported levels on each engine version here:

ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments


There is an algorithm (will find it), but practically it will stay there:

while being used (Edit: See AdaTheDev's answer)
until statistics update (also implied by index rebuild)
until definition changes (including whitespace and case)
SQL instance restart
until severe memory pressure

SQL Server 2008 article


Add a suffix to duplicates on PostgreSQL

You can use ROW_NUMBER function to add a suffix to each repeated value.

If you'd rather avoid the first one (mostly for those rows without duplicates):

with ct as
(
  select id , row_number() over (partition by data order by id) rn
  from   t
)
update t
set    data = data || case when ct.rn = 1 then '' else (ct.rn-1)::text end
from   ct
where  ct.id = t.id;

select * from t;

id | data
-: | :---
 1 | foo
 2 | foo1
 3 | foo2
 4 | bar
 5 | bar1

db<>fiddle here


Take query results and insert into new table

If you are trying to insert into a new table that has already been created, you can say:

    ;WITH Nbrs ( n ) AS (
        SELECT CAST(1 as BIGINT) UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 480000)

    INSERT dbo.ExistingTable(First,Second,Third,Fourth,Fifth,Sixth)
----^^^^^^ this line

    SELECT
     tblOrderRandomNumbers.[1] AS First,
     tblOrderRandomNumbers.[2] AS Second,
     tblOrderRandomNumbers.[3] AS Third,
     tblOrderRandomNumbers.[4] AS Fourth,
     tblOrderRandomNumbers.[5] AS Fifth,
     tblOrderRandomNumbers.[6] AS Sixth
    FROM
     Nbrs
    ...

If you trying to create a new table based on the output, you can say:

    ;WITH Nbrs ( n ) AS (
        SELECT CAST(1 as BIGINT) UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 480000)
    SELECT
     tblOrderRandomNumbers.[1] AS First,
     tblOrderRandomNumbers.[2] AS Second,
     tblOrderRandomNumbers.[3] AS Third,
     tblOrderRandomNumbers.[4] AS Fourth,
     tblOrderRandomNumbers.[5] AS Fifth,
     tblOrderRandomNumbers.[6] AS Sixth

    INTO dbo.NewTable
----^^^^^^ this line

    FROM
     Nbrs
    ...


How to find cores and logical processors in SQL Server

See the answer to this question.

It quotes https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143760(v=sql.105)

  SQL Server supports the specified number of processor sockets
  multiplied by the number of logical CPUs in each socket. For example,
  the following is considered a single processor for purposes of this
  table: A single-core, hyper-threaded processor with 2 logical CPUs per
  socket. A dual-core processor with 2 logical CPUs. A quad-core
  processor with 4 logical CPUs. SQL Server is licensed per processor
  socket, and not per logical CPU basis.


SQL Server by default operates under Read Committed isolation.

If you want SQL Server to behave more like other implementations, what you're looking for is either Read Committed Snapshot Isolation, or Snapshot Isolation.

You can read more about them here.

Full disclosure, I am a Brent Ozar Unlimited employee

Using NOLOCK or Read Uncommitted is generally frowned upon for serious production use, as it can allow dirty reads and other unintended artifacts while modification queries are in progress.


why the processing is sometimes terminated , sometimes not

You can force the same behaviour accross errors with SET XACT_ABORT

SET XACT_ABORT ON;

From the documentation:

  Specifies whether SQL Server automatically rolls back the current
  transaction when a Transact-SQL statement raises a run-time error.

Source

By default XACT_ABORT = OFF this means that different errors create different outcomes.

To force the behaviour on error to be the same, add SET XACT_ABORT ON; at the beginning of your script.

Testing with your example

SET XACT_ABORT ON;

DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
 (BusinessEntityID
 ,PersonID
 ,ContactTypeID)
 VALUES
 (0,0,1);
SELECT @Error = @@ERROR;
PRINT 'The Value of @Error is ‘ + CONVERT(varchar, @Error)'

With the only error shown:

  Msg 547, Level 16, State 0, Line 17 The INSERT statement conflicted
  with the FOREIGN KEY constraint "FK_Person". The conflict occurred in
  database "my_test", table "Person.Person", column 'PersonID'.

Also note this important statement regarding error handling and SET XACT_ABORT

  The THROW statement honors SET XACT_ABORT. RAISERROR does not.
  New applications should use THROW instead of RAISERROR.

Source


Query Netezza to find all tables in views

Check this Link and provide the error that its generated in your current query.

or its this query not returning data ? in that case maybe could be the where statement


How to determine or predict space on disk per table row?

Your calculation is close for the maximum bare row size. The actual range is 68 - 212 bytes per row, or 84 - 228 bytes including the index.

Most importantly a varchar(n) does not have to occupy the maximum length. The data type is implemented with varlena internally, which adds 1 byte overhead for short strings on disk, plus the actual number of bytes for the string.

Data types implemented with varlena don't require alignment padding on disk.

And a NULL value (allowed for content_id) is effectively free. See:

Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

Finally the PRIMARY KEY constraint is implemented using a standard btree index. We have to add that to the total size on disk.

So the calculation for the row size on disk is:

23        bytes  tuple header
 1        byte   padding or null bitmap
 8        bytes  id BIGSERIAL PRIMARY KEY
 0 - 51   bytes  content_id VARCHAR (50) NULL
 0        bytes  alignement padding
 2 - 101  bytes  type VARCHAR (100) NOT NULL
 ?        bytes  alignment padding
 8        bytes  size bigint NOT NULL
 8        bytes  timestamp1 timestamp NOT NULL
 8        bytes  timestamp2 timestamp NOT NULL
---
min 64 (incl 6 bytes padding) - max 208 bytes (no padding required)

+ 4      bytes item identifier in heap page
---
68 - 212 bytes

+ 16     bytes for the index tuple
---
84 - 228 bytes

Plus some overhead for heap pages and index pages as detailed in these related questions:

Calculating and saving space in PostgreSQL
Measure the size of a PostgreSQL table row
Configuring PostgreSQL for read performance

Actual row sizes on disk (without item identifier):

SELECT pg_column_size(a) AS size_on_disk, *
FROM   audit a;

Note that size in RAM can differ:

SELECT pg_column_size(content_id) AS content_id_size_on_disk
     , pg_column_size(content_id || '') AS content_id_size_in_ram
     , content_id
FROM   audit a;

See:

What is the overhead for varchar(n)?

About ...

  Which brings me to think that Postgres reserves a space of 24 kb to start

The heap starts with 0 bytes. The index starts with one meta-page (8kb).
After adding the first row, we see a minimum of 8 kb for the first heap page and 16 kb for the index (1 meta page + 1st index page). Details in the fiddle!

db<>fiddle here


Should I backup or copy [an Access database file]?

The "Back Up Database" and "Save Database As" options do the same thing. The only real difference is that "Back Up Database" will provide a default file name like

MyDatabase_2014-11-18.accdb

whereas for "Save Database As" the new name will default to

MyDatabase1.accdb

They both do what is essentially a "Compact and Repair" into a new database file. (The original database file is not compacted, only the resulting copy.)


There is no log of when this was changed.  By default, and for 99.9% of systems this should be set to the default value of 0 so that SQL Server can manage this by itself.  Set it back to 0, restart the SQL Server instance and be done with it.


What should one do if the auto increment primary key exceeds the maximum?

Since you need to go to BIGINT, prepare for Disk Space Growth.

INT is 4 bytes
BIGINT is 8 bytes

For every table that uses INT and you want to switch to BIGINT, you must forecast how much additional space to expect.

For example, for figure out how much space mydb.mytable will increase when you shift all INT(10) columns to BIGINT, run this query

SELECT CONCAT('SELECT COUNT(1)*',int_column_count*4,
' SizeIncreaseInBytes FROM ',table_schema,'.',table_name,';') INTO @SQLStmt
FROM (SELECT table_schema,table_name,
COUNT(1) int_column_count from
(
    SELECT table_schema,table_name,column_type
    FROM information_schema.columns
    where table_schema = 'mydb'
    AND table_name = 'mytable'
    AND column_type = 'int(10)'
) AA group by table_schema,table_name) A;
SELECT @SQLStmt;
PREPARE s FROM @SQLStmt;
EXECUTE s; DEALLOCATE PREPARE s;

Let's use the table dev_oxygen.bplv_reg

mysql> show create table dev_oxygen.bplv_reg\G
*************************** 1. row ***************************
       Table: bplv_reg
Create Table: CREATE TABLE `bplv_reg` (
  `id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(40) DEFAULT NULL,
  `lastname` varchar(40) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `zipcode` varchar(11) DEFAULT NULL,
  `email` varchar(200) NOT NULL,
  `phone` bigint(20) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `newsletter` smallint(6) NOT NULL DEFAULT '0',
  `ip_address` varchar(20) DEFAULT NULL,
  `fb_user_id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Notice the table has age is int(10)

Here is the table count:

mysql> SELECT COUNT(1) FROM dev_oxygen.bplv_reg;
+----------+
| COUNT(1) |
+----------+
|     2038 |
+----------+
1 row in set (0.00 sec)

mysql>

Now let's compute the number of bytes the table will have to grow:

mysql> SELECT CONCAT('SELECT COUNT(1)*',int_column_count*4,
    -> ' SizeIncreaseInBytes FROM ',table_schema,'.',table_name,';') INTO @SQLStmt
    -> FROM (SELECT table_schema,table_name,
    -> COUNT(1) int_column_count from
    -> (
    ->     SELECT table_schema,table_name,column_type
    ->     FROM information_schema.columns
    ->     where table_schema = 'dev_oxygen'
    ->     AND table_name = 'bplv_reg'
    ->     AND column_type = 'int(10)'
    -> ) AA group by table_schema,table_name) A;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @SQLStmt;
+-----------------------------------------------------------------+
| @SQLStmt                                                        |
+-----------------------------------------------------------------+
| SELECT COUNT(1)*4 SizeIncreaseInBytes FROM dev_oxygen.bplv_reg; |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @SQLStmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s; DEALLOCATE PREPARE s;
+--------------------+
| SizeIncreaseInBytes|
+--------------------+
|               8152 |
+--------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

This number is just the size increase (in bytes) for the age column.

What about the id? It's not int(10) but int(11). Could you forecast for int(11), Sure, just replace int(10) in the query with int(11).

What about the primary key and what if a primay key has multiple int columns defined as int(11)?

Let's pick a different table called oxygen.history;

mysql> show create table oxygen.history\G
*************************** 1. row ***************************
       Table: history
Create Table: CREATE TABLE `history` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `nid` int(11) NOT NULL DEFAULT '0',
  `timestamp` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`nid`),
  KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select count(1) from oxygen.history;
+----------+
| count(1) |
+----------+
|      564 |
+----------+
1 row in set (0.00 sec)

mysql>

This table has two columns in the primary key, both columns are int(11)

mysql> select CONCAT('SELECT COUNT(1)*',int_column_count*4,
    -> ' IndexSizeIncreaseInBytes FROM ',table_schema,'.',table_name,';')
    -> INTO @SQLStmt FROM
    -> (SELECT AA.table_schema,AA.table_name,
    -> COUNT(1) int_column_count FROM
    -> (
    ->     select table_schema,table_name,column_name
    ->     from information_schema.columns
    ->     where table_schema ='oxygen'
    ->     and table_name='history'
    ->     and column_type = 'int(11)'
    -> ) AA INNER JOIN
    -> (
    ->     select table_schema,table_name,column_name
    ->     FROM information_schema.STATISTICS
    ->     WHERE INDEX_NAME='PRIMARY'
    -> ) BB
    -> USING (table_schema,table_name,column_name)
    -> GROUP BY AA.table_schema,AA.table_name) A;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @SQLStmt;
+-----------------------------------------------------------------+
| @SQLStmt                                                        |
+-----------------------------------------------------------------+
| SELECT COUNT(1)*8 IndexSizeIncreaseInBytes FROM oxygen.history; |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @SQLStmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s; DEALLOCATE PREPARE s;
+--------------------------+
| IndexSizeIncreaseInBytes |
+--------------------------+
|                     4512 |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Based on how much increase is forecasted for the primary key, you can now increase these

MyISAM Key Cache (if you are forecasting for a MyISAM table, sized by key_buffer_size)
For InnoDB, you combine the two queries above to measure data and index size increases to size up the InnoDB Buffer Pool (size by innodb_buffer_pool_size).


MySQL: fetch multiple global status and system variable in a single query

select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Threads_cached' or VARIABLE_NAME like 'Threads_connected' or VARIABLE_NAME like 'Threads_created' or VARIABLE_NAME like 'Threads_running' or VARIABLE_NAME like 'Select_scan';


I don't know enough about Oracle to know if the below is valid syntax there or not... or if there are niftier tricks specific to that RDBMS.  However, on SQL Server I would do something like this.
/** If you are updating small numbers of rows then this is probably good enough
    on SQL Server.  Properly indexed tables, etc.
    **/
UPDATE mytab
SET colA = COALESCE(colA, 1)
    , colB = COALESCE(colB, 2)
WHERE EXISTS (SELECT TOP (1) 1 FROM othertab WHERE othertab.id = mytab.otid AND othertab.Lock = 'SomeLock')
    AND (   colA IS NULL
            OR colB IS NULL
            )

/** Larger numbers of rows could do better like this.
    **/
UPDATE M
SET colA = COALESCE(colA, 1)
    , colB = COALESCE(colB, 2)
FROM mytab AS M
    INNER JOIN othertab AS O ON O.id = M.otid AND O.Lock = 'SomeLock'
WHERE M.ColA IS NULL
    OR M.ColB IS NULL


Sql Server Service Pack Update Notifications

You could subscribe to the RSS feed on Brent Ozar's new site sqlserverupdates.com

He keeps an up to date list of the latest Service Packs and Cumulative Updates required for each version of SQL Server since 2005.


So, the answer was simple, I was using "!=", I needed to use "<>".
Problem solved.
Sc


Azure sql database how to track table schema change

You should had enabled Azure SQL Auditing.
Here you can find all about it.
https://docs.microsoft.com/en-us/azure/azure-sql/database/auditing-overview#subheading-1
Now you have to restore the database as a new database with an alternative name and the examine te differences in table definition.


Is there any difference between these two version of MySQL - 5.1.73-community-log Vs 5.1.73-community

They are the same software. The -log suffix is simply appended to the version string at runtime.
Here's an explanation (nearly from the last century), by long-time MySQL author Paul DuBois:

I want to know what the -log means and how it got in there.

-log is added if you start the server with logging turned on.
-debug is added if you're running a debugging version of the server with debugging on.
-demo is added if the server is running in demo mode

I verified this in the source, in sql/mysqld.cc, function set_server_version():
. . .
if (opt_log || opt_slow_log || opt_bin_log)
  strmov(end, "-log");                        // This may slow down system
. . .

I think the meaning of that comment is not that appending four extra characters to the version slows down the system, but that logging may slow down the system, so it's worth noting to the DBA that logging is enabled.


I think a lot of people solve this with the STUFF function.  I found an example from this link and adapted it.

SET NOCOUNT ON

DECLARE @Table TABLE (StringText VARCHAR(100))
DECLARE @ConcatString VARCHAR(100)

INSERT INTO @Table VALUES ('Tom')
INSERT INTO @Table VALUES ('Dick')
INSERT INTO @Table VALUES ('Harry')

SELECT @ConcatString = STUFF((
            SELECT ',' + StringText
            FROM @Table
            ORDER BY StringText
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

PRINT @ConcatString

As Dan Guzman points out in the comments, specifying TYPE and value method avoids entity references in the resultant string for special characters like ampersand.


Best practice Azure Datawarehouse for less data-movements

For Azure SQL Data Warehouse, unless you have have millions of records, then it's unlikely you will really benefit from clustered columnstore indexes.  In fact this could even be detrimental to performance.  For smaller volume tables, you would probably be better off with either heap (for fast load) or clustered index.  ROUND_ROBIN will at least ensure even distribution.  If you do plan to hash distribute your table, ensure your chosen hash column will give even distribution, ie has a large number of distinct values.  What is EMP_BK for example?

I would generally recommend having a table to store dates, rather than some trick construct.  The table is likely to scale better.  As the datatype is date, it again seems unlikely there will be enough rows in this table to justify clustered columnstore.  Try this with clustered index instead.

Have a read through the 'Hash distribute large tables' section in this article:

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices

Just as an observation, all columns in your tables are nullable, which would be bad for a dimensional model (you cannot build cubes on NULLs) and generally does not represent good data modelling.

If you have a genuine performance problem, consider posting an EXPLAIN plan and give an idea of the number of rows in your table and the type of data.  As mentioned, for your larger tables, to avoid data movement, hash distribute your tables on the same column.


Does changing a parameter value in a stored procedure before the query affect the cardinality estimate?

Does it count as using a local variable if the value originated from a parameter?

No, SQL Server will still see the reference as a parameter, which may be sniffed.

"Parameter sniffing" refers to a process whereby SQL Server's
execution environment "sniffs" the current parameter values during
compilation or recompilation, and passes it along to the query
optimizer so that they can be used to generate potentially faster
query execution plans. The word "current" refers to the parameter
values present in the statement call that caused a compilation or a
recompilation. Both in SQL Server 2000 and SQL Server 2005, parameter
values are sniffed during compilation or recompilation for the
following types of batches:

Stored procedures
Queries submitted via sp_executesql
Prepared queries

Generally, the plan for the whole procedure is compiled (and parameters sniffed) before execution begins, so your SET will not have executed when the sniffing occurs. An exception may occur if the statement is recompiled for any reason after the procedure starts executing. In that case, the recompilation process will see the new value you have set.
In most cases, each statement referencing a parameter will be optimized based on the sniffed value, not any changes you have made within the procedure.
Further reading: Parameter Sniffing, Embedding, and the RECOMPILE Options


You are confusing PL/PgSQL with SQL.

You do not need the RETURN QUERY, nor is it valid in normal SQL.

Simply SELECT text FROM eightks ...

Additionally, if you want to direct output to a particular file with psql's \o, you must run the \o command before the statement you want to write the output for.

Finally - I wonder if you should be looking into the \copy command, if you're trying to write query output to files.

\copy (SELECT ....) TO '/some/file' WITH (FORMAT CSV)


How to use the dbms_sql to get the analyze for insert into statement

The child_number argument to dbms_xplan.display_cursor needs to be taken from v$sql as well, it's not necessarily zero. (I'm not exactly sure what influences that number, but dropping and recreating table2 in your example gives me a new child_number each time).

Here's a modified version of your procedure (fixed a few typoes in what you have above):

DECLARE
  v_cursor NUMBER;
  stmt     VARCHAR2(2000);
  s        VARCHAR2(40);
  cn       NUMBER;
  CURSOR cc1
  IS
    SELECT voc.SQL_ID, vs.child_number
    FROM v$open_cursor voc
    LEFT JOIN v$sql vs
    ON voc.SQL_ID  =vs.SQL_ID
    WHERE voc.SID IN (SELECT sid FROM v$mystat)
      AND TO_CHAR(SUBSTR(vs.SQL_FULLTEXT,1,2000))=stmt;
BEGIN
  DELETE FROM table1;
  v_cursor:=dbms_sql.open_cursor;
  stmt    :='insert into table2 values (''a'',''b'')';
  dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
  OPEN cc1;
  LOOP
    FETCH cc1 INTO s, cn;
    EXIT;
  END LOOP;
  CLOSE cc1;
  -- To inspect the child number:
  -- dbms_output.put_line(s || ' '|| cn);
  INSERT INTO table1 SELECT * FROM table(dbms_xplan.display_cursor(s,cn,'ALL'));
END;
/


How to make a union view execute more efficiently?

The comments on the question show that the issue is that the test database the OP was using to develop the query had radically different data characteristics than the production database.  It had much fewer rows and the field being used for filtering wasn't selective enough.

When the number of distinct values in a column is too small the index may not be sufficiently selective.  In this case a sequential table scan is cheaper than an index seek/row lookup operation.  Typically a table scan makes exensive use of sequential I/O, which is much faster than random access reads.

Often, if a query would return more than just a few percent of rows it will be cheaper just to do a table scan than an index seek/row lookup or similar operation that makes heavy use of random I/O.


Selecting preferred row in database based on condition

If there is no another contractor_code values except 24 and 60, then

SELECT /* ch.contractor_no */ sh.contractor_no ,
       CASE WHEN COUNT(DISTINCT sh.contractor_code) = 2
            THEN 1
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
GROUP BY /* ch.contractor_no */ sh.contractor_no
ORDER BY multiple_roles DESC

If there exists another contractor_code values except 24 and 60, then

SELECT /* ch.contractor_no */ sh.contractor_no ,
       CASE WHEN COUNT(DISTINCT CASE WHEN sh.contractor_code IN (24, 60)
                                     THEN sh.contractor_code
                                     END) = 2
            THEN 1
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
GROUP BY /* ch.contractor_no */ sh.contractor_no
ORDER BY multiple_roles DESC


First visible improvement (assuming that there is no date_awarded values in future) is
WHERE date_awarded >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

Second - the index by (subscription_id, date_awarded, coins) (or maybe (date_awarded, subscription_id, coins), depends on table statistic, must be tested).


Strange upper() behaviour for dateranges in PostgreSQL

'[2005-11-01,2005-12-04]' is just another string literal for a date range to represent the canonical form '[2005-11-01,2005-12-05)'. The documentation:

  The built-in range types int4range, int8range, and daterange all use
  a canonical form that includes the lower bound and excludes the upper
  bound; that is, [).

Bold emphasis mine.

Else you would have two equal values and upper() would still return a different result, which wouldn't be acceptable.


Query to check for free disk space and send an alarm if the disk space exceeds threshold value

Check out this article:
Disk Space Monitoring: How To

And do not forget to monitor other crucial metrics: log file usage, cpu, blocking and so on...


How do I resolve Postgresql error, "could not determine which collation to use for string comparison"?

Found the error in the Release Notes for 9.1.2

  Make contrib/citext's upgrade script fix collations of citext columns
  and indexes (Tom Lane)

  Existing citext columns and indexes aren't correctly marked as being
  of a collatable data type during pg_upgrade from a pre-9.1 server.
  That leads to operations on them failing with errors such as "could
  not determine which collation to use for string comparison". This
  change allows them to be fixed by the same script that upgrades the
  citext module into a proper 9.1 extension during CREATE EXTENSION
  citext FROM unpackaged.

  If you have a previously-upgraded database that is suffering from this
  problem, and you already ran the CREATE EXTENSION command, you can
  manually run (as superuser) the UPDATE commands found at the end of
  SHAREDIR/extension/citext--unpackaged--1.0.sql. (Run pg_config
  --sharedir if you're uncertain where SHAREDIR is.)

So I looked in that file for the UPDATE commands and found these:

UPDATE pg_catalog.pg_type SET typcollation = 100
WHERE oid = 'citext'::pg_catalog.regtype;

UPDATE pg_catalog.pg_attribute SET attcollation = 100
WHERE atttypid = 'citext'::pg_catalog.regtype;

UPDATE pg_catalog.pg_index SET indcollation[0] = 100
WHERE indclass[0] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[1] = 100
WHERE indclass[1] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[2] = 100
WHERE indclass[2] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[3] = 100
WHERE indclass[3] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[4] = 100
WHERE indclass[4] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[5] = 100
WHERE indclass[5] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[6] = 100
WHERE indclass[6] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[7] = 100
WHERE indclass[7] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);


Poor SQL Server performance when management studio closed

The database you are querying probably has "auto close" enabled.

The connection from SSMS stops the database closing. When you close SSMS, the database closes. A subsequent call requires it to be opened.

Auto close is the default for SQL Server Express and is not a good idea:

http://blogs.msdn.com/b/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-auto-close
https://stackoverflow.com/questions/706732/what-is-the-auto-close-option-when-creating-a-new-database-in-sql-server-2008


lost all my databases after reinstalling sql server management studio

Background

Q1

Did you reinstall SSMS or your actual database engine? Can you add a picture with what you are seeing?

OP - A1

Q2

Did you use the same login as before? If you reinstalled your database engine the database files might still be there. Can you check your default Data / Log folders to see if there are any database files there? If yes, you could try Attaching these databases again. What does this return? SELECT IS_SRVROLEMEMBER ('sysadmin')

OP - A2

  Yes, I use the same login as I was using before. I pasted "SELECT
  IS_SRVROLEMEMBER ('sysadmin')" and run it on SQL query window and the
  result is " no column name (1) nothing else. I don't know where my
  database was saved to look on it if I can find any thing related to my
  lost database. Could you please tell us where our databases would be
  saved by default in C drive?

Q3£

This means that you are a sysadmin on the instance and that you should be able to see the database if it exists. You can try finding the system databases by using select physical_name from sys.master_files where database_id = 1 and find  the default data path by executing SELECT SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile]

OP - A3

  Yes, I found two files: LostDatabaseName.mdf and
  LostDatabaseName_log.ldf. Now, how can I move them back to SSMS?

Solution

You can try attaching the database to your new instance.

EXEC sp_attach_db @dbname = N'LostDatabaseName',
    @filename1 =
N'C:\FoldersHere\LostDatabaseName.mdf',
    @filename2 =
N'C:\FoldersHere\LostDatabaseName_log.ldf';

An example from Microsoft docs

EXEC sp_attach_db @dbname = N'AdventureWorks2012',
    @filename1 =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf',
    @filename2 =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf';

Source

I would also advise to not keep all your database files onC:\ drive. If your drive fills up your server will crash and corruption can occur. It is also important to take backups of your databases, and putting these on separate locations.


MongoDB on Raspberry Pi --dbpath not setting

Firstly instead of installing mongodb through apt just get a tar or zip of it.

1)Extract it to your home folder or wherever you would like.

2)Mark the mongod,mongo,mongos executable.

3)make a conf file say mongod.conf and add

 dbpath=conf/data/db
 logpath=conf/log/mongod.log
 logappend=true
 port = 27001
 fork = true

4)Start mongod by

 path/bin/mongod --config path/mongod.conf

"bin" because you will find mongod in bin folder after extract


Low performance with dynamic queries and cursors

If you are considering using cursors in SQL Server, it may be helpful to use arguments that best fit your use case, as they may significantly change the performance of the cursor.

Conveniently, there have been many performance studies on these arguments in blog posts to look at. All possible arguments can be found for the current SQL Server version here: DECLARE CURSOR (Transact-SQL)

My favorite for cases similar to yours is:

DECLARE CURSOR LOCAL FAST_FORWARD

LOCAL tells SQL Server that the data set your working with is the only one it should care about. FAST_FORWARD provides flexibility to choose between static and dynamic SQL (though one downside is that it inhibits parallelism). One example of why these two arguments matter when determining cursor performance can be found here: Using SQL Server cursors – Advantages and disadvantages

Aside from this, large data sets tend to perform better, and are more accurate by using set based operations. This is due to two things: the nature of changing data sets/the limitations of cursors when data changes, and the inability to take advantage of parallelism which could severely cripple performance. Like Peter and I mention in the comments below this answer, fully testing and understanding cursor options, or not even using a cursor at all, could drastically change not only your query performance, but your consistency.


Ola Hallengrens - Output File cleanup does not delete files

I should add that this works fine with 2 or more instances that are not set up with availability groups. It's only when it's more then 1 instance and AG set up. But what I did instead was to edit the parameters and change $(ESCAPE_SQUOTE(SQLLOGDIR)) to the actual path folder and it worked great.


Copy few rows from one database to another

Off the top of my head, here are a few ways to do it without resorting to 3rd-party tools.

If you are using PostgreSQL 9.3, you can use the extension postgres_fdw.

If you are using an older version of PostgreSQL have a look at dblink

If you want a command-line only solution, and you are using Linux (or Cygwin etc), you could try something like the following:

psql -d yourdb -qtAXc "COPY (select col1, col2, col3 from your_table where id between 123 and 456) TO STDOUT CSV HEADER" -U youruser -h localhost -p 5432 \
| psql -d yourremotedb -U youruser -h your_remote_host -qtAXc "COPY yourtable FROM STDIN CSV HEADER"


Cannot drop distribution database in SQL Server 2012

first of all, thanks for the help and suggestions. I have now managed to delete the distribution database without rebooting the server. this is how i did it.

Created a distribution database with similar file locations on a different server.
Took the backup of this new distribution database.
Restored this backup on the existing distribution database with REPLACE, MOVE option.
Finally disabled distribution on the server.

Thanks again for the help.


To get the first author of the first book, you can use

select ExtractValue(xml_doc, '//book[1]//author[1]');

To get a real distinct list you would have to write a stored function that circles through all books and all authors.


How to speed up query containing concat() in WHERE clause?

Since your situation is

product is commercial
you cannot change the query
you cannot change table layout

Your best shot (really your only shot) would be

ALTER TABLE table2 ADD INDEX status_table1_ndx (status,table1);

You may or may not see a change since the

AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 )

is really a JOIN clause due to having the two tables on opposite sides of the = sign. The CONCAT on the right side would also trigger a full table scan. Adding the compound index on status and table1 seems to be the only option left.


Installing SQL Server on not the default path

From BOL:

During installation of SQL Server 2012, Windows Installer creates temporary files on the system drive. Before you run Setup to install or upgrade SQL Server, verify that you have at least 6.0 GB of available disk space on the system drive for these files. This requirement applies even if you install SQL Server components to a non-default drive.

http://msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx

You still need 6GB free on the system drive, regardless of where you are installing it.  Although, depending on your options for install, that may be smaller, as in your case.


Minimising downtime implementing merge replication on live system

The biggest time saving option is to generate the snapshot and compress it with your favorite program, like 7-Zip, copy the compressed snapshot to the subscriber using file-copy or FTP, and then apply it locally using the -AltSnapshotFolder Merge Agent parameter.  The existing Product database can be left in place while the snapshot is being copied over and client applications can operate normally.

The way this works is open the SQL Agent job for the Subscriber you wish to deploy the snapshot to and get the Run Agent. command.  It looks something like this:

-Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1

Then, grab the snapshot files from the publisher and compress it.  For example, on my server I compress the snapshot folder found in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc.  I then copy the snapshot folder to the subscriber using file-copy or FTP and decompress it in a folder called C:\TEMP\unc.

So looking at this on the Subscriber I would see this for the snapshot folder:

C:\TEMP\unc\PACIFIC_TESTDB2_TESTMERGEPUB1\20130207200123

Then from the command line on the Subscriber, run this:

c:\Program Files\Microsoft sql server\100\com\replmerg.exe -Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1 -AltSnapshotFolder C:\TEMP

This will apply the snapshot locally and will be significantly faster then applying it over the wire.  Downtime at the Subscriber will be minimal.


Tricky condition for PostgreSQL UPDATE query

Just add an ON DELETE CASCADE option to your foreign key:

ALTER TABLE links_link
DROP CONSTRAINT constraint_name,
ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id)
      REFERENCES links_publicreply(id) ON DELETE CASCADE;

Apparently in django the above is translated as:

on_delete=models.CASCADE


make random numbers for each row and column

Since there is no correlation between the table rows and the RAND function, the expression is evaluated only once in the query.

One method to generated random values is with the NEWID() function.  The example below generates random integer values between 0-99 range.  The modulo value can be adjusted for a different range and you can use this basic technique to generate random values for dates, character, etc.

WITH x AS
(
  SELECT mintwee, mineen, nul, pluseen, plustwee
  FROM Topic
)
SELECT mintwee = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       mineen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       nul = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       pluseen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       plustwee= CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100
FROM x;

EDIT:

Per Aaron's comment, the expression can be simplified if you need only 32-bit integers.  Additional examples:

--postive int
SELECT ABS(CHECKSUM(NEWID()));
--postive and negative int
SELECT CHECKSUM(NEWID());
--postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 7) AS bigint);
--negative and postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 8) AS bigint);
--dates between now and 1 year ago
SELECT DATEADD(day, -ABS(CHECKSUM(NEWID())) % 365, GETDATE());
--random uppercase character
SELECT CAST(CAST(65 + ABS(CHECKSUM(NEWID()) % 26) AS binary(1)) AS char(1));


SQL - Need some advices for building a large db

I think you're having a hard time with this because I'm not sure you're looking at it from the right perspective. Database performance is a big topic and generally depends on an enumerable number of factors. But table partitioning is only addresses parallelism across cores vs. parallelism across threads. Generally speaking, the bottle-neck is not going to be there.

Let me give you some advice. Don't worry about partitioning - leave it in whatever state it's currently in - but do focus on the following.

What's more important, reads or writes? If it's reads then ensure the queries you are performing against the database are optimized by building covering indexes where you can - reading the data page is expensive. If it's writes then be careful how many indexes you build because of the shear size of your data, so only build what you have to because rebuilding the index is expensive if there are a lot of transactions.
Key constraints. Do not go down the road of not building proper foreign key constraints. When you build a foreign key it will build an index, you do not want to build in the referential integrity yourself, there is no performance bottle-neck with foreign keys. If you are using a database engine where foreign keys are a bottle-neck you should probably consider a different engine.
Last but not least, tune your database to your usage. A substantial amount of usage statistics are needed to understand exactly how to tune your database and because of the size of your database it will need tuned regularly.

I'm sorry if this feels nebulous, but you have a huge task before you and database optimization is a regular process on a database of that size. So, in short, the aforementioned items will start you down the right path but there is no answer to your whoas.


Backup to unc path in workgroup environment

the problem are not coming from the dba tools but from your network.
You can't mount a share on a local path. That path (K) can't be seen by sql server.
The unc path works with the user that is running the sql server service, in your case the computer account; but you can't authorise the compunter account in your nas under workgroup.
The only option i can suggest you to test is to change the sql server user to a local windows user and then create a user in the nas that match it in the username and password.
Then you can try with the UNC path; but i'm not sure if it will work.


Why is :r SQLCMD command marked as wrong in Post Deployment Script?

Assuming that the error happens during the build process, and that you do not have any actual syntax errors, and it is a simple

:r path\to\file.sql

then the error also occurs when the file being imported does not exist. Please check the location of the file. If you did not provide an absolute path, then the path will be relative to the solution folder (at least for me it is).

If, while developing, you want to see the error that the build process reports, then make sure to enable "SQLCMD mode" in the T-SQL editor in Visual Studio. There is a button on the far right in the button bar with a "!" in it that should enable this. Or you can go to the SQL menu, select Transact-SQL Editor ->, select Execution Settings ->, then finally select SQLCMD Mode. Now when you Execute the script, it will correctly interpret :r, just like the build / publish process does.

Also, your Post Deploy Script (PDS) needs to have its Build Action set to PostDeploy. If it is set to None then it will be skipped entirely by the SSDT build process.


Why Regenerate Snapshots For Merge Replication

The assumption I think with the default setting it that you'll be pushing out new subscriptions on a regular basis.  You don't need to do this, however when you push out a new subscription you'll need to manually run the snapshot agent (which really isn't that big a deal).

When the subscription is created the distributor will store information about the subscriber so that the distributor has this information available for cleanup and data distribution.


How to validate column while creating Table?

PostgreSQL does not support triggers on system tables, so there's no way you can write a normal trigger that fires when table definitions are changed.

PostgreSQL 9.4 introduces a new feature called event triggers. (More options have been added for event triggers in the coming 9.5 release too). You could use an event trigger to validate that table definitions follow rules you set out by writing suitable validation code in PL/PgSQL. You can query information_schema and/or pg_catalog to find details of the tables, and you can abort the transaction with an ERROR if you don't like what you see.

That said, this seems like a complicated technical solution to a simple people problem. I wouldn't advise going down this route when your code review process can be fixed to catch these sorts of problems, your app can be modified to query the schema and emit warnings on tables that aren't defined how you require, etc.


Is it possible to achieve zero downtime when upgrading database schema using this method?

pt-online-schema-change in the Percona Toolkit is used in production in many companies for many years. It's more than a candidate solution.

It avoids the race condition by dropping old table later.

RENAME TABLE users TO user_old, users_new TO users;
DROP TABLE users_old;

Check out the source code around "Step 5: Rename tables: orig -> old, new -> orig"

pt-osc creates three triggers - on update, on delete and on insert. So when any changes are made during copying from users to users_new, the triggers reflect the change in users_new. So by a moment right before the RENAME, users and users_new are identical.


Recursion on deletion trigger that calls a function

Add the following at the beginning of your function:

IF pg_trigger_depth() > 1 THEN
   RETURN OLD;
END IF;

Then recursion will be cut short after the first iteration.

To avoid the error that you get when the same row is deleted twice in the same statement, explicitly exclude it:

DELETE ...
WHERE ...
  AND OLD.ctid <> ctid


What you did, was to add another datafile (quite tiny one, though) to an existing tablespace. If that was your intention, then I'd say you succeeded.
However, as title says "CREATE" and if you wanted to create another tablespace, then you should have used CREATE TABLESPACE command (which is also described in document whose link you posted).


Performance of count() vs count() in a window function

why there is such a performance difference between executing a SELECT + a count() and selecting and counting in the same query?

Because PostgreSQL isn't optimized to do what you want, clearly. The count(*) OVER () is subject to WindowAgg which presumably does more (perhaps even with an exposed  accumulator) than the PartialAggregate. And, moreover, as you pointed out one of them is subject to Parallel Seq Scan, presumably Window Functions are not subject to Parallel queries yet.

I'm sure patches are accepted.

  What surprises me is that the planner chooses to do a sequential scan, then a window aggregation, and finally a limit over the whole set.

That's to be expected, what's surprising? The LIMIT always runs last.

  creating an btree index didn't help,


If not all ids are present return nothing

One way:

WITH
  qry AS
  (  -- your query
    SELECT ATV.ATTRIBUTEVALUE_ID,
           ATV.ATTRIBUTE_ID,
           ASS.ASSET_ID
    FROM   CLOTH.ATTRIBUTEVALUE ATV
    JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
    ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
    JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
    ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
    JOIN   CLOTH.ASSET ASS
    ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
    WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 )
    AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )
  ),
  cnt AS
  ( -- count condition to be satisfied
    SELECT 1
    FROM qry
    HAVING COUNT(DISTINCT ASSET_ID) = 3
  )
SELECT qry.*
FROM qry, cnt ;

The cnt will have 0 rows and so will the cross join (FROM qry, cnt) if the condition is not met.

You could write the final query like this, perhaps it's more readable:

WITH
---

  cnt AS
  ( -- count condition to be satisfied
    SELECT COUNT(DISTINCT ASSET_ID) AS assets
    FROM qry
  )
SELECT qry.*
FROM qry
WHERE (SELECT assets FROM cnt) = 3 ;

Another way would be a window function:

WITH
  qry AS
  (  -- your query with one more column
    SELECT ATV.ATTRIBUTEVALUE_ID,
           ATV.ATTRIBUTE_ID,
           ASS.ASSET_ID,
           COUNT(DISTINCT ASS.ASSET_ID) OVER () AS assets
    FROM   CLOTH.ATTRIBUTEVALUE ATV
    JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
    ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
    JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
    ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
    JOIN   CLOTH.ASSET ASS
    ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
    WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 )
    AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )
  )
SELECT qry.ATTRIBUTEVALUE_ID,
       qry.ATTRIBUTE_ID,
       qry.ASSET_ID
FROM qry
WHERE qry.assets = 3 ;


Updating a column in Table A from a Column in Table B during an after insert Trigger

You need a before insert trigger ("after insert", the bird has flown, it's useless modifying the new vales - if MySQL allows you).  In the trigger body, use the following code:

Declare v_tax int;
select tr.tax_combined_rate into v_tax from sales_tax_rate as tr
  where NEW.res_zip = tr.zip_code;
set NEW.res_sales_tax = v_tax;

See the effect here: https://www.db-fiddle.com/f/eEiK6pKGEQEoYL9BPdfooP/0


Error: 468 trying to connect to SQL server with SQL Server management studio

How I could specify the proper collation for the connection?

You can't. And even if you could, that wouldn't help here since the problem is a conflict between two different string columns. The only aspect of this problem that is specific to SSMS is that SSMS is selecting from a specific system view — msdb.dbo.sysdac_instances — that is joining on these two string columns, expecting that they will always be using the same collation (and in most cases they do).

Please see my answer to this related / duplicate question:

Collation conflict … Could not use dbo.sysdac_instances

There I explain why this is happening and what your options are. This should be caused by having a different instance-level collation than the msdb collation. This can happen when restoring / attaching msdb created in another instance that had a different instance-level collation.

The option that should have the least amount of impact would be updating the definition of the dbo.sysdac_instances system view. Not ideal, but given the other options, it is also the easiest to reverse should it prove to be a problem, though I don't see how it could be a problem. In that example I used Latin1_General_CI_AS for the fix as it was the instance-level collation. In this case, I would use SQL_Latin1_General_CP1_CS_AS as I am guessing it is your instance-level default collation.

Please also see the following post of mine if you are considering updating the collation of the instance and database(s):

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Also, this is another example of why we need to have a special collation similar to DATABASE_DEFAULT but working at the instance level — INSTANCE_DEFAULT — so that there can be a generic solution to this type of issue. This would allow the msdb.dbo.sysdac_instances_internal table to be created using:

...
[instance_name]   sysname COLLATE INSTANCE_DEFAULT,
...

Or, it could be that the msdb.dbo.sysdac_instances view has this at the end:

LEFT JOIN sys.databases sd
    ON dac_instances.instance_name = sd.name COLLATE INSTANCE_DEFAULT

So please vote for the following suggestion:

Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation


Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB

First, as always a_horse_with_no_name is right. Read this

As to how you can factor out your jsonb. You can use a single-table hierarchy.

CREATE TABLE items (
  item_id    serial  PRIMARY KEY
  parent_id  int     REFERENCES items,
  details    jsonb
);

At the very least with this scheme you've removed out the IDS. Now you just use a recursive query to query the data.

For more information look up

an example with id/parentid/query
[hierarchy] + [postgresql]


Try and see if this yields the result you want:

SELECT some_description
FROM something_about_courses A
JOIN courses B
ON (A.course_code = B.course_code
AND A.course_session = B.course_session
AND (A.course_type = B.course_type OR B.course_type IS NULL))


MySQL - Master/Slave Replication with different server specs

Yes you can upgrade the Master's my.cnf file to handle the new environment.

I have an additional suggestion.

InnoDB prior to MySQL 5.1.38 cannot use multiple CPUs.

You can do one of two things that will not affect the Slave

Upgrade to Percona Server 5.1
MySQL 5.1.38 has InnoDB Plugin (dsiabled by default) which acceses mulitple CPUs

If you have MySQL 5.1.37 or prior, upgrade to the latest MySQL 5.1 and install InnoDB Plugin
If you have MySQL 5.1.38+, install InnoDB Plugin

Once you do, one of the above, you can configure the necessary options for accessing multiple CPUs

About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?


Prevent read access on primary AG replica, but allow it on the readable secondary

It seems we basically need to allow a login connect permission on the primary replica [...]

That's exactly right.  The login being used to connect has to be able to connect to the primary.

When you using ApplicationIntent=ReadOnly in a connection string that's pointed at an AG listener, the driver initially connects to the primary instance.  This is so that SQL Server can review the list of replicas and see if there are any available, online readable replicas to send you to.  This is documented here:

Read-Only Application Intent and Read-Only Routing

  The primary database of the availability group processes the incoming read-only routing request and attempts to locate an online, read-only replica that is joined to the primary replica and is configured for read-only routing. The client receives back connection information from the primary replica server and connects to the identified read-only replica.

There's also a good summary of the process laid out by Top Microsoft Man Sean Gallardy on the PFE blog here:

Finding Which Connections Have Been Read Only Routed

  We create a connection through the driver
  The driver connects to SQL Server through the listener and passes the relevant information
  SQL Server notices that we have read only routing setup and checks the configuration
  SQL server reports back to the driver that we have a read only secondary that can be used
  SQL Server sends the new secondary information to the driver
  The driver creates a new connection to the secondary with the given information

Regarding the other item you noted in the question:

  [...] while denying it read access to the database in the AG on the primary, but give that login permission to read the database on the readable secondary replica.

Database level permissions are going to be replicated across the AG - so if you remove the user's read-access on the primary, they will lose read-access on the secondary as well.

I'm a little confused about the goal of such an endeavor, since the data should, in theory, be the same (or close to the same).  Maybe you just don't want certain logins running queries on the primary at all for performance reasons.  Expressing Read-Only intent and connecting to the listener is really the most straightforward approach to solving this.

Other options would involve separate hardware, or custom coding in your app that deals with connecting directly to the right replica (and retry logic if a failover has occurred, and scheduled jobs on each replica to disable / enable the logins in the event of a failover, etc).


Change MAXDOP from 0 to 6? Are we likely to gain anything from
  changing this setting?

The short answer is: it depends on your workload and the cpu usage of your queries.

  Example 1:

What is your CPU usage, are you seeing spikes on certain queries, are these queries bottlenecking other queries?

If the answer is yes

consider changing your maxdop to 2 or 4. Also, check your cost threshold for parallellism, if this is at the default of 5, consider setting this higher, but as always, monitoring your workload before and after is key.

  Example 2:

This one is a bit tricky, since one instance could hog all the resources on your machine.

An important factor in this example is determining which instances are the most critical to always run their queries without possible bottlenecking of the other  five instances.

Monitor workload before and after, but leaving it at 0 for all instances and having big queries running at 2 or more instances will cause bottlenecks.

I would consider again setting it to 2 for less important instances and 4 for more important instances to start with, and change this again based on the results. (Here also consider the cost threshold for parallellism setting).


How to enable "only" drop partition DDL for Oracle Golden Gate?

Ok, so worked out a solution with Oracle via the SR .. wanted to put the solution here for completeness.
The solution was to use following DDL commmand:
DDL INCLUDE MAPPED INSTR 'DROP PARTITION'


What techniques to use when designing one or more tables to allow keeping a pending and approved version of objects?

It turned out clunky, the Slots for Playlists needed to have Foreign
  Keys to both the ID and ModerationStatus of these objects while I wish
  I would only need an ID

Why you wish to have only ID.you can have composite FK.Suppose only ID was FK then what problem it would solve ?This is not clear.

  searching for objects with a priority on their pending versions
  required using a subquery in the Where clause

This requirement is not clear to me.can you explain with example ?

Also how this table will be populated on daily basis ?

With how many rows will be pending and approved.
Do you soft delete or permanent delete ?

You can keep 2 tables . PendingImage and ApprovedImage.when user upload then record goes to PendingImage.when that record is approved then it goes to ApprovedImage.

Child table will have FK relation with ApprovedImage table.

I think only in few places you will require info from both table.
And very frequently use table in front end will be  ApprovedImage

I will modify my answer after clearing few doubts.


Differentiating physical standby to cascaded standby

Why is it that for non-real time apply from cascading to cascaded standby that we have to mention as SYNC as the redo transport mode?

Whether the Real-Time cascade feature is used or not, actually it depends on the attributes in the parameter LOG_ARCHIVE_DEST_n of the cascading standby that control how the redo stream is forwarded to the cascaded database. Beginning with Oracle 12c, the attributes LGWR and ARCH of the LOG_ARCHIVE_DEST_n parameters are deprecated. The only valid values are SYNC, ASYNC or none of them.
In particular, for a cascading standby (thus, for a DB in Standby Role), the only valid attribute for the transport is ASYNC, meaning that synchronous transport from a standby database to another is not possible. If ASYNC is specified then Real-Time cascade is used, if no transport attribute is specified, Real-Time cascade is disabled and the redo stream from the cascading to the cascaded database is transported using the archived logs.

  Ideally we mention SYNC for real time apply right?

Yes, when we are not using cascading and cascaded standby

  should we mention as SYNC NOAFFIRM for non-real time apply?

Maximum Availability mode now allows the LOG_ARCHIVE_DEST_n attributes SYNC and NOAFFIRM to be used together. This enables a synchronous standby database to be deployed at a further distance from the primary site without increasing the impact on primary database performance. (In an Oracle Data Guard broker configuration, this is referred to as FASTSYNC mode.)

So, its not necessary.


SQL Log Shipping Read Only/Standby Different users

This can easily be achieved if you disable the login on the primary:

Add the login on the primary
Add the user to the database and grant permissions
Copy the login to the secondary
Disable the login on the primary


Which Query of JOIN with DISTINCT & JOIN with Subquery has better Performance?

The first one is good, when you select a larger range, the engine should no longer treat the "subqueries" as such, but run them independantly, and join the results.


How to allow an SQL-ID to access one database through a view in another?

You can grant access to the view without granting any rights on the underlying tables using Ownership Chaining.

In this scenario you would need to enable Cross Database Ownership Chains, and grant the user access to the target database, but not SELECT on any of the tables.


What does the error "Ambiguous column name 'id'" mean?

The error is referring to "id" in the first line of your query.  SQL Server doesn't know whether you are referring to tbldata.id or tblimg.id.

It is good practice to always use an alias so that table schema changes are less likely to break your query:

SELECT TOP 6 td.id
FROM   dbo.tbldata td
       RIGHT JOIN tblimg ti
               ON td.id = ti.newsid
ORDER  BY createdDate DESC


LOB in frequently queried table or separate table

is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is?

Leave it. That's tiny, and not worth adding complexity.  Also, even if both the table and the LOBs were larger, the answer is still to leave it.  Large LOBs will be moved off of the row, and you can set a configuration option for all the LOBs to be stored off-row.  You can also configure the table for the LOBs to be stored on a separate filegroup.  So there's no reason to change the logical model here.

See:

  large value types out of row

  1 = varchar(max), nvarchar(max),
  varbinary(max), xml and large user-defined type (UDT) columns in the
  table are stored out of row, with a 16-byte pointer to the root.

  0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT
  values are stored directly in the data row, up to a limit of 8000
  bytes and as long as the value can fit in the record. If the value
  does not fit in the record, a pointer is stored in-row and the rest is
  stored out of row in the LOB storage space. 0 is the default value.

From sp_tableoption


How to filter a character followed by numbers string pattern in PostgreSQL?

You can use the operator ~ that uses the full power of regular expressions
select *
from (values ('#1234'), ('#3333'), ('#abcd')) as boats (number)
where number ~* '#\d{4}'

A stricter version of the regex would be '^#\d{4}$', so that it matches only strings that start with # then have 4, and only 4 numbers, and then there is the end of the string.


As you mentioned you can use a CTE, actually a Recursive CTE, to do what's known in the industry as a BOM Explosion.
If I understand your logic correctly, this is essentially the query of the Recursive CTE you'd want to use.
WITH CTE_Recursive AS -- Recursive CTE to explode the BOM
(
    -- Base Case
    SELECT ID AS ChildId, Parent_ID AS ParentId, [Level], [User]
    FROM BOM
    WHERE [User] = 5678-- Filter on the User to start with here

    UNION ALL -- This allows us to call the CTE recursively

    -- Recursive Case
    SELECT R.Parent_ID AS ChildId, B.Parent_ID AS ParentId, R.[Level] - 1 AS [Level], B.[User] -- Notice how the Parent of the previous call becomes the Child here, as it recursively works its way up the tree (and the new Parent comes from the BOM table, and is the Parent of the previous Parent).
    FROM CTE_Recursive AS R
    INNER JOIN BOM AS B
        ON R.ParentId = B.ID
)

-- Final SELECT for the results
SELECT ChildId, ParentId, [Level]
FROM CTE_Recursive

Let me know how this works out as I don't have any data to test it with at the moment, and you basically want a reverse BOM explosion, since you need to filter on a specific child and get all ancestors. (A regular BOM explosion is what I'm more used to writing recursive CTEs for, where you start with a specific ancestor and get all children.) Also please see the link I provided above for more examples and information on using recursive CTEs.


How to store YMD, HM but not S Seconds in mariaDB/MySQL?

There is no special datatype for this :) Instead, you can use DATE_FORMAT

Example:

SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%i');

So you can create a view to converting your datetime column to the specific date format.

Create table with Datetime

create table test (date datetime);
insert into test values (now());
select * from test;
2017-12-28 10:30:24

Create view to convert date format

create view v_test as (select DATE_FORMAT(date, '%Y-%m-%d %H:%i'));

select * from v_test;
2017-12-28 10:30


how the mysql server settings replicatd in innodb cluster

Yes, you have to change the variable on every node, and if you want the change to be permanent, then you also have to change the relevant options file (e.g. my.cnf).

innodb_buffer_pool_size is dynamic (since MySQL 5.7), so at least you don't have to restart the nodes for the change to take effect:

SET GLOBAL innodb_buffer_pool_size=402653184;

(Don't forget to also set the value in the options file so it becomes permanent, i.e. so the change survives restarts.)

EDIT

If you're using MySQL 8.0+ then there's a handy feature that allows you to set a global variable and persist it to a special options file:

SET PERSIST max_connections = 1000;

See the SET syntax documentation for more details.


Optimizing a "categorized" search table using trigrams

Is it possible to use a single index that does trigram searches, but also exact match on user_id and type where they can optionally be NULL.

Yes, NULL is included in indexes. And you can search for it like for any other value.

Yes, you can have a multicolumn trigram GiST index. But GiST indexes typically don't make sense for the data type integer. Btree indexes are better in every respect - except for your case of a multicolumn index. So Postgres does not install the required operator class by default. You need to install the additional module btree_gist first, once per database:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- only if not installed, yet

Then you can create your multicolumn index:

CREATE INDEX foo ON search USING gist (user_id, type, search_on gist_trgm_ops);

Related (with detailed instructions):

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
Optimizing queries on a range of timestamps (two columns)

And get operator precedence in your WHERE clause right:

WHERE (user_id = 123 OR user_id IS NULL)  -- parentheses!
AND    search_on % 'mystring'

Or:

WHERE (user_id = 123 OR user_id IS NULL)
AND   (type = 'my-type' OR type IS NULL)
AND    search_on % 'mystring'

Depending on data distribution, cardinalities, selectivity of predicates, cost settings etc. Postgres may still prefer an index on one (or two) column(s) (occasionally).


Getting current number of connections in mysql

max_connections limits the number of connections overall.  But usually many are in "Sleep" mode, therefore encurring virtually no load.
There is one "extra" connection allowed -- this lets 'root' get in to see what is going wrong, even when the max is hit.
Max_used_connections is a high-water-mark toward max_connections.
Threads_running may be closest to what you are looking for.  It is the current number of connections not in "Sleep".  It is often "1", namely the connection you are using to read the value.  A value of "10" is rather high.  "100" probably means that MySQL is stumbling over itself.
SHOW PROCESSLIST gives you hints of some of the above info, plus clues of what queries are running.
Connections is a counter that started when the server started.  Divide it by Updtime to get an average of new connections.  1/second is pretty typical.  100/second is high, but not necessarily 'bad'.
There is no metric to recent "recent", etc.  The would require a monitoring program or script.  I would not do SHOW GLOBAL STATUS more than once a minute, else it might have an adverse impact on the system.
Connection_errors_max_connections a counter that is optimally 0.  Since you have 434 and Max_used_connections = 152, I will guess that you started max_connections with the default of 151, hit the error, then raised max_connections to 300.  Am I right?
Raising to 300 is fine, though not necessary on 'most' machines.  You should also see if having lots of connections is justified by your app.  Maybe something else is going wrong.
A common issue is to have the webserver that talks to MySQL allow lots of connections.  It then simply assumes it can hand things off to MySQL; instead, it should allow fewer connections to the webserver itself.
If Threads_running gets high, response time will probably suffer.  The likely remedy is to find "slow" queries via the slowlog.  The fix may be a better index or a reformulation of a query.  Alas, Threads_running is hard to monitor.
For the slowlog:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Another tunable: wait_timeout controls how quickly an idle connection will be disconnected.  Check that the clients are disconnecting when finished.


MySQL InnoDB does not return the last row on select with DESC order

Transaction isolation levels and database locking can make it very difficult to handle the problem you've described.

Hopefully ypercube's suggestion does the trick.

If it's valid to have multiple (campaign_id, user_id) combinations, just not within seconds of each other, you might want to consider adding a column with the user's web/application session id, and putting a unique index on (campaign_id, user_id, session_id).

This would prevent a user from being able to enter the same data during a single login session - whether that's useful or not really depends on your application. You might be able to come up with some other unique index that serves your purposes.

It might also be worth trying to prevent data being submitted twice from the client side. You'll see that sort of behaviour all through StackExchange, for example - the interface changes in such a way that you can't actually hit the same button twice.


DatetimeFieldOverflow when inserting more than three decimals precision

Thanks to Akina, I found the problem. The error has been on client-side, because I used the following in the INSERT function:

INSERT INTO l12 VALUES (TO_TIMESTAMP('2019-05-23 12:16:50.954276', 'YYYY-MM-DD HH24:MI:SS.ms'))

which fails with the mentioned ERROR:  date/time field value out of range: "2019-05-23 12:16:50.954276"

Just remove that TO_TIMESTAMP function and be happy or use 'YYYY-MM-DD HH24:MI:SS.msus as the correct formating input.


It boils down to database design esp. choosing data types can affect performance as well - storing zipcodes as integers requires more thoughts ! Eg. non US countries like Canada will have letters in zip code. If you store zip codes as integers, when your company does business in Canada, you will have to change the datatype.

I would select integer datatype where any sort of arithmetic (Calculations) comes in play. Adding or subtracting zipcodes is of no value !

Also, US zipcodes have leading zeroes e.g. 08874. For integers, a leading zero is of no value.

I would suggest you store zipcode as varchar(10) and if all your zipcodes are numbers, you can use cast or convert to fill whatever you are filling out.

From msdn :

  Character fields can store text information that is not used in mathematical calculations, such as names, addresses, and numbers. For example, phone numbers or zip codes, though they include mostly numbers, are actually best stored as character values.

Also, check out @srutzky's answer 9 digit zip codes are reporting LEN of 12


This is a simple UNION query:

SELECT id, date
FROM work
WHERE id = 1
UNION ALL
SELECT id, date
FROM home
WHERE id = 1
ORDER BY date;

I've used the UNION ALL syntax which return all rows from both source tables.  Leaving the ALL keyword out ensures rows that are duplicated in both tables are only returned a single time.

I've added an ORDER BY clause at the end of the query to return the list in date ascending order, since your sample data seems to indicate that.

See the Wikipedia entry for further details.


Admin on phpMyAdmin does not have Super privileges

Plesk via GoDaddy does not allow you to have admin privileges over phpMyAdmin. This seems to be a result of shared hosting. In order to do any action that does require that privilege you have to use work arounds or third party software.

I answered this here as it seems that this information cannot be found on the Plesk or goDaddy forums.


Performance implications of using OPENQUERY in a view

Summary

Let the linked server do as much as possible.
It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server

Long

The key factor is where the query runs.

In this case, it is a trivial SELECT so all rows from a table will be sent down the wire. It doesn't matter.

When you add JOINs and WHEREs then it can matter. You want SQL Server to allow the linked server to do as much filtering as possible to reduce the size of the data coming over the network.

For example, the 2nd case here should be more efficient.

SELECT *
FROM OPENQUERY(<linked server>,
            'SELECT <column list> FROM MyTable') T1
     JOIN
     SomeLocalTable T2 ON ...
WHERE T1.foo = 'bar'

SELECT *
FROM OPENQUERY(<linked server>,
           'SELECT <column list> FROM MyTable WHERE foo = ''bar''')
     JOIN
     SomeLocalTable T2 ON ...

A limitation of OPENQUERY is that you can't parametrise: so you need dynamic SQL to add WHERE clauses etc.

Linked servers performance can be affected by sp_serveroption. The setting collation compatible says it all

  If this option is set to true, SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.

That is, try not to let SQL Server process the data locally.

Note: On my foo = 'bar' 2nd example above, the filter is sent to the linked server because it just a string constant to SQL Server. The real WHERE clause in the first example may or not be sent remotely.

Finally, I've also found that staging the data into a temp table and joining that onto local tables is often better then joining directly onto the OPENQUERY.


How to determine when to reduce physical memory for SQL Server?

Your Page life expectancy counter is at 874 seconds.  That isn't a lot.  I'm guessing that the server isn't configured correctly, or something on the OS is taking the memory.  Odds are this server could use more RAM to up the PLE.


Will TDE encryption protect my SQL database from hacker in this scenario

[…]Is that possible?

Yes. The scenario where a server is compromised is not what is being targeted by TDE. TDE is targeting data at rest scenarios such as lost/stolen backups, hard drives that were discarded and not scrubbed, etc., but not entire server compromises.

In this case I'd advocate for you to upgrade off of 2008R2 (which only has <6 months of support left) and onto something much newer. There you have options such as column level encryption through HSMs and Always Encrypted (which can also use HSMs) which would be two main scenarios that can potentially (depending on external factors) stop the attack you've specified from being able to steal any extra information not already on the server and unencrypted.


Increase performance WITHOUT adding indexes

I wrote some posts about RAM disk and temp tables

Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
Jan 17, 2012 : How can I optimize my mysql setup to create my index faster?
Sep 23, 2011 : skip copying to tmp table on disk mysql

The basic idea would be to set set tmp_table_size and max_heap_table_size to the bare minimum value so that move temp tables to the RAM disk quickly.

This would only help if your queries produce temp tables and you do not wish to tune tables and indexes. However, it is imperative to tuning queries and use indexes to produce the smallest possible temp tables before tuning the tmp_table_size and max_heap_table_size options or even go the route of the RAM Disk.

Nevertheless, since you have 16GB on the server, you could set up good 2G RAM disk, configure good settings for join, sort and read buffers per connection, and sufficient RAM left for the OS.

UPDATE 2014-11-25 11:09 EST

Your last comment

  I'm wondering what's the difference between temp tables on a RAM Disk and temp tables in "memory"? I think that's the same, isn't it? Therefore it makes no difference wheter I set tmp_table_size & max_heap_table_size to a high or a low value - RAM is used in both situations. Or am I wrong here?

It makes a big difference. Why ?

If you set tmp_table_size/max_heap_table_size too high, each DB Connection will needlessly glut memory (This is in addition to other per-connection buffers : return my.conf values to sane levels (total memory footprint = 220GB)) I have seen customers set these to 100G to avoid thinking about this issue (Bad decision).
If you set tmp_table_size/max_heap_table_size high and queries need just a little more here is what has to happen:

You have tmp_table_size/max_heap_table_size set at 64M
You makes a temp table that takes up more that 64M (let's say 66M)
Temp table reaches 64M
Query suspends processing
Temp table is migrated to disk
Last 2M needed is loaded into the temp table on Disk
Query continues processing
This is counted in Created_tmp_disk_tables

HERE IS MY POINT : If you set tmp_table_size/max_heap_table_size low on purpose (let's sa the minimum value of 1K), the temp table will materialize in the RAM Disk faster because it only has to stop and transfer 1K from RAM to Disk.


This is Sql server 2008 script.i think it can be easily translated to other rdbms.

Secondly your sample data is wrong.It should be 08:43 instead of 09:43

create table #t (CalanderDateTime datetime, PtID int,EventDateTime  datetime,GapinMinutes int)
insert into #t VALUES
 ('2017-09-23 08:34',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:35',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:36',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:37',    1  ,    '2017-09-23 08:30'   ,  1 )
 ,('2017-09-23 08:38',    1  ,    '2017-09-23 08:35'   ,  4 )
 ,('2017-09-23 08:39',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:40',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:41',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:42',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:43',    1  ,    '2017-09-23 09:40'   , 3  )
 ,('2017-09-23 08:44',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:45',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:46',  NULL ,         NULL          ,NULL  )

 ;WITH CTE as
 (
  select *
 ,ROW_NUMBER()OVER(ORDER BY CalanderDateTime)rn
 from #t
 )
,CTE1 AS(
 select c.rn from cte c
cross apply(select rn from cte c1
 where c1.GapinMinutes is not null and c1.GapinMinutes>3 and
 (c.rn>c1.rn and c.rn<(c1.rn+c1.GapinMinutes+1)) )ca
 )
 select * from cte c
 where not exists (select rn from cte1  c1 where c.rn=c1.rn)

 drop TABLE #t


SQL Server 2012 Intellisense Support for Extended Events

I haven't come across Intellisense support for XEs in SSMS yet. It will tell you when you have an error in your syntax (my common mistake was omitting ON SERVER). SQL Prompt 5 (latest version) has support for the control commands (CREATE EVENT SESSION ... ALTER EVENT SESSION, etc.) but not for the packages and events yet. For now I'd recommend just sticking with the metadata queries from Jonathan Kehayias to get a sense for the packages, events, predicates, maps, etc.

I think this is probably the case because Extended Events are still a work in progress and are undergoing such rapid change and development that more effort would have to be put into generating the Intellisense XML than they'd like to put into it until it's stabilized (from a feature perspective, not a reliability perspective).


ERROR: column "FLAT" does not exit. Line 1. SELECT UPPER(FLAT). Kindly fix the trigger

The updated or inserted row is referenced using the new record:
CREATE OR REPLACE FUNCTION trigger_fct_tri_sv_tab_temp()
RETURNS trigger
AS
$BODY$
BEGIN
  new.FLAT := UPPER(new.FLAT);
  RETURN NEW;
end
$BODY$
 LANGUAGE plpgsql
 SECURITY DEFINER;


Data sharing between client and server two ways

The out-of-the-box solution for this in SQL Server is Peer-to-Peer Transactional Replication. This requires Enterprise edition at all nodes.

If your application truly needs this architecture, I would strongly recommend using the built-in tools rather than trying to patch something together using another technology. Peer-to-Peer replication was designed for this exact scenario.

Having said that, I would recommend that you make sure this architecture is actually necessary, as opposed to having one centralized database. I'm not saying this is the case in your scenario, but sometimes the people in suits can try to dictate a solution like you're asking for, without fully understanding the technical complexity. Setting up and managing this type of replication can be challenging.


"WITH REPLACE" Restore failing with same logical filenames

This is the answer I've found for why the file autolocation doesn't work in SQL 2012 for me.  I don't believe it has anything to do with the backup being from SQL 2008.

  In SQL Server 2012, during the restore of a full backup without MOVE clauses that indicate the target physical location of each file, on top of an existing database, the file autolocation algorithm requires: not only a logical name match between the file declared in the backupset and any given file in the existing database, but also their file GUID, file ID, and create LSN must match. If any of those attributes don’t match, the physical location used for that logical file is the one stored in the backupset. This additional requirement was implemented to fix this last scenario I just exposed which was considered a defect.

This was taken from this MSDN article: http://blogs.msdn.com/b/ialonso/archive/2012/06/08/sql-server-changes-behavior-in-cases-where-file-autolocation-is-invoked-during-the-planning-phase-of-a-full-restore-with-replace-clause.aspx


Web Database - How to store user information

The one benefit for storing passwords in a separate table, if you have a db that supports this, is you can use a security definer function to read the passwords, and this prevents sql injection or the like from reading it in other queries.  Think of it as being like the /etc/shadow file on Linux.  Of course this is in addition to salted hashes.

This being said I am of the opinion you should really avoid storing passwords in your database.  The less of this sort of thing your app does the less you have to worry about security-wise.  Outsource it to OpenID providers, etc. or if it is an intranet app, something like LDAP, AD, or the like.


Postgres: Why does pg_stat_get_dead_tuples return 0 when there is still wasted space?

ANALYZE does not remove dead tuples.  It looks like autovac kicked in and removed the dead tuples around the same time as you were doing the ANALYZE, and that is why the dead tuple count fell to zero.  If you are careful to prevent that from happening, you will find that an ANALYZE does not reset the dead tuple count.

The space freed up by the autovacuum is available for internal reuse, and can be seen using the pg_freespacemap extension.

select avail,count(*) from pg_freespace('sam_silly') group by 1 order by 1;
 avail | count
-------+-------
 3,648 |    22
 3,680 | 1,232
 3,712 | 1,153
 3,744 | 1,749
 3,776 | 1,233
 3,808 |   360
 3,840 |    20
 4,384 |     1
(8 rows)

Every page of this table is about half full and available for internal re-use.  This is only bloat if you do not intend to re-use that space in the near future for new tuples (including updates of old tuples, which create new versions of the old tuples).

The purpose of the dead tuple count is to let the autovac launcher know when it might be advisable to launch an autovac.  Pursuant to that goal, it records the number of tuples which are dead but not yet vacuumed away.  And when they are vacuumed away, the vacuum resets that count downward.  It is not there to show you bloat, which is why it does a poor job when forced into that purpose.

If you want to query for bloat, you can check out the suggestions at https://wiki.postgresql.org/wiki/Show_database_bloat


Large time difference between two almost identical simple queries....why?

I am willing to bet the issue is that the first does a scan of the table, while the second can look things up from an index.  This happens all the time.  The reason, as Phil has said, is that there are an infinite number of possibilities.

If you want to make the first approach work better with more flexibility you need to use a recursive query structure.  Something like:

WITH RECURSIVE sparse_scan AS (
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
     WHERE twg.metadata = m.Metadata and m.Metadata = 1100
  GROUP BY m.Metadata
 UNION ALL
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
      JOIN sparse_scan s ON (m.Metadata = s.Metadata + 1000)
     WHERE twg.metadata = m.Metadata
  GROUP BY m.Metadata
)
SELECT * FROM sparse_scan order by m.Metadata;

This works differently, running the query for 1000, 2100, 3100, 4100, etc, until a row is not returned.  You can be a little fancier to skip cases where a row does not exist but the next one does, but the basic approach  is something I use frequently, albeit on PostgreSQL.


How do I map SSIS 2012 Package Variables to Parameters?

I am bit too late to answer this question but I would like to make a point about Parameter role in package deployment model

It says'

  In general, if you are deploying a package using the package deployment model, you should use configurations instead of parameters.

  When you deploy a package that contains parameters using the package
  deployment model and then execute the package, the parameters are not
  called during execution. If the package contains package parameters
  and expressions within the package use the parameters, the resulting
  values are applied at runtime. If the package contains project
  parameters, the package execution may fail.

Here is a beautiful answer about setting up variables for different environment configurations.


Error while connecting SQL Server management studio 2008

Sounds like you only installed the management studio and not the actual database engine.  You need to connect to a remote SQL Server instance not to your local machine as your local machine doesn't appear to have a SQL Server instance installed.

If you need your own database engine installed you'll need to run through the installer again and install the database engine.


You must specify the server name with -S (capital letter) instead of -s (lowercase).

From the documentation:

   -s col_separator
   -S [protocol:]server[instance_name][,port]


To answer your question: [:ascii:] works. You may have some characters in your text that you do not recognize as non-ASCII, yet they're there. They can be something like a non-breakable space, for instance, or any other Unicode space character.

It is not strange to have non-breakable spaces (&nbsp;) in texts that you copy-and-paste from a web page, yet you don't notice they're there.

Here is an example to show:

WITH t(t) AS
(
    VALUES
      ( 'Сталинская правозащитница: мать Меленкова бабушка Настя' ),
      ( 'Дневник НКВДиста Шабалина: Знает ли Москва положение на фронте?' ),
      ( 'Бег по городу и поездка на осле: как в средневековье наказывали прелюбодеев' ),
      ( 'Как комиссар Крекшин в 1740-е чуть не отменил историю России' ),
      ( 'Have you heard of Saint Death? Don’t pray to her.' ),
      ( 'Архаїчна українська мова: перевага чи недолік?' ),
      ( 'Гренада не их' ),
      ( 'China’s marriage rate is plummeting because women are choosing autonomy over ' )

)
SELECT
    t,  regexp_replace(t, '([^[:ascii:]])', '[\1]', 'g') AS t_marked
FROM
    t
WHERE
    t ~ '[^[:ascii:]]' ;

That's what you get:

                                       t                                       |                                                                                t_marked
-------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Сталинская правозащитница: мать Меленкова бабушка Настя                       | [С][т][а][л][и][н][с][к][а][я] [п][р][а][в][о][з][а][щ][и][т][н][и][ц][а]: [м][а][т][ь] [М][е][л][е][н][к][о][в][а] [б][а][б][у][ш][к][а] [Н][а][с][т][я]
 Дневник НКВДиста Шабалина: Знает ли Москва положение на фронте?               | [Д][н][е][в][н][и][к] [Н][К][В][Д][и][с][т][а] [Ш][а][б][а][л][и][н][а]: [З][н][а][е][т] [л][и] [М][о][с][к][в][а] [п][о][л][о][ж][е][н][и][е] [н][а] [ф][р][о][н][т][е]?
 Бег по городу и поездка на осле: как в средневековье наказывали прелюбодеев   | [Б][е][г] [п][о] [г][о][р][о][д][у] [и] [п][о][е][з][д][к][а] [н][а] [о][с][л][е]: [к][а][к] [в] [с][р][е][д][н][е][в][е][к][о][в][ь][е] [н][а][к][а][з][ы][в][а][л][и] [п][р][е][л][ю][б][о][д][е][е][в]
 Как комиссар Крекшин в 1740-е чуть не отменил историю России                  | [К][а][к] [к][о][м][и][с][с][а][р] [К][р][е][к][ш][и][н] [в] 1740-[е] [ч][у][т][ь] [н][е] [о][т][м][е][н][и][л] [и][с][т][о][р][и][ю] [Р][о][с][с][и][и]
 Have you heard of Saint Death? Don’t pray to her.                             | Have you heard of Saint Death? Don[’]t pray to her.
 Архаїчна українська мова: перевага чи недолік?                                | [А][р][х][а][ї][ч][н][а] [у][к][р][а][ї][н][с][ь][к][а] [м][о][в][а]: [п][е][р][е][в][а][г][а] [ч][и] [н][е][д][о][л][і][к]?
 Гренада не их                                                                 | [Г][р][е][н][а][д][а] [н][е] [и][х]
 China’s marriage rate is plummeting because women are choosing autonomy over  | China[’]s marriage rate is plummeting because women are choosing autonomy over

You can see from this, that your problem is the right-apostrophe character. ASCII only supports the apostrophe. Left-apostrophe and right-apostrophe are typographically correct Unicode extensions.

dbfiddle here

You can check it also with previous versions at http://rextester.com/UKIQ48014  (PostgreSQL 9.5) and http://sqlfiddle.com/#!15/4c563/1/0 (PostgreSQL 9.3)

The texts that I guess you think are pure ASCII, and are not:

 WITH t(t) AS
 (
     VALUES
       ('A fully ASCII text!'),
       ('Have you heard of Saint Death? Don’t pray to her.'),
       ('China’s marriage rate is plummeting because women are choosing autonomy over ')
 )
 SELECT
    regexp_replace(t, '([^[:ascii:]])', '[\1]', 'g') AS t_marked
 FROM
    t
 WHERE
    t ~ '[^[:ascii:]]' ;

 | t_marked                                                                        |
 | :------------------------------------------------------------------------------ |
 | Have you heard of Saint Death? Don[’]t pray to her.                             |
 | China[’]s marriage rate is plummeting because women are choosing autonomy over  |

dbfiddle here

These texts are using ’ instead of ' to mark apostrophes.

Check Punctuation: Why is the right single quote (U+2019), and not the semantically distinct apostrophe (U+0027), the preferred apostrophe character in Unicode? ... to see that you're not the first person encountering this problem.


How do I find out if Sql server Agent's login has rights to a DB?

Account

There is no documented way to find out what the current service account for the agent is, at least not before SQL Server 2008R2.

As Jon commented below, in SQL Server 2008R2 SP1 and later you can use this query:

SELECT * FROM sys.dm_server_services;

For versions prior to that you can try one of the following:

Aaron suggested a xp_regread solution here: https://stackoverflow.com/questions/7324407/get-service-account-details-of-the-sql-agent-service
But I currently can't get it to work, and even if you get it to work, there is no guarantee that it still works after the next SQL Server patch was installed.

The easiest would be to start the SQL Server Configuration Manager on the instance's server and look it up in there.

Alternatively you could use the SUSER_SNAME() function to do something like

SELECT SUSER_SNAME() AS LoginName
  INTO tempdb.dbo.AgentAccountTable;

Put this statement into a job, execute that job and then look up the value in that table:

SELECT * FROM tempdb.dbo.AgentAccountTable;

Permissions

As for the permissions: The Agent Server Account must be a member of the sysadmin fixed server role (http://technet.microsoft.com/en-us/library/ms191543.aspx). Therefore it will have access to any database.


Postgresql inheritance based database design

Whether you are better off with single-table-inheritance or class-table-inheritance really depends on the particulars of your case.  The performance advantage can go either way.  The difficulties presented by having lots of NULLS in a table range from the trivial to the overwhelming.
It depends on what your data looks like, and what you intend to do.

Kudos for figuring out that the problem is basically due to a mismatch between object modeling and relational modeling.

PS if you use UserID as the primary key on both Parent and Babysitter, and also declare it as a foreign key, you'll get some benefits, at the cost of a little programming when you go to insert new users.  This technique is called shared-primary-key, and it's also presented over on SO.


Identifying postgres db health tool similiar to mysqlcheck

As of 9.2, there is no such tool, and probably wont be for some time.   There have been efforts to put checksum per page but that has not happened yet due to what has been referred to as a set of "deadly traps."  Until that happens, I doubt that such tools will even be feasible beyond some very basic cases.  So the answer, I am afraid is that the infrastructure required to do this efficiently is not part of the system yet.

Assuming working hardware, drives that don't lie about write completion, sane settings, etc. it should be impossible for something like an unclean shutdown to cause corrution in PostgreSQL but I have seen it a few times (overheating machines, and the like).  So I do agree with you about the need.  It's just worth noting that it is not that easy to do.


Insert as many rows as count on other table returns

You can use this query.

INSERT INTO ProjectKPIData (`kpi_id`,`project_id`,`value`) SELECT k.id,NEW.id,0 FROM KPI k;


It seems that you have run into the reason why NULLs exist in relational databases.  Choosing an arbitrary value from somewhere is a little crazy as you describe.

Options:

Create the needed key (PK or other Unique value) prior to doing the update of the data.  This would require you to pre-decide how to fill each Not NULL FK.  Obviously you want the FK to connect to something or else you would be happy with a NULL.
Note:I suppose that you could create a row for a default value that joined to each new Not NULL FK that you add. Perhaps give it an ID of -1 and a text of "(Unknown)" or something else that you could work around.  You might wind up scattering around many such "keys" if you are not careful.
Use an Intersection Table instead of adding a foreign key column to the existing table.  The intersection table would consist of two columns: TableAID and TableBID.  In that way, the intersection row will be inserted as needed without any dummy values.

Sample, from:https://stackoverflow.com/questions/977615/many-to-many-relation-design-intersection-table-design

CREATE TABLE SomeIntersection
    (TableAID INT REFERENCES TableA NOT NULL,
     TableBID INT REFERENCES TableB NOT NULL,
     PRIMARY KEY(TableAId, TableBId ));


How to match a WiredTiger index file to its corresponding collection

Ref:
https://docs.mongodb.com/manual/reference/method/db.collection.stats/

https://docs.mongodb.com/manual/reference/command/collStats/#collStats.indexDetails

As you are using WiredTiger storage engine you can use db.collection.stats() with indexDetails option set to true.

Easy copy paste:

.stats({"indexDetails":true})

  A document that reports data from the WiredTiger storage engine for
  each index in the collection. Other storage engines will return an
  empty document.

  The fields in this document are the names of the indexes, while the
  values themselves are documents that contain statistics for the index
  provided by the storage engine. These statistics are for internal
  diagnostic use.


Do I need an auto-generated ID in addition to a unique field?

Seeing as your question appears to hinge on preference, any answer seems necessarily subjective. Given Akina's point that some systems require UUID, it might be useful to have such a field in place if you foresee such an eventuality.

If you're at all concerned with the cumulative effects of your choice on your storage needs, then you'd likely want to stick with your varchar(3) value, seeing as it will consume around 4 bytes, whereas a UUID value will clock in closer to 128 bits (16 bytes).

That being said, I don't imagine you would need to worry about that unless you're working with exceptionally large amounts of data, which appears unlikely seeing as you're limiting the length of the varchar id to three characters, which caps your id column at 50,653 entries, provided you stick to alphanumeric values which use the English alphabet.

Beyond all that, you may want to use only one unique identifier so as to avoid any possible confusion from having two columns that essentially serve the same purpose.