Is two unrelated tables with foreign keys to a third table breaking first normal form?

No, it's fine.

Consider the situation where you have owners of things. Many types of thing can have an owner and it's not worth splitting owners into types of owner.


how to automate DB restore to another server?

The best suitable solution in your scenario is LOG SHIPPING which is available in Web, Standard and Enterprise editions. I assume that your SQL server is not express as you mentioned There is already a scheduled job

If you want work with T-SQL, i don't think appending backup files is good idea, so you may want to change schedule job to create separate file for each backup. Then you can start with following query to get dynamic restore command based on backup history:

SELECT      [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            f.physical_device_name,
            ((b.compressed_backup_size / 1024) / 1024) as CompressedSize_MB,
            CASE    WHEN Type = 'L'
                        Then 'RESTORE LOG '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
                    WHEN Type = 'D'
                        Then 'RESTORE DATABASE '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
            END as Script,
            b.is_single_user,
            b.user_name
FROM    [msdb].[dbo].[backupset] AS [b]
            LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
where   b.type in ('D', 'L')
        and b.database_name = 'TestDAG' --- add your database name here
order by b.backup_finish_date desc


You can right-click the query you are interested in and select Show execution plan:

Once you have the execution plan open, just right-click in an empty area and select Show Execution Plan XML.... Once you have that you will see a section in the XML <ParameterList>. In that node it will show you the ParameterCompiledValue:

    <ParameterList>
      <ColumnReference Column="@records" ParameterCompiledValue="(0)" />
      <ColumnReference Column="@traceid" ParameterCompiledValue="(2)" />
    </ParameterList>
  </QueryPlan>
</StmtSimple>

Another way would be to copy the execution plan XML out and paste it into Plan Explorer and view the Parameters tab:

Edit for the nerds

If you open up Activity Monitor, and then go into tempdb you will find all the temp objects (tables and all) that Activity Monitor is using. For the Expensive Queries it is using the object dbo.#am_get_querystats. You can open that procedure up and look at the exact query they use to pull it. Pretty cool. I did this for the activity section so I could get similar information without having to open up Activity Monitor all the time.


Short solution: The problem was IO-related. It was solved by moving to a new, more modern system with faster SSDs.

Longer version:

I invested a lot of time over several months trying to get to the bottom of the performance issue described above. As a stopgap measure, I did everything possible to prevent concurrent queries that caused a lot of IO (such as backups and search engine indexing). However, this never really solved the problem or answered the questions I posted -- it only covered up the underlying issue with one IO-intensive process blocking database activity.

My "answer," if you can call it that, was finally realized by moving to more modern hardware. Unlike the old system, which used consumer-grade Micron SSDs, the new server uses IBM datacenter SSDs and a faster processor. Otherwise, the software configuration is identical as in the system tested above (same OS, same use case for MySQL, same mdraid RAID-1 config, etc.).

I can only assume that the new system's better SSDs and/or more modern motherboard and chipset are better at handling concurrent operations. I no longer experience slow queries of any sort, no matter what's going on with the database server. Database operations do not seem to be affected by disk activity nearly like before.

So, if you're experiencing strange slowdowns with database queries and can't find any application-level reasons for this, don't forget to look at the underlying hardware as a possible source. Try renting a new dedicated server or VPS for a month from a different hosting provider, replicate your software configuration, and give it a try. Worked for me!


Technique Translation

The technique for solving a problem in one RDBMS doesn't always translate well into another.

I've had performance problems with similar code to that which was in the article.  Thus, my comment "This technique will kill performance in Oracle".

I don't see CAST(MULTISET()) (Oracle's version of ARRAY_AGG) being used too much.  As such, the code might be unmaintainable by your replacement after you get a promotion.

Translating Terms

Translation goes like this

STRUCT in BigData is TYPE in Oracle
ARRAY_AGG in BigData is CAST(MULTISET()) in Oracle
UNNEST in BigData is TABLE() in Oracle
UNNEST of XML Data is XMLTABLE() in Oracle
UNNEST of JSON Data is JSON_TABLE() in Oracle (12c+)

You can't create the structure on the fly.  You have to define it ahead of time.

EXAMPLES

Creating a structure of a single row:

create type emp_t as object (
  EMPNO             NUMBER(4),
  ENAME             VARCHAR2(10) ,
  JOB               VARCHAR2(9)  ,
  MGR               NUMBER(4)    ,
  HIREDATE          DATE         ,
  SAL               NUMBER(7,2)  ,
  COMM              NUMBER(7,2)  ,
  DEPTNO            NUMBER(2)
);
/

Creating a structure of a set of rows based on above: ( nested table )

create type emp_tt as table of emp_t;
/

Using CAST(MULTISET())

select d.deptno
  ,cast(multiset(
           select * from scott.emp e where e.deptno=d.deptno
        ) as emp_tt) as emp_table
from dept d
;

Using TABLE()

with data as (
  select d.deptno
    ,cast(multiset(
             select * from scott.emp e where e.deptno=d.deptno
           ) as emp_tt) AS emp_table
  from dept d
)
select b.*
from data a, table( a.emp_table ) b
order by empno;

Oracle's Solution

Business Requirement:  I want the first time a hurricane reached its maximum category along with its position.

Other, inferred, identifiers to "GROUP BY" would be season, basin, subbasin in addition to hurricane name.

One method to solve this in Oracle is to use Analytics.

Since I don't have access to the huricane data, I'll have to improvise.

  select deptno as hurricane_name
        ,2017   as season
        ,'NA'   as basin
        ,'WP'   as subbasin
        ,sal    as category
        ,ename  as position
        ,rownum as time
  from scott.emp

First thing we do is use the analytic function RANK() to rank all of the rows by category (descending) and time (ascending) but partition the rankings by season, basin, subbasin, and hurricane_name

  select h.*
    ,RANK() over (partition by season, basin, subbasin, huricane_name
                  order by category desc, time)
      as rank_score
  from huricane_data h

Finally, we'll pick only the best (rank_score=1)

select *
from analyized_data
where rank_score=1
order by season, basin, subbasin, hurricane_name, time

Putting it all together

with hurricane_data as (
  select deptno as hurricane_name
        ,2017   as season
        ,'NA'   as basin
        ,'WP'   as subbasin
        ,sal    as category
        ,ename  as position
        ,rownum as time
  from scott.emp
), analyized_data as (
  select h.*
    ,RANK() over (partition by season, basin, subbasin, hurricane_name
                  order by category desc, time) rank_score
  from hurricane_data h
)
select *
from analyized_data
where rank_score=1
-- place season/basin/subbasin filters here
order by season, basin, subbasin, hurricane_name, time;


What is held in memory during the life of a CTE?

A CTE term's output is accumulated into a TupleStore. A TupleStore may internally be in-memory or may spill to disk. See src/include/utils/tuplestore.h and src/backend/utils/sort/tuplestore.c, particularly the comment at the top of tuplestore.c.

For a wCTE that'll be the RETURNING output for that term.

To determine whether anything else is retained requires analysis of the memory contexts in which each CTE term is executed, as PostgreSQL uses a hierarchical memory context based allocator.  Here you need to look at src/backend/executor/nodeCtescan.c. It's pretty complicated, but a read of CteScanNext suggests that the queries are often executing concurrently, with one query consuming the results of another.

So. While memory might be freed at the end of each sub-statement (CTE term) there's no gurarantee that any of the CTE terms finish at any particular time.

Rephrasing your question:

  Is all in-memory data from a CTE term except RETURNING data discarded before the next CTE term executes

the answer is "no".

However, that doesn't mean PostgreSQL will keep piles of data in memory. It frequently discards rows as soon as it's done with them and never needs to see them again. It can spill big temporary results to tempfiles on disk. If it materialises, sorts or aggegates query output it will generally throw away the un-aggregated/un-sorted data after it finishes. etc.


Restoring a WordPress site from WAMP with only .ibd and .frm files, into a new WAMP with ... .myd and .myi files?

.myd and .myi files are the data and index files for MyISAM database engines. There is nothing wrong with them being there. They won't affect in any way your Innodb files that you are copying. They don't have to be overwritten if you copy the files into the folder.

In my installation of wampserver the MyISAM files (myd and myi) are one folder deeper than the ibdata1 file. That folder name should be the name of your database. If its not there then create the folder with the database name.

Copy the data that you have over. This probably won't worth without ibdata, but just in case, after copying the other files into the appropriately named folder, click the wamp icon in the bottom right of the screen and select my.ini. Add the line:

innodb_force_recovery = 1

Restart wamp server. Let me know if your database is able to actually get anything out of the files you copied over.


SQL Server how to detect unused views and/or get usage stats

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc.
Since you want a method that works across 2000-2012, your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event:
http://msdn.microsoft.com/en-us/library/ms175013.aspx
You will want to filter on the ObjectType = 8278 so that you only catch Views being accessed:
http://msdn.microsoft.com/en-us/library/ms180953.aspx
Then setup a job that pulls the file data in and aggregates the counts up every couple of hours (or even days depending on the data generation rate), and you'll be able to accurately trace out the accesses that are occurring.
Yeah I hate Trace as much as the next guy, but this is one of those scenarios where it is the right tool for the task at hand.
FWIW, on 2012 you could use Server Audit or Extended Events for this to track the object access as well, but once you create the Trace definition for 2000, it should be portable for the most part to 2012, and you can use Profiler to generate the scripts to make it much easier to work with.


It only allows access to data dictionary views, so the only security implication is that the users(s) would be able to see which objects existed in the other schemas. They would not, however, be able to actually view the data in the other schemas.

I said there was only one security implication, but another is that they would be able to view the source of any stored procedures/functions in all schemas, by viewing DBA_SOURCE etc.


MySQL, get rows for non-matching reference or reference does not exist in other table

SELECT c.category_id
FROM category c
LEFT JOIN type_match t ON c.category_id = t.category_id AND t.type_id = 3
WHERE t.category_id IS NULL

Or the same in NOT EXISTS form:

SELECT category_id
FROM category c
WHERE NOT EXISTS ( SELECT NULL
                   FROM type_match t
                   WHERE c.category_id = t.category_id
                     AND t.type_id = 3 )


PostgreSQL: Foreign key on string column

varchar keys are okay, but they do have some issues:

varchars use more space than ints
you are more likely to have to update a varchar value than an int value, causing cascading updates
might not be appropriate in internationalized applications (i.e. different values for different languages)

To answer your particular question, no there won't be a pointer to the value, the actual value will be stored.

I rarely use varchars in key columns. Sometimes I'll use short char columns like ISO country codes, for convenience.

Also, you can just use the text type in PostgreSQL, as varchar uses text internally. Also, I'd recommend using unquoted snake_case column names.


Database Mail stops working after browser service stopped

It has issues because it would use the instance name rather than a port number, and the Browser service is required to do translation from instances to ports. Your other apps must be hardcoded to use the port numbers (or have aliases set in their SQL Client settings, or something similar).

Anyway when a mail is sent this triggers MSSQL\Binn\DatabaseMail.exe to run. I wasn't able to find out how it specifically works out what the default server name is, but you may be able to reconfigure it by creating an MSSQL\Binn\DatabaseMail.exe.config like this on each node, and including a port number:

<configuration>
    <appSettings>
        <add key="DatabaseServerName" value ="LocalServerName" />
        <add key="DatabaseName" value ="msdb" />
    </appSettings>
</configuration>


How can I implement full-text search on a single value in a JSONB column?

The way I read this question, you only care about message. The difficulty here is that you need to,

map over a json array returning the message element
reduce/fold the array of message element strings to an aggregate string.

This is easy in functional programming. It's not as easy with the stock functions in PostgreSQL, and it'd be difficult to make it work with a declarative language. Maybe one day you'll have a jsonb_array_elements(jsonb [,path]) which will get you by but until then we can create a function in our database.

Creating a function with plpgsql

Note this probably isn't as a fast nor as clean as a plv8 function, but in the next revision we'll return a tsvector.

Here we use jsonb_array_elements to expand the json, and then aggregate back the 'message' elements into a string.

CREATE OR REPLACE FUNCTION jsonb_message_to_string( jsondata jsonb, out string text )
AS $func$
  BEGIN
    SELECT INTO string
      string_agg(d->>'message', ' ')
    FROM jsonb_array_elements(jsondata) AS d;
    RETURN;
  END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

Creating tsvector_agg and improving our function.

This function is not yet optimal though because it's returning a string. However, there is a second difficulty in that as of 9.6 PostgreSQL does not yet ship with a tsvector_agg; but, it's PostgreSQL so we can make one..

CREATE AGGREGATE tsvector_agg (tsvector) (
  SFUNC = tsvector_concat,
  STYPE = tsvector
);

This permits us to now return an aggregate tsvector which is faster and retains positional information. Now we can improve our function. Here we create a new jsonb_message_to_tsvector.

CREATE OR REPLACE FUNCTION jsonb_message_to_tsvector( jsondata jsonb, out tsv tsvector )
AS $func$
  BEGIN
    SELECT INTO tsv
      tsvector_agg(to_tsvector(d->>'message'))
    FROM jsonb_array_elements(jsondata) AS d;
    RETURN;
  END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

Now we can create our index..

CREATE INDEX ON FOO
  USING gin (jsonb_message_to_tsvector(jsondata));

And we would query it like so..

SELECT jsonb_message_to_tsvector(jsondata) @@ 'first'
FROM foo;


Search for partial matches in website domain names

This looks like a perfect use for a trigram index. As long as your queries don't get much shorter than the one shown.  If you want to search for '%.br%', then probably not much is going to help you.


how can I use a CASE with an alias defined befor case?

OK, I'll take the provided example, and modify it a bit:

select
    (<insanely complex expression>) as pop,
    case
        when pop is null then 'isnull'
    end
from rap4 ;

The above will of course produce an error because an alias defined in the SELECT list, cannot be used in another expression in the same SELECT list or the (same level) WHERE or GROUP BY clauses.

What you can do however is either duplicate the expression:

select
    (<insanely complex expression>) as pop,
    case
        when <insanely complex expression> is null then 'isnull'
    end
from rap4 ;

or use a derived table:

select
    pop,
    case
        when pop is null then 'isnull'
    end
from
    ( select
          (<insanely complex expression>) as pop
      from rap4
    ) t ;

or use a common table expression (CTE):

with cte as
  ( select
        (<insanely complex expression>) as pop
    from rap4
 )
select
    pop,
    case
        when pop is null then 'isnull'
    end
from cte ;


Unable to open database (Datafile corrupted)

Ok, so you have no backup.

You made sure it is not a permission issue.

But you have Data Pump dumps, at least that is something.

First try to recover the datafile without data loss:

startup mount
alter database datafile 'V:\DB\CST001.DBF' online;
recover datafile 'V:\DB\CST001.DBF';

Answer the prompts accordingly, if any. If this succeeds, you can open the database with:

alter database open resetlogs;

I highly doubt the above would work in your case, so below is the data loss scenario.

startup mount
alter database datafile 'V:\DB\CST001.DBF' offline drop;
alter database open;

Using OFFLINE DROP allows you to open the instance (as long as the affected datafile is not critical) without it trying to access that file. Since you can not restore and recover the datafile without backup, you can drop the tablespace (I am just guessing the name from the datafile):

drop tablespace cst including contents and datafiles;

Recreate the tablespace:

create tablespace cst datafile 'V:\DB\CST001.DBF' size ..;
alter tablespace cst add datafile 'V:\DB\CST002.DBF' ...;
...

Regrant any tablespace quotas if you had them before (check DBA_TS_QUOTAS before dropping the tablespace).

And import what you have in your Data Pump dumps.

The not so easy way would be trying to salvage data from the other datafiles in the tablespace before dropping it.


Good video tutorials/series for learning Oracle?

Search the Oracle Learning Library / OBE (Oracle-By-Example)


logging in Bulk logged recovery VS Full recovery model

I finally found the answer Red gate transaction log management book by Tony and Gail shaw.

Below is the Snip for a select into operation done in full recovery model:

From the above snip we can see all the operations done to sometable are logged under the context of lop_format_page and logrecordlength is also 8276 bytes which indicates SQl eventhough under full recovery model,doesnt log each and every row,rather it logs entire image of the page plus log headers.

Below is the Snip for the same operation done in Bulk logged recovery model:

From the above Snip,we can see now there is no record indicating LOP_FORMAT_PAGE,rather Sql is now tracking only page allocations and extent allocations,plus some internal system table changes,but no data pages are present in the log.

Thanks to redgate for the amazing Book


SQL Server is known for by default consuming as much memory as the OS allows it to take, so you'll have to reduce it manually.

One way of doing this is described here and here. On the second link, specifically, you will find this example that might be useful for you:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Instead of 4096, you might want to enter something smaller depending on your available RAM.

And, as @outcoldman says in a comment to your question, SQL Server Express doesn't allow 15 GB databases. This answer specifies 4 GB max for SQL Server Express 2005 and 10 GB max for SQL Server Express 2008 & 2012.


MySQL is inserting "" as 0 in decimal fields. How to stop that?

This seems be an on-going bug although it is not considered a bug, such as Bug #73041: Error 1366 when loading null into columns with decimal type using strict mode

According to Oracle's MySQL Documentation

  If you are not using strict mode, then whenever you insert an “incorrect” value into a column, such as a NULL into a NOT NULL column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error:

  The following rules describe in more detail how this works:

  If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possible value, whichever is closest to the invalid value.

  For strings, MySQL stores either the empty string or as much of the string as can be stored in the column.

  If you try to store a string that does not start with a number into a numeric column, MySQL Server stores 0.

That's also in the the regular MySQL Documentation.

With rules like these, that's why a zero(0) pops up for an unspecified DECIMAL value.

If you look back at the bug report, someone got around it when using \N for null values while loading a CSV file with LOAD DATA INFILE.


Is it better to start with a smaller INT types and grow as needed?

I would suggest right-sizing your columns from the get-go.  You need to take into account expected growth of the data versus the life of the application.  If it will take 100 years for your integer data to require an INT datatype, then you can likely stick with a SMALLINT if you think your application likely won't live that long.  If it will take only a few years to get there, then it would be easier to just handle that right now rather than risk an application failure when the upper limit of SMALLINT is reached or to risk forgetting to fix it when it gets close.

If you don't, your application will likely fail at some point, likely in the middle of the night or when you're least expecting it!  Or will fail for someone else who is tasked with maintaining your application.

If the users can live with having your application be down while you determine that your data type has exceeded it's size and take steps to fix it, then you can wait. I would bet that is not an acceptable scenario though and you would like to keep the application up time as close to 100% as possible.

Don't wait for "someday" to arrive, get it right today!


How to find out who is active and locking the oracle database?

Please try the below query to see if it's helps:

  select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;


Find a date range from rows with consecutive dates and identical column values

This is a 'Gaps and Islands' problem.   I took this example and tweaked it for your situation.

DECLARE @TestData TABLE (
    ID NUMERIC(18) NOT NULL
    ,[DATE] DATE NOT NULL
    ,[TYPE] VARCHAR(20) NOT NULL
    ,[VALUE] VARCHAR(20)
    )
INSERT INTO @TestData VALUES
(17482, '2016-08-24', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-25', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-26', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-04', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-05', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-16', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-17', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-23', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-24', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-25', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-27', 'VOLUME_EXCEPTION', 'Low'),
(17482, '2016-09-28', 'VOLUME_EXCEPTION', 'Low')
;

WITH mycte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY [Value] ORDER BY [Date]
                ), [Date]) AS grp
    FROM @TestData
    )
--select * from mycte       --Uncomment to see the data from mycte
SELECT min([Date]) AS [From]
    ,max([Date]) AS [To]
    ,[Type]
    ,[value]
FROM mycte
GROUP BY [Type]
    ,[value]
    ,grp
ORDER BY [From];

| From       | To         | Type               | value |
|------------|------------|--------------------|-------|
| 2016-08-24 | 2016-08-26 | PRESSURE_EXCEPTION | Over  |
| 2016-09-04 | 2016-09-05 | PRESSURE_EXCEPTION | Over  |
| 2016-09-16 | 2016-09-17 | PRESSURE_EXCEPTION | Under |
| 2016-09-23 | 2016-09-25 | PRESSURE_EXCEPTION | Under |
| 2016-09-27 | 2016-09-28 | VOLUME_EXCEPTION   | Low   |

The OVER/PARTITION logic is basically assigning each row to a 'group' so you can pick out the MIN and MAX from each group.  If you un-comment the
select * from mycte (I added an order by date) and run the script from that part to the top, you'll see that each row gets assigned to a group.

Look at the first group of rows assigned to '2016-08-23'.  The ROW_NUMBER for date '2016-08-24' is 1, so the DATEADD subtracts 1 from '2016-08-24' to put that row in group '2016-08-23'.  The ROW_NUMBER for date '2016-08-25' is 2, so the DATEADD subtracts 2 from '2016-08-25' to put it in the same group as row number 1 and so on.

| ID    | DATE       | TYPE               | VALUE | grp        |
|-------|------------|--------------------|-------|------------|
| 17482 | 2016-08-24 | PRESSURE_EXCEPTION | Over  | 2016-08-23 |
| 17482 | 2016-08-25 | PRESSURE_EXCEPTION | Over  | 2016-08-23 |
| 17482 | 2016-08-26 | PRESSURE_EXCEPTION | Over  | 2016-08-23 |
| 17482 | 2016-09-04 | PRESSURE_EXCEPTION | Over  | 2016-08-31 |
| 17482 | 2016-09-05 | PRESSURE_EXCEPTION | Over  | 2016-08-31 |
| 17482 | 2016-09-16 | PRESSURE_EXCEPTION | Under | 2016-09-15 |
| 17482 | 2016-09-17 | PRESSURE_EXCEPTION | Under | 2016-09-15 |
| 17482 | 2016-09-23 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-24 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-25 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-27 | VOLUME_EXCEPTION   | Low   | 2016-09-26 |
| 17482 | 2016-09-28 | VOLUME_EXCEPTION   | Low   | 2016-09-26 |

Now, it's just a matter of pulling the MIN and MAX from each group.


Below code fully meets the requirement:

PER_PERIOD_START_DATE between

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3) + 14
ELSE Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3)
END
)
and trunc(sysdate, 'MM')-1

Alternative:

PER_PERIOD_START_DATE between

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN '15-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(sysdate, -9)))
ELSE '01-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(sysdate, -9)))
END
)
and trunc(sysdate, 'MM')-1


Most of what these pragmas do for SQLite can be best accomplished by using an UNLOGGED table or a TEMPORARY table.

This also makes it very obvious that these are ephemeral tables, that aren't crash or restart safe. It also highlights that there are very real tradeoffs for blazing speed versus data durability.

Going off of the PRAGMA documentation for SQLite, I've attempted to translate them into equivalent PostgreSQL GUC variables.

pragma synchronous = OFF; is roughly equvalent to setting synchronous_commit off. If you're using a TEMPORARY or UNLOGGED table, this setting will have no real effect because those types of tables aren't actually written into the WAL anyway.

pragma journal_mode = OFF; shuts off the WAL, in PostgreSQL you can only do that with UNLOGGED or TEMPORARY tables.

pragma temp_store = MEMORY; temporary tables in PostgreSQL can write to disk, but setting temp_buffers should let you keep more of the tables in memory for better performance.

pragma count_changes = OFF; is deprecated in SQLite, and doesn't really make sense in PostgreSQL.

Robert Haas has a great blog posting about Temporary and Unlogged Tables.

Whatever you do, be sure to not put any of your tablespaces in RAM. The dangers are spelled out in the following two blog posts.

PostgreSQL no tablespaces on ramdisks

Cannot recover from the loss of a tablespace


Understanding how relationship sides works

0- CASCADE means to 'replicate' the changes that happens on the 'master' table to the 'details' tables. For simplicity, assume the relation (country, city). Each city[detail] belongs to a country[master], or each city is a child of a country. With cascade, if you delete a country, all cities belong to that country will be deleted.

In your case, general_orders is the master table (call it the parent). When you delete an order, the referenced rows from natural_person and legal_person will be deleted.

1- No, not all records will be deleted. Only the referenced one will be.

2- Nothing will happen to general_order table. (If you delete a city, the country will still be there)

Regarding the design: It depends on your model, but I feel that the design is not right. i.e. I think each order should belong to a 'person', and this person's type is either natural or legal. So, I would have:

grnaral_order(id, ..., person_id, ...)
person(id, type[legal, natural])
natural_person(id, name, ...)
legal_person(id, company_name...)

This is one way, but not necessarily the best one, because it totally depends on your analysis


Oracle MODEL clause giving unusual output


Best way to async data inserts into timescaledb

If the files are comma separated or can be converted into CVS, then use Timescale tool to insert data from CVS file in parallel: timescaledb-parallel-copy

A manual approach to insert data into hypertable can be to create several sessions of PostgreSQL, e.g., by executing psql my_database in several command prompts and insert data from different files into the same hyperatble. It is important that parallel loading will insert data into different chunks. For example, if hypertable is created with default chunks size, one chunk will fit one week of data. Thus different parallel sessions should read data from files belonging to different weeks. Otherwise, parallel execution will not perform well due to conflicts. Also you want to fit active chunks into memory.

In both cases it is good that the data are already sorted by timestamp as mentioned in the question.


Join queries are not working as expected when trying to compare a count result with a value

I would start with:

SELECT course_id
FROM courses_students
GROUP BY course_id
HAVING COUNT(student_id) > 5

The result of this query is a new relation (table, derived table), which you can use as any other table. Let's call this corses_5_students

SELECT c.*
FROM courses as c
JOIN (
    SELECT course_id
    FROM courses_students
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
) AS corses_5_students
    ON corses_5_students.course_id = c.course_id


If you want to pass data to a trigger that is not provided by one of the predefined variables you could put that data in a temp table before you perform the INSERT/UPDATE/DELETE that will fire the trigger. Then you can retrieve the data from the table inside the trigger function. Temp tables are local to sessions so this is safe to do when multiple clients connect to the same database.


How do I dump only the data for all tables using mysqldump

mysqldump --no-create-db --no-create-info  ...options...

See http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html


What's a way/ways to return Identity element (that is auto-generated) in a table from a stored procedure?

The Db2 function to obtain the most recent assigned identity value is IDENTITY_VAL_LOCAL().


I cannot access local database on terminal (MAC)

It could be your admin user is connecting from admin@127.0.0.1.

Assuming admin is the actual user you have privileges on, try connecting through:

> mysql -h 127.0.0.1 -u admin -p

The way MySQL handles users, the host is very important, and in this case 127.0.0.1 is different than localhost.

If your phpMyAdmin user has access to the mysql database, you can the following statement to see what user/host combinations exist on your install.

 SELECT User, Host FROM mysql.user


Like this, I presume.
SQL> create table test (id number, old number, new number);

Table created.

SQL> insert into test (id, new) values (1, 100);

1 row created.

SQL> select * from test;

        ID        OLD        NEW
---------- ---------- ----------
         1                   100

SQL> update test set old = new,
  2                  new = 200
  3    where id = 1;

1 row updated.

SQL> select * from test;

        ID        OLD        NEW
---------- ---------- ----------
         1        100        200

SQL>


In short, put the OSes on slow storage and all the data/log & backup files on fast storage.

I know that my environment is different that yours, but I think similar principles apply.  We have a SAN and the storage pools range in different speeds, 7.2k, 10k, 15k.

We install the OSes on the slower drive pools and have all backup files and data/log files stored on the 15k drives.  There was a time when we accidentally put everything on the 15k drives and we didn't notice a performance difference.  It was brought to our attention by our SAN admin after an audit that the OS was stored on the faster drives.

We monitor SQL Server performance with perfmon and Dell Spotlight.  For grins, we went and looked at the historical metrics that mattered, and saw nothing to note.


The most direct method shrinking a table is:
ALTER TABLE [table name] ENABLE ROW MOVEMENT;
ALTER TABLE [table name] SHRINK SPACE CASCADE;

More details here: https://oracle-base.com/articles/misc/alter-table-shrink-space-online
There are some limitations to this command:

Moving rows can cause problem with rowid based triggers.
Rowid materialized views must be rebuilt after a shrink operation.
The shrinking process is only available for objects in tablespaces with
automatic segment-space management enabled.
You can't combine the SHRINK SPACE clause with any other ALTER TABLE clauses.
You can't shrink a cluster or a clustered table.
You can't shrink any object with a LONG column.
You can't shrink tables with dependent function-based indexes, domain indexes, or bitmap join indexes.
You can't shrink tables that are the master table of an ON COMMIT
materialized view Mapping tables of index-organized tables are not
affected by a shrink.
Shrinks can't be used for compressed tables, except those using Advanced Row Compression (ROW STORE COMPRESS ADVANCED).
The shrink operation against a table doesn't cascade to
the LOB segments. They need to handled separately.
You can't shrink securefile LOB segments.
Changing the arrangement of rows in a table can have a negative impact on performance in some circumstances. Test thoroughly before making any decisions.
After any structural change, like a move, remember to check for unusuable indexes. If you have any, rebuild them.


How and when do I use an alias in my SQL statement

Structured Query Language is a declarative language.  Declarative languages don't tell the computer how to run, they simply describe the desired result.  The implication here is the alias is only important for the results, not the execution of the query.

Since MySQL (and most other RDBMSs) only apply aliases after the query has completed, MySQL will complain if you attempt to use an alias in another column since it doesn't actually understand what the alias represents until the query completes.


Is it possible to get a list of procedures created with WITH EXECUTE?

You need sys.sql_modules:

SELECT p.name, m.execute_as_principal_id
  FROM sys.procedures AS p
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  WHERE m.execute_as_principal_id IS NOT NULL
  ORDER BY p.name;

If you need to identify explicit EXECUTE AS CALLER vs. no EXECUTE AS (which are functionally the same), you'll need to parse sys.sql_modules.definition, which is far from foolproof (since EXECUTE AS can appear in many different contexts, including inside comments and string literals).


Maybe I'm missing something but why wouldn't you just use a lookup table with a foreign key relationship for this sort of thing potentially with some views to provide a layer of abstraction?  That's a very common approach, it's supported by all databases, it's pretty easy to implement, and it solves other issues such as data consistency (i.e. what happens if you want to update an existing string without updating every row in the table that has that string).  If you know the maximum number of entries in the lookup table, you can pick the data type that minimizes data volumes for the primary key.  That's also much easier to handle in the future when someone decides that the number of distinct values needs to exceed whatever limit you specified initially-- you just need to increase the size of the column in both tables.


SQL Indexed View for Datamart Reporting with Transactional Replication

Yes.  It is generally safe, and often useful to create indexed views on subscriber tables in transactional replication.

You may need to drop and recreate the indexed views using custom scripts before and after initializing from a snapshot.  See Execute Scripts Before and After the Snapshot Is Applied

You can also use a different indexing scheme on the subscriber (also by using post-snapshot scripts). In particular you can make the subscriber tables Clustered Columnstore tables, or add non-clustered Columnstore indexes on them for reporting.


Trying to install SQL Server 2008 Management Studio Express

You need to install SQL Server 2008 first, then before running or rebooting install SQL Server 2008 Service Pack 1.

You are attempting to install the service pack files first, without an existing base installation of SQL Server 2008.  Since the Service Pack 1 package does not contain a full installation of SQL Server, you must have already installed the non-service-pack version.  The other option would be to create a slip-streamed version of SQL Server 2008 setup by, essentially, combining the Service Pack 1 with the original version on DVD - then installing directly from that.  Check https://duckduckgo.com/?q=%22sql+server%22+slipstream+setup for more info.

Don't click "Check for solutions online", click "Run Program" instead.  After that completes, install Service Pack 1.


Error while trying to create a database for SSRS

I installed SQL Server 2017 side-by-side with an existing SQL Server 2014 instance.

I installed SQL Server Reporting Services 2017 as per the download link.

During the installation I was asked which edition of SSRS I would like to install. The options were:

Developer Edition
Evaluation Edition (180 days)
Express Edition

I selected Developer Edition and everything worked fine.

Mixed Editions

If I install the SSRS as an Express Edition, then the configuration of the SSRS datbase will fail with the mentioned error message:

Solution

You have to install the SSRS 2017 using the same Edition as your SQL Server 2017 Edition.

If you are running SQL Server 2017 Developer Edition then you have to install SQL Server 2017 Reporting Services with the option Developer Edition.
If you are running SQL Server 2017 Evaluation Edition then you have to install SQL Server 2017 Reporting Services with the option Evaluation Edition.
If you are running SQL Server 2017 Express Edition then you have to install SQL Server 2017 Reporting Services with the option Express Edition.

You will have to de-install your current SQL Server 2017 Reporting Services and ensure you install the identical Edition.

SSRS 2017 Developer Edition with SQL Server 2017 Developer Edition

I had no issues installing SSRS 2017 Developer Edition with SQL Server 2017 Developer Edition.

SSRS Setup Step 1

SSRS Setup Step 2

SSRS Setup Step 3

SSRS Setup Step 4

SSRS Setup Step 5

SSRS Setup Step 6

SSRS Setup Step 7

If the editions match, then you won't have any issues.
If the editions don't match, then you will encounter the error message.


Reminder systems seem simple at first, but can be wonderfully complex as you think through all the things modern reminder apps are capable of.
One way to approach the problem would be with the use of a “transaction” table that can contain a number of types. By doing this, a person could “take” a reminder, which would let their colleagues know that something is in progress but not yet complete. This would also make it possible to determine whether a task was completed on a given day, or whether something was started, but not marked as complete.
For the Reminder table, something like this might work:
id
subject
note
client_id
due_at
created_at
is_deleted

The note and due_at values would be optional.
For the ReminderRepeat table, you can probably go a bit overboard if you really want to …
id
reminder_id
start_at
until_at
type
remind_at
on_sunday
on_monday
on_tuesday
on_wednesday
on_thursday
on_friday
on_saturday

start_at would default to today and until_at would be NULL to allow repeats until “the end of time”. The type could be any of onetime,daily,weekly,monthly,yearly and so on. The on_ values would let you say “Weekly on Monday, Thursday, and Friday”. By having the reminder_id here you can allow edits to the repeat schedule to be tracked over time.
Then there’s the transaction table, which would be a simple:
id
reminder_id
user_id
status
event_at

With this you can query the transaction table to determine if a reminder is done. If the reminder is to run once (or for a limited period of time), then you can update the is_deleted value in Reminder to keep lookups clean.
Mind you, I do tend to overthink these things before the first line of code is even written …


What are the best practices regarding lookup tables in relational databases?

There is a third approach which has some of the advantages of your two options - put an actual code in the code table. By this I mean a short character sequence that captures the essence of the full value and is unique. For your given example it may be
Idn: 1
Name: Democrats
Code: D      (or DEM)

The Code is carried into transactional tables as a foreign key. It is short, intelligible and somewhat independent of the "real" data. Incremental changes to the a name would not suggest a code change. Should Republicans decamp en masse, however, a change of code may be necessary, with its attendant problems that a surrogate id would not incur.
This style has been termed an abbreviation encoding. I can recommend Celko's writing on this. Google books holds several examples. Search for "Celko encoding".
Other examples:  2 or 3 letter encodings for countries, 3-letter encoding (GBP, USD, EUR) for currency codes. Short, self-explaining and not changing (and there is an ISO for them).
Each of Idn, Code and Name will be unique so each is a candidate key and any one could be chosen as the primary key. So for the example given Idn could be removed from the table definition and Code used instead. Different DBMS handle integers and strings in their own way, so there may be performance considerations. It may be useful to have Idn as the FK in some tables and Code in others.


.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

An error in the form of:

  Msg 10314, Level 16, State 11, Line 1
  An error occurred in the Microsoft .NET Framework while trying to load assembly id #####. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
  System.IO.FileLoadException: Could not load file or assembly '{_assembly_name_}, Version=#.#.#.#, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

means that the Assembly, which is currently marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE is not allowed to make use of that permission level until the second part of the SQLCLR permissions setup is taken care of. That second part is to do one of the following:

The very much preferred approach

Sign the Assembly when compiling it (and give it a password!). This is sometimes referred to as giving it a Strong Name.

If the Assembly isn't signed, and you don't have the source code to recompile it, and it doesn't have any other Assemblies that reference it, then you can still sign it by following the instructions in the first half of this blog post: http://ianpicknell.blogspot.com/2009/12/adding-strong-name-to-third-party.html
Create an Asymmetric Key in [master] from the DLL of that Assembly
Create a Login from that Asymmetric Key
Grant that Login one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (the UNSAFE ASSEMBLY permission also allows Assemblies to be marked as EXTERNAL_ACCESS, so you don't need both permissions)
Create the Assembly in whichever Database(s) it should exist in
Do NOT set the TRUSTWORTHY property of the Database(s) containing this Assembly to ON. TRUSTWORTHY can remain as OFF.

The NOT preferred approach

Alter the Database(s) where the Assembly should exist to be TRUSTWORTHY ON
Make sure that the Login of the owner of the Database(s) containing this Assembly have one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY.

If the owner of the Database(s) is sa, or any other Login that is in the sysadmin fixed server Role, then you don't need to worry about having one of these two permissions explicitly set as they are implied by the sysadmin Role.
Try to avoid this approach, if at all possible :-)


What all to start for oracle database server

If you have the entries for your instance added to /etc/oratab (that should be "orcl:/your/oracle/home:Y" ) and you did run the root.sh in the end of that installation and the root.sh placed the environment scripts in /usr/locla/bin (the default), you can issue:

ORACLE_SID=orcl
ORAENV_ASK=NO
. /usr/local/bin/oraenv
unset ORAENV_ASK
$ORACLE_HOME/bin/dbstart

What oraenv does is mainly setting environment variables like ORACLE_HOME, ORACLE_BASE and PATH. PATH is made up of $ORACLE_HOME/bin. You could add the environment setup to your .profile and of course, you can start the database manually using sqlplus.

The dbstart script starts all databases that are registered in the oratab file, that have a 'Y' in the third column.

Manually starting, after the environment has been setup for a specific instance as shown above:

sqlplus / as sysdba
startup

If you want to switch to a different database just issue . oraenv (if /usr/local/bin is in the PATH) and give it the ORACLE_SID of the database that you want to manage.

Most databases will be accessed using a client from an other machine/VM. In that case you also might want to start the listener: lsnrctl start


Selecting, filtering and aggregating in MongoDB

how we can get all the states that have at least 2 cities with
  population > 1000000 (1M). And we need to remember that city can be
  split into multiple records, and we should aggregate its population
  first. (zips dataset)

I have zips collection database having 29353 records.

> db.zips.find().count()
29353
> db.zips.count()
29353

and the documents structure inside the collection is such as

> db.zips.find().pretty()
{
        "_id" : "01007",
        "city" : "BELCHERTOWN",
        "loc" : [
                -72.410953,
                42.275103
        ],
        "pop" : 10579,
        "state" : "MA"
}
{
        "_id" : "01002",
        "city" : "CUSHMAN",
        "loc" : [
                -72.51565,
                42.377017
        ],
        "pop" : 36963,
        "state" : "MA"
}
{
        "_id" : "01008",
        "city" : "BLANDFORD",
        "loc" : [
                -72.936114,
                42.182949
        ],
        "pop" : 1240,
        "state" : "MA"
}
{
        "_id" : "01011",
        "city" : "CHESTER",
        "loc" : [
                -72.988761,
                42.279421
        ],
        "pop" : 1688,
        "state" : "MA"
}
{
        "_id" : "01010",
        "city" : "BRIMFIELD",
        "loc" : [
                -72.188455,
                42.116543
        ],
        "pop" : 3706,
        "state" : "MA"
}

which is the same as mention documents structure by the OP.

{
     "city": "ACMAR",
     "loc": [
         -86.51557,
         33.584132
     ],
     "pop": 6055,
     "state": "AL",
     "_id": "35004"
}

Suppose that here i want to write an aggregation query with a single match phase that filters for zipcodes with greater than 100,000 people.

Here i will write down the simple query with $match (aggregation) and Operator $pop to filter the documents.
$match Filters the documents to pass only the documents that match the specified condition(s) to the next pipeline stage.

> db.zips.aggregate([{$match:{pop:{$gt:100000}}}])
{ "_id" : "10021", "city" : "NEW YORK", "loc" : [ -73.958805, 40.768476 ], "pop" : 106564, "state" : "NY" }
{ "_id" : "10025", "city" : "NEW YORK", "loc" : [ -73.968312, 40.797466 ], "pop" : 100027, "state" : "NY" }
{ "_id" : "11226", "city" : "BROOKLYN", "loc" : [ -73.956985, 40.646694 ], "pop" : 111396, "state" : "NY" }
{ "_id" : "60623", "city" : "CHICAGO", "loc" : [ -87.7157, 41.849015 ], "pop" : 112047, "state" : "IL" }
>

In the above example i have found out the 4 states and their respective cities . Where population is > 100000.

  we need find a day (date) with the largest number of HTTP 500 errors
  (status=500).

For spherical queries, use the 2dsphere index result.

As MongoDB documented Here The use of 2d index for spherical queries may lead to incorrect results, such as the use of the 2d index for spherical queries that wrap around the poles.

MongoDB geospatial queries on  GeoJSON objects calculate on a sphere; MongoDB uses the WGS84 reference system for geospatial queries on GeoJSON objects.


Activating standby DR after opening it in read-write mode

If you had flashback enabled or created a restore point before activating the standby database, you can flashback the database to that point/SCN, and the standby database can continue from there like nothing happened.

You can do this manually, or if you have Data Guard Broker configured, you can use the REINSTATE DATABASE command to do this.

In case you have not prepared for this, you will need to rebuild (duplicate/restore) the standby database.

Next time do not do this, there is a feature called Snapshot Standby designed for testing tasks like this, that works just as I described above.


Where to start learning the IDEF1X method of model notation?

Answer left in comments by bbaird:
IDEF1X is commonly described as a methodology, and perhaps the reason we see it as "another way to represent a model" is that it was among the first and so all other graphical representations that have come since have borrowed from it in one way or another (typically through trying to copy features from ERWin).  I've found this to be a good primer on the subject:
http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf
You can also read the original standard here:
https://nvlpubs.nist.gov/nistpubs/Legacy/FIPS/fipspub184.pdf


Try

IF int(s_day) < DAY(s_date)
THEN
    SET start_date = THIS_MONTH(s_date) + int(s_day) days;
END IF


PostgreSQL: permission denied for relation

I just wrote about this in my answer to Granting rights on postgresql database to another user on ServerFault.

Basically, the best solution when you have a single user and you want to grant other users the same rights is to turn that user into a group, create a new user with the same name as the original one that's a member of the group, and grant that group to other users too.

So in your case, rails gets renamed to say myapp_users, then you create a new login role (user) named rails and GRANT myapp_users TO rails. Now you an GRANT myapp_users TO meltemi. Both the new rails account and the meltemi user now have the rights of the old railsaccount.

For more fine-grained control I ususally advise that you avoid giving the day-to-day login users or their groups ownership of the tables. Give them access via a NOINHERIT group they must explicitly SET GROUP to, or better, use a completely different user for privileged operations like DDL and GRANTs. Unfortunately this doesn't work with Rails, because Rails likes to apply migrations whenever it feels like it and AFAIK doesn't give you the ability to specify a different, more-privileged user to run the migrations as.


InnoDB internals - checkpoint, LSN, dirty pages

Your answers are on the source code: innodb_buffer_pool_pages_dirty. Most of these variables are maintained on memory directly or through very simple calculations. There is no danger of durability loss, as the ones that are "essential" for innodb to work can be recalculated from the transaction log or are 0 on start.

Not really, the lsn is just an offset in bytes, it is maintained on memory (log_sys->lsn) and in the case of a failure, it can be recovered from the header of the transaction log (plus pure file offsets). They are changed on commit/log flush/tablespace flush, both on memory and on disk (exact method depends on the durability configuration, buffering type, etc.).

For basic InnoDB stuff, you can check this blog post by the Oracle InnoDB team and/or check the source code for InnoDB.


What are each of the separate mongodb-org packages for ubuntu?

Please find below the details of each item from MongoDB official documentation.


Problem With Loading txt File in mySql 5.6 Command Line Client

If you already enabled LOAD DATA INFILE and verified its SET to ON, most likely the command you are using or the path you specify for the text file might be wrong.

The command would be mysql> LOAD DATA LOCAL INFILE '/path/filename.txt' INTO TABLE tablename;

Refer to Load Data Infile.


How do I run a long-running stored procedure on SQL Server, even if I get disconnected?

Azure Automation: Schedule the Execution of SQL Jobs in Azure with Automation Service

and make a small powershell script and schedule it in the azure dashboard.


How to configure SSRS (SQL Server 2012) to run on a different instance, but same server as SharePoint

Are you sure of your version of Sharepoint? I thought SP2010 could not run on SQLS2012 but 2008 and 2008R2.

With which machine do you try to access the report server ?

If you have SP and SSRS native mode installed on the same machine you will get error due to the address (SP and Report Server use the same). You can change the report server address in the Reporting services configuration manager (Start, Microsft SQL server 2012, Configuration Tools) and Report Manager URL tab.


A non-clustered index scan may be chosen in this scenario:

the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans
the non-clustered index is "skinnier" than the clustered index
the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still cheaper than a clustered index scan)

It can also happen, obviously, if you have a heap (no clustered index) and 3. is still true.

This is not meant to be an exhaustive list - there are other cases where a non-clustered index scan may be chosen, but this is probably the most common.


If you add a TABLOCK or READUNCOMMITTED hint to the table, you will get an allocation-ordered scan, which will report the same number of logical reads as the number of pages in the table.

With an allocation-ordered scan, SQL Server uses allocation structures to drive the distribution of pages among threads. IAM page accesses are not counted in STATISTICS IO.

For a non-allocation-ordered scan, SQL Server breaks up the scan into subranges using index keys. Each internal operation to find and distribute a new page, or range of pages (based on a key range), to a worker thread requires accessing the upper levels of the b-tree. These accesses are counted by STATISTICS IO, as are the upper-level accesses made by worker threads when locating the start point of their current range. All these extra upper-level reads account for the difference you see.

Too much weight is given to I/O statistics in my opinion. Tune your queries according to what is important to you and your workload. This is often elapsed time, but resource utilization can also be a factor. No one metric is 'best' - you should take a balanced view.


Buffer pool above 90% utilized causes MySQL to crash

First, take a look at the InnoDB Architecture

Look closely at the Buffer Pool in the upper left corner.

There is an Insert Buffer inside it. It is responsible for migrating changes to nonunique indexes from the Buffer Pool to the Insert Buffer inside the system tablespace (better known to us commoners as ibdata1).

When InnoDB tables have lots of secondary indexes, heavy INSERTs, UPDATEs, and DELETEs to those tables will trigger growth and congestion in the Buffer Pool's Insert Buffer.

The setting innodb_change_buffer_max_size governs what percentage of the Buffer Pool is to be use for the Insert Buffer. The default is 25. That means if you allocate 24G for innodb_buffer_pool_size, don't be shocked if the Insert Buffer uses 6G of it.

When a Buffer Pool is 90% full of data, it's only good for doing heavy SELECTs (when doing reports and analytics). In your particular case, since you ran that query and determined that 90% of the Buffer Pool had data, that leaves only 10% for the Insert Buffer and other InnoDB incidentals. It is entirely possible that a loading of a mysqldump could push the limits of a Buffer Pool that has an insufficient amount of room for its Insert Buffer. Personally, I have never heard anyone complain about InnoDB crashing MySQL because of this.

For the sake of erring on the side of caution, perhaps you may want to try setting innodb_change_buffer_max_size to 50 when doing heavy INSERTs with

SET GLOBAL innodb_change_buffer_max_size = 50;

and scaling it back to 25 with

SET GLOBAL innodb_change_buffer_max_size = 25;

afterwards.

Incidentally, you could set it with

SET GLOBAL innodb_change_buffer_max_size = 10;

If you are not doing any heavy INSERTs for days or weeks. Just remember to set it back to 25.


Log shipping 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER

Logshipping is a death tested technique that has been around since many ages.

Looking at your specific error sequence on secondary server

The log restores errors out after a couple of successful restores.
The database goes into suspect mode
you tried to restore the log files and they restore it fine, so the log files are not corrupt.

You should check if the Bytes per Physical Sector on secondary server for the disk that you are trying to restore is NOT 512 bytes.

You can install the Hotfix for SQL Server 2008 R2 SP2 or correct the disk bytes per physical sector.


If you are running on Linux, you can use control groups to limit MongoDB memory as shown in the following article:

Easy Steps to Limit Mongodb Memory Usage by Ramakanta Sahoo

On Windows, a similar technique using the Windows System Resource Manager is described in:

Limit MongoDB memory use on Windows without Virtualization by Simon Green


Why is SQL Profiler showing empty for database name and database id?

Confirm the trace is actually gathering those columns.  The following query will show the events and columns being captured for each trace (except the default system trace):

SELECT TraceID = t.id
    , TracePath = t.path
    , EventName = te.name
    , ColumnName = tc.name
FROM sys.traces t
    CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
    LEFT JOIN sys.trace_events te ON ei.eventid = te.trace_event_id
    LEFT JOIN sys.trace_columns tc ON ei.columnid = tc.trace_column_id
WHERE t.id > 1 /* exlude the system trace */
ORDER BY t.id
    , ei.columnid;

The results look something like this (depending on the events and columns you've defined for your trace):

╔═════════╦═════════════════╦═════════════╦═════════════════╦═════════╦══════════╗
║ TraceID ║    TracePath    ║  EventName  ║   ColumnName    ║ eventid ║ columnid ║
╠═════════╬═════════════════╬═════════════╬═════════════════╬═════════╬══════════╣
║       2 ║ C:\temp\trc.trc ║ CursorClose ║ ApplicationName ║      78 ║       10 ║
║       2 ║ C:\temp\trc.trc ║ CursorClose ║ SPID            ║      78 ║       12 ║
║       2 ║ C:\temp\trc.trc ║ CursorClose ║ EventSequence   ║      78 ║       51 ║
╚═════════╩═════════════════╩═════════════╩═════════════════╩═════════╩══════════╝


First order by importance then group by section

i used this query

SELECT t.id, t.section, t.rev, t.importance FROM (
   SELECT docs.id, docs.section, docs.rev, (SELECT SUM(docs_importance.importance) FROM docs_importance WHERE docs_importance.doc_id = docs.id) AS importance
   FROM docs
   GROUP BY docs.id
   ORDER BY importance DESC
) AS t
GROUP BY t.section

where i first made a subquery as a temporary table then ordered it by importance then grouped the outer query by section to get the final result.


Exporting schema data (tables, permisions) into another schema on same database

Use expdp and impdp, in combination with the remap_schema option.

For example...

Firstly, make sure the target user/schema is already created. It may also temporarily need the IMP_FULL_DATABASE role, but I haven't tested this.

Create a directory object to hold the dump files and logs (make sure this exists on your filesystem):

create directory DUMPDIR as '/path/to/your/dump/directory';
grant read, write on directory DUMPDIR to system;

Export the data, specifying the schema you wish to export ( FROMUSER in this case):

expdp fromuser/frompass@yourdb schemas=FROMUSER directory=DUMPDIR dumpfile=fromuser.dmp logfile=expdp.log

Import with impdp:

impdp touser/topass@yourdb directory=DUMPDIR dumpfile=fromuser.dmp logfile=impdp.log remap_schema=fromuser:touser

remap_schema=fromuser:touser does the magic!

You can also do this without creating an interim dumpfile by using the network_link= parameter. See the Oracle documentation for more information.


Difference between request and session in sp_blitzwho?

One of the cool things about open source stored procedures like sp_BlitzWho is that you can actually see the source code. It's really useful when you have questions about where data is coming from and what it means.

If I open sp_BlitzWho and do a control-F for request_writes, for example, I see that it's:

COALESCE(r.writes, s.writes) AS request_writes,

And a little further down, I can see which tables the r and s aliases point to:

FROM sys.dm_exec_sessions AS s
             LEFT JOIN sys.dm_exec_requests AS r

From there, you can hit the documentation for sys.dm_exec_sessions and sys.dm_exec_requests, which explain that a request is a currently executing task (like a query), and a session is someone's connection that has been open for a while, which may have performed several requests over time.

As you suspected, wait_info is indeed what the query is waiting on - but rather than spoon-feeding you that one, I'll let you start your journey of reading the source code there. Hope that helps!


What can go wrong using the same sequence across multiple tables in postgres?

Three possible issues that spring to mind are:

With any shared resource you are creating a potential bottleneck. My gut says that for your peak load this should not be an issue but I strongly suggest benchmarking any such solution in a production-like production-sized environment to be sure.
You are essentially assigning meaning to surrogate keys which defeats part of their purpose in RDB theory. A surrogate key by its nature should not have meaning beyond being a key for identifying tuples in that relation. If the entities might have meaning together and so need collision free keys, is it correct that they are being modelled separately or has something been missed in the requirements and/or data model design?
You are introducing a potential point of failure. What if a deployment doesn't get its initial sequence starting point set? You then either have a deployment blocking error or deployments start from the same place "breaking" your feature. Also, what will you do if somewhere down the line someone thinks it is a good idea to branch a deployment (in production perhaps a tenant company divests part of itself and needs to separate out the data). What if the seed somehow gets reset by a bad upgrade deployment or other migration?[0]

If none of those issues concerns you then go ahead, the idea isn't going to break anything IMO. Of course there may be better ways even if this one isn't wrong in itself.

When you say "UUID-lite" you imply that you have already considered and discounted UUIDs. Is that the case, and if so are there particular reasons for deciding they are not suitable for this project?

One possible reason for not using UUIDs is index fragmentation though the significance of that is often greatly over-stated[1]. SQL Server's answer to this is the "sequential GUID" which is pretty much equivalent to what you are suggesting if we discount assigning meaning to key values - perhaps postgres has an equivalent to that? Of course always increasing indexes can have their own performance issues (last-page contention, index stats growing stale) in some very specific high volume workloads[2].

Another common argument against UUIDs is key length: why use 16 bytes per value when 4 or 8 will suffice? If the uniqueness is truly a useful property then this will usually trump key-size concerns significantly. If key-size is a concern but you are happy to use a 64-bit INT rather than needing to keep inside 32-bits you could use your technique without adding a potential shared-resource contention issue by doing your seeded-integer-key idea per table[3] using a normal INT IDENTITY(<start>, 1)[4] column definition, though again this is adding deployment complexity (a small amount, but certainly not zero).

Human readability is sometimes cited as a problem, but that goes back to assigning meaning to surrogate keys.

Compressibility is a less common concern but one you might come across. To just about any compression algorithm UUIDs are likely to look like random (therefor uncompressible) data unless you are using something like SQL server's sequential UUIDs. This might be a concern for a very large set of links (or other block of data) that contains many entity IDs served to an application over a slow network, or if needing to use something like SQL Server's index compression features, though both these matters are essentially just restating the key size concern in a slightly different way and sequential UUIDs may help here too.

[0] this could happen for normal identity columns too of course, but as you are using a less common feature you are increasing the chance of a less experienced DBA after you missing the problem if it happens once you are off doing something new and exciting elsewhere!

[1] I'm a SQL Server guy, I suspect the potential issue is the same in postgres but for all I know it may have a different index layout that can mitigate the effect.

[2] Though again these may be SQL Server specific especially the latter of the two examples I listed

[3] Top two bytes: vary by database, next two: vary by table, remaining four: the incrementing bits

[4] That is MS SQL Server syntax, postgres syntax may vary but you shoudl see what I mean and be able to translate

tl;dr: if you find yourself reinventing the wheel, make sure that all of the existing designs really aren't suitable before starting to considering why a new one might or might not be.


What is the difference between "OPTION (OPTIMIZE FOR UNKNOWN)" and "OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))"

The documentation on query hints says

DISABLE_PARAMETER_SNIFFING ... is equivalent to trace flag 4136.

That trace flag was documented in KB980653 which tells us

Enabling trace flag 4136 disables parameter sniffing, which is equivalent to adding an OPTIMIZE FOR UNKNOWN hint to each query which references a parameter.

The documentation on supported trace flags says about 4136

... to accomplish this at the database level, see the PARAMETER_SNIFFING option in ALTER DATABASE SCOPED CONFIGURATION

To accomplish the same result at the query level, add the OPTIMIZE FOR UNKNOWN query hint. The OPTIMIZE FOR UNKNOWN hint doesn't disable the parameter sniffing mechanism, but effectively bypasses it to achieve the same intended result.

.. a second option to accomplish this at the query level is to add the USE HINT 'DISABLE_PARAMETER_SNIFFING' query hint instead of using this trace flag.

It seems to me that these are all a single feature, wrapped in several syntactic formulations, to be applied at different granularity. It can be applied on the instance, a database, the session, a stored procedure, a query or a single parameter within that query. They all do the same thing, just use a different form of words to achieve it. This opinion is supported by the steps to apply when addressing sniffing issues which uses OPTIMIZE FOR and DISABLE.. in turn.
Some of the duplication can be explained in the general move from opaque trace flags to developer-friendly transparent hints. I find "disable sniffing" easier to reason about than "optimize for unknown".
I suppose we may never know if there is any actual code-path difference internally, unless someone with source code access happens to becoming interested in answering. Practically, I don't believe there is.

I looked at the plans produced for very simple queries
DBCC TRACEON(3604);
dbcc freeproccache;
declare @c int = 1;

select * from dbo.Skewed where c = @c
OPTION (QUERYTRACEON 8605, USE HINT('DISABLE_PARAMETER_SNIFFING'));
-- OPTION (QUERYTRACEON 8605, OPTIMIZE FOR UNKNOWN);

The query plans for both hints are indistinguishable. Both the actual execution plan and the bound tree (trace flag 8605) are the same.


A Canonical Name record (abbreviated as CNAME record) is a type of resource record in the Domain Name System (DNS) used to specify that a domain name is an alias for another domain, the "canonical" domain.

So, you give a 'dnsname' to your config servers. For example mongoCfg1, mongoCfg2 and mongoCfg3.

Those dns you can register on a dns server, or in the hosts files of your mongo-servers:

mongos:~$ cat /etc/hosts
127.0.0.1 localhost
127.0.1.1   mongos1

10.0.0.10   mongoCfg1
10.0.0.11   mongoCfg2
10.0.0.12   mongoCfg3
10.0.0.13   mongoShard1Repl1
10.0.0.14   mongoShard1Repl2
...

In your mongos config file or startup parameters or even the replication/shard configuration in your mongodb, it is adviced to use those CNAMES.

Example of mongos with cname as configdb parameters:

mongos --configdb mongoCfg1:27019,mongoCfg2:27019,mongoCfg3:27019 --port 27017 --logpath /data/log/log.mongos

In case you need to replace a server, let's say mongoCfg2 needs to be replaced, then you can change the ip for mongoCfg2 in all the hosts files so they will refer to the new config server.


Stop, Start, or Restart a SSAS Tabular service instance in PowerShell with Invoke-Command and $variables

The script block works a bit different when it comes to passing variables. You have to tell the command what the variable contains on the remote computer.

As well if you want to pass the full command as a variable you will need at the type of the variable as [scriptblock].

$stop = "stop-service 'MSOLAP$TABULAR'"
Invoke-Command -ComputerName $server { $stop }

This needs to be changed to something like this (changed variable names to suite my example):

$MyService = 'W32Time'
$get = [scriptblock]::create( "param(`$servicename) Stop-Service `$servicename")
Invoke-Command -ComputerName sql2014-01 -ScriptBlock $get -ArgumentList $MyService

The other way:

$MyService = 'W32Time'
Invoke-Command -ComputerName SQL2014-01 -ScriptBlock {param($servicename); Stop-Service $servicename }  -ArgumentList $MyService


How to dump to csv valid json with Postgresql?

The quotes around fields and inside fields are part of the CSV format, and they're required here, because, according the CSV spec:

  Fields containing line breaks (CRLF), double quotes, and commas should
  be enclosed in double-quotes
  If double-quotes are used to enclose fields, then a double-quote
     appearing inside a field must be escaped by preceding it with
     another double quote

I think that you don't want or need CSV in your case. Just take the output of SELECT, with the unaligned format of psql

=# \pset format unaligned
Output format is unaligned.

=# select json_build_object('foo', 1, bar, 2) AS myjson
   from (values (E'xy\zt'), ('ab,cd')) as b(bar);

myjson
{"foo" : 1, "xyzt" : 2}
{"foo" : 1, "ab,cd" : 2}
(2 rows)

You may also use \g output.json instead of the semi-colon at the end of the query to have psql redirect the results of that query to a file, and \pset tuples_only to remove headers and footers.


PostgreSQL: Corrupt primary key, inconsistent table

There are various ways this can happen in Oracle - I'm not sure about postgres, but I think I would call this an "integrity violation" rather than "corruption"

Perhaps you can do one of the things suggested here, ie set enable_indexscan = off or

begin;
drop index rank_details_pkey;
select user_id, count(*) from rank_details group by user_id having count(*) > 1;
rollback;

But "there are likely some locking issues with this, so be careful with it in production"

The idea is to force the query to scan the table rather than just the index (which does not have the duplicates). You may also, and more simply, be able to acheive the same by:

select user_id, f(<some other column>), count(*)
from rank_details
group by user_id, f(<some other column>)
having count(*) > 1

where f() returns a constant, which may trick the planner into a table scan.


How to get count for different columns on same table

It's easiest with SUM() and a CASE statement:

select CompanyName,
sum(case when StatusID=1 then 1 else 0 end) as TotalOpenClaims,
sum(case when StatusID=2 then 1 else 0 end) as TotalClosedClaims,
sum(case when StatusID=3 then 1 else 0 end) as TotalReOpenedClaims,
sum(case when StatusID=4 then 1 else 0 end) as TotalPendingClaims
from Claims
group by CompanyName;


3 Masters must be in a circle.  A Slave can hang off any of them.

However, Multi-master is a nightmare when any Master dies for any reason.  I strongly recommend against building such a system.

Replication systems usually expect all data to be on all servers.  There are many exceptions, but I await your justification for not doing that.


Help required with query (join) optimization

The problem is the use of scalar functions. These are executed once per reference per row, and the current internal implementation is such that this is very nearly as expensive as running a separate query per invocation (18 million times, for 6 million rows with three function references per row).

A quick solution is to convert the function to an in-line table-valued function. These are in-lined into the query text, much in the same way views are expanded before query optimization. So the first step is to translate the function to:

CREATE FUNCTION dbo.dFK_InLine
    (@dt as sql_variant)
RETURNS table
AS
RETURN
    SELECT
        ReturnValue =
        CASE
            WHEN CA.datatype = 'int'
                AND @dt >= 19000101 AND @dt <= 20451231
                THEN CONVERT(integer, @dt)
            WHEN (CA.datatype = 'int' AND @dt < 19000101)
                OR (CA.datatype = 'int' AND @dt > 20451231)
                THEN 1
            WHEN @dt IS NULL
            THEN 1
            WHEN (@dt < CAST('1900-01-01 00:00:00.000' AS DATETIME)
                OR @dt > CAST('2045-12-31 11:59:59.000' AS DATETIME))
                AND CA.datatype = 'datetime'
            THEN 1
            WHEN (@dt < CAST('1900-01-01' AS DATE)
                OR @dt > CAST('2045-12-31' AS DATE))
                AND CA.datatype = 'date'
            THEN 1
            ELSE FORMAT(CAST(@dt AS DATETIME2), 'yyyyMMdd')
        END
    FROM
    (
        VALUES
        (
            CONVERT(varchar(128), SQL_VARIANT_PROPERTY(@dt, 'BaseType'))
        )
    ) AS CA (datatype);

Then modify the source query to use it:

SELECT
    SD.ReturnValue AS StartDate, -- INTEGER DATE!
    ED.ReturnValue AS EndDate,
    EFD.ReturnValue AS EffectiveDate
FROM OpenEnrollmentWindow oew
CROSS APPLY dbo.dFK_InLine(oew.StartDate) AS SD
CROSS APPLY dbo.dFK_InLine(oew.EndDate) AS ED
CROSS APPLY dbo.dFK_InLine(oew.EffectiveDate) AS EFD
INNER JOIN ProductYear py
    ON oew.OrganizationProductYearID = py.ID
INNER JOIN Marketplace m
    ON py.MarketplaceID = m.ID
INNER JOIN Organization o
    ON m.OrganizationID = o.ID
INNER JOIN Consumer c
    ON c.OrganizationID = o.ID
LEFT JOIN OpenEnrollmentWindowProduct oewp
    ON oew.ID = oewp.OrganizationOpenEnrollmentWindowID
LEFT JOIN OpenEnrollmentWindowProductType oewpt
    ON oew.ID = oewpt.OrganizationOpenEnrollmentWindowID;

All that said, this is still quite an...unusual strategy, particularly the use of sql_variant and the CASE logic. You might get better value from refactoring the design to use a strong types, and a more traditional model.


MS SQL Server 2000 ODBC driver for Linux

@Dan, this is by design. SQL Server 2005 is not longer supported. Newly released client drivers support SQL Server 2005+.

That said, can you try this?

wget https://gallery.technet.microsoft.com/scriptcenter/SQLCMD-and-BCP-for-Ubuntu-c88a28cc/file/142121/1/Ubuntu%2014.04%20MSFT%20ODBC%20Driver.sh
sudo bash Ubuntu\ 14.04\ MSFT\ ODBC\ Driver.sh
sqlcmd –S yourServerName.database.windows.net – U yourUsername – P yourPassword

These steps install the older version (ODBC Driver v11).


JOIN to obtain all the rows from the first table along with NULLs from the second table

First of all, the correct type of join to use in this case is a left join:

...
FROM
    [tblRepresentatives] AS tblReps

LEFT OUTER JOIN
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
...

In your specific case, you could still use FULL because this condition in WHERE would turn it into a left join anyway:

tblReps.[sActive] = 'True'

But it is better to express the intent accurately.

The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE condition:

tblDaily.sDate = CAST(GETDATE() AS DATE)

transforms your join further into an inner join. That is why the 37th row is missing from the output.

The reason for that happening is this. The FROM clause returns a null in tblDaily.sDate for the unmatched row. Because the WHERE clause logically executes after the FROM, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE) does not evaluate to True.

What you need to do instead is move the sDate condition to the ON subclause:

...
FROM
    [tblRepresentatives] AS tblReps

LEFT OUTER JOIN
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
                                   AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE
    tblReps.[sActive] = 'True'

That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE) before the join takes place. Consequently, the query will return the expected 37 rows from tblReps complemented either with matching data from tblDaily or with nulls.


JOIN tables and return count even if it is 0

LEFT JOIN is the key, like ypercube commented.

And you probably don't need the self-join - assuming settings.customer_id is unique not null.

If your predicate is selective (more than a few rows without alarms), I suggest to count in a LATERAL subquery:

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
     , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN LATERAL (
   SELECT count(*) AS reg_ct
   FROM   registration
   WHERE  customer_id = s.customer_id
   AND    date = '2018-10-10'
   ) r ON true
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

Else, a plain subquery should be faster:

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
     , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN (
   SELECT customer_id, count(*) AS reg_ct
   FROM   registration
   WHERE  date = '2018-10-10'
   GROUP  BY 1
   ) r USING (customer_id)
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

Either way, it's typically faster and less error prone to aggregate before the join.
Related:

Understanding multiple table join with aggregation
What is the difference between LATERAL and a subquery in PostgreSQL?


Add article to transactional publication without generating new snapshot

You can add the article through SSMS using the GUI and even apply filters to it. As long as you do not change any of the other properties of the article you will not need to generate a full snapshot.

When you hit OK in the publication GUI (after adding the article), it will close without prompting to reinitialize - if it does prompt to reinitialize, then you have changed something which requires a FULL snapshot. If that happens, hit cancel and try again.

After you add the article you can simply start the snapshot job and you will notice that it only generates a snapshot for the new article (called a mini-snapshot).

Then check your distribution job, and notice that it created the table at the subscriber and bulk-copied your data.

Good luck, and let me know if you require further assistance.


There is already an object error after database is dropped

You’ve created a database called dbo but you’re creating the table in the current database. Try adding this before the create table statement:

USE dbo;

I might choose a different database name, too. dbo has specific meaning in SQ Server.


SQL Server equivalent of Oracle USING INDEX clause

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle?

No. When you create a primary key or unique constraint in SQL Server, a unique index to support that constraint is created automatically, with the same keys.

  Which seems to imply that there is a way of specifying what index should be used for a Primary Key.

No. The documentation is only attempting to explain whether the automatic supporting index will be created as clustered or nonclustered, if you do not specify. It is confusingly worded, I agree.

To clarify, when you add a primary key constraint to an existing table without expressing a preference, the supporting index will be clustered if there is no pre-existing clustered index on the table. The supporting index will be created
as nonclustered if there is already a clustered index

You can specifically request a clustered or nonclustered primary key using: PRIMARY KEY CLUSTERED or PRIMARY KEY NONCLUSTERED.

In fairness, the documentation is much clearer on the subject at:

table_constraint (Transact-SQL)


How does innodb_stats_on_metadata relate to query execution plan stability?

Here is something you may find surprising

Pick any large InnoDB table. Let's say the table is mydb.mytable.

Run this

SHOW INDEXES FROM mydb.mytable;

If you run this query repeatedly, you will notice that the Cardinality column changes. That happens because the InnoDB Storage Engine tries to compute (estimate) the Cardinality based on the BTREE indexes and their leaves rather than brute force counting.

Disabling innodb_stats_on_metadata makes the Cardinality stand still to compute a consistent Explain Plan.

I wrote about this before

Can SHOW TABLE STATUS query performance on MySQL be improved - Amazon RDS
Why are these queries slow in some environments and not others? Can we fix their performance?
When are InnoDB table index statistics updated?
MySQL IS NULL / IS NOT NULL Misbehaving?


Query behavior - with regard to statistics

Auto-stats and stats per se is only one aspect. Another is the plan, the calculated selectivity, and also the fact that plans can be cached.
So imagine a plan was generated when stats were "ok" (along with other circumstances such as sniffed parameter values). This can now stick around for a while in cache, and even if the stats deteriorate (a bit), your "good plan" can linger in cache.
Then you have the aspect that autostats samples data. The larger the dataset, the smaller the part of that data set that is sampled. So even when auto-stats kicks in, with a larger dataset you may get lower quality stats.


Accidentally Deleted SQL Server Log Reader Service Account

Ah, I guessed it.  It was "NT AUTHORITY\NETWORK SERVICE".  When I added it back as a login, then the log files showed up after I refreshed the node tree.  (sorry for the noise)


Implementing "ANY" aggregate operator in T-SQL

I think you are trying to optimize a performance problem that doesn't exist. I don't know why you think a GROUP BY is going to be problematic here. Conceptually there isn't really much difference between what you wrote and the following:

SELECT d.Id, d.Name, s.c
  FROM dbo.Departments AS d
  INNER JOIN
  (
    SELECT ID = DepartmentID, c = COUNT(*)
    FROM dbo.Personnel
    GROUP BY DepartmentID
  ) AS s
  ON d.ID = s.ID;

So this query doesn't do the GROUP BY on the Name column, but in my testing this is actually less efficient than your version unless you create a non-clustered index on Personnel.DepartmentID (in which case the plans are exactly the same). I suggest optimizing performance when you actually observe a performance problem, not based on hunches. In your case the grouping essentially comes along for free because it's part of the same clustered index scan that has to read all the data anyway. I'm not sure how forcing SQL Server to pick a random name (which is going to be in the same row as the ID you already read anyway) can possibly make anything faster...


Teradata SQL Assistant - List tables under DBC in the Database Explorer

By default, Teradata SQL Assistant will attempt to query the views DBC.Databses, DBC.Tables, DBC.Columns to populate the Database Explorer. It is possible in your environment that those objects are not accessible to developers or end users via the PUBLIC user. Instead, you may need to modify your connection settings ODBC or .Net Provider to use the X-Views in DBC. These are a collection of views which restrict the rows returned based on the privileges your user account has been granted to access or which you have created.

The ODBC DSN, .Net Provider, and JDBC drivers for Teradata have a means to use the X-Views by default to enable database tools such as SQL Assistant or Teradata Studio/Studio Express to populate the database explorer controls "transparently". Try this and see if it works.


What should we do when same column fields occurring multiple time in many tables?

You can achive this by creating History table. In that table you can store all changes from those tables. You have to use tiggers to populate data in History table. Take into account that if the history table stores all changes you need engine to populate it.
Another approach is to have History table for each table that you want to observe. In your case Countries_History.
You have to decide when you make design what amount of such info to keep.
Of course you can just remove all that columns if you don't care about the info they store.


setup transaction replication in cross domain

You need to add a CNAME in your DNS at  your primary SERVER putting the DR server's hostname.  This way publisher.mainsite.mycompany.com can be resolved.  Then use publisher as the hostname to use when connecting.


Self referencing scalar function nesting level exceeded when adding a select

This is a bug in project normalization, exposed by using a subquery inside a case expression with a non-deterministic function.

To explain, we need to note two things up front:

SQL Server cannot execute subqueries directly, so they are always unrolled or converted to an apply.
The semantics of CASE are such that a THEN expression should only be evaluated if the WHEN clause returns true.

The (trivial) subquery introduced in the problematic case therefore results in an apply operator (nested loops join). To meet the second requirement, SQL Server initially places the expression dbo.test6(1) + dbo.test6(2) on the inner side of the apply:

[Expr1000] = Scalar Operator([dbo].[test6]((1))+[dbo].[test6]((2)))

...with the CASE semantics honoured by a pass-through predicate on the join:

[@i]=(1) OR [@i]=(2) OR IsFalseOrNull [@i]=(3)

The inner side of the loop is only evaluated if the pass-through condition evaluates to false (meaning @i = 3). This is all correct so far. The Compute Scalar following the nested loops join also honours the CASE semantics correctly:

[Expr1001] = Scalar Operator(CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END)

The problem is that the project normalization stage of query compilation sees that Expr1000 is uncorrelated and determines that it would be safe (narrator: it isn't) to move it outside the loop:

[Expr1000] = Scalar Operator([dbo].[test6]((1))+[dbo].[test6]((2)))

This breaks* the semantics implemented by the pass-through predicate, so the function is evaluated when it should not be, and an infinite loop results.

You should report this bug. A workaround is to prevent the expression being moved outside the apply by making it correlated (i.e. including @i in the expression) but this is a hack of course. There is a way to disable project normalization, but I have been asked before not to share it publicly, so I won't.

This problem does not arise in SQL Server 2019 when the scalar function is inlined, because the inlining logic operates directly on the parsed tree (well before project normalization). The simple logic in the question can be simplified by the inlining logic to the non-recursive:

[Expr1019] = (Scalar Operator((1)))
[Expr1045] = Scalar Operator(CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(int,[Expr1019],0)+(2),0))

...which returns 3.

Another way to illustrate the core issue is:

-- Not schema bound to make it non-det
CREATE OR ALTER FUNCTION dbo.Error()
RETURNS integer
-- WITH INLINE = OFF -- SQL Server 2019 only
AS
BEGIN
    RETURN 1/0;
END;
GO
DECLARE @i integer = 1;

SELECT
    CASE
        WHEN @i = 1 THEN 1
        WHEN @i = 2 THEN 2
        WHEN @i = 3 THEN (SELECT dbo.Error()) -- 'subquery'
        ELSE NULL
    END;

Reproduces on the latest builds of all versions from 2008 R2 to 2019 CTP 3.0.

A further example (without a scalar function) provided by Martin Smith:

SELECT IIF(@@TRANCOUNT >= 0, 1, (SELECT CRYPT_GEN_RANDOM(4)/ 0))

This has all the key elements needed:

CASE (implemented internally as ScaOp_IIF)
A non-deterministic function (CRYPT_GEN_RANDOM)
A subquery on the branch that should not be executed ((SELECT ...))

*Strictly, the above transformation could still be correct if evaluation of Expr1000 was deferred correctly, since it is referenced only by the safe construction:

[Expr1002] = Scalar Operator(CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END)

...but this requires an internal ForceOrder flag (not query hint), which is not set either. In any case, the implementation of the logic applied by project normalization is incorrect or incomplete.

Bug report on the Azure Feedback site for SQL Server.


Is there any way to replace sql server file_guid with new GUID?

We had to do the same - as we wanted to copy a database to a new database, changing the name and file IDs at the same time.

We found the original process posted here to not to be repeatable, I guess it depends on how the original file ids were created.  In any event, I have updated the following procedure, which is repeatable.

Backup database from RDS to S3 using standard RDS database backup.

exec msdb.dbo.rds_backup_database
@source_db_name = '<database-name>',
@s3_arn_to_backup_to = 'arn:aws:s3:::<bucket-name>/<backup-filename>'

Restore .bak file into SQL Server on a local machine.  We have MSDN account, so we just downloaded SQL 2016 and installed, giving us full access to SQL on a local machine.  But, I believe you can download SQL Express and do the same, some functionality may be lost depending on the source version of the SQL Engine.
Perform a Export Data Tier Application for the database.  This will create a .bacpac file which includes the schema, data and code (stored procedures, etc.).
Delete the original data imported into SQL Server in step 3.
Perform an Import Data Tier Application and point to the .bacpac file created in step 3.  This step will set the file guids = _Data and _Logs, making them unique.
Do a full backup to get a new .bak file.
Upload new .bak file to S3 and do standard RDS database import.

exec msdb.dbo.rds_restore_database
@restore_db_name = '<database-name>',
@s3_arn_to_restore_from = 'arn:aws:s3:::<bucket-name>/<backup-filename>'


Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

There is absolutely no way to reduce the size of ibdata1 in-place. As for the already present data pages and index pages in ibdata1 from obsolete tables, you could let mysql overwrite them with transactions over time.

RECOMMENDATION #1

You must migrate all data out and reload with an empty ibdata1.

I have already addressed this in StackOverflow back in Oct 29, 2010 (Steps Outlined).

I have also addressed this in other posts in the DBA StackExchange

Jul 19, 2012 : whether a big (but not used) ibdata1 slow anything down
Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
Apr 19, 2012 : MySql - Clean ibdata1 (Steps Outlined)

If you cannot do this in Production at this time, you will have to present this as a case study for the need to do so. Here is what you do:

Setup a Staging DB Server

No initial data
innodb_file_per_table configured

Take mysqldump of Production (use --single-transaction)
Load the mysqldump into Staging

Please note that ibdata1 will not grow dramatically because data and index pages for each InnoDB table will reside in its own personal tablespace file.

For example, the table mydb.mytable will reside in ibdata1. All you will have is /var/lib/mysql/mydb/mytable.frm as an external manifestation of the table.

Once you load the Staging DB Server, the table will look like this on disk:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

Only data dictionary elements of the table are left behind in ibdata1.

That will give you a look at what production will look like when you perform the InnoDB Cleanup.

RECOMMENDATION #2

Once you have performed InnoDB CleanUp in Production, you may be concerned with old data trapped within each individual .ibd file. Cleaning up an .ibd file is much easier.

To cleanup the InnoDB table mydb.mytable, run one of the following:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
OPTIMIZE TABLE mydb.mytable;

That's it. The only difference is that OPTIMIZE TABLE may try to run ANALYZE TABLE under the hood. It may get bypassed for an InnoDB table.

RECOMMENDATION #3

If the goal is to truly stomp over all unused pages in ibdata1, here is something you may want to try out on a Staging Server:

STEP01) Extract all InnoDB tables from ibdata1. (Since you have already done so, I will write this up for the benefit of others):

SQLTORUN="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLTORUN="${SQLTORUN} FROM information_schema.tables WHERE engine='InnoDB'"
mysql -uroot -p...  -ANe"${SQLTORUN}" > ExtractInnoDB.sql
mysql -uroot -A < ExtractInnoDB.sql

STEP02) Create a large temp table in MyISAM called test.junkman:

CREATE TABLE test.junkman (a INT) ENGINE=MyISAM;
INSERT INTO test.junkman VALUES (0);
INSERT INTO test.junkman VALUES SELECT * FROM test.junkman; ## run this line 30 times

When done, you will generate a 7 GB MyISAM table with no indexes:

mysql> SELECT data_length/power(1024,3) TableSize
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> AND table_name='junkman';
+-----------+
| TableSize |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

mysql>

This should take about 10-15 minutes to create.

STEP03) Disable innodb_file_per_table

Goto /etc/my.cnf and comment it out

[mysqld]
#innodb_file_per_table

then service mysql restart

STEP04) Create InnoDB table test.junkman2

CREATE TABLE test.junkman2 LIKE test.junkman;
ALTER TABLE test.junkman2 ENGINE=InnoDB;

This will create test.junkman2 inside ibdata1

STEP05) In another SSH session run this watch

watch ls -l /var/lib/mysql/ibdata1

STEP06) Load test.junkman INTO test.junkman2

INSERT INTO test.junkman2 SELECT * FROM test.junkman;

STEP07) Repeat STEP06 until the size of ibdata1 actually changes in the other SSH session

STEP08) Uncomment innodb_file_per_table in /etc/my.cnf to re-enable it

[mysqld]
innodb_file_per_table

STEP09) service mysql restart

STEP10) TRUNCATE TABLE test.junkman2; DROP TABLE test.junkman2;

STEP11) TRUNCATE TABLE test.junkman; DROP TABLE test.junkman;

I've never tried this before (this was just a working theory although the 7GB MyISAM stunt was real). Go give it a try and let me what happens.


Is saving disk space still considered an advantage of normalization?

Normalization was never about saving disk space. In his 1971 normalization paper E.F.Codd wrote: "It is important to remember that we are not making a case for or against any physical storage structures in this paper"[1] (emphasis in the original).

Normalization is all about the best logical representation for data and by no means implies that a normalized database will take up less space than a denormalized one.

Given the range of advances in database storage techniques since then (e.g. compressed formats, join indexes, columnar storage) there is probably even less correlation between normalization and storage size today than there was 50 years ago.

[1] Normalized Data Base Structure: A Brief Tutorial


Group multiple rows with different column values to a single row

You should be able to get the desired result GROUPING BY student and getting max value of each subject.

You can find some examples of MAX function on TERADATA here or here.

SELECT   student as StudentID,
         MAX(maths) as Maths,
         MAX(physics) as Physics,
         MAX(chemistry) as Chemistry
FROM     student
GROUP BY student;