Does the Postgres 9.4.9 update fix MultiXactId "does no longer exist" in addition to "has not been created yet" errors?

I upgraded to PG 9.6.8. This particular environment is a legacy application that runs on an "appliance" style deployment so I'm forced into only updating to certain versions. 9.6.8 was the smallest increment I could take.

The initially appeared to have solved the MultiXactId xxxx does no longer exist problem with some simple queries. Subsequent queries to create indexes failed with the original error.


Processor affinity for SQL Server on vmware

The Microsoft documentation states not to change processor affinity unless it is necessary.

affinity mask Server Configuration Option

Because setting SQL Server processor affinity is a specialized operation, it is recommended that it be used only when necessary.

It also states that the feature will be removed in a future version.

This, combined with the VMware documentation stating that it should not be changed, should be quite convincing.  Additionally, recommendations from the SQL Server community online to change it by default seem to be lacking.

Lastly, Window kernel processes run at a higher priority than other processes on the system (unless they are unwisely set to real-time priority), so all critical processes in Windows will get the CPU they need regardless of how much CPU SQL Server wants at any given time.  Ref. Thread Priorities


Why would this not be sufficient?

SELECT * FROM (
  SELECT MOD.MODULE_NAME, MA.MARK
  FROM MODULES MOD
  JOIN MARKS MA ON (MOD.MODULE_CODE = MA.MODULE_CODE)
  JOIN STUDENTS S ON (S.STUDENT_NO = MA.STUDENT_NO)
  WHERE S.SURNAME = 'Doe'
  ORDER BY 2) x
WHERE ROWNUM = 1;


How to delete a Backup from a backup set in SQL Server 2008 R2

I would change your backup strategy to write to individual files for each database backup.  You could then have a maintenance plan step that examines your backup location and removes *.bak files older the X days.


Are you able to set up a shared folder where only the SQL Service accounts have access? If so, since you are looking for a read-only subscriber, one option may be to use log shipping leaving the database in STANDBY. Of course, this is dependent on how often you need the data updated. Every time a log is restored, the users will be kicked off the secondary. Let's say every hour you restore a log backup, users will be kicked off during the restore, but the data will be pretty close to that time.


Will performance be better if I specify a filter multiple times?

In general there will be no difference. SQL is a declarative language that gets translated into an "execution plan" by the query optimizer. The goal of the optimizer is to provide the best execution plan the produces the results that were asked for in the query. That means that two logically equivalent queries should always produce the same execution plan.

So far the theory. In praxis however, there are way to many ways to build an execution plan for an even moderately complex query. (See for example my answer here: https://stackoverflow.com/questions/16974241/sql-server-join-selects-slower-than-join-select-with-local-table/16974641#16974641) That means that instead of aiming for the best plan, the optimizer can only aim for a good enough plan. The plans the optimizer is even considering are selected based on a set of rules and heuristics and are certainly influenced by the original query.

In SQL Server 2000 a lot of the performance optimization strategies were build around helping the optimizer to start with a better plan set by rewriting the query into another logically equivalent form. Since SQL 2005 the optimizer rarely needs that form of help anymore, but there are still a few queries out there that could benefit from a rewrite.

So to answer your question, if you observed that a query got faster by supplying that additional "hint" in the join condition, the above should give you an explanation. In general this is not the first thing to look at when trying to optimize a query.

Instead you should first make sure that your tables are properly indexed and all statistics are up to date.


Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting?

Well, do you want to keep track of all the versions of the image data, or not? If not, can't you specify only certain columns for CDC instead of just enabling all columns? That will let you keep track of the changes to other columns but ignore the image data changes.

In other words, if all you care about is the current state of the image data, you can get that from the base table. If you care about the previous states of the image data, there isn't any way to store that data without storing it somewhere.


This query with a DISTINCT:

SELECT DISTINCT Name, MyID
FROM data;

Returns distinct Name and MyID:

Name     |  MyID
Alan     |  2
John     |  3

This query with a GROUP BY:

SELECT MIN(ID) AS ID, Name, MyID
FROM data
GROUP BY Name, MyID;

Returns Name and MyId with the smallest ID like your sample:

ID   |  Name     |  MyID
1    |  Alan     |  2
3    |  John     |  3

Both query are in this SQL Fiddle and will work with most RDBMS. (Sample uses SQL Server)


MySQL replication and master users on slave server

The "user" that the Slave connects to the Master with -- It needs only REPLICATION SLAVE, no other privileges.  Replication's goal is to keep the Slave identical to the Master.  This goal overrides any privilege issues.
The Slave should have read_only = ON as an extra protection.
"Users" (either human or application), other than the system admin, should not have SUPER privilege.  This keeps you out of trouble, and helps prevent hacking.
Another hacking prevention is to GRANT users access only to dbname.*, not *.*.  The latter would include the system tables where all the Grants are stored.
Users need write permissions when writing to the Master.  Don't worry about whether the users have write perm to the Slave; the Slave user bypasses that kind of check.  (This is probably the answer you were looking for.)
Users need only read permission when reading from the Slave.
TEMPORARY tables follow different rules.  (It is something you might want to use on a read-only Slave.)  I hesitate to spell them out here, because I am not sure of the rules.


How to real time replicate a SQL Server DB without primary keys defined

It is unlikely that any replication solution you can import would work without a PK (or at least a unique index) otherwise it can't tell the difference between an update and a delete+insert (amongst other problems) which could be quite a significant issue depending on what keys and triggers exist on or refer to the affected table(s).

If you can't add a PK or other unique index then you will need to try write your own synchronisation solution for the situation.

Is there any reason why the table(s) can't have a primary key?


foreign key on multiple tables based on multiple columns

There are three ways:

If projects and invoices have almost the same columns and are ofhen used together like “get all projects or invoices that fulfill a certain condition” or “there must be a project or invoice that...”, then it is natural to model these two objects with a single table, introducing a column type to tell them apart.
Then you can have a regular foreign key.

If they should be modeled as different tables and have different columns, introduce two foreign key columns in fields, ideally with a check constraint:
 CHECK (parent_invoice IS     NULL AND parent_project IS NOT NULL OR
        parent_invoice IS NOT NULL AND parent_project IS     NULL)

A hybrid solution would be
 CREATE TYPE p_or_i AS ENUM (
    'project',
    'invoice'
 );

 CREATE TABLE project_or_invoice (
    id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
    type p_or_i NOT NULL,
    /* other common columns */,
    PRIMARY KEY (id, type)
 );

 CREATE TABLE project (
    id bigint NOT NULL,
    type p_or_i GENERATED ALWAYS AS ('project') STORED NOT NULL,
    /* specific columns */,
    PRIMARY KEY (id, type),
    FOREIGN KEY (id, type) REFERENCES project_or_invoice
 );

 CREATE TABLE invoice (
    id bigint NOT NULL,
    type p_or_i GENERATED ALWAYS AS ('invoice') STORED NOT NULL,
    /* specific columns */,
    PRIMARY KEY (id, type),
    FOREIGN KEY (id, type) REFERENCES project_or_invoice
 );

Then you would reference project_or_invoice in your foreign key.


How to take backups on a defined directory instead of creating sub-directories

You just need to change the Directory parameter in the Ola Hellengren script. Go to the job, click the Step --> Edit-->@Directory = 'C:\Backup',

This will be enough.

Adding to your particular requirement, I went into the Store proc and was able to figure out. This took me some time so apologies for that. Please mark this as answer once you are able to complete your task.

Below are the steps to get your required directory :

For getting all backups to just one location, you will need to remove few parameters from the store proc [dbo].[DatabaseBackup]. There will be this part where you set variebles( Look for the comments) -- Set variables
Go ahead and look for the below part :

  SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN
  RIGHT(DirectoryPath,1) = @DirectorySeparator THEN '' ELSE
  @DirectorySeparator END + CASE WHEN @CurrentAvailabilityGroup IS NOT
  NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE
  REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar(max)),'\','$')
  END + @DirectorySeparator + @CurrentDatabaseNameFS +
  @DirectorySeparator + UPPER(@CurrentBackupType) + CASE WHEN
  @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN
  @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER()
  OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0   FROM @Directories
  ORDER BY ID ASC

Now you need to remove the parameters : @CurrentDatabaseNameFS and UPPER(@CurrentBackupType). This will create all your backups to a single location as below

Go through each of the available parameters in Ola hellengren scripts. Ola Hellengren


It's converting your variables, because bigint is what OFFSET ... FETCH is expecting for those values. It probably makes sense to

DECLARE @paramPageNumber AS BIGINT,
         @paramPageSize AS BIGINT;

to avoid any possible performance issues this may cause.

Your id column in your table, however, should still be fine as an int.

From the documentation for TOP (Transact-SQL):

-- Syntax for SQL Server and Azure SQL Database

[
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

  Arguments

  expression
  Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.


How many tables will be there in Magento 1.8

I wouldn't worry if I were you. Many systems (Oracle's EBS e-business suite) have gazillions (a techical term meaning a large number) of tables, most of which will never be used by many customers. Any major e-commerce/ERP/ERM system will be like this. You'll find that you'll be spending 85% of your time working with just a few modules which are core to your own business - and that's as it should be.

Just as a matter of interest, what prompted you to go with Magento? You business speciality? Are you going commerical or Open Source? Did you consider other solutions Sugar CRM/Adiempere/Apache OfBiz?


Incorrect total using SUM of DATEDIFF results

Well, quite simply, there are gaps in your time series.

A naive DATEDIFF(MIN, MAX) ignores the fact that there is roughly a 6-minute gap between your second and third rows.

Think about it this way: If you go to school from Monday-Friday, how many days did you attend in June? Not 30, because some of those days in June were Saturday and Sunday (and maybe holidays and PD days, too).


How to match similar records using multiple columns

The simplistic method is to count the number of columns that match:

select
 n.id as needle_id,
 h.id as haystack_id,
 case when n.name = h.name then 1 else 0 end
 + case when n.phone = h.phone then 1 else 0 end as relevance
from
 needles n
join
 haystack h
on
 n.name = h.name
 or n.phone = h.phone
order by
 relevance desc;

where "name" and "phone" are columns in your tables. You can use the same table as both the needle and haystack if you want (to find duplicates in a table).

http://blog.databasepatterns.com/2014/12/fuzzy-record-matching-in-sql-p1.html

The more correct and much more complicated method is to use Probabilistic Record Linkage. There's an R package for it (recordlinkage)


Ramifications of Deleting Transaction Log Backup

So order of operation:

1st tlog backup
delete 1st tlog backup
full backup
tlog backup
no catastrophe, just concerned over integrity

If this is correct then you will be fine. As long as the LSN or chain of (backup) events since the last full backup is not broken you should have no issues with recovery.


Rephrasing your query it seems like you simply want to check if there's no row with today's date. It's a bit tricky to make it an conditional Insert:

INSERT INTO scan (id, date)
SELECT (MAX(id)+1), CURDATE()
FROM scan
HAVING COUNT(CASE WHEN scan.date = CURDATE() THEN 1 end) = 0;

To make it work even with an empty table change to (COALESCE(MAX(id),0)+1)


UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

Why am I getting this error in this scenario?

The agent itself is switching QUOTED_IDENTIFIER to OFF.

I have written about it here (mind the unreadable font, not my choice).

Here are the tests run on sql server 2012 to sql server 2017:

If I run this query in a ssms query window:

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';
IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;

The result shows as on:

QUOTED_IDENTIFIER
ON

When I put the query to check the quoted identifier in a job and run it, this happens:

create table dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER varchar(3));

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';
IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;

insert into dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER)
select @QUOTED_IDENTIFIER;

View the results:

select * from dbo.QUOTEDIDENTIFIER_CHECK;

Result:

QUOTEDIDENTIFIER
OFF

When tracing the agent, you can clearly see the statement that turns quoted identifier off.

  How do I resolve this? Do I need to re-create all the Agent jobs,
  switching QUOTED_IDENTIFIER ON before I do?

To resolve this you have to add explicit SET QUOTED_IDENTIFIER ON; statements to the agent job steps.


But the statement which extract the the year is failing I do not know why.

Change the way it is assigning the the variables. Use SELECT...INTO. Below is an example,

SELECT IF( (SELECT EXTRACT(YEAR FROM date_creation) FROM billing_invoice
WHERE (id = @current_id)) = YEAR(CURDATE()) ,@next_id := @next_handler, @next_handler := 0001) INTO @dummy;

This is not a perfect solution in that it creates the @dummy variable, but I can't comment so I just posted it.


Django Auth user as well as custom users table?

I understand you might not still be looking but it might help someone else. The best way to do this, in my opinion, would be a simple OneToOne or ForeignKey on your new model to User. In the admin you can override the User admin and add an inline:

class FbDetailsInline(admin.TabularInline):
    model = FbDetails
    extra = 1 # Or however many you would like

class UserAdminWithFb(admin.ModelAdmin):
    ... # list display etc.
    inlines = [FbDetailsInline]

admin.site.unregister(User)
admin.site.register(User, UserAdminWithFb)

You can then set up a signal to update the relevant info and then view in the admin directly with the user.


but for example no "arrow-up" key for the previous history entry is available.

You're talking about sqlplusw the "GUI" tool - the oldschool sqlplus does support
arrow-up-bring-previous-statements feature.

  What is a good replacement / extension for sqlplus

Oracle's SQL Developer, a free GUI-based tool, is pretty neat.

If you're a vim addict, there's VoraX which is augments SQL*Plus very well.

  VoraX aims to be a simple, yet a powerful IDE for Oracle databases. Instead of reinventing the wheel, VoraX relies on the well-known SqlPLUS which is actually used to connect to the database, to execute SQL statements and to get the results back. However, this interaction is hidden from the user who just uses his/her Vim environment without knowing that, behind, a SqlPLUS process does all the work.

  The big advantage of this approach is that almost all SqlPLUS commands may be used without a problem. You can format columns, set pagination, autotrace queries etc.

  FEATURES

  99.9% of the sqlplus commands supported
  advanced code completion
  it runs on Windows and UNIX
  a nice results window with highlighting for errors and logging support
  a database explorer which may be used to browse the database objects in a hierarchical way
  support for editing and compiling PLSQL objects like packages, types etc.
  convenient key mappings for: describe object under cursor, execute current statement etc.
  connection profiles management (including securely storing of passwords)
  fuzzy search for database items
  offline search for Oracle html documentation


IBM DB2 v9.7 HADR TSA failure on the standby server

Ember Crooks works with HADR and TSA a LOT on DB2. (She uses it for WebSphere Commerce, but you can apply the generalities from her posts.)

Here are two posts(#1 and #2) regarding HADR that feature a similar issue to what you are facing. Based off of what she has documented, what you are seeing appears to be normal behavior (or at least expected behavior, even if it isn't desirable).

Check out Ember's blog (the first link in my post). I subscribe to it as I've learned a lot of key things about DB2 operations-especially related to HADR and TSA-from her.


Can PAGELATCH Waits cause increase in SOS_SCHEDULER_YIELD wait in SQL Server?

Could the PAGELATCH Waits cause the SOS_SCHEDULER_YIELD Wait to go up?

No.

SOS_SCHEDULER_YIELD wait time is spend waiting on an available scheduler (CPU).  After any other resource wait, a session will accumulate Signal Wait Time while waiting for an available scheduler.  The Signal Wait Time is tracked along with the previous wait, and included in the reported wait_time, but broken out in the signal_wait_time column in the Instance Level and Session Level Wait Stats DMVs.

So there may be a relationship between the two waits, but it's not that the PAGELATCH causes the SOS_SCHEDULER_YEILD.  Check the signal_wait_time on the PAGELATCH wait.  If the signal_wait_time is a significant proportion of the wait_time, then the PAGELATCH waits are really CPU waits.

Note that when a session is waiting for a page latch, the session that currently owns the page latch may itself be waiting for CPU, so some of the non-signal wait time may also be related to CPU waits.

Put another way, excessive CPU use (or insuffient CPU resources) can cause both SOS_SCHEDULER_YEILD waits and cause other short waits to drag out as no scheduler is available at the end of the resource wait.


Based on the information in SQL Server Agent Fixed Database Roles, it appears that non-sysadmin users must be a member of one of the following fixed database roles in msdb to utilize the SQL Agent:

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

Each of the above fixed database roles progressively allows more authority than the previous one, but with regards to being able to modify jobs, all three indicate that permissions are granted for owned jobs only.

The way I interpret the information in this post is, if you are not a member of the sysadmin fixed role, you only have the permissions to modify jobs that you own, whether that be an individual or a windows group.

You should be able to easily test this theory by creating two or more users, assigning them to one of the above fixed database roles (making sure they are not members of sysadmin), creating individual jobs under each user and then attempting to alter jobs not owned by that user.

I've included some information below about the permissions for each of fixed database roles mentioned above (highlighting mine).

SQLAgentUserRole Permissions

  SQLAgentUserRole is the least privileged of the SQL Server Agent fixed
  database roles. It has permissions on only operators, local jobs, and
  job schedules. Members of SQLAgentUserRole have permissions on only
  local jobs and job schedules that they own. They cannot use
  multiserver jobs (master and target server jobs), and they cannot
  change job ownership to gain access to jobs that they do not already
  own. SQLAgentUserRole members can view a list of available proxies
  only in the Job Step Properties dialog box of SQL Server Management
  Studio. Only the Jobs node in SQL Server Management Studio Object
  Explorer is visible to members of SQLAgentUserRole.

SQLAgentReaderRole Permissions

  SQLAgentReaderRole includes all the SQLAgentUserRole permissions as
  well as permissions to view the list of available multiserver jobs,
  their properties, and their history. Members of this role can also
  view the list of all available jobs and job schedules and their
  properties, not just those jobs and job schedules that they own.
  SQLAgentReaderRole members cannot change job ownership to gain access
  to jobs that they do not already own. Only the Jobs node in SQL Server
  Management Studio Object Explorer is visible to members of the
  SQLAgentReaderRole.

SQLAgentOperatorRole Permissions

  SQLAgentOperatorRole is the most privileged of the SQL Server Agent
  fixed database roles. It includes all the permissions of
  SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also
  view properties for operators and proxies, and enumerate available
  proxies and alerts on the server.

  SQLAgentOperatorRole members have additional permissions on local jobs
  and schedules. They can execute, stop, or start all local jobs, and
  they can delete the job history for any local job on the server. They
  can also enable or disable all local jobs and schedules on the server.
  To enable or disable local jobs or schedules, members of this role
  must use the stored procedures sp_update_job and sp_update_schedule.
  Only the parameters that specify the job or schedule name or
  identifier and the @enabled parameter can be specified by members of
  SQLAgentOperatorRole. If they specify any other parameters, execution
  of these stored procedures fails. SQLAgentOperatorRole members cannot
  change job ownership to gain access to jobs that they do not already
  own.

  The Jobs, Alerts, Operators, and Proxies nodes in SQL Server
  Management Studio Object Explorer are visible to members of
  SQLAgentOperatorRole. Only the Error Logs node is not visible to
  members of this role.


Is there any way to increase the mysql reserved pool for super users beyond 1?

Is there some sort of sane upper limit any one user should never exceed?  Maybe your busiest application  has at most 20 open connections during peak normal operation.  Let's assume you have 20 non root users.

set max_user_connections = 30 (to give a bit of a safe buffer)

set max_connections = max_user_connections * # users + extra buffer,
   or 30 * 20 + 50 = 650 max connections.

This way your global max connections is safe from every getting reached and there should always be room for you to get in even if one particularly application or user is getting maxed out.


Discrepancy between system catalog view and sp_change_users_login?

First, sp_change_users_login is deprecated feature and is discontinued in SQL Server 2014.

Secondly, EXEC sp_change_users_login @Action = 'Report' is not a proper way to get orphan users list. As with 'Report' parameter, it will execute following query. According to which it will return users where issqluser = 1 (It must be a SQl Server Login), and no result if orphan user is NT User instead of SQL User.

  select UserName = name, UserSID = sid,* from sysusers
            where issqluser = 1
            and   (sid is not null and sid <> 0x0)
            and   (len(sid) <= 16)
            and   suser_sname(sid) is null
            order by name

According to BOL "sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN."

Best script to find orphan users against all databases on an instance is already provided by "Mark Storey-Smith"

Find orphaned users


How do I enable full text search on MySql 8?

You have to add or create an INDEx like
 ALTER TABLE directory_coop ADD FULLTEXT idx_name(name);

For more information read the manual ALTER TABLE


Efficient way on updating multiple tags or categories

Usage?

You have not listed what kind of queries will hit this table.  You can't really design a table, much less optimize it, until the SELECTs are tentatively written.  More later.

First, some simplification

Get rid of id; make PRIMARY KEY(post_id, meta_key, meta_value).  After all, that has to be unique.  The id is clutter and slows things down for the normal operations which involve WHERE post_id = constant AND meta_key = constant.

Because of the 3 tags vs 4 tags you mentioned, the only reasonable way to do it is

DELETE FROM meta WHERE post_id = ? AND meta_key = ?;
INSERT INTO meta (post_id, ... )
    VALUES
    (...), (...), (...), (...);

Then some more simplification

The EAV schema (which you have) sucks.  You may as well have a table called Tags.  Now you can get rid of meta_key, and everything gets simpler and shorter.

You may choose to keep the EAV stuff for where there is only one value for a meta_key.  That is a separate discussion.  I'm arguing that it is wrong to fit multi-valued "tags" into that mold.

Further

Do you really need those timestamps?  WP may be omnipresent, but it is not necessarily 'right'.

Ordering?

When you fetch everything for some tag, don't you want to put them is some order?  And might you paginate it?  So, maybe you need one of those timestamps for this?

Bottom line

CREATE TABLE `tags` (
    post_id ... NOT NULL,
    tag varchar(55) NOT NULL,
    sort_order ... NOT NULL,
  PRIMARY KEY (tag, sort_order, post_id),  -- for the main select, plus dedup
  INDEX(post_id)  -- for maintenance
) ENGINE=InnoDB;

Further discussion


Startup failing with ORA-14694 after running utl32k.sql in upgrade mode

I figured it out
- PURGE DBA_RECYCLEBIN;
- CREATE SPFILE FROM PFILE = '$ORACLE_HOME/dbs/initorcl.ora';
- SHUTDOWN IMMEDIATE;
- startup mount;
- ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=SPFILE;
- shutdown;
- STARTUP UPGRADE;
- @?/rdbms/admin/utl32k.sql
-show parameter MAX_STRING_SIZE
    NAME               TYPE        VALUE
    ------------------ ----------- --------------
    max_string_size    string      EXTENDED
- SHUTDOWN IMMEDIATE;
- STARUP;

Now startup is successful and max_string_size = EXTENDED


how to drop a db2 database that is in an inconsistent state

Since the database seem to be of no value, you can try:
db2stop force
ipclean
db2start
db2 drop database mydb

You may be interested in the following post/answer. It is not exactly your situation but may be of some help anyhow:
unable-to-restore-database-because-it-exists-unable-to-drop-because-it-does-not


SQL Server 2008 - How to schedule a copy of view to another database?

Moving 1GB is not a trivial operation, though not a scary one either.  A simple INSERT .. SELECT would have too much impact on the running server.  Batching so only n-thousand rows are moved at a time would be better.  'n' can be tweaked to the idiosycracies of your particular system.  Better still would be a dedicated ETL package.  You'll need a SQL Server developer to help you with that, most likely.  If it is important that you never miss a row be sure to build reconciliation into whatever solution you adopt.

Be careful using a datetime to determine whether a row should be copied or not.  Times have to rounded to the resolution of the column holding them.  It is therefore possible that a new row is rounded down and missed by the next extraction.  It sounds like you will be copying data that is 13 days old so will not be at risk.


Comparing dmp files like that is probably quite difficult to do, as the format is quite complex, with snippets of embedded SQL and all sorts.  Having said that, although it is a binary format, it is a stream format (i.e. no internal pointers), so it's quite amenable to processing with sed.

I think loading the contents of he dmp files into two schemas and comparing the two is by far the easiest way you're going to do this, barring the existence of some tool that I'm not aware of.


How to conditionally append rows to a result set?

Assuming (like was added later) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT e.id, '2D'
FROM   event e
LEFT   JOIN cte ON cte.event_id = e.id
               AND cte.attribute IN ('2D','3D')
WHERE  cte.event_id IS NULL;

Related:

Select rows which are not present in other table
Optimize GROUP BY query to retrieve latest record per user
Is there a shortcut for SELECT * FROM?

If your query only returns a subset of all event_id, you can't use the table event to simplify like this. Alternative without table event:

WITH cte AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT event_id, '2D'
FROM   cte
GROUP  BY 1
HAVING count(*) FILTER (WHERE attribute IN ('2D', '3D')) = 0;

This is somewhat similar to what you answered yourself, just shorter and a bit faster. In particular the aggregate FILTER clause should be instrumental. Related:

For absolute performance, is SUM faster or COUNT?

Since there are no indexes on the derived table from the CTE, the second query may be faster to begin with.


How do I copy a materialized view to a table?

It sounds like you just want a CREATE TABLE AS SELECT

CREATE TABLE your_new_table_name
AS
SELECT *
  FROM your_materialized_view

If there are indexes on your materialized view that you want to create on the table, you'd need to create those separately.


I'm presuming you don't have metalink as this was the first hit:

SYMPTOMS

Instance not reachable and process dying.

Background processes are dying and oracle is unable to spawn new processes. Clients cannot connect and they receive error:

java.sql.SQLRecoverableException: ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 2673
Additional information: 488341508
Additional information: 1744830464

When this happens local connections are not possible, but already existing connections continue to work. The problem appears when running backups or complex queries then it disappears.

No specific memory related error is reported on the server log just logs like this one:

Errors in file /u01/app/oracle/diag/rdbms/alma/ALMA2/trace/ALMA2_cjq0_6234.trc:
Process J000 died, see its trace file
Thu Apr 06 14:25:40 2017
kkjcre1p: unable to spawn jobq slave process   <=============  important symptom
Thu Apr 06 14:25:40 2017
Errors in file /u01/app/oracle/diag/rdbms/alma/ALMA2/trace/ALMA2_cjq0_6234.trc:
Process PPA2 died, see its trace file
Process J000 died, see its trace file
Thu Apr 06 14:25:42 2017
kkjcre1p: unable to spawn jobq slave process

CAUSE

This is was investigated in

  Bug 24921392 CJQ FAILED TO SPAWN SLAVE PROCESS KKJCRE1P: UNABLE TO
  SPAWN JOBQ SLAVE PROCESS

The problem may be seen if MEMORY_TARGET is used and memory or swap space is under pressure.

SOLUTION

Apply patch 24921392 if available

OR

As a workaround, increase memory and/or swap on the affected system to avoid the issue.


Are there any security implications with enabling "Data Access" on a sever?

Enabling Data Access is not considered a finding in the CIS Microsoft SQL Server 2016 Benchmark but that doesn't mean it couldn't be a problem if it's poorly implemented.

If you are querying data on another server limit the privileges granted to the remote user.


User on SQLAgentOperatorRole unable to edit jobs

See the documentation at https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver15. It clearly states what you can do with this role. It does not include privileges to edit a job owned by somebody else, for instance.


You do not need to enumerate each object you want to export/import, you can pass a query that returns the list:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table tables_to_export(table_name varchar(128 char));

Table created.

SQL> insert into tables_to_export values ('T1');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@o71 ~]$ expdp bp/bp include=table:\"in \(select table_name from tables_to_export\)\"

Export: Release 12.2.0.1.0 - Production on Thu Jun 15 21:18:57 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "BP"."SYS_EXPORT_SCHEMA_01":  bp/******** include=table:"in (select table_name from tables_to_export)"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP"."T1"                                   9.535 MB   76511 rows
Master table "BP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BP.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/max/dpdump/expdat.dmp
Job "BP"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 15 21:19:19 2017 elapsed 0 00:00:21


How do I completely disable MySQL replication

To completely disable replication with a master-master setup, you should do the following on each slave:

STOP SLAVE;
RESET SLAVE; (Use RESET SLAVE ALL; for MySQL 5.5.16 and later)
Edit the my.cnf and remove any information (if present) which refers to "master-..." or "replicate-..." options.  You may not have anything in the my.cnf, since replication can be setup dynamically as well.
Restart mysqld.


Receiving error "invalid identifier" while trying to update a table in Oracle

Your update query doesn't seem complete or it would give another error message , also it helps to make a [mre].

create table TEST_FILE_MERGE
(
  customer_num    NUMBER,
  CUST_FIRST_NAME VARCHAR2(180),
  CUST_LAST_NAME  VARCHAR2(180),
  r_id            NVARCHAR2(1000)
  ,
  CONSTRAINT cumstom_unique UNIQUE (customer_num)
);

INSERT INTO TEST_FILE_MERGE VALUES( 123,'perter','foe','1')

create table REQUEST
(
  req_id              NVARCHAR2(1000),
  cust_no             VARCHAR2(50),
  customer_first_name VARCHAR2(50),
  customer_last_name  VARCHAR2(50),
  error_code          NUMBER,
  "IS_CHECKED"          NUMBER
);

INSERT INTO REQUEST VALUES (1,123,'Peter','Foe',0,0)

UPDATE (SELECT A.CUST_FIRST_NAME, A.CUST_LAST_NAME
,B.CUSTOMER_FIRST_NAME,B.CUSTOMER_LAST_NAME,B.ERROR_CODE,B.IS_CHECKED
          FROM TEST_FILE_MERGE A
         INNER JOIN REQUEST B
            ON A.CUSTOMER_NUM = B.CUST_NO)
   SET CUSTOMER_FIRST_NAME  = CUST_FIRST_NAME,
       CUSTOMER_LAST_NAME   = CUST_LAST_NAME,
       ERROR_CODE           = 0,
       IS_CHECKED           = 1;

1 rows affected

SELECT * FROM REQUEST

REQ_ID | CUST_NO | CUSTOMER_FIRST_NAME | CUSTOMER_LAST_NAME | ERROR_CODE | IS_CHECKED
:----- | :------ | :------------------ | :----------------- | ---------: | ---------:
1      | 123     | perter              | foe                |          0 |          1

db<>fiddle here
db<>fiddle here


Which account should be used for the SQL Server Service logon account where the server has been added to a domain?

What I have seen implemented in a few enterprise size companies is to have an Active Directory  managed service account created on a per server or cluster basis which runs the database and agent services.

Depending on group policies in place you may have to also request that AD group policies or local security policies be modified to allow the right to "logon as a service".  There is no need to grant extra privileges to the account.

Ideally you would add this account during the setup of a new install so the permissions on the SQL Server executables are granted automatically.

This benefits security policy as auditors can see that the password policy of the domain is applied to the account and that the account has privileges only on the server/cluster that are appropriate to run the database and nothing else.

I recommend a managed service account to do this because if you are at the stage where you are implementing encryption you might as well do all the right things from a security standpoint now so you don't have to go back and redo them later.  A well named dedicated account will allow you apply a complex password that can be changed as required by company standards without wondering about where else you used that account.


Register dll to use in SSIS Script Component

You don't need Visual Studio, you need the Windows SDK. You can use MSDN to find the SDK matching your platform. The assembly has to be strongly named and can only be added to the GAC using the gacutil /i your.dll command or by using a Windows installer package. Prior to .NET 4.0 you could drag and drop the dll into the C:\Windows\Assembly folder but this is now obsolete.


Changing the administrative user on mysql from `root` to something else

Connect to MySQL as -u root, then

First, see what root has:

SHOW GRANTS;

Then add a new user, similar to the root:

GRANT ALL PRIVILEGES ON *.* TO 'foobar'@'localhost' IDENTIFIED BY ('long secure pwd') WITH GRANT OPTION;

where foobar is the "mortal" mentioned in your quote.

Then re-login as foobar to verify it.  Then get rid of root (or at least emasculate it).  Perhaps this

REVOKE ALL *.* FROM root@localhost;

Then verify with

SHOW GRANTS FOR root@localhost;
SELECT user, host FROM mysql.user WHERE user = 'root';

The first should show only GRANT USAGE, which lets root in, but without any privileges.  The second checks to see of root can get in from any other "host".  If there are any ways in, we can discuss zapping them.


Why shouldn't I have one table for multiple relationships?

What does your colleague propose as the primary key for this link table?
Primary key columns can not be NULL of course: the table above has nullable.

There isn't any natural row identifier (which is what a PK is) in the example above (a IDENTITY column is not a primary key), therefore it fails in any modelling process. Don't even think about creating tables without some model (ERD, ORM, IDEF1X, whatever)

You'd also need CHECK constraints to ensure you don't have 3 way links.

Finally, you're straying into 4th and 5th normal form territory but for the wrong reasons.

I can't find any examples on the internet: that shows how stupid this is


The only easy way to do this is with UNION ALL queries, but with more than one row in your database you may not get the answer you are expecting.

SELECT unitid FROM your_table
UNION ALL
SELECT row1 FROM your_table
UNION ALL
SELECT row2 FROM your_table
UNION ALL
SELECT row3 FROM your_table
UNION ALL
SELECT row4 FROM your_table
UNION ALL
SELECT row5 FROM your_table
UNION ALL
SELECT row6 FROM your_table

If you have two rows, you will get two unitid values, then two row1 values, etc.


The problem is that you are only filtering by that large calculation so there is no choice but to do a full table scan and calculate that formula for every row.

You could limit the number of rows considered by adding an index on the lat and long columns and filter by an approximate square as well as the exact crow-flies calculation. That way the query runner should be able to seek using that index to find those in the larger area of the square ("x between n1 and n2 & y between n3 and n4" should use that index (an index seek followed by a partial scan)) then only need to lookup & read the full rows and do the calc & sort on those few matches to find the final filtered/limited set.

If you have the chance to consider changing database backend at this point in your project some (postgres with the PostGIS extensions for example) support special types and indexes for handling geometry data (even sometimes natively supporting lat/long based calculations), though that change may be overkill if this is the only part of your application that would make use of it.


How to create Always Encrypted Certificate for specific time period?

When this first came out I had a blog post on this, creating the certificate via Powershell: https://justdaveinfo.wordpress.com/2015/10/15/always-on-encrypted-generating-certificates-and-column-encryption-key-encrypted_value/

Checking the documentation for the Powershell cmdlet New-SelfSignedCertificate you should be able to use "-NotAfter (Get-Date).AddMonths(24)"

https://docs.microsoft.com/en-us/powershell/module/pkiclient/new-selfsignedcertificate?view=win10-ps


Unable to export 8.0.1 MySQL database using MySQL Workbench 8.0

information_schema_stats_expiry was added in 8.0.3. Use the MySQL Workbench for 8.0.1. I hope you're upgrading to a stable version.


You might want to have a look at the following article:

Configure and Manage Stopwords and Stoplists for Full-Text Search (Microsoft Docs)

  To prevent a full-text index from becoming bloated, SQL Server has a mechanism that discards commonly occurring strings that do not help the search. These discarded strings are called stopwords. During index creation, the Full-Text Engine omits stopwords from the full-text index. This means that full-text queries will not search on stopwords.

It contains a general overview of how stoplists work and references a further article that goes on to explain how you can modify stoplists to achieve what you are looking for:

ALTER FULLTEXT STOPLIST (Transact-SQL) (Microsoft Docs)

  Inserts or deletes a stop word in the default full-text stoplist of the current database.

The syntax of the command is as follows:

ALTER FULLTEXT STOPLIST stoplist_name
{
        ADD [N] 'stopword' LANGUAGE language_term
  | DROP
    {
        'stopword' LANGUAGE language_term
      | ALL LANGUAGE language_term
      | ALL
     }
;


MySQL query to find time difference based on a condition

SELECT MIN(`time`) time_from,
       MAX(`time`) time_till
FROM ( SELECT *,
              @group := @group + CASE WHEN NOT sp AND @prev THEN 1 ELSE 0 END group_num,
              @prev:=sp
       FROM test, (SELECT @prev:=-1, @group:=0) variable
       WHERE ign = 1
       ORDER BY `time` ) temp
WHERE NOT sp
GROUP BY group_num;

fiddle


Always Encrypted on Sql 2017 Express Edition

As per this link, Always Encrypted is supported in SQL Server 2017 Express Edition.


SQL Server - creating a clustered index in a large heap - should I add a column?

In general, a surrogate ID (an incrementing number that doesn't have any other meaning) is the best choice unless you have a specific reason why it won't work.

One of the biggest problems with using a non-incrementing value for a primary key is that you'll have page splits on some inserts, which will cause them to be slower.  How much slower depends on a lot of factors, so it's just something you'd have to measure on the system.

Google "benefits of surrogate primary key" and you'll find a ton of information.  It's really a small book to cover the topic, so the full answer isn't really appropriate for this site.  This question also has a few answers that provide good information on this topic.


Where is my old partition in mysql show create table?

You don't have plessthanmax part? When you set up a partition (that "moves" daily) the best practice is to set up a plessthanmax part to avoid errors. Each day you create a new partition and reorganise the plessthanmax like this :

For December 29

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p24 VALUES LESS THAN (to_days('2012-12-29')), partition plessthanmax values less than maxvalue)

For December 30

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p25 VALUES LESS THAN (to_days('2012-12-30')), partition plessthanmax values less than maxvalue)

Max.


Postgresql - Can I do a join in a function?

Yes, except the Postgres syntax through CREATE FUNCTION is  more natural:

CREATE FUNCTION my_proc(myOpt bool, myParam varchar)
RETURNS SETOF (types)
AS $$
  SELECT types
  FROM my_table1 t1
  INNER JOIN table2 AS t2 USING (id)
  WHERE myOpt AND t2.param = myParam
$$ LANGUAGE sql;

If you need a more complex procedural language check out PL/pgSQL


tsql to find out memory, cpu and IP utilization by a databases over a week - MS SQL Server 2005 and above

There are several options you can use:

You can use Use Dynamic Management Views (DMVs), such as sys.dm_exec_sessions, sys.dm_os_performance_counters, sys.dm_os_memory_brokers, sys.dm_os_memory_nodes, sys.dm_exec_procedure_stats, sys._dm_os_sys_info, sys.dm_exec_requests, sys.dm_exec_requests, and many more, depending on what you actually want to monitor.

As SQL Server doesn't store the performance metric values in an archive table, you can query the views on a schedule and insert the results into a table that you will use as a repository.You cfan query and analyze the records easily then.

Another question is - what counters to monitor.

For processor usage: Processor : % Processor Time, Processor Queue Length

For memory, Available memory bytes, Total server memory, and Target server memory

You can find a complete list of counters recommended for monitoring here: Performance Monitor Counters

Note that when you use the dm_os_performance_counters view, it's essential to understand the counter_type. There are five different values, and the current value is calculated differently for each value type. You have examples and explanations here: sys.dm_os_performance_counters

Another option is to use a third party tool that collects the metrics you want to monitor, stores them in a repository and shows historic data for the time period you select. As the history data is stored in SQL tables, you can also easily query the data yourself and create reports.

Such a tool is ApexSQL Monitor, and it has built-in graphs for the last day, week, and month. It will have the reports available soon.

Disclaimer: I work for ApexSQL as a Support engineer


INSERT .. ON CONFLICT DO UPDATE SET error

The excluded record needs to reference columns from the target table, not the source (e.g. a VALUES clause would not have any column names anyway).
It's also good coding practice to always specify the target columns in the INSERT statement.
INSERT INTO the_table (id, first_seen, last_seen, duration)
SELECT ID_tmp, First_seen_tmp, Last_seen_tmp, Duration_tmp
FROM tmp_table
ON CONFLICT (ID)
DO UPDATE
   SET last_seen = EXCLUDED.last_seen,
       duration = EXCLUDED.last_seen - the_table.first_seen;


Help understanding mongodb explain results

I have trouble understanding this because even though the index is used for sorting , shouldn't it have done a collection scan to fetch the documents that needed to be sorted?

If you fully iterate both of your queries they will end up fetching all documents from the collection, but they take different approaches in terms of query planning and execution.

A collection scan means an index was not used and documents were retrieved in natural order (which is the undefined "order of documents on disk"). An index scan indicates an index was used regardless of whether that stage returns some or all documents in a collection.

If you review the full query explain output for a collection scan you should see totalKeysExamined is 0 and totalDocsExamined equal to the number of documents returned.

  db.coll.find().explain('executionStats')

The  winningPlan for your first query should be a simple COLLSCAN stage:

    "winningPlan" : {
        "stage" : "COLLSCAN",
        "direction" : "forward"
    }

  db.coll.find().sort({f1:1}).explain('executionStats')

In your second query example the index supports the requested sort order, so you should see totalKeysExamined and totalDocsExamined are equal to the number of documents returned (assuming that f1 is not not a multikey (array) index).

The winningPlan for this should be an index scan (IXSCAN) which iterates the f1 index in sorted order to fetch matching documents. You should see the IXSCAN as the inputStage for a FETCH:

    "winningPlan" : {
        "stage" : "FETCH",
        "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                "f1" : 1
            },
            ...
    }

If the winning plan did not support the requested sort order, you would still see an IXSCAN or COLLSCAN but it would be part of an in-memory SORT stage (limited to ~32MB of documents):

    "winningPlan" : {
        "stage" : "SORT",
        "sortPattern" : {
            "f1" : 1
        },
        "inputStage" : {
            "stage" : "SORT_KEY_GENERATOR",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    ...
                },
                ...
            }
        }
    }


How can I change the sequence_name parameter after renaming a sequence (PostgreSQL)

Don’t worry about it. The sequence name stored inside the sequence is ignored. This field is gone in PostgreSQL 10.


Unable to create a self signed Certificate for SQL Server 2017(14.x.xxxx)

You need to enclose CertStoreLocation, Subject, DnsName and FriendlyName with double quotes.

There is no need to specify a location as it will default to "Local Computer/Personal/Certificates" where it needs to be in order to use it by SQL Server.

This will generate a valid certificate on Windows Server 2016 that will be usable by SQL Server 2017:

New-SelfSignedCertificate -Subject "CN=insert FQDN here" -DnsName "<insert FQDN here>","insert NetBIOS name here" -FriendlyName "Self Signed Certificate Friendly Name" -KeySpec KeyExchange -KeyLength 2048 -NotAfter (Get-Date).AddYears(5)

Please replace 'insert FQDN here' and 'insert NetBIOS name here' with the actual FQDN and NetBIOS name keeping the double quotes.


Since all your matches have to match the LIKE pattern in order to be included, the simple thing to do is assume that shorter values for the column you're matching are "better" matches, as they're closer to the exact value of the pattern.

ORDER BY LEN(item_nale) ASC

Alternatively, you could assume that values in which the match pattern appear earlier are "better" matches.

ORDER BY PATINDEX('%' + @user_input + '%', item_nale) ASC

Or you could combine the two; look for earliest matches first, and within those, prefer shorter values.

ORDER BY PATINDEX('%' + @user_input + '%', item_nale) ASC, LEN(item_nale) ASC

It's not as sophisticated as what full-text indexing does, and it only accounts for matching on a single column, but it gives decent enough results.


When to use JSONB type for such a column?

The rule of thumb is: if the data are just payload to the database and will never be used in WHERE conditions and the like, use json. Else, use jsonb.


Number of locks acquired during query execution

This is the perfect use for extended events.

-- replace Session_id with your spid
CREATE EVENT SESSION [Specific_Locks] ON SERVER
ADD EVENT sqlserver.lock_acquired(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_escalation(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_released(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_timeout_greater_than_0(
    WHERE ([sqlserver].[session_id]=(55)))
ADD TARGET package0.event_file(SET filename=N'Specific_Locks',max_file_size=(100))
GO

ALTER EVENT SESSION [Specific_Locks] ON SERVER STATE=START
GO

-- run your query

ALTER EVENT SESSION [Specific_Locks] ON SERVER STATE=STOP
GO

This will give you everything your session does, however, you can filter on the object ids for the table and indexes to make it more readable. I included acquired, released, timeout, and escalation as I don't know your environment.

If you want to look at the captured data, there are two built in ways without using outside programs.

Use sys.fn_xe_file_target_read_file() with proper supplied arguments
Use SMSS to open the file target for you and give you a semi-intelligible display (picture below)


Filter extended event xml_deadlock_report by database

I am using Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

SSMS: 13.0.16106.4

I can create a filter for deadlock without any error.

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([database_name]<>N'test')),

ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([sqlserver].[database_name]<>N'test'))

ADD TARGET package0.event_file(SET filename=N'c:\blocked_process.xel',max_file_size=(65536),max_rollover_files=(5),metadatafile=N'c:\blocked_process.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Here is the picture where I am adding filter (if I am using GUI).


Select maintenance subplans via database_id

Maintenance Plans are actually SQL Server Integration Services packages stored in msdb.

You can see the binary package by looking at msdb.dbo.sysssispackages.

You cannot easily modify the package using T-SQL, nor can you easily inspect the package contents to determine which databases are being backed up.

Instead of using Maintenance Plans, it is advisable to use SQL Server Agent in combination with some pre-designed set of scripts, such as those provided by Ola Hallengren or MinionWare


SQL Server Always Encrypted - Create an external table containing an encrypted column

First:

  Before creating a database scoped credential, the database must have a
  master key to protect the credential. For more information, see CREATE
  MASTER KEY (Transact-SQL).

CREATE DATABASE SCOPED CREDENTIAL

So the Database Master Key is used to encrypt the credential.

Second:

  I'd like the [AlwaysEncrypted] column to be unencrypted when querying MyTable from Elastic Database Query.

That is not possible.  AlwaysEncrypted is intended to have the data decrypted only on the client.  Even in the case where SQL Server is the client, there's no way to store the encryption key inside SQL Server and use it in the database engine.  There's no fundamental reason why this couldn't be implemented.  But the main use case for AlwaysEncrypted is storing data in such a way that SQL Server (and its administrators) cannot access this data.


Oracle 11g database archive log got increased suddenly in production server

If what you say is true (no unusal transactions), then I say this is typically caused by Segment(Compression) Advisor going rogue.

Compression Advisor still Generates Redo Log on 11.2.0.2 (Doc ID 1324598.1)

Huge Redo Generation by Compression Advisor (Doc ID 1969765.1)

Sometimes it copies your tables to check if you could reclaim space by reorganizing them, in the maintenance window (nights, weekends). This may generate a high amount of redo and archivelogs depending on the size of the table.

This is the reason I always disable the maintenance task for it (also because I find it useless, and I can run my own diagnosis whenever I need to):

begin
  dbms_auto_task_admin.disable
  (
    client_name => 'auto space advisor',
    operation => null,
    window_name => null
  );
end;
/

You can create an AWR/Statspack report of the problematic time period, it is quite easy to recognize if it was the culprit (CREATE TABLE statements with weird/specific names that was not executed by the DBA/application/regular jobs).


Use partitions across multiple filegroups. Once a section of data (eg, for a month once reconciliation has taken place) must be read only, mark the file group as such.


Installing SQL Server 2014 Enterprise in Windows 7 Ultimate 64 Bit Incompatibility Error

According to Microsoft you have to pay for these versions of their operating system if you want to run their "Enterprise" SQL Server.

Windows Server 2016 Datacenter
Windows Server 2016 Standard
Windows Server 2012 R2 Datacenter 64-bit
Windows Server 2012 R2 Standard 64-bit
Windows Server 2012 R2 Essentials 64-bit
Windows Server 2012 R2 Foundation 64-bit
Windows Server 2012 Datacenter 64-bit
Windows Server 2012 Standard 64-bit
Windows Server 2012 Essentials 64-bit
Windows Server 2012 Foundation 64-bit
Windows Server 2008 R2 SP1 Datacenter 64-bit
Windows Server 2008 R2 SP1 Enterprise 64-bit
Windows Server 2008 R2 SP1 Standard 64-bit
Windows Server 2008 R2 SP1 Web 64-bit
Windows Server 2008 SP2 Datacenter 64-bit
Windows Server 2008 SP2 Enterprise 64-bit
Windows Server 2008 SP2 Standard 64-bit
Windows Server 2008 SP2 Web 64-bit

It also seems to run with the same versions in 32-bit, it just stores them in a separate column and amusing Microsoft can't fill an HTML table properly (nor can they generate Markdown-friendly urls.)
Interestingly, "SQL Server Standard"  runs on

Windows 7 SP1 Ultimate 64-bit
Windows 7 SP1 Enterprise 64-bit
Windows 7 SP1 Professional 64-bit

So the question is
What feature does Enterprise do that Standard doesn't that requires Windows Server 2008 (released in February 27, 2008) instead of the newer Windows 7 Ultimate (released in October 22, 2009)?
I assume nothing, except licensing and marketing wank. It seems you're at impasse, so I suggest upgrading your operating system, and upgrading your database server.
It may also be worth nothing that other people have managed to do this.
Sean Gallardy, whose profile claims insider information,

commented on DBA.se

it may physically install, but it's not supported at all. Kind of like those trying to use wine, etc, to get it to run on Linux ;) I'm also with you, use developer edition if absolutely needed for something like this (if dev applies to the workload). - Sean Gallardy

Of course, details omitted we have no idea why it wasn't supported or what the "serious limits" are (from the context of the conversation).. Betting it's still a hold up for more cash.
Perhaps you can find a new version of Enterprise without this software-imposed restriction.


Unable to connect oracle as sysdba tables have been dropped

This is probably as complete a way of killing an Oracle database as you could wish for. The sys tables contain all the metadata about every object in the database -- objects, segments, extents ... so the database now contains no information on what user tables it stores, including the tables that store the data about that.

New database, I think.

And no more sys connection accidents.


Oracle: How can I track the progress of an index rebuild?

You should Query the V$SESSION_LONGOPS

select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$session_longops ops
where ops.sid=&your_sid
and ops.serial#=&your_serial
and time_remaining>0;

If the index rebuild is parallel, than you you must find the parallel slaves that do the work.
This can be done by querying the V$PX_SESSION. Your session that issued the 'alter index' statement is the query coordinator

select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$px_session par, v$session_longops ops
where par.qcsid=&your_sid
and par.qcserial#=&your_serial
and ops.sid=par.sid
and ops.serial#=par.serial#
and ops.time_remaining>0;

But an index creation consists of more than one longops (scan the table, sort the data,...)


I was able to figure this out by just using multiple CTE's, then selecting what I needed to combine.


How to run recurring tasks on Postgresql without an external cron-like tool?

Even if you were running the soon-to-be-released (at time of writing) PostgreSQL 10 or the current PostgreSQL 9.6 not an ancient release like 8.3, there's still no built-in task scheduler.

Something like PgAgent or external cron jobs is required, there is no convenient workaround.

The background workers feature introduced in 9.3 should hopefully permit a tool like PgAgent to be moved into the PostgreSQL core in a later release, but it hasn't been done yet. Even on 9.3 you still have to run cron or pgagent.

A few people are working on background worker based schedulers, and there are some patches coming that should provide facilities to help with that. But as of PostgreSQL 10 there's still no good quality, widely adopted scheduler, and most people use cron / ms task scheduler / etc.

Please take a look at the version policy, too; you're running an obsolete and unsupported release.


Good reason to convert LONGTEXT column to smaller width if field is smaller?

Let's do the math.

6500 characters, even in CHARACTER SET utf8mb4 takes no more than 26000 bytes.
TEXT has a limit of 64K bytes and needs a hidden 2-byte length field.
LONGTEXT has a 4-byte length field.
Let's say (for the 'math') that the average row length, including this text, is 3000 bytes.

Math...

Savings of switching from LONGTEXT to TEXT: 2 bytes per row
Percentage space savings:  2/3000 = 0.06%
Performance improvement:  Less than that.

Conclusion...  We have spent much more effort than this minuscule improvement is worth.  (But you probably learned some details.)


maybe:
with latest as (
select element_name, element_sequence, max(creationdate) max_date from table group by element_name, element_sequence
)

select * --your column list
from table t
join latest lt on (lt.element_name = t.element_name and lt.element_sequence = t.element_sequence and lt.max_date = t.creationdate)

or
 select * from
 (
 select element_name, element_sequence
, row_number() over (partition by element_name, element_sequence order by creationdate desc) lp -- you can add other columns
  from table
  ) d
    where lp = 1


Oracle - Is a separate database more secure than using the same database and a separate schema

Management wants to split the production database which uses Oracle Apex because the Apex application will be on the public internet and therefore security is an issue.

If Management don't want the database server that hosts the Apex application on the "public internet" then that's what they want.  That database server will move to somewhere "inside" the Firewall.

Is a separate database more secure than using the same database and a separate schema when using the Oracle database?

From an Information Security stand-point?  Almost certainly.
Machines tend to be compromised, not Applications.
If this database server were to be compromised then the attacker would presumably target the root account and thereby gain access to any other account on the machine.  If, like most other DBAs, you've allowed O/S authentication for your "oracle" account, then the whole database is vulnerable.
From a cross-application perspective?  Not if you've set up your security model properly.
You can (and should) set up Roles that grant specific permissions on specific objects and grant these Roles to the Application Accounts that need to use them.  In that way, Application A can access the tables in Schema B that it needs to use but nothing more.
REST and other API-based strategies move this into the Application layer, where Developers are move "comfortable", but even these still need access to database objects so the creation/granting of Roles still applies.


Searching for a keyword across multiple columns

You could write a simple query using OR in WHERE clause and passing your search string.

SELECT school_name
FROM schools
WHERE Subject1 = 'Physics'
OR Subject2 = 'Physics'
OR Subject3 = 'Physics'
OR Subject4 = 'Physics'

You can parameterize the search string and pass on to this query.


What can cause an Index "total writer" to be the same in every index?

Unless an index is filtered, they should all be written to the same number of times under normal DML activity.

If a row is updated or added, any columns in any index must also be added/updated.

So you should not expect some indexes to be written to a different number of times, unless you are reorganizing or rebuilding individual indexes specifically.

Also, please read this post over on SQLPerformance.com:

Don't just blindly create those "missing" indexes!


Storing created/retired dates for a large amount of different entities

It seems that you are involved in a project that requires the creation of a temporal database. You may as well find related information by searching for the terms auditable databases and database history tables.
I deem this exceptional Stack Overflow answer as the top material with respect to these topics. In such a post, @PerformanceDBA models an auditable relational database for a very interesting business environment, and the instructiveness contained in there encompasses multiple aspects that are pertinent not only to these themes, but also to database design and practice as a whole.
Background
As you know, a database is built to retain information that is relevant to its users, and information, naturally, can change as time passes. In this way, the values contained in a specific database can suffer successive modifications and, as a consequence, go ceasing to be “current”, but these circumstances do not imply that the previous “states” of the values in question become irrelevant after having undergone their corresponding chronological updates.
In this regard, yes, there are cases in which keeping track of the updates that affect an entity over time is paramount, and there are other cases where changes should be expressly forbidden and prevented, hence the retention of entity alterations would not apply. It may seem a cliché, but these points depend on your exact informational requirements, therefore you have to analyze each particular situation thoroughly so that you can define how to proceed. Then, once “audit trail” has been determined valid and necessary, it must be implemented.
Suggested approach
1. Illustrative IDEF1X diagram
Let us take the Truck entity type as a reference since, in accordance with your specifications, it is a good example of an aspect that entails enabling temporal capabilities. In order to illustrate the approach that I am going to propose to construct said capabilities, I have prepared an IDEF1X1 diagram that is shown in Figure 1 (and you can download it as a PDF from Dropbox, as well):

As demonstrated in the aforesaid diagram, apart from depicting the Truck and User entity types, I have included an additional one that represents the History of Truck (denominated, accordingly, TruckHistory).
The only difference between the Truck and the TruckHistory entity types is the PRIMARY KEY of the latter, since it consists of TruckNumber and a complementary property called AuditedDateTime which, of course, indicates the particular point in time when a given Truck occurrence was “audited” (or updated). It is important to note that TruckHistory.TruckNumber is defined as a FOREIGN KEY that points to Truck.TruckNumber, depicting the type of association that takes place between these two entity types.
2. Resulting expository SQL-DDL logical layout
Taking the present example to the logical level of abstraction, I have derived the following DDL statements from the IDEF1X diagram presented above:
CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        BirthDate,
        GenderCode
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE Truck ( -- Contains the “current” versions.
    TruckNumber     INT      NOT NULL,
    OtherColumn     CHAR(10) NOT NULL,
    IsRetired       BIT      NOT NULL,
    CreatedUserId   INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Truck_PK              PRIMARY KEY (TruckNumber),
    CONSTRAINT TruckToUserProfile_FK FOREIGN KEY (CreatedUserId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE TruckHistory ( -- Holds the “past” versions.
    TruckNumber     INT      NOT NULL,
    AuditedDateTime DATETIME NOT NULL,
    OtherColumn     CHAR(10) NOT NULL,
    IsRetired       BIT      NOT NULL,
    CreatedUserId   INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT TruckHistory_PK              PRIMARY KEY (TruckNumber, AuditedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT TruckHistoryToTruck_FK       FOREIGN KEY (TruckNumber)
        REFERENCES Truck (TruckNumber),
    CONSTRAINT TruckHistoryToUserProfile_FK FOREIGN KEY (CreatedUserId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT DateSuccession_CK            CHECK       (AuditedDateTime > CreatedDateTime)
);

Sample data
Having enabled the logical design previously exposed, say that we are keeping the next two rows in the UserProfile table:
+-——————-+-—————————-+-————————-+-——————————-+-——————————-+-———————————————————————-+
| UserId | FirstName | LastName | BirthDate  | GenderCode | CreatedDateTime         |
+-——————-+-—————————-+-————————-+-——————————-+-——————————-+-———————————————————————-+
|      1 | James     | Smith    | 1985-06-30 | M          | 2013-02-12 07:32:04.000 |
+--------+-----------+----------+------------+------------+-------------------------+
|      2 | Nicole    | Johnson  | 1987-10-14 | F          | 2013-03-24 09:02:03.000 |
+--------+-----------+----------+------------+------------+-------------------------+
Then, suppose that the Truck table contains the (“current” or “present”) row that corresponds to the Truck identified by TruckNumber 1750 as displayed bellow:
+-———————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
| TruckNumber | OtherColumn | IsRetired | CreatedUserId | CreatedDateTime         |
+-———————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
|        1750 | Bar         | False     |             2 | 2015-06-30 16:58:12.000 |
+-------------+-------------+-----------+---------------+-------------------------+
And, finally, assume that the Truck identified by TruckNumber 1750 holds the TruckHistory rows that follow:
+-———————————-+-———————————————————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
| TruckNumber | AuditedDateTime         | OtherColumn | IsRetired | CreatedUserId | CreatedDateTime         |
+-———————————-+-———————————————————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
|        1750 | 2013-12-10 17:05:01.000 | Foo         | False     |             1 | 2013-06-30 10:34:12.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2014-03-22 14:08:08.000 | Bar         | True      |             2 | 2013-12-10 17:05:01.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2014-09-14 09:45:06.000 | Bar         | False     |             2 | 2014-03-22 14:08:08.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2015-06-30 16:58:12.000 | Foo         | False     |             1 | 2014-09-14 09:45:06.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
Advantages
Thus, in the TruckHistory table, there is a time series made up of each and every one of the changes related to the Truck identified by TruckNumber 1750. One can know

who are the Users that made such changes (via CreatedUserId);
the exact moment in which the values associated with these changes began to be “current” or “present” (by way of CreatedDateTime); and
the specific instant in which a certain row was modified receiving new values in the “current” Truck table (through AuditedDateTime).

I consider that this configuration is much more advantageous than maintaining only the most recent Date in which each instance of a Truck was updated along with the Identifier of the User who performed the respective update operation but, at the same time, losing every one of the previous entity “states”.
Data manipulation considerations
From a data manipulation point of view, this implies that every time that (1) a precise Truck row suffers an UPDATE, you also have to (2) INSERT into the TruckHistory table the corresponding Truck values that were “current” until the aforementioned Truck UPDATE took effect. I would decidedly carry out these operations with the aid of ACID TRANSACTIONS, so that they are either succeed or fail as a single Unit of Work.
Similar scenario
I do not know how the post revision history is set up by the Stack Exchange team throughout the network, but it provides a functionality that I find very similar to the one offered by the approach I suggest. It would be helpful to examine closely this Stack Exchange process so that you can get a broader perspective about the ways in which scenarios of this nature can be managed.

Endnote
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model of data, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?

Oracle has had the capability of executing external code from SQL for a long time - EXTPROCs, data cartridges, and so on. You say

  someone with access to the OS (or remotely....) could exploit it to
  cause the database to get compromised

But what does this even mean? Someone with access to the OS as the oracle user can access your DBFs directly (they're just files on the disk), can attach directly to the SGA, can make a backup and copy it off, can snoop the network traffic (as root). In the case of a malicious developer, the can do whatever they want in PL/SQL and wrap it. I don't see how you are introducing a new vulnerability by using this feature. If it makes your job or your user's jobs easier, go for it.


How would you design a table for a booking system?

If the concern is manual maintenance of a large number of rows, you could solve this (potentially) by creating tables for each of your dimensions: weeks, time slots, and venues.  The complete set of possible slots for booking would be the cross product of these three dimensions. Actual bookings would be another table with foreign keys pointing to all three of these dimension tables.

With this type of design, instead of maintaining 3 x 25 x 26 records you will maintain 3 + 25 + 26 records.  Note that if you segregate day of week and time of day into two tables you can reduce the number of records to be maintained even further (3 + 5 + 5 + 26).

The problem with this approach is when (and if) you have an exception.  This design assumes that there are no blackouts in your schedule.  For example, what if you close a room for a couple of weeks to be renovated?  One way to handle this issue is to create booking records that cover the blackouts.  If you have enough exceptions, then managing them may be almost as bad as just using the brute force method.

The question I would seriously consider is whether or not generating the initial list of slots available for booking is really that big a deal.  You could easily automate the  process to generate your big pile of available slots.  This is really just a single query.


Do locks on readonly secondary databases propagate to the Read/Write Database

AlwaysOn secondaries map all reads to versioned reads, which are (almost) lock-free
Even with versioned reads there are some (few) locks in place, eg. schema-stability locks on obejcts. There is never any sort of propagation of locks from secondaries to primaries. Furthermore, there cannot be any propagation of locks from secondaries to primaries simply because the secondary is a back-in-time copy of the primary. There is no point in propagating a lock on object 'foo' from secondary to primary because the primary may had dropped 'foo' 2 hours ago and the secondary did not catch up yet. The propagation of locks downstream from primary to secondary goes through the log records and as such the locks are re-acquired on the secondaries when the log is redone, therefore the lock is always applied to the appropriate structure (if 'foo' existed on the primary when the lock was acquired, then by definition exists on the secondary at the moment the lock is re-played).
Secondary reads may block redo (eg. by holding on a schema-stability lock when the redo is trying to acquire a schema-modification). The redo resumes when the secondary reads releases the lock. And 'redo' in this context means 'applying log from the primary on the secondary', ie. catching up.


Adding new columns to Table with Clustured Columnstore Index

What happens when I add another 50 columns to the table?

These columns will be added to the table with NULL values if you have not specified a default value. If you have added a default value, the compressed rowgroups will stay as they are and no updates will be done.

Will they automatically be added to the CCI?

Yes

What should be the best way to handle such conditions?

If you are going to update all the values of the new columns, you will get a lot of compressed row groups with more deleted rows and open delta stores (heaps), when doing a massive update the tuple mover will kick in and turn these delta stores into compressed segments if they are marked as closed (size 1 000 000 rows). To get rid of the compressed segments with only a few rows left you will have to do a REORGANIZE statement

Additional Information

--> You need 1 000 000 rows at least to get CCI to work well, otherwise you will be stuck in your heap delta store. (you only have 400 000)

This will require you to do a rebuild or reorganize of the index to get all rows into a compressed segment

ALTER INDEX idx_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

You should also upgrade you SQL Server 2016 to SP1

Try this

Because you do not have so many rows in your Fact table, it might be better to use the NCCI trick. You can create an empty filtered nonclustered columnstore index on your table. This will have a zero cost on your table, but will trigger the batch mode executions plans which will give you a lot of gains for analytical queries.

CREATE NCCI_TEST on TESTABLE (ALL COLUMNS)
WHERE ID = -1 and ID = -2

Examples

CREATE TABLE dbo.test
(
Bla datetime,
blabla varchar(200)
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TEST on DBO.TEST
GO

And then insert some rows

INSERT INTO dbo.test
select DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),cast(ABS(CHECKSUM(NEWID())) as varchar)
GO 10000

insert into dbo.test
select * from dbo.test
GO 5

If you then go and check your columnstore elements

select * from sys.column_store_row_groups

You see that you now have a delta store, which means this is a heap table without indexes.

If you then rebuild your index with the compress all rowgroups

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

The tombstone elements are the previous deltastore/compressed segments which still linger they will be removed after about 5 minutes

You now have your columnstore compression (which will avoid locking issues)

If we now add a new column

alter table dbo.test add nondefaultvalue varchar(20)

And we check the row groups again

you still have the good layout of your row groups.

If we add another column with a default value and check the rowgroups

alter table dbo.test add defaultvalue varchar(20) not null default('DEFAULT')

If you then however decide to update those values.

UPDATE dbo.test
set nondefaultvalue = 'DEFAULT'

Things change

We no longer have a compressed segment we can use. If we then try to reorganize this index

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE

We get our open delta store again, which will cause locking.
We can however get it back to a compressed segment by issuing the following command we saw previously

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

Now we have to good columnstore segment again.

So in your case you will have to do a reorganize with compress all row groups, to get it working fine. This should not take long on a table with only 400 000 rows but it is necessary to avoid locking.

If you do bulk inserts the story changes again, but you will have to do 100 000 rows at once to get everything straight into a compressed segment.

Dictionaries

When adding another column a new dictionary will be made for your columnstore index (sort of mapping to the column elements inside your columnstore segments).

select * from sys.column_store_dictionaries

alter table dbo.test add defaultvalue2 varchar(20) not null default('DEFAULT')

select * from sys.column_store_dictionaries

For more information I suggest you go to http://www.nikoport.com/ he has all the extra information you need on columnstore on his blog.


SSRS Linked Report with allow multiple parameters

I' pretty sure you actually have to use a function for that.

From a quick google search, here are a few options:

=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

Or

=join(Parameters!<your param name>.Value,",")

One thing I remember vividly from my SSRS/ReportBuilder days was that when you're passing the value in from the main to the sub report (and it has multiple values), you pass the param in as "[@paramName]" and not "Parameters!.param.Value"


I noticed that my assumption that multiple inner joins should just take the sum in time of the individual joins is apparently not true.
I got the query up to a decent speed by preprocessing the parts to be joined, so a more efficient join strategy can be used:
SELECT Websites.*,
       L1.label1,
       L2.label2
  FROM Websites
       JOIN
       (
           SELECT Websites.*,
                  Label1.label1
             FROM Websites
                  JOIN
                  Label1 ON (Websites.protocol REGEXP '^' || Label1.protocol_re || '$' AND
                             Websites.domain REGEXP '^' || Label1.domain_re || '$' AND
                             Websites.path REGEXP '^' || Label1.path_re || '$')
       )
       AS L1 USING (
           protocol,
           domain,
           path
       )
       JOIN
       (
           SELECT Websites.*,
                  Label2.label2
             FROM Websites
                  JOIN
                  Label1 ON (Websites.protocol REGEXP '^' || Label2.protocol_re || '$' AND
                             Websites.domain REGEXP '^' || Label2.domain_re || '$' AND
                             Websites.path REGEXP '^' || Label2.path_re || '$')
       )
       AS L2 USING (
           protocol,
           domain,
           path
       );

It may not look like much, but it works very well.


The problem here is that / operator in integer division truncates the result, check  Mathematical Functions and Operators

Try with:

SELECT *,(fatalities::float/population)*100000 AS rate FROM all_states;

Or

SELECT *,(fatalities::numeric/population)*100000 AS rate FROM all_states;


an element with the same SQL SERVER key has already been added

use UNION instead of UNION ALL in this way:
SELECT DESCRIPCION AS nodeText,'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS
nodeKey,''AS nodeParentKey FROM DEPARTAMENTO
UNION ALL
SELECT DESCRIPCION AS nodeText,'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,
'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey FROM PROVINCIA
UNION
SELECT DESCRIPCION AS nodeText,'DIST' + CAST(IDUBIGEO AS VARCHAR) AS nodeKey,
'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo

it will remove duplicates


drop table if exists test;
create table if not exists test(val varchar(255));

drop table if exists log;
create table log(id int auto_increment primary key,
                 val varchar(255),
                 summ int );

insert into test
values
('{"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50","neckdf":"0","thiddgh":"0"} '),
('{"shoulder":"0","bustfd":"10","waisdsdt":"40","sdhip":"10","nsdfeck":"0","thigfsdh":"0"} '),
('{"back":"0","bust":"40","waist":"35","sdfhip":"22","necsdk":"10","thigh":"0"}');

drop function if exists my_sum;

DELIMITER @@;

create function my_sum(s text)
returns bigint deterministic
begin
set @sum := 0;
while locate('"', s) do
-- debug 1, remove after debugging
    insert into log(val, summ) select s, @sum;
    set @sum := @sum + substring_index(substring_index(s, '"', 4), '"', -1);
-- debug 2, remove after debugging
    insert into log(val, summ) select s, @sum;
    set s = substring(s FROM 2 + length(substring_index(s, '"', 4)));
-- debug 3, remove after debugging
    insert into log(val, summ) select s, @sum;
end while;
return @sum;
end;
@@;

DELIMITER ;

select val, my_sum(val) from test;

drop table if exists test;
drop function if exists my_sum;

select * from log order by id;
drop table if exists log;


In PostgreSQL, what is the behavior of the bit(n) -> "char" cast?

That's because the cast directly to "char" without going through int first is taking the first character from the stringification of the bitfield.

SELECT 65::bit(8);
   bit
----------
 01000001
(1 row)

SELECT '01000001'::"char", '0'::"char";
 char | char
------+------
 0    | 0
(1 row)

If that was greater than 128, you'd have a one in that position,

SELECT 128::bit(8);
   bit
----------
 10000000
(1 row)

Casting that to "char" would give you a 1 rather than a 0,

SELECT 128::bit(8)::"char";
 char
------
 1
(1 row)

Keep in mind, "char" is signed, so the range is [-128,127]. For this reason you can't actually go from bit(8) to "char" without first shifting those bits. For example

SELECT 128::bit(8)::int::"char";
ERROR:  "char" out of range

See also

How exactly does the one-byte “char” type work in PostgreSQL?


Should I convert empty varchar values to NULL?

As a DBA, your sacred duty is to take whatever data was entrusted to you, and leave it as is.

A NULL in a field usually means "DATA was not entered" or "DATA is not applicable" (but can have other meanings - it is context specific), and may actually MEAN something to the business layer. An empty string may actually be what the user entered and WANTS. Of course, I am generalizing - possibly the reason you get either NULLs or '' (empty strings) is due to some sloppy coding in the business layer (let us hope not, though).

Either way, have a chat with whoever provides the Middle Tier and see what they are using NULL vs Empty String for - and then go from there. Maybe they are thinking that they always pass in an Empty String (or always NULLS) and are not aware of the randomness (in which case advise them on what you think should be the standard), or maybe NULL really is information to them


MySQL trigger: Compare value in another table

DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    DECLARE found_it INT;
    IF (NEW.post_type != 'revision' AND NEW.post_status != 'auto-draft') THEN
        SELECT COUNT(1) INTO found_it FROM some_other_table
        WHERE post_type = NEW.post_type;
        IF found_it = 0 THEN
            INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
        END IF;
    END IF;
END;

If found_it = 0, then the row does not contain a value matching NEW.post_type.

Give it a Try !!!


How to design functionality to comment about a user joining a Facebook group?

I think if you want to be able to comment on someone joining a group, then "join a group" must be an entity that can be referenced by other records. You might want to have another "parent_type" called "event" and one of the event types would be "joined a group". That would let you reference it from your comments table.


Heap-Organized Tables and 'Heap' the Data Structure

In computing generally a "heap" is just a general dumping ground, available for allocation then a block of memory/disk is needed. See https://en.wikipedia.org/wiki/Memory_management#HEAP for a lot more detail.

The data structure called a heap can be used to manage the allocation of space, or to operate as a priority queue, amongst other more minor uses. See https://en.wikipedia.org/wiki/Heap_(data_structure)

In a database, and more generally, a storage heap may be managed by a head-based data structure, though that does not have to be the case: the names being the same does not imply that they are in any way mutually inclusive.


Batch process to delete table from SQL Server backup files

No, you can't edit the contents of backup files in this way.  You should either compress the backup files or archive them to a different server or storage medium if you require them to be kept.


How to create month ranges without using a specific date in Postgresql

This is the way I chose to get 1st and end of the month, without using current date, but rather the creation date (of an order) in my data. Please suggest any other query that might seem more effective or if you feel there is a flaw in this query:

select distinct creation_date::date,
       (creation_date - Interval '1 months')::date as Minus_1M,
       (creation_date - Interval '2 months')::date as Minus_2M,
       (creation_date - Interval '3 months')::date as Minus_3M,
       extract('day' from creation_date) as Day_of_month,
       date_trunc('month', (creation_date::date - extract('day' from
       creation_date)::int))::date as Beginning_of_month,
       creation_date::date - extract('day' from creation_date)::int as End_of_month
from order_data
order by 1 desc;


How to combine these two queries and have them be as fast?

Would help if you post SHOW CREATE TABLE for the two tables and also how many rows each table has.

Most likely the slowness is due to use of LIKE "%Smith,John%". This will do a full table scan on tbl_employee.

Try LIKE "Smith,John%" just to see if the query performance is any better this way. I am assuming you have an index on tbl_employee.name column.


If you find yourself saying "nullable columns" before you have written code I generally think you need to normalize more(as always, it depends.)

If you want one system to maintain, the last option seems the most relevant, but "splitting them into two tables" is not really where you would want to take it.

if we want to track basic things like Players, Games, and Sport type you would simply add intersection tables between the relevant things you want to store.

Players(playerid, player information) -> PlayerGames(playerid, gameid) <- Games (gameid, game information)
^
|
PlayerSport (playerid, sportid)
|
v
Sports (sportid, sportname)

This means we could count all of Michael Jordan's games by sport(as he played multiple) with a query something like:

select count(*), sportname
from Players p
join PlayerSport ps ON ps.playerid = p.playerid
join PlayerGames pg ON p.playerid = pg.playerid
where p.name='Michael Jordan'
group by sportname


Database schema design implementation feedback

Ok, given there are no restrictions on the behavior of someone asking a question, we can forgo a lot of trouble migrating the UserId through the data model.
I don't think you're too far from the mark, just some unnecessary Id columns that aren't providing value.
This should satisfy the requirements as you've described them:

Some notes:

I changed Like to Vote since that gives more flexibility.
If we do need to restrict the behavior of the person asking the question, we would need to use their UserId in the primary key, or change the primary key from QuestionId to something like (UserId,AskDatetime) or (UserId,QuestionNumber).
If you do choose to allow more than one answer per question, you would change the primary key of Answer to (QuestionId,UserId) (although you might want to rename UserId to something like AnswererUserId).  To denote an answer as accepted, you'd need to create an AcceptedAnswer table where QuestionId is the primary key and AcceptedAnswer would have the relation to LiveStream.
The one 'oopsie' that could occur with this data model is logging that the presenter (the person who answered the question) also attended their own Livestream.  If you were doing attendee counts, you'd need to query Answer to make sure you don't count them.  If that is very mission critical, you'd move the UserId of the answerer to the primary key of Answer and migrate that through LiveStream to LiveStreamAttendee and place a check constraint to ensure the UserId of the attendee is not the UserId of the answerer.


Avoiding repetition without creating a view

You can use Common Table Expressions for this:

WITH q1 AS
  ( SELECT ... )
SELECT *
  FROM q1
UNION
SELECT *
  FROM (some query that uses q1 outcome)
;

or even:

WITH q1 AS
  ( SELECT ... )
TABLE q1
UNION
SELECT *
  FROM (some query that uses q1 outcome)
;

You can also have more than one CTEs used in sequence:

WITH q1 AS
  ( SELECT ... )
  , q2 AS
  (some query that uses q1 outcome)
  , q3 AS
  (some other query that uses q1 and/or q2 outcome)
SELECT *
  FROM q1
UNION
SELECT *
  FROM q2
UNION
SELECT q3.*
  FROM q2 JOIN q3 ON ...
;

No view created and no running twice of the Q1 query - well, that's implementation dependent. What path of execution is actually followed depends on the optimizer and several other factors. The query is more compact and elegant but it doesn't mean it's more efficient than expanding it.


Constraint on existing table - unique combination of values only when another column is null

Blatantly copied from A-K's answer in the similar question (Can I add a unique constraint that ignores existing violations?) and adjusted for this one.

It assumes there is an ID column with a unique constraint:

ALTER TABLE dbo.Example
ADD IgnoreThisDuplicate integer NULL;
GO
UPDATE E
SET IgnoreThisDuplicate = ID
FROM dbo.Example AS E
WHERE EXISTS
(
    SELECT *
    FROM dbo.Example AS P1
    WHERE
        P1.Username = E.Username
        AND P1.Gateway = E.Gateway
        AND P1.Code = E.Code
        AND P1.ActiveTo IS NULL
        AND E.ActiveTo IS NULL
        AND P1.ID < E.ID
);
GO
-- This constraint is not trusted
ALTER TABLE dbo.Example
WITH NOCHECK
    ADD CONSTRAINT CHK_Example_NoNewDuplicates
    CHECK (IgnoreThisDuplicate IS NULL);
GO
-- Applying the constraint to the table above would ignore the two user1 duplicates.
GO
SELECT * FROM dbo.Example;
GO
CREATE UNIQUE INDEX
    UNQ_Example_UniqueNewUsernameGatewayCode
ON dbo.Example
    (Username, Gateway, Code, IgnoreThisDuplicate)
WHERE
    ActiveTo IS NULL;

Test it out: db<>fiddle demo


Calling another function inside a postgres function

Your syntax is twisted in several ways. If the return type of function A is defined as RETURNS TABLE (id int, address text), it could work like this:

Select A.address, B.name
FROM   f_a() A
JOIN   B USING (id);

Aside: While your identifiers look alright for the purpose, I strictly use legal, lower case identifiers to avoid complications.


SQL Server 2016 still uses mixed_extent_allocation in TempDB

According to this post, IAM pages are still allocated from mixed extents, even in SQL Server 2016.
So this will be the mixed_extent_allocation I saw in my trace.

The blog post by Paul Randal provides some more internals explanation.
Thanks to Scott Hodgin for pointing me to the article.

I did soms more research about the content of de pages allocated in the first SGAM page and found out that SQL Server also uses mixed extents for system tables (object_id lower than 100).


Performing SELECT on EACH ROW in CTE or Nested QUERY?

Let me know if this helps.  This is using MSSQL (T-SQL) syntax so you might have to adjust it for postgres, but, using a temp table called #tree, containing the two columns id and parent and populated as your example is with

SELECT 1, 0
UNION
SELECT 2, 1
UNION
SELECT 3, 1
UNION
SELECT 4, 2
UNION
SELECT 5, 2
UNION
SELECT 6, 4
UNION
SELECT 7, 6
UNION
SELECT 8, 6:

with cte as
(
    select id as currentnode, id as root
    from #tree
    union all
    select t.id as currentnode, cte.root
    from #tree t join cte on t.parent = cte.currentnode
)
select *
from cte
order by root

The above recursive CTE will yield all nodes reachable starting from some root.  You can do:

SELECT root, count(*)
from cte
group by root

The count(*) query should yield,

root   |  count
----------------
  1    |    8
  2    |    6
  3    |    1
  4    |    4
  5    |    1
  6    |    3
  7    |    1
  8    |    1

You could then subtract 1 from each of those to get the result you were hinting at.  Is this what you were looking for?  Even if not, I feel like a recursive CTE can probably help with what you need.


MySQL slow importing on hyper-v virtual server compared to host

Turning on: "Turn off Windows write-cache buffer" solved the issue for me.
(no restart required for me)

Benchmark write speed, before and after the setting.
dd if=/dev/zero of=/tmp/test2.img bs=512 count=1000 oflag=dsync


Transforming start and end times to simple graph

I've upgraded and worked a bit with CTEs. The following query runs in about half of the time.

WITH RECURSIVE tstamp_cte AS (
    SELECT 0 AS n, CURRENT_TIMESTAMP AS tstamp FROM DUAL
    UNION ALL
    SELECT n + 1, tstamp - INTERVAL 15 MINUTE FROM tstamp_cte WHERE n < 96
)
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE starttime < tstamp AND endtime >= tstamp OR endtime IS NULL) AS 'sessionCount'
FROM tstamp_cte