Huge Heap Table and table compression on SQL Server 2016

Still i think that the table size is huge for the no. of records.

Why? What makes you say this?

  what it does is stores different version of same entry from another table.

So it holds versions, which could be large or small, especially given:

[Data] [varbinary](max) NOT NULL,

Have you checked the datalength() on the rows to see if you have some large rows in there eating space?

Seems to me, based on the data the size it is given the function seems reasonable. Versioning is rarely small unless it's just changed bytes and you use various algorithms to understand which bytes, how, and if it builds off previously changed versions or not.


MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

If you have access to the server file system, I would suggest that you script (Perl, PHP, C++, etc.) the number generation into a flat file and perform a LOAD DATA INFILE operation.

Typically LOAD DATA INFILE performs faster than repeated INSERT statements for larger row sets and can also handle the IGNORE clause. Have a look at this answer regarding the bulk_insert_buffer_size variable, which is important when doing bulk inserts, should you elect to go with the LOAD DATA INFILE option.


How can I restart Database Mirroring after intentionally breaking it?

Once you have broken the database mirror, you have no other option than to re-initialize it using a full backup and transaction log backup of the principal.

The steps you must take are:

Restore a recent full database backup of the principal database, after making sure that the principal database was already using the full recovery model when the backup was taken. The mirror database must have the same name as the principal database.
If you have taken any differential backups of the database since the restored full backup, restore your most recent differential backup.
Restore all the log backups done since the full or differential database backup.
Complete the remaining setup steps as soon as you can after taking the backup of the principal database. Before you can start mirroring on the partners, you should create a current log backup on the original database and restore it to the future mirror database.
Establish the mirroring session using ALTER DATABASE <database_name> SET PARTNER =<server_network_address> on the secondary server first, then on the principal server.

I would advise you use the native backup compression feature in SQL Server to compress the database and log backups (and perhaps subsequently compress them with 7-Zip); then ship the files using the fastest overnight shipping method you can find.  Have someone at the secondary location put those files onto the secondary server in a mutually agreeable location where you can subsequently restore them using WITH NORECOVERY.

Probably the most important piece of advice is to understand there is no need to validate the data at the secondary side since SQL Server commits the data at the secondary the same way it commits data at the primary; that is it uses ACID-compliant methodologies.  As such you can be assured the data at the secondary is an exact copy of the data at the primary.  The best way to confirm the secondary is a functional copy is to fail-over the mirroring session so the secondary becomes the primary.  You can do this during a maintenance window, with testers on-hand ready to validate the system works as expected.  Clearly, you'd take a complete set of backups prior to the first failover, just in case things don't work as expected.


Raising an exception is not something you do very often. However, if that's your case...

Your trigger function should probably look similar to:

CREATE FUNCTION ensure_consistent_enrolled()
RETURNS trigger AS $$
BEGIN
    IF old.enrolled is NULL THEN
        IF not
               (old.id in (select id from table1) or
                old.id in (select id from table2) or
                old.id in (select id from table3) or
                old.id in (select id from table4) or
                old.id in (select id from table5) or
                old.id in (select id from table6)    )
        THEN
            RAISE EXCEPTION 'User not enrolled plan'
            USING HINT = 'Ensure user is enrolled in a plan' ;
        END IF ;
    END IF ;

    -- If we didn't raise any exception, return the new row "as is"
    RETURN new ;
END;
$$ LANGUAGE plpgsql;

I am not sure when this trigger should be fired. I assume it should be fired BEFORE an UPDATE. I don't think it makes sense to fire it before/after an INSERT; and don't know at all if it is sensible to trigger also BEFORE DELETE.

If you only need to check this condition when UPDATING, you would have

CREATE TRIGGER check_enrolled
   BEFORE UPDATE
   ON your_table FOR EACH ROW
   EXECUTE PROCEDURE ensure_consistent_enrolled();


How to access the text definition of sql indexes

The objects that have definitions found in the sys.sql_modules Catalog View are the only ones that have full definitions, and indexes are not in this group. The sys.check_constraints and sys.default_constraints Catalog Views have partial definitions, but again, those aren't indexes. So no, you are not going to find the text definition of indexes.

What you can find, however, is the meta-data related to indexes that can be used to construct the full text definition. You would find that info in the following Catalog Views:

sys.indexes
sys.index_columns
sys.columns
sys.data_spaces
sys.key_constraints
sys.xml_indexes
sys.partition_schemes  (might be needed for partitioned indexes)

If you need to get this definition programmatically, then you can use SMO:

SQL Server Management Objects (SMO) Programming Guide
Automated Script-generation with Powershell and SMO

P.S. This answer was originally posted on this same question on Stack Overflow prior to the question getting copied over here.


If the SELECT works as expected, so should your UPDATE. More: it should update all columns, which is most probably not what you want.

UPDATE test
SET    field_1 = replace(field_1, $$

publicidade

$$, '')
WHERE  field_1              LIKE $$%

publicidade

%$$

I added a WHERE clause to avoid expensive and pointless empty updates.

Related:

Insert text with single quotes in PostgreSQL
How do I (or can I) SELECT DISTINCT on multiple columns?


How can you insert data into a ARRAY column on a Snowflake table using SQL?

It looks like you need to apply a function to parse the array. As of 9/13/2018 functions are not allowed in value lists see Is there a literal syntax for arrays

One way around this is to combine INSERT INTO and SELECT statement. Example:

CREATE OR REPLACE TABLE array_test_table (
  id number,
  ids array
);

INSERT INTO array_test_table (id, ids)
SELECT $1, PARSE_JSON($2)
  FROM VALUES (1, '[1,2,3]' );


There's really not enough information here to make any specific recommendations.

64676.96 reads/s seems pretty high for a 16 core server, and it seems disproportionately large compared to the number of inserts/updates/deletes.

I would guess (again, with a big grain of salt because I know nothing about your app, your database, or your queries) that this is so high because you have queries that are not optimized with indexes.

Creating the right indexes for your queries usually helps reduce the average rows examined per query (but of course this depends a lot on the types of queries you run).

You might like to read my presentation How to Design Indexes, Really or a video of me giving this presentation: https://www.youtube.com/watch?v=ELR7-RdU9XU

Re your comments:

In most cases, you can optimize with indexes without changing any queries. You can get the log of queries from MySQL without looking at any code in Jamf.

Tuning settings can be important, but only up to a point. You should read:

https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/


2 Date Ranges - How many days from one range excluding weekends overlap

Create date tables or table variables with your ranges and do the join query. Assuming SQL Server as the database, my sample solution is given below.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

Declare @DateTable1 table (Date1 date);

Insert into @DateTable1
select dateadd(day, number, @Range1Begin)
from
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End;

Declare @DateTable2 table (Date2 date);

Insert into @DateTable2
select dateadd(day, number, @Range2Begin)
from
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End;

-- Get individual dates
select dt1.Date1 as date, datename(w, dt1.date1) as weekday from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);

-- Get total days
select count(*) as TotalDays from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);

Without declaring table variables, following is the solution.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

-- Get total days
select count(*) as TotalDays from (select dateadd(day, number, @Range1Begin) date1
from
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End) dt1
inner join (select dateadd(day, number, @Range2Begin) date2
from
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End) dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);


If you follow this link there are several ways.
One of them shows you the architecture.

From that page:

$ db2level
DB21085I  Instance "db2inst5" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23034", and Fix Pack "1".
Product is installed at "/opt/ibm/db2/V9.7".


Does lightweight pooling disable built-in CLR facilities?

Yes.
When running in fibre mode, everything that needs the CLR is unavailable.
For example, both:
SELECT FORMAT(GETDATE(), N'd', N'en-us');

and:
DECLARE @g geometry;
SELECT @g;

...return the (slightly misleading) error message:

Msg 5846, Level 16, State 2, Line 13
Common language runtime (CLR) execution is not supported under lightweight pooling.
Disable one of two options: "clr enabled" or "lightweight pooling".

It is the lightweight pooling that is the problem, no setting of the clr enabled option will allow CLR execution of any kind under lightweight pooling.


Try this rewriting. It avoids the GROUP BY and should be using the index on (sensor_id, recorded_at) for both the subquery and the join:

SELECT
    input_times.instant,
    c.sensor_id,
    c.recorded_at AS first_before,
    c.value
FROM
    ( SELECT 80 AS sensor_id UNION ALL
      SELECT 85  UNION ALL
      SELECT 88  UNION ALL
      SELECT 89
    ) AS s
  CROSS JOIN
    ( SELECT '2016-06-29 00:00:00' AS instant
      UNION ALL
      ...
    ) AS input_times
  JOIN
    cumulative_energy_consumptions AS c
  ON  c.sensor_id = s.sensor_id
  AND c.recorded_at = ( SELECT ci.recorded_at
                        FROM cumulative_energy_consumptions AS ci
                        WHERE ci.sensor_id = s.sensor_id
                          AND ci.recorded_at <= input_times.instant
                        ORDER BY ci.recorded_at DESC
                        LIMIT 1
                      ) ;


Why is a NOT NULL computed column considered nullable in a view?

The [Ranking] field is showing as "Nullable" due to being a computed column. Yes, it is declared as NOT NULL, but as the MSDN page for Computed Columns states, the database engine can change that determination at query-time:

  The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

So, let's see if this is true:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
);
GO

EXEC sp_help 'dbo.Realty';
-- Ranking: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'), N'Ranking', 'AllowsNull') AS [AllowsNull?];
-- 0

SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- Ranking: is_nullable = 1  ==  :-(

Now let's see if their advice regarding ISNULL works:

SELECT * FROM sys.dm_exec_describe_first_result_set(
   N'SELECT Id, RankingBonus, ISNULL(Ranking, -99) AS [RealRanking] FROM dbo.Realty;',
   '',
   NULL);
-- RealRanking: is_nullable = 0

Their advice does seem accurate, so let's try applying that to the definition of the computed column:

ALTER TABLE dbo.Realty
  ADD [RankingFixed] AS (ISNULL(([Id]+[RankingBonus]), -99))
  PERSISTED NOT NULL;
GO

And now we check the properties again, but for the new field:

EXEC sp_help 'dbo.Realty';
-- RankingFixed: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'),
                      N'RankingFixed',
                      'AllowsNull') AS [AllowsNullsNow?];
-- 0

This is looking positive so far, but even the original definition reported "NOT NULL" from these two checks. So let's try the real test -- how the database engine determines the nullability at run-time:

SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- RankingFixed: is_nullable = 0  ==  :-) WOO HOO!


copy/sync only data between 2 database with non identical table structure

Alright so this is an intro on how to accomplish this using SSIS. This is just a base model to get you going you're going to need to tune this based on your specific database to improve performance. This tutorial also assumes you know how to set connection managers. Keep that in mind.

First off you're going to want to create and then open a dataflow task. Then you want to add two OLE DB Sources, one Merge Join operation, one conditional split, one (possibly two) OleDB destinations, and one (possibly zero) OLEDB commands.

Once you've done this we will need to build the query for this table my example is just a calendar table.

SELECT
    [BaseDate],
    [YearNumber],
    [MonthNumber],
    [DayNumber],
    [DayofWeek],
    [DayofYear],
    [WeekNumber],
    [Holiday],
    [WorkDay],
    [JulianDate],
    [BusinessDay],
    [Quarter]
FROM [Calendar]
ORDER BY [BaseDate] -- Very important to order by your primary key columns

Open your OLEDB Source.

NOTE:By use Identical column names I mean in each source.

Once you've done this right click on the source and go to the advanced editor.

Rinse and repeat for your second OLEDB Source. Once this is connect the source table to the Merge. This will prompt you for either Left or Right Input. The source table should always be the left side.

Once both are connected Open the Merge Join operator.

Open the conditional split and add this.

Connect the Merge Join Operator to the Conditional Split

Then connect that to your OLE DB Destination map the columns and boom you have your insert. Now for the tricky part. You will have to write an expression for comparing all of those columns. However if you're lazy like me you will use this stored procedure I made to do it for you. Simply create this stored procedure on the database you are pulling from and then execute with that table name in the parameter.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[MakeSSISColumnCompare]
        (@TableName VARCHAR(50))
AS

DECLARE
        @SqlCmd VARCHAR(max)
        ,@ColumnName VARCHAR(50)
        ,@DataType VARCHAR(50)
        ,@Nullable VARCHAR(3)
        ,@Count INT

DECLARE ColCsr CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName

OPEN ColCsr
SET @Count = 0

FETCH NEXT FROM ColCsr INTO @ColumnName, @DataType, @Nullable
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @ColumnName NOT IN ('CreatedDateTime','UpdateDateTime')
    BEGIN
        IF @Count = 0
                SET @SqlCmd =
                CASE
                        WHEN @Nullable = 'NO' THEN '[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        WHEN @DataType LIKE '%CHAR%' THEN '(ISNULL([' + @ColumnName + '])?"XXXX":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?"XXXX":[' + @ColumnName + ' (1)])'
                        WHEN @DataType LIKE '%INT%' THEN '(ISNULL([' + @ColumnName + '])?-99:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?-99:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'DATETIME' THEN '(ISNULL([' + @ColumnName + '])?"(DT_DBDATE)1/1/1900":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + ' (1)])'
                        WHEN @DataType IN ('MONEY', 'NUMERIC', 'DECIMAL','FLOAT') THEN '(ISNULL([' + @ColumnName + '])?0:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?0:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'BIT' THEN '[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        ELSE 'TRUE' END
        ELSE
                SET @SqlCmd = @SqlCmd +
                CASE
                        WHEN @Nullable = 'NO' THEN '||[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        WHEN @DataType LIKE '%CHAR%' THEN '||(ISNULL([' + @ColumnName + '])?"XXXX":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?"XXXX":[' + @ColumnName + ' (1)])'
                        WHEN @DataType LIKE '%INT%' THEN '||(ISNULL([' + @ColumnName + '])?-99:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?-99:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'DATETIME' THEN '||(ISNULL([' + @ColumnName + '])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + ' (1)])'
                        WHEN @DataType IN ('MONEY', 'NUMERIC', 'DECIMAL','FLOAT') THEN '||(ISNULL([' + @ColumnName + '])?0:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?0:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'BIT' THEN '||[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        ELSE '||TRUE' END
        SET @Count = @Count + 1
    END
    FETCH NEXT FROM ColCsr INTO @ColumnName, @DataType, @Nullable
END
SELECT @SqlCmd AS SqlSelect

CLOSE ColCsr
DEALLOCATE ColCsr

GO

Then execute like so.

EXEC    [dbo].[MakeSSISColumnCompare]
        @TableName = N'YourTableName'

This should give you the String you need to compare. You will want to delete the piece of this string that compares the key values. In my case turning this.

[BaseDate]!=[BaseDate (1)]||[YearNumber]!=[YearNumber (1)]||[MonthNumber]!=[MonthNumber (1)]||[DayNumber]!=[DayNumber (1)]||[DayofWeek]!=[DayofWeek (1)]||[DayofYear]!=[DayofYear (1)]||[WeekNumber]!=[WeekNumber (1)]||[Holiday]!=[Holiday (1)]||[WorkDay]!=[WorkDay (1)]||[JulianDate]!=[JulianDate (1)]||[BusinessDay]!=[BusinessDay (1)]||[Quarter]!=[Quarter (1)]

Into this.

[YearNumber]!=[YearNumber (1)]||[MonthNumber]!=[MonthNumber (1)]||[DayNumber]!=[DayNumber (1)]||[DayofWeek]!=[DayofWeek (1)]||[DayofYear]!=[DayofYear (1)]||[WeekNumber]!=[WeekNumber (1)]||[Holiday]!=[Holiday (1)]||[WorkDay]!=[WorkDay (1)]||[JulianDate]!=[JulianDate (1)]||[BusinessDay]!=[BusinessDay (1)]||[Quarter]!=[Quarter (1)]

This is why keeping the column names the same was important earlier. This allows that stored procedure to build the comparison string for us. Now simply copy and paste that into the second place of the conditional split.

Now either that to an OLEDB command to update the necessary table or send to to a staging table and then perform and traditional SQL update from there. This will depend on the size of the dataset being updated as to which performs better.

I would recommend including a timestamp system of some sort as well. This will allow you to limit the amount of rows you grab in your initial select.

Sorry this is isn't using 2012 data tools but I don't have access to that quite yet.


mongodb heartbeat - unicast, broadcast or multicast?

Bottom line, is this a necessary point to consider when mongodb are
  used in production?

Is it worth figuring out your bandwidth requirements? Yes

Is it worth including the heartbeat in that calculation? No

The heartbeats between the nodes in a set are basically TCP based and unicast - it is done by background threads that monitor the other members of the set from each of the constituent node and fire every ~2 seconds with minimal amounts of data.  If you are not doing enough traffic in terms of data to make that an insignificant amount of traffic, then you probably don't need to bother modeling for traffic at all.

The real thing you need to determine is the data traffic - each secondary will be replicating data from a source.  Your primary will need to both ingest that data from whatever application or other client is feeding it as well as replicate the data to at least one secondary (other secondaries can be "chained" off the first, but at least one must replicate from the primary).

Then, of course, for most non-archival databases, something is going to be consuming data as well as writing it.  Again, this can all be from the primary, or you might be OK with eventual consistency and go for secondary reads also.  In read heavy applications, this can dwarf write traffic and replication traffic.

If you are talking about a sharded environment, then you get some extra overhead - each shard is usually a replica set and you will get some communication between them as chunks are balanced.  But again, this is usually dwarfed by the application reads and writes.

To summarize, unless you are going to be doing something out of the ordinary, the amount of data you read and write will be your main source of bandwidth usage and hence what you have to model.


updating a hstore with multiple new columns

you can concatenate them:

set hstore_col = hstore('new_key_1',foo.new_value_1)||hstore('new_key_2',foo.new_value_2)

Alternatively use the constructor function that accepts two arrays:

set hstore_col = hstore(array['new_key_1', 'new_key_2'],array[foo.new_value_1, foo.new_value_2])

The second solution will be easier to extend when you have more keys then just two

If you don't want to overwrite the current value, then append that to the original one:

set hstore_col = hstore_col || hstore('new_key_1',foo.new_value_1)||hstore('new_key_2',foo.new_value_2)

or

set hstore_col = hstore_col || hstore(array['new_key_1', 'new_key_2'],array[foo.new_value_1, foo.new_value_2])


How can I install pgtap on rds postgresql?

As of May 2019 pgTAP is an extension in RDS postgres version 11.2, (not version 10,7 however) according to
https://aws.amazon.com/about-aws/whats-new/2019/05/amazon-rds-postgresql-supports-minor-version-112/


Can the new “SCRAM-SHA-256” password authentication mechanism in Postgres 10 and later work with any JDBC drivers?

I have not verified this myself, but the answer seems to be Yes, from version 42.2.0 of the driver from the jdbc.postgresql.org project.

To quote the changelog for Version 42.2.0:

  Support SCRAM-SHA-256 for PostgreSQL 10 in the JDBC 4.2 version (Java 8+) using the Ongres SCRAM library.

See GitHub pull request # 842.

That driver uses the SCRAM Java Implementation project by ongres.


Is it worth indexing a varchar(32) column of UUIDS where half the UUIDS are v1 and half are v4?

Both v1 and v4 (or the mix) UUIDs will build a good B+ tree index for faster SELECTs.
From normalization point of view duplicating the column is a bad idea. But if we put this aside, and any SELECTs will benefit from the new index, and impact on writes is acceptable - why not?


If you don't need fractions, choose bigint. In your case it will be a byte smaller:

10 digit decimal is 9 byte: https://msdn.microsoft.com/it-it/library/ms187746.aspx

bigint is 8 byte: https://msdn.microsoft.com/it-it/library/ms187745.aspx

Plus, it is faster (it has no variable part, and it is an integer type, much faster processed by cpu).


Problem Configuring Log Shipping for DR Server

I don't understand your issue.  You can definitely log ship while performing Full and Diff backups.  Are you trying to have some manually performed version of log shipping?
Performing a Full or Diff backup will cause no issues, log shipping just uses log backups so the chain will not be broken.

Have you actually tried this out, from your question it appears that you just think it will break.


fix `mysqldump: Error 2013: Lost connection` on "cloud" server

In my case it helped to stop Apache, do mysqldump and then start Apache.

Nothing else helped, no options that were recommended. Nothing (and I searched all posts). I have low 512M memory and maybe this is also the cause.


Restore database excluding FILESTREAM data

What you're trying to do would leave the database in a (transactionally) inconsistent state, hence it isn't possible.

The Partial Database Availability whitepaper is a useful reference guide and includes an example of how to check whether a particular table or file is online. If your data access were through stored procedures, you could relatively easily incorporate that check.

One alternative (but somewhat hacky) approach that might be worth a look in your scenario would be to hide the table and replace it with a view.

-- NB: SQLCMD script
:ON ERROR EXIT
:setvar DatabaseName "TestRename"
:setvar FilePath "D:\MSSQL\I3\Data\"

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SET NOCOUNT ON;
GO

USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$(DatabaseName)')
  DROP DATABASE $(DatabaseName)
GO

CREATE DATABASE $(DatabaseName)
ON PRIMARY
  (
  NAME = N' $(DatabaseName)'
  , FILENAME = N'$(FilePath)$(DatabaseName).mdf'
  , SIZE = 5MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
, FILEGROUP [FG1] DEFAULT
  (
  NAME = N' $(DatabaseName)_FG1_File1'
  , FILENAME = N'$(FilePath)$(DatabaseName)_FG1_File1.ndf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
, FILEGROUP [FG2] CONTAINS FILESTREAM
  (
  NAME = N'$(DatabaseName)_FG2'
  , FILENAME = N'$(FilePath)Filestream'
  )
LOG ON
  (
  NAME = N'$(DatabaseName)_log'
  , FILENAME = N'$(FilePath)$(DatabaseName)_log.ldf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
GO

USE $(DatabaseName);
GO

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL
    , [FileName] [varchar](50) NOT NULL
    , [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL UNIQUE DEFAULT (NEWSEQUENTIALID()) NOT NULL
  , [FileContentBinary] VARBINARY(max) FILESTREAM  NULL
) ON [PRIMARY] FILESTREAM_ON [FG2]
GO

-- Insert test rows
INSERT
  dbo.BinaryContent
  (
  [FileName]
  , [FileContentBinary]
  )
VALUES
  (
  CAST(NEWID() AS VARCHAR(36))
  , CAST(REPLICATE(NEWID(), 100) AS VARBINARY)
  );
GO 100

USE master;
GO

-- Take FILESTREAM filegroup offline
ALTER DATABASE $(DatabaseName)
MODIFY FILE (NAME = '$(DatabaseName)_FG2', OFFLINE)
GO

USE $(DatabaseName);
GO

-- Rename table to make way for view
EXEC sp_rename 'dbo.BinaryContent', 'BinaryContentTable', 'OBJECT';
GO

-- Create view to return content from table but with NULL FileContentBinary
CREATE VIEW dbo.BinaryContent
AS

SELECT
  [BinaryContentID]
    , [FileName]
    , [BinaryContentRowGUID]
  , [FileContentBinary] = NULL
FROM
  [dbo].[BinaryContentTable];
GO

-- Check results as expected
SELECT TOP 10
  *
FROM
  dbo.BinaryContent;
GO


You can use two SELECTs, one for each Measure.

create table tbl2 (ID int identity, ServiceName varchar(100), ServiceCode varchar(100),
                 Age varchar(100), Gender varchar(100),
                 Measure varchar(100), [Value] int, Location varchar(100));

insert into tbl2 (ServiceName, ServiceCode, Age, Gender, Measure, Value, Location)
select ServiceName, ServiceCode, Age, Gender, 'Number', [Number], location
from tbl
union
select ServiceName, ServiceCode, Age, Gender, 'TotalCost', [TotalCost], location
from tbl

select * from tbl2;
GO

ID | ServiceName | ServiceCode | Age      | Gender | Measure   | Value | Location
-: | :---------- | :---------- | :------- | :----- | :-------- | ----: | :-------
 1 | service1    | 0000234     | under 12 | Male   | Number    |   121 | loc1
 2 | service1    | 0000234     | under 12 | Male   | TotalCost |  1234 | loc1

db<>fiddle here


MS SQL Server does not create tables in a specified filegroup

I think your problem stems from the fact that PRIMARY is a reserved word.  There are several options for the ON clause of a CREATE TABLE statement and putting square brackets around the word [PRIMARY] eliminates confusion about what you are actually asking SQL Server to do.


PostgreSQL + why archive_command line is marked after barman restore

As far as barman knows, you did the restore in order to make a clone of your instance and the original instance is still running someplace.  You definitely don't want two instances sending WAL to the same archive.  If you are dead certain the original instance has been irreversibly destroyed, then you should turn archiving back on.  Otherwise, you should start archiving to a different location.


How to Restore Postgresql Cluster from data directory

Here's what I'd do:

Download postgresql-8.1_8.1.8-1.diff.gz, postgresql-8.1_8.1.8-1.dsc and postgresql-8.1_8.1.8.orig.tar.gz from http://archive.debian.org/debian/pool/main/p/postgresql-8.1/ on a machine running the same OS as what you intend to install on.
dpkg-source -x postgresql-8.1_8.1.8-1.dsc
Examine debian/rules for configure options.

This shows:

DEB_CONFIGURE_EXTRA_FLAGS := --mandir=\$${prefix}/share/postgresql/8.1/man \
                    --with-docdir=\$${prefix}/share/doc/postgresql-doc-8.1 \
                    --datadir=\$${prefix}/share/postgresql/8.1 \
                    --bindir=\$${prefix}/lib/postgresql/8.1/bin \
                    --includedir=\$${prefix}/include/postgresql/ \
                    --enable-nls \
                    --enable-integer-datetimes \
                    --enable-thread-safety \
                    --enable-debug \
                    --disable-rpath \
                    --with-tcl \
                    --with-perl \
                    --with-python \
                    --with-pam \
                    --with-krb5 \
                    --with-openssl \
                    --with-gnu-ld \
                    --with-tclconfig=/usr/lib/tcl$(TCL_VER) \
                    --with-tkconfig=/usr/lib/tk$(TCL_VER) \
                    --with-includes=/usr/include/tcl$(TCL_VER) \
                    --with-pgport=5432 \
                    $(ARCH_OPTS) \
                    CFLAGS='$(CFLAGS)' \
                    LDFLAGS='$(LDFLAGS)'

This should help you do your own source build. In particular I think you probably need --enable-integer-datetimes.

Alternately, you could dpkg-buildpackage -rfakeroot -uc -b to build the package, then copy it to the target server and install it.


Manipulating a Column Containing Key/Value Pairs

UPDATE

If as you posted in your own answer you're able to use a UDF to get specific key values let me suggest this one: (You don't need to split all key/values and you don't need to read the table again, you can get it by using text functions.)

CREATE FUNCTION fnGetKey(@Data text, @Key varchar(20))
RETURNS varchar(100)
AS
BEGIN

  RETURN
  (
  SELECT
      SUBSTRING (
                  @Data,
                  /* Position of first '=' after key + 1 */
                  CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data)) + 1,
                  /* Lenght, Position of first chr(13) after key less previuos value - 1 */
                  (CHARINDEX(CHAR(13), @Data, PATINDEX('%' + @key + '%', @Data))
                  -
                  CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data))) - 1
                )
  )

END

SELECT
    FruitID, Name, Description,
    dbo.fnGetKey([Data], 'key 2') as [key 2],
    dbo.fnGetKey([Data], 'key 4') as [key 4]
FROM
    [Fruit];

FruitID | Name   | Description | key 2   | key 4
------: | :----- | :---------- | :------ | :------
      1 | Banana | Delicious   | value 2 | value 4
      2 | Pear   | Rotton      | value 2 | value 4
      3 | Kiwi   | Okay        | value 2 | value 4

db<>fiddle here

Original answer

The only solution I can figure out is by splitting key/values and then pivot it to obtain the desired result.

Unfortunately there are some inconveniences:

STRING_SPLIT doesn't works with text columns. Hence you must cast it to varchar before you are able to manipulate it.
STRING_SPLIT requires a nchar(1) or nvarchar(1), ergo you should replace CHAR(3)+CHAR(10) by a single character.
Aggregate function on PIVOT works better with numeric values, then you should cast Value to some numeric data type.
PIVOT needs a well-known number of columns, in my example I have used a few of them but you should write the whole sequence unless you'd rather deal with dynamic queries.

This is what I've got using your sample data:

WITH KP AS
(
    SELECT FruitID, Name, Description, value as KPair
    FROM   Fruit
    CROSS APPLY STRING_SPLIT(REPLACE(CAST(Data AS varchar(max)), CHAR(13)+CHAR(10), ','), ',') /* STRING_SPLIT only allows nchar(1),  varchar(1) */
)
, KP1 AS
(
  SELECT
      FruitID,
      SUBSTRING(KPair, 5, CHARINDEX('=', KPair) - 5) AS [Key],
      SUBSTRING(KPair, CHARINDEX('=', KPair) + 7, LEN(KPair) - CHARINDEX('=', KPair) - 6) AS [Value]
  FROM
      KP
)
SELECT [FruitID], [1],[2],[3],[4],[5]
FROM   KP1
PIVOT (MAX([Value]) FOR [Key] IN ([1],[2],[3],[4],[5])) AS PVT;

First CTE split every Key X=Value Y. The second one cut this value to obtain each [Key] and [Value]. And the final PIVOT compose the final result in columns.

FruitID | 1  | 2  | 3  | 4  | 5
------: | :- | :- | :- | :- | :-
      1 | 1  | 2  | 3  | 4  | 5
      2 | 1  | 2  | 3  | 4  | 5
      3 | 1  | 2  | 3  | 4  | 5

db<>fiddle here

NOTE: I'm not sure if I should maintain [Key 1] & [Value 1] or it should be converted as a column named [Key] & [Value].

A different approach

When I work with 3rd party databases I usually add a new database, on the same server/instance if possible, and then I use it for my own purposes, just to avoid conflicts with the DB owners.

In this case you could add a new table and periodically throw a process to update it with the new values.

You could use a table with all columns:

CREATE TABLE [FruitKeys]
(
    [FruitID] int NOT NULL PRIMARY KEY,
    [V1]      int NULL,
    [V2]      int NULL,
    [V3]      int NULL,
    [V4]      int NULL,
    [V5]      int NULL
);

or a table with Key/Value pairs and use a pivot to obtain the final result:

CREATE TABLE [FruitKeys]
(
    [FruitID] int NOT NULL,
    [Key]     int NOT NULL,
    [Value]   int NOT NULL,
    CONSTRAINT [PK_FruitKeys] PRIMARY KEY ([FruitID], [Key])
);


Separate table creation at runtime or add column

It is better to have single patient table.

Each doctor must have different UserId(or call PhysicianId) and then make entry in patient table with FkPhysicianId as a column and you can have one table for storing clinic information(auto incremented key can be used as ClinicId) and one for storing physicians Information like ClinicId etc.

single patient table will be better as you don't need to add table for each practice you add and data can queried using joins on these table and maintaining too many table in future will be problomatic and other implementation in the EMR application as well.

I would suggest to have a Master Table to assign auto incremented Id to physician and patient as well.


What's the difference between a v$ view and its dba_ equivalent?

The easiest way to think of it is:

DBA_ / USER_ / ALL_ views are built on the data dictionary - they're not available if the database is not mounted and opened.
V$ views tend to run against the instance, and therefore may be available if the database is not mounted, or is not mounted and opened, depending on the nature of the view.

Using your example:

V$TABLESPACE is a view on X$KCCTS, which is an internal memory structure.
DBA_TABLESPACES is a view on the data dictionary table SYS.TS$


MariaDB RAM usage does not reflect configuration

MariaDB's memory usage grows over time. It stops growing when certain configurable settings are hit. In practice, it mostly stops growing long before the 'theoretical' limit is hit.
The buffer_pool is allocated only in RAM.  It is the main memory user that grows over time, stopping when it hits innodb_buffer_pool_size.
Since the buffer_pool is a "cache", the data can be much bigger than the buffer_pool; the downside is the need to do more I/O to pull "blocks" of data (or indexes) into the cache, bumping out other blocks.
If all of the activity is concentrated on a subset of the data (eg, "recent" items), then the buffer_pool grows until "big enough" and may not actually expand to innodb_buffer_pool_size.  I/O activity will be low.
If the buffer_pool is bigger than all the InnoDB data and indexes you have, it will grow to about that size and then simply stay at that size.
The innodb_buffer_pool_size should not be so large that you run out of RAM.  When that happens, swapping occurs.  If there is no swap space, then the process is killed.


MS-Access lacks window functions, so you'll need a subquery solution. I think this would work:

SELECT c.*
FROM Customers AS c
  INNER JOIN
    ( SELECT CustCity, MIN(CustID) AS CustID
      FROM Customers
      GROUP BY CustCity
    ) AS m
  ON (  m.CustCity = c.CustCity
    AND m.CustID = c.CustID
     ) ;


There is a known issue in some circumstances.

As far as I can see, you need to use NO CACHE so numbers cannot be reused


How to search a given date value in all tables of present in database

You'll need to construct some dynamic T-SQL in order to search all columns in your database for a specific value.
Since you're searching for a DATETIME value, I've constructed the following example code which should point you in the right direction:
SET NOCOUNT ON;
DECLARE @FindText NVARCHAR(MAX) = 'CONVERT(DATETIME, ''2017-04-01 00:00:00.000'')';
DECLARE @cmd NVARCHAR(MAX);
DECLARE @deets NVARCHAR(MAX);
DECLARE @Params NVARCHAR(MAX);
DECLARE @Name VARCHAR(1000);
DECLARE @Count INT;
DECLARE @RowNum INT;
SET @RowNum = 0;
SET @cmd = '';
SET @deets = '';
SET @Params = '@Name VARCHAR(1000) OUTPUT, @Count INT OUTPUT';

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT 'SELECT @Name = ''' + s.name + '.' + o.name + '.' + c.name + ''', @Count = COUNT(1) FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' c WHERE ' + QUOTENAME(c.name) + ' = ' + @FindText + ' HAVING COUNT(1) > 0;'
    , 'SELECT * FROM ' + s.name + '.' + o.name + ' WHERE ' + QUOTENAME(c.name) + ' = ' + @FindText + ';'
FROM sys.schemas s WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON s.schema_id = o.schema_id
    INNER JOIN sys.columns c WITH (NOLOCK) ON o.object_id = c.object_id
    INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.is_ms_shipped = 0
    AND o.type_desc = N'USER_TABLE'
    AND s.name <> N'sys' --ignore the sys schema
    AND t.name IN ( --could eliminate all but datetime if certain!
          'date'
        , 'datetime2'
        , 'datetimeoffset'
        , 'smalldatetime'
        , 'datetime'
    );

OPEN cur;
FETCH NEXT FROM cur INTO @cmd, @deets;
WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT N'RowNum:' + CONVERT(VARCHAR(50), @RowNum);
    SET @Name = NULL;
    SET @Count = NULL;
    EXEC sp_executesql @cmd, @Params, @Name = @Name OUT, @Count = @Count OUT;
    IF @Count > 0 PRINT @Name + ': ' + CONVERT(VARCHAR(50), @Count) + '                            ' + @deets;
    SET @RowNum = @RowNum + 1;
    FETCH NEXT FROM cur INTO @cmd, @deets;
END
CLOSE cur;
DEALLOCATE cur;

Since this type of code can be susceptible to code-injection vulnerabilities, I'd never recommend using this in a stored procedure, or any code that is called by code where a user can enter the value for @FindText.  See "little bobby tables" for details about that.


Oracle: Very slow to purge table from recycle bin

My database server was out of disk space, which caused the never-ending purging.


How to repair mongodb collection when index is corrupt?

Try to repair it by using repairDatabase.

More information:
https://docs.mongodb.com/manual/reference/command/repairDatabase/


Low cardinality fields: integer or string

I would definitely normalize for big tables. You could use an integer or int2 column with a FK constraint to a lookup table.

For small tables either of your ideas is ok. If in doubt, stick to the guidelines of the project.

I would use neither. Instead:

For small tables: use an enum. The documentation:

  An enum value occupies four bytes on disk.

For big tables: use a "char" field (1 byte) as FK to a lookup table. A good choice for very small sets of lookup values. And each value can be a mnemonic for the referenced state:

status_id | status
----------+------------
n         | new
i         | in_progress
o         | on_hold
c         | closed

It also depends on the complete picture. You only actually save space (and gain performance) in table and indexes if it's not lost to alignment padding anyway. More:

Configuring PostgreSQL for read performance

For character types (except for "char"), collation rules may be relevant, too. More:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


How can I pass on a value in the hierarchy based on the value of the parent using an hierarchial query?

with a (id, parent_id, name, user_id, discount, lvl) as
(
  select
    c.id, c.parent_id, c.name, cd.user_id, cd.discount, 1 as lvl from category c
  LEFT JOIN category_discount cd
       ON c.id = cd.category_id AND cd.user_id = 1
  where parent_id is null
  union all
  select
    c2.id, c2.parent_id, c2.name, cd.user_id,
    nvl(cd.discount, a.discount),
    lvl + 1
  from category c2
  LEFT JOIN category_discount cd
       ON c2.id = cd.category_id AND cd.user_id = 1
  join a on c2.parent_id = a.id
)
search depth first by id set dummy
select id, parent_id, name, user_id, discount, lvl from a;

        ID  PARENT_ID NAME    USER_ID   DISCOUNT        LVL
---------- ---------- ---- ---------- ---------- ----------
         1            root          1         30          1
         2          1 C1                      30          2
         3          1 C3                      30          2
         4          1 C4            1         20          2
         5          4 C5                      20          3
         6          4 C6                      20          3
         9          6 C9                      20          4
        10          6 C10                     20          4
         7          1 C7            1         25          2
        11          7 C11                     25          3
        12          7 C12                     25          3

Answer to original question:

Assuming the following based on your example data (but I am not sure this is what you want):

There is a discount for the whole branch defined by the root - I call this root_discount.

If a child (c1) has discount set, then that is the discount for that child.

If c2 is a child of c1, and c2 does not have discount set, then c2 inherits the discount of c1.

If c3 is a child of c2, and c3 does not have discount set, then c3 inherits the discount from the root (root_discount). This makes the discount 30 for rows with ID 9,10. Without this, it gets a lot simpler.

with a (id, parent_id, name, user_id, discount, prev_discount, root_discount, lvl) as
(
  select
    c.id, c.parent_id, c.name, cd.user_id, cd.discount, 1 as prev_discount,
    discount as root_discount, 1 as lvl
  from category c
  LEFT JOIN category_discount cd
       ON c.id = cd.category_id AND cd.user_id = 1
  where parent_id is null
  union all
  select
    c2.id, c2.parent_id, c2.name, cd.user_id,
    nvl(cd.discount, case when prev_discount = 1 then a.discount else root_discount end),
    case when cd.discount is not null then 1 else a.prev_discount - 1 end as prev_discount,
    root_discount, lvl + 1
  from category c2
  LEFT JOIN category_discount cd
       ON c2.id = cd.category_id AND cd.user_id = 1
  join a on c2.parent_id = a.id
)
search depth first by id set dummy
select id, parent_id, name, user_id, discount, lvl from a;

        ID  PARENT_ID NAME    USER_ID   DISCOUNT        LVL
---------- ---------- ---- ---------- ---------- ----------
         1            root          1         30          1
         2          1 C1                      30          2
         3          1 C3                      30          2
         4          1 C4            1         20          2
         5          4 C5                      20          3
         6          4 C6                      20          3
         9          6 C9                      30          4
        10          6 C10                     30          4
         7          1 C7            1         25          2
        11          7 C11                     25          3
        12          7 C12                     25          3


You dont need to close the database for backup, just setup log-shipping with two servers (legacy and target), once you decide to migrate (cut-over) to target server just take log backup from legacy server and restore on target server "with recovery".


How do I modify a column type if the DB has many values

The problem isn't the number of rows, the problem is that you have a row which does not fit into the decimal(18, 0) data type. If you're on SQL 2012 you can identify the rows fairly easily (and can do your own number checking function on other versions).

Create Table dbo.Moo (
    Data Nvarchar(255)
)

Insert dbo.Moo Values ('-1'), ('100'), ('1.1'), ('Cows')

Select  Convert(Decimal(18, 0), Data)
From    dbo.Moo
-- Msg 8114, Level 16, State 5, Line 7
-- Error converting data type nvarchar to numeric.

Select  *
From    dbo.Moo
Where   Data Is Not Null
And     Try_Convert(Decimal(18, 0), Data) Is Null
-- Cows

Once they're identified you can fix the data on those rows and then convert the table over as you originally attempted.


A deadlock is effectively two concurrency transactions updating the same data. This need to be handled at application level because there's no obvious solution that always works without accounting for the business case.


UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL)

I would take a different approach.

Instead of updating existing tables, just build a new  table that has what you need in it.

This will almost certainly be faster:

SELECT DISTINCT
    AutoClassID,
    <Other fields>
INTO
    AutoDataImportStaging.dbo.Automobile
FROM
    AutoData.dbo.AutoClass

As currently written, there are a lot of logical operations happening:

Read all values of A.AutoClassName
Read all values of B.AutoClassName
Compare A and B values
Of the matching set, read all values of B.AutoClassID
Update existing values of A.AutoClassId to be the B.AutoClassId value through whatever indexes exist


Automatic failover support on SQL Server having only two servers

For automatic failover, you need a tie-breaker.

Otherwise, what would happen when the two servers had a network split and couldn't see each other? You wouldn't want them both automatically promoting themselves to primary, and both accepting writes. Think about what would happen on a table with an identity field, for example: both servers could quickly end up with two different records with the same identity field.

This is called a split brain scenario, and you always want SQL Server to fail into a non-available state when that happens.

To break the tie, database mirroring can use a witness server, but it can be an absolutely free SQL Server Express Edition.

Always On Availability Groups is different than mirroring in that it's built atop Windows clustering, which means it relies on quorum voting to achieve the tiebreaker. Normally, you would want a third Windows server involved, but good news: it doesn't have to have SQL Server installed. You can use a file share to act as a quorum witness, or even a cloud witness.

You could even get fancy and remove the quorum votes from one of your servers - but if that server goes down, your entire cluster will go down.


The usual way to do this, is to use a derived table:

select *
from (
 select first_name,
        last_name,
        first_name || ' ' || last_name AS user_full_name
 from users
) t
where user_full_name ilike '%lvi%';

That is extremely basic and generic SQL and any obfuscation layer (aka "ORM") should be able to deal with that.

Other options you have:

1. Use a view

Create a view with the calculated function and tell your obfuscation layer to use that view.

2. Create a "virtual" column

Postgres doesn't really have calculated columns (like e.g. Oracle or SQL Server) but you can simulate them with a function:

create function user_full_name(p_row users)
  returns text
as
$$
  select concat_ws(' ', p_row.first_name, p_row.last_name);
$$
language sql;

This can be used like this:

select u.user_full_name
from users u
where u.user_full_name ilike %lvi%'

Note that the prefix for the column name is required otherwise it does not work. I don't know if you can teach your obfuscation layer to create SQL like that.

3. Populate an additional column with a trigger

CREATE TABLE users
(
    first_name varchar(60) NOT NULL,
    last_name varchar(60) NOT NULL,
    user_full_name text
);

create or replace function update_full_name()
  returns trigger
as
$$
begin
  new.user_full_name := concat_ws(' ', new.first_name, new.last_name);
  return new;
end;
$$
language plpgsql;

create trigger update_full_name_trigger
  before update or insert on users
  for each row
  execute procedure update_full_name();

Then you can simply write:

select user_full_name
from users u
where user_full_name ilike %lvi%';

That is probably the solution that is the easiest to be integrated into an obfuscation layer.


Microsoft SQL secondary read-only DB for SSRS?

Transactional replication

In theory Transactional Replication would be your go to here, but in reality it can be tricky to set up and maintain. This is very opinionated, but I don't think you would find many DBA's who really enjoy (setting up) transactional replication.

I am not saying you shouldn't, but it can get frustrating.

Log shipping

Another option could be Log shipping while keeping the databases in Standby Mode, but then you would have to choose between:

A) Getting your log restores behind.

B) Getting your connections kicked out when logs are restored.

It is not ideal, but it could work.
There are other things you would need to work around, listed here.

Backup Restore

You could restore from backup, either by using your full (+ Diff / Log) backups, and simply restoring up until your last backup.
The main advantage being that it is simple to set up and does not put a lot of strain on the server.

You could also set up side by side restores to refresh your data during reporting hours.

Check out Restore-DbaDatabase by Dbatools if you haven't yet.

The main disadvantage being that you would work on stale data.
If this disadvantage is a no-go or not depends on your requirements, hardware, database size,....


Does PL/SQL code run differently in sql plus vs sql developer?

SQL*PLUS and SQL Developer both are client application for Oracle database. These applications just validate the syntax of statements and submit to Oracle Database server for execution. As far as the question is concerned about the uses of / and ; in these application I would like to demostrate as best as I can.

SQL*PLUS

In SQL*PLUS-
 ; is used to end the current statement.
 / is used to end current statement as well as If you are in the SQL prompt(SQL>) to run the command which are in the buffer.

Examples:

To end and execute the statement-

SQL> select * from jobs;

SQL> select * from jobs
  2  /

To run the command which is in the buffer(Only the SQL command(not SQL*PLUS command) and the last executed command remains in the buffer).

SQL> /

Instead, you can use RUN command for the same purpose. The difference is / doesnt show the command being executed but RUN does.

SQL> RUN
  1* select * from jobs

SQL*PLUS treats PL/SQL program as the SQL command except the ; and the blank line. In normal SQL command if you insert an blank line then it terminates the command as shown below.

SQL> select * from jobs
   2
SQL>

But in PL/SQL block it doesnt terminate the program.

To terminate and execute the PL/SQL block we use the same /.

SQL Developer

In SQL Developer we use CTRL+ENTER or F5 keys to execute commands. We dont need to use ; and / to indicate the end of the command instead we use Run Statement(CTRL+ENTER) or Run Script(F5).


Logshipping alert emails are being sent out while there is no error

I have had similar issues to this and every time it was caused by a cloned machine where the SQL components had not been renamed (@@SERVERNAME, Database Mail SMTP, and so on).  I would check that possibility first.  If the LSAlert job is stopped you should not be receiving mails any more.


But why do Oracle stores this so inefficiently? (a separated structure
  for the primary key)

It is inefficient for this use case.

  Do Oracle has something similar to MS SQL's clustered index, that
  makes the index part of the related table?

It is called IOT (Index-organized Table) in Oracle.

Overview of Index-Organized Tables

For example:

CREATE TABLE mytable
(
 c1 number,
 c2 number,
 c3 number,
 c4 number,
 CONSTRAINT pk_mytable PRIMARY KEY (c1,c2,c3)
)
ORGANIZATION INDEX
;

With this, you will have only one segment caled PK_MYTABLE.

  Is there something I can do to avoid this?

See above.


UPDATE statement processing records that should be eliminated by WHERE clause

You should not make any assumptions about how SQL Server will process your query, except this: you should always assume that SQL Server can process your query in a way that is different from how it is explicitly written on the screen. And also this behavior can change based on any of the factors that can influence whether a new plan will be used for the next execution of even the same query, so if you apply a hint or change the query in any way or add or remove an index and the error goes away, don't assume the error won't come back tomorrow.

In this case, SQL Server is processing a calculation before it is eliminating rows from the WHERE clause. The way you avoid this is, like you said, ensuring that those rows are also filtered out inside the CASE expression (not statement).

A more common but similar approach is this kind of thing:

SELECT DATEPART(MONTH, varchar_column)
FROM dbo.some_table
WHERE ISDATE(varchar_column) = 1;

In many scenarios you will get an error message because SQL Server tried to apply the date functions against some values in the column that didn't turn out to be dates (and this attempt occurred prior to filtering). The workaround is tedious - use the CASE expression - but necessary unless you have some other way to verify the worthiness of the column (e.g. a computed column or fixing the data type in the first place). Just keep in mind that even this can fail to "short circuit" in some scenarios.

SELECT CASE WHEN ISDATE(varchar_column) = 1
  THEN DATEPART(MONTH, varchar_column) END
FROM dbo.some_table
WHERE ISDATE(varchar_column) = 1;

This is explained more thoroughly in the following feedback item by Erland Sommarskog:

SQL Server should not raise illogical errors


If this is a manual update you could script out the commands you need:

SELECT 'UPDATE '+ QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) +
' SET deleted = NULL
WHERE ID =1;'
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'deleted';

But, be wary of these INFORMATION_SCHEMA dmvs.

Aaron Bertrand lists some good reasons not to use them in this blogpost on bad habits:

  ... I feel similarly about the INFORMATION_SCHEMA views. Since they are
  incomplete, aren't being updated for most new features, and Microsoft
  themselves recommend against them, I find it hard to justify using
  them in cases where you can (like getting just a list of columns from
  a table), but then having to use the catalog views when you need
  information the INFORMATION_SCHEMA views simply don't have (like
  partitions, or filtered indexes, or INCLUDE columns). Unless you need
  to write code that will work on multiple RDBMS platforms (in which
  case you can't use any of the SQL Server-specific features anyway),
  why not just use the catalog views always?...

So here is another solution using sys.tables & sys.columns

SELECT 'UPDATE '+ QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name]) +
' SET deleted = NULL
WHERE ID =1;'
FROM   sys.tables t
WHERE EXISTS
(
SELECT * FROM
sys.columns c
WHERE t.object_id = c.object_id
AND  c.[name] = 'deleted'
);

Testing

CREATE TABLE dbo.tableNR1(Id int, deleted bit);
INSERT INTO dbo.tableNR1(id,deleted)
VALUES(1,1);

CREATE TABLE dbo.tableNR135(Id int, deleted bit);
INSERT INTO dbo.tableNR135(id,deleted)
VALUES(1,1);

Result for both:

UPDATE [dbo].[tableNR1] SET deleted = NULL   WHERE ID =1;
UPDATE [dbo].[tableNR135] SET deleted = NULL   WHERE ID =1;

DB<>Fiddle


How to get the next auto-increment id in MySQL?

Here is an approach for this.

Step 1: Insert without payment_code.

Step 2: Get mysql_insert_id from STEP 1.

Step 3: Update payment_code with where condition as mysql_insert_id.

Code Snippet in Php:
 The commands might have to be corrected.

1.    mysql_query("INSERT INTO payments (date, item, method, payment_code)
    VALUES (NOW(), '1 Month', 'paypal')");

2.     printf("Last inserted record has id %d\n", mysql_insert_id());

3.     mysql_query(“UPDATE payments set payment_code=CONCAT("chaminda",mysql_insert_id()) where id=mysql_insert_id());


Creating SQL Assembly error Method parent has circular class type parameter constraints

The issue probably has something to do with the BigInteger class. It is found in System.Numerics which is not in the list of Supported .NET Framework Libraries. Which means that you probably loaded that manually as UNSAFE (or have not loaded it yet?). Which then means that your assembly, HMDACalculateULICheckDigit, needs to be marked as UNSAFE.

This assessment is further supported by the fact that you got a "failed verification" error message, since verification is a process that happens at CREATE ASSEMBLY time and checks for obvious indicators of requiring UNSAFE.


Number of virtual tables that specified user is able to write queries in PostgreSQL

I try to answer your question - to be correct, I try to answer the question I think you've asked (see the comments).  Until then I'll assume that a virtual table is simply a table and you want to count the tables a user has SELECT privilege on.

First, you have to collect all roles which the given user belongs to.  This will include the user itself, all roles that are granted to it, all roles that are granted to the roles granted to your user etc., and finally, but usually most importantly, the PUBLIC role.

You can collect these from the information_schema.applicable_roles view with a query like this:

WITH RECURSIVE privs(grantee, role_name) AS (
    SELECT grantee, role_name
    FROM information_schema.applicable_roles
    WHERE grantee = 'my_user'

    UNION ALL

    SELECT ar.grantee, ar.role_name
    FROM
        information_schema.applicable_roles ar
        JOIN privs ON ar.grantee = privs.role_name
)
SELECT role_name FROM privs

UNION

SELECT 'my_user'

UNION

SELECT 'PUBLIC'

Then you have to consult the information_schema.table_privileges view.  In order to see all the rows belonging to all grantees, you have to be logged in as a superuser.  With an ordinary user you will see only the roles granted to your user (including indirectly granted roles, ie. if the grants are user -> user1 -> user2, you will see user -> user1 and user1 -> user2 if logged in as user, and only user1 -> user2 if logged in as user1.)

Here you can look for tables with SELECT privilege like

SELECT table_name
FROM information_schema.table_privileges
WHERE
    privilege_type = 'SELECT'
    AND grantee IN (... the recursive query above goes here ...)
;

Turning this into a count is left as an exercise for the reader :)


Optimise and merge SELECT query results from 2 tables

Try this:

SELECT u.user_id,
       u.user_name,
       u.user_email,
       u.user_password_hash,
       fn.meta_value,
       ln.meta_value
FROM   `users` AS u LEFT JOIN `usermeta` AS fn ON u.user_id = fn.user_id
                    LEFT JOIN `usermeta` AS ln ON u.user_id = ln.user_id
WHERE  u.user_name = 'demo'
AND    fn.meta_key = 'first_name'
AND    ln.meta_key = 'last_name'
LIMIT  1


For each field name in a list of fields, get the unique values

One approach for solving this problem is to use dynamic sql. In fact, your question is one of the example use cases:

For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.

At compile time, you don't know the table names, column names, or the number of subclauses for your query. As far as I know, you cannot write this as a simple SQL query. You'll need to have some intermediate object create the query text for you.
First let's write out a full query that does what you need for your three example rows. I propose the following:
SELECT 'ENG.TABLE_1', 'FIELD_1', FIELD_1 FROM ENG.TABLE_1
UNION
SELECT 'ENG.TABLE_1', 'FIELD_2', FIELD_2 FROM ENG.TABLE_1
UNION
SELECT 'ENG.TABLE_2', 'FIELD_A', FIELD_A FROM ENG.TABLE_2

The UNION operator will only return the distinct result set from the query so this should return the correct results. One way to generate the above query using a query against the view is to take advantage of the LISTAGG operator.

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

For the measure expression I propose building a string that adds quotes around the column names as needed. Because this is a string it gets a bit messy:
'SELECT ''' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))
    || ''', ''' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ''', ' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ' FROM ' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))

For the delimiter I propose the following:
' UNION '

Here is what the query looks like when you put it all together and use the view:
SELECT LISTAGG(
    'SELECT ''' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))
    || ''', ''' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ''', ' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ' FROM ' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))
    , ' UNION ')
WITHIN GROUP (ORDER BY NULL) into varStringToExecute
FROM FIELD_LIST_VW;

What the above query does is generates the select query that you need for each table and column from the view as a string, adds UNION between all of the queries, and concatenates the entire string together. If you need the query to build in a specific order you can use the ORDER BY clause in LISTAGG.
You can execute the query using dynamic SQL:
EXECUTE IMMEDIATE varStringToExecute;
You didn't say what you need to do with this data. If you need to just return it to the client you may want to use a cursor. Here is one set of ideas on how to accomplish that.
Note the return value of LISTAGG is VARCHAR2, which means that your query text is limited to 4000 characters with default settings. It is possible to increase that limit up to 32767 by setting the MAX_STRING_SIZE parameter, but I don't know if that's appropriate for your database. If you can't do that, there are various workarounds for creating a string over 4k characters.
Apologies if some of the code in this answer doesn't not compile. I don't have access to Oracle at the moment.


The most important consideration is to pick the right database recovery model that meets the business needs. If you're using a recovery model of full then you also need to backup the log on an appropriate frequency. There's also a question of scale here. If your log is 32 GB in size for a 10 GB database then I understand why you're considered. However, if that log file is for a 10 TB database then the right thing to do might be to allocate more space for the transaction log files. Be careful in how you think about the log files. It's a good thing that protects against data loss. It shouldn't be viewed as your enemy that should shrunk as much as possible.
With all of that said, there may be different ways of writing code which lead to the same results but reduce total writes on the transaction log file. Taking advantage of possible optimizations in this space can improve performance. Some high level tips:

If your business needs allow you to change the recovery model to simple then some code may qualify for minimal logging.
There may be an opportunity to change code to use temp tables instead of user tables. Tempdb always has a recovery model of simple so you can get minimal logging for those operations.
Your application design may allow for the use of a scratch database to hold temporary data, such as a staging database. Your business requirements may allow the staging database to have a recovery model of simple while keeping the recovery model of the real database as full.
Getting data Right The First Time can reduce log writes. For example, a single INSERT instead of an INSERT + UPDATE will often write less to the log. Same concept applies to avoiding operations which delete large numbers of rows, if possible.
SQL Server acquired locks need to be written to the transaction log as well. In high scale scenarios you may see an improvement by adding TABLOCK hints to reduce transaction log activity.


check your my.cnf file to see if there is anything force innodb to recover

force_innodb_recovery

take a look to this topic that talk about similar problem Got error -1 from storage engine

or make sure the /tmp partition or the partition the MySQL databases are stored on is not full.


log_warnings doesn't seem to work for aborted connections in MySQL 5.1

This seems to be an elusive bug nobody wants to fix

http://bugs.mysql.com/bug.php?id=24761 (2006-12-01 : Status Triaged)
http://bugs.mysql.com/bug.php?id=41602 (2008-12-18 : Status Closed)
https://serverfault.com/questions/235369/how-can-i-get-mysql-5-5-to-log-warnings-to-one-of-the-log-files (2011-02-14)

According to http://bugs.mysql.com/bug.php?id=24761, the aborted connect is recorded in the general log

[29 Sep 2008 8:03] Konstantin Osipov
OK, it was fixed differently:
      /*
        Log the command before authentication checks, so that the user can
        check the log for the tried login tried and also to detect
        break-in attempts.
      */
      general_log_print(thd, command,
                        (thd->main_security_ctx.priv_user ==
                         thd->main_security_ctx.user ?
                         (char*) "%s@%s on %s" :
                         (char*) "%s@%s as anonymous on %s"),
                        thd->main_security_ctx.user,
                        thd->main_security_ctx.host_or_ip,
                        db ? db : (char*) "");

So, this is logged in the general log at least.

I was reviewing a patch that added more logging,
but I can't remember the worklog task number.

We just have to wait on MySQL (I mean Oracle) to fix it. (That was sarcasm)


Create user on Azure elastic database and deny view to other databases i same setting

Reading you comment I see now what you are looking for.

Here is how you can do this.

Create 2 empty database:

USE [master]
GO
CREATE DATABASE [TestPerm1]
GO
ALTER DATABASE [TestPerm1] SET COMPATIBILITY_LEVEL = 130
GO

USE [master]
GO
CREATE DATABASE [TestPerm2]
GO
ALTER DATABASE [TestPerm2] SET COMPATIBILITY_LEVEL = 130
GO

Create 2 login

USE [master]
GO
CREATE LOGIN [testPerm1] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [master]
GO
CREATE LOGIN [testPerm2] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Create a user in master using one of the login

USE [master]
GO
CREATE USER [testPerm2] FOR LOGIN [testPerm2] WITH DEFAULT_SCHEMA=[dbo]
GO

Using SSMS connect with user testPerm2 using default setting. You will be able to see all database ( I know you do not want this, but hang on)

Create a user with testPerm1 in testPerm1 database that we created earlier.

USE [TestPerm1]
GO
CREATE USER [testPerm1] FOR LOGIN [testPerm1] WITH DEFAULT_SCHEMA=[dbo]
GO

Now using SSMS connect to the server by changing default database, this user will only see testPerm1 but not testPerm2.


MySQL 8: Truncate table stuck in `checking permissions` state for minutes

Hence my advice not to have more than about 50 partitions in a table.  Or to abandon partitioning altogether.

What may be happening:  table_open_cache (or one of the other caches) may be too small, and, hence, thrashing.

To verify that theory, I need to check several things.  Please provide SHOW GLOBAL STATUS;, SHOW VARIABLES; and how much RAM you have.  More details.

To critique the partitioning, please provide SHOW CREATE TABLE for at least one of the tables with a lot of partitions.

And you are TRUNCATEing a table with a lot of partitions?

If possible, look in the filesystem and list some of the files for that table.


Your operation will most likely be a logged operation as you're performing an insert.

Monitor the log file growth and you should see that's what is utilizing your remaining disk space.

Consider inserting using a minimally logged approach, these include:

BULK INSERT
INSERT...SELECT
MERGE with Trace Flag 610

I have also found an insert to a column with an identity specification will also cause logging to occur.

Links:
http://blogs.msdn.com//b/sqlserverstorageengine/archive/2008/02/05/bulk-logging-optimizations-minimal-logging.aspx
http://www.sqlmag.com/article/tsql3/minimally-logged-inserts

Also: I would recommend inserting into a heap and then apply your indexes unless your data has been pre-sorted in the source database.

Consider defining the identity value in your source database, select all the data into the destination and then apply the identity specification and set the seed.


Imagine that the deletion is a process which consists from 2 steps. At the 1st step the server performs according SELECT * selection, and marks the rows which must be deleted. At the 2nd step it deletes marked rows.
DELETE p1 in your query means that only rows selected from a table aliased as p1 must be marked for deletion. If you'd use DELETE p1, p2 or simply DELETE FROM then selected rows from all table copies 'd be marked.


Why does my Query use a Bitmap Heap Scan

It is doubtful whether this query needs speeding up, but if anything can improve the performance, it would be
CREATE INDEX ON connections (host_name, epoch_time) INCLUDE (connections);

This assumes that there is actually a column connections; if that is a whole-row reference, you cannot get an index-only scan.
Make sure the table is vacuumed often.
Also, bear in mind that each index slows down data modifications. So use it only if query speed is paramount.


Does mongo *really* need a dedicated volume, if I need to back it up?

Yes, hot snapshot is easy and fast way to do backup... But of course there is also mongodump. Here is my crontab entry. Every hour, overriding current date backup.
SHELL=/bin/bash
DATEVAR=date +%d

31 * * * * /usr/bin/mongodump --host localhost -o /dbdata/dump$($DATEVAR) --gzip --oplog >> /dev/null 2>&1


As far as I can see your schedule is set to execute between midnight (12:00AM) to noon (12:00PM), i.e. your job will not execute in the afternoon and evening. Change your ending time to 11:59:59PM and it should run all day.


Inspecting Anonymous Column in Stored Proc Resultset

Store the results in a temporary table or table variable and work with the stored set.

Here's a quick prototype you can work with.

create PROCEDURE [dbo].[TestMe]
AS
BEGIN
IF 1=1
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END
END

Declare @Results Table (CanDeleteIndicator char(1),Description varchar(30))
insert @Results exec dbo.testme
select * from @Results


The postmaster.pid file is one of the measure that PostgreSQL takes to prevent starting multiple database servers on the same data directory at the same time (which would cause data corruption).
If you start the PostgreSQL server and there is a postmaster.pid file present, PostgreSQL checks if there is a process running with the process with the process ID from the PID file. If not, PostgreSQL will ignore the obviously stale file and start anyway. Otherwise, it will refuse to start.
The correct solution is to see that the database is shut down cleanly when you turn off the machine. A clean shutdown will remove postmaster.pid. This will also save you the time to perform crash recovery on the PostgreSQL database.


MONGODB primary became secondary after servers restart

You need to change the config such that there is only one member remaining, and you have to do it on that host, i.e. Ubuntu12041vanilla:27017

The others are unreachable from its perspective, so even if you go with 2 nodes, 1 out of 2 down means you cannot form a majority and you will still only have secondary state.

Given your current rs.status() output, try this when connected via the mongo shell to Ubuntu12041vanilla:27017:

cfg = rs.conf()
cfg.members = [cfg.members[0]]
rs.reconfig(cfg, {force : true})

That will give you a config only one node and should mean that Ubuntu12041vanilla:27017 will be the only remaining member and primary.


How to update values in production table with foreign keys?

If  you don't change the a ID simply do a
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE [database_name.]table_name;
INSERT INTO new Values
SET FOREIGN_KEY_CHECKS=1;

If you want to change the a ID
First do your updates, so that you have all changes and then disable the foreing keys


Why are you using a batch file to backup the databases?  Why not put the code in a SQL Agent job and have that kick off the backup?


"My question is how and when does SQL Server create multiple leaf
  levels (index_level 0) for the same index."

The 2008 R2 documentation for sys.dm_db_index_physical_stats includes a link to Table and Index Organization, which shows the following diagram:

It describes the data that may be stored in each of the three possible allocation unit types:

Your clustered index does contain three leaf levels, one per allocation unit type.  For example:

CREATE TABLE dbo.Example
(
    example_id  integer PRIMARY KEY,
    lob_data    nvarchar(max) NULL,
    padding     varchar(8000) NULL,
    overflow    varchar(8000) NULL
);

INSERT dbo.Example
    (
    example_id,
    lob_data,
    padding,
    overflow
    )
VALUES
    (
    1,
    REPLICATE(CONVERT(nvarchar(max), N'X'), 8001),
    REPLICATE('Y', 4000),
    REPLICATE('Z', 6000)
    );

SELECT
    ddips.index_id,
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID('dbo.Example'),
        1,
        1,
        'DETAILED'
    ) AS ddips;

Output:

╔══════════╦═════════════════╦══════════════════════╦═════════════╦════════════════════════════════╗
║ index_id ║ index_type_desc ║ alloc_unit_type_desc ║ index_level ║ avg_page_space_used_in_percent ║
╠══════════╬═════════════════╬══════════════════════╬═════════════╬════════════════════════════════╣
║        1 ║ CLUSTERED INDEX ║ IN_ROW_DATA          ║           0 ║ 50.3953545836422               ║
║        1 ║ CLUSTERED INDEX ║ ROW_OVERFLOW_DATA    ║           0 ║ 74.3019520632567               ║
║        1 ║ CLUSTERED INDEX ║ LOB_DATA             ║           0 ║ 99.0239683716333               ║
╚══════════╩═════════════════╩══════════════════════╩═════════════╩════════════════════════════════╝

Your table contains large object (LOB) columns (MAX or old-style text, ntext or image types) and variable-length column definitions which allow individual rows to exceed the 8060 byte INROW limit.

For rows that exceed 8060 bytes, ROW_OVERFLOW_DATA allocation units will be created.  This is often problematic for performance, since row data access requires following an off-page pointer to retrieve the overflowed data.

I would certainly look at the design of the table before worrying too much about how full the pages are on average.  Whether you should be concerned about page fullness depends on which allocation unit it refers to.


Why does the CBO eliminate `distinct` and not `group by` in this query?


SQL Server 2005 datacube processing in high transaction system

Dimensions need to have all the values that the cube has. To avoid having cube value that doesn't exist in the dimensions, I did the following:

have a separate database as a staging database
periodically fill this staging db with data from transaction db
the cubes are generated from this staging db
dimensions are refreshed first, then the cubes
NO data modifications in the staging db between refreshing dimensions and refreshing cubes


Login permissions outside of sa - SQL Server

what about:

removing them from the sa server role
give them the following server roles: dbcreator, securityadmin
give them db_owner on every user database they need
If they need to use SQL Agent, on msdb give them
SQLAgentOperatorRole
If they deploy SSIS packages, on SSISDB, give them ssis_admin

then you wait for someone's complaining and give also what they need.

Plus:

Well, I know that this is not the best script (10 min...lol), but will solve your problem to give permissions on every database. Maybe you can improve it to solve your problem.

EXEC sp_msforeachdb @command1 =
N'
    use ?
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')
    CREATE USER [<DOMAIN\MyGroup>] FOR LOGIN [<DOMAIN\MyGroup>]
'

EXEC sp_msforeachdb @command1 =
N'
    use ?
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')
    ALTER ROLE [db_owner] ADD MEMBER [<DOMAIN\MyGroup>]
'


Affinity side effects / TF 8002- how to measure

Since these instances are SQL Server Standard Edition, this will have to be without the functioning or performance counters provided by the SQL Server Resource Governor, which is an Enterprise Edition feature.
I recommend capturing the [\Process(sqlservr*)% Processor Time] perfmon counters (on a scale of logical processors * 100%), and the [\Processor Information(*)% Processor Time] counters.  You'll want to note which SQL Server process is affnitized to which logical processors, since sharing is happening.  Within the SQL Server instances, I recommend capturing wait_time_ms for SOS_SCHEDULER_YIELD type, as well as signal_wait_time_ms across all wait types. SOS_SCHEDULER_YIELD is the wait that is incurred when a task has used up its quantum on the scheduler and must yield, waiting for its next turn to get on the scheduler and run again.  You'll notice that the signal_wait_time_ms for SOS_SCHEDULER_YIELD is almost if not equal to wait_time_ms.  That's because once it's yielded that task/worker really is ready to get back on the scheduler as soon as possible.  And the signal_wait_time_ms across all wait types (including SOS_SCHEDULER_YIELD) indicates how much time is spent ready to run but just waiting for a turn after the wait resources are available.
The hope is that under a similar workload, trace flag 8002 and the "opportunistic" binding of schedulers to underlying logical CPUs will result in increased rate of work, higher CPU utilization, and decreased times for SOS_SCHEDULER_YIELD and signal wait time across all waits.


Delete using id's not found in loop - MySQL

Maybe im wrong but the  SET item_separator='' looks empty. I could imagine that your ID´s you build there are not right and therefore he does not ignore them.
I'm going to rebuild a scenario like this and try to help you. But first see if the output of save_list is really a string of ID´s.


Compare the structure of two MySQL Databases

Using INFORMATION_SCHEMA.COLUMNS, here is my proposed query

SELECT B.* FROM
(
    SELECT DISTINCT table_name FROM
    (
        SELECT table_name,column_name,ordinal_position,data_type,column_type,COUNT(1) match_count
        FROM information_schema.columns WHERE table_schema IN ('db1','db2')
        GROUP BY table_name,column_name,ordinal_position,data_type,column_type
        HAVING COUNT(1) = 1
    ) AA
) A INNER JOIN
(
    SELECT table_schema,table_name,column_name,ordinal_position,data_type,column_type
    FROM information_schema.columns WHERE table_schema IN ('db1','db2')
) B;
USING (table_name)
ORDER BY B.table_name,B,table_schema;

The output will be each the columns differences. You will see differences by data type, column type, and/or column position. You should quickly see if a table only appears in one one database and not another.


Split varchar2 variable based on vertical line delimiter

You can use the functions INSTR to locate the poition of '|' in the string and SUBSTR to retrieve the relevant part of the string.

So

select
    SUBSTR(personal_info, 1, INSTR(personal_info, '|') - 1),
    SUBSTR(personal_info,INSTR(personal_info, '|') +1 )
from users

will returrn two columns with the appropriate parts of personal_info.


Database link from Local Oracle 11g to Oracle Database Cloud Service 11g

That is not how you connect to another database through database links. Database links require SQL*Net connectivity, not HTTP(S).

First you need to enable access, then you can connect using the address of the compute node and the service name for your database.

http://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-372D4233-9793-47EC-8DFC-AFD6CF32866C.htm#CSDBI3449


Why would SET ARITHABORT ON dramatically speed up a query?

A little dated, but for anyone ending up here with a similar problem...

I had the same problem. For me it turned out to be parameter sniffing, which at first I didn't understand enough to care about. I added a 'set arithabort on' which fixed the problem but then it came back. Then I read:

http://www.sommarskog.se/query-plan-mysteries.html

It cleared -everything- up. Because I was using Linq to SQL and had limited options to fix the issue, I ended up using a query plan guide (see end of link) to force the query plan I wanted.


SQL trigger when a row is updated question

first create one temp table , and create records whenever your updating row. for example if you are updating one row with id=24, save this id as refference id into temp table.then using Php  if temp table contains any records call curl request and delete temp records once curl request is completed. by using cron jobs run this php scripts repeatedly


Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it

The time-consuming part of shrinking is moving the data out of the latter parts of the file into spaces nearer the beginning so that the rear parts can be truncated, you have to move the data before reclaiming the space because SQL Server doesn't support sparse files (it can't just tell the OS "I don't need that bit, feel free to allocate it elsewhere for now"). This means that there is nothing you can do in-place that will be any faster.
You may be lucky though and find that most of the back end of the files is empty, allowing you to simply truncate them to release some space to the filesystem (DBCC SHRINKFILE (<file>, TRUNCATEONLY);).
You could migrate the data to fresh filegroups then drop the existing filegroups. If you do this on the same volume though, given that you are trying to release 50% or less this will be no quicker, and possibly much slower (it must move 200Gb to the new files, where a shrink will move at most 200Gb around the files). If you do it between volumes though you might see this work much faster if you are using spinning-disk based media, because there is likely to be a lot less head movement latency compared to the shrink. If you are using low-latency storage this benefit will not be significant.

Shrinking is not an option as it is time consuming.

One thing to note is that shrinking is effectively a resumable process - you do not lose all your progress to a rollback if you stop it part way through. So if you have short maintenance windows where you can accept the performance impact of running shrink operations you could start the process and cancel it at the end of your window, and start it again next time. This may take several such windows but would eventually get the job done.
Another issue to mention is that both shrinking and moving data between filegroups work at the page level so will not release unallocated space within pages. This is not likely to be a massive problem for you if most of the free data was off-page LOBs, but if a low of row data was removed to you might want to rebuild your indexes (and heaps, especially heaps, if you have any such tables with no clustered index) before shrinking/migrating the data. You may even find that this will effectively move the data mostly to the front of the files anyway allowing you to truncate them significantly without needing to do a full rearrange and shrink. Depending on your SQL Server version and edition, you can use resumable online index rebuilds to split this process over multiple maintenance windows as described for the shrink operation above.


"Insufficient privileges" while shutting down as sysoper

It depends upon how you have granted the SYSOPER privilege. To shutdown the database instance user should have SYSOPER privilege on all containers.

The following demonstrate the scenario.

I have common user call C##_TEST.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:47:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select common from dba_users where username='C##_TEST';

COM
---
YES

I have granted SYSOPER to the user for the ORACDB(CDB) only.

SQL> grant sysoper to c##_test container=current;

Grant succeeded.

Then I have connected to container database using C##_TEST user as SYSOPER and tried to shut down the database and got the ORA-01031 error.

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
ORA-01031: insufficient privileges
SQL>

Now I again granted the SYSOPER privilege with the option container=all. And this time the user can shut down the database.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant sysoper to c##_test container=all;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:50:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


To answer your question, I start out with an empty collection csdbprotobuf.archive on a stand-alone server that has nothing currently in cache

db.serverStatus({workingSet:1})

"workingSet" : {
    "note" : "thisIsAnEstimate",
    "pagesInMemory" : 10,
    "computationTimeMicros" : 4736,
    "overSeconds" : 81
},

Create the database and insert 10000 documents, an integer _id field and a string some name that has nine characters

use csdbprotobuf
t=db.archive
for(var i = 1; i <= 10000; i++){t.insert({a : i, name : 'some name'})}

Run an explain to load my data into memory called pre-heating

t.find().explain()
> t.find().explain()
{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 10000,
    "nscannedObjects" : 10000,
    "nscanned" : 10000,
    "nscannedObjectsAllPlans" : 10000,
    "nscannedAllPlans" : 10000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 78,
    "nChunkSkips" : 0,
    "millis" : 1,
    "server" : "precise64_work:27000",
    "filterSet" : false
}

What is the status of memory?

    "workingSet" : {
        "note" : "thisIsAnEstimate",
        "pagesInMemory" : 363,
        "computationTimeMicros" : 2136,
        "overSeconds" : 600
    },

Initally I had 10 pages of data in memory, after the pre-heat using .explain(), I now have 363 pages in memory. Started out with 31MB of resident memory and now have 34MB which give me a difference of 3MB or 3072KB.

10000 documents / 3072KB = 3.255kb per document.

10000 documents / 353 pages of data = 28 documents per page

Using MongoDB's db.serverStatus({workingSet:1}) command, I was able to determine (approximately) how many documents fit onto a page.

It is important to note though, that each document in MongoDB is totally different. This is because of the polymorphic nature of its schema design. So just trying to determine exactly how much space one document is going to take is probably not what you are looking for.

Just because one document takes up 3KB doesn't mean that the next document won't take up twice that much or even more. This is not the way a traditional RDBMS works as you would know how much space each row can use.


It depends on how you did it.
With a plain SET, you would change the parameter for the whole session.
With SET LOCAL, you would change the parameter for the complete database transaction.
The best way would be to modify the procedure so that the setting is only active for the duration of the execution of the procedure:
ALTER PROCEDURE procname SET enable_hashjoin = off;

Let me add that this is only a last ditch measure: it would be much better if you could fix the bad row count estimates that lead to the bad choice of join strategy.


Alternative to second column for tracking a FOREIGN KEY pre-deletion

I've worked with a SQL Server DB where the relationship between two tables was maintained by triggers instead of actual foreign keys (without the need for the second column).

Basically, in the "child" table:

on INSERT, check to make sure the inserted value in the "FK" column exists in the "parent" table;
on UPDATE, check to make sure any updated value in the "FK" column exists in the parent table.

In the "parent" table (using an AFTER trigger or equivalent):

on UPDATE, if the "PK" value is changed, take whatever action is appropriate if there are rows in the "child" table using the PK (change the "FK" column to  match; prevent the update if there are "children" already using the current value; do nothing; etc.)
on DELETE, take whatever action is appropriate if there are rows in the "child" table using the PK (prevent the deletion; set the value to NULL; or, in your case, do absolutely nothing).

Since the DB is not maintaining the relationship, you need to make sure you handle each of the possibilities above properly. It'll need to be documented as a known "gotcha" of the DB design, something that new devs get cued in on quickly, so they don't make assumptions regarding it.

Also, remember that disabling triggers also disables this "foreign key" relationship (unlike a true FK).

I'm not necessarily saying this would be better than your history column solution (which has its own maintenance hassles), but it is an option.


The number of copies for each table stored in physical storage

It sounds like you might be coming from an Oracle background, where the Oracle database generally manages the storage system directly.

That's not how PostgreSQL works. It uses the file systems, RAID support, etc provided by the operating system.

To get redundant storage with PostgreSQL you need to set up redundant stoage - in the form of RAID, or whatever - in the underlying operating system. Then simply put the PostgreSQL data directory and tablespace(s) on file systems that are stored on the redundant storage array(s).

A common configuration for small installs is:

  SATA disk 1           SATA disk 2
       |                     |
       -----------------------
                   |
                   v
  [Linux 'md' software RAID driver, RAID 1]
                   |
                   v
  [ext4 file system mounted on /var/lib/pgsql]
                   |
                   v
 [PostgreSQL datadir in /var/lib/pgsql/9.3/data]

Of course, that does you no good at all if the machine is destroyed by fire/lightning surge/etc, or someone does an rm -rf /var/lib/pgsql. Which is why you should also have replication to another machine, preferably off-site.

Replication won't help you if someone does a DROP TABLE precious_data; ... which is why you should additionally have frequent logical backups (pg_dump) and/or point in time recovery with WAL archiving.


Self join increase paging query performance

I really cannot see the point of using self-join in this query. It is 100% equivalent to this:

select m.*
from message m
where m.thumbs_up = 98 and m.id < 13
   or m.thumbs_up < 98
order by m.thumbs_up desc, m.id desc
-- limit N
  ;

I would expect an index on (thumbs_up, id) to be used by this.


What are the advantages and disadvantages of Third normal form and Boyce-Codd normal form?

Third normal form(3NF) includes the rules of the lower level normal form, such as second (2NF) and first normal form (1NF). Similarly, Boyce/Codd normal form (BCNF) includes the rules of the lower level such as 3NF, 2NF and so on (see here). Normalization provides both advantages and disadvantages, some of them are mentioned below.

Advantages of  normalization:

Reduced data redundancy:

In each level of normalization a type of data redundancy (same data present in more than once) removed from the model. Once the redundancy is removed, it is easy to change the data since data is present in only one place.

Increased data quality:

By applying business rules, there is a smaller chance in storing unwanted data in the table. Also there is less need for storing null values.

Capturing complete business requirements:

In normalization we have to ask a lot of questions to business people, so our understanding of the business becomes stronger and requirements of the business are easy to capture.

Data modification anomalies (Insert, delete and update)  are reduced.

Normalization splits a entity into a smaller number of entities, so you will have more smaller tables, so it is easier for sorting, indexing and searching.

Since there are many smaller tables, you will have many clustered indexes, it will help you in query tuning.

A normalized data model can be easier to modify and maintain.

Disadvantages

With many number tables and joins in between those tables slow down the performance of the database.

Having a large number of tables consumes more development time for implementation.

A completely normalized database needs clear and broad understanding of the business, it takes more time to analyse and understand the business. I hope this will help you. Thank you.