Find out beforehand how many records a query has

Unfortunately, the total number of records is not known until the query completes. This means that somehow you need to let the unrestricted query finish, and then grab the subset of rows.

One way to do this is to add COUNT(*) OVER () AS [TotalRows] to the SELECT list. This will return the value as a column with the same value repeated for every row, but still.

SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET 10 ROWS FETCH NEXT 7 ROWS ONLY;

Of course, that is not simply accessing a value that is freely available. Just compare th execution plan of the query above with the plan for the same thing without the COUNT(*).. expression:

SELECT [object_id], [schema_id], [name], [type_desc]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET 10 ROWS FETCH NEXT 7 ROWS ONLY;

Another method I come up with is described in detail in my answer to a nearly identical question on Stack Overflow:

TSQL: Is there a way to limit the rows returned and count the total that would have been returned without the limit (without adding it to every row)?

The difference between that question and this one is that the other question specifically did not want to return the value as a column in the result set it was counting. Also, the other question was more concerned with the "limit" aspect and this one is concerned with at least "offset" if not both. But adding the "offset" part isn't difficult: just add a loop prior to the loop that returns the desired results to do something like the following:

// assume an input param or variable of: int Offset;

int _RowCounter = 0;
while (Reader.Read() && ++_RowCounter < Offset);

Just for fun, I thought I would experiment with another mechanism that that can run a query and get the rowcount without retrieving the rows: a CURSOR (yes, the E-V-I-L CURSOR). The variable @@CURSOR_ROWS is populated after calling OPEN, even if you never FETCH any rows.

In the following (which I have no idea how well it performs), I make use of FETCH ABSOLUTE x to handle the "Offset" aspect since there is no need to cycle through fetching those initial rows just to not use them. But using ABSOLUTE disallows using options such as FAST_FORWARD and FORWARD_ONLY. Not being able to use FAST_FORWARD, I then used STATIC, but it would have to be tested to see if adding in STATIC actually helped or hurt.

Now, the only way to get the desired results is to store them in a table variable (or temp table would work but I think a table variable is better in this case) so they can be returned as a single result set. Doing FETCH without the INTO clause will return a different result set per call (most likely undesirable).

DECLARE @Offset INT = 10,
        @Limit INT = 7;

DECLARE cursed CURSOR LOCAL READ_ONLY STATIC -- can't use FAST_FORWARD or FORWARD_ONLY :(
FOR  SELECT so.[object_id], so.[schema_id], so.name, so.type_desc
     FROM   sys.objects so
     ORDER BY so.[object_id] ASC;

DECLARE @object_id INT,
        @schema_id INT,
        @name sysname,
        @type_desc NVARCHAR(60);

DECLARE @RowCounter INT = 0,
        @StartRow INT = (@Offset + 1); -- Offset is how many rows to skip

DECLARE @Results TABLE ([object_id] INT NOT NULL, [schema_id] INT NOT NULL,
                        [name] sysname NOT NULL, [type_desc] NVARCHAR(60) NOT NULL);

OPEN cursed; -- execute the query

SELECT @@CURSOR_ROWS AS [RowCountBeforeRetrievingAnyRows];

FETCH ABSOLUTE @StartRow -- position cursor at beginning of desired range
FROM  cursed
INTO  @object_id, @schema_id, @name, @type_desc;

WHILE (@@FETCH_STATUS = 0 AND @RowCounter < @Limit)
BEGIN
  INSERT INTO @Results ([object_id], [schema_id], [name], [type_desc])
  VALUES (@object_id, @schema_id, @name, @type_desc);

  SET @RowCounter += 1;

  FETCH NEXT
  FROM  cursed
  INTO  @object_id, @schema_id, @name, @type_desc;
END;

CLOSE cursed;
DEALLOCATE cursed;

-- return desired range of rows
SELECT * FROM @Results;

-----------------------------------------------
-- check the actual data to see if it worked
SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;

Considering that a STATIC CURSOR saves its results into tempdb, the following is essentially the same operationally, but a bit more direct:

DECLARE @Offset INT = 10,
        @Limit INT = 7;

DECLARE @Results TABLE ([object_id] INT NOT NULL, [schema_id] INT NOT NULL,
                        [name] sysname NOT NULL, [type_desc] NVARCHAR(60) NOT NULL,
                        [RowID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY);

INSERT INTO @Results ([object_id], [schema_id], [name], [type_desc])
  SELECT so.[object_id], so.[schema_id], so.name, so.type_desc
  FROM   sys.objects so
  ORDER BY so.[object_id] ASC;

SELECT @@ROWCOUNT AS [TotalRows];

-- return desired range via OFFSET / FETCH (Clustered Index Scan)
SELECT [object_id], [schema_id], [name], [type_desc]
FROM @Results
ORDER BY [RowID] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;

-- return desired range via WHERE (Clustered Index Seek)
SELECT [object_id], [schema_id], [name], [type_desc]
FROM @Results
WHERE [RowID] > @Offset
AND   [RowID] <= (@Offset + @Limit)
ORDER BY [RowID] ASC;

-----------------------------------------------
-- check the actual data to see if it worked. OR, maybe this is actually better?
SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;


How can I compare the number of transactions in the first weeks or months of different stores

We really need more detail on your measures and dimensions to give you an exact answer on this, but since you are using SSAS a few general rules apply.

If you add more detail I can expand my answer to be in line with your data model.

Since you want to compare the number of transactions you should have a measure showing the number of transactions.

As you want to compare them over time you would also need a Time Dimension with a hierarchy.

If you have both those prerequisites you can resort to MDX to calculate sums and differences of your measure over your dimension hierarchies and members.

In essence what you need to try is to construct a set and sum your measure over that set.

Sum({[opening date]:[opening date+2weeks]},[Measures].[Number of transactions])

The hard part in your problem will be constructing the set however. And that's the part we can't answer without your data model and some sample data.

As you say you have different start dates for different stores you could use a property on the store dimension indicating it's start date and use that to construct a set to sum over.

A great resource to get started with MDX is the Stairway to MDX series on SQL Server Central.

You should look at functions like PeriodsToDate.

You also have the option of resorting to VBA functions and could use that to use DateDiff and DateAdd if you can't get there with pure MDX.

You can even write your own SSAS Stored procedure if all else fails.


How can I select the latest record having one state where no later records exist with any other state?

SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
       ( SELECT *
         FROM tableX AS tt
         WHERE tt.widget = t.widget
           AND tt.state <> 'down'
           AND tt.`timestamp` > MAX(t.`timestamp`)
       ) ;

I think that you'll need two indices, one on (widget, state, timestamp) and one on (widget, timestamp, state) for efficiency.

This will work, too, and will be needing only one index, on (widget, timestamp, state):

SELECT t.widget, t.`timestamp`
FROM
        tableX AS t
    JOIN
        ( SELECT widget, MAX(`timestamp`) AS ts
          FROM tableX
          GROUP BY widget
        ) AS tm
            ON  tm.widget = t.widget
            AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;

Tested both at SQL-Fiddle: test


Perhaps the routing configuration change didn't take effect immediately.  To be sure test with a new broker.  Try this from a database other than the database containing the target service:
CREATE EVENT SESSION [ServiceBrokerRouting] ON SERVER
ADD EVENT sqlserver.broker_dialog_transmission_body_dequeue,
ADD EVENT sqlserver.broker_dialog_transmission_queue_enqueue,
ADD EVENT sqlserver.broker_forwarded_message_sent,
ADD EVENT sqlserver.broker_message_classify,
ADD EVENT sqlserver.broker_remote_message_acknowledgement

GO
ALTER EVENT SESSION [ServiceBrokerRouting] ON SERVER STATE = START;
go
DROP ROUTE [AutoCreatedLocal]
alter database current set single_user with rollback immediate
alter database current set new_broker
alter database current set multi_user

--Watch Live Data on the ServiceBrokerRouting event session
--test to ensure that new messages are delayed

--add the forwarder route
CREATE ROUTE Forwarder
    WITH
    ADDRESS = 'TCP://localhost:4022' ;

--Watch Live Data on the ServiceBrokerRouting event session
--test to ensure that new messages are routed through the transmission queue


In a CASE Statement, which is faster: "WHEN [field] in (0)" or "WHEN [field] = 0"?

The following construct:

c IN (x, y, z)

Gets extrapolated to:

c = x OR c = y OR c = z

It will logically follow that c IN (x) extrapolates to c = x. Therefore the first comment is absolutely right: you will not see a difference in performance except at the very edge case (where the size of the query text is actually impacted sufficiently by the choice), but even there it is hard to fathom the performance difference being worth mentioning.


Oracle - clean LOB files - recovering disk space

Only deleting lines from tables will not gain the disk space.
You need to reclaim the table space after deleting data.

I would suggest go through the Oracle documents on reclaiming space.


writing a trigger in order to change Table Name automatically

Sys.Trigger_event_types will have all the events that can be fired.

On checking, it has an event type called RENAME. You could use that write a DDL trigger, though this may not be ideal but serves your purpose.

alter trigger  tst
on database
for
RENAME
as
begin

declare @oldtblname sysname,@newtablename sysname
  SELECT
       @oldtblname= EVENTDATA().value
        ('(/EVENT_INSTANCE/Parameters/Param)[1]','nvarchar(max)') ;

          SELECT
       @newtablename= EVENTDATA().value
        ('(/EVENT_INSTANCE/Parameters/Param)[2]','nvarchar(max)') ;

exec otherdbname.dbo.sp_rename @oldtblname,@newtablename;

end

There are some catches, since this is database level scoped event, this will fire for all rename events, you may want to check event type and if it is only <ObjectType>TABLE</ObjectType>, then you may wish to continue.

Note:
I have  SQLServer 2012 and 2005 machines, this RENAME didn't worked on 2005 but worked on 2012. Not sure about 2008.


Why when I JOIN to a casted field in an index view, the execution plan warns about "type conversion in expression"?

An indexed view is expanded during query compilation unless the NOEXPAND table hint is used. The warning is generated on that expansion. In Enterprise Edition, the optimizer may later match the expansion back to the materialized view. This is a cost-based decision.

In anything other than Enterprise Edition (or equivalent) you must use a NOEXPAND hint to use the index(es) on a materialized view.

In Enterprise Edition, there are still good reasons to use the NOEXPAND hint:

Indexed Views and Statistics
Another Reason to Use NOEXPAND hints in Enterprise Edition


Is the limit on the number of databases in an Azure SQL managed instance a hard limit?

This is a hard limit that cannot be changed at this time.

The limit is imposed to ensure seamless operation of the instance and its performance.


Using `min_active_rowversion` for global temporary tables

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle


SQL Server Express in a shared hosting environment

Most hosting companies are going to want you to connect to a database server. Having the database on the web server is more for development ease, not for production.


Data Migration from Oracle to SQL Server

You can do this directly from SQL Server Management Studio.
Within SSMS, just right click the database, select Tasks -> Import Data, select an Oracle typed data source and set up your connection to the Oracle database.

Here's a really good description of the full procedure, including setting up an Oracle client on the machine doing the migration.

http://dbadiary.wordpress.com/2012/10/04/data-migration-from-oracle-to-sql-server-with-ssms/


Best practice between using LEFT JOIN or NOT EXISTS

The biggest difference is not in the join vs not exists, it is (as written), the SELECT *.

On the first example, you get all columns from both A and B, whereas in the second example, you get only columns from A.

In SQL Server, the second variant is slightly faster in a very simple contrived example:

Create two sample tables:

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

Insert 10,000 rows into each table:

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

Remove every 5th row from the second table:

DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

Perform the two test SELECT statement variants:

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

Execution plans:

The second variant does not need to perform the filter operation since it can use the left anti-semi join operator.


Here's a solution with a rational table design and reasonable reporting requirements.

SQL> -- create test tables
SQL> CREATE TABLE estevens.projects
  2  (
  3    project_id     NUMBER(6) NOT NULL,
  4    project_name   VARCHAR2(200) NOT NULL,
  5    project_budget NUMBER(10,2) not null,
  6    PRIMARY KEY (project_id)
  7  );

Table created.

SQL>
SQL> CREATE TABLE estevens.project_expenses
  2  (
  3    expense_id        NUMBER(6) NOT NULL,
  4    project_id        NUMBER(6) NOT NULL,
  5    expense_amt       NUMBER(10,2),
  6    expense_date      DATE,
  7    CONSTRAINT project_expense_fk
  8    FOREIGN KEY (project_id)
  9    REFERENCES projects(project_id)
 10  );

Table created.

SQL> --
SQL>
SQL> --  populate tables
SQL> insert into estevens.projects
  2           values (1,
  3               'DEMO PROJECT',
  4               1000.00
  5              )
  6  ;

1 row created.

SQL> insert into estevens.projects
  2           values (2,
  3               'ANOTHER DEMO PROJECT',
  4               1000.00
  5              )
  6  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (1,
  3               1,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (2,
  3               1,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (3,
  3               2,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> -- daily management report
SQL> col project_name for a10
SQL> select p.project_id,
  2         p.project_name,
  3         p.project_budget,
  4         sum(e.expense_amt) total_expense,
  5         case
  6           when sum(e.expense_amt) > p.project_budget
  7           then sum(e.expense_amt) - p.project_budget
  8           else null
  9         end OVER_BUDGET
 10  from estevens.projects p
 11  join estevens.project_expenses e on p.project_id = e.project_id
 12  group by p.project_id,
 13         p.project_name,
 14         p.project_budget
 15  order by p.project_id
 16  ;

PROJECT_ID PROJECT_NA PROJECT_BUDGET TOTAL_EXPENSE OVER_BUDGET
---------- ---------- -------------- ------------- -----------
         1 DEMO PROJE           1000          1200         200
           CT

         2 ANOTHER DE           1000           600
           MO PROJECT

2 rows selected.

SQL>  --
SQL> drop table estevens.project_expenses;

Table dropped.

SQL> drop table estevens.projects;

Table dropped.

SQL> --
SQL> spo off

You could also expand the idea to have an 'expense remaining' column.

Note that I have also replaced MS-style cAmElcAsE names with oracle-style names.  When in Rome . . .


Failed to create instance: Standalone DB Instances are not supported for Amazon RDS Aurora

Aurora databases use the database cluster API rather than the database API.

This means in terraform for instance you need to create them via the rds_cluster and rds_cluster_instance resources.

In ansible using this module might work: https://github.com/manicminer/ansible-modules-rds-cluster


I am not sure that I would consider this to be an issue of inheritance, and from the description, I would say that the Address table would depend on the other two tables instead of those two tables depending on the Address table.

But that minutia aside, I would recommend:

If you are only storing U.S. or maybe U.S. and Canada addresses, then you can do a "StateRegion" table. But in that case you don't really need a Country table.
If you are storing addresses from many countries, then you would want a Country table, but the StateRegion table is trickier as that varies greatly between countries. I will have to look to see if there is a standardized list for that.

If you want to attempt a "StateRegionProvince" table, check out this info on ISO 3166-2: http://en.wikipedia.org/wiki/ISO_3166-2

For countries, a Country table should be just the ISO list of countries. There is really no need for a separate ID field as the ISO country codes are an acceptable standard (hence the "S" in ISO :-). Just make CountryCode (I prefer the 2-character codes over the 3-character codes) a CHAR(2) NOT NULL.

Full ISO 3166-1 list of 2 character codes (most up to date): https://www.iso.org/obp/ui/#search
ISO list of 2 and 3 character codes: http://www.nationsonline.org/oneworld/country_code_list.htm
ISO 3166-1 and others list: http://www.geonames.org/countries/

If storing non-US/Canada-only addresses:

For "Zip Code", do a VARCHAR(15) field for "Postal Code" as there are various ways of doing those as well.

VARCHAR is fine as the only characters used are: 0-9, A-Z, [space], and [hyphen]
Postal Codes general info: http://en.wikipedia.org/wiki/Postal_code
Postal Code formats by country: http://en.wikipedia.org/wiki/List_of_postal_codes

Use NVARCHAR instead of VARCHAR for the Address, City, StateRegion, etc fields.

So, generally speaking:

CREATE TABLE County
(
   CountryCode CHAR(2) NOT NULL PRIMARY KEY,
   CountryName VARCHAR(100) NOT NULL
);

CREATE TABLE Address
(
   AddressID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   UserID INT NOT NULL, -- FK to some User table
   AddressLine1 NVARCHAR(100) NOT NULL,
   AddressLine2 NVARCHAR(100) NULL,
   City NVARCHAR(100) NULL,
   StateProvince NVARCHAR(100) NOT NULL, -- or INT FK to StateProvince table?
   PostalCode NVARCHAR(20) NOT NULL,
   CountryCode CHAR(2) NOT NULL -- FK to Country table
)


Oracle How to find Debug Compiled Stored Procedures/Functions

I think DBA_PLSQL_OBJECT_SETTINGS gives more appropriate information.


How to copy a table in a production server without WRITE lock and without writing to a file

I suppose you mean you have a "read lock on the table" (you can't write, also known as shared locks). InnoDB writes like yours create read locks (writes have to wait). Reads can work continue without problem. Be careful with the terminology, a write lock is an "exclusive lock".

You can avoid most of the lock problems if you avoid the extra locks that innodb sets because of the statement based replication:

binlog-format = ROW
transaction-isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2

Be careful, because if the server is a master, or if you need point in time recovery, in certain cases it may create inconsistences. It also changes the locking logic, so be careful, as it may change the application behaviour. However, if you are not using replication or it is a development database, it will be safe. With this, you will be able to update and insert new records on the table at the same time the command runs.

The second part (kill the query without doing a rollback) is not possible for InnoDB due to internal consistency requirements (atomicity of transactions). However, there are several tricks to overcome it. One is to restart the server with innodb_force_recovery = 4 and then drop the table. A better approach would be to do the copy in chunks - a tool like pt-archiver with the --no-delete option, which allows you to copy in several transactions controlling the impact on the server (by the way, probably solving your first problem, too). The tool is a free and open source perl script, easy to use.


Scheduled stored procedure on all databases in my server

Use the better version of the undocumented sp_MSForEachDB to iterate through the databases in a SQL Server Agent job.

Or simply use a WHILE loop yourself on sys.database entries if you have a naming pattern.


If I were you, I would use a classic normalised database structure rather than using JSON. I think that this is what you are driving at with your Option 3 (By using row constructors)?

I constructed a normalised version of your data. A sample is shown below - the full data is available at the sqlfiddle here.

CREATE TABLE country_location
(
  cl_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  country INTEGER NOT NULL,  -- you might wish to use TINYINT for some fields
  location INTEGER NOT NULL,
  cl_code CHAR (5) NOT NULL,
  cl_type INTEGER NOT NULL,
  cl_status INTEGER NOT NULL,
  CONSTRAINT country_location_code_uq UNIQUE (country, location, cl_code)
);

-- Experiment with these indexes on your own data!
-- but the plans seem good - better than with no index.

CREATE INDEX cl_ix ON country_location (country, location);
-- CREATE INDEX country_ix ON country_location (country);
-- CREATE INDEX location_ix ON country_location (location);

And the data:

INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 6, '0Dxsh', 1, 1);
INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 16, '0Dxsh', 1, 1);
INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 4, '0Dxsh', 1, 1);
INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 8, '0Dxsh', 1, 1);

A sample SELECT of the data from this table:

cl_id   country location    cl_code cl_type cl_status
    1         3        6      0Dxsh       1         1
    2         3       16      0Dxsh       1         1
    3         3        4      0Dxsh       1         1
    4         3        8      0Dxsh       1         1

There's not enough data to be really sure, but with the indexes, the PLANs seem to be optimal and you would get excellent results on single country-location record searches.

I don't know enough about your data to do the joins - I've created the tables in the fiddle and I'm sure that with the proper FOREIGN KEYs, your system would be very performant.


This turned out to be my own fault and not making sure that the Foreman host had a postgresql93 client installed so that the rake task that imports the database could actually do the pg_restore on the valid dump.

I was able to use pg_dump -Fp to get the plain text and validate that my data was in there. Thank you everyone for your help. Chalk this one up to a newbie trying to learn Postgres.

Cheers.


Can't execute dynamic DDL, argument is NULL

@dezso already explained the direct cause of your error. But the whole function is needlessly convoluted. Use instead:

CREATE OR REPLACE FUNCTION part_test()
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN
   FOR _sql IN
      SELECT format ('
         CREATE TABLE IF NOT EXISTS harry_%s (
           CHECK (logged_time >= date %L AND logged_time < date %L)
         ) INHERITS (harry)'
       , to_char(d, 'YYYY_MON_DD')  -- safe against SQL injection!
       , d::date, d::date + 1)
      FROM  generate_series (timestamp '2015-09-01'
                           , timestamp '2015-09-09'  -- 1 day before last
                           , interval  '1 day') d
   LOOP
   -- RAISE NOTICE '%', _sql;  -- to debug
      EXECUTE _sql;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Major points

Use format() to make concatenating statements simpler and safer.
No need for date_trunc(), the result of to_char() is the same.
You don't need a record variable, it's just text.
You don't need a window function, just add 1 day to the start.
Always use ISO 8601 format for date and timestamp literals. Those work irregardless of your current locale settings.
I am using default format in the CHECK constraint now. (date parameters to format() are cast to text with default format automatically.) You might consider the pattern 'YYYY_MM_DD' instead of 'YYYY_MON_DD' for the table name as well.
Resulting table names are lower case (like they should be), because I did not double-quote.

Are PostgreSQL column names case-sensitive?

Not sure why you compare logged_time to a date, and not a timestamp or timestamptz. There may be a problem with time zones / volatility lurking there. Consider timestamptz. Or if the column is actually a date type, don't call it "time" ...

The formatted date happens to be safe against SQL injection. Else you have to do more. Consider:

SQL injection in Postgres functions vs prepared queries


Should I have both Transaction Log Backups and Log Shipping

You will only have one stream of log backups, and that would of course in this situation be your log shipping backups. If you keep doing log backups in your maint plan, then the log shipping restore will fail as soon as maint plan log backup was done "in between" two log shipping log backups. A: Remove the log backups from your maint plans. B: Yes, it does - it is a regular log backup, after all.


How can I set Timeout by View, User or Role?

Given the constraints and in addition to query optimizations and tuning.

Three stage approach, to limit potential conflicts when reporting from production server –

Use Resource Governor to limit report logins to Max 20% Min 0
Use an Operating Level Agreement (OLA) to require applications reporting from production databases to a 5 second timeout (DBPROP_COMMANDTIMEOUT)
Use a trace to track SQL queries against production database and confirm OLA compliance (filter by login)

Rationale:

Resource Governor, does not have any contra-indications when limiting occasional report queries against OLTP with constant connect
OLA is not an iron clad guaranty, but if the timeout is used, it limits the potential exposure to the main application (OLTP)
Trace permits follow-up and validation of solution.

References

Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866(v=sql.105).aspx

White paper  “Using the Resource Governor” from the Microsoft Download Center
http://msdn.microsoft.com/en-us/library/ee151608%28v=sql.100%29.aspx


MySQL 5.7 Community Server End of Life/Support

As per MySQL documentation here the below you can find the life cycle for each version of application MySQL

Current releases

Release        Release date              End of life

MySQL 8.0      April 19, 2018
MySQL 5.7      October 21, 2015          October 21, 2023
MySQL 5.6      February 5, 2013          February 5, 2021

All releases

Release        Release Date            End of life

MySQL 8.0      April 19, 2018
MySQL 5.7      October 21, 2015       October 21, 2023
MySQL 5.6      February 5, 2013       February 5, 2021
MySQL 5.5      December 3, 2010       December 3, 2018
MySQL 5.1      November 14, 2008      December 31, 2013
MySQL 5.0                             January 9, 2012

For further your ref here and here


SQL Server 2005 - Best way to constraint a Datetime field as Date

You can simplify it a little bit, since SQL Server knows with datetime that it can treat the value as the number of days since 0. I prefer to use '19000101' over zero, since it's slightly less cryptic what's going on:

ALTER TABLE dbo.MyTable
ADD CONSTRAINT CHK_MyTable_DateCode
CHECK (DateCode = DATEDIFF(DAY, '19000101', DateCode));

However, I find it an odd choice to scold users who might use built-ins like GETDATE() or parameters like @OrderDate which they don't realize contains a time component. So a slightly cleaner way to solve this would be with a trigger:

CREATE TRIGGER dbo.MyTable_RoundCodeDate
ON dbo.MyTable
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.MyTable(Code, DateCode)
    SELECT Code, DATEDIFF(DAY, '19000101', DateCode)
    FROM inserted;
END
GO

Using an INSTEAD OF trigger is not popular with a few people here, but I think it is better to massage those values before inserting than after. PK will be handled the same as without a trigger - violations will still be caught at insert time (with an after trigger, though, you wouldn't hit the violation until after the trigger code runs, which theoretically at least means more logging). In fact you could add a GROUP BY to the trigger to silently ignore duplicates from the source statement, e.g. INSERT ... VALUES(1,GETDATE()), (1,CURRENT_TIMESTAMP);.


Calculate difference between current value and next value without CTE/ inline table

I think efficiency is more reliant on the fact that you are doing 2 scans against the table. You could try using a window function instead:

SELECT uid, visit_date
     , LEAD(visit_date) OVER (PARTITION BY uid
                              ORDER BY visit_date) as next_visit_date
FROM Visit E1

Now you can calculate the diff from there

SELECT uid, DATEDIFF(day, visit_date,
                          LEAD(visit_date) OVER (
                              PARTITION BY uid
                              ORDER BY visit_date
                          ))
FROM Visit E1


What's the best way to deal with a database with a big table with information that will RARELY be used?

You basically have a couple of options, but in the end if you have records, they must occupy space.

You can try to compress the data. This will reduce disk space, but has side effects (it can make queries slower, for example). And it may not be available in your version of SQL. If I remember correctly, in SQL 2008 r2, compression requires Enterprise Edition. Brent Ozar's website talks a bit about some other side effects.

Otherwise, the best you can do is either move the data to cheaper (slower) disks via partitions and/or file groups or work out a retention policy that lets you purge data past a certain age (possibly after exporting it?)


SQL Sentry Plan Explorer - Execution Mode in plan diagram?

It doesn't show it in the plan diagram tooltip but in the plan tree tab, go to column chooser and select estimated and actual execution mode.


any not aggregated column must be included in GROUP BY

SELECT COL1, COL2 ,SUM(total_request_count) AS total_calls  FROM API_REQUEST_SUMMARY GROUP BY COL1, COL2

alternative if many columns for GROUP BY

SELECT T1.*, T2.total_calls FROM API_REQUEST_SUMMARY T1 LEFT JOIN
(SELECT GROUP_COLUMN, SUM(total_request_count) AS total_calls  FROM API_REQUEST_SUMMARY GROUP BY GROUP_COLUMN) T2 ON T1.GROUP_COLUMN = T2.GROUP_COLUMN

it is my work solution, may be other present as well


Can queries containing deterministic user defined functions be cached in mysql query cache?

Based on some experimentation with MySQL 5.5.33, the answer is "no".  I have a function definition which is deterministic (it's a simple date calculation) and is marked as DETERMINISTIC.  If I use it in a query, the query is not cached.  If I replace the call by its definition in terms of the built-in date_sub() function, the query is cached.


RANK will deal with multiple max values (instead of ROW_NUMBER)

SELECT
   dtimesent, Request, ResponseTime
FROM
    (
    select dtimesent, Request, ResponseTime ,
       RANK() OVER (ORDER BY ResponseTime DESC) AS rn
    FROM allrequests
    where Request like '%Requestsa%'
    ) T
WHERE
   rn = 1

Also see How to get the MAX row please


Like @Akina says in his comment, "," and CROSS JOIN is equivalent:
A, B   <=>    A CROSS JOIN B

This is also known as CARTESIAN PRODUCT. The result will have a cardinality (size) of:
| A, B | = | A | * | B |
We can restrict the result so that it becomes a subset of the CROSS JOIN like:
A, B WHERE <some condition>

The corresponding operation becomes
A INNER JOIN B ON <some condition>

INNER can be left out:
A JOIN B ON <some condition>

OUTER JOIN is a bit more complicated to explain in terms of sets, but let's start with a LEFT OUTER JOIN:
A LEFT OUTER JOIN B ON <some condition>

This means:
A JOIN B ON <some condition>
UNION
A WHERE it does NOT EXISTS a corresponding B

I deliberately left out any information regarding columns in the resultset.
A RIGHT OUTER JOIN B ON <some condition>

is equal to
A JOIN B ON <some condition>
UNION
B WHERE it does NOT EXISTS a corresponding A

Finally FULL OUTER JOIN is a UNION between a LEFT and a RIGHT outer JOIN:
A FULL OUTER JOIN B ON <some condition>

is equal to:
A LEFT OUTER JOIN B ON <some condition>
UNION
A RIGHT OUTER JOIN B ON <some condition>

To get it completely right we would need to extend the explanation with the columns involved.
I use to think that the "," notation was short and sweet, but after spending years and years of tuning, modifying and extending existing SQL I now prefer the ANSI JOINs over the "," ones. As shown in the comments by ypercubeᵀᴹ fiddle strange things can happen if you have a "," join and the extend it with an ANSI join. I also find it much easier to convince myself that I have gotten the join conditions correct when they are closely located with the join, instead of the WHERE clause:
SELECT ...
FROM A
JOIN B
    ON ...
   AND ...
JOIN C
    ON ...
   AND ...

vs
SELECT ...
FROM A, B, C
WHERE ...
  AND ...
  AND ...
  AND ...


You cannot assume that a user stays logged in forever.  Glitches occur everywhere, especially in networks.  You must code to recover from such.

When running in a browser, each page is necessarily a separate database connection.  Code accordingly.


Strategy on Standby restore option for hourly log shipping file

Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode?

Either will work, but it is faster to apply all the logs except the last one using NORECOVERY. The last one applied uses STANDBY, making the database available for read-only access. SQL Server has to do extra work to make the database available for reading in a transactionally-consistent state. It then has to undo that work (using the standby file) when you ask it to apply the next log backup. In short, specifying STANDBY unnecessarily can really slow things down because you end up doing and undoing part of the recovery process (see link by Paul Randal below).
In your case, that would mean applying 23 transaction log backups with NORECOVERY, then the 24th with STANDBY. Next day, you do the same thing.
Note that you will need to ensure that nothing is connected to the database in standby mode when you go to start the next restore sequence. That ought not be an issue in your case, since you control all access to the database. If you do need to forcibly disconnect other users:
ALTER DATABASE DbName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Don't forget to make the database available to other users again later with:
ALTER DATABASE DbName
SET MULTI_USER;

If I accidentally run a script to restore this database as "NoRecovery or Recovery" mode, is there way to change the mode back to "Standby"

You can change from NORECOVERY to STANDBY and vice-versa e.g.:
-- From norecovery to standby
RESTORE DATABASE DbName
WITH STANDBY = N'C:\StandbyFiles\DbName.sby';

-- From standby to norecovery
RESTORE DATABASE DbName
WITH NORECOVERY;

You do not need to change from STANDBY to NORECOVERY to apply the next set of log backups.
Once the database is recovered (WITH RECOVERY), you cannot go back, and must start the whole restore sequence again. You would never do that in your scenario.
The standby file will be automatically deleted (or reused) by the next restore step, but SQL Server does not lock the file while it is not actively in use by a restore command. Treat the file with the same care you would any other database file - if it is lost, so is the database.
Recommend reading:

Reporting From a Log Shipping Secondary in STANDBY mode by Kendra Little.
Log shipping performance problems by Paul Randal


30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd@LIVE:REPORT

I want to do the same thing and about to test for myself in next week or so.

are you sure @LIVE:REPORT is correct?
LIVE:REPORT try remove @
try --drop-first ( old way was --force < 1.4.2 )

So:

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd  LIVE:REPORT --drop-first

This is what I am going to test.  I am admittedly a noob - fair warning.

After thinking I may tweak script a little.

0 12 * * * mysqldbcopy --source=user:pass@host:port:socket --destination=user:pass@host:port:socket orig_db:new_db --drop-first

I will most likely make cred.cnf file to call with credentials for both source and destination.

cred.cnf file like this (mine hidden and locked down)...

[source]
port=3306
user=user
password=secret
host=pathtodb

[destination]
port=3306
user=user
password=secret
host=pathtodb

This is how my testing cron job will look...

0 12 * * * mysqldbcopy --source=/path/to/cred.cnf[source] --destination=/path/to/cred.cnf[destination] orig_db:new_db --drop-first

good luck.  I will follow-up when I get time to test my system and confirm this works or not.


How to use OPENROWSET with MSOLEDBSQL driver?

Although, the MSOLEDBSQL documentation recommends Authentication=ActiveDirectoryIntegrated to enable new encryption and certificate validation behavior, I don't think the stricter validation will allow a self-signed certificate, trusted or not.

Try removing the Authentication keyword and instead specify Trusted_Connection=Yes if you are using a self-signed cert.

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=<Computer Name>;Database=<Database Name>;TrustServerCertificate=Yes;Trusted_Connection=Yes;'
    , 'SELECT * FROM [database name].[dbo].[table name];'
    ) c;


Typically any OLTP database that can't fit into memory has random reads.  If you are reading that much from your OLTP database that you need EFDs (what EMC calls SSDs) then you need to first look at putting a LOT more RAM into the database server.  It will be a lot cheaper to prevent the reads than to make the reads faster.  Those EMC EFDs run about $20k each.


Thanks to @Colin's recommendation to read an article about using lag/sum to create virtual groups, I come up with the following solution:

WITH
  lookup_is_successful_change AS (
    SELECT
      esl1.id,
      esl1.event_id,
      esl1.lookup_is_successful,
      CASE
        WHEN (
          lag(esl1.lookup_is_successful) OVER (PARTITION BY esl1.event_id ORDER BY id ASC) != esl1.lookup_is_successful OR
          lag(esl1.lookup_is_successful) OVER (PARTITION BY esl1.event_id ORDER BY id ASC) IS NULL
        )
        THEN 1
        ELSE 0
      END lookup_is_successful_change
    FROM event_seating_lookup esl1
    ORDER BY esl1.id DESC
  ),
  look_is_successful_change_group AS (
    SELECT
      lisc1.id,
      lisc1.event_id,
      lisc1.lookup_is_successful,
      sum(lisc1.lookup_is_successful_change) OVER (PARTITION BY lisc1.event_id ORDER BY id ASC) group_vid
    FROM lookup_is_successful_change lisc1
    ORDER BY lisc1.id DESC
  )
SELECT DISTINCT ON (liscg1.event_id)
  liscg1.event_id,
  liscg1.lookup_is_successful,
  row_number() OVER (PARTITION BY liscg1.event_id, liscg1.group_vid ORDER BY id ASC) consecutive_lookup_is_successful_without_change
FROM look_is_successful_change_group liscg1
ORDER BY liscg1.event_id, liscg1.id DESC;

http://www.sqlfiddle.com/#!15/7f550/5

This query is not very performant, though.


How to load .dat files to oracle database

Not all .dat files are the same and it's hard to determine from the screenshot what the format is.

If you extracted that from a telecom switch, you will need to contact the telecom vendor to ask if they have a data provider for the file format and use that provider to make a connection to the .dat file from within your ETL tools.

If they don't have a provider, which is entirely plausible, you will need to extract the data in another format such as .csv. Again, contact the vendor to see if that is an option


Why would the Parallelism (Repartition Streams) Operator Reduce Row Estimates to 1?

Query plans with bitmap filters can sometimes be tricky to read. From the BOL article for repartition streams (emphasis mine):

  The Repartition Streams operator consumes multiple streams and produces multiple streams of records. The record contents and format are not changed. If the query optimizer uses a bitmap filter, the number of rows in the output stream is reduced.

In addition, an article on bitmap filters is also helpful:

  When analyzing an execution plan containing bitmap filtering, it is important to understand how the data flows through the plan and where filtering is applied. The bitmap filter and optimized bitmap is created on the build input (the dimension table) side of a hash join; however, the actual filtering is typically done within the Parallelism operator, which is on the probe input (the fact table) side of the hash join. However, when the bitmap filter is based on an integer column, the filter can be applied directly to the initial table or index scan operation rather than the Parallelism operator. This technique is called in-row optimization.

I believe that's what you're observing with your query. It is possible to come up with a relatively simple demo to show a repartition streams operator reducing a cardinality estimate, even when the bitmap operator is IN_ROW against the fact table. Data prep:

create table outer_tbl (ID BIGINT NOT NULL);

INSERT INTO outer_tbl WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

create table inner_tbl_1 (ID BIGINT NULL);
create table inner_tbl_2 (ID BIGINT NULL);

INSERT INTO inner_tbl_1 WITH (TABLOCK)
SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 2000000 - 2) NUM
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

INSERT INTO inner_tbl_2 WITH (TABLOCK)
SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 2000000 - 2) NUM
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Here is a query that you should not run:

SELECT *
FROM outer_tbl o
INNER JOIN inner_tbl_1 i ON o.ID = i.ID
INNER JOIN inner_tbl_2 i2 ON o.ID = i2.ID
OPTION (HASH JOIN, QUERYTRACEON 9481, QUERYTRACEON 8649);

I uploaded the plan. Take a look at the operator near inner_tbl_2:

You may also find the second test in Hash Joins on Nullable Columns by Paul White helpful.

There are some inconsistencies in how the row reduction is applied. I was only able to see it in a plan with at least three tables. However, the reduction in expected rows seems reasonable with the right data distribution. Suppose that the joined column in the fact table has many repeated values that aren't present in the dimension table. A bitmap filter might eliminate those rows before they reach the join. For your query the estimate is reduced all the way to 1. How the rows are distributed amongst the hash function provides a good hint:

Based on that I suspect that you have a lot of repeated values for the Object1.Column21 column. If the repeated columns happen to not be in the stats histogram for Object4.Column19 then SQL Server could get the cardinality estimate very wrong.

I think that you should be concerned in that it might be possible to improve the performance of the query. Of course, if the query meets response time or SLA requirements then it may not be worth further investigation. However, if you do wish to investigate further there are a few things that you can do (other than updating stats) to get an idea on if the query optimizer would pick a better plan if it had better information. You could put the results of the join between Database1.Schema1.Object10 and Database1.Schema1.Object11 into a temp table and see if you continue to get nested loop joins. You could change that join to a LEFT OUTER JOIN so the query optimizer won't reduce the number of rows at that step. You could add a MAXDOP 1 hint to your query to see what happens. You could use TOP along with a derived table to force the join to go last, or you could even comment out the join from the query. Hopefully these suggestions are enough to get you started.

Regarding the connect item in the question, it is extremely unlikely that it is related to your question. That issue doesn't have to do with poor row estimates. It has to do with a race condition in parallelism that causes too many rows to be processed in the query plan behind the scenes. Here it looks like your query isn't doing any extra work.


Process staging table by getting unique rows only (incremential)

OK, let's look at a simple change to the code snippet from the previous post.

-- Create indexes to support the unique merge of the two tables.
CREATE TABLE StagingTable ([Key] INT, A INT, B INT);
  CREATE INDEX Staging_MergeHelp ON StagingTable (A, B);
CREATE TABLE FinalTable ([Key] INT IDENTITY, A INT, B INT);
  CREATE INDEX Final_MergeHelp ON FinalTable (A, B);
--
--  A first run of your job to insert unique values
--
INSERT INTO STAGINGTABLE VALUES( 1,1,1)
INSERT INTO STAGINGTABLE VALUES( 2,1,2)
INSERT INTO STAGINGTABLE VALUES( 3,1,1)
INSERT INTO STAGINGTABLE VALUES( 4,1,2)
INSERT INTO STAGINGTABLE VALUES( 5,2,1)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--
--  First Answer
SELECT * FROM FinalTable
--
--  A second run of your job to insert NEW unique values
--
INSERT INTO STAGINGTABLE VALUES( 6,1,1)
INSERT INTO STAGINGTABLE VALUES( 7,1,2)
INSERT INTO STAGINGTABLE VALUES( 8,3,1)
INSERT INTO STAGINGTABLE VALUES( 9,1,2)
INSERT INTO STAGINGTABLE VALUES( 10,2,4)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--
-- Cumulative answer after including newer values
SELECT * FROM FinalTable
--
DROP TABLE FinalTable
DROP TABLE StagingTable

If this FinalTable gets really big, then there will be some serious overhead.  That is why this time I created indexes on both StagingTable and FinalTable for columns A and B.  This will help the joins run more efficiently.

However, there are also more powerful tools, such as SSIS which could be a big help as the job gets bigger.  The code above is just a simple picture of how to construct the logic.


Providers in SQL Server linked server configuration

What is Allow Inprocess : as the name says by selecting Allow Inprocess, SQL Server allows the provider to be instantiated or allows the provider to run as an In Process server. When the option is not set, the default behavior is to allow the provider to run outside the SQL Server process.
The question that arises is what is the benefit of running the process inside the SQL Server process or outside the SQL Server process.
Running the process outside the SQL Server Process:
Allowing the provider to run outside the SQL Server Process helps to protect the SQL Server process from errors in the provider, as when the provider runs outside the SQL Server process Updates or inserts involving column data types like  (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) are not allowed.
Running the process inside the SQL Server Process:
By setting this option in the SQL Server providers, we will be able to handle the data types like (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) including long columns, image and text data.
In case of excel data we can see that when we import an excel data into the database tables without having predefined datatypes, by default the datatypes of some of the columns may be assigned to nvarchar(max) or ntext etc, so if we do net set the Allow Inprocess it will throw error.
If we don’t have such scenarious it is always advisable to leave the default settings of the SQL Server as it is. Only in cases where there is exception we will have to enable the option.


Selecting a value from a row where another column is max

In your case you can simply exchange the column names, but remember that text sorts differently then integer 1.10 comes before 1.2, so if you get such numbers you have to check and change your approach. see https://lists.mysql.com/mysql/183349
SELECT bug.`id`,
       Max(report.`date`),
       Count(report.`id`),
       Max(version.`name`),
       bug.`id`
FROM   `bug`
       LEFT OUTER JOIN `stacktrace`
                    ON ( stacktrace.`bug_id` = bug.`id` )
       CROSS JOIN `version`
                  LEFT OUTER JOIN `report`
                               ON ( report.`stacktrace_id` = stacktrace.`id` )
WHERE  stacktrace.`version_id` = version.`id`
GROUP  BY bug.`id`
ORDER  BY Max(report.`date`) DESC;


How to use tsvector_update_trigger with jsonb column?

You don't.  You will need to write your own trigger function.  The built in one only works for text columns, not for jsonb columns and not for functions over columns.
On newer versions, an alternative would be use a generated column.
create table tags (
    translations jsonb,
    tsv tsvector generated always as (to_tsvector('pg_catalog.english',translations#>>'{en,name}')) stored
);


Restore database to point in time from full backup only

No, you need to have taken transaction logs that cover the time frame you want to use with STOPAT. You can't do this from a full database backup only - that is just a one-time copy, and that is why we have different types of backups (full, log, diff).

If you have taken transaction log backups in between your full backups, please update the question with that information, and we can show you steps for restoring to a point in time. But those tutorials are easy to find, so I suspect you're asking here because you haven't taken log backups. If that is the case, you're kind of stuck - you may be able to use some kind of log reading utility to get at that information from the current transaction log, but I wouldn't be confident about success there - and it doesn't seem like that product, at least, has a feature called "restore to point in time" - it allows you to recover specific pieces of information, like restoring a table or undoing a truncate. So I think that would be quite a manual "undo transaction A, undo transaction B, undo transaction C, etc etc" to get at your eventual result. Probably still less work than trying to do that yourself from fn_dblog output.

This post is rather timely:

The Importance of a Full Backup in SQL Server

And these may be useful too:

Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN
Paul Randal's backup myths
Paul Randal's restore myths

To demonstrate that you can't now take a log backup and try to restore to some point in time prior to the last full backup. First, run this script:

USE master;
GO
CREATE DATABASE splunk;
GO
ALTER DATABASE splunk SET RECOVERY FULL;
GO
USE splunk;
GO
SELECT GETDATE(); -- use this in STOPAT
GO
WAITFOR DELAY '00:00:10';
GO
BACKUP DATABASE splunk TO DISK = 'c:\temp\sp_full.bak' WITH INIT;
GO
WAITFOR DELAY '00:00:10';
GO

The above simulates what you have: a full backup, taken after the time you want to restore to, and that's it.

Now, take a tail-log backup:

BACKUP LOG splunk TO DISK = 'c:\temp\sp_tail.trn' WITH INIT, NO_TRUNCATE;
GO

Now, let's start the recovery process. First, restore the full backup with norecovery:

USE master;
GO
RESTORE DATABASE splunk FROM DISK = 'c:\temp\sp_full.bak' WITH REPLACE, NORECOVERY;
GO

Now, attempt to restore the tail-log backup with the STOPAT parameter set to whatever was output above for GETDATE():

RESTORE LOG splunk FROM DISK = 'c:\temp\sp_tail.trn'
  WITH NORECOVERY, STOPAT = '<paste GETDATE() output from above>';

I get the following error messages:

  Msg 4335, Level 16, State 1
   The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
   Msg 3013, Level 16, State 1
   RESTORE LOG is terminating abnormally.

In order to restore to a point in time, you need to have taken at least one transaction log backup after a full backup (not necessarily after the last full backup, but after one that is valid and can be restored), and after the point in time you want to restore to (but before the next full database backup).

This is why we typically take full backups nightly or weekly, and transaction log backups every 15 minutes or every hour (and sometimes diff/filegroup backups in there too, depending on business needs, RTO/RPO, etc). In full recovery model there is certainly no use case where you'd want to take a full backup once a week, and nothing in between. This can be a very expensive lesson to learn the hard way, but I bet it doesn't happen to you twice.

This post probably has a lot of useful points about backups, why we take them, the usefulness of log backups, etc. - I highly recommend bookmarking it and giving it a thorough read once you solve your current problem.

Why Does the Transaction Log Keep Growing or Run Out of Space?


It depends on what you want to log.

One solution (taken from here) that does not use the general query log is the following:

CREATE DATABASE admin;

CREATE TABLE admin.connections (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, connect_time DATETIME NOT NULL, user_host VARCHAR(50) NOT NULL, connection_id INT UNSIGNED NOT NULL);

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

This solution uses the init_connect MySQL variable which stores SQL queries executed each time a user logs in. Now each time a user logs in, it will be logged into the table admin.connections. Be sure to check the permissions of all of your users to ensure that they can insert a record into the table.

Another solution, available only for MySQL Enterprise and that I include here for completeness' sake, is to install the MySQL Enterprise Audit plugin.


New boolean column in big table OR - bad idea ? - new related 0-1 table

From a theoretical point of view, booleans are a bit suspicious (not necessarily wrong though). The idea is that we store true propositions, the absence of something is considered false. So, it's definitely nothing wrong with your suggestion from a theoretical viewpoint. Without knowing what your attribute represents it's difficult to be more specific than that.

From a practical point of view, since most of the rows are false, you will save some space by not storing false for almost all rows.

The downside is that you will have to do an outer join, which may affect performance.

EDIT: As pointed out by @eckes in his comment, deletes in A will have to investigate ABitAttribute for presence of the id beeing deleted. The same goes for updates (if the id column being updated). For inserts, there should be no affect


DBCC SHRINKFILE for Log and Data when using Availability Groups

Denis P. wrote a basic SQL Script to resize the log files in an AG, so here is the overall process:

Make sure replication has finished, as you can't shrink the files while the logs are still being sent to a secondary replica
Figure out what new size you want the data and log files to be. There are plenty of SQL scripts to find this information, or you can use the database properties or Shrink File Task wizard in SSMS to figure out how much space is still in use.
On the primary use the script below to resize your files. It may require multiple executions to take effect.
Once the primary files have the correct size and the replication queue has stabilized, you may need to restart the SQL Server service on the secondaries for the new file size to take effect on those systems.

TSQL Scripts:

--Run on primary to shrink db file to 10000 MB. May need to run multiple times.
DBCC SHRINKFILE (N'MyDatabaseName', 10000, NOTRUNCATE)
DBCC SHRINKFILE (N'MyDatabaseName', 10000)

--Check data file size
USE Master
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MyDatabaseName'
ORDER BY SizeMB DESC

--Once data file is resized and replication has finished, start resizing log file

--Sorted view of log file sizes and percent used
USE MyDatabaseName
Drop table IF EXISTS #tmplogs
CREATE TABLE #tmplogs (
  DatabaseName varchar(100)
  , LOGSIZE_MB decimal(18, 2)
  , LOGSPACE_USED decimal(18, 2)
  , LOGSTATUS decimal(18, 0)
)
INSERT INTO #tmplogs EXEC('DBCC SQLPERF(LOGSPACE);')
SELECT * from #tmplogs ORDER BY LOGSIZE_MB DESC, LOGSPACE_USED DESC

--Run on primary to shrink log file. You must take a backup of the log file before it can be truncated
BACKUP LOG MyDatabaseName TO DISK = '\\Backups\SQL\_Trans\My_AG\MyDatabaseName\MyDatabaseName_LOG_20170721_200008.trn' WITH NOFORMAT, INIT, NAME = N' Trn Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
DBCC SHRINKFILE (N'MyDatabaseName_log' , 2000, NOTRUNCATE)
DBCC SHRINKFILE (N'MyDatabaseName_log' , 2000)

--Again this may require multiple executions to remove unused space
--Also the file size on the replicas may not decrease until the SQL Server service is restarted on those systems

And of course once you are finished you may have index fragmentation or other performance issues that can occur when using DBCC SHRINKFILE. In our case this is a small database where we don't care too much about the performance and there are weekly jobs to handle index maintenance, so we would rather reclaim the unused space.

The script helped us reclaim a total of over 250GB of unused data and log file space!


Is having all entity tables inherit from one main entity table a bad idea?

Yes, there are some issues with this design.  The pattern is Entity-Attribute-Value at least for the EntityChange table.  See the link for all the reasons why this almost always should be avoided.

The Entity table itself may not be EAV, but it does nothing but increase complexity without adding any value.  To know if a Contact has another tenant, another version, another draft, or is logically deleted you have to join.  Even if none of these are applicable to the Contact table, your design dictates their existence and accommodation.

If you decide that Contacts can be logically deleted then add a deleted column to the contact table.  If you decide you need different drafts of Contacts, then add a drafts column, etc.  Essentially, there is no advantage to moving these attributes to a common table.  It increases storage requirements, reduces performance more than it helps, and make the system more complex.


How do you list all logins for Sql Azure?

A login is for authenticating to the server.

A login maps to a database user to access the database.

SELECT * from master.sys.sql_logins will list logins for Azure.


I assume that the user is a common user connected to the root container.

A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects

SQL> alter user c##hr set container_data=all container=current;

User altered.

SQL> conn c##hr
Enter password:
Connected.
SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
ORCLPDB

SQL>


Recover PostgreSQL password with admin access to host

This is easy to fix. Edit pg_hba.conf to add a line like this:

host all postgres 127.0.0.1/32 trust

(You may need to restart Postgres server after editing it).

Then, use this:

psql -d postgres -U postgres

You should be able to get admin access without password now (that's why you added trust rule to pg_hba.conf).

Once this command works, change your password:

ALTER ROLE postgres PASSWORD 'newpassword';

After doing this, edit pg_hba.conf to remove line you added.


SELECT UNION from multiple tables with same schema in MySQL

Yes, there is a less redundant/verbose way -- PARTITION a single table by year.  Then a single SELECT will do the job.

But, that may not even be wise.  What queries benefit from your splitting it into multiple tables?


Selecting two values from the same table but under different conditions

For readability I would rewrite the query using the more modern join syntax.  This will clearly separate your join conditions from your filters.

select a.myVal,
       b.myVal
  from MyTable a
  join MyTable b on b.id = a.id
where a.OtherVal = 100
  and b.Otherval = 200

For performance, ensure you have proper indexes.  In this limited example, ideally you would have a clustered index on ID and a non-clustered index on OtherVal.

After looking at your query, however, I cannot tell just what it is you are trying to accomplish.


Error code 1064  :

This means you have an error in your SQL syntax.( ; missed)

 Select Model, Variant, EngineSize, RegYear, Mileage, Price, Sector
 from sampledata ;
 update sampledata
 set RegYear = 2010 - RegYear
 where sector = 'Van Derived Cars'
 and mileage > 200
 and regyear != -1
 and price != 999999;

  Mysql error  code 1175 :

If you are in a safe mode, you need to provide a key (ex. primary key) in the where clause or you can SET SQL_SAFE_UPDATES = 0;


Arithmetic overflow error when using sp_Blitz

There was an issue with the latest release, shown in ISSUE 526. It was updated, by changing this code on line 472.

FROM

SELECT @MsSinceWaitsCleared =
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000

TO

SELECT @MsSinceWaitsCleared =
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000.0

If you do a new Pull request through github it has the fix in it.


How to create a view from a join with multiple duplicate column names?

I figured it out, the alias goes up with the SELECT statement so the query would be:

CREATE VIEW myView as (

  SELECT table1.id as t1, table2.id as t2, table3.id as t3, value
  FROM myTable

  INNER JOIN Table1
    ON Table1_id = Table1.id
  INNER JOIN Table2
   ON Table2_id = Table2.id
  INNER JOIN Table3
   ON Table3_id = Table3.id

);


Find rows that have the same value on a column in MySQL

One way by using a subquery that returns reportsTo of employeeNumber = 1088

select
from   employees
where  employee.reportsTo = (select reportsTo
                             from   employees
                             where  employeeNumber = 1088);


Index before or after bulk load using load infile?

I have tried a different variety of solution with a similar data load -over 1B- but the better that I have found is this:
From MySQL documentation
With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes. Use the following procedure:

Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name to remove all use of indexes for the table.

Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.

Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.

Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

LOAD DATA INFILE performs the preceding optimization automatically if the MyISAM table into which you insert data is empty. The main difference between automatic optimization and using the procedure explicitly is that you can let myisamchk allocate much more temporary memory for the index creation than you might want the server to allocate for index re-creation when it executes the LOAD DATA INFILE statement.
In order to obtain better performance from the myisamchk you have to tune some params like :
--key_buffer_size --myisam_sort_buffer_size --read_buffer_size --write_buffer_size

Depending on your hardware architecture
Note
When using LOCAL with LOAD DATA, a copy of the file is created in the server's temporary directory. This is not the directory determined by the value of tmpdir or slave_load_tmpdir, but rather the operating system's temporary directory, and is not configurable in the MySQL Server.
So, you you have this kind of problem and your file it's a csv, you can split you "huge" file into chunks
$ split -l (numbersofrowsinfile / ((filesize/tmpsize) + 1)) /path/to/your/<file>.csv

Then repeat your LOAD DATA LOCAL (step 3) for every chunk file.


How to group records under certain conditons in mysql

Schematically:

SELECT user.id
       CASE
       -- if all docs are approved by bank then the status must 'verified'
            WHEN SUM(document.status = 'approved') = COUNT(document.id)
                THEN 'verified'
       -- If at least one document is 'verified', the overall status is 'verified'
            WHEN SUM(document.status = 'verified')
                THEN 'verified'
       -- If no document is 'verified' but at least one is 'in progress', the overall status is 'in progress'.
            WHEN SUM(document.status = 'in progress')
                THEN 'in progress'
       -- If no document is 'verified' and 'in progress', but at least one is 'expired', the overall status is 'expired'.
            WHEN SUM(document.status = 'expired')
                THEN 'expired'
       -- If no document is 'verified', 'in progress' and 'expired', but at least one is 'rejected', the overall status is 'rejected'.
            WHEN SUM(document.status = 'rejected')
                THEN 'rejected'
       -- If no document is uploaded at all, the status is 'no xyz uploaded'
                ELSE 'no docs uploaded'
       END AS `overall_status`
FROM user
JOIN document ON (joining conditions)
GROUP BY user.id

  (for documents without expiration date the status is 'expired')

I don't understand the place of this condition.


SQL Server cached an execution plan which is not optimal in some cases and uses it for all consequent queries

This is called parameter sniffing, and it's covered extensively in Erland Sommarskog's epic post, Slow in the App, Fast in SSMS.

I can't even begin to do justice to it here, but sample solutions include:

OPTION (RECOMPILE) - which causes increased CPU use for the plan compilation, plus loses historical metrics of the query execution, but can build a unique plan for each set of parameters (although it can still be a suboptimal plan in cases of cardinality estimation issues)
Optimizing for a specific value - if you know your data well, you can use an OPTIMIZE FOR hint so that a plan is always built for a specific parameter value, regardless of what the user passed in. This is like creating technical debt - if your data skew changes, you may have to revisit your code in order to get a better plan.
Using index hints - which are generally worse than optimizing for a specific value because not only are you bossing the query optimizer around, but if that index disappears, your query simply fails. SQL Server doesn't try to use an alternate index for your query.
Plan guide - but if anything whatsoever changes about your query, even a single letter, then the plan guide will no longer match.
Combination of query and index tuning - get the developers to avoid selecting * (all the fields), and just get the fields they truly need. Then, build a covering index to match, and you'll get a single query plan that works well for all parameters.

Head on over and tackle Erland's excellent article - not only will it pay dividends today, but it will continue to pay off over your career as you solve this problem again and again. The solution that works well for your query today is likely to be very different than the solution you use for another query tomorrow.


Is there a way to limit log file usage for a transaction in SQL Server?

If I am sure that none of the transactions opened against a database
  should not allocate more than a certain amount of log,

The problem of long-running transaction is NOT that it generates too many log records, but in that it prevents the log of being truncated.

So even if there would be the way to limit log records generation per transaction, it could not save you from uncontrolled log growth.

It's enough to write begin tran update myTbl set col =... where key_field = ...
that will generate 8-10 log records (depending on server version), but then leave SSMS open and go to a vacation for one week, and if no one kills that open transaction ALL THE LOG generated from that moment will accumulate in a log file independently of recovery model. The log cannot be truncated in this case and it will grow accomodating all the log record generated during the week.


How to identify the top disk space consuming tables by file?

You may go to the report --> standard report and select Disk Usage by Top Table. This will be give you the table name in descending order which is used from maximum on disk to minimum on disk.
Alternatively, you may use the SQL below to get details:
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

Or this query:
SELECT TOP 1000
        a3.name AS SchemaName,
        a2.name AS TableName,
        a1.rows as Row_Count,
        (a1.reserved )* 8.0 / 1024 AS reserved_mb,
        a1.data * 8.0 / 1024 AS data_mb,
        (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
        (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb

    FROM    (   SELECT
                ps.object_id,
                SUM ( CASE WHEN (ps.index_id < 2) THEN row_count    ELSE 0 END ) AS [rows],
                SUM (ps.reserved_page_count) AS reserved,
                SUM (CASE   WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
                    ) AS data,
                SUM (ps.used_page_count) AS used
                FROM sys.dm_db_partition_stats ps
                GROUP BY ps.object_id
            ) AS a1

    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'
    order by a1.data desc

Note: I have not tested these queries myself, confirming back whether it works or not would be appreciated.
This has been asked on multiple forums and I have used answers from here.
As far as files are concerned, you may need to list filegroups, if none are defined, all the allocations will take place in primary filegroup.
Below query could help you finding physical location:
SELECT  OBJECT_NAME(i.id)   AS [Table_Name]
       , i.indid
       , i.[name]           AS [Index_Name]
       , i.groupid
       , f.name             AS [File_Group]
       , d.physical_name    AS [File_Name]
       , s.name             AS [Data_Space]
FROM        sys.sysindexes i
INNER JOIN  sys.filegroups f        ON  f.data_space_id = i.groupid
                                    AND f.data_space_id = i.groupid
INNER JOIN  sys.database_files d    ON  f.data_space_id = d.data_space_id
INNER JOIN  sys.data_spaces s       ON  f.data_space_id = s.data_space_id
WHERE       OBJECTPROPERTY(i.id, 'IsUserTable') = 1
ORDER BY    f.name, OBJECT_NAME(i.id), groupid

Hope this helps.


Forcing execution plan with local join hints

I want to enforce the join types to be somewhat the same and will
  therefore use local joins hints also for these

Adding join hints should be a last resort. There should be ways to rewrite the query / add indexes to get a more consistent result.

These plans are also estimated execution plans, in this case only you knows how well / bad the actual query will perform.

If the issue is parameter sniffing, OPTION(RECOMPILE) would be the easiest solution.

Is the LEFT JOIN only used for filtering? A NOT EXISTS might work better to filter earlier.

Having said all that, with the limited information given, here are some possible, quick rewrites.

Rewrite #1 LEFT JOIN to NOT EXISTS

The OPTION(RECOMPILE)is added to get better estimates based on the parameters provided.

INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp

 INNER JOIN
  (SELECT
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
   MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
   MAX(AjourholdDato) AS maxAjourholdDato
  FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
  WHERE --PeriodeStartDato < @dato--GETDATE()
   ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
    OR (Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
   )
  AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  GROUP BY
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato),
   MONTH(PeriodeStartDato)
  ) ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato <= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);

Rewrite #2 Also removing the OR by using UNION

   INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp
 INNER JOIN
  (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
    MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
    MAX(AjourholdDato) AS maxAjourholdDato
    FROM
    (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM
    [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE --PeriodeStartDato < @dato--GETDATE()
    ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
    )
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
    UNION
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE
    (Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  ) AS A
GROUP BY
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato),
MONTH(PeriodeStartDato)
 )
  ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato <= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);

Rewrite #3 Adding an additional temp table to store the inner join

By adding a temp table to split up the queries, the optimizer might get better estimates on the final query.

 SELECT
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
   MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
   MAX(AjourholdDato) AS maxAjourholdDato
INTO #TEMP
    FROM
    (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM
    [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE --PeriodeStartDato < @dato--GETDATE()
    ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
    )
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
    UNION
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE
    (Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  ) AS A

INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp

 INNER JOIN
  (
  SELECT *
  FROM #TEMP
  ) ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato <= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);

DROP TABLE #TEMP;

Closing note

If there is more information, like the table definitions and some sample data, more can be done, these three rewrites simply look like the fastest and easiest wins at the time of writing.


Contained User within a Non-Contained Azure SQL Database

Azure SQL Database allows database-level authentication regardless of whether the database is contained or not. There is no need to change the database containment for the purpose of database-level authentication alone.

The reason you might use a contained database is to ensure better portability. See the Contained Databases documentation for more info.


How can I use RMAN to show if there are archive logs that need to backed up?

You can do this from RMAN.

list archivelog all backed up 0 times to disk;

or

list archivelog all backed up 0 times to sbt_tape;

Depending on the backup method you use.


Connection to localhost dramatically slower than to network server

Try enabling TCP/IP on your local instance:

Open SQL Server Configuration Manager (mmc.exe -> [Ctl] + [M] -> Add "SQL Server Configuration Manager" snap-in)
Expand "SQL Server Network Configuration"
Click on "Protocols for MSSQLSERVER" (or whatever your local instance name is)
Set "TCP/IP" to Enabled

I just ran into this problem tonight.  Contrary to @Aaron Bertrand's seemingly intuitive suggestion in the comments, having TCP/IP disabled was actually causing my delay.  TCP/IP is disabled by default, so this is likely a common source of frustration for others, too.

I ran ProcMon.exe while connecting to a remote server and a local instance of SQL Server.  I was using the Jet/ACE ODBC TraceSQLMode to debug the delay.  TraceSQLMode writes to a text file every time an ODBC command is issued.

When connected to the remote server, seven commands were written to the text file in under one second total.  With the local instance, there were almost exactly two seconds between each ODBC call.

I assume that the Jet/ACE ODBC driver was attempting to connect to the localhost via TCP/IP initially with every ODBC call.  Since TCP/IP was disabled, this likely failed after a two-second timeout, at which point the Jet/ACE ODBC connection appears to have fallen back on Shared Memory access.

Enabling TCP/IP eliminated the two-second timeout delay, resulting in a massive performance increase (especially when making multiple calls to the database).


"ERROR: could not read from config file" MongoDB

First, assuming you are actually specifying where to read the file from, make sure that you have permission read that file with the current user (cat /usr/local/Cellar/mongodb/2.4.6/mongod.conf - or use less/vi/editor of choice). Assuming that works (and if it does not, adjust your permissions), then the next thing you need to do is make sure you are actually pointing at the correct file.

However, if you are not specifying where to read the file from, by default, if you just run mongod using the brew installation it will attempt to read from:

/usr/local/etc/mongod.conf

I verified this by installing 2.4.6 with brew and then checking the logs when it starts up:

Tue Oct 22 17:17:30.695 [initandlisten] options: { bind_ip: "127.0.0.1", config: "/usr/local/etc/mongod.conf", dbpath: "/usr/local/var/mongodb", logappend: "true", logpath: "/usr/local/var/log/mongodb/mongo.log" }

You can either modify that file (/usr/local/etc/mongod.conf) to look the way you want in your example, and make sure you have permissions to get to it, or you can run this instead to specify the original file:

mongod -f /usr/local/Cellar/mongodb/2.4.6/mongod.conf


How to select all columns plus one alias column in MySQL

I figured out the solution. I simple had do repeat the table name, as follows:

SELECT bin_to_uuid(id) as uuid, table_name.* FROM table_name

But if you have a solution that does not require to repeat the table name i will upvote your answer, since repeating all the table names in all the queries will bloat my the code a little.

EDIT:

As suggested in the comments by @ypercubeᵀᴹ and @Akina, looks like the * selector position is limited in MySQL. If you move the * to the first position everything works fine:

SELECT *, bin_to_uuid(id) as uuid FROM table_name


So I finally got the outage to do this yesterday evening and to summarize:

I opened an admin prompt, changed to the 'C:\Program Files\Microsoft
Team Foundation Server 2010\Tools' directory on the application tier
and quiesced TFS (TfsServiceControl.exe quiesce)
Changed all of the databases from LocalSystem to my service (domain)
accounts from inside sql server configuration manager.
Rebooted (I had windows update nagging me for a reboot)
Ensured all of the sql services started
Unquiesced TFS (TfsServiceControl.exe unquiesce)

The only problem I had is somehow inheritance on the Users folder where profiles are stored had some issue that prevented my service account from working. Once I fixed that everything worked just fine. I'm not sure if the profile issue was related to the account change.


How do I query a JSON array stored in a JSON object?


You need not move the data into another server just for performance improvement.
You could just move the data into another table (named sometable_20201023, assuming the archive/purge is happening on 2020/10/23 and you are OK with such a naming convention) and delete from the existing table. Since the original table is small, performance should improve.
Performance takes a hit when active data set does not fit in memory and disk I/Os are triggered. Having  inactive table(s) in your server will not cause any significant performance issue - your data dictionary size may go up by a bit.
In case you are concerned that your back-up/recovery time is also going up and want to fix it, this approach is not the best way. Moving the data to another server is best.


How to get table definition of a PostgreSQL table?

I suggest you use pg_dump to get tables' and views' definition, it's more convenient. The following command gets the definition of the database francs.

pg_dump -h 127.0.0.1 -E UTF8 -s -v francs > francs.ddl

-s means only dump matching  schema, not data.


Avoid joining to the same table multiple times

Try how good this works for you

Converted left join to one inner join for table Two for both t_id's. And on computing min, using case statement to split to two columns based on the value t_id. What we save here is scanning the table two time and an inner join compared to left join

SELECT
    o.p_id,
    o.k_id,
    MIN(case when t.t_id = 1 then t.pos else null end) AS t_pos,
    MIN(case when t.t_id = 2 then t.pos else null end) AS t1_pos
FROM
    one o
INNER JOIN two T ON t.p_id = o.p_id
AND t.k_id = o.k_id
AND t.t_id =any ( 1,2)
WHERE
    o.p_id = 1
AND o.c_id = 1
GROUP BY
    1,
    2
LIMIT 1


There is a normal form that would separate this column into its own entity (table). If you want to go down that path it's a legitimate choice. In my experience it's rare, though.
Accounting for unknown values is part of life and business computing. Procedural languages have their techniques. SQL has nulls. One way or another you'll eventually have to deal with nulls in the system, even if all table columns are defined "NOT NULL". Outer joins, for example, may introduce them.
As for performance, RDBM systems have efficient mechanisms to deal with nulls so this should not be a concern.
Were I writing this I would add the nullable column to the existing table.


What is a good way to replicate code across multiple databases?

SQL Server has replication built in. Have you looked into that?

If it's just a few views or procedures, I'll use a Central Management Server to deploy them to multiple servers. You just define the group of servers and then execute the script. Pretty simple.

For 3rd party options, Idera has Multi Query and Red Gate has Multi Script as well.


Postgresql createrole permission for user

According to the docs, you have to use SET ROLE before actually using the createrole privilege.

Connect as user1 and issue

SET ROLE group1;
CREATE ROLE newuser;

Also, beware - CREATEROLE is in fact equivalent of superuser. This is also said in the docs.


Annonymous user has no privileges, but still able to show databases?

I just ran this in MySQL 5.6.15 on my Windows 8 machine

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| ODBC@localhost | @localhost     |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.05 sec)

mysql>

Then, I logged into MySQL as redwards@localhost

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user();
+--------------------+--------------------+
| user()             | current_user()     |
+--------------------+--------------------+
| redwards@localhost | redwards@localhost |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

What is the difference? An anonymous user can still execute SHOW DATABASES; but can only see information_schema (contains metadata only anonymous users can see). Why can the database test be seen? The table mysql.db allows test databases to be visible to everyone (See my question MySQL : Why are there "test" entries in mysql.db? and the answer I posted)


It is certainly possible to use a Maintenance Plan to handle the fragmentation issues.  However, many people use the free scripts at: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I am part of the 'many people'.  These scripts by default implement the suggested standards for reducing the fragmentation of indexes.  So, if you implement the script with one of the many sample settings further down the page, it should bring your indexes into better order.

Your database is not particularly huge, so scheduling an online index rebuild on a relatively quiet time, perhaps Saturday night if that is a low use time, and let it run.

If you link to the home page (http://ola.hallengren.com/) you will also find scripts for database backups and integrity checking.  It seems that you have a good backup schedule overall, though I do not know your retention period, so you do not need to change unless you personal see the need.

Second, regarding the large LOG file, you can look into the DBCC SHRINKFILE() command to reset the size of the log file. On the other hand why bother unless you need the space.

DBCC SHRINKFILE ('LogFileName', );

If it does not shrink the first time, run CHECKPOINT, backup the log again, and try to shrink the file again. Once the high order portion of the log is empty (through the log backups) the file will be able to shrink.


I needed to include the context:
First(Fields!person_id.Value,"FamilyID") = Fields!person_id.Value


Assuming you have the appropriate DBA-level permissions, you can try this to get some basic information on the SecureFile/LOBs in your system:

SELECT table_name, segment_name, index_name, securefile, in_row
FROM user_lobs;

You can get the size on disk for a specific table name (your table with a SecureFile/LOB) by doing this:

SELECT segment_name, segment_type, segment_subtype, bytes/1024/1024 meg_bytes
FROM user_segments
WHERE table_name = 'yourTableName';

To get the actual compression rating (no/low/high), try this using your table name:

SELECT table_name, column_name, partition_name, tablespace_name, compression, deduplication
FROM user_lob_partitions
WHERE table_name = 'yourTableName';

Hope this helps!


If you are doing replication and you are just practicing, the my.cnf you choose is not that important. However, the smaller the config, the better. For example, if you DB Server has 2GB RAM, you should not choose my-innodb-heavy-4G.cnf.

If you started up mysql with a my.cnf, you create the default 10M or 18M ibdata1 and 2 5M InnoDB log files (ib_logfile0, ib_logfile1). The medium cnf has the following InnoDB settings:

[root@******** ~]# cat /usr/share/mysql/my-medium.cnf | grep innodb
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

Since everything related to InnoDB is commented out, you should OK to choose my-medium.cnf

Simply run this:

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
service mysql restart

Now, as for MySQL Replication, I have Good News : I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with all the steps you need to automate a Slave's creation.

Here is a couple more of my posts with more advanced replication setups

May 07, 2012 : Setting Circular Replication in mysql
Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?