Is a composite index also good for queries on the first field?

It certainly is. We discussed that in great detail under this related question:

Working of indexes in PostgreSQL

Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (much less common) 4 bytes on a 32-bit OS. If you are not sure, check pg_controldata. It also depends on data types of indexed columns (some require alignment padding) and actual content.
An index on, say, two integer columns (4 bytes each) typically ends up to be exactly as big as an index on just one, where another 4 bytes are lost to alignment padding.
Update for Postgres 13: The new index deduplication changes this. Repeated index values can be compressed. An index on (a,b) is often less duplicative, so it gains less from deduplication.
If a is a unique column, deduplication doesn't change much. (There can still be duplicative entries due to the MVCC model, only one visible to any transaction at the same time, so adding b can still have a minor effect.)
Apart from that, there is really no downside for the query planner to use an index on (a,b) - compared to an index on just (a). And it is generally preferable for multiple queries to use the same index. The chance for it (or parts of it) to reside in (fast) cache grows when shared.
If you already maintain an index on (a,b), then it doesn't make sense to create another index on just (a) - unless it is substantially smaller. The same is not true for (b,a) vs. (a). Follow the link in the first line for more on that.
Coming from the opposite direction, when you need an additional index like that on (a,b), then consider dropping an existing index on just (a) - if possible. Often not possible as that's the index of a PK or UNIQUE constraint. Since Postgres 11 you might get away with just appending b to the constraint definition with the INCLUDE clause instead. Details in the manual.
Or create the new index on (b,a) instead to cover queries on just b additionally. For only equality conditions the order of index expressions in btree indexes does not matter. It does, though, when involving range conditions. See:

Multicolumn index and performance

There are potential downsides to including additional columns in an index, even if that only uses space otherwise lost to alignment padding:

Whenever the additional column is updated, the index now needs an update, too, which might add cost to write operations and create more index bloat.
HOT updates (Heap Only Tuple) on the table are not possible while any index column is involved.

More on HOT updates:

Redundant data in update statements

How to measure object sizes:

Measure the size of a PostgreSQL table row


How does mysql decide if concurrent insert is possible for a MyISAM table?

While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:

  we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Yes (emphasis mine):

  If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. [src]

Disclaimer: I haven't actually tested it. It seems unless you inserted the exact same data-length in the holes, you will still have holes somewhere.

You can see if there are holes from a query such as this (data_free=0 would mean no holes):

SELECT table_name, data_free FROM information_schema.tables WHERE table_schema='FOO' AND engine='myisam'


What is the fastest way to duplicate inserts of an identity column to another table?

Try

WITH cte AS ( INSERT INTO data(name)
              VALUES ('some value'), ('one more value')
              RETURNING id )
INSERT INTO ids
SELECT id
FROM cte;

fiddle


How to insert all the dates in a date range into Oracle database?

You can use a hierarchical query to generate a sequence of dates, something like this:

insert into my_table (my_date_column)
select dt + (level - 1)
from (select date '<your start date>' dt from dual)
connect by dt + (level - 1) <= date '<your end date plus 1 day>';

Date arithmetics: dt + (level - 1) -- are smart enough to know when one month ends and another begins (including Februaries in leap years).

If you do need a PL/SQL function for this, I'm sure you will be able to wrap it around this statement easily.


How to make MySQL use indexes in an integer range select query

Whenever you use AND clause and picking exact string/integer type with equals = . Its effective to use composite index.

Try having composite index on hilong and lolong. See if the query picks index, as you have two different columns in their own ranges I'm not sure if it could help.

It mostly depends upon how the app-db architecture is defined. If I were to be you.

See if this is in transactional path or analytics path?
See if you can identify any other AND condition that has PK/higher cardinality would reduce the range scan of the columns.
If the table growth is limited or high, upon which you can keep the logic in Mysql if its limited and Latency with data retention is accepted by End users. If not work on other DB stores to suit its requirement in ElasticSearch/Cassandra or any DistributedSystems etc with retention.


How does table compression affect SQL Server's buffer pool?

When data is in the buffer pool it is compressed.  The data within the row/column needs to be decompressed when the row/column is read.  The storage engine team (the guys that wrote this stuff) did a blog post about this which has some good info in it.  (Yes I stole the above line from the blog post.)


How are entries in index leaf node ordered?

The actual data in a page is not necessarily in any particular order.
The logical order of data in any index 8k page is determined in the row offset array.
https://www.sqlservercentral.com/blogs/does-a-clustered-index-really-physically-store-the-rows-in-key-order


Actually DBMS_UTILITY.COMMA_TO_TABLE and DBMS_UTILITY.TABLE_TO_COMMA are a very old procedures. Nowadays people usually prefer this one:

WITH t AS
    (SELECT TRIM(REGEXP_SUBSTR('25,27', '[^,]+', 1, LEVEL)) AS n, LEVEL as pos
    FROM dual
    CONNECT BY REGEXP_SUBSTR('25,27', '[^,]+', 1, LEVEL) IS NOT NULL),
a AS
    (SELECT pos,
        CASE n
            WHEN '25' THEN 'Bird.'
            WHEN '27' THEN 'Elephant.'
        END AS animal
    FROM t)
SELECT LISTAGG(animal, ' ') WITHIN GROUP (ORDER BY pos) AS value
FROM a;


AlwaysOn Availability Groups go to Resolving status

I've seen this happen a lot when folks only have one set of network cables connecting their servers - meaning, a pair of 1Gb Ethernet cables in each node, at best, and they're using those for both regular networking as well as iSCSI storage connectivity. (The fact that you're using Equallogic is a clue - I've seen a lot of those with 1Gb implementations.)

If you have any networking problems at all:

The two nodes won't be able to see each other
Neither node will be able to see the storage
Presto, no one sees a majority, and you lose quorum

Things that can cause this include:

Backup software (doing huge reads from disk while simultaneously saturating network)
Running a CHECKDB (again, huge reads from disk plus huge writes to TempDB, which can prevent the cluster heartbeats from getting through if you only have one network interface for both regular networking and storage)

To work around it:

Use separate network interfaces for regular networking and iSCSI (like a pair of 1Gb (at least) ports just dedicated to iSCSI, and nothing else)
Use faster network interfaces (like 10Gb instead of 1Gb)
Do less disk/network-intensive work (stop doing backups and CHECKDB, ha ha ho ho, but also ease up on the index rebuilds)


Yes, you will get the same results in every case. There are a few exceptions where SQL Server doesn't seem to process the code in the order the code is written, and a few cases where there are no guarantees at all. Some are intentional and some are unintuitive.

GOTO

GOTO b2;

b1:
PRINT 'first';
GOTO b3;

b2:
PRINT 'second';
GOTO b1;

b3:
PRINT 'done';

Results:

second
first
done

This might not be expected simply because these labels can get lost in more complex code, can be hit only in certain conditions, etc.

Deferred Name Resolution

CREATE PROCEDURE dbo.floob AS
  SELECT * FROM dbo.ThisTableDoesNotExist;

This is kind of related in that it allows you to create an object at compile time that references an object that will cause the procedure to fail when you call it. I mention it only because it allows you to create this procedure before you create the table that the procedure will ultimately depend on.

SQLCMD mode

There is a mode in SSMS that allows you to intersperse SQLCMD and T-SQL. SSMS will make a first pass at the code and process all the SQLCMD bits, and then make a second pass where it will process the T-SQL. Let's say you have a table with an identity column, and a file that includes an insert into this table. With the following script run in SQLCMD mode, the filename.sql query will get executed on the first pass, and the insert of Bob will happen on the second pass. So the IDENTITY values will be generated in the opposite order:

:CONNECT instance
INSERT dbo.table(name) VALUES('Bob');
:r filename.sql

Compile Time Name Checking

IF 1=1
  CREATE TABLE #foo1(x int);
ELSE
  CREATE TABLE #foo1(y int);

Result:

  Msg 2714, Level 16, State 1, Line 4
  There is already an object named '#foo1' in the database.

The parser has gone ahead to line 4 and prevented you from creating two objects of the same name, even though that would never have been possible at runtime due to the IF conditional never being able to evaluate as true (line 2 was never executed).

Order of execution of a single statement

Depending on the version and compatibility level of SQL Server, you may see various cases where clauses are not evaluated in the order you expect. E.g.:

CREATE TABLE #x(y varchar(32));

INSERT #x(y) VALUES('foo'),(4);

SELECT y FROM #x
  WHERE ISNUMERIC(y) = 1
    AND y < 5;

Result:

  Msg 245, Level 16, State 1, Line 5
  Conversion failed when converting the varchar value 'foo' to data type int.

SQL Server tried to evaluate the y < 5 predicate before the ISNUMERIC() result could filter out ineligible rows. People often first try to use a CTE or correlated subquery, but that often fails because you still can't control the order in which these things are evaluated. Typical workarounds include using TRY_CONVERT() or a CASE expression (example here, and more on CASE in a minute), while more elaborate and less desirable approaches exist (such as dumping the filtered rows to a #temp table first).

CASE + AGG

While in most cases a CASE expression is the only surefire way to guarantee order of evaluation (misconceptions abound, though), even that can fail in some scenarios, one of which was discussed here at great length:

Example that fails, even though the ELSE should never be reached:

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

Result:

  Msg 8134, Level 16, State 1
  Divide by zero error encountered.

JOIN order

For completeness I want to make sure it is clear that if your query says...

SELECT ... FROM t1
  INNER JOIN t2
  INNER JOIN t3

...SQL Server is free to perform those joins in whatever order is most efficient, unless you use OPTION (FORCE ORDER) or any other hint that implicitly forces order. So you may see join strategies that optimize a join between t1 and t3, and then join those results to t2.

Surely, there are others...

...those are just a few off the cuff.


group tick data to minute data using MySQL

What you want is something like this which uses the extract(HOUR_MINUTE..)

SELECT t1.date, t1.time, t2.price, t2.vol
FROM (
  SELECT date,
    extract(HOUR_MINUTE from time) AS time,
    max(id) AS maxid
  FROM tbl
  GROUP BY date, extract(HOUR_MINUTE from time)
) AS t1
JOIN tbl AS t2
  ON t1.maxid = t2.id;

The inner selects and groups by the date,hour/min, and grabs the max(id) and count(*). The outer joins back to the table and resolves to find the price.

In other news, why are you storing date and time separately? You should be using a timestamp.


MySQL query with an OUTFILE to csv not working when called from a batch file

I bet that the double quote is the culprit: it terminates the string at ENCLOSED BY ', and the rest derails the whole thing.  The solution is very simple: just escape the double quote in your SQL string:

... ENCLOSED BY '\"' ...

(I have to tell that on my local Windows box and psql (instead of mysql) client I don't have to escape the double quote.)


Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

A composite key like that counts as non-sequential ordering and will be splitting around the 1/2 fill factor, although this depends on the actual usage.

If author 1 makes the first 3 blog posts followed by author 2 making the next 3, you'd get keys like:
11
12
13
24
25
26

and innodb will pick a fill rate closer to 15/16 then 1/2, but if they post intermixed:
11
22
13
24
15
26

innodb will pick a fill rate closer to 1/2 rather then 15/16.

The actual fill rate will be entirely dependent on your data.

For your I/O thoughts, I would use the composite key over an auto-increment as it will be less I/O overhead for a single primary composite key vs a auto-increment key and a composite key together. Given it's a key you are not updating, your effective fill rate shouldn't be overly important and if you run an optimize table, it will compact your primary index to 15/16 anyway, so if you actually notice an performance improvement from a higher packed index, you can manually make it so.


There are 4x different RDBMS products under the Sybase name ... Adaptiver Server Enterprise (ASE), SQLAnywhere, IQ and Advantage.  The 4x products do not share a common SQL language/syntax.
The question references a link for the create trigger syntax in ASE.
The sample code in the question appears to also be for ASE.
However, from the comments we've discovered the OP is actually using the SQLAnywhere product (version 16.0.0.1915).  And while SQLAnywhere does provide some half-workable support for ASE's T-SQL dialect, it does not include a common syntax for the create trigger command.
From the comments: OP has located the documentation for SQLAnyhere's create trigger command.
Using one of the examples from that link (scroll down to find CREATE TRIGGER myTrig), and modifying OP's current trigger code to match the documentation, I'm thinking OP's looking for something like:
create trigger "DBA"."WKM_autoFillCL143"
after insert order 4 on "DBA"."case_checklist"
REFERENCING NEW AS inserted
FOR EACH STATEMENT
BEGIN
....

NOTES:

I work primarily on ASE, and my SQLAnywhere experience is rather limited, so OP may need to tweak the above to work correctly in SQLAnywhere; also ...
OP should review the various options available via SQLAnywhere's create trigger command (eg, should the trigger fire before or after the insert?)
OP will need to review the rest of the current (ASE/T-SQL) trigger code to see which parts can be used 'as is' in SQLAnywhere and which parts will need to be rewritten to match SQLAnywhere's syntax
OP will also want to verify SQLAnywhere's support for quoted identifiers (ie, does the current use of double-quoted identifiers meet SQLAnywhere's syntax?)


I have been using extended properties since they are very flexible.  Most standard documentation tools can be driven off MS_Description, and then you can use your own with custom-built tools.

See this presentation: #41-Get a Lever and Pick Any Turtle: Lifting with Metadata

And this code: http://code.google.com/p/caderoux/wiki/LeversAndTurtles


Count Total Transaction each specific row even if 0

SELECT
    channels.channel AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM (           SELECT 'PC01' channel
       UNION ALL SELECT 'PC02'
       UNION ALL SELECT 'PC03'
       UNION ALL SELECT 'PC04'
       UNION ALL SELECT 'PC05'
       UNION ALL SELECT 'PC06'
       UNION ALL SELECT 'PC07'
       UNION ALL SELECT 'PC08'
       UNION ALL SELECT 'PC09') channels
LEFT JOIN B2C_BUY_LOG ON B2C_BUY_LOG.PG_CHANNEL = channels.channel
LEFT /* or INNER */ JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER /* or LEFT */ JOIN COMM_CODE ON B2C_channels.channel = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
GROUP BY Payment_Channel, Payment_Name;


Separate month and year columns, or date with day always set to 1?

Personally if it's a date, or can be a date, I suggest always storing it as one. It's just easier to work with as a rule of thumb.

A date is 4 bytes.
A smallint is 2 bytes (we need two)

... 2 bytes: one smallint for year
... 2 bytes: one smallint for month

You can have one date which will support day if you ever need it, or one smallint for year and month which will never support the extra precision.

Sample data

Let's look at an example now.. Let's create 1 million dates for our sample. This is approximately 5,000 rows for 200 years between 1901, and 2100. Every year should have something for every month.

CREATE TABLE foo
AS
  SELECT
    x,
    make_date(year,month,1)::date AS date,
    year::smallint,
    month::smallint
  FROM generate_series(1,1e6) AS gs(x)
  CROSS JOIN LATERAL CAST(trunc(random()*12+1+x-x) AS int) AS month
  CROSS JOIN LATERAL CAST(trunc(random()*200+1901+x-x) AS int) AS year
;
CREATE INDEX ON foo(date);
CREATE INDEX ON foo (year,month);
VACUUM FULL ANALYZE foo;

Testing

Simple WHERE

Now we can test these theories of not using date.. I ran each of these a few times so as to warm things up.

EXPLAIN ANALYZE SELECT * FROM foo WHERE date = '2014-1-1'
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=11.56..1265.16 rows=405 width=14) (actual time=0.164..0.751 rows=454 loops=1)
   Recheck Cond: (date = '2014-04-01'::date)
   Heap Blocks: exact=439
   ->  Bitmap Index Scan on foo_date_idx  (cost=0.00..11.46 rows=405 width=0) (actual time=0.090..0.090 rows=454 loops=1)
         Index Cond: (date = '2014-04-01'::date)
 Planning time: 0.090 ms
 Execution time: 0.795 ms

Now, let's try the other method with them separate

EXPLAIN ANALYZE SELECT * FROM foo WHERE year = 2014 AND month = 1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=12.75..1312.06 rows=422 width=14) (actual time=0.139..0.707 rows=379 loops=1)
   Recheck Cond: ((year = 2014) AND (month = 1))
   Heap Blocks: exact=362
   ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.64 rows=422 width=0) (actual time=0.079..0.079 rows=379 loops=1)
         Index Cond: ((year = 2014) AND (month = 1))
 Planning time: 0.086 ms
 Execution time: 0.749 ms
(7 rows)

In fairness, they're not all 0.749.. some are a bit more or less, but it doesn't matter. They're all relatively the same. It simply isn't needed.

Within one month

Now, let's have fun with it.. Let's say you want to find all intervals within 1 month of Jan 2014 (the same month we used above).

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE date
    BETWEEN
      ('2014-1-1'::date - '1 month'::interval)::date
      AND ('2014-1-1'::date + '1 month'::interval)::date;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=21.27..2310.97 rows=863 width=14) (actual time=0.384..1.644 rows=1226 loops=1)
   Recheck Cond: ((date >= '2013-12-01'::date) AND (date <= '2014-02-01'::date))
   Heap Blocks: exact=1083
   ->  Bitmap Index Scan on foo_date_idx  (cost=0.00..21.06 rows=863 width=0) (actual time=0.208..0.208 rows=1226 loops=1)
         Index Cond: ((date >= '2013-12-01'::date) AND (date <= '2014-02-01'::date))
 Planning time: 0.104 ms
 Execution time: 1.727 ms
(7 rows)

Compare that to the combined method

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE year = 2013 AND month = 12
    OR ( year = 2014 AND ( month = 1 OR month = 2) );

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=38.79..2999.66 rows=1203 width=14) (actual time=0.664..2.291 rows=1226 loops=1)
   Recheck Cond: (((year = 2013) AND (month = 12)) OR (((year = 2014) AND (month = 1)) OR ((year = 2014) AND (month = 2))))
   Heap Blocks: exact=1083
   ->  BitmapOr  (cost=38.79..38.79 rows=1237 width=0) (actual time=0.479..0.479 rows=0 loops=1)
         ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.64 rows=421 width=0) (actual time=0.112..0.112 rows=402 loops=1)
               Index Cond: ((year = 2013) AND (month = 12))
         ->  BitmapOr  (cost=25.60..25.60 rows=816 width=0) (actual time=0.218..0.218 rows=0 loops=1)
               ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.62 rows=420 width=0) (actual time=0.108..0.108 rows=423 loops=1)
                     Index Cond: ((year = 2014) AND (month = 1))
               ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.38 rows=395 width=0) (actual time=0.108..0.108 rows=401 loops=1)
                     Index Cond: ((year = 2014) AND (month = 2))
 Planning time: 0.256 ms
 Execution time: 2.421 ms
(13 rows)

It's both slower, and uglier.

GROUP BY/ORDER BY

Combined method,

EXPLAIN ANALYZE
  SELECT date, count(*)
  FROM foo
  GROUP BY date
  ORDER BY date;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=20564.75..20570.75 rows=2400 width=4) (actual time=286.749..286.841 rows=2400 loops=1)
   Sort Key: date
   Sort Method: quicksort  Memory: 209kB
   ->  HashAggregate  (cost=20406.00..20430.00 rows=2400 width=4) (actual time=285.978..286.301 rows=2400 loops=1)
         Group Key: date
         ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=4) (actual time=0.012..70.582 rows=1000000 loops=1)
 Planning time: 0.094 ms
 Execution time: 286.971 ms
(8 rows)

And again with the composite method

EXPLAIN ANALYZE
  SELECT year, month, count(*)
  FROM foo
  GROUP BY year, month
  ORDER BY year, month;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23064.75..23070.75 rows=2400 width=4) (actual time=336.826..336.908 rows=2400 loops=1)
   Sort Key: year, month
   Sort Method: quicksort  Memory: 209kB
   ->  HashAggregate  (cost=22906.00..22930.00 rows=2400 width=4) (actual time=335.757..336.060 rows=2400 loops=1)
         Group Key: year, month
         ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=4) (actual time=0.010..70.468 rows=1000000 loops=1)
 Planning time: 0.098 ms
 Execution time: 337.027 ms
(8 rows)

Conclusion

Generally, let the smart people do the hard work. Datemath is hard, my clients don't pay me enough. I used to do these tests. I was hard pressed to ever conclude that I could get better results than date. I stopped trying.

UPDATES

@a_horse_with_no_name suggested for my within one month test WHERE (year, month) between (2013, 12) and (2014,2). In my opinion, while cool that's a more complex query and I'd rather avoid it unless there was a gain. Alas, it was still slower though it's close -- which is more of the take away from this test. It simply doesn't matter much.

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE (year, month) between (2013, 12) and (2014,2);

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=5287.16..15670.20 rows=248852 width=14) (actual time=0.753..2.157 rows=1226 loops=1)
   Recheck Cond: ((ROW(year, month) >= ROW(2013, 12)) AND (ROW(year, month) <= ROW(2014, 2)))
   Heap Blocks: exact=1083
   ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..5224.95 rows=248852 width=0) (actual time=0.550..0.550 rows=1226 loops=1)
         Index Cond: ((ROW(year, month) >= ROW(2013, 12)) AND (ROW(year, month) <= ROW(2014, 2)))
 Planning time: 0.099 ms
 Execution time: 2.249 ms
(7 rows)


MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

Regarding your questions:

first, middle, and last name should not be made primary keys.  Consider placing indexes on these fields to enable better query performance when querying on them.
To query people that match a certain item code, I'm not sure you can do that just yet.  See my comment on the relationship between item and itemCode below
I think that you have the People-to-Item relationship correct.  It's one-to-many, 1 person to many items
I'm not sure whether you will need a view for counties - why do you think so?

Here are some additional thoughts:

Don't go overboard with the addition of indexes.  Though indexes can speed up query performance, they also slow down row inserts.  Only index what you must.
Consider naming all of your tables in the singular form.  So rather than "People", you would have "Person".
I think that the relationship between People and County is backwards.  1 person has one county, but one county has many persons (people).
The relationship between Item and ItemCode seems to be backwards as well.  Are there many items for a given item code?  If so, then what you're showing in the diagram needs to be reversed.  If you reverse this, then you will effectively have a many-to-many relationship between people and itemCode.  Does that sound right to you?

Keep working at it! Becoming a good database designer takes time and lots of practice.  You will learn more with each project you do.


Backing up a single node Percona XtraDB Cluster

Well, I've tested this and the answer is yes. One small thing to keep in mind is that in RHEL 6 default filesystem is EXT4 while in RHEL 7 it is XFS and you will be mounting an EXT4 filesystem to a RHEL7 machine.


If the join is on a single column typed as tinyint, smallint, or integer* and if both columns are constrained to be NOT NULL, the hash function is 'perfect' – meaning there is no chance of a hash collision, and the query processor does not have to check the values again to ensure they really match.

Otherwise, you will see a residual as items in the hash bucket are tested for a match, not just a hash function match.

Your test does not specify NULL or NOT NULL for the columns (a bad practice, by the way), so it appears you are using a database where NULL is the default.

More information in my post Join Performance, Implicit Conversions, and Residuals and Hash Join Execution Internals by Dmitry Pilugin.

* Other qualifying types are bit, smalldatetime, smallmoney, and (var)char(n) for n = 1 and binary collation


How can one fetch subtotal in relation to a group by from an outer query?

In order to get the missing data, first you need to create a table with all the hours.  Then LEFT JOIN from that table to your table.

You may also need a COALESCE(..., 0) to turn NULL into 0.


How Should TCP/IP be configured to allow an external application access to SQL via the same port #?

Not sure if I fully understand the question. But the internal applications should be fine about the port number unless there are also firewalls internally, in which case you just allow the port through there.

For the external access, I'd consider a static port so you can stop doing what you have to do. In an ideal world you'd only really expose your SQL Server to the web server itself externally, so if the firewall can just trust the webservers IP only that is a good thing to do only, instead of just being wide open for that port for any incoming traffic.

But you just ned to set an static port in the network config:

So instead of a 0 for dynamic ports, type a port number in for the TCP Port you wish to use.


Will this trigger always complete before returning results of Insert to client?

DELTE the UPDATE from your trigger

CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
    AFTER INSERT, DELETE
AS
BEGIN

When you delete the UPDATE trigger Case 4 and 3 wouldn't fire the  trigger again.

So only when you INSERT or DELETE, the trigger would run, and update Cust or insert into AuditEntries

With this AFTER UPDATE TRiGGER you would create a loop.

so you should write another triigger only for UPDATES, if you need one.

To answer your question

The trigger would run after the INSER or DELETE of dbo.Cust and will  run completely before starting another command, that is because you define  your trigger  as FOR EACH STATEMENT

As Example

INSERT INTO dbo.Cust VALUE (1,),(2,),(3,),(4,),(5,)
TRIGGER dbo.TR_MyTrigger
INSERT INTO dbo.Cust VALUE (6,),(7,),(8,),(9,),(10,)
TRIGGER dbo.TR_MyTrigger
DELETE dbo.Cust ...
TRIGGER dbo.TR_MyTrigger#

Every step is completed before the next


Your PROCESSLIST

Look back the processlist in your post. I see you are using INSERT DELAYED. I have always felt it was invented as a band-aid to squeeze some write performance out of MyISAM. Note that the first line of the INSERT DELAYED Documentation says

  The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that can be used for certain kinds of tables (such as MyISAM). When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

For those using MySQL 5.6, INSERT DELAYED IS DEPRECATED:

  As of MySQL 5.6.6, INSERT DELAYED is deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead.

You should consider using INSERT instead of INSERT DELAYED. Why? From the Documentation:

  INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it.

What is the permanent and long term solution?

You need to get away from MyISAM. Any kind of writing you do against a MyISAM table will always require a full table lock. Don't get me wrong. There are rare occasions when MyISAM is better than, and could even outperform, InnoDB :

Sep 20, 2011 :Best of MyISAM and InnoDB
May 03, 2012 : Which is faster, InnoDB or MyISAM?

At times, MyISAM could also have a slight edge in read performance over InnoDB. Why? Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT=Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR fields are treated as CHAR. This will reduce access time for data retrieval.

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

In the short term, just ditch INSERT DELAYED. It seems that your write load has increased in overhead beyond what MyISAM can handle.

After doing that, give it some time. If you still see perform issues, it may be time to switch all MyISAM tables to InnoDB (See my old post When to switch from MyISAM to InnoDB?)


Initializing Associative Array in PL/SQL

Updated answer for 18c and beyond
In 18c Oracle has introduced qualified expressions that can also be used to initialize an associative array. Example from the documentation:
DECLARE
  TYPE t_aa IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  v_aa1 t_aa := t_aa(1=>FALSE,
                     2=>TRUE,
                     3=>NULL);
BEGIN
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(1)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(2)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(3)));
END;

See also chapter Qualified Expressions for Associative Arrays from Easy Initializing for Records and Arrays by Steven Feuerstein
Original answer upto 12c
No - there is no a short-cut syntax to initialize an associative array. Unlike varrays and nested tables associative arrays do not have collection constructors.
Instead you should write your own initializer function. See an example from Declaring Associative Array Constants.


It depends on your db architecture. If you have many tables in separate files in the MyISAM, ARIA, or Innodb with files per table you will want a higher open file limit. If you have everything in the Innodb / xtradb in a single tablespace then upping your file limits will not benefit.

Check current system limits

ulimit -a

Temporary limit increase

ulimit -n 65535

Permanent update /etc/security/limits.conf

The mysql open file limit can't exceed system limits.

* soft nofile 65535
* hard nofile 65535


You have two easy options

You can use a MATERIALIZED VIEW
You can also use a TRIGGER that inserts another table.

Both of these will allow you to cache the SUM(). I would go with the MATERIALIZED VIEW unless you need up to date changes all the time.

Before you go down that route though PostgreSQL 9.6 enables parallel seq scans and aggregation which will increase performance. In fact, it's an ideal use case. If you just need this to be faster try setting max_parallel_workers_per_gather


Is it necessary to drop foreign keys before adding a null column to a table and then re-add them?

No it is not necessary.  See this working example below:

use TestDatabase;
go

-- create the parent table (houses the PK)
create table dbo.ParentTable
(
    ParentId int identity(1, 1) not null
        constraint PK_ParentTable_ParentId primary key clustered,
    some_int int not null
);
go

-- insert some dummy data
insert into dbo.ParentTable(some_int)
values (5), (4), (3);
go

-- create the child table (houses the FK)
create table dbo.ChildTable
(
    ChildId int identity(1, 1) not null,
    ParentId int not null
        constraint FK_ChildTable_ParentId foreign key references dbo.ParentTable(ParentId)
);
go

-- insert some dummy data
insert into dbo.ChildTable(ParentId)
values (1), (3);
go

-- view the contents of each table
select *
from dbo.ParentTable;
select *
from dbo.ChildTable;

-- add a nullable int column
alter table dbo.ParentTable
add another_col int null;
go

-- view the new layout
select *
from dbo.ParentTable;
select *
from dbo.ChildTable;

As you can see by the code, I modify dbo.ParentTable by adding a column.  That is a successful operation with the primary key constraint still in place.

As to why your third party software does this, we could guess all day.  But most likely they do it to handle some corner case without first testing whether or not the current operation falls into that.


Biological Sequences of UniProt in PostgreSQL

Exploring the functions at PostBio it looks like they have a couple of ways of encoding. However, given that those extensions are optimized for searching, they make multiple references to simply using the text data type.

According to the documentation:

  Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

Therefore, by putting the table into its own very large tablespace on dedicated hardware should be sufficient for your performance goals. If 1 GB is too small for your data, the int_interval from ProtBio should provide excellent performance:

  A sequence feature corresponds to a triplet (id, orient, ii) where id is a sequence identifier (possibly the primary key for a sequence table), orient is a boolean indicating if the feature is in the same or contrary orientation of the sequence, and ii is the int_interval representing the feature as a subsequence.

Encoding the sequence in sha1 looks to be a very painful way of making a GUID, considering the potential lengths of the sequence.

If the different sequences are unrelated, store them on different tablespaces on different disks for maximum performance.


Start by changing IN ( SELECT max... ) to = ( SELECT max... ).

Change f.Food_name to

( SELECT Food_name FROM Food WHERE Food_therm_id = t.Therm_id ) AS Food_name

and get rid of the LEFT JOIN Food ... (I am unsure whether the LEFT JOIN before JOIN is causing trouble.)

If those suggestions do not speed it up enough, then...

Please provide EXPLAIN SELECT ... so we can see how the query is executed.

What table is Temp_records_id in?  Perhaps e?  (Please qualify all column names.)


Take a look at SQL Server Data Tools (SSDT).  The command-line SqlPackage utility can be scheduled to Extract and Publish schema.  See https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx.  Some operations cannot be done automatically, such as refactoring a single table into two separate ones.  Those operations will require custom scripts for data migrations.

There is also a schema compare tool from RedGate, SQL Compare, that provides similar functionality.  They also provide an automation API product.


If you don't want to specify the schema explicitly, you can choose one of several options, for example:

Modify the default schema in the session, e.g. by issuing the statement SET CURRENT SCHEMA USERX.
Create an alias for the table USERX.MYTABLE in the schema MYUSER.
Before importing data, edit the file db2move.lst and replace all occurrences of USERX with MYUSER.


how to upload a file on a mysql server if access to temp folder is not present

From MySQL documentation, it seems it is not possible, since it is operating systems temporary directory and not it is not configured in MySQL.

  When using LOCAL with LOAD DATA, a copy of the file is created in the
  server's temporary directory. This is not the directory determined by
  the value of tmpdir or slave_load_tmpdir, but rather the operating
  system's temporary directory, and is not configurable in the MySQL
  Server. (Typically the system temporary directory is /tmp on Linux
  systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for
  the copy in this directory can cause the LOAD DATA LOCAL statement to
  fail.

Alternative, you may try with mysqlimport with local parameter, but make sure, you have access to that directory, else you may encounter an error. I hope this answer will help you. Thank you.


MongoDB - Adding a new server to an existing replica set with auth enabled

Just add the new secondary with auth enabled and using identical keyfiles from the start.  If you do that, they will connect, use the keyfile to authenticate, and then sync from scratch - there is no reason to change the original members of the set, just make sure that the new ones you add have the correct (identical) keyfile as the original members.

The other thing to make sure of is that the user you are authenticated as has the correct permissions to do the additions (since turning off auth would solve that issue also).  For reference, the built in role with the relevant permissions is clusterManager for 2.6+ - clusterAdmin should also work, and has the advantage of existing in 2.4.

Per the comments discussion, the caveat with clusterAdmin in 2.4 is that although you have permission to run the relevant commands, you will need to have read privileges on the local database (per the Combined Access section of the 2.4 docs) to use the rs.conf() command.  It's not strictly required but without it you will have to construct the entire config document from scratch for a reconfiguration command which is certainly inconvenient.


Your queries are fine.
To combine multiple row insert/updates/deletes together, use transactions.


Nothing changed; always get connection timeout on alter table in MariaDB

Which version of MariaDB are you upgrading from? It could be that there has been a table or index format change between the versions. Or it could be that the newer version is using the ONLINE alter table and the previous version was not, resulting in the newer version using more of a particular resource. What's the server doing during the time before the timeout? (swapping, writing to disk, etc)

You can try executing the command with the '--old-alter-table' argument.

If you still don't have any luck, the first thing I would try is to rebuild the table. Dump it (good to have a backup in this situation anyway) into an sql file and then reload it into a table with a new, different name. If all goes well drop the old one and rename the new one. Now try the alter statement again.


How to convert columns into rows in MS Access

SELECT "NoOf_A" AS Type_ofCValue, NoOf_A AS [Value] FROM T1

UNION

SELECT "NoOf_B" AS Type_ofCValue, NoOf_B AS [Value] FROM T1

UNION

SELECT "NoOf_C" AS Type_ofCValue, NoOf_C AS [Value] FROM T1

UNION

SELECT "NoOf_D" AS Type_ofCValue, NoOf_D AS [Value] FROM T1;


best way to add Entity id & entity types in database

Make "Entity" an entry in attributes_list and treat it as any other attribute.

Also, do the same for "Category" (whatever your categories might be) and drop Category_details table


To split you can use something like this...

sh.stopBalancer();
var c=0;
db.chunks.find({ns:"database.collection", "shard" : "S0RS0"}).sort({$natural:1}).limit(1400).forEach(function(d)
{
  printjson(d.min);printjson(d.max);
  var tulos=db.adminCommand( { split: d.ns, bounds: [ d.min, d.max ] } );
  if (tulos.ok < 1) {
    print("Split ERROR");
    printjson(tulos);
  }
  print(c);
  c++;
})
sh.startBalancer();


Issue: Why Restore database wizard/GUI for SQL Server 2014 loading old backup set to restore

The Wizard is really just a listing of backup history records in MSDB on that local server. This wizard is not necessary to do backups. In fact, you should run a process to purge backup history on a regular basis. When I do a SQL Server Health Check for a client, I consider it a finding (relatively minor but still a finding) when this history is purged.

I am wondering if you are attempting to restore to a different server which also has that database on it?

Instead, choose Device and browse to your backup file. You'll see the right backupset to restore to inside of that backup file.

The dialog here is sort of a helper if you are restoring on the same server where your SQL Agent backups are taken but if you take backups in different ways, if you clean up history or if you are restoring to a different server, that dialog is likely not so helpful.


Restoring SQL Server Database Command Line Error

You used NORECOVERY which leaves the database ready to receive diff/log restores

You can remove it above, or simply run this

RESTORE DATABASE NewDB WITH RECOVERY

See MSDN RESTORE, look for "[ RECOVERY | NORECOVERY | STANDBY ]" header


Db design for historical option data (~200GB)

The best first strategy is always to keep your transactional data all together in one, normalized data store.  This minimizes the risk of data integrity problems and keeps the amount of code you have to write to a minimum.

Often, people will find that there is some kind of concern around performance.  This leads them to consider denormalization.  The best thing is to wait until your volume testing proves that there is a real problem with performance, rather than assuming that there will be one because your table is "big".

Given that you have a large amount of historical data that you don't use most of the time, it is possible that you may benefit from denormalization.  You really should test this out before assuming it to be the case though.  If it is the case, then one approach which might work well for you is to keep two data stores: (a) one where your current values are kept and (b) one where all values, including current and historical are kept.

If this makes sense to do (for performance reasons), you should build some automation to track all changes to the current data and record them in your history table.  This is best done with triggers in DBMS that support them.

What almost never makes sense is keeping current values in one place and historical values (excluding current values) in another place.  I have never come across a situation where you need to query only historical records, excluding current values.  This means that your queries would have to be twice as complex in order to use current + historical values if you segregate them in this way.

This is what I would recommend to you:

Keep you data in one place if you can (definitely index on date)
Denormalize if you have to, but keep current values in your history table.
Do not use a bit flag for "is_current".  This almost never indexes well.


Storing Social Security Number as DECIMAL(10,9) for Columnstore

I wouldn't use a DECIMAL to store SSNs with rowstore or columnstore tables. The INT data type has the following advantages over DECIMAL:

It's generally faster for SQL Server to work with
It allows bitmap filters to be pushed down to the storage engine
If the column doesn't allow NULLs then it allows for a "perfect hashing function" which doesn't require a probe residual in joins.

If you need better performance for your SSN column I would use an INT with a leading 1. That preserves leading zeroes which seems to be desired. You should store all SSNs in the same format and only cast when necessary. For example if you need to display an SSN as a string to an end user then SELECT RIGHT(CAST(1012345678 AS INT), 9) returns "012345678". Otherwise work with the raw value.

I have no idea what your queries look like, but suppose you have 100k SSNs in a table and you need to check if any of those SSNs appear in a different table that has a billion rows. Here's what the query might look like:

SELECT *
FROM dbo.SSNS_TO_CHECK_3 c
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ALL_SSNS_CCI_INT_LEADING_1 t
    WHERE c.SSN = t.SSN
)
OPTION (MAXDOP 1);

Using an INT column with a leading 1, the above query takes 7 seconds on my machine. With the DECIMAL(10, 9) format that you proposed the query takes 63 seconds my machine. Nearly all of the time is spent on the bitmap operator.

There are some additional considerations with columnstore but they don't matter here. INT is superior in every way that I know of compared to DECIMAL(10, 9).


It seems like Oracle silently restarts my procedure

Not likely. It is possible that your procedure is being run more than you realize? How does the procedure get run?


Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row"

The set column1 = clause requires a single value as the error message indicates.  Sounds like you have a data model problem, but if you need a quick fix, your select query can be modified to return a single value; the following a unique color from table2 if more than one color matches your where clause:

update table1
set column1 = (SELECT distinct color
                FROM table2
                Where table1.id = table2.id
                and rownum = 1
              )

If I had more time, I'd add another level to find the most common color that matched, and returned the first row.

Updated subquery to return first distinct color - thanks to RDFozz for pointing this out.


Since the data is stored in a TIMESTAMP column, you cannot automatically convert it.  You'll have to write some code that tells Oracle what time zone to treat the data as being stored in the GMT time zone and tell it that you want it converted to the session's time zone.  You would get the automatic behavior you are looking for if you used the TIMESTAMP WITH LOCAL TIME ZONE data type instead.  If you used the TIMESTAMP WITH TIME ZONE data type, you would avoid having to tell the database what time zone the timestamp comes from when you write the conversion.

Assuming that your session sets its time zone properly

ALTER SESSION SET time_zone = '-8:00'

for example, would tell Oracle that the current session is 8 hours before GMT (currently the Pacific time zone), if the data is stored in a TIMESTAMP column, you'd need something like

FROM_TZ( ts, 'GMT' ) AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH TIME ZONE column, you'd just need

ts_tz AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH LOCAL TIME ZONE column, you would just select that column.

An example

SQL> ed
Wrote file afiedt.buf

  1  create table foo(
  2    ts timestamp,
  3    ts_tz timestamp with time zone,
  4*   ts_local_tz timestamp with local time zone )
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  insert into foo
  2    values( sys_extract_utc( systimestamp ),
  3            systimestamp,
  4*           systimestamp )
SQL> /

1 row created.

SQL> select from_tz( ts, 'GMT' ) at time zone sessiontimezone ,
  2         ts_tz at time zone sessiontimezone,
  3         ts_local_tz
  4    from foo;

FROM_TZ(TS,'GMT')ATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_TZATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM

In response to the comment below from DylanKlomparens -- you cannot just apply the AT TIME ZONE to a TIMESTAMP and expect to get the proper result so I'm not sure that I understand what you are seeing.  If you just to an AT TIME ZONE on a plain TIMESTAMP, the timestamp is simply treated as having the time zone that you specified.  It doesn't change the time.  It also doesn't depend on the session time zone.  In all these cases, if ts represents a UTC time, the PST version represents the wrong time-- there should be an 8 hour difference between the starting and ending values.

SQL> select ts from foo;

TS
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-0:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-4:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

In response to Vincent's comment-- A TIMESTAMP WITH LOCAL TIME ZONE does adjust automatically to the session's time zone regardless of the server's time zone.  As I adjust my session's time zone, the results change as well.

SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 05.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '-0:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '+4:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
03-MAY-13 01.59.03.171000 AM

Elapsed: 00:00:00.00


MS SQL Server accepts non-SSL connections even with Force Encryption enabled on the server side

Yes, this is correct.
It's also described here Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
And maybe also this discussion helps: force-encryption-on-sql-server-not-working


How to re-sync the Mysql DB if some Masters have different database incase of MySQL Group replication?

I assume you have a group with transactions A and B and your diverged server has some transactions C that are not in the group, so you cannot join it.

In this case you can setup a group member (if in single primary mode it shall be the primary) as a slave of this diverged member using master-slave replication.
This way all the data that was written into the diverged member will be replicated to the group.

I'm assuming 1) all the data on the diverged member has GTIDs that were not reused by the group and 2) your data is safe to replicate to the group, i.e., it is not outdated or has any other data logic issue that only you can know.

When group has this data, you can stop the master slave connection and add the diverged member to the group.


Domain Controller on Multi Subnet Always on Availability Group

Suppose if the Primary site goes down the server on the DR will still be able to used cached credentials.

Assuming the DCs and the rest of your infrastructure will fail over to DR by some other means, your DR SQL Server may not be usable until that happens.  So the AG will give you a  very good Recovery Point Objective (RPO), but the Recovery Time Objective (RTO) won't be any better than a VM failover.


Amazon RDS t2.small as a production server

T2 instances are given cpu time based on a credit system, and t2.micros are have the least overall cpu capability in the instance class. These credits are accumulated at a rate of 6 per hour on a t2.micro, which gives you the ability to burst to 100% of the cpu core for 6 minutes. All other times you are only granted 10% of the core. Furthermore, you will only accumulate credits if your instance is completely idle.

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/t2-instances.html

Depending on the purpose on your production server, and how much downtime is acceptable, ... you can always start with a t2.micro, and upgrade the instance if you see that there is need for more resources. Mostly you can upgrade the instance type in 3-5 minutes. Just power off, upgrade type and power on. I think that's more than a reasonable 'maintenance downtime'.


Is there a way to include an assertion about the expected number of rows?

It is not an assert, but you can just have the query return the empty string (rather than NULL) when there is no match:

... AT TIME ZONE ((SELECT timezone FROM timezone union select '' limit 1))

when invoked with empty timezone query gives:

ERROR:  time zone "" not recognized


Since you are deleting 90% of the rows, I'd recommend copying the rows you need to keep into a new table with the same structure, then use ALTER TABLE ... SWITCH to replace the existing table with the new table, then simply drop the old table.  See this Microsoft Docs page for the syntax.

A simple test-bed, without replication which shows the general principle:

First, we'll create a database for our test:

USE master;
IF (SELECT 1 FROM sys.databases d WHERE d.name = 'SwitchTest') IS NOT NULL
BEGIN
    ALTER DATABASE SwitchTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SwitchTest;
END
CREATE DATABASE SwitchTest;
ALTER DATABASE SwitchTest SET RECOVERY FULL;
BACKUP DATABASE SwitchTest TO DISK = 'NUL:';
GO

Here, we create a couple of tables, with a trigger to move rows from table "A" to "B", approximating your setup.

USE SwitchTest;
GO
CREATE TABLE dbo.A
(
    i int NOT NULL
        CONSTRAINT PK_A
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , d varchar(300) NOT NULL
    , rowdate datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.B
(
    i int NOT NULL
        CONSTRAINT PK_B
        PRIMARY KEY CLUSTERED
    , d varchar(300) NOT NULL
    , rowdate datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

GO
CREATE TRIGGER t_a
ON dbo.A
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE
    FROM dbo.B
    FROM dbo.B b
        INNER JOIN deleted d ON b.i = d.i
    INSERT INTO dbo.B (i, d, rowdate)
    SELECT i.i
        , i.d
        , i.rowdate
    FROM inserted i;
END
GO

Here, we insert 1,000,000 rows into "A", and because of the trigger, those rows will also be inserted into "B".

;WITH src AS (
    SELECT i.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))i(n)
)
INSERT INTO dbo.A (d, rowdate)
SELECT d = CRYPT_GEN_RANDOM(300), DATEADD(SECOND, s6.n + (s5.n * 100000) + (s4.n * 10000) + (s3.n * 1000) + (s2.n * 100) + (s1.n * 10), '2017-01-01T00:00:00.000')
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
    CROSS JOIN src s6;

Clear the transaction log, to avoid running out of room.  DO NOT RUN this in production since it sends the transaction log data to the "NUL" device.

BACKUP LOG SwitchTest TO DISK = 'NUL:';
GO

This code creates a transaction to ensure none of the affected tables can be written to while we're migrating rows:

BEGIN TRANSACTION
EXEC sys.sp_getapplock @Resource = N'TableSwitcher', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = '1000', @DbPrincipal = N'dbo';
BEGIN TRY
    -- create a table to hold the rows we want to keep
    CREATE TABLE dbo.C
    (
        i int NOT NULL
            CONSTRAINT PK_C
            PRIMARY KEY CLUSTERED
        , d varchar(300) NOT NULL
        , rowdate datetime NOT NULL
    ) ON [PRIMARY]
    WITH (DATA_COMPRESSION = PAGE);

    --copy the rows we want to keep into "C"
    INSERT INTO dbo.C (i, d, rowdate)
    SELECT b.i
        , b.d
        , b.rowdate
    FROM dbo.B
    WHERE b.rowdate >= '2017-01-11T10:00:00';

    --truncate the entire "B" table
    TRUNCATE TABLE dbo.B;

    --"switch" table "C" into "B"
    ALTER TABLE dbo.C SWITCH TO dbo.B;

    --drop table "C", since we no longer need it
    DROP TABLE dbo.C;

    --shows the count of rows in "B" which were retained.
    SELECT COUNT(1)
    FROM dbo.B
    WHERE b.rowdate >= '2017-01-11T10:00:00';

   --look for rows in "B" that should no longer exist.
    SELECT COUNT(1)
    FROM dbo.B
    WHERE b.rowdate < '2017-01-11T10:00:00';

    --release the applock and commit the transaction
    EXEC sys.sp_releaseapplock @Resource = N'TableSwitcher', @LockOwner = 'Transaction', @DbPrincipal = N'dbo';
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @message nvarchar(1000) = ERROR_MESSAGE();
    DECLARE @severity int = ERROR_SEVERITY();
    DECLARE @state int = ERROR_STATE();
    RAISERROR (@message, @severity, @state);
    EXEC sys.sp_releaseapplock @Resource = N'TableSwitcher', @LockOwner = 'Transaction', @DbPrincipal = N'dbo';
    ROLLBACK TRANSACTION;
END CATCH
GO

The sp_getapplock and sp_releaseapplock prevent multiple instances of this code running at the same time.  This would be helpful if you enable this code to be re-used through a GUI.

(Note that app locks are only effective if every process accessing the resource implements the same manual resource locking logic explicitly - there is no magic that "locks" the table in the same way that SQL Server automatically locks rows, pages, etc. during an insert/update operation.)

Now, we test the process of inserting rows into "A", to ensure they're inserted into "B" by the trigger.

INSERT INTO dbo.A (d, rowdate)
VALUES ('testRow', GETDATE());

SELECT *
FROM dbo.B
WHERE B.d = 'testRow'

+---------+---------+-------------------------+
|    i    |    d    |         rowdate         |
+---------+---------+-------------------------+
| 1000001 | testRow | 2018-04-13 03:49:53.343 |
+---------+---------+-------------------------+


Attaching a database (created in SQL Server 2012) to an instance of SQL Server 2014 without upgrading it

It is not possible for that upgrade to not happen. There are changes required for the database to be attached and usable on newer versions.

This is under-the-hood stuff like internal references and pointer formats, the exact internal changes from version to version aren't documented. See here for some detail if only to satisfy your curiosity, but I would never risk trying to edit those bits directly.

It is possible to not change the compatibility level of the database, however you would still not be able to take a backup of that database and restore it to another SQL Server 2012 instance.

Because of differences in server/database versions your production and development servers should be on the same versions to really get the full benefit of testing in dev. If you aren't going to upgrade production soon, then you could downgrade dev.


$ perror 13
OS error code  13:  Permission denied

Fix the permissions so that "mysql" can read the file.  Keep in mind that the OS 'root' and the mysql 'root' are not the same.


This appears to be Sql Server.

When you created the temporary table, you gave it a meaningful name:

Create Table #Sales (id int, sales decimal(11,2))

Perhaps you created the temporary table using SELECT INTO #Sales FROM...

That is the name you use to reference the table (#Sales).

Due to the fact that other users in the instance can also create a local temporary table with the exact same name, Sql Server has to use an internal 'generated' name to differentiate between YOUR #Sales and another persons #Sales.

You can observe this by creating a temporary table in one session called #Sales with some columns.  Under SSMS object explorer, you can see that entry under TEMPDB temporary tables.  Now, go to a brand new query window and create the same table.  Refresh the TEMPDB temporary tables node and you'll see the newly added temp table.  But they are completely independent from each other.

Don't worry about the long weird table name under TempDB – just use the name you gave it at creation time.

And speaking of temporary tables, I'd recommend reading the information in Temporary Tables: Local vs. Global so you'll have a better understanding of how temporary tables work and how long they exist.

Additional information can be found on the Internet by searching for

  Difference between local and global temporary table in sql server


‘PRIMARY’ filegroup is full. How do I increase temdb size in SQL Server Express 2017?

You do not need to increase the size of temp db.

  in database ‘mydatabase’ because the ‘PRIMARY’ filegroup is full.

Is telling you it can't write to your primary filegroup is full (c:\path to database\mydatabase.mdf).  Either there is no disk space where the MDF is stored, or the more likely cause is that your Primary Filegroup has no more available room to write into.

You can address this a few different ways

Enable autogrowth if not enabled
If autogrowth is enabled increase the maximum size by MB's, percentage or unlimited

In the GUI

In SSMS:

right click your database
select properties
select files in the database properties window
On the Autogrowth Max Size select your auto growth options.

SQL Alter statement

use master
go
-- Use one of the statements below

-- this statement will allow for unlimited file growth
alter database [mydatabase] modify file ( name ='mydatabase', maxsize = UNLIMITED)

-- this statement will allow the database size to 100MB's.
alter database [mydatabase] modify file ( name ='mydatabase', maxsize = 102400KB)


Privileges required to install PostgreSQL

Try this Starting PostgreSQL in windows without install.
Follow the steps below:

Get binaries for Windows. You can either copy the postgresql folder
(minus the data folder) from an existing PostgreSQL install, or just
download the PostgreSQL binaries from PostgreSQL Windows. Make sure
to choose the zip archive.
Next copy the below batch file into the root of the postgresql folder
For first time use, unremark out the (initdb step)
Run the batch file:

.

@ECHO ON

REM The script sets environment variables helpful for PostgreSQL

@SET PATH="%~dp0\bin";%PATH%

@SET PGDATA=%~dp0\data

@SET PGDATABASE=postgres

@SET PGUSER=postgres

@SET PGPORT=5439

@SET PGLOCALEDIR=%~dp0\share\locale

REM "%~dp0\bin\initdb" -U postgres -A trust

"%~dp0\bin\pg_ctl" -D "%~dp0/data" -l logfile start

ECHO "Click enter to stop"

pause

"%~dp0\bin\pg_ctl" -D "%~dp0/data" stop


Update query for Minimum absolute difference in Access [SQL]

The Update statement you posted doesn't include any join to table_3. This leads to a cross join and all the rows will be set to the same (rather random) value.

Here's the query that should perform the correct update:

UPDATE (Table_1 INNER JOIN Table_2 ON Table_1.Acct_Name = Table_2.Acct_Name)
INNER JOIN Table_3 ON Table_2.Acct_Name = Table_3.Acct_Name
SET Table_3.Final_Value = [Table_2].[Amt_2]
WHERE Abs([Table_1].[Amt]-[Table_2].[Amt_1])<Abs([Table_1].[Amt]-[Table_2].[Amt_2]);


SQL DB design to support user feeds (in application like facebook)

Get rid of ambiguous columns like object_id.  Use log_type to determine what you are querying on.  So something like:

CREATE TABLE logs (
    log_id bigint AUTO_INCREMENT, -- not sure if this is the mysql syntax
    user_id int references users(id),
    post_id bigint references posts(id)
);

A second point is I would suggest changing the friend/unfriend to be immediate so it doesn't hit this table.  This should be only for logging actions to posts, not friendships and the like.  If you must log and propagate, create a separate table for this.


Using Powershell Variables in a File-Stored Query with SQL Server

I expect the sample query you gave is basic, so my offer is basic but I think should work for you even with complex script files that require multiple variables to be passed/replaced.

The key here is you need to have a place holder in your script files that you can easily search for in PowerShell, then just replace it with that value.

An example query:

SELECT * FROM sys.databases WHERE name = 'var1'

In this example I am going to pull that query in from a file and then search for the var1 value, replacing it with the database name I want to query for...

$var = 'MyDatabase'
$query = Get-Content .\Sample.sql | foreach {$_.replace("var1",$var)}

Example usage:


Using existing SQL Server credentials for log shipping reporting purposes

The trick is to avoid the orphaned logins in the first place, not to synchronize them afterwards.

As you probably know, 'orphaned' users are due to the SID associated with the user in the database not matching the SID associated with the login on the server. When a login is created on a server, the value for the SID is either taken from Active Directory (if you are using "integrated" or "windows" logins) or generated by SQL Server (in the case of SQL logins). With SQL Server logins, the value of the SID is generated by SQL Server. The value will vary each time you create a login [hint] unless you take special care.

IOW, if you create a login named "foo" on two servers, each login will have a different SID value. If you create a login named "foo", drop it and create it again, each occurrence of "foo" will have a different SID value.

Strategy #1: use integrated security

With Integrated security, the SID used by SQL Server for the login on the server and the user in the database is taken from the Active Directory login. Because of that, the values can't differ. Changing from SQL logins to integrated logins is usually a hassle, developers generally don't like to spend time on infrastructure work and it's probably a non-starter.

Strategy #2: use the "SID" parameter when creating logins on the destination server.

If you create a login using TSQL code, you can specify a specific value for a SID. You can construct a set of CREATE LOGIN statements (including SIDs and exported passwords) so that the SIDs match on both servers. That way, when you restore the database (via shipped log backups or any other way), the SIDs will match and people can get to the data.

Microsoft provides code that will help you create these CREATE LOGIN statements based on what exists in the source server. You can then copypaste those statements over to the destination server and run them. The name of the procedure is sp_help_revlogin. The code varies slightly between SQL Server versions, but the version for SQL2008R2 is available from Microsoft here

TLDR: I'd recommend using sp_help_revlogin on the source server to create new CREATE LOGIN statements for the logins that have to sync up. (You don't need to do every login, just the ones that have problems.) Then, use those statements to drop and recreate the logins on the destination server. Since you will be dropping logins, you should be aware of any database permissions that those logins already have (maybe there are other databases on the destination server that the developers already have permissions in?) and you should be able to recreate those permissions after you get the login recreated.


Natural join output doesn't appear in table

Let me suggest to read carefully the Postgres docs:

SELECT INTO

SELECT INTO — define a new table from the results of a query

Description

SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

Notes

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

That is:

SELECT INTO creates a new table, the new table must not exists previously.
INSERT INTO requires an existent table.

Example:

CREATE TABLE t1 (id int, name text);
INSERT INTO t1 VALUES
(1, '1111'),(2, '2222'),(3, '3333');

-- Correct, t2 doesn't exists.

SELECT id, name INTO t2 FROM t1;

SELECT id, name FROM t2;

id | name
-: | :---
 1 | 1111
 2 | 2222
 3 | 3333

CREATE TABLE t3 (id int, name text);

-- Error, t3 already exists
SELECT id, name INTO t3 FROM t1;

  ERROR:  relation "t3" already exists

-- Error, t4 doesn't exists
INSERT INTO t4
SELECT id, name FROM t1;

  ERROR:  relation "t4" does not exist
  LINE 1: INSERT INTO t4
                      ^

-- Correct, t3 already exists
INSERT INTO t3
SELECT id, name FROM t1;

SELECT * FROM t3;

id | name
-: | :---
 1 | 1111
 2 | 2222
 3 | 3333

db<>fiddle here


Can a temporary table be shared across sessions?

Local table tables should never be shared across sessions. As you've pointed out, they are only visible to the session that created them and are destroyed when the creating session is terminated, or you drop them.

Global temporary variables are visible to all sessions, but you'd need to define them with the double hash i.e. ##temp, for them to be defined as global.

As for putting a suffix on the table name when creating it, you're wasting your time, as SQL Server does that anyway.

CREATE TABLE #temp (Name VARCHAR(20));

USE tempdb;
GO

SELECT name FROM sys.tables WHERE name LIKE '#temp%';

For me, this returns

#temp_______________________________________________________________________________________________________________00000000004C

However, as Aaron Bertrand pointed out in this answer:

  if you call everything #temp, or #t, or #x, then it's possible that such a table already exists from an outer scope prior to calling the procedure.

In this case, you could be sharing the temp table between a top-level process and a number of different procedures that it calls within that process. This of course could lead to data corruption, depending on how you defined and constructed your temporary table.


Add NOT NULL constraint to large table without table scan

Is there a way to prevent a full table scan during the alter table statement?

At this time there is no supported, safe way to do that with PostgreSQL.

Some kind of ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY would be nice, but nobody's implemented it. Same with the alternative of adding a NOT VALID constraint that still affects new rows, and that you then VALIDATE later - it'd be good, and it's something everyone knows is needed but nobody's had the time or funding to add yet.

In theory you could directly modify the system catalogs to add the constraint if you know it is true and valid. In practice, well, it's generally not a great idea.

So no, there isn't really a way.


Are there any unforseen down sides to using views to abstract away from cryptic table names?

For the use you describe, using synonyms for the tables should be a better solution.  A synonym is basically an alias for an object name, so you wouldn't need to define a SELECT statement as is required for a view.  Basically the only way a synonym can break is if the table is dropped.


You could do it like this instead.

select E.EventID,
       E.EventName,
       count(*) as CountClients,
       sum(datediff(millisecond, I.StartDate, I.EndDate)) as SumDuration
from dbo.Events as E
  cross apply (
              select max(T.StartDate) as StartDate,
                     min(T.EndDate) as EndDate
              from (
                   values(@StartDate, @EndDate),
                         (E.StartDate, dateadd(millisecond, E.Duration, E.StartDate))
                   ) as T(StartDate, EndDate)
              ) as I
where E.StartDate < @EndDate and
      dateadd(millisecond, E.Duration, E.StartDate) > @StartDate
group by E.EventID,
         E.EventName;

Not sure it looks any simpler than what you have but your version is missing out on intervals that starts before @StartDate and ends after @EndDate. I changed the where clause to take care of that and at the same time it will be possible for you to use an index to somewhat limit the number of rows read from the table.

I also changed the way you calculate max StartDateand min EndDate for the sum of duration. Don't think it matter much for performance but to me the code looks neater.

With an index on StartDate having Duration, EventID, EventName as include columns you will get an index seek on E.StartDate < @EndDate and dateadd(millisecond, E.Duration, E.StartDate) > @StartDate as a residual predicate.

create nonclustered index IX on dbo.Events(StartDate)
  include(Duration, EventName, EventID);

This will scan the index from the beginning as long as StartDate is less than @EndDate and it will make wonders for performance if you are looking at early dates and not so much for later dates.

An even better option could be an index on EventID, EventName with StartDate, Duration as include columns to remove the Sort operator from the plan.

create nonclustered index IX on dbo.Events(EventID, EventName)
  include(StartDate, Duration);

Every row in the table has to be looked at but it will be way better if you need to scan almost the entire table anyway.


SSIS Package when executed via agent job or manually on SQL Server does nothing, but shows it completed successfully. What gives?

I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.

[https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]


Replication from MySQL 5.0 to MariaDB 10.2.8 - Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193

I found the solution/workaround. I need to set the variable slave_sql_verify_checksum=0 on the slave (MariaDB 10.2.8). I was trying this by dynamically setting the global variable on the slave but it wasn't working. Then I found bug #MDEV 8505 which indicates that resetting the variable dynamically does not work. Once I set it in the my.cnf and restarted the instance, everything started working as expected. Thankfully this is only a interim solution until we complete the upgrade!


Error during the installation of oracle 11gxe

Don't think MCE are certified to run Oracle 11g XE.

Try

This thread mentions deleting install paths found at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\Folders

key from the registry. You can try that. As always, ensure you have a working backup before you fiddle with the registry.
Reinstalling SP3


Can the Data Export Service in Microsoft Dynamics export data to an Azure Synapse Data Warehouse?

The answer is no. The reason is that an Azure Synapse Data Warehouse does not allow you to create user data types and DES creates data types to use in it's stored procedures that help merge the incremental data into the SQL tables.


how can i use multiple primary keys in postgres ?

You can't. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. You can't have more than one.

You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key for a table.

However, the code you show doesn't produce the error you mentioned:

$ psql -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__
CREATE TABLE
ALTER TABLE
ALTER TABLE
$

At a guess you've actually already defined this table and you're ignoring the previous errors, then showing only the last one. If I re-run this code I get the output:

ERROR:  relation "word" already exists
ALTER TABLE
ERROR:  multiple primary keys for table "word" are not allowed

The real error here is the first one, of course.

I strongly recommend always using -v ON_ERROR_STOP=1 in psql, e.g.:

$ psql -v ON_ERROR_STOP=1 -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__

ERROR:  relation "word" already exists
$

See how it stops at the first error?

(It'd be the default but that'd break backward compatibility).


Advice on T-SQL script, change to use dynamic SQL

Calendar table

As LowlyDBA and Anti-weakpasswords have pointed out in their comments you can find more than one method to generate a calendar table:

Bones of SQL - The Calendar Table
Calendar Tables in T-SQL
Creating a date dimension or calendar table in SQL Server

For the sake of the question I'll generate a (basic) calendar table just to show you how to use it with your data, you'll find examples on-line that use more detailed fields (seconds, day of year, quarters, etc)

CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date,
                                 DAY(@date),
                                 DATEPART(weekday, @date),
                                 DATENAME(weekday, @date));

    SET @date = DATEADD(day, 1, @date);
END

Accordingly with your query, it seems that you're interested on get results for Saturday and Sunday only. In this case you should filter Calendar table by day name or by day of week. Have a look at SET DATEFIRST if you want to filter by day of week.

SELECT * FROM Calendar WHERE cDayName IN ('Saturday', 'Sunday');
GO

cDate               | cDay | cDayOfWeek | cDayName
:------------------ | ---: | ---------: | :-------
06/01/2018 00:00:00 |    6 |          7 | Saturday
07/01/2018 00:00:00 |    7 |          1 | Sunday
13/01/2018 00:00:00 |   13 |          7 | Saturday
14/01/2018 00:00:00 |   14 |          1 | Sunday
20/01/2018 00:00:00 |   20 |          7 | Saturday
21/01/2018 00:00:00 |   21 |          1 | Sunday
27/01/2018 00:00:00 |   27 |          7 | Saturday
28/01/2018 00:00:00 |   28 |          1 | Sunday

Populating CommandLog table with random data

Now let me simulate a CommandLog table and populate it with 1000 random dates between Jan-01 and Jan-31.

CREATE TABLE CommandLog
(
    ObjectName varchar(10),
    IndexName varchar(10),
    IndexType int,
    StartTime datetime,
    EndTime datetime
);

DECLARE @step int = 1;
DECLARE @startDate datetime;

WHILE @step <= 1000
BEGIN
    SET @startDate = DATEADD(minute, RAND() * 59,
                             DATEADD(hour, RAND() * 23,
                                     DATEADD(day, RAND() * 31, '20180101')))

    INSERT INTO CommandLog VALUES('OBJ1',
                                  'INDEX1',
                                  1,
                                  @startDate,
                                  DATEADD(second, RAND() * 59,@startDate));
    SET @step += 1;
END

/* just to check first 10 records */
SELECT TOP 10 * FROM CommandLog;
GO

ObjectName | IndexName | IndexType | StartTime           | EndTime
:--------- | :-------- | --------: | :------------------ | :------------------
OBJ1       | INDEX1    |         1 | 16/01/2018 04:36:00 | 16/01/2018 04:36:43
OBJ1       | INDEX1    |         1 | 06/01/2018 09:11:00 | 06/01/2018 09:11:33
OBJ1       | INDEX1    |         1 | 05/01/2018 14:23:00 | 05/01/2018 14:23:43
OBJ1       | INDEX1    |         1 | 10/01/2018 19:53:00 | 10/01/2018 19:53:11
OBJ1       | INDEX1    |         1 | 14/01/2018 14:31:00 | 14/01/2018 14:31:53
OBJ1       | INDEX1    |         1 | 06/01/2018 18:43:00 | 06/01/2018 18:43:07
OBJ1       | INDEX1    |         1 | 21/01/2018 21:52:00 | 21/01/2018 21:52:41
OBJ1       | INDEX1    |         1 | 28/01/2018 06:51:00 | 28/01/2018 06:51:03
OBJ1       | INDEX1    |         1 | 19/01/2018 08:39:00 | 19/01/2018 08:39:58
OBJ1       | INDEX1    |         1 | 30/01/2018 19:57:00 | 30/01/2018 19:57:50

Formatting data

Ok, so far so good, I've chosen a PIVOT solution in this case, but first I need to format source data to make it possible. Records can be filtered or grouped using the Calendar table.

ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)

/* check first 10 records again */
SELECT     TOP 10
           cl.ObjectName, cl.IndexName, cl.IndexType, cDayName,
           CASE cDayName
                WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
           END FormatDate,
           DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GO

ObjectName | IndexName | IndexType | cDayName | FormatDate    | DiffSeconds
:--------- | :-------- | --------: | :------- | :------------ | ----------:
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           7
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          25
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          40
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           0
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          13
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          52
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          50
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          15

PIVOT query

Well, this is the PIVOT query I'm trying to simulate. Keep in mind that PIVOT queries require a well-know number of columns and you often must generate it dynamically.

SELECT ObjectName, IndexName, IndexType,
      [20-40 06-01],[5-40 07-01],[20-40 13-01],[5-40 14-01],
      [20-40 20-01],[5-40 21-01],[20-40 27-01],[5-40 28-01]
FROM
(
    SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
               CASE cDayName WHEN 'Saturday' THEN '20-40 ' + FORMAT(cDate, 'dd-MM')
                             WHEN 'Sunday'   THEN '5-40 ' + FORMAT(cDate, 'dd-MM')
                             END FormatDate,
               DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
    FROM       CommandLog cl
    INNER JOIN Calendar c
    ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
    AND        cl.StartTime < DATEADD(day, 1, c.cDate)
    WHERE      cDayName IN ('Saturday', 'Sunday')
)src
PIVOT
(
    SUM(DiffSeconds) FOR FormatDate IN ([20-40 06-01],[5-40 07-01],[20-40 13-01],
                                        [5-40 14-01],[20-40 20-01],[5-40 21-01],
                                        [20-40 27-01],[5-40 28-01])
) pvt;
GO

ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

Generating dynamic query

You can use STUFF function to dynamically generate columns names. And then simply concatenate strings.

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ',' +
                   CASE cDayName
                     WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                     WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                   END
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =
    'SELECT ObjectName, IndexName, IndexType, ' + @cols
 + ' FROM
     (
       SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
                  CASE cDayName
                       WHEN ''Saturday'' THEN ''20-40 '' + FORMAT(cDate, ''dd-MM'')
                       WHEN ''Sunday''   THEN ''5-40 '' + FORMAT(cDate, ''dd-MM'')
                  END FormatDate,
                  DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
       FROM       CommandLog cl
       INNER JOIN Calendar c
       ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
       AND        cl.StartTime < DATEADD(day, 1, c.cDate)
       WHERE      cDayName IN (''Saturday'', ''Sunday'')
     )src
    PIVOT
    (
      SUM(DiffSeconds) FOR FormatDate IN (' + @cols + ')'
 + ') pvt';

EXEC sp_executesql @cmd;
GO

Final result

ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

GROUP BY solution

Ok, it's Saturday afternoon and I have nothing else to do than read some interesting book. Having a look at your query I think you could replace all this table joins by a single GROUP BY + SUM(CASE... query using the Calendar table.

Similar to this one:

SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
           SUM(CASE WHEN cDay = 6 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 06-01],
           SUM(CASE WHEN cDay = 7 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 07-01],
           SUM(CASE WHEN cDay = 13 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 13-01],
           SUM(CASE WHEN cDay = 14 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 14-01],
           SUM(CASE WHEN cDay = 20 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 20-01],
           SUM(CASE WHEN cDay = 21 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 21-01],
           SUM(CASE WHEN cDay = 27 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 27-01],
           SUM(CASE WHEN cDay = 28 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 28-01]
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType;
GO

ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

This query can be generated dynamically too:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ', ' + ('SUM(CASE WHEN cDay = '
                                 + FORMAT(cDay, 'D')
                                 + ' THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS '
                                 + CASE cDayName
                                        WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                                        WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                                   END
                                 + ' ')
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =   'SELECT     cl.ObjectName, cl.IndexName, cl.IndexType, '
           + @cols
           + 'FROM       CommandLog cl
              INNER JOIN Calendar c
              ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
              AND        cl.StartTime < DATEADD(day, 1, c.cDate)
              WHERE      cDayName IN (''Saturday'', ''Sunday'')
              GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType';

/* REMOVE Warning: Null value is eliminated by an aggregate or other SET operation. */
SET ANSI_WARNINGS OFF;

EXEC sp_executesql @cmd;
GO

dbfiddle here


Unable to uninstall Sql Server 2012 Express (With SP1)

A month later, I need to test with a full version of SQL locally but remembered I had this pain in the a** to deal with.

TO RESOLVE:

Change SQL compatibility mode to "Windows Vista SP1/SP2". Go to where it was installed. Path belows for x64 version. For 32x it will be in the ~\program Files (86) directory.
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

Launch CMD, run as an Administrator. Navigate to the directory above.
Type command Setupup.exe /action=uninstall
Follow the prompts.
Find your life ~ Shaun T :)


SQL Server Replication and Recovery Model

Even in simple recovery, configuring a database for Transactional Replication prevents log reuse until the Log Reader has consumed the log records and marked them as read.


Using Ola Hallengren's full backup but database option is for simple?

A full backup can be taken from a database in any recovery model (full, simple, or bulk-logged). It is an unfortunate conflict in terms that is somewhat overloaded. You could consider this type of backup to be a single, point-in-time, snapshot of a database that can be restored on its own in entirety.

The recovery model just dictates how logging works and whether log backups are possible.

In simple recovery model, you can take full backups and differential backups, but not log backups. This means you cannot restore to a point-in-time other than the time the any given full backup was taken (or that full + latest subsequent diff). It also means your log space is re-used because, once a transaction is committed, it no longer needs to be in the log in order to be backed up.
In full recovery model, you can take full and differential backups, but you can also take log backups, which allow you to restore to any point in time covered by any log backup taken since the previous full or previous full + previous diff. In this case log space isn't re-used until they are backed up, and most people find this out the hard way after the logs have filled a disk.
In bulk logged recovery model, the situation is essentially the same as full, except that portions of the log are "blocked out" - meaning you can't restore to a point in time that exists in any window where bulk operations that were minimally logged took place.

To summarize:

The choice between full and simple recovery model is impacted by your need to support point-in-time recovery (and is a choice that has nothing to do with whether you use Ola's solution to manage backups).
The choice between full and log backups in Ola's solution is clearer - you must use full if you want any backups at all, and you can additionally specify diff if you want, and log if you have databases in full recovery model.

There is a ton about recovery model and backup/recovery strategies online, I just wanted to clarify the difference between the two uses of the term "full" here.


Can I update only the needed changes in a database?

I would suggest reading this whitepaper on database version control from DBmaestro TeamWork (where I work)

Basically, it discuses how database enforced change management combines the enforcement of version control processes on the database objects with the generation of the deployment script when required based on the version control repository and the structure of the environment at that time.

This approach uses build and deploy on-demand, which means the deploy script is built (generated) when needed, not as part of development. This allows for efficient handling of conflicts, merges, and out-of-process changes.

It handles the challenges by:

Ensuring all database code is covered – structure, business logic written in the database language, reference content, database permissions, and more are managed

Ensuring the version control repository can act as the single source of truth – the enforced change policy prevents anyone (developers, DBAs) using any IDE (even command line) from modifying database objects which were not checked-out before and checked-in after the change. This guarantees that the version control repository will always be in sync with the definition of the object at check-in time.

Ensuring the deployment script being executed is aware of the environment status when the script is executing – building (generating) the deployment script when needed (just before executing) guarantees it is aware of the current environment status.

Ensuring the deployment script handles conflicts and merges them – by using baselines in the analysis, the nature of the change is known and the correct decision whether to promote the change, protect the target (ignore the change), or merge a conflict is easy.

Generating deployment scripts for only relevant changes – the integration with application life-cycle management (ALM) and change management systems (CMS) enables you to assign a reason to the change, as is done in the file-based version control or task management system.

Ensuring the deployment script is aware of the database dependencies – the sophisticated analysis and script generation algorithm ensures the DDLs, DCLs, and DMLs will be executed in the correct order based on the database dependencies, including inter-schema dependencies.


Where is sp_MSrepl_getpublisherinfo in SQL Server?

So after more research I found that sys.sp_MSrepl_getpublisherinfo lives in a database named mssqlsystemresource, which lives in Sql Server's binn folder. This database is invisible to SSMS, but you can copy and attach it as a different name to have full access. This process is described here:

http://www.sqlnewsgroups.net/sqlserver/t21348-browse-mssqlsystemresource-mdf-sql-servers-resource-database.aspx

I have copied that text here, in case that page goes away in the future:

  Giving credit where credit is due, I found the following info I am
  about to  post at the following location:

  http://web.archive.org/web/20150407042809/http://www.aspfaq.com/sql2005/show.asp?id=28

  Since the engine has hooks that hide the mssqlsystemresource database
  from  users, you don't have direct access to it through the GUI.

  However, there is a way around this:

  Determine where the system databse files live, and keep this path handy:

USE master
GO
SELECT REPLACE(filename, 'master.mdf', '')
  FROM sys.sysfiles
 WHERE fileid = 1

  Stop the SQL Server service;
  Copy the files mssqlsystemresource.*df -> resource_copy.*df.  NOTE: do not rename or remove the mssqlsystemresource files!
  Start the SQL Server service;
  Run the following code in a new query window:

EXEC sp_attach_db
  'Resource_Copy',
  '<path from above>resource_copy.mdf',
  '<path from above>resource_copy.ldf'

  Now, the system will no longer identify this database as a "special"  database
  You can easily scan through the names of system objects that might  otherwise be unknown (and browse any code associated with these
  objects) from  a GUI (like SQL Server Management Studio).


Split cases in WHERE clause based on boolean variable

CASE probably isn't the right tool here. But you can use a Boolean expression.

...
WHERE showzerovariable
      AND intfield = 0
       OR NOT showzerovariable
          AND intfield > 0
...


Inserting two tables together with no Id or UNIQUE anything

Should be:

insert into table1
select * from table2
except
select * from table1;

You could also use:

select *
from table2
where Name not in (select Name from table1)

This works too (assuming Name cannot be NULL).

The difference is that the except version delivers all rows that are not the same whereas the not in query delivers all rows where the name does not exist. Up to you to decide if this is what you need.