The constant scans each produce a single in-memory row with no columns. The top compute scalar outputs a single row with 3 columns

Expr1005    Expr1006    Expr1004
----------- ----------- -----------
NULL        NULL        60

The bottom compute scalar outputs a single row with 3 columns

Expr1008    Expr1009    Expr1007
----------- ----------- -----------
NULL        1048576        10

The concatenation operator Unions these 2 rows together and outputs the 3 columns but they are now renamed

Expr1010    Expr1011    Expr1012
----------- ----------- -----------
NULL        NULL        60
NULL        1048576     10

The Expr1012 column is a set of flags used internally to define certain seek properties for the Storage Engine.

The next compute scalar along outputs 2 rows

Expr1010    Expr1011    Expr1012    Expr1013    Expr1014    Expr1015
----------- ----------- ----------- ----------- ----------- -----------
NULL        NULL        60          True        4           16
NULL        1048576     10          False       0           0

The last three columns are defined as follows and are just used for sorting purposes prior to presenting to the Merge Interval Operator

[Expr1013] = Scalar Operator(((4)&[Expr1012]) = (4) AND NULL = [Expr1010]),
[Expr1014] = Scalar Operator((4)&[Expr1012]),
[Expr1015] = Scalar Operator((16)&[Expr1012])

Expr1014 and Expr1015 just test whether certain bits are on in the flag.
Expr1013 appears to return a boolean column true if both the bit for 4 is on and Expr1010 is NULL.

From trying other comparison operators in the query I get these results

+----------+----------+----------+-------------+----+----+---+---+---+---+
| Operator | Expr1010 | Expr1011 | Flags (Dec) |       Flags (Bin)       |
|          |          |          |             | 32 | 16 | 8 | 4 | 2 | 1 |
+----------+----------+----------+-------------+----+----+---+---+---+---+
| >        | 1048576  | NULL     |           6 |  0 |  0 | 0 | 1 | 1 | 0 |
| >=       | 1048576  | NULL     |          22 |  0 |  1 | 0 | 1 | 1 | 0 |
| <=       | NULL     | 1048576  |          42 |  1 |  0 | 1 | 0 | 1 | 0 |
| <        | NULL     | 1048576  |          10 |  0 |  0 | 1 | 0 | 1 | 0 |
| =        | 1048576  | 1048576  |          62 |  1 |  1 | 1 | 1 | 1 | 0 |
| IS NULL  | NULL     | NULL     |          60 |  1 |  1 | 1 | 1 | 0 | 0 |
+----------+----------+----------+-------------+----+----+---+---+---+---+

From which I infer that Bit 4 means "Has start of range" (as opposed to being unbounded) and Bit 16 means the start of the range is inclusive.

This 6 column result set is emitted from the SORT operator sorted by
Expr1013 DESC, Expr1014 ASC, Expr1010 ASC, Expr1015 DESC. Assuming True is represented by 1 and False by 0 the previously represented resultset is already in that order.

Based on my previous assumptions the net effect of this sort is to present the ranges to the merge interval in the following order

 ORDER BY
          HasStartOfRangeAndItIsNullFirst,
          HasUnboundedStartOfRangeFirst,
          StartOfRange,
          StartOfRangeIsInclusiveFirst

The merge interval operator outputs 2 rows

Expr1010    Expr1011    Expr1012
----------- ----------- -----------
NULL        NULL        60
NULL        1048576     10

For each row emitted a range seek is performed

Seek Keys[1]: Start:[dbo].[t].c2 > Scalar Operator([Expr1010]),
               End: [dbo].[t].c2 < Scalar Operator([Expr1011])

So it would appear as though two seeks are performed. One apparently > NULL AND < NULL and one  > NULL AND < 1048576. However the flags that get passed in appear to modify this to IS NULL and < 1048576 respectively. Hopefully @sqlkiwi can clarify this and correct any inaccuracies!

If you change the query slightly to

select *
from t
where
      c2 > 1048576
   or c2 = 0
;

Then the plan looks much simpler with an index seek with multiple seek predicates.

The plan shows Seek Keys

Start: c2 >= 0, End: c2 <= 0,
Start: c2 > 1048576

The explanation for why this simpler plan cannot be used for the case in the OP is given by SQLKiwi in the comments to the earlier linked blog post.

An index seek with multiple predicates cannot mix different types of comparison predicate (ie. Is and Eq in the case in the OP). This is just a current limitation of the product (and is presumably the reason why the equality test in the last query c2 = 0 is implemented using >= and <= rather than just the straightforward equality seek you get for the query c2 = 0 OR c2 = 1048576.


How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL

"last_active_user" sounds like

( SELECT user_login FROM users WHERE ... ORDER BY ... DESC LIMIT 1 )

count(comments.comment) sounds like

( SELECT COUNT(*) FROM abc_comments WHERE ... )

That is, get rid of the joins and replace the aggregate values by subqueries as above.

JOIN explodes the number of rows, then

You need a GROUP BY to get it back in check
COUNT counts too big a number
Items (such as user_login) that are not in GROUP BY end up with unpredictable values.
Etc.

(Yes, MAX(comment_posted), is the same as, but simpler than, MAX(CAST(comments.comment_posted AS CHAR)).)


Assembly 'EDCLR' references assembly 'system.servicemodel, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089 error

There are two problems here:

ServiceModel is not a supported .NET Framework Library for SQLCLR. Meaning, it is not included in SQL Server's CLR host. In such cases, one needs to manually load, via CREATE ASSEMBLY, the Assembly. BUT,
Starting in SQL Server 2012, you cannot reference / load ServiceModel as that version is the first to be linked to CLR v 4.0 in which ServiceModel became a mixed mode Assembly. Only pure MSIL Assemblies are allowed.

You will need to find a different way to accomplish this. One way is to use HttpWebRequest and HttpWebResponse.

This issue has been documented on Stack Overflow here:

(Unfixable) Assembly exists on SQL Server 2014 but it claims it doesn't have it
Register CLR function (WCF based) in SQL Server 2012
SQL CLR - Migration from 2008 R2 to 2012.

More info here:

SQL Server: "CREATE ASSEMBLY for assembly 'Test' failed because assembly 'Test' is malformed or not a pure .NET assembly."
SQL Server custom CLR fails with error "Could not load file or assembly or one of its dependencies. The system cannot find the file specified."


The scope of a variable lasts for the duration of when it is declared to the end of the batch or stored procedure.  So in other words if you have something like this:

declare @someVar varchar(100)
select @someVar = 'this is a test'    -- this is legal

go

select @someVar = 'this will not work'    -- the variable @someVar is out of scope

See the MSDN Reference on T-SQL Variables:

  The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

In other words, you don't explicitly drop variables like you do for other database objects.  (the same goes for table variables as well)


Querying dates with different timezones at db and application level

Check this:

SELECT created_at,
convert_tz(created_at , '+00:00', '-07:00'),
convert_tz(created_at , 'UTC', 'America/tijuana')
FROM
    (SELECT TIMESTAMP '2016-06-01 09:26:50' as created_at FROM DUAL) orders;

If the last column is null , see prerequisites at https://mariadb.com/kb/en/mariadb/convert_tz/


Login failed for user 'domain\username'. (Microsoft SQL Server, Error: 18456)

Scenario - I was connecting my local SQL server by using SQL authentication.
                 I got an error code: 18456.
After digging a bit i found the solution -
SQL server error: 18456 overview - This error occurs when your authentication request is been successfully accepted by SQL server named, but due to some reason SQL server is not able to grant the access to connect.

Reason - This error reveals that SQL server authentication is not enabled in local server.

To enable SQL server authentication, Follow the below steps

Step 1 - Go to SQL server properties by Right click -> Properties.

Step 2 - Enable SQL server authentication mode.

Step 3 - Restart the SQL server.

I hope it will help, Check here for more detail


How to use JSON path wildcards in JSON_QUERY?

Looks like that is not currently supported, and would make a good feedback item.

You can get the names into a resultset with a query like:

select json_value(v.value,'$.Name') Name
from openjson(@json,'$.Dims') v

And from there you can construct a new JSON doc, but if you want an array of primitives, you need to do something like this.

select concat('[',string_agg( quotename(json_value(v.value,'$.Name'), '"'), ', ')  WITHIN GROUP ( ORDER BY [key] )   ,']')
from openjson(@json,'$.Dims') v

I don't think there is a way to get an array of primitive values (ie not objects) with FOR JSON queries.


delete tables on import of dump created by mysqldump

You need to drop and re-create the database. So you need to use some extra / different options when creating the dump file:

mysqldump --login-path=lp --add-drop-database --databases test_db > dump.sql

And import with:

mysql --login-path=kp < dump.sql


If you're talking about Object Explorer within SQL Server Management Studio (SSMS) then it sounds like you've drilled in too far to the tree-view.

Server principals (Logins) can be found under:

Server Name -> Security -> Logins

You got to Users because you did the following:

Server Name -> Databases -> YourDatabaseName -> Security -> Users


The statistics are produced from the values in the columns. As these values are not changed by VACUUM FULL or even by VACUUM, a fresh ANALYZE is not specifically required.

If the database is in a maintenance window, it's a good opportunity to run it, though. Its execution time is likely to be insignificant compared to VACUUM FULL.

By default, a DBA will rely on autovacuum/auto-analyze rather that doing this manually, with the exception of the manual ANALYZE for when a new table that was just bulk-loaded or heavily modified is immediately involved in a query for which a good planning can't be obtained without good statistics.


Find time difference in different rows with a condition

You don't need a pivot here. You can use LAG window function to retrieve the previous status and datetimes for each device, then compare their minutes if it goes from 0 to 0 or from 0 to 1.

IF OBJECT_ID('tempdb..#DeviceStatus') IS NOT NULL
    DROP TABLE #DeviceStatus

CREATE TABLE #DeviceStatus (
    rownum INT IDENTITY,
    device VARCHAR(10),
    datetime datetime,
    status BIT)

INSERT INTO #DeviceStatus (
    device,
    datetime,
    status)
VALUES
    ('device_1',   '2018-06-27 12:10:05.443',      1),
    ('device_1',   '2018-06-27 12:02:31.467',      0),
    ('device_1',   '2018-06-27 11:57:24.260',      1),
    ('device_2',   '2018-06-27 11:57:24.260',      1),
    ('device_2',   '2018-06-26 12:57:24.260',      1),
    ('device_3',   '2018-06-26 09:57:24.260',      1),
    ('device_3',   '2018-06-25 09:57:24.260',      0),
    ('device_3',   '2018-06-26 09:57:24.260',      0),
    ('device_3',   '2018-06-24 09:57:24.260',      1),
    ('device_4',   '2018-06-27 09:57:24.260',      0),
    ('device_4',   '2018-06-26 09:57:24.260',      0)

;WITH LaggedStatus AS
(
    SELECT
        D.*,
        LaggedStatus = LAG(D.status) OVER (PARTITION BY D.device ORDER BY D.datetime ASC),
        LaggedDatetime = LAG(D.Datetime) OVER (PARTITION BY D.device ORDER BY D.datetime ASC)
    FROM
        #DeviceStatus AS D
),
MinutesBroken AS
(
    SELECT
        L.*,
        MinutesBroken = CASE WHEN L.LaggedStatus = 0 THEN DATEDIFF(MINUTE, L.LaggedDatetime, L.datetime) END
    FROM
        LaggedStatus AS L
)
SELECT
    M.device,
    TotalMinutesBroken = ISNULL(SUM(M.MinutesBroken), 0)
FROM
    MinutesBroken AS M
GROUP BY
    M.device

Result:

device      TotalMinutesBroken
device_1    8
device_2    0
device_3    1440
device_4    1440

Note: LAG is for version 2012+, if you have a previous one, you can simluate lag's effect with ROW_NUMBER().


How to only pick the first matched row in a one-to-many relationship?

Use analytics - see documentation or examples here eg

Tables

create table A ( c1 ) as
select 'A1' from dual union
select 'A2' from dual ;

create table B ( c1, c2 ) as
select 'A1', 'B11' from dual union
select 'A1', 'B12' from dual union
select 'A1', 'B13' from dual union
select 'A2', 'B21' from dual union
select 'A2', 'B22' from dual ;

When joining the tables, we get ...

SQL> select *
  2  from A join B on A.c1 = B.c1 ;

C1 C1 C2
-- -- ---
A1 A1 B11
A1 A1 B12
A1 A1 B13
A2 A2 B21
A2 A2 B22

Use FIRST_VALUE (without joining the tables)

select unique
  c1
, first_value( c2 ) over ( partition by c1 order by c2 ) fv
from B ;

C1 FV
-- ---
A1 B11
A2 B21


In general, it is a bad idea to have long clustered index keys. For that it is not important if the key is made up of several short columns or few long columns. This makes the maintenance of every index as well as the read access of every index on that table a lot more expensive that it has to be. As the PK is by default enforced by a clustered index, you most likely have a very long clustered index key.

Also in general it is a good idea to have you clustered index key be monotonously increasing. As your column is valued based on NEWID it will distribute new rows all over the table causing fragmentation. The extra long key will make this even worse.

And finally, in general it is a good idea to not spend too much time tuning performance preemptively. If you do new development follow best practices (like the two recommendations above). With existing software, fix only the stuff that is causing a headache.

Now it looks like you have that headache already. Therefore I would start by migrating those primary keys to either identity columns (preferred) or to uniqueidentifier columns that default to newsequentialid().

But this is a difficult change to do if you can't take downtime. I that case I would start by adding new indexes to support you current strongest headache (read: stored procedure). Than work on cleaning up the mess afterwards.


Trouble understanding INITRANS in oracle

As always: it depends.
However in most cases the default values are adequate.

The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50?

INITRANS specifies the minimum number of transaction slots (ITL slots) in a database block. Of course, the number of ITL slots can go higher if needed, the database manages that. The maximum number depends on the database block size (as the ITL entries are not allowed to occupy 50% of more of a database block, and 1 ITL entry takes 24 bytes), and MAXTRANS (=255 from version 10.2).

So, if:

all the new data end up in the same database block (not necessarily a table, a common example is a PK index on an ID column, where the ID values come from a sequence)
changes are not committed immediately (or quick
enough), so locks will be held for a long time, and all the 50 sessions will hold the locks at the same time

then yes, the database block will need the 50 ITL slots. But even then, most likely you will not need to set INITRANS to 50, because the database will manage this automatically, and 50 is well below the actual limit in a standard 8K database block.

This may not be true for DELETE, UPDATE, SELECT ... FOR UPDATE, as when they happen, the database block is most likely full and the number of ITL slots may not increase in that case. Imagine a full database block that stores 50 rows and has 2 ITL slots, and 50 concurrent transactions want to lock 1 row each with SELECT ... FOR UPDATE. But with INSERT, that uses empty database blocks or continues writing to existing ones with free space in them, it should not be a problem.

Note that there are many ifs, mays and questions here, and as I said, in most of the cases, the default values are adequate. Yes, I can create an artificial example that works as above, but real scenarios are fortunately different.


I don't see any way you could normalize this further. In fact unless each property could have more than one value I would denormalize a bit by combining the Property and PropertyString tables.

select I.ImageID, I.Data
  from Image I
  join ImageType IT on I.ImageTypeId = IT.ImageTypeId
  join Property P1 on I.ImageID = P1.ImageID
  join PorpertyType PT1 on P1.PropertyTypeId = PT1.PropertyTypeId
  join Property P2 on I.ImageID = P2.ImageID
  join PorpertyType PT2 on P2.PropertyTypeId = PT2.PropertyTypeId
 where IT.IsoCode = @ImageCode
   and PT1.Name = @PropertyName1 and P1.Value = @PropertyValue1
   and PT2.Name = @PropertyName2 and P2.Value = @PropertyValue2

I haven't tested it, but my guy says that this will perform better than having separate subqueries per property you are checking. If you're really want to maintain the PropertyString table you still could include it in this join.


Need postgres statement that returns non unique records even if I pass same ids in array

Join against the array:

select tt.*
from tennis_ticket tt
  join unnest(array[3,3]) x(id) on x.id = tt.id;

or use a values clause

select tt.*
from tennis_ticket tt
  join (values (3), (3)) as x(id) on x.id = tt.id;


SQL Server: how to determine a column algorithm encryption

You can use sys.columns system catalog view.

Details here:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact-sql


join same table twice and get the results of rows

How about a "self join":

SELECT a.object_id
    FROM wp_term_relationships AS a
    JOIN wp_term_relationships AS b  ON a.object_id = b.object_id
    WHERE a.term_taxonomy_id = 44
      AND b.term_taxonomy_id =  8;


innobackupex is failing while exporting backup

Try with option: --safe-slave-backup

This is for consistent backup on the slave machines.
This option basically stops the slave SQL activity dueing the backup
of individual DBs.
You will see slave SQL thread error and initilization messages
regularly during the backup.

Hope this helps. Please let us know back on the results...


It seem that your event scheduler is not running causing the problem of event not getting executed. use following command to check

show global variables like 'event_scheduler';

if you get that it is off, you can do if you are in MySQL 5.6 or higher

 set global event_scheduler=1;

or add the following line in cnf file to make this change permanent and in versions <5.6

 event_scheduler=ON

The given code would work once you turn on the event scheduler using above approach.

Hope it helps.


Send fewer rows to the client.

Send less data per row to the client.

Compress the data from the server to the client.

Note that there seems to be a raging argument on the web as to the meaning of "Sending data".  Some day that it might actually mean the prior step.


Third Normal Form: Composite PRIMARY KEY vs System-Generated Surrogate (IDENTITY)

According to your description of the case under consideration, it seems that (a) appending a column to hold system-assigned surrogates* (surrogates for brevity) via an IDENTITY property to the History table is (b) superfluous.

A column retaining surrogates can be useful when a tentative natural PRIMARY KEY (PK) constraint has the potential of being referenced from FOREIGN KEY (FK) constraint definitions, but such tentative PK constraint is

wide, i.e., it encompasses a relatively large number of columns and/or
the values to be contained in the potential PK column(s) are physically heavy in terms of bytes (evidently, heavy values behave slower than light ones because of, e.g., larger memory and disk space usage concerning reading and writing processes, with the respective impact on the response speed of the logical data manipulation operations).

As a consequence, having FK constraints that point to a single column that (1) would somewhat work as a “substitute” for a natural KEY like the one described above and (2) would keep lighter values, is a possibility worth to assess. Regarding the History table at hand, it is neither logically wide nor is supported by a heavy physical scaffolding.

Besides, attaching a column for surrogates would necessarily make the table in question wider due to the incorporation of a tentatively needless element (and, perhaps?, of the speculative corresponding physical INDEX), so it might end up behaving slower. Hence, considering its effect on the overall performance of the database, it may not provide any pragmatic value regardless.

A situation in which this peculiar class of column can sometimes be of help is when sticked to tables that stand for entity types that are indpendent† but, as you know, even in said kind of situation each particular table demands (i) individual and (ii) contextual analyses to determine whether sticking a column of that sort is convenient or not (and I guess that is why you asked the question).

At the logical level, a column that maintains surrogates, of course, neither captures more business domain meaning (which hinders readability and interpretation of result sets) nor protects real row uniqueness (a column that keeps meaningless values is an additional non-data artefact, so you still have to ensure row uniqueness of the genuine data elements). See this outstanding Stack Overflow answer by @PerformanceDBA for evidence about these aspects.

However, you “could” add it to the History table, “use” it as the PK and, at the same time, declare the combination of columns (DateCreated, SecurityId, FieldId) as an ALTERNATE KEY‡ (AK) by means of (a) the configuration of a composite UNIQUE constraint and (b) the specification of every one of the columns in said combination as non-NULLable (arrangement that would actually guarantee row uniqueness with regard to the true data elements). In this way, the non-key column named Value would be functionally dependent on both

the column that retains surrogates, “utilized” as PK, and
the meaningful and natural AK made up of the three relevant columns;

thus, the table would still comply with 3NF§ anyway, although the usage of the column with surrogates would not be providing benefits, therefore it would be excessive.

Practical examples of why it would be excessive are:

The History table would mostly be queried by virtue of SELECT statements that include one or more of the columns that are part of the natural AK as conditions in the WHERE clause (while a column enclosing surrogates would hardly be included, because they are meaningles to end users).
INSERT operations INTO the table would be slowed down as a result of the integration of an extra column.

It is important to note that, at the physical level, establishing a good INDEXing strategy to serve the composite PK definition is paramount to optimize the functioning of the pertinent processes, hence some testing sessions should be carried out with respect to, e.g., the determination of the suitable order of the columns assisted by the applicable INDEX(es) configurations.

Endnotes

* System-assigned surrogates were evaluated in the 1979 paper entitled Extending the Database Relational Model to Capture More Meaning, which was written by Dr. Edgar Frank Codd, the creator of the relational paradigm. In said paper, it is stipulated that surrogate values must not be displayed to database users, even though in most databases I have seen that rule is not followed. The present answer is based on the assumption that, in the scenario under deliberation, surrogates are exposed just like ordinary business context values.

† At the conceptual level, an entity type (or entity prototype) is independent when every one of its occurrences (i.e., rows, when retained in a SQL table) is uniquely identified by the values of one or more of its own attributes (i.e., columns) alone. A dependent one is that which requires the use of the value(s) of one (or more) attribute(s) that belong to other entity types so that each of its instances can be uniquely identified.

‡ An ALTERNATE KEY is a column (or a combination of columns) that holds values that uniquely identify each row of the pertinent table but was not chosen as the PRIMARY KEY; each table can have zero, one or more ALTERNATE KEYS.

§ To analyze why this is so, see the genuine definitions of normal form and normalization in A Relational Model of Data for Large Shared Data Banks (1970) and of the successive normal forms (as suggested in Joel Brown’s subsequent answer) in Further Normalization of the Data Base Relational Model (1971), both works —naturally— by Dr. E. F. Codd. It is worth to mention that the original relational model (1970) does not include surrogates.


Keeping data in old Database (SQL 2008) identical to new Database (SQL 2016)?

Converted from comments:

  According to Replication Backward Compatibility, Transactional Replication will not work between a 2016 Publisher and a 2008 Subscriber, but Merge Replication would be supported. Check out Merge Replication to see if that will suit your needs. – Scott Hodgin

  Or Upgrade to 2014 and it will be backward compatible to 2008 for transactional replication. Once You have a steady ship, you can move ahead to later versions if needed. – Ramakant Dadhichi


Take one replica offline from an availability group

The database replica to be taken offline can be primary or secondary.

The database taken offline should always be the secondary.

  The database replica has to be take offline and online via transact SQL

That's possible.

  When the database replica is offline it should not be possible for it to execute requests. All existing connections must be moved to the other database replica or fail, so that the client can reconnect using the IP listener to the other replica.

Basically here's how it would go:

If the database replica that needs to be taken offline is a primary, check for the current secondary to be synchronized.
Set the database to be "taken down" as a non-readable secondary.
If the database replica was a primary, failover.
Do any other items you'd like here, such as removing it from the AG, etc.


Find weighted random row. Or split 1 row into many based on column value

Instead of proliferating the number of rows, let's add a cumulative total:

| id  | item   | count | cumm_tot
| --- | ------ | ----- |
| 1   | item a | 3     |  3
| 2   | item b | 2     |  5
| 3   | item c | 4     |  9

Then get the last number, 9, for use below.
Index the new column.
Fetch random item thus:

WHERE    cumm_tot > 9 * RAND()
ORDER BY cumm_tot
LIMIT 1

To set the column and get the 9, perform this task:

SET @c := 0;
UPDATE tbl
    SET cumm_tot = (@c := @c + `count`)
    ORDER BY id;
-- @c will now be the total (9)

Whenever you add items or change the weights, rerun the two statements above.

(This code should work on any version of MySQL/MariaDB.  There may be a "window function" that works better in new versions.)


but which is more accurate / relevant for more number of records ?

Neither and both. The optimizer is free to rewrite subqueries as joins.

For clarity, since you don't need any columns from Table2, I'd go with a third option and use EXISTS.

This is only a benefit for the reader in this case. In other cases, writing a join may result in unwanted row duplication, which will typically lead someone to add a DISTINCT or GROUP BY to remove them, which are unnecessary with EXISTS or IN...

Of course, it's different with NOT IN. If NULLs exist in the relation, you'll get an empty result set. This can be quite confusing to beginning query writers.

As a style choice, I go with EXISTS or NOT EXISTS when I'm referencing a subquery, and IN/NOT IN when I have a list of literal values.

Hope this helps!


sp_dts_getfolder isn't in my MSDB database, any ideas why?

Why I think it can be something about the maintenance plans is because on
msdn it states:

  If the account that the SQL Server Agent runs under on the target
  server (TSX) is not a member of the sysadmin fixed server role on the
  master server (MSX), to import a distributed maintenance plan, the
  account must be granted the following permission on the master server.

  .......

  If this permission is not
  granted, the following error will appear in the SQL Server Agent log
  on the target server when the multiserver SQL Server Agent Job that
  runs the maintenance plan is refreshed by the target server:

  "Failed to import Maintenance Plan  from <
  NameOfMasterServer>:
  Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: While trying to
  find a folder on SQL an OLE DB error was encountered with error code
  0x80040E09 (The EXECUTE permission was denied on the object
  'sp_dts_getfolder', database 'msdb', schema 'dbo'.)"

  After these permissions are changed, each existing distributed
  maintenance plan must be reloaded on the target server. Make a small
  change to each plan and save. This will force the target server to
  reload the maintenance plan from the master server. By default, the
  target server will update jobs from the master server every 60
  seconds. There will be a minimum delay of 120 seconds after you save
  the maintenance plan before it appears and can be run on the target
  server.

Here is the link to read about it

You should also looked at this kb. This is a fix from Microsoft regarding this problem.

After some investigation I found that going from sql 2005 to sql 2008 there are some changes in the system store procedures

  Now the twist in the story is, since SSIS 2005 has grown up from DTS,
  the system tables and system stored procedures use a naming convention
  like "dts" in its name as you can see in first column of table below.
  With SSIS 2008, the SSIS team has standardize the naming convention
  and uses "ssis" in its name as you can see in the second column of the
  table below. So if you are using these system tables or system stored
  procedure in your code and upgrading to SSIS 2008, your code will
  break unless you change your code to accommodate this new naming
  convention.

So the [msdb].[dbo].[sp_dts_getfolder] is actually changed to [msdb].[dbo].[sp_ssis_getfolder].

You can find all of the other procedure changed and some more information in the article Best Practice #17 - Names of system tables and procedures have changed between SSIS 2005 and SSIS 2008


Restoring dump(backup): ERROR 1022 (23000) at line 44: Can't write; duplicate key in table

In the block you have added to your question it is clear that you create a table with ENGINE=ARCHIVE. The Archive storage engine in MariaDB (and the other flavors of the ecosystem) does not support indexes.

The error you get is because of a UNIQUE/PRIMARY index reporting a duplicate. This means you are not restoring your backup to an ARCHIVE table.

I would assume that your backup includes the schema, and that it creates the table as InnoDB (or another storage engine that supports indexes). Can you make sure that the table is still the ARCHIVE storage engine after trying to import the dump?

Remove this part from your dump, or create it as ARCHIVE without specifying any indexes and importing the data should work.

Alternatively, you can remove the creation of indexes from your dump file and later on changing it to the storage engine you want it to be.


MySQL - INSERT ... SELECT .. FROM .. - Single transaction or transaction per row?

It will all fail or it will all be succesfull meaning one giant transaction.


SQL Server 2008R2 Cluster: Spread tempdb over several files AND shrink original single file

Don't know if anyone will read this, but... I performed the rollover this weekend without problems. All 8 tempdb-files are now 512MB. "Case closed"


Cannot change logical file name: The logical file name "PROD1" is already in use. Choose a different name

Try this:

What you have to do is, first set it to another name.

ALTER DATABASE [PROD]
MODIFY FILE (NAME=N'PROD0', NEWNAME=N'PROD1Temp')
GO

The file name 'PROD1Temp' has been set.

Then change it back to PROD1 so that you can achieve your target.

ALTER DATABASE [PROD]
MODIFY FILE (NAME=N'PROD1Temp', NEWNAME=N'PROD1')
GO

Some helpful links :

'Logical file name "MyDB" is already in use' Even though the database doesn't exist

https://stackoverflow.com/questions/7534664/sql-server-script-error-database-is-already-in-use


SQL profiler how to filter with EventClass column

Event Classes cannot be filtered, instead you select which Event Classes to include in your trace and then filter events based on the properties of each Event Class.
For example, to exclude "RPC:Completed" from your trace, on the Events Selection tab, expand Stored Procedures and uncheck the box on the left-hand side of RPC:Completed.
To only "RPC:Completed" for a particular stored procedure, you click Column Filters, locate ObjectName and add your procedure name under Like.


Derived classes with same base class and class specific ids

You can simply inherit the primary key from the Animals table in the Fish and Cattle table.

This example uses syntax that is SQL Server specific, however you should be able to convert this into SQLLite syntax:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);

This design prevents the need for surrogate key in the Fish table, while still ensuring uniqueness for the rows in that table by way of the unique constraint UQ_Fish.

If you want to see all the details about Fish, you'd use a query similar to:

SELECT Animals.AnimalID
    , Animals.DateCreated
    , Fish.FinCount
FROM dbo.Animals
    INNER JOIN dbo.Fish ON Animals.AnimalID = Fish.AnimalID;

Alternatively, if you are constrained by the classes built by the app, you can simply create the following structure:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    FishID INT NOT NULL
        CONSTRAINT PK_Fish
        PRIMARY KEY CLUSTERED
    , AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);


How to narrow down records in SQL based on conditions

The rank 1 customers and the other customers behave differently, so just use different queries.
The last two logic conditions can be computed with a simple ORDER BY per customer; to relate that order back to the entire row requires a window function:

WITH Rank1Customers AS (
  SELECT DISTINCT Customer
  FROM MyTable
  WHERE Rank = 1
)

SELECT Customer, Rank, Joining_date, Salary
FROM MyTable
WHERE Customer IN (TABLE Rank1Customers)
  AND Rank = 1

UNION ALL

SELECT Customer, Rank, Joining_date, Salary
FROM (SELECT *,
             row_number() OVER (PARTITION BY Customer
                                ORDER BY Rank DESC, Salary ASC) AS OrderNr
      FROM MyTable
      WHERE Customer NOT IN (TABLE Rank1Customers)
     ) AS _
WHERE OrderNr = 1;

(SQL Fiddle)


What type of rows does `SELECT my_table FROM my_table` return?

It's a named composite type: https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

When you create a table, a TYPE with the same name is created.

A row of that table is of that type.


row-based replication with slave triggers

You can't.
Triggers do not fire on the slave in row-based replication, even if they are defined, because the rows in the other tables affected by the triggers on the master are replicated as row events, and if the triggers were allowed to fire on the slave, that would not work.

“With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.”
— http://dev.mysql.com/doc/refman/5.6/en/replication-features-triggers.html

If you have entire tables that need to be eliminated on the slave, you can alter those tables to use the BLACKHOLE storage engine, which happily accepts the insert queries from replication and then discards the data, returning no data when you SELECT from them... though this requires some attention to detail down the road since an ALTER TABLE on the master that specifies a storage engine will alter the table on the slave as well.
If you are in a position to rearrange column order on tables, MySQL supports the slave having fewer columns than the master in row-based mode, as long as the columns they do have are all identical by definition and ordinal position in the table, as I discussed here.  If moving the sensitive columns to the right side of the table is practical, you can then ALTER TABLE ... DROP COLUMN ... on the slave.
But this also requires attention down the road.
Probably the simplest solution is to use permissions on views that you essentially create for the purpose of enforcing the access rights to the underlying tables.  Views, by default, are executed with the permissions of the user who defined them, not the user who invokes them... so a view can allow a user to access columns in a table that aren't otherwise accessible to that user, if the table and columns are accessible to the DEFINER user.

“...views can have a SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If the SQL SECURITY characteristic is omitted, the default is definer context.”
“A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute.”
— http://dev.mysql.com/doc/refman/5.6/en/stored-programs-security.html

If you create a view with ALGORITHM=MERGE then queries written against that view will be handled by the server by rewriting the query internally based on the view definition, so there's no significant overhead.  As long as you are simply selecting all or some of the columns from each table, and not using functions in the views that are incompatible with MERGE then this seems like the optimum solution, since the analytics user would have no access at all to the underlying tables, and not necessarily even be aware of their presence on the server if the permissions are set correctly.  Technically speaking, as long as there are no conflicts in naming, you could even create these views directly on the slave, since MySQL replication has no problem with spurious structures on the slave as long as the replication SQL thread never has any reason to notice them.

If none of these are suitable alternatives, is one more alternative approach that can be used if part of the problem is that you can't even allow the sensitive data to be sent to or stored on the other server, because of its physical location or other security concerns... but it requires a third MySQL server in the middle (between existing master and slave) and is somewhat fragile because every ALTER TABLE operation occurring on the master will cause the intermediate machine's replication SQL thread to pause until you manually reconfigure it to understand the new table definition.  On a stable schema, it works pretty much flawlessly (I've had one in production for over a year) but it's still a delicate setup, so I'll spare you the details on this option for the moment.


WITH
cte1 AS ( SELECT t1.id_item,
                 t1."group",
                 t2.id_color,
                 t2.color,
                 t2.subcolor
          FROM tb_items t1
          NATURAL JOIN tb_colors t2 ),
cte2 AS ( SELECT color, COUNT(DISTINCT subcolor) subcolors
          FROM cte1
          GROUP BY color )
SELECT cte1.id_item,
       cte1."group",
       CASE cte2.subcolors
       WHEN 1 THEN cte1.color
              ELSE cte1.color || '-' || ROW_NUMBER() OVER (PARTITION BY cte1.color
                                                           ORDER BY tb_colors.subcolor)
       END color
FROM tb_colors
NATURAL JOIN cte1
NATURAL JOIN cte2
ORDER BY cte1.id_item;

fiddle


Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change?

FORMAT returns nvarchar which has a higher data type precedence than the compared varchar column. In addition the imprecise row count estimate, the implicit conversion of the compared varchar column to nvarchar will prevent indexes on that column from be used efficiently

Try casting the FORMAT result to varchar.


How to verify the index on a table was created "Concurrently"

You would know that the index was created concurrently by observing the level of the lock on held on the table while the index was being built.

Once the index creation is done with, it is just an ordinary index.  Building an index concurrently is not an ongoing property of the index.


Turn off auto qualification of table names when creating a view

No. There is no way. I think there is a slight misconception of how things work.
Unlike functions, which store the function body as string (late binding!), a view parses the query and does not store the original text at all. All identifiers are resolved according to the current search_path and only their internal OIDs are saved (early binding!). That's also why SELECT * always resolves to the list of columns at the point in time when the VIEW was created.
What you see in pgAdmin or other clients when looking at the definition of a VIEW is a re-engineered SQL string. public.schedule will always be public.schedule and sally.schedule will always be sally.schedule (unless you rename table or schema). The (schema-qualified) names that are displayed are chosen such that the identifier is unambiguous with regard to the current search path.
This also shows when you change the names of columns, tables or schemas. The view keeps just working, because it does not depend on these attributions. The display of the definition is adapted dynamically.
Possible solution
If you want late binding you could use a function instead of a view and default to the current search path (one could SET the search path for the scope of a function to avoid that effect precisely.)
So instead of:
CREATE VIEW v_mechanics_schedule AS
SELECT * FROM schedule s  -- use aliases for short qualified column names in display
LEFT     JOIN mechanics m USING (mechanic_id);

Without schema-qualification, table names are resolved according to the current search_path at creation time.
You could:
CREATE FUNCTION f_mechanics_schedule()
  RETURNS TABLE (...) AS  -- you have to spell out columns
$func$
SELECT * FROM schedule s  -- this is typically error prone
LEFT     JOIN mechanics m USING(mechanic_id);
$func$  LANGUAGE SQL

Now, table names are resolved according to the search_path at execution time. Note that the same does not apply to the return type ( RETURNS TABLE (...)), which is determined at creation time. So this trick only works for compatible tables - the query has to return the same list of columns, only data types and the number of columns matter, column names are ignored (only the names in the definition of the return type are visible outside the function).
That's also why SELECT * to return values in the function body is typically unreliable. If you change the definition of underlying tables, the function breaks: The defined return type remains the same, but SELECT * resolves to a different column list ...
OTOH, if you declare a function as RETURNS SETOF some_table (different syntax variant), a functional dependency is registered and the return type of the function is bound to the table definition, so SELECT * FROM some_table would make more sense.
This is also the reason why it is unsafe to have SECURITY DEFINER functions without fixing the search_path: Any user with the TEMP privilege can create a temp table that hides other tables because pg_temp comes first in the search path by default ...
Related:

Permission for sequence in another schema

You really need to understand the underlying mechanisms to play with this.


GROUPING SETS returning unexpected results with calculated expression

I'm going to use a simpler example where it is clear to see what the expected results are.

CREATE TABLE Queen
(
   FirstName        VARCHAR(7),
   Surname          VARCHAR(7)
);

INSERT INTO Queen
    (FirstName, Surname)
VALUES
    ('Brian',   'May'),
    ('Freddie', 'Mercury'),
    ('John',    'Deacon'),
    ('Roger',   'Taylor')
;

Query 1

SELECT Surname,
       NULL AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)

Query 1 Results

+---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | NULL           |     1 |
| May     | NULL           |     1 |
| Mercury | NULL           |     1 |
| Taylor  | NULL           |     1 |
| NULL    | D              |     1 |
| NULL    | M              |     2 |
| NULL    | T              |     1 |
+---------+----------------+-------+

Query 2

SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )
ORDER BY SurnameInitial, Surname

Query 2 Results

Despite the ORDER BY SurnameInitial and the fact that NULL sorts first in SQL Server the rows with SurnameInitial as NULL are ordered last.

+---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | D              |     1 |
| May     | M              |     1 |
| Mercury | M              |     1 |
| Taylor  | T              |     1 |
| NULL    | NULL           |     1 |
| NULL    | NULL           |     2 |
| NULL    | NULL           |     1 |
+---------+----------------+-------+

Query 1 and 2 should return the same results. The problem is that SQL Server decides to treat it like the following SQL

WITH GrpSets AS
(
SELECT Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)
)
SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       Count
FROM GrpSets

This just looks like a bug to me (trace flag 8605 shows that the damage is already done in the initial query tree representation). BUG REPORT.

Query 3

SELECT Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(FirstName,1)) )

Query 3 Results

+---------+------------------+-------+
| Surname | FirstNameInitial | Count |
+---------+------------------+-------+
| NULL    | B                |     1 |
| NULL    | F                |     1 |
| NULL    | J                |     1 |
| NULL    | R                |     1 |
| Deacon  | NULL             |     1 |
| May     | NULL             |     1 |
| Mercury | NULL             |     1 |
| Taylor  | NULL             |     1 |
+---------+------------------+-------+

Query3 does not meet the problematic pattern of grouping on a column and an expression referencing that column. It wouldn't even be possible for the same issue to occur here anyway because the grouping sets part is equivalent to

SELECT Surname,
       NULL AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(FirstName,1)

This does not pass out the entire FirstName column upstream (or even have a guaranteed unique  FirstName column that could be passed out) so it isn't possible for the LEFT(FirstName,1) expression to be calculated on top of that.

For the same reason you don't see the issue with (RN10), (SUBSTRING(RN,3,99)).

@i-one reasons in the comments that it is likely

  a bug in normalization (algebrization). It has logic that finds a
  match for non-aggregated columns and expressions in the SELECT list
  within members of GROUP BY. The same logic seemingly allows us to
  write for example

SELECT Surname, LEFT(Surname, 1), COUNT(*)
FROM   Queen
GROUP BY Surname

without having to explicitly add the calculated expression as below

GROUP BY Surname, LEFT(Surname, 1)

Or another example would be

SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       LEFT(Surname,2) AS SurnamePrefix,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )

In this case the LEFT(Surname,2) is allowed and the only way of computing it would be to do it in the manner that is problematic for the LEFT(Surname,1) case.


Changing IP address from existing Mariadb10.1 Galera Cluster

The nodes are not registered by their IP addresses, but by a UUID and server ID. Just ensure that wsrep_cluster_address reflects the nodes properly, and do a rolling restart.


Visual Studio SSDT Ignore Query Store Options in Publish Profile

There does not seem to be a way to ignore individual database properties. However, you can ignore them all by adding the following to your publish profile XML file:

<ScriptDatabaseOptions>False</ScriptDatabaseOptions>

I have tested this and it does indeed leave out the QUERY_STORE statements.

Then, if there are any database-level properties that you do want set, you can add them back in manually via a Post Deploy SQL script that is added to the project and tagged as being PostDeploy in the file properties, under the "Build Action" property drop-down.

You can also add that XML element to your .sqlproj file or your .sqlproj.user file.

This option needs to go within a <PropertyGroup>, regardless of which of the three files you put it in. The order of them items within the <PropertyGroup>...</PropertyGroup> don't matter.

Another option, if you are merely generating the "Create" or "Publish" SQL script and are not deploying it immediately, is to use a utility to find and replace the two SET QUERY_STORE... lines with something like:

SET {some other DB property} = {its current setting}; --

For example:

SET TRUSTWORTHY OFF; --

Which should result in those two lines being turned into:

SET TRUSTWORTHY OFF; -- (QUERY_CAPTURE_MODE = NONE, ...

You might want to look into the following dos-based RegEx processor:

JREPL.BAT - regex text processor with Unicode and XRegExp support


SQL Agent job does not show up in the jobs folder in SSMS although they are in sysjobs table

A better option might be to script the jobs out instead of taking the information directly out of the system tables. In SSMS just right click each job and it should give you the option to script to a new query window or file.

Scripting the jobs out in this manner is probably the most affective, short of just restoring the msdb database to the other instance. You can review the script before running it on your second server and ensure the job steps and schedule looks right. If you have any that run frequently you might adjust the script to have those created as disabled, so you can verify first.

There are a few things that might affect why your pocess did not work, one being the order in which you inserted them and ensuring that the records or linked by the jobid like they should be.


Is it still outer join? Or is it becomes inner join?

It is still an outer join:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table tab_a(num integer);
create table tab_b(num integer);

insert into tab_a(num) values(1);
insert into tab_a(num) values(2);

insert into tab_b(num) values(2);
insert into tab_b(num) values(3);

Query 1:

select *
from tab_a, tab_b
where tab_a.num = tab_b.num(+)

Results:

| NUM |    NUM |
|-----|--------|
|   2 |      2 |
|   1 | (null) |

Query 2:

select *
from tab_a, tab_b
where round(tab_a.num, 2) = round(tab_b.num(+), 2)

Results:

| NUM |    NUM |
|-----|--------|
|   2 |      2 |
|   1 | (null) |


Is it possible to log backup results to table in a different database on a different server

The dbo.CommandLog table will be in whatever database you created it. By default it will be in master (here is a portion of the MaintenanceSolution.sql script):

USE [master] -- Specify the database in which the objects will be created.

...

IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CommandLog](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [DatabaseName] [sysname] NULL,

...

If you want to log on another server, you need to be prepared for things like flaky or dead connections, and the potential latency of writing a lot of information over a linked server.

If you really want to do this, then create the dbo.CommandLog table on that other instance, then in the database where that table exists currently:

-- you could drop, but in case you have history you want to keep:
EXEC sys.sp_rename N'dbo.CommandLog', N'CommandLogBackup', N'OBJECT';

CREATE SYNONYM dbo.CommandLog
  FOR LinkedServerName.DatabaseName.dbo.CommandLog;

In dbo.DatabaseBackup there is a check for the table in the same database as the procedure:

IF @LogToTable = 'Y' AND NOT EXISTS
(
  SELECT * FROM sys.objects objects
    INNER JOIN sys.schemas schemas
    ON objects.[schema_id] = schemas.[schema_id]
    WHERE objects.[type] = 'U'
    AND schemas.[name] = 'dbo'
    AND objects.[name] = 'CommandLog'
  )
  BEGIN
    SET @ErrorMessage = 'The table CommandLog is missing. Download...'
    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    SET @Error = @@ERROR
  END

It's only failing because it's explicitly looking for a table - the fact that it is a synonym won't prevent any subsequent logging calls from working (again, as long as everything - including the synonym - live in the same database). You can try just commenting out the RAISERROR line or just removing the check in its entirety. Just remember to do that any time you deploy an update to the procedure. You might consider asking Ola for a feature to support a synonym or view so that the logging table could be kept elsewhere.


Get all dates in the current month report from three table with date wise sum and balance

Here is one approach I've used in the past. I think this will help you with the basics. I've only included 2 of the tables, adding the third should help you to understand the answer rather than copy/paste it.

(You'll need to change the first line to add references to sum(Costings), and add a 'union' 'select ... from bank_mobile_costing', in addition to adding a reference 'Costing' column to the 'Investments' and 'Earnings' select statements.)

select date,sum(Investments),sum(Earnings),sum(Investments+Earnings) as Balance from
(
select date,amount as 'Investments',0.0 as 'Earnings' from bank_mobile_investments
union
select date,0.0,amount from bank_mobile_earnings

) mydata
group by date
order by 1

It's worth my noting here that this will NOT display a list of calendar dates in the date column, but rather it displays a complete list of all the dates which exist in your 3 tables; I don't know if that's appropriate to your needs.

On the subject of your running total column at the end, this is a convoluted subject but a quick google search yields the following stack overflow result which gives a couple of methods of doing it, and there are some interesting points raised in the comments about the advantages or drawbacks of the methods.

Calculate a Running Total


Schedule Full, Differential and Transactional Backup in SQL Server 2016

Adding to the answer of Shekar Kola.

Personally I always suggest and use custom scripts over maintenance plans as these are easier to deploy on multiple servers, and easier to configure.

If you're going for the route of the custom scripts instead of the maintenance plans, consider using Ola Hallengren scripts.
Ola hallengren website

These are some amazing well documented and frequently used scripts that cover everything about backup, index maintenance and itegrity checks. These scripts get recommended by some of the best DBA's around.

The backups wont overwrite eachother and the script will add dates to the .bak files.

The only thing you have to do is run the scripts, and add some schedules yourself.
IF you want more configuration, check the documentation, there's a lot you can add/change yourself if you'd like.

UPDATE:

  Is it fine to have system database backup everyday?

Yes nothing wrong with it.

  and Differential users database backup every 4 hours and Log backup every 15 mins.

Doing a diff during work hours can put a lot of stress on your database, and can hurt performance A LOT. Often this is not acceptable. Again, depends on your case, how long backups take, how often your data changes.

  Do you think this is good enough for a a database that has 2000 transaction per day (approximately)?

The amount of backups are normally decided by the business. You should look up RTO and RPO SQLSkills RTO/RPO. How many backups and which backups you need is related to those numbers, and to the amount of disk space you can use. An often used schedule is, 1 full each week, 1 diff each day, log backups every 10 - 15 min. But it of course depends on your case.

  I do have other jobs such as 'DatabaseIntegrityCheck - USER_DATABASES' , 'IndexOptimize - USER_DATABASES' ,etc. Should I schedule them too? or they will be used automatically with backups?

No they will not be fired with the backups, they are totally separate jobs. But yes, you should have some kind of Integritychecks and index maintenance running. Read up on the documentation of what exactly they do. Very short: Integrity checks check your data for corruption, and IndexOptimize will check your indexes for fragmentation.

But as a starter, just make sure you take backups, it's much more important to have backups not scheduled perfectly, than not having backups at all.


Best database for a real time application

I don't think the database server will be your bottleneck. Many databases can do sub-second responses to queries provided your database is scaled and designed correctly.

I think you need to be asking yourself questions about how the android devices will connect (mobile internet can be slow sometimes), how your middle tier logic will work and how the devices will communicate with the middle tier.

If you are serious about having millions of vehicles on the system you need to be thinking about how often each of them will update their location and how often the other cars will ask for nearby cars.

Let's say you have a million of cars, and each device has to get a sub-second status update about the nearby cars, that would mean that cars need to update their status every half a second and every car requesting statuses every half a second.

That's millions of requests per second from the devices to your web services and millions of queries per second from your middle tier to your database server.
Keeping in mind that your web service will need to calculate proximity, take into account maps and streets and the like, i think your database platform will be the least of your worries.


Minimum requirements to run Sql Server BCP.exe utility

You have to use one of the SQL Server distributable packages to stay within licensing terms. These are not full installs.

The one you want is "Microsoft® SQL Server® 2008 R2 Command Line Utilities"

Note: copying bcp.exe by itself will not work


Help with this query dealing with aggregates

First your query is missing a join on the two tables involved. After that, it can be slightly altered to get you the albums of bands with more than one album, like this:

SELECT b.name, alb.title                     -- show me all bands
FROM band AS b                               -- and their albums
  JOIN album AS alb
    ON alb.band_id = b.band_id
WHERE EXISTS                                 -- where exists at least one
      ( SELECT *
        FROM album AS al                     -- other album
        WHERE al.band_id = alb.band_id       -- of the same band
          AND al.album_id <> alb.album_id    -- (this ensures the "other")
      ) ;


Network design considerations for Oracle Database Appliance

As it turns out, the ODA is factory configured with active-backup bonds. I've tested this to work well without any switch-side LACP/EtherChannel configuration, and each bonded connection may be split across two switches. In my tests, no simulated failure or network reconfiguration caused more than a a few hundred milliseconds worth of network outage.

This means that one can set up an isolated redundant front network for web applications using any layer two switches that are not inherently redundant.

To avoid client connections taking the long way into the company network and back through the other switch (and thus making production dependent on that equipment), one can have a private VLAN that only lives on the two edge switches and on an EtherChannel trunk between them.

As such, only the application servers and the database appliance will exist on that virtual network segment.

I don't see a way to control which path the connections from the application servers take to the database listeners, so the link between the two switches will have to be redundant, less this link becomes a single point of failure. This rules out using unmanaged switches without support for VLAN and either LACP or STP.

Using Cisco Catalyst 2960-series switches, I believe a combination of EtherChannel and Port Fast would be the better choice for a solid independent connection between the two. I would also use Port Fast on the ports for all the bonded connections to ODA and application servers.

Since the production network is isolated, one would need separate network connections for management, backup and connectivity to the rest of the company network.

Naturally, in order for this front production network to be fully self contained, any dependencies to external resources, such as DNS or authentication services, must also be resolved. Ideally production would be able to continue independently, without regard to any faults, ongoing maintenance or network outages anywhere else in the data center or company network.


An execution plan is only going to have the code that was last compiled and used, to my knowledge. In troubleshooting applications myself I have come across where a statement I was looking at through the execution plan was actually written with sp_executesql and so was dynamic T-SQL that contained a bit more logic than the end result.

You don't specify the version you are working with but I would suggest using a server-side trace or Extended Event session to map out the sequence of commands being passed so you fully understand what is being done in your situation.

My first thought is the application (or the developer) wrote the insert statement and in some form or fashion is doing a check on something (i.e. maybe errors). In that check if it is good they are simply passing what ends up looking like SELECT 0, where if an issue occurred it might return SELECT 3910 that would mean something to that application. You are only going to see the full logic (if any) behind all of it by doing a trace on the commands.


Should we need to use Barriers on a production database (MySQL/InnoDB)?

Yes it is always safer. Thought the chance of actually getting corruption is low. When the filesystem gets corrupted it is very likely that repairing it will be successful. ACID compliant databases like InnoDB also do fsyncs/barriers to make sure committed changes are permanently stored on disk.

Don't forget that in a production environment you should be using a high quality server using redundant PSU and or UPS so the chance you would actually ever need the journal is already fairly small.

A reason not to enable it might be performance. Of course, this only affects write performance. But these kinds of things should be measured as they are highly dependent on your hardware. For instance when running with battery-backed write cache on a raid controller the performance penalty will be as good as zero because of the writeback caching of the controller.

So all in all I believe the risk to be very small but it depends on what is at stake. Personally, I think that if the risk is high enough to warrant this, you should be looking at replication to a second server. You already are making regular backups of course.

BTW: fsync is inefficient on ext3 as it always syncs all files! ACID compliant databases tend to do a lot of fsyncs so it is better to use ext4 if you can.


Postgres 9.5 Materialized View from a table with jsonb columns

You just need to GROUP BY the product to build the sum() of the quantities of the products.

CREATE MATERIALIZED VIEW products
AS
SELECT "content"->'items'->>'product' "product",
       sum(("content"->'items'->>'qty')::integer) "quantity"
       FROM "my_table"
       GROUP BY "content"->'items'->>'product';


mysqldump is ignoring option '--databases' due to invalid value

In order to fix this issue, just move the database parameter from client to mysql in my .cnf

i.e

$ cat ~/.my.cnf
[client]
host=localhost
user=trunks
password=mai

[mysql]
database=capsule_corporation_database


Unable To Restore Database From .BAK File

Perform the following commands in SQL Server Management Studio (SSMS) on the SQL Server instance you are trying to restore the database to.

Verify BAK File

RESTORE VERIFYONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

The backup set on file 1 is valid.

Possible Errors

If the check terminates abnormally then the cause could be because one of these checks have failed:

  Checks performed by RESTORE VERIFYONLY include:

  That the backup set is complete and all volumes are readable.
  Some header fields of database pages, such as the page ID (as if it were about to write the data).
  Checksum (if present on the media).
  Checking for sufficient space on destination devices.

Reference: RESTORE Statements - VERIFYONLY (Transact-SQL) (Microsoft | SQL Docs)

Check the BAK Fileheader

RESTORE HEADERONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

BackupName  BackupDescription     BackupType ExpirationDate Compressed Position DeviceType UserName                         ServerName    DatabaseName         DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN              LastLSN               CheckpointLSN         DatabaseBackupLSN     BackupStartDate         BackupFinishDate        ........
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ........
NULL        NULL                  1          NULL           0          1        2          NT SERVICE\SQLSERVERAGENT        NOTHING       StackExchange        782             2019-07-24 09:17:00.000 1688399872  568000013810400037    568000013813600001    568000013810400037    568000013524000037    2020-01-15 19:25:27.000 2020-01-15 19:25:29.000 ........

Check the Contents of the BAK File

RESTORE FILELISTONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

LogicalName              PhysicalName                                   Type FileGroupName    Size         MaxSize         FileId  CreateLSN           DropLSN  UniqueId                             ReadOnlyLSN         ReadWriteLSN       BackupSizeInBytes  SourceBlockSize FileGroupId LogGroupGUID  DifferentialBaseLSN  DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint
------------------------ ---------------------------------------------- ---- ---------------- ------------ --------------- ------- ------------------- -------- ------------------------------------ ------------------- ------------------ ------------------ --------------- ----------- ------------- -------------------- ------------------------------------ ---------- --------- --------------
StackExchange            C:\SQL\SQL_DATA\StackExchange.mdf              D    PRIMARY          2623537152   35184372080640  1       0                   0        725120E9-8DF3-4E09-9A89-94433A6EB49B 0                   0                  1687224320         4096            1           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_DefRO      C:\SQL\SQL_DATA\StackExchangeRO.ndf            D    DEFAULTRO        5242880      35184372080640  3       89000000045300001   0        05C6BA44-123A-4749-8FB5-A0A16EF49A51 102000000014600005  90000000013100004  131072             4096            2           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 1          1         NULL
StackExchange_PUBL_SNAP  C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf    D    PUBL_SNAP_DBASE  5242880      35184372080640  4       286000001633700001  0        DCA761C4-6260-4BE3-8280-64099C98911D 0                   0                  131072             4096            3           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_log        C:\SQL\SQL_LOGS\StackExchange_log.ldf          L    NULL             21798322176  2199023255552   2       0                   0        364124B1-081E-45C5-971C-2CD1CBBF13EC 0                   0                  0                  4096            0           NULL          0                    00000000-0000-0000-0000-000000000000 0          1         NULL

If the Steps Fail

If any of the above steps fail, then you will have to determine what the issue is. The RESTORE ...  command should provide enough information to assist you in finding the root cause.

Example Solutions

If the backup file is corrupt, then you will have to find a backup file that works.
If the disk is full, then you will have to add additional space.
If all else fails, then you might have to open a case with Microsoft Support.

Best Practice

(emphasis mine)

  Test your backups!

  You do not have a restore strategy until you have tested your backups. It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use. It is also recommended that once you restore the backup, you perform database consistency checks via DBCC CHECKDB of the database to validate the backup media was not damaged.

Reference: Back Up and Restore of SQL Server Databases (Microsoft | SQL Docs)


If it's not possible to do it automatically, can it be done manually? So only entries which are not present in the primary database A will be inserted out of one secondary of database B ?

Little Example: In A we got: 1,2,3 In B we got: 5,6,7

What I want is to add 5,6,7 from database B into database A and vice versa, so it looks like this at the end:

A: 1,2,3,5,6,7

B: 5,6,7,1,2,3


How do you create a composite foreign key when key being referenced is also a foreign key

You can create a composite foreign key from B to C:

alter table TableC
     add constraint FK_TableC_XY
     foreign key (x, y)
     references TableB (x, y)

That's better than two foreign keys: this will ensure that the combination of (x, y) is present, and not just the individual values.


Alternative query to this (avoid DISTINCT)

You could get that same result set with the following:

select country from countries where exists
    (select countryid from users
        where users.countryid = country.countryid)

OR

select country from countries where countryid in
    (select countryid from users)

Neither of these use distinct but other than that I don't know how much "better" they are.


Complex queries in CouchDB across multiple documents

For quick one time queries I would use _find, for permanent ones where performance is important I would create a view for this.

Views are very well explained in the booklet "Writing and Querying MapReduce Views in CouchDB: Tools for Data Analysts".


May I suggest to use views?  Consider the following tables (and test data):

-- T1
create table ihead (
  id number primary key
, col_a varchar2(32)
, col_b varchar2(32)
);

-- T2
create table irow (
  headid number references ihead(id)
, col1 varchar2(32)
, col2 varchar2(32)
, irowid number primary key
);

In the second table (that you have called T2), there should the rows should be numbered somehow, otherwise it will be hard to get to invoice details into the correct order - as @Bill has hinted.

-- test data
begin
  insert into ihead values (1, 'AAA', 'BBB');
  insert into ihead values (2, 'CCC', 'DDD');
  insert into irow values (1, 'eee', 'fff', 1);
  insert into irow values (1, 'ggg', 'hhh', 2);
  insert into irow values (2, 'iii', 'jjj', 3);
  insert into irow values (2, 'kkk', 'lll', 4);
end;

I'd create a join first, and store it as a VIEW, like so:

-- join, similar to the first select you have written

create or replace view alldetails
as
select
  H.id, H.col_a, H.col_b, R.col1, R.col2, R.irowid
from
  ihead H join irow R on id = headid;

Check the output:

select * from alldetails;
-- output
ID COL_A  COL_B  COL1  COL2  IROWID
1  AAA    BBB    eee   fff   1
1  AAA    BBB    ggg   hhh   2
2  CCC    DDD    iii   jjj   3
2  CCC    DDD    kkk   lll   4

Second view: get all INVOICE_HEADS (first select), then INVOICE_ROWS (second select)

create or replace view idetails (c1, c2, c3, c4, irow)
as
select 'INVOICE_HEAD', col_a, col_b, col1, col2 from alldetails
where irowid in (
  select min(irowid) from iheader group by id
)
union
select 'INVOICE_ROW', null, null, col1, col2 from alldetails
where irowid not in (
  select min(irowid) from iheader group by id
)

Now we can just:

select * from idetails order by irow
-- output
C1              C2      C3      C4      IROW
INVOICE_HEAD    AAA     BBB     eee     fff
INVOICE_ROW     -       -       ggg     hhh
INVOICE_HEAD    CCC     DDD     iii     jjj
INVOICE_ROW     -       -       kkk     lll

Get to the final output by executing:

select
  c1 ||';'|| c2 ||';'|| c3 ||';'|| c4 ||';'|| irow
from idetails
order by irow

-- final output (some padding added manually,
-- but can be done with RPAD or LPAD)

INVOICE_HEAD ; AAA ; BBB ; eee ; fff
INVOICE_ROW  ;     ;     ; ggg ; hhh
INVOICE_HEAD ; CCC ; DDD ; iii ; jjj
INVOICE_ROW  ;     ;     ; kkk ; lll

UPDATE 2017-05-17 (requirements have changed)

-- leave the first view as it is,
-- change the second view to be:
create or replace view idetails (
  c1, c2, c3, id, c4, c5, irowid )
as
select distinct
  'INVOICE_HEAD', col_a, col_b, headid, null, null, 0
from alldetails
union
select
  'INVOICE_ROW', null, null, headid, col1, col2, irowid
from alldetails;

Then, select all the columns you need (notice: 2 columns in the ORDER BY clause):

select
  c1 ||';'|| c2 ||';'|| c3 ||';'|| c4 ||';'|| c5
from idetails
order by id, irowid;

Output (padding added by hand, just for clarity):

INVOICE_HEAD;AAA;BBB;   ;
INVOICE_ROW ;   ;   ;eee;fff
INVOICE_ROW ;   ;   ;ggg;hhh
INVOICE_HEAD;CCC;DDD;   ;
INVOICE_ROW ;   ;   ;iii;jjj
INVOICE_ROW ;   ;   ;kkk;lll


CREATE DATABASE permission denied for sysadmin

The EXECUTE AS clause of CREATE PROCEDURE can only be used to impersonate a user (not a login) and the scope of impersonation is restricted to the current database. The sysadmin permission is associated with the login, not the user, so you receive a permissions error.

The correct way to grant CREATE DATABASE here is to sign the procedure. The process is a little involved because we want to sign the procedure to grant a server-level permission, but each step of the process is reasonably simple:

First, create a certificate and login in master with the CREATE ANY DATABASE permission. The certificate will later be copied to the target database to allow procedure signing.

USE master;
GO
CREATE CERTIFICATE CreateDatabaseCert
   ENCRYPTION BY PASSWORD = 'password'
   WITH SUBJECT = 'Create Database',
   START_DATE = '20140101',
   EXPIRY_DATE = '20141231';
GO
CREATE LOGIN CreateDatabaseLogin
FROM CERTIFICATE CreateDatabaseCert;
GO
DENY CONNECT SQL TO CreateDatabaseLogin;
GO
GRANT CREATE ANY DATABASE
TO CreateDatabaseLogin;

We now need to copy the certificate to the database where the procedure will be run. There are a couple of ways to do this, but the most compatible is to read and write the certificate via a file. This next step writes the certificate:

BACKUP CERTIFICATE CreateDatabaseCert
TO FILE = 'C:\Temp\CreateDatabase.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\Temp\CreateDatabase.pvk',
    ENCRYPTION BY PASSWORD = 'password',
    DECRYPTION BY PASSWORD = 'password'
);

Now we switch to the target database, restore the certificate, and delete the temporary files:

USE Sandpit;
GO
CREATE CERTIFICATE CreateDatabaseCert
FROM FILE = 'C:\Temp\CreateDatabase.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\Temp\CreateDatabase.pvk',
    ENCRYPTION BY PASSWORD = 'password',
    DECRYPTION BY PASSWORD = 'password'
);
GO
--EXECUTE sys.sp_configure 'show advanced options', 1;
--RECONFIGURE;
--EXECUTE sys.sp_configure 'xp_cmdshell', 1;
--RECONFIGURE;
EXECUTE sys.xp_cmdshell 'ERASE C:\Temp\CreateDatabase.*';

Now create the procedure, and sign it using the certificate:

CREATE PROCEDURE dbo.CreateDatabase
(
    @DatabaseName SYSNAME
)
WITH EXECUTE AS CALLER
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(max);
    IF COALESCE(@DatabaseName,'') <> ''
    BEGIN
        SET @cmd = 'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + ';'
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = 'INVALID DATABASE NAME';
        RAISERROR (@cmd, 0, 1);
    END
END;
GO
ADD SIGNATURE TO dbo.CreateDatabase
BY CERTIFICATE CreateDatabaseCert
WITH PASSWORD = 'password';

To test this works, we create a new login and user with permissions to execute the procedure only:

CREATE LOGIN test WITH PASSWORD = 'password';
CREATE USER test FROM LOGIN test;
GRANT EXECUTE ON dbo.CreateDatabase TO test;

The test itself:

EXECUTE AS LOGIN = 'test';
EXECUTE dbo.CreateDatabase @DatabaseName = 'NewDB';
REVERT;

The database is created successfully. To clean up, run the following:

DROP DATABASE NewDB;
DROP USER test;
DROP LOGIN test;
DROP PROCEDURE dbo.CreateDatabase;
DROP CERTIFICATE CreateDatabaseCert;
GO
USE master;
DROP LOGIN CreateDatabaseLogin;
DROP CERTIFICATE CreateDatabaseCert;

For more information on permissions, see Erland Sommarskog's excellent article.


Error join database to SQL availability group

It seems like you don't have permission to the folder M:\UserLogs\.

Try the below method, it may be help you.

Grant full access to SQL server service account for M:\UserLogs. (Which must be a domain account)
Manually restore the database.
While adding the db to AG choose JOIN ONLY.


Handling expiration in a course registration or ticketing system

There are two principals that should govern your thinking about you data modeling options for this application:

1. Never Throw Away Important Information

Discarding data that you might need is a terrible idea.  It's up to you to decide whether a failed registration is something you might need.  I would think it probably is.  What would you do if someone showed up for a class saying "hey I registered!"?  Wouldn't you want to be able to say "oh, I see your cheque bounced so your registration failed."?

2. Model Close to Reality

If you try to abstract your entity types too much, you will find your model gets brittle and hard to adapt to changing business rules.  Your system is tracking a couple of kinds of events.  In particular, the act of someone registering for a class and the act of someone paying for a registration.  You should have a table for each of these events, along with tables for the people and classes.

Keeping separate tables for registration (which you already have) and for payment (which you don't really have in any of your options) allows you to handle situations like payment by multiple methods, e.g. some cash, some credit card, some promotional coupon, etc.  It also lets you deal with the situation of failed payments, credit notes processed, and other real-world exceptions that are probably important to know about.

Instead of keeping columns for registration status, which is ultimately a calculated value, you should be keeping columns (and records) for the various components of the registration status calculation.  If your system turns out to have peculiar performance demands during real runtime testing, then consider using denormalization of the calculated registration status value, but beware of all of the potential issues that will raise for data integrity and plan to deal with those issues.

Bonus:

If you keep a REGISTRATION table and a PAYMENT table, as I suggest, and treat registration status as a calculated value, then you can add a column to REGISTRATION that allows you to bend the payment time limit rule.  You can do this different ways.  For example you could have a flag or code that means "consider this registration valid, even without payment".  Alternatively, you could have a numeric or date column that gives a payment extension allowance in number of days or by a different, arbitrary deadline.


Effectively saving a graph (terabytes) in a database

Column < Row < Block < Table
One column had a number, date, string, or a small number of other possibilities.  It takes a few bytes or many bytes.
One row like you described (4 columns each taking a few bytes) will take perhaps 40 bytes when you add in some overhead.
One block is 16KB and holds (in your case) a few hundred rows.  I mention a block because it is the unit of caching in InnoDB tables.  All activity is done on blocks in RAM.  If an operation needs to work on the rows in a block but that block is not in RAM, it will be brought into RAM, possibly pushing out some other block.  (Read about "caching".)
One table is composed of as many blocks as are needed -- billions in your case!
So,...  because of caching those billions of blocks can be handled.  But with a lot of I/O.
I am assuming you need one row to describe one edge.  And probably another table with nodes -- and 4 billion rows in that table.
These are huge numbers.  Am I misinterpreting the number of edges and nodes?  What do you expect to walk through all the edges for some operation?  If so, let's see...

40TB in edge table;
2.5 billion blocks, each needing to be read at least once;
SSD drive that can handle 1K reads/second;
2.5 million seconds to read all edges once;
That's about 1 month.

Somethin's gotta give!


Sql server 2012 grant role permission to just validate view but not select data from it

Is there a permission in SQL server 2012 to just validate/ existence
  an object (in my case it's a view) but not a select permission?

Consider granting VIEW DEFINITION  permission as defined in the below quoted reference; this still applies to SQL Server 2012 as well.

  VIEW DEFINITION

  The VIEW DEFINITION permission lets a user see the metadata of the
  securable on which the permission is granted. However, VIEW DEFINITION
  permission does not confer access to the securable itself. For
  example, a user that is granted only VIEW DEFINITION permission on a
  table can see metadata related to the table in the
  sys.objects
  catalog view. However, without additional permissions such as SELECT
  or CONTROL, the user cannot read data from the table. For more
  information about viewing metadata, see GRANT
  (Transact-SQL).

Creating a per Database Role for VIEW DEFINITION permissions

/* CREATE A NEW ROLE  -- View Definition */
-- Database specific
CREATE ROLE db_All_ViewDefinition
GRANT VIEW DEFINITION TO db_All_ViewDefinition

Members of this role in the database you create it on should only have access to the applicable metadata as long as they don't have other permissions to database objects such as explicit SELECT access to the a table or a member of the db_datareader fixed database role.

Granting VIEW DEFINITION permissions explicitly per object

USE [DatabaseName];
GRANT VIEW DEFINITION ON dbo.tablename
TO [domain\username]

The above TSQL will grant explicit VIEW DEFINITION access to the specific DB object and to the specific security principal as specified in the applicable TSQL logic

Further Resources

GRANT Object Permissions (Transact-SQL)
CREATE ROLE (Transact-SQL)
VIEW DEFINITION


Switching out of one scheme into another

I was able to figure it out on my own.  If you switch out from a partitioned table to a file group, you can then switch that table into a partitioned table with another partition scheme.

Here's my proof of concept:

USE [master]
GO

    CREATE DATABASE [test]
     CONTAINMENT = NONE
     ON  PRIMARY
    ( NAME = N'test', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [a_fg]
    ( NAME = N'a_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\a_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [archive_l_null_fg]
    ( NAME = N'archive_l_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\archive_l_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [archive_r_null_fg]
    ( NAME = N'archive_r_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\archive_r_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [b_fg]
    ( NAME = N'b_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\b_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [main_l_null_fg]
    ( NAME = N'main_l_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\main_l_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
     LOG ON
    ( NAME = N'test_log', FILENAME = N'T:\SQLLog01\MSSQLSERVER\LOG\test_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
    GO

    USE [test]
    GO

    CREATE PARTITION FUNCTION main (datetime)
        AS RANGE RIGHT FOR VALUES
        (
            '2018-1-1',
            '2018-2-1'
        )

    CREATE PARTITION FUNCTION archive (DATETIME)
        AS RANGE RIGHT FOR VALUES
        (
            '2099-01-01'
        )

    CREATE PARTITION SCHEME main_ps
        AS PARTITION main TO
        (
            'main_l_null_fg',
            'a_fg',
            'b_fg'
        )

    CREATE PARTITION SCHEME archive_ps
        AS PARTITION archive TO
        (
            'archive_l_null_fg',
            'archive_r_null_fg'
        )

    CREATE TABLE dbo.main_table
    (
        PK_ID INT not null IDENTITY(1, 1),
        CreatedDate datetime not null
    ) ON main_ps (CreatedDate)

    CREATE TABLE dbo.archive_table
    (
        PK_ID int not null,
        CreatedDate datetime not null
    ) ON archive_ps (CreatedDate)

    INSERT INTO dbo.main_table ( CreatedDate )
    VALUES ( '2018-01-01' ),
        ( '2018-01-03' ),
        ( '2018-01-07' )

    CREATE TABLE dbo.switchout_table
    (
        PK_ID INT not null,
        CreatedDate DATETIME NOT null
    ) ON a_fg

    ALTER TABLE dbo.switchout_table
    ADD CONSTRAINT CK_SOT CHECK (CreatedDate >= '2018-01-01' AND CreatedDate < '2099-01-01')

    ALTER TABLE dbo.main_table
    SWITCH PARTITION 2 TO dbo.switchout_table

    ALTER PARTITION FUNCTION main()
        MERGE RANGE ('2018-01-01')

    ALTER PARTITION SCHEME archive_ps
    NEXT USED a_fg

    ALTER PARTITION FUNCTION archive()
        SPLIT RANGE ('2018-01-01')

    ALTER TABLE dbo.switchout_table
    SWITCH TO dbo.archive_table PARTITION 2

    DROP TABLE dbo.switchout_table


You don't need to disable writes to the database while pg_dump is running. It's designed to create consistent backups by default. Quoting the manual:

  pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.  pg_dump does not block other users accessing the database (readers or writers).


Databse ER Design for Apartment Complex and Automaker Employees

I think overall you have a decent enough design there. My only points of issue might be with the Invoice Table having a calculated field (TotalNights) and that the Discount uses just the DiscountID, not the amount being discounted. Like this, a person looking at the table would have to JOIN to the Discount Table to see what the discount is by itself, unless he wanted to do the manual math of breaking down the Total, Flat Rate and Discount amount.


Finding posts with exactly set of tags per user

If you are using MySQL 5.6 or later, then the query will be reasonably efficient.  If using an older version, it is "order N*N", which is very slow for large subqueries.


How to select rows based on other tables - MySql

Is this what you're looking for?

SELECT Whatever FROM UserTable
WHERE UserTable.username NOT IN
(
  SELECT CheckTable.user_id FROM CheckTable
);

If this is not suitable, please expand. Possibly with table definitions - (SHOW CREATE TABLE My_Table\G) and some sample data - (INSERT INTO My_Table VALUES(....)), and finally, the result you want with the logic you used to obtain it.

With further research, you might also find this interesting - and also this. As a generic response, substituting NOT EXISTS for NOT IN might be the way to go - YMMV.


How to use PostgreSQL bit_or function to aggregate column?

bit_or is an aggregate function, like sum or count.

SELECT bit_or(flags) FROM items;


Upgrade to SQL Server from 2008 to 2012 - Developer perspective

Yes there may be impact. To start with, you must read

List of SQL Server features deprecated in SQL Server 2012
List of deprecated Database engine features in SQL Server 2012
Breaking changes to DB engine feature in SQL server 2012

You must run SQL server 2012 upgrade advisor and generate report before doing migration. This report would list out all features deprecated and which would not work moving ahead in SQL Server 2012.

There is an option to allow few old features to work in SQL server 2012 and that would be by keeping compatibility level of database to 100 after migration. As a good practice you should change compatibility level of database to match that of Server. In case you want to change compatibiltity level please read Alter database Compatibility level. The good thing with compatibility level is it can be changed immediately if something goes wrong. About compatibility level please note that

  Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2012, convert the application to work properly.

For upgrading SSIS packages you should read BOL document I guess pretty much every thing is documented in Upgrade Integration services packages. If you want reference document please download 5 Tips on smooth SSIS upgrade to SQL Server 2012

Basically you have to read a lot and you have to look for features that has been deprecated. You might as well need to make changes in your code. As you can understand it wont be possible for me to tell you changes, you have to look at your code taking doc as refernce as do necessary changes.


This simply means you do not have any table called USERS.CA_FILE.


Xampp 3.2.1 error: MySQL shut down unexpectedly

Go to Xampp/mysql/bin/my.ini

open it and add the following line:

innodb_force_recovery = 1

Atleast this was what solved it for me.
I'd backup my .cnf file to be on the safe side.
After adding the single line you should be able to start your mySQL service again.

P.S. I think anything greater than 0 works, since the log error simply states > 0.


Can a single SQL Server instance issue the same SYSDATETIME twice?

Yes. The resolution of the Windows clock is decent, but not perfect. You should use another field, such as an INT IDENTITY, to ensure uniqueness.  But go ahead and cluster on the date first, if it suits your needs.

Alternatively, you could use a DATETIME PK, but supervise inserts with a stored procedure that verifies the uniqueness of the incoming date, and pauses if necessary.  I can whip up an example if you'd like.

Edit:

Another option, borrowing from ypercube's comment, would be a non-unique clustered index on the date field, and a unique non-clustered primary key on the ID field. This would keep your other indices smaller, as they would point to the narrow ID column, but would also entail two hops to get to actual data.


'mysql' Recognized by cmd but not PowerShell?

So as I am reading about powershell now a days and I find this interesting and wanted to know answer myself, on some googling here is what I found:

Considering that you are using  similar command as given below to fire the Mysql

  PS C:\Program Files\mysql\MySQL Server 5.7\bin> mysql

Here is the full version of error that you should be getting :

  mysql : The term 'mysql' is not recognized as the name of a cmdlet,
  function, script file, or operable program.  Check the spelling of the
  name, or if a path was included, verify that the     path is correct
  and try again. At line:1 char:1+ mysql
  + ~~~~~
          + CategoryInfo          : ObjectNotFound: (mysql:String) [],     CommandNotFoundException
      + FullyQualifiedErrorId : CommandNotFoundException

  Suggestion [3,General]: The command mysql was not found, but     does
  exist in the current location. Windows PowerShell does  not load
  commands from the current location by default. If you      trust this
  command, instead type: ".\mysql". See "get-he lp
  about_Command_Precedence" for more details.

If we see the Suggestion part it says Windows PowerShell does not load commands from the current location by default. If you trust this command, instead type: ".\mysql".

So try using .\mysql instead of just mysql after setting the current directory to C:\Program Files\mysql\MySQL Server 5.7\bin> but this might lead you to another error which most probably will say :

ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (usingpassword: NO)

which you can than resolve changing the syntax to mysql -u root -p

So it looks like kinda security thing that powersehll is imposing.

Source:1

Note: Try running $env:Path in powershell to see if the path is correctly set.


Tables names in a supertype/subtype schema

Instead of changing the names of individual tables, I would recommend using schema names to denote the different types of users.

For instance:

Admin.Users
Reader.Users
Approver.Users

Using Schemas in this way allows a more flexible method for controlling access since you can assign rights by schema instead of at the individual table level.


To Foreign Key or not to Foreign Key..... (inet -> cidr)

To answer my own question here, here is what I eventually did:

Function ip_block(inet) to look up the ip block record of an inet address.
Check constraint to make sure that returns not null for appropriate classes of addresses.  That would be triggered on insert or update to the assignments table.
Trigger function to check assignments table for addresses in a block and raise an RI exception if such are found.
Trigger which runs the above function when the block_def of the ip block is updated, or the ip block is deleted.

I will leave this answer unaccepted for a while to see if anyone else has a better approach.  However the amount of code to actually do this turned out to be a lot less than expected so I am pretty happy with it so far.


What is the primary key on a temporal table?

I'm thinking PRIMARY KEY (insertion_timestamp, deleted_flag, natural_key), but the only reason for including deleted_flag is to account for the possibility of a row being inserted and then (soft) deleted immediately, sooner than the next tick of the granularity of the TIMESTAMP data type. This feels overly paranoid...

I don't know if it's too paranoid, but it's reasonable to just stipulate that no two rows may share the same insertion_timestamp and natural_key, and make it the responsibility of the clients doing the inserts to handle the edge case.  It simplifies using the table considerably if you can assume that those combinations are unique—plus in the tripartite key you suggested, it's tricky to order the rows in the triply-paranoid case where there are two immediate "soft" deletes in a row.

So my recommendation would be PRIMARY KEY (natural_key, insertion_timestamp) (in that order—but I'm guessing natural_key is going to be queried for more often, which could be wrong).

Also, you should seriously consider the following additions:

Add an inactive_timestamp column to the tables.  This allows you to query for rows that were active at a point in time.
Think carefully about what the timestamps mean.  Is the table supposed to represent the history of the real-world values ("Joe's phone number was 555-5555 until January 22, then it was 666-6666 from January 22"), or history of modifications to an "eternal" value?  Your schema looks like it's going after the latter, but make sure you understand which is going to be needed.


SSMS 2012 - loaded saved query not executable ("invalid object name" error)

You might need to add "GO" after your "USE [DB_NAME]" statement.
Some commands need to be first in the batch.
With GO, the USE DB command is executed as first batch, with another batch following it.

So:

USE [DB_NAME]
GO

CREATE PROCEDURE...


mysql workbench migration changed my database. Can I trust it?

Seems you've found a bug in migration wizard, I follow your description and I was able to repeat issue on my machine. Can you please fill bug report at bugs.mysql.com? Appreciate. We'll do our best to fix that isse.
As workaround you can try this way - use Data Export to export only structure of your database. Run exported stricpt on target server. Then run migration wizard normally, but skip create target schema step. Let me know if that works for you.


By default, it is 50 seconds

Just set innodb_lock_wait_timeout as needed.