If you are going to continue logging data in this database, the last thing on earth you want to do is shrink the database file (and then perform index maintenance that will require it to grow again). Never mind that these shrink and grow operations will interfere with performance, and that the end result is not going to leave you much better off than when you started.

Since the file is only going to grow again, this is an extremely futile operation - much like drying off while still in the shower. What are you going to do with all that disk space you free up temporarily? Lease it out to another application until the database needs to grow again? Of course not. If the database grew to that size once, it will grow to that size again, but it is going to be much more efficient to reuse the space within the file without all this unnecessary shrink-grow-shrink-grow roller coaster of the file itself.

Even if you move the logging table to another database, you should do what you can to pre-allocate the data file to a size that will accommodate the amount of logging you want to save (be it a week, a month, what have you). Keep this database trim by purging data every day, and stop worrying about shrinking & re-indexing. If you size it right, there should always be some free space, but not excessive free space. And if you need to re-index (you shouldn't really, if your clustered index is datetime or otherwise monotonically-based), do so after the purge (when you have the most amount of free space), not after a shrink (when you have the least).

You can do what Mark suggests without introducing a new database to the application, or changing the application or its interface to the database at all (one important change, of course, would be the removal of any foreign keys or other database-dependent features). You can simply create your table(s) in the new database, then add an INSTEAD OF INSERT trigger to the table in the current database (I assume there are no updates to the logging table, but you may also need an INSTEAD OF DELETE trigger in case you don't directly control the process that performs the purging). That will help with writes, but you'll have to point reads elsewhere, since there are no INSTEAD OF SELECT triggers. Another alternative would be to rename the existing table and create a synonym or even a view that points to the new table.

If you're going to need to clean up the logs table that has grown, I would avoid a single atomic transaction like:

DELETE dbo.logs_table WHERE [datetime] < '20121201';

This will cause massive log growth and will take a long time. Instead you can break the cleanup into chunks, e.g.

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple: CHECKPOINT
  -- otherwise: BACKUP LOG

  BEGIN TRANSACTION;

  DELETE TOP (1000) FROM dbo.logs_table WHERE [datetime] < '20121201';
END

I picked 1000 and Dec. 1st arbitrarily, I don't know what fits your scenario best. The point is that you want to keep transactions short and contained, and prevent any long-term impact while you clean up the table. Another option I've used in the past, instead of deleting 99% of the junk in a table, move the 1% you want to keep to a new table and drop the old one.

BEGIN TRANSACTION;

SELECT *
  INTO dbo.new_logs_table
  FROM dbo.logs_table
  WHERE [datetime] >= '20121201'

COMMIT TRANSACTION;

-- create indexes/constraints/triggers on dbo.new_logs_table

BEGIN TRANSACTION;
  DROP TABLE dbo.logs_table;
  EXEC sp_rename N'dbo.new_logs_table', N'logs_table', N'OBJECT';
COMMIT TRANSACTION;

If you say the logs have never been purged, then you may very well be in a scenario where the database will be at a size that you don't anticipate you will ever need again (say, if you only intend to ever keep a week of logs at a time). In this case I might go along with a shrink operation, but only if truly necessary (e.g. you really do need the space for other purposes). A bunch of empty pages aren't going to affect your backups or other operations, and the pages will eventually be fully deallocated and reused.


Update all rows from a table with random foreign key from another table

Not going to go through what you're trying to accomplish, but for your update, you're using a TOP without an ORDER BY.  While SQL Server doesn't guarantee ordering for such queries, it doesn't just randomly choose, either.  If you want to force a random row to be returned, you'll need to order by a row-level random number.  For that, you can use ORDER BY ABS( CHECKSUM( NEWID() ) ).

Try it out against sys.objects to see the behavior of explicitly ordering randomly.

SELECT  TOP 1 name
FROM    sys.objects
ORDER BY ABS( CHECKSUM( NEWID() ) );

And good luck with whatever it is you're doing.

Edit:

@jean points out in the comments just NEWID() is all that is necessary, which is 100% correct, I just muscle-memory the ABS( CHECKSUM() ) since I tend to need row-level randomization with a % operator.

Edit 2:

So something like this, then?

UPDATE  t
    SET CategoryID = nt.CategoryID
FROM    dbo.Topic t
INNER JOIN (    SELECT  t.ID, ucl.CategoryID,
                        Ordinal = ROW_NUMBER() OVER (
                            PARTITION BY t.ID
                            ORDER BY NEWID() )
                FROM    dbo.Topic t
                INNER JOIN dbo.UserCategoryLink ucl
                    ON  t.UserID = ucl.UserID ) nt
    ON  t.ID = nt.ID
WHERE   nt.Ordinal = 1;


Sum all unique values from previous dates

Look for
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY date DESC) rn
              FROM source_table
              WHERE date_trunc('hour', date) = @needed_datetime_truncated_to_hour )
SELECT @needed_datetime_truncated_to_hour, SUM(amount)
FROM cte
WHERE rn = 1;


SQL Server 2012 backward compatibility for backups with 2008

There's two things involved here: the version number of the file, and the compatibility level.  When you attach a database to a newer major version (like from 2008 to 2008R2, or 2008R2 to 2012) the database version is changed permanently, and you can't attach that database to an older version again.

The compatibility level is for parsing old-school deprecated T-SQL that used to work in older versions of SQL Server.  It doesn't change how the data is stored on disk.

To give the database to someone on an older version of SQL Server, you'll need to export the data and import it into another database.  Tools like Red Gate's Data Compare are helpful for this.


Datediff SECOND with DATETIME type and rounding returns questionable results

Think of DATEDIFF as a truncation and a subtraction.

First the given datetimes are truncated to whatever datepart is chosen. In your case this is SECONDS. So '20200203 06:34:50.100' becomes '20200203 06:34:50' and '20200203 06:34:50.600' becomes '20200203 06:34:50'. Subtract these and the difference is zero - there are zero second-sized time boundaries between the two given inputs.

Note that this is truncation and not rounding, and that the answer depends on the granularity of the datepart chosen. It can be confuing. The difference between '20191231 23:59:59' and '20200101 00:00:00' using YEAR, MONTH, DAY, HOUR, MINUTE and SECOND are all 1.

If you want to report to a precision of one tenth of a second you must calculate to the next most precise datepart (millisecond) and ROUND() the result.


How does the Microsoft tablediff utility work?

I'm not aware of authoritative documentation of the fact that the current version of the tablediff utility uses a checksum to identify data differences. However, I was able to verify this by capturing the queries executed by the utility using a sql_batch_completed event trace. The trace showed a query is run against each table, returning only the primary key value(s) along with the T-SQL BINARY_CHECKSUM over all table columns.

This is the T-SQL script I ran on my local SQL instance to the create tables and trace:

USE tempdb;
DROP TABLE IF EXISTS dbo.table1;
DROP TABLE IF EXISTS dbo.table2;
CREATE TABLE dbo.Table1(Col1 int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY, Col2 int);
CREATE TABLE dbo.Table2(Col1 int NOT NULL CONSTRAINT PK_Table2 PRIMARY KEY, Col2 int);
INSERT INTO dbo.Table1 VALUES
      (1, 1)
    , (2, 2)
    , (3, 3);
INSERT INTO dbo.Table2 VALUES
      (1, 1)
    , (2, 0)
    , (4, 4);

While viewing the trace in SSMS (Object Explorer: right-click Management-->Extended Events-->Sessions-->sql_batch_completed and select Watch Live Data), I ran this from the command prompt:

"C:\Program Files\Microsoft SQL Server\140\COM\tablediff" -sourceserver "." -sourcedatabase "tempdb" -sourceschema "dbo" -sourcetable "Table1" -destinationserver "." -destinationschema "dbo" -destinationdatabase "tempdb" -destinationtable "Table2"

These were the relevant queries executed by tablediff:

SELECT [dbo].[table1].[col1],BINARY_CHECKSUM([dbo].[table1].[col1],[dbo].[table1].[col2],[dbo].[table1].[col3]) as MShash_54267293 FROM [dbo].[table1] WITH (READUNCOMMITTED) ORDER BY [dbo].[table1].[col1]

SELECT [dbo].[table2].[col1],BINARY_CHECKSUM([dbo].[table2].[col1],[dbo].[table2].[col2],[dbo].[table2].[col3]) as MShash_54267293 FROM [dbo].[table2] WITH (READUNCOMMITTED) ORDER BY [dbo].[table2].[col1]


Combine column from multiple rows into one row in SQLITE

You can use the GROUP_CONCAT() function:

SELECT a.Name               AS ArticleName
     , GROUP_CONCAT(t.Name) AS TagList
FROM Articles AS a
  LEFT JOIN ArticlesTags AS at
    ON at.ArticleId = a.Id
  LEFT JOIN Tags AS t
    ON t.Id = at.TagId
GROUP BY a.Id, a.Name ;


I found this blog post that answered the question: https://blog.pythian.com/enabling-binary-logging-rds-read-replica/

Essentially, if you make sure your "Backup retention period" for your RDS instance is set to > 0 days, this will enable binary logs.


What's the difference between a thread vs. a process?

MySQL has been implemented on a lot of Operating Systems -- From Atari to Windows to Solaris to Linux.  Different OSs work differently when it comes to "processes" versus "threads".  MySQL picked whichever was better in each case.  Then it got sloppy in saying "THREADS_runnng" and "show PROCESSlist".

So, in MySQL context, "threads" and "processes" are synonymous.

In Operating System context, they are different enough to necessitate multiple courses in Computer Science.

In MySQL, one connection uses one thread (or process).  There is also a main thread (process) for coordinating between the connections.  Also, InnoDB has some extra threads (which may actually be threads and not processes?) to deal with background I/O tasks.


Oracle flashback query syntax - all tables to same timestamp

Look at the DBMS_FLASHBACK package and the ENABLE_AT_TIME procedure (12.1 Documentation).  This allows you to do something like the following:

execute DBMS_FLASHBACK.Enable_At_Time(TIMESTAMP '2016-11-01 02:00:00');

The session will then run all queries as of the SCN most closely matching the provided timestamp.

SELECT t1.Field1, t2.Field2
FROM table1 t1
JOIN table2 t2 ON t1.somekey = t2.somekey

To stop doing flashback queries as of the set timestamp, simply disable it.

execute DBMS_FLASHBACK.Disable;


Handling MySQL length=22 and decimals=31

Do not use (m,n) on FLOAT or DOUBLE.  It is virtually useless and leads to double-roundings.

DOUBLE is an IEEE-754 "Double", which includes 53 significant bits and a range that tops out at that number given (over 1.E+308).  This is universal to nearly all languages, and machines.

There are virtually no machines / OSs today that deviate from IEEE754 in the representation of numbers.  In theory, you could see differences, but I doubt that you will.

The "22" in information_schema.COLUMNS.numeric_precision may come from how many characters it takes to display (but even that does not make sense).  I don't know where you got the "31".


From InnoDB perspective there are no partitions. Partitioning is implemented on the server level and for InnoDB each partition looks like a separate table.

You're going right direction for optimizing writes. If you partition by time the most recent partition will be one that gets new writes. It will be cached in the buffer pool and that'll be good for performance. If the partition is small enough to fit into the buffer pool you will see no reads and there will be as many writes as commits (if innodb_flush_log_at_trx_commit=1) plus some amount for flushing dirty pages. That'll be your capacity limit.

Before you partition do a research with your read queries. The secondary index was created for some reason and I assume the read queries benefit from it.
After you partition, and if the read query doesn't change, MySQL will have to do as many index searches as number of partitions. So, the read query will be N times slower (I assume an index search time on a partition and the big table is same  which is not strictly true, but ok for rough estimation).

Use EXPLAIN PARTITIONS SELECT... to see what partitions MySQL hits to execute the query. If you can change the SELECT so it hits only one partition (by adding PK clause) that'd be ideal situation.


Help defining the correct relationship between two tables

simply you should have these Tables

Customer: Which Represents some information about your Clients
Order: Which Represents Information about the Orders your customers are making, of course you should have a CustomerID field in this table which references Customer(CustomerID)
Table:  each Restaurant can have some Tables which you are storeing information about Tables in this [Database table]
OrderTables : in order to Relate Orders with Tables you should use  this Table which you will have a structure as {OrderID , TableID} in this Table
so it will be a one-to-many Relation


Frequency of SQL DB Full and Differential backups

The backup policies are actually based on your RPO. But here is my suggestion.
 Use Ola Hallengren's backup script.

If your total size of the DB is < 1 TB

Full Backup - daily 1AM - Retention 7 Days
Differential Backup - Every 12 Hours - Retention 3 days.
Log Backup - Every 15mins - Retention period 1 day.

If your total DB size > 1TB

Full Backup - Weekly once
Differential Backup - Daily once/twice (Based on the database load)
Log Backup - every 15min/30mins


One way is to create a table promotions as:

id (PK)
start_date
end_date
discount_amount
discount_percentage
condition

To determine the promotions the product qualifies for, you would need to dynamically execute all the queries, and select sum(discount_amount) and sum(discount_percentage). Here is a snippet that can help you get started:

SET @SQL = CONCAT('SELECT id FROM products WHERE ', @condition, ' INTO @product_id');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You can put this snippet into a function that takes condition and returns product_id.

Running all those queries every time you SELECT a product will probably end up very time consuming. So you might want to create a table product_discounts, with two foreign keys to both products and discounts, to facilitate the many-to-many relationship between these two tables.

Then you insert and delete rows from product_discounts using either events (daily recalculation of entire relationship) or triggers on the two tables, or a combination of events and triggers.

I hope this helps.


Postgres has Ordered-Set Aggregate Functions for your purpose.

The special difficulty: you want rows "weighted" by cnt. If that's supposed to mean that each row represents cnt identical rows, you can multiply input rows by joining to generate_series(1, cnt):

SELECT site, dateval
     , percentile_cont('{0,.25,.5,.75,1}'::float8[]) WITHIN GROUP (ORDER BY raw)
FROM   source s, generate_series(1, s.cnt)
GROUP  BY 1, 2;

db<>fiddle here

But results differ from your expected output (except for the 0 and 100 percentile). So you are "weighting" differently ...

Aside, your original query can be simplified to this equivalent:

SELECT site, dateval, raw, sum(cnt) AS sumcnt
     , sum(sum(cnt)) OVER w AS cumsumcnt
     , sum(sum(cnt)) OVER w / sum(sum(cnt)) OVER (PARTITION BY site, dateval) AS percentile
FROM   source
GROUP  BY site, dateval, raw
WINDOW w AS (PARTITION BY site, dateval ORDER BY raw);

You can run a window function over the result of an aggregate function in the same SELECT (but not vice versa). See:

Need separate columns that calculate percentage

I added a demo to the fiddle above.

But neither explains the odd numbers in your "expected results". Those strike me as incorrect, no matter how you interpolate. Example: 22.07 in the first line for p25 does not seem to make sense - the value 23 occupies all rows up to the 27.7879 percentile after factoring in cnt according to your own query ...


Select parent rows and include cost of children

That ability was available in Oracle 7; maybe earlier. ( doc )

Use a hierarchical query (in a CTE/sub-query) to find the children and grandchildren.
SUM() the values when you GROUP BY the parent wonum.

with workorder as (
    select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
    union all
    select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
    union all
    select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
), hier as (
    select connect_by_root( wonum ) parent_wonum
        ,level lv
        ,a.*
    from workorder a
    start with a.parent is null
    connect by a.parent = prior(a.wonum)
)
select parent_wonum
    ,listagg(wonum,',') within group (order by lv, wonum) list_wo
    ,sum( actlabcost ) actlabcost
    ,sum( actmatcost ) actmatcost
    ,sum( actservcost ) actservcost
    ,sum( acttoolcost ) acttoolcost
from hier
group by parent_wonum


Based on the additional details provided, I would recommend using staging tables versus temporary tables.

The benefits are:

If there is an error, you can view the data as it was mid process for troubleshooting more easily. You can also more easily control the existence/deletion data (in regards to GDPR) rather than it being in the sometimes nebulous tempdb.
You can simplify the code by utilizing persistent tables and just truncating them after each successful processing.
You won't be thrashing your tempdb and possibly affecting other things relying on the tempdb at the same time.
As Jonathon mentioned, it gives you a more stable tempdb size that isn't inflated just because of a 1x a week process.
The schema more clearly illustrates how the data is used, instead of doing a lot of data processing under the veil of tempdb. This is more of a personal preference, but I think it would be more clear for future maintenance / maintainers.
I can't think of any huge downsides to this approach, unless you have way better disks for tempdb and will suffer by using the drives for a normal database.

Downsides provided by David Browne:

"The downsides are that writing to user databases generates more IO, as Tempdb is allowed to not flush tables and log records to guarantee recoverability. And this is exacerbated if your database is in Full recovery, and further if using an AG"

If you go this route, a common choice is to also segregate the data to a separate staging database altogether, which might help mitigate some of the negatives as this wouldn't have to be in an AG or in Full recovery mode necessarily.


You can backup on any secondary replica if they are connected to the primary replica AND the state is either SYNCHRONIZED or SYNCHRONIZING.

It seems that you should check the connection to the primary replica. Please take a look in the sql server's error log.


This is where it's located:

myuser@mysolarishost:mydbinstance:/usr/local/bin
> ls -l oraenv
-rwxr-xr-x   1 oracle   root        7012 Jan 11 11:15 oraenv


What is the easiest way to backup PostgreSQL and send backups to Google Drive?

You could try:

https://github.com/andreafabrizi/Dropbox-Uploader

to send your pg_dump output to Dropbox. I got this by Googling script load file dropbox. You may find some of the other scripts more to your liking.

Remember the dba's motto - backup or f*ckup and test, test and test again. Personally, I would recommend a local (speed of recovery) and remote (double-sure) copy (as the Americans say, "belt and suspenders").

It is a bash tool, but then I always assume PostgreSQLers are on a decent operating system :-).

Update - the bash shell can now be used natively on Windows as well since Microsoft appear to be doing more embracing and less smothering of *nix tools!


Basic question about grouping and counts

By my reading of your question, you want the numbers of distinct users who have been logged, broken down by each user's role.

Your query returns the number of log entries, broken down by role.

To get distinct users, we'd need to get a COUNT(DISTINCT x) on some column x, which represents users.

If we used a column from test1_users, we'd just get the count of users assigned to each role, without reference to whether or not the user had associated entries in the log.

If we use an INNER JOIN, we'll only count users who do have entries in the log - but we won't see any roles where no user exists in the log (as is true for "Clerk").

However, there's a columns in test1_log that's specific to a user - test1_log.user_id.

So, if we modify the query to be:

SELECT role, COUNT(DISTINCT l.user_id) AS total FROM test1_users u
LEFT JOIN test1_log l ON u.id = l.user_id
GROUP BY role;

we get the results you request (as you can see in this SQLFiddle.


Missing Non Clustered Index already part of Clustered Index

The table Retailer_Relations has the following composite PK index and
  suggested index-

While missing indexes could be helpful and could definitely work, I would not spend too much time on missing indexes, these hints are created on the estimated execution plan, not on the actual execution plan.

More precisely, these index hints are based on the premise of reducing the cost of Query Bucks™ used by operators in the plan. The optimizer calculates the estimated costs, and adds missing index hints accordingly.

As a result they could be very wrong. If you are unsure if it is going to help, the best thing to do is test the situation before and after.
You could do this by adding the statement
SET STATISTICS IO, TIME ON; before running the query.

Also, you could use statisticsparser to make it easier to read these statistics.

  Could this be because of the order of the columns in the index?

That is correct, creating the missing index can improve the selectivity on queries, for example if your query looks like this:

SELECT  RelatedRetailerID
FROM Retailer_Relations
WHERE
RetailerID = 5 AND
RelationType = 20;

or like this:

SELECT  RelatedRetailerID
FROM Retailer_Relations
ORDER BY
RetailerID,
RelationType;

The reasoning behind this is that both indexes could seek on RetailerID, that part is not  going to change.
But what if extra filters/ordering is applied on RelationType?
It would be all over the place in the clustered index, as a result of it being the third key value, not the second key value. And as we know, it is the second key value in the NCI.

Okay, but when or how would the nonclustered index improve the query?

A couple of cases could be:

If relationType filters a lot of values, the residual I/O could be
high, resulting in the possible need of the nonclustered index (Query #1)
Ordering on the two columns occurs (One way), and the resultset is
large (Query #2).
As @AaronBertrand mentioned: if the CI size difference compared to the NCI is of a considerable amount, adding the NCI will reduce the pages read by queries that benefit from it.

NCI Side note

As a side note, adding the key columns to the include list in your NCI is not exactly needed, since CI key columns are automatically included in all Non clustered indexes.

You could opt to do so if you are not sure if the clustered index will remain the same, and want the column to always be included.

Regarding the query itself, if you added the execution plan via  PasteThePlan we could give some more information on indexing / improving the query.

Testing

Create table and add some rows

CREATE TABLE Retailer_Relations (
    RetailerID int ,
    RelatedRetailerID int ,
    RelationType smallint,
    CreatedOn datetime,
    CONSTRAINT PK_Retailer_Relations
PRIMARY KEY CLUSTERED (
    RetailerID ASC,
    RelatedRetailerID ASC,
    RelationType ASC
    ) ON [PRIMARY])

    DECLARE @I Int = 1
    WHILE @I < 1000
    BEGIN
    INSERT INTO Retailer_Relations(RetailerID,RelatedRetailerID,RelationType,CreatedOn)
    VALUES(@I,@I,@I,GETDATE()
    )
    set @I += 1
    END

Query #1

    SELECT  RelatedRetailerID
FROM Retailer_Relations
WHERE
RetailerID = 5 AND
RelationType = 20;

Plan without index Here

While it is doing a seek, it is doing a seek on RetailerID. Afterwards it is issueing a  residual I/O predicate on RelationType

Add the index

CREATE NONCLUSTERED INDEX IX_TEST
ON Retailer_Relations (
    RetailerID,
    RelationType
    )
INCLUDE (
    RelatedRetailerID
    )

The residual predicate is gone, everything happens in a seek predicate, on both columns.

Execution plan

With the second query, the added index helpfullness becomes even more obvious:

SELECT  RelatedRetailerID
FROM Retailer_Relations
ORDER BY
RetailerID,
RelationType;

Plan without the index, with a Sort operator:

Plan with the index, using the index removes the sort operator


Reporting Services Database on different version

According to Docs, you can run a version of SSRS and host the database on lower versions of SQL Server, but there doesn't appear to be explicit support for running the DB on higher versions.


Refactoring database after migrating table

Replace the tables in the old database with synonyms that point to the location in the new database.

This not only minimizes the code change, it gives you a single place to manage the references to objects in other databases.


PostgreSQL select count with dynamic date range

Postgres allows to generate a series of dates using generate_series() function.

select generate_series(createdAt, current_date,
       (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
       , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
from   users
where  id = 1;

It returns:

ddate                   | ddays
:---------------------- | :-----
2018-03-01 13:02:20.904 | 2 days
2018-03-03 13:02:20.904 | 2 days
2018-03-05 13:02:20.904 | 2 days
2018-03-07 13:02:20.904 | 2 days
2018-03-09 13:02:20.904 | 2 days
2018-03-11 13:02:20.904 | 2 days
2018-03-13 13:02:20.904 | 2 days
2018-03-15 13:02:20.904 | 2 days
2018-03-17 13:02:20.904 | 2 days
2018-03-19 13:02:20.904 | 2 days

Then you can use previous query to count friends between this intervals:

with x as
(
    select generate_series(createdAt, current_date,
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       (select count(*)
        from   friends
        where  createdAt >= ddate
        and    createdAt <  ddate + ddays) friends
from x;

Final result:

ddate                   | friends
:---------------------- | ------:
2018-03-01 13:02:20.904 |       0
2018-03-03 13:02:20.904 |       0
2018-03-05 13:02:20.904 |       0
2018-03-07 13:02:20.904 |       1
2018-03-09 13:02:20.904 |       0
2018-03-11 13:02:20.904 |       0
2018-03-13 13:02:20.904 |       0
2018-03-15 13:02:20.904 |       1
2018-03-17 13:02:20.904 |       0
2018-03-19 13:02:20.904 |       2

Or if you prefer a join instead of a correlated scalar subquery:

with x as
(
    select generate_series(createdAt, current_date,
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends
from   x
left join   friends
on     createdAt >= ddate
and    createdAt <  ddate + ddays
group by ddate
order by ddate;

This last option can be further modified to returned running totals, as per your expected output:

with x as
(
    select generate_series(createdAt, current_date,
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends,
       sum(count(friends.*)) over (order by ddate) as friends_total
from   x
left join   friends
on     createdAt >= ddate
and    createdAt <  ddate + ddays
group by ddate
order by ddate;

Result:

ddate                   | friends | friends_total
:---------------------- | ------: | ------------:
2018-03-01 13:02:20.904 |       0 |             0
2018-03-03 13:02:20.904 |       0 |             0
2018-03-05 13:02:20.904 |       0 |             0
2018-03-07 13:02:20.904 |       1 |             1
2018-03-09 13:02:20.904 |       0 |             1
2018-03-11 13:02:20.904 |       0 |             1
2018-03-13 13:02:20.904 |       0 |             1
2018-03-15 13:02:20.904 |       1 |             2
2018-03-17 13:02:20.904 |       0 |             2
2018-03-19 13:02:20.904 |       2 |             4

dbfiddle here


MongoDB durability and consistency with j= true and w:majority

This is not how it works. With j:true and w: majority, w1 would only be acknowledged when the majority of nodes actually had reported to the primary that they have put the operation to the journal. Since the primary went down, this acknowledgement would never happen. So in this edge case, you would only have to do what you'd need to do what you always need to do if a failover happens before a write operation is persisted: redo it.

Let us expand the example a bit: let us assume that the write was successful, and then the nodes which already received the writes minus one go down. Now, an election takes place. The new primary is elected basically by each remaining node announcing the latest timestamp they have in the oplog. So, the node which received w1 would win (since the oplog is processed consecutively for each entry) and the remaining nodes would start to process the oplog from their latest position to the new primaries latest position - w1 will be replicated and hence it will be eventually consistent on all nodes.


You did not save the files where you think you did.

In the first image there are 9 folders below 'Microsoft SQL Server' and above 'MSSQL11.MSSQLSERVER'  in the second screen shot there are only 5.

This is possibly an issues with your local machine VS a seperate server. Assuming the address you are following are identical (C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup) the only solution remaining is that the C drives are on different machines.


You should use dbatools - Copy-DbaCentralManagementServer

This command copies all groups, subgroups, and registered servers from one SQL Server to another. By default, all objects are copied.
To migrate all groups, subgroups, and server instances are copied from sqlserver’s Central Management Server to sqlcluster’s Central Management Server.

Copy-DbaCentralManagementServer -Source sqlserver -Destination sqlcluster


Why does mongodb fail my json schema validation?

As I am able to see you are getting the insert writeError with Error code : 121 like that

"nInserted" : 0,
    "writeError" : {
        "code" : 121,
        "errmsg" : "Document failed validation"

As per MongoDB blog documentation of @Jai Hirsch here for MongoDB document validation.
Sometimes you want completely free form documents and sometimes you don’t. In the 3.2 release of MongoDB the idea of document validation was introduced.
One area I have encountered problems in the past is with dates being inserted using different data types.

Example: (command line notation used for brevity)
The first team was using numeric dates:

 db.date.insert({date:20150321})

The second team was using string format:

db.date.insert({date:"03/21/2015"})

The third team was using ISO dates:

 db.date.insert({date:new Date('Mar 21, 2015')})

The communication issues that lead to this problem is a story for another time, but needless to say a fair amount of data cleanup was required to fix the issue as the primary application that read the data was expecting an ISO date.  The code issue was solved by using a shared library that defined the database mapping object.  Now let’s take a look at how document validation could be used to solve this problem.

We will create a collection with the following command:

db.createCollection( "date", {
    validator: { date: {$type:"date"}}
} )

With the collection created with the validator one can now only insert using the new Date() format:

db.date.insert({date:new Date('Mar 21, 2015')})

The numeric and string inserts will give the following error:

WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 121,
        "errmsg" : "Document failed validation"
    }
})

likewise almost every developer new to MongoDB will make this "mistake" on the command line like:

db.numbers.insert({number:2})

One would think that they have inserted a document containing the integer number two into the collection, when in fact the document now contains a floating-point value.

I can attest to the pain this can cause (especially if you are using a strongly typed language such as JAVA).

We can use the validator to safeguard against this:

db.createCollection( "numbers", {
    validator: { number: {$type:"int" }}
} )

The preceding insert will now give us the 121 error code and you need to use the NumberInt constructor:

db.numbers.insert({number:NumberInt(2)})


Everything needs to be on one line...not multiple so we humans can read it....same as if you were typing it into SQLCMD.  The second you hit return it starts executing from there.


Linking Two database to execute join query

You could make use of FEDERATED Tables. More on them: HERE.

Basically, what you do, is to create a federated table on your local DB, based on the remote table (example in the link). Then you can join them, as they're both local. Everything is pretty straightforward, and explained in the link above.


Why does this logon trigger work in one server but not another?

Assuming your trigger is enabled, I am surprised at your trigger working at all.

Usernames are case-insensitive by default and treated as uppercase strings internally. If you create a user called mytestro or mytestrw, the actual usernames will be MYTESTRO and MYTESTRW. Normally, a condition like the one in your if statement, will be false.

Sure, it is possible to create case-sensivite, lowercase usernames, but that is quite rare and I consider it bad practice, because it has no benefits, it just makes administration cumbersome.

Instead of:

create user mytestro ...

You can use:

create user "mytestro" ...

The former creates a user called MYTESTRO. The latter creates a user mytestro. These are not identical.

The first one behaves as:

SQL> show user
USER is "MYTESTRO"

While the second one behaves as:

SQL> show user
USER is "mytestro"

You can use the above to check the actual usernames when logged in (or select user from dual if your client does not support show). Your trigger in its current form does not work with the first user above, and does work with the second user above.


Why does this Entity Framework query perform so bad in MySQL?

Step 1 - get an explain plan, in particular EXPLAIN EXTENDED will show you what SQL the query optimizer actually generated. Maybe additional indexes will help - but if you "outsource" your SQL generation then you really are at the mercy of whatever was easier for the ORM developer, not what was best for your application. Another thing you might try is in-memory temp db, as this can help with sorts, and derived tables, which you do have...


Table was not locked with LOCK TABLES when recreating an index

Try creating a temp table, and then perform a bait-and-switch

ALTER TABLE stuff RENAME oldstuff
CREATE TABLE newstuff LIKE oldstuff;
ALTER TABLE newstuff DROP INDEX idx_UpdatedTime;
INSERT INTO newstuff SELECT * FROM oldstuff;
INSERT INTO newstuff (x,y,z) SELECT x,y,z FROM inventory;
ALTER TABLE newstuff ADD INDEX idx_UpdatedTime(UpdatedTime);
ALTER TABLE newstuff RENAME stuff;
DROP TABLE oldstuff;

This approach may take longer but should establish some continuity for your processing

I renamed stuff to oldstuff to remove any possibility access to the stuff table during this process


launch database mirroring monitor grayed out in SQL server 2014

Had the same problem - turns out if the first DB in the list is in restoring (mirroring) state that server will have this issue.  Solved it by adding an empty DB "aaa" as the first DB in the list.


Debug why Postgres wants to use an uninstalled extension for a function

Why is Postgres trying to look in the intpair extension for the app_user_id function instead of looking in heap_exts?

It sounds like you still have (or had) an entry in pg_proc for app_user_id(bigint, bigint) which points to the old function definition (probin would be $libdir/intpair).
Assuming there is an entry there, I'd guess it was somehow not properly associated with the old intpair extension (maybe manually added, or the code pre-dates PostgreSQL's extension functionality and the function was missed from CREATE EXTENSION FROM unpackaged or something) so was not removed when the extension was dropped, so is still present in your restored instance - but the associated extension library file no longer exists, of course.
The question is whether that pg_proc entry has somehow ended up associated with your new extension, or you have two entries in pg_proc for the function.
What does this modified version of your above query report?
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema, p.probin
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
  AND p.proname = 'app_user_id'
ORDER BY 1, 3;


Why do two identical strings have a different length but the same binary value?

Good job investigating so far. Some initial notes:

I wouldn't worry about that function from the S.O. answer.
RTRIM and LTRIM only trim spaces, not white-space in general:

SELECT RTRIM('A    ') + 'a';
-- Aa

SELECT RTRIM('A    ' + CHAR(9)) + 'a'; -- CHAR(9) = tab
-- A        a

Adding GROUP BY (2nd query) doesn't change that query since it was implied in the first query ;-).
Whether 0xA000 is 2 VARCHAR characters or 1 NVARCHAR character, there does not seem to be any special behavior with this sequence of bytes for either datatype, using either Latin1_General_CI_AI or Latin1_General_100_CI_AI.

Still, something is amiss. You simply cannot have different length (or different anything) with the same binary value. How was length determined: LEN or DATALENGTH? Is it possible that the value got truncated somewhere in the testing so that it then appeared to be the same?

In order to help further, we need to know two things (please update the question with the results):

The exact datatype of the department_name. Please find via:

SELECT typ.[name], col.*
FROM   sys.columns col
INNER JOIN sys.types typ
        ON typ.[user_type_id] = col.[user_type_id]
WHERE  col.[object_id] = OBJECT_ID(N'dbo.departments')
AND    col.[name] = N'department_name';

The output of the following query:

SELECT dept.department_name,
       LEN(dept.department_name) AS [name_chars],
       DATALENGTH(dept.department_name) AS [name_bytes],
       CONVERT(VARBINARY(MAX), dept.department_name) AS [name_hex]
FROM   dbo.departments dept
ORDER BY dept.department_name

Just find a set of rows for a name that looks the same but is showing duplicates via DISTINCT.

For the moment, I can say that if you have character 0 (ASCII value 0, Code Point U+0000, CHAR(0), NCHAR(0)) in your values then appearances can be deceiving since char(0) is the "null terminator" for strings. So neither it nor anything after it will be displayed, but it and everything after are still part of the string:

DECLARE @Test TABLE
(
  [Something] VARCHAR(50) COLLATE Latin1_General_CI_AI
);

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'feel like a nut');

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'feel like a nut');

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'don''t');

SELECT DISTINCT [Something],
       LEN([Something]) AS [Something_chars],
       DATALENGTH([Something]) AS [Something_bytes]
FROM   @Test;
/*
Something        Something_chars    Something_bytes
Sometimes you    19                 19
Sometimes you    29                 29
*/

Enjoy: Peter Paul Almond Joy & Mounds - "Feel Like A Nut" (1980)

TEMPORARY UPDATE (WAITING FOR QUESTION TO BE UPDATED WITH QUERY OUTPUT):

Based on this info from a comment:

  I used both LEN and DATALENGTH to perform the check. For two exact same strings I got (20,40) and (21,42) respectively.

it is clear that:

these are definitely different values, and
the datatype of the column is NVARCHAR (since DATALENGTH is twice LEN)

That means that the 0xA000 value is a single, UTF16LE character. Due to being Little Endian (bytes in reverse order), the actual Code Point is U+00A0. That character is:

No-Break Space

As in our favorite HTML character: &nbsp;

All you need to do is remove those characters on the way into the DB, using:

REPLACE(@InputParam, NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'')

For example:

SELECT CONVERT(VARBINARY(MAX),
      REPLACE(N'test' + NCHAR(0x00A0), NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'')
    );

To be clear, all of that work messing with VARBINARY and VARCHAR, etc is unnecessary.

  But this won't work long term as users can update this table and I would need to adjust every query to do a replacement in the WHERE clause.

True, updating every WHERE clause is not a workable solution. This is why you need to sanitize the input on the way in. There are a limited number of entry points for the data (INSERT / UPDATE procs for the UI, possibly some ETL processes), so it shouldn't be that bad. You can request that the developers strip out "bad" characters before they call the stored procedure(s), but there is no guarantee that they will, or that new code will, or that things won't change later, or that they will be able to fix ETL processes, etc.


SQL ssis delete imported rows from source table

Since you're working with SQL Server 2008, you can take delete first approach as I outlined on this SO answer https://stackoverflow.com/a/15508174/181965

A option, that might sound stupid but it works, is to delete first and use the OUTPUT clause.

I created a simple control flow that populates a table for me.

IF EXISTS
(
    SELECT 1 FROM sys.tables AS T WHERE T.name = 'DeleteFirst'
)
BEGIN
    DROP TABLE dbo.DeleteFirst;
END

CREATE TABLE dbo.DeleteFirst
(
    [name] sysname
);

INSERT INTO
    dbo.DeleteFirst
SELECT
    V.name
FROM
    master.dbo.spt_values V
WHERE
    V.name IS NOT NULL;

In my OLE DB Source, instead of using a SELECT, DELETE the data you want to go down the pipeline and OUTPUT the DELETED virtual table. Somethinng like

DELETE
    DF
OUTPUT
    DELETED.*
FROM
    dbo.DeleteFirst AS DF;

It works, it works!


Generate unique id based on criteria from multiple rows

One way to approach this problem is to concatenate all of the item values together for each PID and to assign a value to them using the DENSE_RANK window function. Unfortunately, SQL Server does not make it easy to do string concatenation at an aggregate level until STRING_AGG() in SQL Server vNext. Since you're on SQL Server 2008 I'm going to use the FOR XML path method of string aggregation. An explanation of that method along with other ways of doing it can be found in Grouped Concatenation in SQL Server.

Data prep using your sample data:

CREATE TABLE #X165719 (
PID INTEGER NOT NULL,
ITEMID VARCHAR(10) NOT NULL
);

INSERT INTO #X165719
VALUES
(123, 'item1'),
(123, 'item2'),
(234, 'item1'),
(234, 'item3'),
(456, 'item1'),
(456, 'item2'),
(567, 'item1'),
(567, 'item2'),
(567, 'item3');

First let's do the string aggregation. One implementation is as follows:

SELECT
  PID
, ITEMID
, (
    SELECT ',' + SUB.ITEMID
    FROM #X165719 SUB
    WHERE SUB.PID = t1.PID
    ORDER BY SUB.ITEMID
    FOR XML PATH (''), TYPE).value('.', 'varchar(max)') all_items
FROM #X165719 t1

You should pick a delimiter that doesn't appear in your source data if possible. I used a comma. Here is what the result set looks like:

╔═════╦════════╦════════════════════╗
║ PID ║ ITEMID ║     all_items      ║
╠═════╬════════╬════════════════════╣
║ 123 ║ item1  ║ ,item1,item2       ║
║ 123 ║ item2  ║ ,item1,item2       ║
║ 234 ║ item1  ║ ,item1,item3       ║
║ 234 ║ item3  ║ ,item1,item3       ║
║ 456 ║ item1  ║ ,item1,item2       ║
║ 456 ║ item2  ║ ,item1,item2       ║
║ 567 ║ item1  ║ ,item1,item2,item3 ║
║ 567 ║ item2  ║ ,item1,item2,item3 ║
║ 567 ║ item3  ║ ,item1,item2,item3 ║
╚═════╩════════╩════════════════════╝

Now we need to assign a different number for each unique value in the all_items column. One way to accomplish this is with the DENSE_RANK function. Quoting from BOL:

  Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

The final query is:

SELECT
  t2.PID
, t2.ITEMID
, DENSE_RANK() OVER (ORDER BY all_items) COMB
FROM
(
    SELECT
      PID
    , ITEMID
    , (
        SELECT ',' + SUB.ITEMID
        FROM #X165719 SUB
        WHERE SUB.PID = t1.PID
        ORDER BY SUB.ITEMID
        FOR XML PATH (''), TYPE).value('.', 'varchar(max)') all_items
    FROM #X165719 t1
) t2;

The final result set is:

╔═════╦════════╦══════╗
║ PID ║ ITEMID ║ COMB ║
╠═════╬════════╬══════╣
║ 123 ║ item1  ║    1 ║
║ 123 ║ item2  ║    1 ║
║ 456 ║ item1  ║    1 ║
║ 456 ║ item2  ║    1 ║
║ 567 ║ item1  ║    2 ║
║ 567 ║ item2  ║    2 ║
║ 567 ║ item3  ║    2 ║
║ 234 ║ item1  ║    3 ║
║ 234 ║ item3  ║    3 ║
╚═════╩════════╩══════╝


Unable to display parent-child heirarchy

I just had to change the Employee Display NameColumn to LastName.
And it worked!!! Thanks!!


https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm
impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
SQLFILE=dpump_dir2:expfull.sql


Moving a 10g database to 11g with rman backup

After alter database open resetlogs upgrade the database should be open.This statement replaces the startup upgrade command.  If this kind of upgrade is possible than you are after the startup upgrade step in the upgrade procedure and you should continue after this step. But you should have prepared the database in the way as described in the steps before startup upgrade.

I never tried this way to upgrade. You should do this kind upgrade only if you are very experienced with Oracle know exactly what you are doing. Otherwise you should choose an upgrade path that is recommended by oracle like Database Upgrade Assistant or a method like import/export. Check the manual


The ALTER TABLE statement conflicted with the FOREIGN KEY constrain

Try this:

BEGIN TRANSACTION

DELETE a
FROM STEP_RESULT a
WHERE NOT EXISTS (
        SELECT *
        FROM UUT_RESULT
        WHERE ID = a.UUT_RESULT
        )
--commit    --verify rowcount before commit


MariaDB remote access ERROR 1045 (28000): Access denied

The problem was simple, the password in the command line has a special character, the dollar sign $ which is not escaped by the shell unless in single quotes. So the proper command is:
$ mysql -utheuser -p'mypass' -hremote_ip

Nonetheless, security issues are arises when exposing the password in the shell:

6.1.2.1 End-User Guidelines for Password Security
MySQL users should use the following guidelines to keep passwords
secure.
When you run a client program to connect to the MySQL server, it is
inadvisable to specify your password in a way that exposes it to
discovery by other users. The methods you can use to specify your
password when you run client programs are listed here, along with an
assessment of the risks of each method. In short, the safest methods
are to have the client program prompt for the password or to specify
the password in a properly protected option file.


Data Warehouse how normalized for tracking metadata changes?

The short answer is: define your end result ( requirement) and work backwards from there.

Normalization goes out the window pretty quick with a DW schema.

In my case we have 3 schemas for our ETL.

Staging: Tables are extracted into our DW exactly as they appear in the source system plus a few columns for meta data as you describe. The key one being the extract time.
Transform: These tables have exactly the same schema as the end result dimensions and fact tables. At this point we are denormalising data from an OLTP schema into a DW star schema. Again it has fields for ETL meta data. The original extract datetime can travel with the records.
Store: This our end result, a DW star schema which is read by our OLAP cube. To Load our data we compare the transform with the final datastore tables. New rows get inserted. If a row has changed we retire the old and insert the new. To do this we have fields for is_current,  valid_from datetime and valid_to datetime.

Technically we dont need 3 schemas. It's just a convention, and it allows us to keep things tidy.

Read about Kimball methodology and Slowly Changing Dimensions. Type 1 or 2 etc. You will see examples of what fields you will need to store the meta data required.

Talk to your business about what they want or need the DW to be capable of. Whether you need SCD's or not will have a significant impact on your schema, meta data required and ultimately what level of normalisation that is possible.


Adding Timestamp to Exported Filename in SQL Server Management Studio

This gives date and time both and make it windows compatible filename as well

DECLARE @dt AS VARCHAR(30)
SELECT @dt = REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-')
EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout "C:\filename-' + @dt +'" -T -c -t, '


Error 9001, Severity: 21, State: 1 The log for database 'DB_NAME' is not available

These symptoms sometimes occur when AutoClose in database settings is turned on. Deactivate it and try again. I had the same errors with 2008R2 Express Edition vor a Sharepoint Foundation 2010 database instance a short time ago. I investigated and found for example:

http://www.sqlservercentral.com/Forums/Topic1037190-1550-1.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/acfacc5e-bf1e-474d-825e-9eff927e0311/the-log-for-database-xtrans-is-not-available
https://stackoverflow.com/questions/15771371/the-log-for-database-x-is-not-available

Then I changed "AutoClose" to off and the troubles had gone. Before that, I had to take the affected databases offline and online again.

The errors occured everytime a scheduled job did a DBCC CHECKDB. A manually started check of the databases did not show any error.


Does column store index reduce data space ? How this is possible?

A columnstore arranges the data on disk differently to how a "normal" table does it. The column's values are split into segments of just over one million values. Each segment is compressed. Since a single column's values can show a lot of repitition (think "country code" or "product name") the compression ratio can be significant.

Read performance can improve from several factors. First, only the columns required in the query are read off disk. Second, compression means much less IO for a given number of values compared to rowstore. Third, aggregate functions can be performed in what's called "batch mode," which is optimised for CPU cache utilisation.

Compression is also available for rowstores. My experience is that CPU utilisation increases but IO drops, for a nett improvement in elapsed query time. This was for moderately large databases performing reporting & analytics.

Of course, your mileage may vary.


Replace

CONVERT (VARCHAR(20), @Hoy1,108) fechaEntrada,
 CONVERT (VARCHAR(20), @Hoy2,108) fechaSalida,
DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia

with

CONVERT (VARCHAR(20), Entrada,108) fechaEntrada,
 CONVERT (VARCHAR(20), Salida,108) fechaSalida,
DATEDIFF(HOUR, Entrada, Salida) %24 AS horasAlDia

Here is the script:

DECLARE @Hoy DATETIME
DECLARE @Hoy1 DATETIME
DECLARE @Hoy2 DATETIME

SELECT  @Hoy = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))
SELECT  @Hoy1 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'00:00:00'
SELECT  @Hoy2 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'23:59:59'

 select c.CodeCompany,
 CONVERT(char(6), @Hoy, 112) AS fechaActual,
 a.idClave,
 c.nombre + ' ' + c.apellidos as Nombre,
 d.Descripcion as Depto,
 CONVERT (VARCHAR(20), Entrada, 108) fechaEntrada,
 CONVERT (VARCHAR(20), Salida, 108) fechaSalida,
 DATEDIFF(HOUR, Entrada, Salida) %24 AS horasAlDia
  from tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave
 JOIN tblDepto d ON c.fkDepto=d.idDepto
 where
   fechaEntrada BETWEEN CONVERT(DATETIME, @Hoy1,103) and
    CONVERT(DATETIME, @Hoy2,103)and fkStatus !=1


Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each)

You can number and order id by us_state using the ROW_NUMBER() Window Function  and only keep the n first values:

SELECT *
FROM (
  SELECT *
    , ROW_NUMBER() OVER(PARTITION BY us_state ORDER BY id) as n
  FROM data
) as ord
WHERE n <= 2
ORDER BY us_state
;

Or you can CROSS JOIN with a subquery:

SELECT l.*
FROM (
  SELECT DISTINCT us_state FROM data
) as s
CROSS JOIN LATERAL (
  SELECT *
  FROM data d
  WHERE d.us_state = s.us_state
  ORDER BY id
  LIMIT 2
) as l
ORDER BY l.us_state
;

Sample SQL Fiddle here
I used a small sample of 1 to 3 rows per state. Therefore I limit only to 2 values
I ordered it by ids but you can change that and order by whatever works best for you

Output with my small sample:

       id | us_state | n
      123 |       AL | 1
      456 |       AL | 2
 56781234 |       AZ | 1
 78123456 |       CO | 1
 45678123 |       FL | 1
 81234567 |       FL | 2
 34567812 |       GA | 1
      123 |       MA | 1
      456 |       MA | 2

Note that n is the result of ROW_NUMBER and doesn't exist in the second query.
On a big table, an index on the partition (us-state) and order (id here) columns will help.

Sample used:

CREATE TABLE data
    ("id" int, "us_state" varchar(2))
;

INSERT INTO data
    ("id", "us_state")
VALUES
    (12345678, 'MA'),
    (123, 'MA'),
    (456, 'MA'),
    (23456781, 'AL'),
    (123, 'AL'),
    (456, 'AL'),
    (34567812, 'GA'),
    (45678123, 'FL'),
    (56781234, 'AZ'),
    (67812345, 'MA'),
    (78123456, 'CO'),
    (81234567, 'FL')
;


No, shrink is safe. Unless you hit some bug in SQL Server of course, but that can happen whatever you do. The shrinkage is done as a series of mini transactions and it is also safe to terminate the operation.
There are of course other disadvantages of doing shrink, but I take it that you are aware of those.


MySQL Large Table Indexes - Recommended length of index

This is not an answer to your question (how to cut index length), but an alternative to reach the same objective: use less space in your DB.

If you want to optimize storage you should not store UUIDs as varchar(32) (nor character(32)).

You can store them as binary(16) with no loss of information, and save (at least) 50% of space. A UUID contains 128 bits of information. If stored as a text representing the hexadecimal value, you use 4 bits of information out of every character, which employs 8 bits assuming the text is encoded as ASCII or latin1. So, you waste half your available bits. Storing the information in binary format, you waste none.

My response is heavily inspired in Storing UUID Values in MySQL Tables, which I would recommend you read.

The following example shows you how to store the same UUID data in the two different formats:

 CREATE TABLE t
 (
      uuid_a character(32) PRIMARY KEY
     ,uuid_b binary(16) UNIQUE KEY
 ) ;

Both uuid_a and uuid_b will be used to store the same UUID, encoded in two different ways.

Assuming we have a table called generator_64k with 64k of data... we fill t with lots of uuid() using:

 -- We fill it with random data
 INSERT INTO
     t
 SELECT
      replace(u,'-','')             -- text version
     ,unhex(replace(u,'-',''))      -- binary version
 FROM
     (SELECT
         uuid() AS u
      FROM
         generator_64k
     ) AS s0 ;

... and we add also a well known value:

 -- We fill it with one known piece of data
 INSERT INTO
     t
 SELECT
      replace(u,'-','')             -- text version
     ,unhex(replace(u,'-',''))      -- binary version
 FROM
     (SELECT
         'aab5d5fd-70c1-11e5-a4fb-b026b977eb28' AS u
     ) AS s0 ;

You can then query the table and look for the known value using either of the representations:

 -- Encoded as character(32)
 SELECT
     uuid_a, hex(uuid_b)
 FROM
     t
 WHERE
     uuid_a = replace('aab5d5fd-70c1-11e5-a4fb-b026b977eb28', '-', '') ;

 -- Encoded as binary(16)
 SELECT
     uuid_a, hex(uuid_b)
 FROM
     t
 WHERE
     uuid_b = unhex(replace('aab5d5fd-70c1-11e5-a4fb-b026b977eb28', '-', '')) ;

You get, in both cases, the same result:

 uuid_a                          | hex(uuid_b)
 :------------------------------- | :-------------------------------
 aab5d5fd70c111e5a4fbb026b977eb28 | AAB5D5FD70C111E5A4FBB026B977EB28

In practice, you would only use uuid_b in your table.

By doing this, you halve the amount of space used to store your UUIDs (both in table and indexes), without having to resort to using a substring in your index (which can affect your performance, and still not save that much).

See all the code at dbfiddle here


What is a source table, and a target table?

Essentially the source table is the place where the data is coming from and the target table is the place where the data is going to or the table that an action is taking place on. They do not have to be in different schema's or have a different structure to each other and in fact they could actually be the same table.


Should I Link a Fact to Hierarchical Dimension at ALL Levels or only the most granular?

I would link at all/most levels.  This denormalized star means that yes, the data is redundant, but it typically makes the reporting and analysis a lot easier.  Note that this is very different from OLTP normalization, and you don't typically have to worry about redundant data getting out of sync because in a DW scenario data never changes.  New facts get added and dimensions get expired and new ones created.

I don't see a Dim_Folder.  I would assume that the actual path of the folder would be an attribute of the Dim_Folder.  Only the numeric quantity and any degenerate dimensions (http://en.wikipedia.org/wiki/Degenerate_dimension) would be in the fact table.  I wouldn't think of the folder path as a degenerate dimension because it keeps coming back in each snapshot (an a folder isn't a transaction).

So you could do something like this:

SELECT AVG(bytes_on_disk)
FROM FACT_Folder
INNER JOIN DIM_Folder
    ON FACT_Folder.FolderDimID = DIM_Folder.DimID
INNER JOIN DIM_Date
    ON FACT_Folder.SnapshotDateID = DIM_Date.DateID
WHERE DIM_Date.Date BETWEEN '20120101' AND '20121231'
    AND DIM_Folder.FolderPath = '/usr/bin/'

See how the DIM_Folder usage makes the set of dim ids small and then, we're assuming some kind of index on snapshot date and then folder dim id (or vice versa).

See how you also now don't need to join on folder at all if you just want the data at a higher level.  Since you usually know all this at ETL time, there is a different motivation than in OLTP systems where you want everything to move together when something is changed (leg bone connected to the thigh bone, etc.).  In DW scenario, you really don't want anything to move.

So, bam! - total Farm usage analysis:

SELECT DIM_Farm.Farm_Name, SUM(bytes_on_disk)
FROM FACT_Folder
INNER JOIN DIM_Farm
    ON FACT_Folder.FarmDimID = DIM_Farm.DimID
INNER JOIN DIM_Date
    ON FACT_Folder.SnapshotDateID = DIM_Date.DateID
WHERE DIM_Date.Date BETWEEN '20120101' AND '20121231'
GROUP BY DIM_Farm.Farm_Name

Remember stars are really simple for analysis.  You NEVER need to worry about inadvertent cross joins in a single non-snowflaked star.  When linking different stars, you DO have to watch out.  So queries in MOST cases are MUCH simpler in star-schemas.  No network traversal and worrying about many-many relationships like in a normalized model.


In a same context, I was prefering to change the password of the applicative login, during the drop/restoration process.
Is it possible for you?

  Make a query to list active connection from pg_stat_activity table to identify the culprit(s).

Hope this helps,
Thomas


How to structure MySQL database tables for users and how to handle adding/removing permissions?

A sketch for an alternative approach would be:
-- supertype for user / group, it is not clear from your post what
-- an appropriate name would be, so I'll just use `x`. each x is of a
-- certain type.

CREATE TABLE x_types
( x_type CHAR(1) NOT NULL PRIMARY KEY
, -- maybe add a description
);

INSERT INTO x_types (x_type) VALUES ('u'), ('g');

CREATE TABLE x
( xid ... NOT NULL PRIMARY KEY
, x_type CHAR(1) NOT NULL
, UNIQUE (x_type, xid)
-- common attributes for user / group
...
);

CREATE TABLE users
( uid ... NOT NULL PRIMARY KEY
, x_type CHAR(1) DEFAULT 'u' NOT NULL
-- user specific attributes
, ...
, CHECK (x_type = 'u') -- does not work in previous version of MySQL
, FOREIGN KEY (x_type, uid) REFERENCES x (x_type, xid)
);

CREATE TABLE groups
( gid ... NOT NULL PRIMARY KEY
, x_type CHAR(1) DEFAULT 'u' NOT NULL
-- group specific attributes
, group_creator ... NOT NULL
, ...
, CHECK (x_type = 'u') -- does not work in previous version of MySQL
, FOREIGN KEY (x_type, gid) REFERENCES x (x_type, xid)
, FOREIGN KEY (group_creator) REFERENCES users (uid)
);

-- a relationship between supertype and permission
CREATE TABLE x_permissions
( xid ... NOT NULL
, pid ... NOT NULL
, PRIMARY KEY (xid, pid)
, FOREIGN KEY (xid) REFERENCES x (xid)
, FOREIGN KEY (pid) REFERENCES permissions (pid)
);

-- group membership for users
CREATE TABLE group_users
( gid ... NOT NULL REFERENCES groups (gid)
, uid ... NOT NULL REFERENCES users (uid)
, PRIMARY KEY (gid, uid)
);

This may suit your needs, but it is hard to tell without knowing the exact business rules. Identifiers are of course just bogus and need to be adjusted.


Is unique index combined with a "not null" constraint equivalent to a PK constraint?

which effectively yields two identical indexes.

The indexes are not identical.  The CI includes all of the other columns, while the PK has only the ItemId key column on the leaf.  So the Clustered Index is probably 100x the size of the non-clustered index.

For instance COUNT(*) and paging queries will be significantly more expensive with only the clustered index, as they will have to use table scans.


In postgreSQL, database is a logical concept and a PostgreSQL server manages one database-cluster which contains all databases.
The number of databases does not affect to the server performance.

I recommend that you divide databases at the viewpoint of logical structure. If your system can be completely divided into each mini site, you may create databases corresponding to the sites. (When the access of one site increases, you can divide and relocate the site to  other server.)


SQL Server 2012 Mirror in a Disconnected state - how do I fix it?

Actually, everything stayed the same, I found out that the service account some how lost the CONNECT permission.

So, I gave the service accounts the CONNECT permission and all the mirrored database synced as expected!


Select consecutive number of rows starting from some number

You can try something like

SELECT field1, field2
FROM table1
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.


SQL Server 2012: Login failed for user. Reason: Failed to open the database configured in the login object

We had the same issue. Several app servers were logging the same error. It turned out to be a bad SFP network port in the Hyper-V hosts frame. As soon as it was disabled all of the errors stopped immediately.


How can I drop a column from a large (700M rows) hot (multiple transactions per second) table with minimal downtime?

How will SQL Server queue this request (eg First In First Out)? Can I
  be sure the DROP COLUMN will truly only take a few moments?

The way the locking works for ALTER TABLE is that you need a Schema Modification Lock (Sch-M) to perform the ALTER.  Every other query that touches the table needs a Schema Stability Lock (Sch-S). Sch-M and Sch-S are incompatible.

Also while you are waiting to acquire the Sch-M lock, you block any new requests for a Sch-S lock.  So you wait for all in-flight queries and transactions to release their Sch-S locks on the table, and block all new queries while you wait.  This is the normal way DDL locking works, and so long as you don't have any long-running queries on your table it works fine.

But if you have long running queries that your DDL can get stuck behind, your DDL in turn blocks all new queries.  So running DDL on a busy system with both short-running and long-running operations was a problem.

Low Priority Waits solves this problem by allowing your DDL statement to wait at reduced priority, not blocking new queries.  The DDL session will just wait for a "window" where no other session has any lock on the table, and then run.  But since the DDL session is not blocking new queries, it might wait forever.

Unfortunately low priority waits have not been enabled for dropping columns.  You can create a feedback item to ask for it to be implemented here.  Has been enabled for online index rebuild and partition switching.

You can mostly emulate this behavior with LOCK_TIMEOUT and retries.  So configure your DDL session with a lock timeout, so it will abandon the ALTER TABLE if the lock can't be acquired quickly.

So run the alter like this:

set lock_timeout 1000 --1 second
alter table SomeTable
drop column if exists ColumnToDrop

And it will either succeed quickly or fail with a lock timeout error.  If you want to run this in an unattended job, you can use a retry loop to keep trying until the ALTER TABLE succeeds quickly.

Something like this:

set lock_timeout 1000 --1 second
declare @tries int = 0

while 1=1
begin
    begin try
        set @tries = @tries + 1;

        alter table SomeTable
        drop column if exists ColumnToDrop

        print 'complete'
        break;
    end try
    begin catch
      if @tries > 10 or error_number() <> 1222 throw;

      declare @msg nvarchar(2000) = concat('retry ', @tries);
      print @msg
    end catch
end


Postgresql database NOTIFICATION trigger that only returns if some sql is met

You have two options:
1. Do it in the trigger with an IF condition:
if new.site = 'X' then
  notify ...
end if;

2. Only fire the trigger if the column contains the value:
create trigger notify_site
after insert on sites
for each row
when (new.site = 'X')
execute procedure notify_site();

how would I insert the tablename into the json object being returned by the select all?

As documented in the manual the table name is available in the variable TG_RELNAME, so you could just append that to your JSON:
payload := payload || jsonb_build_object('tableName', TG_RELNAME);


query discrepancies on very large postgres table

The output from EXPLAIN ANALYZE is very helpful for understanding this. In particular, note these two lines:

->  Index Scan Backward using logdatetime on tlog
    (cost=0.00..2434879.14 rows=69168 width=8)
    (actual time=1924.800..1924.800 rows=1 loops=1)

->  Index Scan Backward using logdatetime on tlog
    (cost=0.00..2434879.14 rows=1688018 width=8)
    (actual time=32.513..32.513 rows=1 loops=1)

I reformatted and added bold emphasis.

Since you only seem to have an index on logdatetime that's what the query planner is going to use. It seems promising that you only want a single row, with MAX(logdatetime), so the query planner thinks it's a good idea to start from the back of the index  (Index Scan Backward) and scan until the first row with matching pointid pops up.

This strategy works out just nicely for pointid = '359710040139645', but not so much for pointid = 'and.andrew'. The maximum logdatetime for this particular pointid is obviously rather old, so a lot of rows (1.688.018) have to be read before the first hit (which is the youngest row).

But still a lot better than a full table scan where you would need to read all 25m rows to identify the matching ones, then find the max among these. (Try deleting the index an rerun the query. Be prepared for a long wait.)

If you can afford adding indexes (some cost to write operations and considerable disk space for 25m rows) try adding an index on pointid instead, or better yet, this multi-column index:

CREATE INDEX tlog_pointid_logdatetime ON tlog (pointid, logdatetime DESC);

This would make your query fly.

If you don't want indexes to slow down your writes, you might create this index just for the purpose of running a couple of queries and delete it again. Index creation is pretty fast in PostgreSQL. This may pay even for just a few queries.


While there's still some missing info (it would help to know if there is only one table, or if there are two tables, the names of those tables, and which columns are held in each), I think this might help.
From what you wrote, it sounds like there are two tables (let's call them 'table1' and 'table2'), and table1 has columns 'id', 'title', 'date', while table2 has columns 'regnumber' and 'title'. Again, if I'm not understanding this, please correct me by providing more information about the tables and the structures of those tables.
It appears that you're missing information linking the 'id' of the event to the 'regnumber' of the venue. I think the best way of linking the event id to the venue regnumber is through a third table (we'll call it 'table3') with two foreign keys: 'event_id' (which would reference the 'id' column in table1) and 'venue_id' (which would reference the 'regnumber' in table2).
Before I move on to the SQL, let's make sure the structure of our three tables looks like the following:

table1: id, title, date
table2: regnumber, venue
table3: event_id, venue_id

With each event 'id' paired with a 'venue_id' in table3, we can move on to writing the following query.
SELECT t1.id, t1.title, t1.date, t2.regnumber, t2.venue FROM table1 t1, table2 t2, table3 t3 WHERE t1.id = t3.event_id;

The reason you'll want a third table is because multiple events will happen in the same venue, and events can happen across multiple venues (like the concert series SXSW).


SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help

An index on the bit column isn't going to help at all because of selectivity. You should consider a filtered index:

CREATE INDEX unindexed ON dbo.Documents(documentId) WHERE IsIndexed = 0;

You should also include an ORDER BY documentId in your SELECT.

Though you should test this in a staging environment because it may help your SELECT but may offset that with a more expensive UPDATE.


Table or dictionary in code on a client for data that's not changed often?

Any data that can change should not be hard-coded into an app.  Especially if your app already uses a database.

Create a table to store currencies so you can update the list of currencies without needing to rewrite and redistribute the app.

As pointed out by Gareth Lyons in a comment, referential integrity relies on the database being self-contained; that is no references to code outside the database.  As an example, if you conduct transactions, you can maintain a list of valid currencies in a table like this:

CREATE TABLE dbo.Currencies
(
    CurrencyCode varchar(3) NOT NULL
        CONSTRAINT PK_Currencies
        PRIMARY KEY CLUSTERED
    , CurrencyName varchar(100) NOT NULL
);

INSERT INTO dbo.Currencies (CurrencyName, CurrencyCode)
VALUES ('East Caribbean Dollar', 'XCD')
    , ('Argentine Peso', 'ARS')
    , ('Armenian Dram', 'AMD')
    , ('Aruban Florin', 'AWG')
    , ('Australian Dollar', 'AUD')
    , ('Euro', 'EUR')
    , ('Azerbaijanian Manat', 'AZN')
    , ('Bahamian Dollar', 'BSD')
    , ('Bahraini Dinar', 'BHD')
    , ('Taka', 'BDT')
    , ('Barbados Dollar', 'BBD')
    , ('Belarusian Ruble', 'BYN')
    , ('Belize Dollar', 'BZD')
    , ('CFA Franc BCEAO', 'XOF')
    , ('Bermudian Dollar', 'BMD')
    , ('Indian Rupee', 'INR')
    , ('Ngultrum', 'BTN')
    , ('Boliviano', 'BOB');

And a list of transactions in a table list this:

CREATE TABLE dbo.CurrencyTransactions
(
    TransactionID int NOT NULL
        CONSTRAINT PK_CurrencyTransactions
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CurrencyCode varchar(3) NOT NULL
        CONSTRAINT FK_CurrencyTransactions_CurrencyCode
        FOREIGN KEY
        REFERENCES dbo.Currencies(CurrencyCode)
    , DebitAmount DECIMAL(38,4) NOT NULL
    , CreditAmount DECIMAL(38, 4) NOT NULL
);

Attempting to insert a transaction into the table, whether within your app, or from any other source, will result in the currency code being validated:

INSERT INTO dbo.CurrencyTransactions (CurrencyCode, DebitAmount, CreditAmount)
VALUES ('CAD', 1000000, 0);

Attempting to do the insert above results in this error:

  Msg 547, Level 16, State 0, Line 45
  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CurrencyTransactions_CurrencyCode". The conflict occurred in database "tempdb", table "dbo.Currencies", column 'CurrencyCode'.


Cities and States (a few data): single table or splitted?

CHAR(2) CHARACTER SET latin1 takes only 2 bytes.  You are not going to improve significantly on that by using an id TINYINT UNSIGNED (1 byte) to 'normalize' it.

Splitting into 2 tables is something I call "over-normalizing".

JOINs are pretty cheap, but they are not free.  This is a disadvantage of the 2-table approach.

While you are asking this question, I suggest you implement it both ways, see how big the tables are, see how fast the queries are, etc.  Use it as a learning exercise.

Yes, there will be other cases where 2 tables is better.


How to enable Cloud Watch logs for AWS DMS task?

You have to modify your migration task and you can see the Enable CloudWatch Logs like below.


Sql Server thinks I have different Data Compression when swaping partitions

Below is a complete script that works as expected without the error.

:setvar IsArchive ""
:setvar Suffix _Partitioned

CREATE SCHEMA Orders;
GO

CREATE SCHEMA ArchivedOrders;
GO

CREATE SEQUENCE Orders.NextOrderId;
GO

CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS
    RANGE RIGHT FOR VALUES();
GO

CREATE PARTITION SCHEME OrderIdPartitionScheme AS
    PARTITION OrderIdPartitionFunction ALL TO ([PRIMARY]);
GO

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

:setvar IsArchive Archived
:setvar Suffix _Archived

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

ALTER TABLE Orders.Order_Partitioned SWITCH PARTITION 1
      TO ArchivedOrders.Order_Archived PARTITION 1;
GO


Azure automatic fail-over vs always on availability groups clarification

On-Premise SQL Server and SQL Server on a VM hosted in Azure are roughly equivalent and can be configured to be part of the same availability group, although as you mentioned the failover is manual rather than automatic.

This link https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-sql-onprem-availability provides more details.

Azure Database (As A Service) is a different thing.  It is definitely based on SQL Server and it's High Availability is provided by something that is very similar to Always On Availability Groups.  But it's completely managed by Microsoft so you won't be able to add it as a member to your own Availability Group.

This link https://docs.microsoft.com/en-us/azure/sql-database/sql-database-high-availability has more details.

To answer your second question you should view resources in different regions as if they are in separate data centers (which they are).  You can make automatic failover work in these situations by using VPN tunnels and judicious use of cloud witnesses.  However, I would advise against failing between data centers automatically, that should be a deliberate decision.

But mostly this depends on your HA/DR requirements.  I love Azure and we use it heavily in my current organization, but stretching across datacenters or on-prem/cloud is not as pain-free as Microsoft would have you believe.


select query on Temp table is not returning data in correct order even after specifying index hint

You have to use an ORDER BY.  Adding a hint to use a specific index does not guarantee any ordering of the resultset:

https://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause


'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year

There are are 2 elements to your field

Year
An auto incrementing number

They do not need to be stored as one field

Example:

A year column which has a default of YEAR(GETDATE())
A number column based on a sequence.

Then create a computed column concatenating them (with appropriate formatting).
The sequence can be reset on change of year.

Sample code in SQLfiddle:*(SQLfiddle doesn't always work)

-- Create a sequence
CREATE SEQUENCE CountBy1
    START WITH 1
    INCREMENT BY 1 ;

-- Create a table
CREATE TABLE Orders
    (Yearly int NOT NULL DEFAULT (YEAR(GETDATE())),
    OrderID int NOT NULL DEFAULT (NEXT VALUE FOR CountBy1),
    Name varchar(20) NOT NULL,
    Qty int NOT NULL,
    -- computed column
    BusinessOrderID AS RIGHT('000' + CAST(Yearly AS VARCHAR(4)), 4)
                     + RIGHT('00000' + CAST(OrderID AS VARCHAR(6)), 6),
    PRIMARY KEY (Yearly, OrderID)
    ) ;

-- Insert two records for 2015
INSERT INTO Orders (Yearly, Name, Qty)
    VALUES
     (2015, 'Tire', 7),
     (2015, 'Seat', 8) ;

-- Restart the sequence (Add this also to an annual recurring 'Server Agent' Job)
ALTER SEQUENCE CountBy1
    RESTART WITH 1 ;

-- Insert three records, this year.
INSERT INTO Orders (Name, Qty)
    VALUES
     ('Tire', 2),
     ('Seat', 1),
     ('Brake', 1) ;


T-SQL Gurus, I need help optimising these queries for performance

For the first query, this version eliminates a CI seek and a TOP N Sort, and cuts the reads against dbo.Bids in half. It performs more reads overall, but those are caused by a worktable, not an actual table. I believe this gives your desired results, but without more sample data, I can't be sure that it matches your requirements.

SELECT TOP (1) bid, msisdn
  FROM dbo.Bids
  WHERE bid >= 0.01 AND auctionid = @auctionid
  ORDER BY COUNT(*) OVER (PARTITION BY bid), bid, lineid;

For your second query, this gets rid of a CI seek, a sort, a nested loop join, and a few other operators:

;WITH x AS
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid
  FROM x ORDER BY tally
)
SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid)
FROM y ORDER BY ranking;

For the third query, this gets rid of two sorts, a CI seek, and cuts the table reads in half.

;WITH x AS
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid
  FROM x ORDER BY tally
),
z AS
(
  SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid)
  FROM y
)
SELECT TOP (1) msisdn, MinBid, ranking FROM z WHERE msisdn = @msisdn ORDER BY ranking;

For all of these, you'll have to test whether this leads to better or worse overall performance in your environment. I only tested (both accuracy and plan shape / duration) using your limited sample data. Things could go askew depending on data distribution, cardinality estimates, any other indexes, etc. etc.


From the documentation, I understand it like, the server accepts connections only from the IP - 10.4.0.211.

Your understanding is incorrect.

It has nothing to do with where the connection is coming from.

The server listens and accepts connections only on (not "from") the stated address.  Where you can connect from is determined by the entries in the `mysql`.`user` table, which are created by GRANT statements, and, of course, whether you have network connectivity to the address where MySQL is listening.


How Can I Isolate A Merge Replication Timeout

Add the Merge Agent parameters -OutputVerboseLevel 4 -Output C:\TEMP\mergeagent.log to the Run Agent Merge Agent job step to find out where it is timing out.


SET STATISTICS TIME ON elapsed time is different than actual execution time

The SET STATISTICS options will report the statistics for each statement, which when coupled with procedures can lead to including what are effectively "summary" results - as the call to the procedure itself resolves, an additional statistics record will be returned containing the statistics for the procedure itself.

This can lead to multiple "summary" results if a procedure does something like call another procedure.

To illustrate when the SET STATISTICS options return information, we can create up a couple of procedures, one of which that is simply going to call the other.

USE tempdb;
GO

IF ( OBJECT_ID( 'dbo.TestProc', 'P' ) IS NULL )
BEGIN
    EXEC( 'CREATE PROCEDURE dbo.TestProc AS SET NOCOUNT ON;' );
END;
GO

ALTER PROCEDURE dbo.TestProc
AS BEGIN
    -- SET NOCOUNT ON; -- Excluded to reduce noise for the example;
    SELECT  TOP 100000 sao1.*
    INTO    #t_Test
    FROM    sys.all_objects sao1
    CROSS APPLY sys.all_objects sao2;

    SELECT  TOP 100000 *
    INTO    #t_Test2
    FROM    sys.all_objects sao1;

    DROP TABLE #t_Test;
    DROP TABLE #t_Test2;
END;
GO

IF ( OBJECT_ID( 'dbo.TestSuperProc', 'P' ) IS NULL )
BEGIN
    EXEC( 'CREATE PROCEDURE dbo.TestSuperProc AS SET NOCOUNT ON;' );
END;
GO

ALTER PROCEDURE dbo.TestSuperProc
AS BEGIN
    -- SET NOCOUNT ON; -- Excluded to reduce noise for the example;
    EXECUTE dbo.TestProc;
END;
GO

With these procedures in place, let's turn STATISTICS TIME on and run the operations performed by executing either procedure without actually calling one:

-- Just procedure contents;
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SELECT  TOP 100000 sao1.*
INTO    #t_Test
FROM    sys.all_objects sao1
CROSS APPLY sys.all_objects sao2;

SELECT  TOP 100000 *
INTO    #t_Test2
FROM    sys.all_objects sao1;

DROP TABLE #t_Test;
DROP TABLE #t_Test2;
SET NOCOUNT OFF;
SET STATISTICS TIME OFF;

The output will look a little bit like this:

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 305 ms.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 40 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Matching this all up looks as would be expected:

SET NOCOUNT ON;
SET STATISTICS TIME ON;
-- Statistics will now start being reported;
SELECT  TOP 100000 sao1.*
INTO    #t_Test
FROM    sys.all_objects sao1
CROSS APPLY sys.all_objects sao2;

  SQL Server Execution Times:
     CPU time = 171 ms,  elapsed time = 305 ms.

SELECT  TOP 100000 *
INTO    #t_Test2
FROM    sys.all_objects sao1;

  SQL Server Execution Times:
     CPU time = 47 ms,  elapsed time = 40 ms.

DROP TABLE #t_Test;

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 2 ms.

DROP TABLE #t_Test2;

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 8 ms.

SET NOCOUNT OFF;

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 0 ms.

-- Statistics will now stop being reported;
SET STATISTICS TIME OFF;

Four statements ( SELECT, SELECT, DROP, DROP ) plus a SET NOCOUNT OFF; for a fifth statement, five messages from STATISTICS TIME.

However, if we were to capture the statistics by running the procedure:

-- Procedure statistics;
SET NOCOUNT ON;
SET STATISTICS TIME ON;
EXECUTE dbo.TestProc;
SET NOCOUNT OFF;
SET STATISTICS TIME OFF;

 SQL Server Execution Times:
   CPU time = 140 ms,  elapsed time = 488 ms.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 40 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 529 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Suddenly, a sixth message is returned - this is a message indicating the total statistics for the procedure itself, and is the sum of the statements contained within the procedure.  We can generate a seventh summary record in the results of STATISTICS TIME by executing the dbo.TestSuperProc we created:

-- Super Proc statistics;
SET NOCOUNT ON;
SET STATISTICS TIME ON;
EXECUTE dbo.TestSuperProc;
SET NOCOUNT OFF;
SET STATISTICS TIME OFF;

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 259 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 42 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 307 ms.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 307 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

In effect, your second last message ( likely before a SET NOCOUNT OFF as in my example ) is reporting the 31 seconds you are expecting - the total CPU and elapsed time for the calling procedure.  As you seem to be aware, CPU time is really where you want to pay attention for performance tuning, as it can be higher than the elapsed time if the query goes parallel, or lower if the procedure spends time sleeping on wait signals and I/O operations.


How to update MySQL data within intranet from MySQL data in cloud?

MySQL has a built-in functionality called MySQL replication that allows the synchronisation between 2 servers, by applying on a "slave" the operations that have been done on the "master". If no writes are done on the slave, that works pretty well and, while it has its issues, it is widely used by most MySQL users (Facebook, Twitter, etc.) for high availability, load balancing and/or testing/maintenance operations.

It is very easy to setup and has little performance impact, as it is asynchronous by default. That makes possible to perform the replication in real time or in batches, as desired.

Please note that while all "Infraestructure as a service" allows replication, some "database as a service" providers do not allow to do it outside of their servers (for example, RDS only allowed it in MySQL 5.6 servers).

If you need to write to the slave (your intranet server), that would be more complex, as you have to make a plan on how to merge the results and modify the replication settings or use an alternative technology.


Does Azure SQL Managed Instance offer SQL Server 2016?

You cannot restore a backup from a Managed Instance to an on-prem version of SQL Server.  Even if you had an on-prem 2019, it wouldn't work.  During our migration this was one of the things that gave me pause as well, that it was going to be one-way without a lot of work.

Additionally (as pointed out by David Browne) you can use Snapshot Replication between Managed Instances and On-Prem SQL.  So that is a viable method of migrating as well as the horror that I had envisioned internally of doing bulk ETL from MI to on-prem.

Also, to answer the question directly asked, Managed Instances offer one version of the SQL Engine.  You can select a 2016 compatibility level for your databases if you need to limit yourself to those features.


Identify data in PostgreSQL database in violation of foreign keys

So, after much Googling I came up with the below to locate foreign key violations in my dataset - I had indeed missed a single table.

If you're wondering why I don't use information_schema.table_constraints, information_schema.key_column_usage, and information_schema.constraint_column_usage it's because using those tables completley falls apart for multi-column foreign keys.

Hope this helps someone someday:

do language plpgsql $$
declare
    _schemas varchar[];
    _fkey record;
    _sql text;
    _and text;
    _i integer;
    _test record;
begin
    _schemas := array['my_schema_one', 'my_schema_two']::varchar[];

    create temporary table __foreign_key_violations (
        table_name varchar,
        column_names varchar[],
        foreign_table_name varchar,
        foreign_column_names varchar[]
    ) on commit drop;

    for _fkey in(
        with
            __tables as(
                select
                    quote_ident(tables.table_schema) || '.' || quote_ident(tables.table_name) as table_name
                from
                    information_schema.tables
                where
                    tables.table_type = 'BASE TABLE'
                    and table_schema = any(_schemas)
                order by
                    tables.table_schema asc,
                    tables.table_name asc
            ),
            __raw_fkeys as(
                select
                    __tables.table_name,
                    pg_catalog.pg_get_constraintdef(pg_constraint.oid, true) as fkeydef
                from
                    __tables
                    inner join pg_catalog.pg_constraint on(
                        pg_constraint.conrelid = __tables.table_name::regclass
                        and pg_constraint.contype = 'f'
                    )
            )
        select
            __raw_fkeys.table_name,
            string_to_array(regexp_replace(__raw_fkeys.fkeydef, E'^FOREIGN KEY \\((.*?)\\).*$', E'\\1'), ', ') as column_names,
            regexp_replace(__raw_fkeys.fkeydef, E'^FOREIGN KEY .*? REFERENCES (.*?)\\(.*$', E'\\1') as foreign_table_name,
            string_to_array(regexp_replace(__raw_fkeys.fkeydef, E'^FOREIGN KEY .*? REFERENCES .*?\\((.*?)\\).*$', E'\\1'), ', ') as foreign_column_names
        from
            __raw_fkeys
    ) loop
        _sql := '
        select
            true as violations
        from
            ' || _fkey.table_name || ' as local_table
        where
            not exists(
                select
                    *
                from
                    ' || _fkey.foreign_table_name || ' as foreign_table
                where';

        for _i in 1 .. array_upper(_fkey.column_names, 1) loop
            _and := '';
            if _i > 1 then
                _and := 'and ';
            end if;

            _sql := _sql || '
                    ' || _and || 'local_table.' || quote_ident(_fkey.column_names[_i]) || ' is not null
                    and foreign_table.' || quote_ident(_fkey.foreign_column_names[_i]) || ' = local_table.' || quote_ident(_fkey.column_names[_i]);
        end loop;

        _sql := _sql || '
            )';

        for _i in 1 .. array_upper(_fkey.column_names, 1) loop
            _sql := _sql || '
                and local_table.' || quote_ident(_fkey.column_names[_i]) || ' is not null';
        end loop;

        _sql := _sql || '
        limit 1
        ';

        execute _sql into _test;

        if _test.violations = true then
            insert into __foreign_key_violations (
                table_name,
                column_names,
                foreign_table_name,
                foreign_column_names
            ) values (
                _fkey.table_name,
                _fkey.column_names,
                _fkey.foreign_table_name,
                _fkey.foreign_column_names
            );
        end if;
    end loop; -- _fkey in..
end;
$$;

select * from __foreign_key_violations;


In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array?

If you want an index to be used for searching in an array, you need to first store the values as a proper array, not as a string with comma separated values that are enclosed with curly braces, e.g: {"types": [1,2], "name": "item_name1"}

Then you need to access the value as JSONB, not as text. So you need to use -> not ->>.

SELECT *
FROM items
WHERE metadata -> 'types'  @> '[52,53]'::jsonb

And finally you need to index the types array to make that work, not the complete content of the metadata column:

create index idx_metadata_types on items using gin  ((metadata -> 'types'));

To test the index usage, you need a substantially larger table than the one you have with just 38 rows.

Using this query:

explain (analyze, buffers)
SELECT *
FROM items
WHERE metadata -> 'types' @> '[52,53]'::jsonb

On a table with 100000 rows I then get the following plan (using Postgres 11 on my Windows 10 laptop):

Bitmap Heap Scan on items  (cost=5.38..103.08 rows=100 width=78) (actual time=0.315..0.422 rows=54 loops=1)
  Recheck Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
  Heap Blocks: exact=51
  Buffers: shared hit=57
  ->  Bitmap Index Scan on idx_metadata_types  (cost=0.00..5.35 rows=100 width=0) (actual time=0.298..0.298 rows=54 loops=1)
        Index Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
        Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.453 ms


Access 2013 update rows from multiple rows

You can't write a single query to do that in Access but a 'make table query' and a separate 'update query' combo will work. Change to suit your needs.

    select MyTable.RowID
        ,[t1].[MaxOfExtension] & [t1].[MaxOfName] as NewValue
    into t2
    from MyTable
    inner join (
        select MyTable.MapID
            ,Max(MyTable.Extension) as MaxOfExtension
            ,Max(MyTable.name) as MaxOfName
        from MyTable
        group by MyTable.MapID
        ) as t1
        on MyTable.MapID = t1.MapID;

    update MyTable
    inner join t2
        on MyTable.RowID = t2.RowID
    set MyTable.MyNewField = [t2].[NewValue];


Can I do something like RESTORE DATABASE .?

Another option:

EXEC linkedserver.master.dbo.sp_executesql N'RESTORE DATABASE ...';


Custom ID Numbering - fast way on insert?

Using a single statement and ROW_NUMBER(). With proper indexes, it will probably be more efficient than cursors.

Note that I moved the p.[BucketRef] = @stgBucketNo from the WHERE to the ON clause. The way you had it, made the LEFT JOIN work as an INNER JOIN.

WITH
  starting (Id) AS
    ( SELECT ISNULL( ( SELECT TOP (1) v.Id
                       FROM sim.[Variable] AS v
                       WHERE v.BucketRef = @simBucketNo
                       ORDER BY v.Id DESC
                     ), 0)
    ),
  variables AS
    ( SELECT
          p.[Variable],
          idRunner = s.Id + ROW_NUMBER() OVER (ORDER BY p.[Variable])
        FROM starting AS s
          CROSS JOIN simstg.[Parameter] AS p
          LEFT OUTER JOIN sim.[Variable] AS v
            ON  p.[Variable] = v.[Code]
            AND p.BucketRef = @stgBucketNo
        WHERE v.BucketRef = @simBucketNo
          AND v.Code IS NULL
        GROUP BY p.[Variable]
    )
INSERT INTO sim.[Variable]
    ([BucketRef], [VariableNo], [Code])
SELECT @simBucketNo, v.idRunner, v.[Variable]
FROM variables AS v ;

The common table subqueries (WITH) are there only for clarity - and to resemble the original code. You can easily rewrite the statement without them:

INSERT INTO sim.[Variable]
    ([BucketRef], [VariableNo], [Code])
SELECT
    @simBucketNo,
    idRunner = ISNULL( ( SELECT TOP (1) v.Id
                         FROM sim.[Variable] AS v
                         WHERE v.BucketRef = @simBucketNo
                         ORDER BY v.Id DESC
                       ), 0)
             + ROW_NUMBER() OVER (ORDER BY p.[Variable])
    p.[Variable],
FROM simstg.[Parameter] AS p
    LEFT OUTER JOIN sim.[Variable] AS v
        ON  p.[Variable] = v.[Code]
        AND p.BucketRef = @stgBucketNo
WHERE v.BucketRef = @simBucketNo
  AND v.Code IS NULL
GROUP BY p.[Variable] ;


How do you manage SQL jobs that belong to a database?

So let us begin with the obvious: Back up your msdb database. Get a maintenance plan going, back it up regularly, and let us never lose our jobs or job history again.

This also leads to the second part of my answer which is: jobs and their steps are just rows in a table. They have steps which invoke subsystem commands using arguments provided in the job step row. The only way ultimately to "manage" the jobs is by managing the SQL that creates / modifies / deletes them. And because there's surprisingly little customization for a given job step, most people have just stuck to static SQL scripts for deployment.

Build your job in dev, test it, script it out to SQL, store it as a post-deployment file in your database project (your databases are in source control ... right?) and you've got the path of least resistance to SQL Agent job management.

Maybe someday in the future there will be ways to "compare" jobs in a project to a target server job a la Schema Compare - again, it's just SQL, shouldn't be too hard to reconcile - and then publish changes (even bidirectionally!) but for now you're just stuck with scripting.