Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL,
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id)
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id)
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;


Enable SQL server agent service by T-SQL script or PowerShell

It will depend on what version of PowerShell you are running, but this command can be used from at least 3.0 and up:
Set-Service SQLSERVERAGENT -StartupType Disabled

The alternative would be to use WMI, or you can use SQL Server WMI (microsoft.sqlserver.management.smo.wmi namespace).
In T-SQL there is no mechanism native that lets you interact with the services. Outside of just using xp_cmdshell and calling PowerShell or cmd commands.


Cross-cutting I18N native support in databases

With PostgreSQL you can simulate something like using views and environment variables.

create table books_ids(
  book_id serial not null primary key,
  author text not null);

create table books_titles (
  book_id int not null references books_ids,
  lang char(2) not null,
  title text not null,
  isbn text null,
  primary key(book_id, lang));

insert into books_ids (author) VALUES ('J.R.R. Tolkien');
insert into books_titles (book_id, lang, title)
  values (1, 'en', 'The Lord of The Rings'), (1, 'it', 'Il Signore degli Anelli');

create view books as
  select b.book_id as id, t.title, b.author
  from books_ids b
  join books_titles t on (t.book_id = b.book_id and t.lang = current_setting('my.lang'));

-- in English
set my.lang='en'; select * from books;

-- in Italian
set my.lang='it'; select * from books;


SELECT with where between not working as expected

A shorter answer:
... WHERE MID(server, 2) BETWEEN 89 AND 110

Or, if you need to limit to the "C" set of servers:
... WHERE MID(server, 2) BETWEEN 89 AND 110
      AND server LIKE "C%"

Or (to avoid trouble with "24H1"),
... WHERE IF(server LIKE "C%",
             MID(server, 2) BETWEEN 89 AND 110,
             FALSE)

Oh, now I see another 'bad' server name (INT1).  Face it, you need to put some of the burden on the DBA.  He must realize that the practical way is to say
... WHERE server BETWEEN 'C89'  AND 'C99'
       OR server BETWEEN 'C100' AND 'C110'

That is, break up the filtering into as many ranges as needed so that string comparisons will work, and do not think numeric.


procedure is deleting data instead of inserting

There are several problems here:

No explicit BEGIN TRAN / COMMIT / ROLLBACK around the INSERT and DELETE to group them as an atomic operation.
No TRY / CATCH structure to manage COMMIT vs ROLLBACK.
Why are you using UNIX time instead of actual DATETIME values?
Why no WHERE condition on the SELECT that feeds the INSERT?
But most directly related to the stated question: the entire DayTempTable table gets deleted because the WHERE condition of the DELETE statement is a simple boolean expression and does not reference any field of that table. Meaning, if (@datenow-@date1)>=86400 evaluates to true, then all rows will be deleted. Else, no rows will be deleted. If you want a subset of rows to be deleted, you need to actually filter the table ;-).
An alternate approach to the INSERT then DELETE within an explicit Transaction is to handle both operations at the same time via the OUTPUT clause:

DELETE tbl
FROM   DayTempTable tbl
OUTPUT DELETED.problem, DELETED.channel_number, DELETED.username, DELETED.app_id,
       DELETED.Is_On, DELETED.logtype, DELETED.viewing_channel, DELETED.playtime,
       DELETED.macaddress, DELETED.wifimacaddress, DELETED.seen, DELETED.version,
       DELETED.date
INTO   FinalLogs (problem, channel_number, username, app_id, Is_On, logtype,
                  viewing_channel, playtime, macaddress, wifimacaddress, seen,
                  version, date)
WHERE  (@datenow - tbl.[date_field]) >= 86400;

And then you don't need the explicit Transaction (i.e. BEGIN TRAN / COMMIT / ROLLBACK) since it is handled automatically. But you should still do the TRY / CATCH.

Even better would be to change the WHERE condition to be a simple, sargable expression:

WHERE  tbl.[date_field] <= (@datenow - 86400);

Also, I wouldn't use the term "temporary table" to refer to the DayTempTable table since it is not, in fact, a temporary table (which is denoted by having either a single or double pound sign # as the leading character(s) of the table name).


FBI on function with concatenated string

Are the columns nullable ? is the query a LIKE ? Is there an NLS issue ?

I'd expect a

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") = :bind

to use a index range scan

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") LIKE :bind

may use a index fast full scan or a table scan depending on whether columns from the table were likely to be required. If it thinks 1 in 5 rows will match and that for each of those it needs a column not in the index, then it would be slower to use the index+table lookup than a straight table scan.

It could be the table is very small and it isn't worth using the index.


Retrieve game details that their level's value is higher than 1/10 the sum of guilds levels

Assuming the Guild.gName = Game.Name, I think you are looking for something like this:

select
    ga.Name
  , ga.MaxPlayer
  , ga.levels
  , ga.uName
  , gu.GuildsLevels
from Game ga
  inner join (
    select gName, GuildsLevels=sum(level)
      from Guilds
      group by gName
      ) as gu on ga.Name   = gu.gName
             and ga.levels>=(gu.guildslevels*0.10)


Running MySQL Backups to network location

The problem was the missing double hyphen in front of the databases switch. The line in question should have read like this:

mysqldump -u[user] -p[password] --databases [db1] [db2] [db3]> \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30\vmmysqldevw7.sql


Because they have the same GROUP BY you can just add the other aggregate function to the select list, like this..

SELECT
  proveedors.nombre as proveedor,
  COUNT(productos.id) as distintos,
  COUNT(CASE WHEN productos.stock > '0' THEN productos.id END) as instock
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
GROUP BY proveedors.id;

Or you can use the new FILTER clause if your rdbms supports it. PostgreSQL does.

SELECT
  proveedors.nombre as proveedor,
  COUNT(productos.id) as distintos,
  COUNT(productos.id) FILTER (WHERE productos.stock > '0') as instock
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
GROUP BY proveedors.id;

You should probably not be single-quoting the 0, but I have no idea what rdbms you're using and what's going on there.


Insert file content (with white line and special characters ) in bash variable for insert in oracle table with sqlplus

SQL-Plus doesn't support hash as a comment character.  You can use these to make comments:

REM or REMARK at the beginning of a line
-- (double dashes) at the beginning of a line
or paired /* with */ for block comment, as seen in C.


How to retrieve the definition behind statistics added to tables

Between sys.stats and DBCC SHOW_STATISTICS you have all the info needed to see the statistics of your statistics. Eg.:

set nocount on;
declare @object nvarchar(256), @stat sysname;
declare crs cursor forward_only read_only static for
select quotename(object_schema_name(object_id)) +
    '.' +quotename(object_name(object_id)),
    name
    from sys.stats;

open crs;
fetch next from crs into @object, @stat;
while 0 = @@FETCH_STATUS
begin
    dbcc show_statistics(@object, @stat) with NO_INFOMSGS, STAT_HEADER
    fetch next from crs into @object, @stat;
end

close crs;
deallocate crs;

That being said, anybody who understands when the stats need to be updated already knows how to look them up, so I would venture a guess you should not be updating your stats manually.


Type conversion with default fallback value

You can't handle SQL exceptions within an SQL statement like you can from within PL/pgSQL.

You must write a custom function as you suggest, for example:

create function is_valid_date(text) returns boolean language plpgsql immutable as $$
begin
  return case when $1::date is null then false else true end;
exception when others then
  return false;
end;$$;

test:

with w as (select 'asdsad'::text dt1, '2011-12-01'::text dt2, null::text dt3)
select case when is_valid_date(dt1) then dt1::date else current_date end d1,
       case when is_valid_date(dt2) then dt2::date else current_date end d2,
       case when is_valid_date(dt3) then dt3::date else current_date end d3
from w;

     d1     |     d2     |     d3
------------+------------+------------
 2011-12-06 | 2011-12-01 | 2011-12-06


Update all data in a table without affecting ID(PK)

I am not fully clear regarding where you get the new CustomerCredit value but an SQL update should never change ID unless you explicitly tell it to.

UPDATE [table] SET CustomerCredit = 100.0 WHERE ID = [customer-id-to-update]

or setting all records to the same value, just omit the WHERE

UPDATE [table] SET CustomerCredit = 100.0


dm_db_index_operational_stats showing more blocking than extended event report

You are comparing totally different things which are not comparable.

XE trace you are capturing an event called sqlserver.blocked_process_report which is described here.

Here you are capturing all kind of blocking exceeding a fixed amount of time.  If it is less than that duration (set up by sp_configure 'blocked process threshold') it will not be captured by this event.

  The Blocked Process Report event class indicates that a task has been
  blocked for more than a specified amount of time. This event class
  does not include system tasks or tasks that are waiting on non
  deadlock-detectable resources.

Your second collection using dm_db_index_operational_stats only counting waits for row and page lock. It is also cumulative since metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache.

Details here: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql

row_lock_wait_count: Cumulative number of times the Database Engine
waited on a row lock.
page_lock_wait_count: Cumulative number of times the Database
Engine waited on a page lock.


Can I have one table be an "alias", or "symlink", to another?

As far as I know, a new Postgresql lets you have INSTEAD OF triggers on views. So having one table, one view as SELECT * FROM table1, and INSTEAD OF trigger for insert, update, delete should work for you. This approach is not gonna work in Mysql though


Get the most present elements from many fields

You can use derive table to aggregate all your column values in a single column and do ordering and counting in the derived table. Example below.

Select item0 as item, count(*) as cnt from
(SELECT item0 from finalBuild
UNION ALL
SELECT item1 from finalBuild
UNION ALL
SELECT item2 from finalBuild) as item
group by item0
order by count(*) desc;


Why is there a collation conflict between system views

sys.server_triggers is a server view, so it has the server collation. sys.triggers is a database view, so it has the database collation. You have a DB that has a collation different from the server. QED. Repro:

create database foo collate Latin1_General_CI_AI

use foo

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;


Given an arbitrary pair of origin & destination, how do I design a system that can effectively query all paths that cross the pair from a GPS dataset?

A DB with spatial capabilities built-in should be able to do that. One option would be PostGIS. Check out this link for example queries using it.


Are there any MySQL Triggers or Events that my program can wait for?

There is a simple way to find out if a table has been updated recently. Just check the table information_schema.tables. Here is the layout:

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

EXAMPLE : You have a table called mydata.mytable and you want to see if that table was updated in the last 10 minutes. Here is the simple query to tell you:

SELECT COUNT(1) UpdatedCount FROM information_schema.tables
WHERE table_schema = 'mydata'
AND table_name = 'mytable'
AND update_time > ( NOW() - INTERVAL 10 MINUTE );

It will give you either 0 (Was not updated) or 1 (Was Updated).

The update_time of the table should be updated before anyone locks it, especially if the table you are polling is MyISAM. InnoDB is another story. Given InnoDB's transactional nature, you could poll the timestamps in the table rather that information_schema.tables just in case information_schema.tables does not update as quickly as desired. Just make sure the table's timestamp column is indexed.

There is an alternative outside of MySQL but does not work for InnoDB tables configured with innodb_file_per_table disabled.

EXAMPLE : Data is installed in default datadir (var/lib/mysql) and you want to poll the table mydata.mytable.

For mydata.mytable being MyISAM

TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.MYD | awk '{print $8}'`

For mydata.mytable being InnoDB

TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.ibd | awk '{print $8}'`

Of course, you would shell script the time comparison to see if is is less than 10 minutes or whatever interval you want.

With regard to events, you will be OK if you use CREATE EVENT but make sure you do not update InnoDB tables in an event. I went through heartburn helping someone troubleshoot this in a chat room on a past question.

Give it a Try !!!


How to make SQL Server 2008 compatible to SQL Server 2000

According to the following link there is a step missing:

http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

You must first update your SQL Server 2000 by upgrading to SP4 before making a backup to restore onto SQL Server 2008.

The TechNet link refers to moving from SQL Server 2000 to SQL Server 2012, but the first step is moving from SQL Server 2000 SP4 to SQL Server 2008.

If going all the way to a current version of SQL Server you will have to make the migration in two steps:

Step 1: Make a first migration from SQL Server 2000 to SQL Server 2008 for instance. You need to be on SQL Server 2000 SP4.
Step 2: Make a second migration from SQL Server 2008 to 2012.


You can use the DATEADD function to do that:

CASE WHEN [RateCode] = 'AC'
      THEN DATEADD(MONTH,
            CASE WHEN DaysPD = 30 THEN -1
                 WHEN DaysPD = 180 THEN -6
                 WHEN DaysPD = 360 THEN -12
             END,
           [RateResetDate])
    END AS [AdjustedDate]

Alternatively, you can just pass in the days if you want:

CASE WHEN [RateCode] = 'AC'
      THEN DATEADD(DAY, -DaysPD, [RateResetDate])
    END AS [AdjustedDate]


Try turning on Read Committed Snapshot isolation (RCSI) (but be aware this will put increased pressure on your tempDB, which should ideally be on its own set of dedicated physical spindles).

There are two 'snapshot' levels available in SQL Server 2005 onwards: READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes; whereas SNAPSHOT ISOLATION performs optimistic reads and optimistic writes. Suggest you try RCSI.

Enabling Row Versioning-Based Isolation Levels

To alter this setting, you need to switch to single-user mode to ensure there are no queries in flight (which would then fail):

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE dbname
SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER DATABASE dbname
SET MULTI_USER;
GO


Why do roles and schemas have (mostly) the same name?

This has confused me in the past since they have the same names. Why is this?

I think this is simply becuase of convention, it is easier to identify the owner of the schema.

Defualt schemas dbo, INFORMATION_SCHEMA, sys schemas are belongs to dbo, INFORMATION_SCHEMA, sys users respectively and these are inherited from model database.

INFORMATION_SCHEMA and sys are used to store system objects so that we can't select to store user-defined objects. dbo is the default schema so we don't need to select it.

Documentation


SQL Server 2005/8 Query Optimisation Hints

Query tuning 101

There is no magic silver bullet to query tuning, although I can give you some hints and tips.  The first thing to do is to understand what's actually going on behind the scenes.  Get a good internals book like the third Guru's Guide book.

Poorly performing queries tend to come in two basic flavours: Transactional queries that take too long, and grinding batch jobs (or reports) that take too long.  One good sign of a query with something wrong with it is a single item in the query plan taking 99% of the time.

Transactional queries

On most occasions a poorly performing transactional query is one of a few things:

A missing index.  You can see this in the query plan - table scans of large tables on a join that should be very selective (i.e. return few rows).
Query unable to use an index.  If you have OR conditions in the where clause, joins on a calculated value or some other item in the query that is not sarg-able then you may need to re-write the query.  Briefly, sargs are query predicates that can use indexes to eliminate rows.  Logical AND, equality and inequality (>, >=, <, <= and !=) are all sarg-able.  OR is traditionally not sarg-able.  However, you can often translate OR's into sarg-able predicates by inverting the sense from OR to NOT (foo and not bar) type constructs.
Inefficient predicates.  For example, if you have a where in referencing a nested subquery see if it can be re-written as where exists or as a join.  This may result in more efficient query plans and here are other standard re-writes you can try also.  Again, the Guru's guide books and others on the subject are a good starting point.

Batch queries

Batch queries are more complicated and have different tuning issues.  Some tips are:

Indexing.  This can make a large difference for the same reason it does with transactional queries.  Often a good sign of a missing index is a long, grinding operation (99% of the query plan) that doesn't seem to be thrashing the machine.
Temporary tables.  You may find it better to break down a query into several queries populating temporary tables.  Larger queries give the optimiser more room to screw up, although this is less of an issue that it used to be.  Make the temp tables with select into as this operation is minimally logged (much less log activity), which reduces the I/O load.  Note that temporary tables in tempdb are the same data structure that the optimiser uses to store intermediate join results, so there is no performance penalty for doing this.  You can also create an index (including clustered and covering indexes) on a temp table, which may improve performance of the queries reading it for the same reasons that they improve queries on static tables.Don't overdo temp tables though, as they can make things harder to trace back through the query.  For smaller tables within a stored procedure, test to see if table variables help.  These are an in-memory data structure, so they can be a performance win.
Clustered and covering indexes.  These can improve the performance of a query as they force locality of reference on the disk based on some grouping column.  A clustered index can make a large difference to the performance of a batch job.
Inefficient predicates.  These can cause problems with sargs and other sub-optimisation isses in much the same way as they do with transactional queries.
Table scan is your friend.  Contrary to popular belief, table scans are not inherently evil.  Generally they are a sign of something wrong in a transactional query, but they are often the most efficient way to do a large batch operation.  If you are doing something with more than a few percent of rows in a table, a table scan is often the most
efficient way to cover the table.
Nested loops joins.  Take a look at what the optimiser is doing on both sides of the join.  These can be inefficient if you are (for example table scanning two large tables on both sides of a nested loops join.  Consider using clustered indexes or order by and trying to change the operation to a merge join or hinting to promote a hash join if one side is small enough to do this with.

Locking

Locking can also cause performance issues.  If your system is performing badly under load, look at the profiler and perfmon counters related to locks and check whether there is any significant contention.  sp_who2 has a 'BlkBy' column in the result set that will show if a query is blocked and what is blocking it.  Also, profiles with 'deadlock graph' events (if you have queries deadlocking) and lock related events can be useful to troubleshoot lock issues.


SQLite and Write Ahead Logging: why so large files

The WAL file records all changes to the database since the last checkpoint.

(Automatic) checkpoints cannot run when the database is locked, so ensure that there is no other connection with an active transaction.

The documentation says:

  By default, the auxiliary write ahead log and shared memory files used for transaction control are automatically deleted when the latest connection to the database closes.

So ensure that all database connections are closed at some time.

(If you have disabled deleting WAL files with SQLITE_FCNTL_PERSIST_WAL, you have to set PRAGMA journal_size_limit to a nonnegative value to truncate the WAL file to zero bytes in that situation.)


What is the best way to migrate multiple SQL Server databases?

Copy 50 to 100 databases to a completely new server with zero downtime?  I don't think that's going to happen.  When you're talking about a migration, planned downtime is an absolute must.

In this case, depending on the size of the databases, I'd take the scripting route with this.  If you are proficient with PowerShell, you can loop through all the databases and take backups to store in accessible storage for the new server.  Then on the new server, you can script out the restore of all the databases.

Don't forget about the server-level items (logins, jobs, etc.).  You can script these as well, but with this particular requirement SSIS actually makes it very easy.  There are tasks to do all of these server-level items.

If you're expecting no downtime, how are the clients going to redirect their requests to the new database server?  There are a few external solutions for this, but something to consider when you promise "zero downtime".


Apart from sp_whoisactive as mentioned in earlier answer, i can suggest running extended events for the type of query you want to track.

You can use the events and action as mentioned in the log Tracing Query Performance with Extended Events

I really like the extended events for the amount of data you can capture out and its not that expensive depending upon how much you capture.

Additionally you can use sp_blitzwho Forget sp_Who and sp_Who2:
sp_BlitzWho tells you what’s really happening.


Using SMO from PowerShell, is there a way to state that a jobstep output file should be appended and not overwritten?

I think it is more along below lines

$jobStep.JobStepFlags = "AppendToLogFile"
  #call the alter to modify
$jobStep.Alter()
  #display it
$jobStep.JobStepFlags


How can I find the cause of pg_catalog.pg_attribute constantly being vacuumed?

Try to set log_statement=ddl to double check DDL statements. Maybe application code is creating temporary tables.


Is it possible to PIVOT on a LIKE statement

SUM(CASE

For a limited number of Names you can use a SUM(CASE solution in this way:

SELECT
    'Database status' as [DB Status],
    SUM(CASE WHEN Name LIKE 'Test%' THEN 1 ELSE 0 END) As Test,
    SUM(CASE WHEN Name LIKE 'Prod%' THEN 1 ELSE 0 END) AS Prod,
    SUM(CASE WHEN Name = 'Migrated' THEN 1 ELSE 0 END) AS Migrated,
    SUM(CASE WHEN Name = 'Offline' THEN 1 ELSE 0 END) AS Offline,
    SUM(CASE WHEN Name = 'Reserved' THEN 1 ELSE 0 END) AS Reserved
FROM
    [Status];

PIVOT

If there is an extensive list of Names but only few of them must be rewritten you can maintain the PIVOT solution:

SELECT 'Database Status' AS [DB Status],
[Test], [Prod], [Migrated], [Offline], [Reserved]
FROM
(
    SELECT
        ID,
        CASE
            WHEN Name LIKE 'Test%' THEN 'Test'
            WHEN Name LIKE 'Prod%' THEN 'Prod'
            ELSE Name
        END AS Name
    FROM
        [Status]
) AS Source
PIVOT
(
    COUNT(ID) FOR Name IN ([Test], [Prod], [Migrated], [Offline], [Reserved])
) AS PivotTable;

db<>fiddle here

DYNAMIC QUERY

If you feel a bit lazy and don't want to write all column names, you can use a dynamic query:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(CASE WHEN Name LIKE 'Test%' THEN 'Test'
                                                    WHEN Name LIKE 'Prod%' THEN 'Prod'
                                                    ELSE Name END)
                   FROM [Status]
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =
'SELECT ''Database Status'' AS [DB Status],' + @cols + ' FROM
    (SELECT
        ID,
        CASE
            WHEN Name LIKE ''Test%'' THEN ''Test''
            WHEN Name LIKE ''Prod%'' THEN ''Prod''
            ELSE Name
        END AS Name
    FROM
        [Status]
) AS Source
PIVOT
(
    COUNT(ID) FOR Name IN (' + @cols + ')
) PVT'

EXEC(@cmd);

db<>fiddle here


Troubles connecting to a SQL Server Express instance on AWS

SQL Server Express does not enable a TCP/IP listener by default. You must enable it using SQL Server Configuration Manager to allow remote connections.


Passively load a monitoring shared object into an Oracle process

Using LD_PRELOAD to implement a hidden trojan in an oracle database
You can also follow the links there. Like this one: https://yurichev.com/writings/oracle.pdf


SqlDataAdapter vs SqlDataReader for DBAs

In my experience, I think of SqlDataAdapter sort of like a .net SELECT * INTO #TEMP except the #TEMP is on the client computer.  The entire result set is read from SQL Server and that connection is no longer needed.  From a SQL Server perspective, the query comes in, runs and gets out.  From a client perspective, the entire result set is loaded into memory on the client computer. This can be extremely problematic when reading large amounts of data.

On the other hand, I think of SqlDataReader as a cursor mechanism whereby a SQL Server connection is opened and the SqlDataReader serves up one row at a time.  The application might do a bunch of work between each read and that can cause the connection to SQL Server to stay open for an extended amount of time.  This technique can allow the application to process a large result set without having to cache the entire set of data on the client computer.


After fresh install getting `psql: command not found`

According to the README included in the dmg file, postgres binaries are installed in /usr/local/pgsql/bin/. You should add these lines in your ~/.bashrc :

export PSQL_DIR=/usr/local/pgsql/bin/
export PATH="$PSQL_DIR:$PATH"

Update your environment with source ~/.bashrc or run a new shell and psql should be available.


It looks like an on disk index corruption. If you can afford to have the table locked for a while you can solve it by running:

REINDEX INDEX "myIndexName";

The command will rebuild the index getting rid of the error.

If you cannot have the table locked for the time needed by the REINDEX command, you can concurrently create an additional index with the same definition. If the index is still usable,  you can drop it after the CREATE INDEX CONCURRENTLY command finished and the index become valid, otherwise you can drop it before to start.

You can find more information here:

http://www.postgresql.org/docs/9.3/static/sql-reindex.html

http://www.postgresql.org/docs/9.3/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY


updating n number of records mysql database

Write a Stored Procedure that 'constructs' the query, specifically by putting a computed value after LIMIT.


Get id, distinct idLocal of the MIN(datetime) in MySQL

Make your query a subquery.

Take the result of the subquery and find minimum id grouped by idLocal,time ordered by id

PROPOSED QUERY

SELECT MIN(id) as id,idLocal,time
FROM (SELECT P.id,fP.idLocal,P.time
FROM Products P JOIN
(SELECT idLocal,MIN(time) as time FROM Products GROUP BY idLocal) fP
ON P.time = fP.time AND P.idLocal = fP.idLocal) A
GROUP BY idLocal,time
ORDER BY id;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS carlos;
CREATE DATABASE carlos;
USE carlos
CREATE TABLE Products
(
    id      INT NOT NULL AUTO_INCREMENT,
    idLocal INT NOT NULL,
    time    DATETIME NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO Products (idLocal,time) VALUES
(1,'2016-12-15 19:18:00'),(1,'2016-12-15 19:18:00'),
(2,'2016-12-15 20:30:00'),(3,'2016-12-16 20:30:00'),
(2,'2016-12-15 12:15:00'),(4,'2016-12-15 12:15:00');
SELECT * FROM Products;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS carlos;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE carlos;
Query OK, 1 row affected (0.00 sec)

mysql> USE carlos
Database changed
mysql> CREATE TABLE Products
    -> (
    ->     id      INT NOT NULL AUTO_INCREMENT,
    ->     idLocal INT NOT NULL,
    ->     time    DATETIME NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Products (idLocal,time) VALUES
    -> (1,'2016-12-15 19:18:00'),(1,'2016-12-15 19:18:00'),
    -> (2,'2016-12-15 20:30:00'),(3,'2016-12-16 20:30:00'),
    -> (2,'2016-12-15 12:15:00'),(4,'2016-12-15 12:15:00');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Products;
+----+---------+---------------------+
| id | idLocal | time                |
+----+---------+---------------------+
|  1 |       1 | 2016-12-15 19:18:00 |
|  2 |       1 | 2016-12-15 19:18:00 |
|  3 |       2 | 2016-12-15 20:30:00 |
|  4 |       3 | 2016-12-16 20:30:00 |
|  5 |       2 | 2016-12-15 12:15:00 |
|  6 |       4 | 2016-12-15 12:15:00 |
+----+---------+---------------------+
6 rows in set (0.01 sec)

PROPOSED QUERY EXECUTED

mysql> SELECT MIN(id) as id,idLocal,time
    -> FROM (SELECT P.id,fP.idLocal,P.time
    -> FROM Products P JOIN
    -> (SELECT idLocal,MIN(time) as time FROM Products GROUP BY idLocal) fP
    -> ON P.time = fP.time AND P.idLocal = fP.idLocal) A
    -> GROUP BY idLocal,time
    -> ORDER BY id;
+------+---------+---------------------+
| id   | idLocal | time                |
+------+---------+---------------------+
|    1 |       1 | 2016-12-15 19:18:00 |
|    4 |       3 | 2016-12-16 20:30:00 |
|    5 |       2 | 2016-12-15 12:15:00 |
|    6 |       4 | 2016-12-15 12:15:00 |
+------+---------+---------------------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Publishing stored proc execution in transactional replication

how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being updated on the publisher (i.e. actually replicating 25 million update statement)?

SQL Server knows that when you set up article property as below :

You can explicitly set only to replicate SP defination, execution of SP or Execution in a serialized transaction of the SP while setting up Article property.

Refer here. When you select "serialized transaction of the SP", the entire execution is replicated as a single transaction there by reducing the Transactional Replication overhead i.e. With the replication of stored procedure execution, replication sends only the command to execute the stored procedure at the Subscriber, rather than writing all the updates to the distribution database and then sending them over the network to the Subscriber

Test it before hand as performing such a large update (25 million rows) will be way too much for replication to handle (depending on your replication topology - same distribution server as publisher, geographically separate publisher and subscribers, etc) and some caveats as described in BOL.

You can probably thing of another approach like --

Remove the current table from replication.
Do you data update and (To speed up things, you can truncate the table on Subscriber if the table is massive - so when you do a snapshot, replication wont have to perform delete on subscriber table)
Add the table back to replication with nosync option
Snapshot the publication, but this time only the snapshot of the table that was added will be generated.


How to teach the optimizer to use index instead of fts with join on data logging master/detail tables?

Bad plans come from hard choices.  Instead of making the optimizer choose between a plan with two nested loops joins and a plan with a big parallel hash join, you could reorganize B to optimize for the access path from  A to B.

The best index here is probably to make the clustered PK of B (akey,id). Then there would be only one nested loops join in the already-faster plan, making it obviously better than the parallel hash join plan.


what are the conditions under which mysql replication might break?

Given that MySQL Replication is dual-thread, it is importatnt to recognize how Replication looks when it is broken. There are four main topics is this area

SQL Thread Dies

The SQL Thread is responsible for

Getting the Next SQL Statement fromt the Relay Logs
Executing the SQL Statement
Rotating Relay Logs by Deleting any Relay Log that had all its SQL Entries Executed

If any SQL error happens, the SQL Thread simply dies and the following is posted to its Slave Status:

Error Number
Error Message
SQL statement that experienced the Error
Current database
Master Log File where the SQL Originated
Master Log Position where the SQL Originated

This gives an opportunity to troubleshoot, skip the error, run the SQL statement by hand, start replication back up. Sometimes it may be a SQL-based error, such as error 1062 (Duplicate Key). Other times, it may be related to the Storage Engine or the OS.

To figure out if an SQL statement will break replication, you should take any DML (INSERT, UPDATE, or DELETE) and make a corresponding SELECT using the WHERE clause of the DML. Then, run that SELECT to see if the data you are about to manipulate really exists or not.

I/O Thread Dies

The I/O Thread is responsible for four(4) things:

Downloading SQL from the Binary Log Entries of a Master
Recording SQL into its Local Relay Logs as a FIFO queue
Acknowledging Communication Failure
Attempting the Reestablish of I/O Thread

Any network latency may cause the I/O Thread to simply die and retry connection. Once a while under those circumstances, the Slave's viewpoint of the Master's log file and position (as logged in its relay logs) may be out-of-sync with what Master actually recorded in its binary logs.

Other side effects may include corrupt relay log entries

caused by bad network transmission, which can be corrected by running CHANGE MASTER TO from the last SQL statement from the Master that the Slave executed.
caused by corrupt binary log entries on the Master which was successfully transmitted to the relay logs, which can be corrected by

RESET MASTER; on the Master to Zap all binary logs
setting up replication from the new current binary log
using pt-table-sync to correct differences

Temporary Table Usage

Troubleshooting this is like playing "pin the tail on donkey". Most developers are unaware of this until it happens and you try to fix it not realizing where the cause of this began. Here is the scenation: If you use CREATE TEMPORARY TABLE on a Master, it will replicate to the Slave. During the time the table is in use, it will be kept in existence in the SQL Thread. If you issue STOP SLAVE;, the SQL Thread is voluntarily killed along with all temporary tables the SQL Thread was holding. You do not realize that this has occurred until you issue START SLAVE; and the SQL Threads dies again because the needed temp table no longer exists.

To fix this, you have perform surgery on the master's binary logs and replication as follows:

Step 01) Locate the exact log file and position the CREATE TEMPORARY TABLE was issued on the Master
Step 02) Locate the name of the database that the CREATE TEMPORARY TABLE was meant for
Create the table using CREATE TABLE instead of CREATE TEMPORARY TABLE
Step 03) Run CHANGE MASTER TO using the file and position from Step 01
Step 04) Run START SLAVE; until Replication catches up or another table's nonexistence (due to CREATE TEMPORARY TABLE) breaks replication for this same issue
Step 05) If replication breaks again because of CREATE TEMPORARY TABLE on a different table, go back to Step 01

Network Inconsiderations

Once upon a time, there was a tendency for MySQL to say Replication was running when, in fact, it was not. This can happen when the network has intermittency that may delay data transmission of binary logs but not severe enough to timeout the I/O Thread. Since the MySQL process can be inconsiderate by being a little insensitive to the network, I affectionately call this "Network Inconsideration". While the bug report on this is closed, it is good to have multiple ways to check MySQL Replication as to its ability to run, especially the I/O Thread. Using MySQL 5.5, you could adjust the sensitivity of the I/O Thread using the the heartbeat and timeout parameters centered around Semisynchronous Replication.


Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows

Sometimes the person writing T-SQL has more accurate information than the query optimizer. In all of your query plans, SQL Server has a final cardinality estimate of 5360890000 rows, which is how many rows are in the dbo.SE_Pages table. Only a single row is ever returned though, and in the comments you said:

  most of the time it['s result set] is only a selection of a few thousand

Something is going very wrong with cardinality estimation. From the optimizer's point of view, it's giving you the best plan that it can. It thinks that all plan options are expensive because billions of rows need to be returned. When returning all rows, in general, a clustered index scan will be costed much cheaper than using an NCI + key lookups to return every single row. The bad cardinality estimate results in a query plan that does the scan which takes days. The key to getting better performance without index hints is to fix the cardinality estimate.

You said that this problem seemed to start happening after you upgraded from SQL Server 2016 to SQL Server 2017. Did you gather statistics after the upgrade? The two most likely causes are that there's something wrong with the statistics for dbo.SE_Pages or Microsoft made a change in SQL Server 2017 that's harmful to your workload.

You can test if statistics are the cause by updating them for that table. You can test if SQL Server 2017 is the problem by changing the compatibility level of the relevant database to 130, although this could have other effects on your application. If you need another temporary workaround that doesn't involve an index try FAST 1. That hint encourages the optimizer to prefer plans which return the first row as quickly as possible. In your case, it's a way to disfavor plans that do a scan. If you need additional help you'll need to add more information to your question. The histogram for UriKey and the table structure (along with an explanation as to why you're partitioning by UriKey) are likely to be helpful.


Are GIST Index point geometry useful to speed up spatial query?

A GiST index on points can be very useful. It will speed up queries with st_contains, st_overlaps and similar functions in the WHERE condition, where one of the arguments is the indexed column.


Adding a varchar to a datetime column and dealing with nulls

You can use ISNULL or COALESCE in T-SQL to replace a null value:

SELECT
  Trand_Date + ' ' + COALESCE(Trans_Time, '00:00:00') AS Trand_DateTime1,
  Trand_Date + ' ' + ISNULL(Trans_Time, '00:00:00') AS Trand_DateTime2
FROM MyTable

Both columns above should return the same value. Note that if you want to perform date-based comparisons, e.g. DATEDIFF, DATEADD or even something as simple as WHERE Trand_DateTime < GETDATE(), you can create a view of the table that presents the values as an actual SQL DateTime datatype like so:

CREATE VIEW [dbo].[ViewTransDateTimes]
AS
SELECT
  CAST(Trand_Date + 'T' + COALESCE(Trans_Time, '00:00:00') as DateTime) AS Trand_DateTime
  --, [OTHER COLUMNS]
FROM MyTable
GO

Then you can do things like:

SELECT * FROM ViewTransDateTimes
WHERE DATEDIFF(day, Trand_DateTime, GETDATE()) < 1000


Converting to date and performing a count

Don't know about big query but for SQL Server you could do it like this.

select S.UserID,
       min(cast(S.SessionLogin as date)) as SignUpDate,
       datediff(day, min(cast(S.SessionLogin as date)), getdate()) as DaysActive,
       count(distinct cast(S.SessionLogin as date)) as DaysActive
from dbo.SessionLogin as S
group by S.UserID;


Random thoughts

The statistics etc weren't disable on rebuild into new filegroup?
Maintenance tasks do not look at the filegroup?
Heavily fragmented disk?
Try MAXDOP 1
Exactly same fillfactor etc?

The IO looks way off so I wonder if stats or fragmentation is causing an issue


Welcome to the world of Database Administration... and good luck. You're going to want to read up on as much Oracle documentation as you can, as well as other good technical sources (O'Reilly has always been good), and subscribe to lots of Oracle blogs.

I'll answer your questions here, but you're really going to want to get a solid foundation in RMAN ( For 10gR2: http://www.oracle.com/pls/db102/portal.portal_db?selected=4 ).

1.Do you have to shutdown your Oracle DB when you want to make a copy/clone of it?

Yes and no. It depends on if your database is in archivelog mode. If the database is archiving its logs, the backup can be done while your database is online, though you may notice some performance degradation during the backup, so it is still a good idea to schedule the backup during a non-critical time of day. If the database is not archiving the logs, then you must shutdown the database cleanly in order to make a copy of it (any other way will result in a corrupt/incomplete restore).

2.A good beginner example

This is hard to do without knowing your environment. There are various RMAN commands that will happily clone a database on the same server, but when you get into moving a clone from one server to another, you have to go a different route. So without knowing your environment, I can't really tell you a good example.

That said, essentially your goal is to do the following:
 - Get a good copy of all your datafiles
 - Get a good copy of all your archive logs
 - Get a good copy of your database parameters and control file
 - Create a new database with the settings from your old database (or reuse an existing database)
 - Copy over your datafiles and archive logs
 - Restore from your controlfile, and recover until there are no archives to process.

RMAN does a lot of the work for you with some fairly simple commands, so I suggest learning how to use it effectively, but you can always do the hard work without it. (For a long time at  a previous workplace, we did this with some shell scripts and such. Fun it was not, and was easy to screw up.)

3.Does a GUI exist for cloning an Oracle Database?

I think the thing that comes closest would be Oracle Enterprise Manager (GRID). It offers several automated backup/restore options as well as cloning features. Keep in mind, however, that it uses RMAN under-the-hood, and you should never use a tool blindly without understanding what's going on underneath. Sooner or later, you'll need to delve down into the command line because the GUI won't do what you want it to do, and without a good understanding, you'll be stuck.


How to import CSV file in SQL server 2008?

SQL Server has always supported bulk inserting from CSV files, you just have to specify field/row terminators.

file.csv contains:

foo,bar,1
blat,splunge,2

Then we do this:

CREATE TABLE #foo(a varchar(32), b varchar(32), c int);

BULK INSERT #foo FROM 'c:\temp\file.csv'
WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = ',');

SELECT * FROM #foo;

Results:

a        b        c
-------- -------- ----
foo      bar      1
blat     splunge  2


Is fragmention limited to indexes in sql server?

While this probably fits better on dba stackexchange, it's kind of a grey area/borderline.

The short answer is that indexes get fragmented and need to be periodically checked and reorganized or rebuilt based on how much they have fragmented.

Going at it we find the fragmentation levels primarily through querying:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')

However this query also returns the index_types. Where we see the expected clustered and non clustered indexes. But also the 'HEAP' Index type, which, isn't actually an index (the object_ID also doesn't have any kind of name in the sys.indexes view).

These are for tables with no indexes at all, and thus also no primary keys (which automatically creates an index). These too can get fragmented. Because they represent simply how spread out the data is across the disk.

You should also check and 'amend' these, either simply by actually giving them say a primary key. Or, if it's desired that they have no indexes/PK. Simply by rebuilding the table. This reorganizes the data within the table as well.

A quick example of code:

ALTER INDEX [PK_Foo] ON [dbo].[Foo] REORGANIZE --Generally used on low fragmentation count
ALTER INDEX [PK_Foo] ON [dbo].[Foo] REBUILD --Used for high fragmentation count
ALTER TABLE [dbo].[Bar] REBUILD --Used for HEAP indexes with medium-high fragmentation

There are many options that these statements can have, like padding factor. One thing to note is that REORGANIZE allows the table to stay online and other than using resources don't block users. The other options do lock users from accessing the table during the process.


Is it good practice to create new tables for new elements in a database?

No. This will cause maintenance problems: when you want to update the structure of Garden, then changes (adding a new column, removing an old column, changes to constraints, changes to triggers, whatever) will have to be propagated to every Garden_* table. It is also bad data modelling. You should aim to have one table per entity/relation in your system. Having multiple Garden tables would imply that they are fundamentally different.

I'd suggest a structure more like this:

Gardener
--------
  ID (PK)
  Garden_ID (FK to Garden.ID)
  (other fields such as name, contact info, ...)

Garden
------
  ID (PK)
  (other fields such as name, garden address, ...)

Flower
------
  ID (PK)
  Type_ID (FK to FlowerTypes.ID)
  Garden_ID (FK to Garden.ID)
  (other fields)

FlowerType
----------
  ID (PK)
  name
  (other fields)

The data for this schema could look like this:

Gardner
-------
ID | Name  | Garden_ID
-----------|----------
1  | Bob   | 100
2  | Sally | 101

Garden
------
ID | Name
---------
100| Big garden
101| Little garden

Flower
------
ID  |  Type_ID  | Garden_ID
----|-----------|----------
1   |  200      |  100
2   |  201      |  100
3   |  202      |  101

Flower_type
-----------
ID   | Name
-----------
200  | Lilly
201  | Hosta
202  | Rose

This will let a gardener be assigned to one garden. If you need more flexibility such that gardeners can be assigned to multiple gardens, and gardens can have multiple gardeners assigned to them, you'd need a separate garden assignment table, and remove the garden_id field from gardener:

garden_assignments
------------------
  ID (PK)
  garden_ID (FK to garden.ID)
  gardener_ID (FK to gardener.ID)


Distribution database compatibility level

It seems that compatibility level can have an affect on the publishing and subscribing databases, with respect to object ownership and the existence (or not) of a particular schema. If you aren't seeing that, it's probably not a problem for you.

As far as the distribution database goes, there doesn't seem to be any effect. I would leave the compatibility level of any of the SQL Server system databases (master, msdb, tempdb, the distribution database and even the model database) to whatever MS set it to unless Microsoft or a third-party vendor tells me otherwise, gives me a reason and says that I'll be unsupported if I don't change it.


SQL Server 2005 not collecting logins in the log file

Per SQL Server Books Online...

  Changing the audit level requires restarting the service.

Try restarting your SQL Service and then check to see if logins are being properly audited in your Error Log.


DBTIMEZONE is different than host machine timezone, and it needs to be set up separately (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions039.htm)  .

The first step is to change DBTIMEZONE to Moscow so it matches server settings which always makes sense.   As a side effect, you will have your current issues "solved" because now db timezone also matches application server timezone .

However, I'd not recommend to stop at this point unless you can always run Oracle and java application[s] with Moscow timezone settings.  It may be feasible in some cases, but it's not very robust solution.  It easily turns to nightmare when you have to deal with switching to daylight saving time .

I'd rather change application to always store datetime in UTC , and perform all necessary conversion on application side before displaying date to the end user.
Another option you can try is replacing DATE columns with one of Oracle timezone aware types (TIMESTAMP WITH LOCAL TIMEZONE or TIMESTAMP WITH TIMEZONE).


SQL Server Report Manager 404 using HTTPS

The problem has to do with the certifcate being used with SSRS. If your certificate is created with hostname.whatever.com, you can only access the Report URLs through that path, https: //hostname.whatever.com/Reports. If you were to try https:// 10.xxx.xxx.xxx/Reports (where the IP is the one on the server) you will receive the 404 errors.

So the fix for my situation:
I found the friendly name used to generate the certifcate on the server was dorked up. I am in the process of getting that fixed to be the correct fully qualifed DNS name of the server in use. I found this out by installing SQL Server Report Server on a server that had the certificate created with the correct name of the server being used.

Once I have that certificate corrected and verify will simply mark this as the correct answer.

EDIT
Well another problem came up in trying to change the certificate. The admins of the server removed the certificate before I could properly remove it from RSCM. So now it will not bind to the new certificate because it already sees one binded. There is really no clean, easy way of fixing this since you don't have access to IIS (SSRS uses HTTP.sys). So following this KB, you have to go into NETSH to remove the binding(s). Which you will note in that KB article the link to NETSH does not provide much info, so use this one.
I had to use the two following commands to delete the bindings on Window Server 2008 R2:
WARNING: I am on a single, standalone server. If you are in a server farm or have multiple instances of SSRS running, be very careful with which SSL binding you delete.

This is after getting to the "netsh http>" prompt:
delete sslcert ipport=0.0.0.0.:443
delete sslcert ipport=[::]:443

I did that and then went into RSCM and selected the new certificate that was installed on teh server. That is not the end though, for some reason it kept removing the binding and showing "unknown" in the link. I stopped SSRS and then opened up the rsreportserver.config file and found the old ("bad") binding still was showing in the config file. I removed that went back into RSCM and started the service and it seemed to fix it.


galera cluster with mariadb 10.3.13 is not replicating on RHEL 8.1

Ok, I know what happened. What I did:

1) vi /etc/selinux/config

2) Change state to from enforcing to disabled

3) Apply init 6 (restart server)


why does my clustered index scan have ordered = false

It just means that the query optimiser doesn't require an explicit order guarantee either for some later operator in the plan (e.g. merge join or stream aggregate) or to avoid a sort because you have explicitly requested an order by.

When ordered = false you might in some circumstances get an allocation ordered scan rather than a scan that follows the linked list of the leaf pages in index key order for example.


What does "_localserver_view" mean after an MSDB Database table

I'm not sure I understood your question but I'll try to explain the differences.

When you have full permissions in msdb
you'll get the same rows when

executing msdb.dbo.sp_help_schedule
selecting from msdb.dbo.sysschedules_localserver_view
selecting from msdb.dbo.sysschedules

This is because msdb.dbo.sp_help_schedule selects from msdb.dbo.sysschedules_localserver_view which selects from msdb.dbo.sysschedules.

You'll get less rows when select from msdb.dbo.sysjobschedules mentioned in your question, this is because msdb.dbo.sysjobschedules

  Contains schedule information for jobs to be executed by SQL Server
  Agent.

While msdb.dbo.sysschedules shows also schedules currently not related to any existing job, for example is contains schedules for Data Collector that may not be configured.

When the user has only select permission in msdb

he will not be able to execute msdb.dbo.sp_help_schedule, he'll get the full result when selecting from tables msdb.dbo.sysschedules and msdb.dbo.sysjobschedules, but he'll get no rows when selecting from the msdb.dbo.sysschedules_localserver_view view.

This is because this view contains the where clause:

WHERE (svr.master_server = 0)
  AND ( (sched.owner_sid = SUSER_SID())
        OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
      OR (ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0) = 1)
      )

This means that to get any row from this view one of the following should be true for the current user:

user is owner of the job (and only the schedules related to owned jobs will
be returned)
corresponding login is a member of sysadmin fixed server role (all rows will be returned)
user is a member of SQLAgentReaderRole database role (all rows will be
returned)


Using the auto increment id from one query as the fk of the next insert

This is (in part) where transactions come in.
Assuming your transaction isolation level is "read committed", "repeatable read" or "serializable" (MySQL defaults to "Repeatable read" and anyone setting it to lower than "read committed" is committing crimes against the laws of the universe), other transaction are blind (to some extent) to changes made within your transaction.

So you should:

START TRANSACTION;
INSERT #1;
INSERT #2;
COMMIT;

The other transactions will not be able to read your first INSERTed row (though they can find trace of its existence via locks -- probably not interesting to you). They will only be able to read both your changes or none of them.

This, again, depends on the assumption that these transactions don't come from a connection with "Read committed" isolation level -- which, unfortunately -- is something anyone is able to change for their own connection.


How do I maintain high INSERT-performance on PostgreSQL

There are a few things that might be causing this issue, but I can't be sure any of them are the real problem.  The troubleshooting all involves turning on extra logging in the database, then seeing if the slow parts line up with messages there.  Make sure you put a timestamp in the log_line_prefix setting to have useful logs to look at.  See my tuning intro to get started here:  https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Postgres does all of its writes to the operating system cache, then later they head to disk.  You can watch that by turning on log_checkpoints and reading the messages.  When things slow down, it may simply be that all the caches are now full, and all writes are stuck waiting for the slowest part of I/O.  You might improve this by changing the Postgres checkpoint settings.

There's an internal issue with the database people hit sometimes where heavy inserts get stuck waiting for a lock in the database.  Turn on log_lock_waits to see if you're hitting that one.

Sometimes the rate you can do burst inserts at is higher than you can sustain once the system autovacuum process kicks in.  Turn on log_autovacuum to see if the problems are concurrent with when it's happening.

We know that large amount of memory in the database's private shared_buffers cache doesn't work as well on Windows as it does on other operating systems.  There isn't as much visibility into what goes wrong when it happens either.  I would not try to host something that's doing 1000+ inserts a second to a Windows PostgreSQL database.  It's just not a good platform for really heavy writes yet.


Your dynamic SQL has an error.  Try this:

IF COALESCE(OBJECT_ID('dbo.FailingDatabase'), 0) = 0
Create Table [dbo].[FailingDatabase]
(
    DateFailed datetime
    , DatabaseName varchar(1000)
);
TRUNCATE TABLE dbo.FailingDatabase;
Insert INTO [dbo].[FailingDatabase] (DateFailed, DatabaseName)
VALUES ('2016-03-18 08:29:00.000', 'server.database1.dbo.tablename')
    , ('2016-03-18 08:35:00.000', 'server.database2.dbo.tablename');

DECLARE @fullsyntax VARCHAR(MAX);
SET @fullsyntax = '';
;WITH faileddb
AS
(
    SELECT TOP (2) databasename --why you want TOP(2) is unclear
    FROM dbo.FailingDatabase
)
SELECT @fullsyntax = @fullsyntax
    + CASE WHEN @fullsyntax = ''
        THEN ''
        ELSE CHAR(13) + CHAR(10)
        END
    + 'SELECT datefaield, database FROM ' + [databasename] + ';'
FROM faileddb
WHERE faileddb.DatabaseName NOT IN (
    'master'
    , 'tempdb'
    );
PRINT (@fullsyntax);


Oracle Enterprise Manage "CPU utilization" is confusing

That figure is the share of the overall CPU utilization for each process.

In your case, it means that that specific oracle process is responsible for 100% of your current CPU utilization - i.e., you have no other applications that are eating CPU. So 100% of your ~1-core load is generated by that specific Oracle process.

There is an example in the Monitoring Real-Time Database Performance, and they state it like this:

  In this example, the database is consuming 92 percent of CPU utilization.

So the figure in the top graph shows how busy your cores are, and the figure in the table tells you what part of that load is generated by what process.


How to quota a CDB only tablespace to a CDB user?

All containers have a default tablespace:
SQL> select p1.name, p2.property_name, p2.property_value
from v$containers p1 join cdb_properties p2 on (p1.con_id = p2.con_id)
where property_name = 'DEFAULT_PERMANENT_TABLESPACE' order by 1;

NAME       PROPERTY_NAME                  PROPERTY_VALUE
---------- ------------------------------ --------------------
CDB$ROOT   DEFAULT_PERMANENT_TABLESPACE   USERS
PDB1       DEFAULT_PERMANENT_TABLESPACE   USERS
PDB2       DEFAULT_PERMANENT_TABLESPACE   SALES

Create the user without specifying anything:
SQL> create user c##myadmin identified by password;

User created.

SQL> select p1.name, u.default_tablespace
from v$containers p1 join cdb_users u on (p1.con_id = u.con_id)
where username = 'C##MYADMIN' order by 1;

NAME       DEFAULT_TABLESPACE
---------- ------------------------------
CDB$ROOT   USERS
PDB1       USERS
PDB2       SALES

Then alter the user in the root container:
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter user c##myadmin default tablespace admints quota unlimited on admints container=current;

User altered.

SQL> select p1.name, u.default_tablespace
from v$containers p1 join cdb_users u on (p1.con_id = u.con_id)
where username = 'C##MYADMIN' order by 1;

NAME       DEFAULT_TABLESPACE
---------- ------------------------------
CDB$ROOT   ADMINTS
PDB1       USERS
PDB2       SALES

SQL> select c.name, q.tablespace_name, q.max_bytes
from v$containers c join cdb_ts_quotas q on (c.con_id = q.con_id)
where q.username = 'C##MYADMIN';

NAME       TABLESPACE_NAME                 MAX_BYTES
---------- ------------------------------ ----------
CDB$ROOT   ADMINTS                                -1


SQL SERVER auditing SELECT of a single column

Not knowing your systems or the load on them, I cannot stress enough the need to test this suggested solution, very thoroughly. However, that said, you can do this, partly, through Extended Events. Here's a sample script:

    CREATE EVENT SESSION [ColumnAudit]
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'HamShackRadio')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%RadioName%')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%SELECT%')
           )
    ),
    ADD EVENT sqlserver.sql_statement_completed
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'HamShackRadio')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%RadioName%')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%SELECT%')
           )
    );

A few notes on this.

First, I would strongly recommend that you put good filtering in place. I've filtered first on the database name in order to only capture this from the one database. Second, I've ensured that I can filter the statement for the columnname ahead of anything else to be sure I'm only looking at those statement. Then I filter on SELECT.

Second, lest you balk at all to use Extended Events, remember that Audit is just using them behind the scenes, so this is a normal use of the functionality offered.

Third, and most importantly, test this. I've no idea how a wild card filter like this is likely to behave on your system. If you have another way to add additional filters before the wild card, say, on object_id or something, I would.

Finally, this won't work if someone types SELECT * FROM dbo.MyTable. I'm not sure how I'd tackle attempting to capture that. I guess you could filter for SELECT, the table name, and *. That would probably do it.

In case I haven't emphasized it enough, test this thoroughly before you implement it in production.


CTE To Use Alternate Select When Select Returns No Rows

Here is the way I would do this, assuming the "do it all in one query" isn't just a pointless, artificial requirement.

SELECT cols INTO #x FROM dbo.Users WHERE UserID > 5;

IF @@ROWCOUNT > 0
  SELECT cols FROM #x;
ELSE
  SELECT cols FROM dbo.Users;

If the "do it all in one query" is a hard requirement and you can't use a #temp table, or you really have complex logic you've dumbed down from us and/or don't want to repeat, then you're going to have to pay for those choices in performance, I'm afraid. This looks like it should be quite efficient, but there are going to be more hits to the base table than most people would hope / expect:

;WITH TargetUsers AS
(
  SELECT cols FROM dbo.Users WHERE UserID > 5
),
AllUsers AS
(
  SELECT cols FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT cols FROM TargetUsers
  UNION ALL
  SELECT cols FROM AllUsers
)
...;

In fact if UserID is the primary key and the filter really is on UserID, this might be better as follows (but you'll have to check the plans to see, depends on a variety of factors):

;WITH TargetUserIDs AS
(
  SELECT UserID FROM dbo.Users WHERE UserID > 5
),
AllUserIDs AS
(
  SELECT UserID FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT UserID FROM TargetUserIDs
  UNION ALL
  SELECT UserID FROM AllUserIDs
)
...
-- and join to dbo.Users to get other columns necessary
-- deferring that to as late as possible

Hopefully your queries are using suboptimal practices due to simplification, and not like that in production, but please give these a read:

Bad habits to kick : using SELECT * / omitting the column list
Bad habits to kick : avoiding the schema prefix


Concatenating only desirable values with LISTAGG

Regexp_replace goes a long way:
 Regexp_replace( LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC),
                   '^[0-9]*(, )?','')

See db fiddle


Split read/write requests over different read/write datasources

There is no automatic way to tell a data modifying query from a reading one.

Imagine the following SQL statement performing a function call:

SELECT delete_all();

There is no way of telling what the function does without analyzing its source code.

So you have to make the decision in your code that (hopefully) knows which queries modify data and which don't.

Using streaming replication in PostgreSQL to off-load read workload to standby servers works, but there is one caveat:

By default, replication is asynchronous, so there is a delay between the transaction commit on the primary server and the time the changes become visible on the standby. Your application has to be able to deal with this (or perform the queries that cannot on the replication primary).

The only way around that would be to use synchronous replication with synchronous_commit = remote_apply, but that will drastically increase the time a transaction commit takes, because it has to wait for the change to be replayed on a standby before it can report success.


Importing CSV's into MySQL(5.6) tables leaves date fields as 0000-00-00

You can import your .csv file and replace after:

Make your date column new_date_column VARCHAR(10).
Import the file.
UPDATE with REPLACE.

  UPDATE db1.table1
   SET  new_date_column = STR_TO_DATE(REPLACE(new_date_column,'/','.'),GET_FORMAT(DATE,'EUR'));

Change the datatype of new_date_column.

ALTER TABLE db1.table1 MODIFY new_date_column new_date_column DATE;

This will convert your new_date_column into XXXX-XX-XX date format.


How to use mySQL to generate auto-increment ID for me

You could implement the following solution.

Game "Table"

This table stores the auto-generated values of the (playing) Tables for the game:

CREATE TABLE tblTable (
    ID int NOT NULL AUTO_INCREMENT);

Game "Players"

This table contains all the relevant Player data:

CREATE TABLE tblPlayer (
    ID         int          NOT NULL AUTO_INCREMENT,
    LastName   varchar(244) NOT NULL,
    FirstName  varchar(244) NOT NULL,
    Age        int          NOT NULL,
    PRIMARY KEY (ID)
);

Game "Games" (Matches)

This table links the tblPlayer table with the tblTable data.

CREATE TABLE tblGame (
    ID_Table   int,
    ID_Player  int
);

Creating a new table

When you create a new table in your game you just initialize a new ID by setting off the following statement:

INSERT INTO tblTable (ID) VALUE DEFAULT;

This will create a new value (ID). You would have to store this value in your program code to use when assigning players to the table.


Is there a way to export an Oracle database to a CREATE DATABASE command?

Rather than doing this, I'd advise on getting the character set info from the nls_database_parameters view & then using DBCA to create a new database, along with dbms_metadata.get_ddl to handle tablespace creation. - Much easier and less prone to error. The character set and national character set are really the only things that are a pain to change when a database has already been created & DBCA lets you chose them in the UI. I imagine you're needing to package some scripts up to automate creation or something?

Anyway, if you really must proceed, you can't do exactly what you're asking out of the box, but you can dump the Control File which will give you most of the information you need to construct a CREATE DATABASE statement, including most pfile parameters (a pfile is required to start a new instance before issuing a CREATE DATABASE statement), redo logs & character set etc.

For example:

SQL> alter database backup controlfile to trace as '/tmp/db.sql';

Database altered.

SQL> !cat /tmp/db.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="PHIL112"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PHIL112" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PHIL112/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/PHIL112/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/PHIL112/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PHIL112/system01.dbf',
  '/u01/app/oracle/oradata/PHIL112/sysaux01.dbf',
  '/u01/app/oracle/oradata/PHIL112/undotbs01.dbf',
  '/u01/app/oracle/oradata/PHIL112/users01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PHIL112/temp01.dbf'
     SIZE 937426944  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PHIL112" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PHIL112/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/PHIL112/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/PHIL112/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PHIL112/system01.dbf',
  '/u01/app/oracle/oradata/PHIL112/sysaux01.dbf',
  '/u01/app/oracle/oradata/PHIL112/undotbs01.dbf',
  '/u01/app/oracle/oradata/PHIL112/users01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PHIL112/temp01.dbf'
     SIZE 937426944  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--

SQL>

Then you can extract the tablespace creation DDL with:

select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) as ts_ddl
from dba_tablespaces;

If you want me to expand further on all of the steps required to manually create a database, just ask (if you don't already know).


DB for storing simple time-stamped values

How unique are the DataID's?  Are there just a handful of DataID's for the 20 million entries per hour or are DataID's more numerous?

But if your most frequent query is based on a narrow time frame first and then specific DataID's then this create statement should give you decent performance.  You will need to change the data types to what makes sense for your environment.

Note: statements are for MS-SQL

CREATE TABLE TestData
(
DataID VARCHAR(20) NOT NULL
, [TimeStamp] DATETIME2 NOT NULL
, Value VARCHAR(1000) NULL
)
GO

CREATE CLUSTERED INDEX IDX_C_TestData ON TestData ([TimeStamp], DataID)
GO


You are right, PostgreSQL's built-in replication (aka Hot Standby) replicates whole cluster - so it's not suitable in your case.

You will need some trigger-based solution. For example,

Slony-I - most mature, written in C, flexible, good for complex setups
SkyTools pgq+londiste - C+Python, more lighweight than Slony-I
RubyRep - Ruby/JRuby, simple, easy to setup, not so mature

Please note that triggers always cause some extra load, and also initial sync will be equivalent of dumping all replicated tables.

Hope this helps, I'd propose to come back with more detailed questions if you have problems.

Good luck!


Solving "Gaps and Islands" with row_number() and dense_rank()?

So the trick here is a property of two equally incrementing series which produce a difference that can be used to identify islands {11,12,13} - {1,2,3} = {10,10,10}. This property isn't enough to identify islands in and of itself, but it's a crucial step that we can exploit to do so.

Background

Stepping aside from the problem. Let's check this out.. Here we

Define 1 group as 3 rows.
Create one group for each pair/row of offsets in xoffsets.

Here is some code.

SELECT x1, x2, x1-x2 AS difference
FROM (VALUES
  (2,42),
  (13,7),
  (42,2)
)
  AS xoffsets(o1,o2)
CROSS JOIN LATERAL (
  SELECT x+o1,x+o2  -- here we add the offsets above to x
  FROM generate_series(1,3) AS t(x)
) AS t(x1, x2)
ORDER BY x1, x2;

 x1 | x2 | difference
----+----+------------
  3 | 43 |        -40
  4 | 44 |        -40
  5 | 45 |        -40
 14 |  8 |          6
 15 |  9 |          6
 16 | 10 |          6
 43 |  3 |         40
 44 |  4 |         40
 45 |  5 |         40
(9 rows)

This looks pretty good, and grouping by the difference would be good enough in this example. You can see we have three groups starting at       (2,42), (13,7), and (42,2), corresponding to the groups in xoffsets.  This is essentially the problem in reverse. But we have one major issue because we're demonstrating this with static offsets. If the difference between any two offsets o1-o2 is the same we'll have a problem. Like this,

SELECT x1, x2, x1-x2 AS difference
FROM (VALUES
  (100,90),
  (90,80)
) AS xoffsets(o1,o2)
CROSS JOIN LATERAL (
  SELECT x+o1,x+o2  -- here we add the offsets above to x
  FROM generate_series(1,3) AS t(x)
) AS t(x1, x2)
ORDER BY x1, x2;

 x1  | x2 | difference
-----+----+------------
  91 | 81 |         10
  92 | 82 |         10
  93 | 83 |         10
 101 | 91 |         10
 102 | 92 |         10
 103 | 93 |         10

We'll we have to find a way define the second offset statically.

SELECT x1, x2, x1-x2 AS difference
FROM (VALUES
  (100,0),
  (90,0)
) AS xoffsets(o1,o2)
CROSS JOIN LATERAL (
  SELECT x+o1,x+o2  -- here we add the offsets above to x
  FROM generate_series(1,3) AS t(x)
) AS t(x1, x2)
ORDER BY x1, x2;

 x1  | x2 | difference
-----+----+------------
  91 |  1 |         90
  92 |  2 |         90
  93 |  3 |         90
 101 |  1 |        100
 102 |  2 |        100
 103 |  3 |        100
(6 rows)

And, again we're back on track to making groups for each pairs of offsets. This isn't quite what we're doing, but it's pretty close and hopefully it serves to help illustrate how the two sets can be subtracted to create islands.

Application

Now let's revisit the problem above with table foo. We sandwich the variables between two copies of x for display purposes only.

SELECT
  id,
  x,
  dense_rank() OVER (w1) AS lhs,
  dense_rank() OVER (w2) AS rhs,
  dense_rank() OVER (w1)
    - dense_rank() OVER (w2)
    AS diff,
  (
    x,
    dense_rank() OVER (w1)
      - dense_rank() OVER (w2)
  ) AS grp,
  x
FROM foo
WINDOW w1 AS (ORDER BY id),
  w2 AS (PARTITION BY x ORDER BY id)
ORDER BY id
LIMIT 10;

 id | x | lhs | rhs | diff |  grp  | x
----+---+-----+-----+------+-------+---
  1 | 2 |   1 |   1 |    0 | (2,0) | 2
  2 | 1 |   2 |   1 |    1 | (1,1) | 1
  3 | 2 |   3 |   2 |    1 | (2,1) | 2
  4 | 3 |   4 |   1 |    3 | (3,3) | 3
  5 | 3 |   5 |   2 |    3 | (3,3) | 3
  6 | 2 |   6 |   3 |    3 | (2,3) | 2
  7 | 3 |   7 |   3 |    4 | (3,4) | 3
  8 | 1 |   8 |   2 |    6 | (1,6) | 1
  9 | 3 |   9 |   4 |    5 | (3,5) | 3
 10 | 1 |  10 |   3 |    7 | (1,7) | 1
(10 rows)

You can see here all of the variables at play, in addition to x and id

The lhs is simple. We're just generating a unique sequential identifier with that (because we're feeding it a unique sequential identifier: id -- though never forget that ids are seldom gapless)
The rhs is slightly more complex. We partition by x and generate a sequential identifier with that amongst the different x values. Observe how rhs increments over the set each time it sees a row with a value that it has already seen. The property of rhs is how many times the value was seen.
The diff is the simple result of subtraction but it's not too useful to think of it like that. Think of more like subtracting a sequence than digits (though they're digits for any single row). We have a sequence increasing by one for the amount of times a value is seen. And, we have a sequence increasing by one for every distinct id (every time). Subtracting these two sequences will return the same number for repeating values, just like in our example above. (11,12,13) - (1,2,3) = (10,10,10). This is the same principle in the first section of this answer

diff doesn't independently mark the group by itself
diff forces all identical islands of x into a group (which may have false-positives, ex. the three cases where diff=3 above and their corresponding x values)

The group grp is a function of (x, diff). It serves as the grouping id, albeit in a slightly weird format. This serves to reduce false positives that would happen if we just grouped by diff.

Simple Unoptimized Query

So now we have our simple unoptimized query

SELECT x, diff, count(*)
FROM (
  SELECT
    id,
    x,
    dense_rank() OVER (ORDER BY id)
      - dense_rank() OVER (PARTITION BY x ORDER BY id)
    AS diff
  FROM foo
) AS t
GROUP BY x, diff;

Optimizations

On the matter of replacing dense_rank() with something else, like row_number(). @ypercube commented

  It also works with ROW_NUMBER() but I think it may give different results with different data. Depends if there are duplicate data in the table.

So let's review it, here is a query that shows

row_number() and dense_rank()
the respective differences computed.
over a result set that includes, ids, 1,2,3,4,5,6,7,8,6,7,8, and two different "islands" of x vals.

SQL Query,

SELECT
  id,
  x,
  dense_rank() OVER (w1) AS dr_w1,
  dense_rank() OVER (w2) AS dr_w2,
  (
    x,
    dense_rank() OVER (w1)
    - dense_rank() OVER (w2)
  ) AS dense_diffgrp,
  row_number() OVER (w1) AS rn_w1,
  row_number() OVER (w2) AS rn_w2,
  (
    x,
    row_number() OVER (w1)
    - row_number() OVER (w2)
  ) AS rn_diffgrp,
  x
FROM (
  SELECT id,
  CASE WHEN id<4
    THEN 1
    ELSE 0
  END
  FROM
  (
    SELECT * FROM generate_series(1,8)
    UNION ALL
    SELECT * FROM generate_series(6,8)
  ) AS t(id)
) AS t(id,x)
WINDOW w1 AS (ORDER BY id),
  w2 AS (PARTITION BY x ORDER BY id)
ORDER BY id;

Result set, dr dense_rank, rn row_number

 id | x | dr_w1 | dr_w2 | dense_diffgrp | rn_w1 | rn_w2 | rn_diffgrp | x
----+---+-------+-------+---------------+-------+-------+------------+---
  1 | 1 |     1 |     1 | (1,0)         |     1 |     1 | (1,0)      | 1
  2 | 1 |     2 |     2 | (1,0)         |     2 |     2 | (1,0)      | 1
  3 | 1 |     3 |     3 | (1,0)         |     3 |     3 | (1,0)      | 1
  4 | 0 |     4 |     1 | (0,3)         |     4 |     1 | (0,3)      | 0
  5 | 0 |     5 |     2 | (0,3)         |     5 |     2 | (0,3)      | 0
  6 | 0 |     6 |     3 | (0,3)         |     7 |     3 | (0,4)      | 0
  6 | 0 |     6 |     3 | (0,3)         |     6 |     4 | (0,2)      | 0
  7 | 0 |     7 |     4 | (0,3)         |     8 |     6 | (0,2)      | 0
  7 | 0 |     7 |     4 | (0,3)         |     9 |     5 | (0,4)      | 0
  8 | 0 |     8 |     5 | (0,3)         |    10 |     8 | (0,2)      | 0
  8 | 0 |     8 |     5 | (0,3)         |    11 |     7 | (0,4)      | 0

You'll see here, that when the column you're ordering by has duplicates you can't use this method because you can't ensure an ordering for a query that has duplicate in the ORDER BY clause. It's very much just the effect of the ordering of the duplicates that throws off the difference: the relationship of the global incremeneting value to the incrementing value over the partition. However, when you have an unique id column, or a series of columns that define uniqueness, by all means use row_number() instead of dense_rank().


designing data model similar to Power Point Presentation slides

It sounds like you are trying to store a whole lot of information, as if you are creating an entire Powerpoint application. Not a bad thing, but as you have indicated you do not want to just use Slide Templates in files, you have to store everything about the Slide (both Templates and user Slides) in the database. This would enable you to reproduce the template on the fly based on the data saved once the user creates their own Presentation, based on a template.

I would approach this with a data model that uses an Entity-Attribute-Value method to store (what could be) a large number of Elements on your slides. The following is the data model I have come up with for you:

The interesting part, that you are probably struggling with, is on the right hand side of the model. SlideElement is an Element object that appears on your Slide. Each Element has a defined set of Attributes as described by the type of element you are adding.

This may not make much sense to you unless you see some example data as shown below:

So each Attribute defines the Attributes of an Element - an Element (could also be called "Control" or something similar) being a text box/picture/video, in the above data.  Elements are placed on Slides at particular locations, defined by the X/Y Position as described on the Element table and with a particular size (Length/Width). The Element entity, along with all the Attributes, is an instance of an Element, with its value described by the "Value" field. For a text box, the value would be its text/text size/colour/back colour etc, for videos/photos, their location/format/encoding/type etc.

Note the "PresentationType" field on the Presentation. I have put this as the identifier to decide if the file is a "Template" or "User" presentation. If the user chooses one of the Template files, the Presentation/Slide/SlideElement/Element rows would all be copied from the "Template" tables and re-inserted for the User tables. The default Element values would therefore be used to start with, with the user overwriting these as they work on their Slide design. The user would be able to move the elements around their slide as well, this would be reflected in the "SlideElement" table XYPos values as well as the size of the Element. The user could also add Elements, like a new text box or photo, which would create a new set of Element rows based on what was added. You would need a default list of Elements to add for each type, that step I haven't modelled but could be done easily.


CMD prompt can't display Unicode characters that are stored in oracle database correctly

Most likely you corrupted your database and you should consider to make a full restore from backup.

ALTER DATABASE CHARACTER SET ... was supported till Oracle version 9 and worked only at certain conditions.

Never use clause INTERNAL_USE or make any updates on Oracle system tables unless you are advised by Oracle Support. If you like to change the character set on an existing database, follow this instruction: Character Set Migration

Perhaps you had good luck, because you changed only WHERE NAME='NLS_NCHAR_CHARACTERSET'. National character set are hardly used nowadays, perhaps you did not destroy everything.

Anyway, items 2.) and 3.) are correct. In case of 32 bit check also registry key HKEY_LOCAL_MACHINE>SOFTWARE>Wow6432Node>ORACLE>KEY_OraDB12Home1

In order to verify if your data is really stored in UTF-8 run

SELECT DUMP(YOUR_COLUMN, 1016) FROM YOUR_TABLE;

And check the binary values. For comparison you can use page Unicode code converter to get UTF-8 binary values (field "UTF-8 code units") of arbitrary strings.


Query to filter out the duplicate and pick the latest - Advanced

Assuming:

the base portion is always 15 characters
the suffix is always 3 numeric digits
the suffix is never '000' [I'm going to use '000' as a default for names with no suffix]

Setup:

CREATE TABLE forms (name nvarchar(50), moddate datetime);

INSERT INTO forms VALUES
('abc123v1-123456'    ,'2018-08-19 00:00:00.000'),
('abc123v1-123456-001','2018-08-19 00:00:00.000'),
('abc123v1-123456-001','2018-08-21 00:00:00.000'),
('abc123v1-123456-002','2018-08-03 11:00:00.000'),
('abc123v1-123456-002','2018-08-03 10:00:00.000'),
('abc123v1-666666-092','2018-08-15 00:00:00.000'),
('abc123v1-000000'    ,'2018-08-15 00:00:00.000');

Break the name into 2 components, defaulting to '000' if the name does not have a suffix:

select -- strip off first 15 characters as part1
       substring(name, 1,15)        as part1,
       -- strip out positions 17-19; for forms that don't have 17-19 we'll add a default '-000'
       substring(name+'-000',17, 3) as part2
from   forms
order by 1,2;

part1            part2
===============  =====
abc123v1-000000  000      -- add default '000' for part2
abc123v1-123456  000      -- add default '000' for part2
abc123v1-123456  001
abc123v1-123456  001
abc123v1-123456  002
abc123v1-123456  002
abc123v1-666666  092

At this point we can place the above in a CTE and perform a basic max()\group by operation to find the name with the highest number ... remembering to strip off our default '000' when displaying our results:

with parts as
(
select substring(name, 1,15)        as part1,
       substring(name+'-000',17, 3) as part2
from   forms
)
select part1 +
       -- ignore '000' otherwise append a '-' and the numeric portion
       case when max(part2) = '000' then '' else '-' + max(part2) end  as maxname
from   parts
group by part1
order by 1;

maxname
===================
abc123v1-000000
abc123v1-123456-002
abc123v1-666666-092

And lastly we'll join these max names back to the original table to find the max() modification date, against using a CTE to manage our intermediate max/name logic:

with parts as
(
select substring(name, 1,15)        as part1,
       substring(name+'-000',17, 3) as part2
from   forms
),
first_max as
(
select part1 +
       -- ignore '000' otherwise append a '-' and the numeric portion
       case when max(part2) = '000' then '' else '-' + max(part2) end  as maxname
from   parts
group by part1
)
select fm.maxname     as 'FormName',
       max(f.moddate) as 'ModifiedDate'
from   first_max fm
join   forms f
on     fm.maxname = f.name
group by fm.maxname
order by 1;

FormName            ModifiedDate
=================== ===================
abc123v1-000000     15.08.2018 00:00:00
abc123v1-123456-002 03.08.2018 11:00:00
abc123v1-666666-092 15.08.2018 00:00:00

NOTE: Tweak the order by to get the desired ordering.

NOTE: Dates are displayed in the default format of this particular db fiddle, so your SQL Server instance may display differently, ymmv.


Restore SQL Server 2012 backup to a SQL Server 2008 database?

You have couple of options :

Option A : Script out database in compatibility mode using Generate script option :

Note : If you script out database with schema and data, depending on your data size, the script will be massive and wont be handled by SSMS, sqlcmd or osql (might be in GB as well).

Option B:

First script out tables first with all Indexes, FK's, etc and create blank tables in the destination database - option with SCHEMA ONLY (No data).

Use BCP to insert data

bcp out the data using below script. set SSMS in Text Mode and copy the output generated by below script in a bat file.

-- save below output in a bat file by executing below in SSMS in TEXT mode

-- clean up: create a bat file with this command --> del D:\BCP\*.dat

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" ' /* path to BCP.exe */
    +  QUOTENAME(DB_NAME())+ '.' /* Current Database */
    +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
    +  QUOTENAME(name)
    +  ' out D:\BCP\'  /* Path where BCP out files will be stored */
    +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
    +  REPLACE(name,' ','')
    + '.dat -T -E -SServerName\Instance -n' /* ServerName, -E will take care of Identity, -n is for Native Format */
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       /* sysdiagrams is classified my MS as UserTable and we dont want it */
/*and schema_name(schema_id) <> 'unwantedschema'    */                             /* Optional to exclude any schema  */
order by schema_name(schema_id)

Run the bat file that will generate the .dat files in the folder that you have specified.
Run below script on the destination server with SSMS in text mode again.

--- Execute this on the destination server.database from SSMS.

--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destinationDB' /* Destination Database Name where you want to Bulk Insert in */
select 'BULK INSERT '
/*Remember Tables must be present on destination database */
+ QUOTENAME(@Destdbname) + '.'
+ QUOTENAME(SCHEMA_NAME(SCHEMA_ID))
+ '.' + QUOTENAME(name)
+ ' from ''D:\BCP\' /* Change here for bcp out path */
+ REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '')
+ '.dat'' with ( KEEPIDENTITY, DATAFILETYPE = ''native'', TABLOCK )'
+ char(10)
+ 'print ''Bulk insert for ' + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + ' is done... '''
+ char(10) + 'go'
   from sys.tables
   where is_ms_shipped = 0
and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */
and schema_name(schema_id) <> 'unwantedschema' /* Optional to exclude any schema */
    order by schema_name(schema_id)

Run the output using SSMS to insert data back in the tables.

This is very fast bcp method as it uses Native mode.


SQL Server Failover cluster installation fails `RequireKerberos` to value '1'

Finally found the a solution online which lead me to the actual solution of the problem.

Apparently it is an issue with SQL Server 2008 and installing the Service Pack is the answer to this. How can we install a service pack while installing the application:

See this article Slipstream Service Packs and Cumulative Updates on a SQL Server 2008 Installation by Edwin Sarmiento.

I tried to install the Service Pack 1 but it failed with a different error messages:

  The installed language for the SQL_Engine_Core_Inst feature is
  inconsistent between the nodes of the cluster. To cluster an instance,
  the language of the instance on each node must be the same.

for me details about the error message read this article FIX: You cannot add a node to a SQL Server 2008 cluster by running the Setup program if there are multiple instances of different languages installed on the same node

I used the method suggested by Edwin Sarmiento but installed SQL Server 2008 Server Pack 4 and wala it worked.

Had to do a lot of reading and tried a lot of different methods to fix it, thought it will be nice to share it with you guys.

cheers thank you.


I too am looking for a better way to do this, but what I've found is that you can enable tracing on the client side and then search the tracefile that was created.

I have this in my client-side sqlnet.ora file:

DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=/tmp
TRACE_FILE_CLIENT=nettrace
TRACE_LEVEL_CLIENT=16

If you're running the client from the same server, you can override TNS_ADMIN to point to a different directory (you may need to copy tnsnames.ora, etc, to that directory as well).