Lookup tables

I believe this to be where a table refers to a second table, where many rows/records in the first table share identical fields/column data. In the simpler cases, this table may be replaced by an ENUM type on the original table.

Example, where many users can have the same job title, the "Job Title" table is identified as a lookup table and the "Users" table is identified as the data table (see below):

Users >- Job Title

Transactional / Junction / Many-to-Many tables

I believe this to be where a many-to-many relationship occurs between two tables, and this transaction/junction table resolves this relationship.

Example, where many developers may be working on many projects, the "developer_project" table is identified as the transaction/junction table, and the "Developer"/"Project" tables are indentified as data tables (see below).

Developer -< developer_project >- Project

Data/Entity table

I am pretty much making this one up, but where a table is able to exist on its own, irrespective of other tables I consider this to be primarily a data storage table as opposed to a structural table. It may use lookup tables as part of the normalisation/optimisation process, but this is incidental.

I have seen the term "weak" entity floating around and it may/may not apply here. I need to do further reading to be sure.

Example, "staff" and "building" may exist independently of any other tables whereas transactional or relational tables/entities may not:

Staff
Building

Self-Referencing Table

A self-referencing table has a foreign key to itself, creating a hierarchical structure.

create table employee (
  id serial primary key,
  name text not null,
  supervised_by int null references employee(id)
);

Closure Table

History Table

Subtype/Supertypes

Represents concrete entities and their abstraction. For example, it is wise to abstract an Individual and an Organization into a Party type, so that you can easily add either as a customer on a sales order line item.

Single Table Inheritance:

create table party (
  id serial primary key,
  party_type int not null references party_type(id),
  organization_name text null,
  individual_first_name text null,
  individual_last_name text null
);

Class Table Inheritance:

create table party (
  id serial primary key,
  party_type int not null
);

create table organization (
  id int primary key references party(id),
  name text not null
);

create table individual (
  id int primary key references party(id),
  first_name text not null,
  last_name text not null
);

In data warehousing:

Fact Table

Represents a process or event to be analyzed, and usually contains (unenforced) keys to dimension tables, as well as some numeric measures.

create table sales_item_fact (
  order_id bigint not null,
  order_line_item bigint not null,
  calendar_key int not null,
  customer_key bigint not null,
  quantity_sold int not null,
  unit_price numeric(19,4) not null
);

Dimension Table

Describes a dimension of a fact table, often with hierarchical attributes. For example you can analyze (break apart) dates into their component pieces to make querying easier.

create table calendar (
  id int primary key,
  date_iso date unique,
  year int,
  quarter_of_year_int,
  month_of_year int,
  ...
);

Aggregate Table


SQL Server database has very small backup

As Paul Randall stated in his MCM Internals on Backup/Restore for SQL Server, the backup file does not require the empty space in the database to be in the backup, but it DOES require it when restoring the backup.  The backup file basically puts pointers to where the empty space needs to repopulate when it rebuilds it during the restore process.

Database compression works differently than removing empty space from backups.  It uses something closer to a winzip algorithm.  If you were to winzip/winrar/7zip with maximum compression your backup, you would get very similar space usage as SQL Server native compression.  One of the benefits of using it native in SQL Server is it requires a lot less space to write to disk thus it's faster assuming your CPU isn't pegged.

Mr. Denny is also correct, so that could be part of it hence why I voted it back up.

Paul Randal's MCM video list including backup/recovery as mentioned:
http://www.sqlskills.com/T_MCMVideos.asp

EDIT:
After re-reading your answer there's some discrepancies I want to clarify, but the information stated above is still relevant.

You have a database that when you right click on the DB, properties, and space used, you see:
Free Space: 300MB
SP_HELPDB shows: 216GB

run:
dbcc sqlperf('logspace').  What do you get back for the log life related to this DB?  Please post your findings.

Please validate and get back to us.

/*
10/29/2012 Edit after posting findings & using block comment code to drive Buck Woody crazy:

Next, please run this, it will tell us everything about your backup right when SQL Server took it.  This modified script originally from Pinal will help us validate if something after the backup happened to the file, or if SQL Server really just spit out a small backup:
*/

USE [DatabaseName]

GO

SELECT TOP 100

s.database_name,

m.physical_device_name,

CAST(CAST(s.compressed_backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS CompBkSize,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

WHERE s.database_name = DB_NAME() -- Remove this line for all the database

ORDER BY s.backup_start_date DESC, s.backup_finish_date

GO

Edit after final results were posted:
As suspected, your backups have compression enabled.  As you can see in the query, Compressed backup size shows 38GB where as just 'backup size' shows 228GB. Review your backup scripts, you will find 'WITH COMPRESSION' in it.

Remember the factors that affect backup size:
-Transaction Log files are backed up along the SQL Server backup file.  This is for redo/undo operations during the recovery portion.

-Data pages, GAM, SGAM, etc. are to the backup file. Empty space in the database is removed and only pointers are placed, thus if you have lots of free space your backup will remain small.  However it will be required when you restore.

-Compression plays a huge factory typically in SQL Server databases.  One of the biggest contributing factors.

-Breaking up a backup file into smaller multiple files obviously will also affect size.

Let us know if you have any questions but your original question regarding the small backup size is resolved.


Optimizing a simple query on a large table

It would run faster with InnoDB,

The PRIMARY KEY is 'clustered'; this makes the lookup into name faster.
A suitable setting of innodb_buffer_pool_size of, say, 5G (for your 8GB machine) would cache a lot of stuff, thereby minimizing the I/O for 10K operations.

You could also shrink the data (and decrease the I/O) by using MEDIUMINT UNSIGNED or SMALLINT UNSIGNED where appropriate.

There are redundant indexes that could (should) be removed; this would speed up the load.  Also name(6) is probably useless.


Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate?

There is a bug that aggregates the time in a parallel operation.  This is fixed in 2014.
The total_elapsed_time will be correct for a particular parallel query in a batch until it moves on to the next statement in the batch, then the total_elapsed_time will explode by the DOP.
Example
Run this in one query window:
USE AdventureWorks2012
GO
SELECT *
FROM Sales.SalesOrderDetail sod
JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

waitfor delay '00:00:15'

Run this in a second:
select
    DATEDIFF(ms, start_time, getdate()) ActualElapsedTime,
    total_elapsed_time from sys.dm_exec_requests
where session_id = <your session_id for the above batch>

The two values will be close to identical until SQL Server moves to the WAITFORDELAY statement, then you should see the total_elapsed_time explode (assuming the first query has a parallel plan as it does on my server).
I remember working on this for a customer a few years ago. Found the bug in the internal database (I am a Microsoft Premier Developer Consultant), but no public reference.


How to design a database for search history with multiple facets?

What you store should mimic what the user sees in the UI.  That way you can present it in a format that is familiar to him.

This format may, or may not, be anything close to the SQL that has ANDs and ORs.


Can SQL Server use a spatial index and another index at the same time and be efficient?

mickyt: With only 2 models in your dataset the spatial index will be more effective than an index on model id.  It will still do an index seek on the model id if required.  However if you start adding more and more models to the table, you may get to a tipping point.

I tested on a table with 1,000,000 points and 99 models.  Using the model index only was quicker and more efficient than forcing the spatial.  With 9 models over the 1,000,000, forcing the spatial index was quicker and more efficient.


mysqldump between servers with different timezones (tz-utc)

That sounds like the "proper" way.

A TIMESTAMP column contains the time in UTC, but converts when storing and fetching, so that you can only see the value converted to your local time.

A DATETIME column, on the other hand, is like taking a picture of a clock.  There is no TZ conversion during store/fetch.

Since mysqldump is essentially a bunch of SELECTs, and the reload is a bunch of INSERTs, the data in the file looks like a picture of a clock.  By using UTC during the fetch, the file will contain UTC time.  By having that SET, the INSERT will, again, do no conversion to mess up the value.


Unable to connect to pluggable database using TNS but EZCONNECT is OK

The problem is the TNS name which contains a leading whitespace.

For example:

[Whitespace]ORCLPDB =
  (DESCRIPTION =
...

As we know, the @ in connection string should immediately followed by the TNS name we can't have whitespace in front of a TNS name.


Import XML data while creating IDs with relationships

You can use a variant of the technique described in Using merge..output to get mapping between source.id and target.id and by Adam Machanic in Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE.

You use merge and capture the generated ID with the XML fragment that belongs to that ID in a table variable and then use that table variable when adding rows to the child tables.

declare @C table
(
  CONSTRUCTION_ID int primary key,
  PROJECT xml
);

merge CONSTRUCTION as T
using (
      select T.X.value('(constructionName/text())[1]', 'varchar(30)') as CONSTRUCTIONNAME,
             T.X.value('(constructionType/text())[1]', 'varchar(30)') as CONSTRUCTIONTYPE,
             T.X.query('project') as PROJECT
      from @xml.nodes('/root/construction') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTIONNAME, CONSTRUCTIONTYPE)
  values (S.CONSTRUCTIONNAME, S.CONSTRUCTIONTYPE)
output inserted.CONSTRUCTION_ID, S.PROJECT into @C;

declare @P table
(
  PROJECT_ID int primary key,
  BUSINESSPARTNERS XML
);

merge PROJECT as T
using (
      select C.CONSTRUCTION_ID,
             T.X.value('(projectname/text())[1]', 'varchar(30)') as PROJECTNAME,
             T.X.query('businesspartners') as BUSINESSPARTNERS
      from @C as C
        cross apply C.PROJECT.nodes('/project') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTION_ID, PROJECTNAME)
  values(S.CONSTRUCTION_ID, S.PROJECTNAME)
output inserted.PROJECT_ID, S.BUSINESSPARTNERS into @P;

insert into BUSINESSPARTNERS(PROJECT_ID, PARTNERNAME)
select P.PROJECT_ID,
       T.X.value('text()[1]', 'varchar(30)')
from @P as P
  cross apply P.BUSINESSPARTNERS.nodes('/businesspartners/partnername') as T(X);

SQL Fiddle


How much does an ordered query of an attribute costs in terms of operations when the attribute is a key or an attribute?

Not sure what your question is exactly, as the symbols you use seem a bit confusing, but the general idea I think is this:

Firstly, you have to visit all records in the heap to find those where Attribute = 50, so that would be, in pages, 100000 records times 100 bytes each divided by 1024 (page size) rounded up, which comes to 9766. This determines your I/O cost, which will be all you need to know for the query without ordering -- matching records can be returned to the client immediately.

The rest is impossible to evaluate without knowing the selectivity of your search criteria, that is, how many records match it. For the query with ORDER BY ultimately you will need to 1) store all matching records in memory, determining your memory cost, and 2) sort them, determining your CPU cost.

Edit:

With Attribute uniformly distributed across 1000 distinct values we can estimate that the equality predicate will fetch on average 100000/1000 = 100 rows, each 100 bytes long, which will require 10000 bytes to store. CPU cost to sort them will be proportional to log₂(100 rows).

Note that some smarty-pants optimizer might notice that there's no need to order the result set by the value that's on the right hand side of the sole equality predicate and ignore the ORDER BY clause altogether.


See the SQL Server 2008 calculation in this article. It applies to 2012, 2014 and 2016 too.

cpu_ticks is what was used in SQL Server 2005.

declare @ts_now bigint

select @ts_now = ms_ticks from

sys.dm_os_sys_info

select record_id, dateadd (ms, (y.[timestamp] -@ts_now), GetDate())
...


How can I speed SQLPS connection when I don't have WMI permissions at the server

Apparentely, after some testing...there is a way to speed up SQLPS load time (tremendously) and prevent the WMI warnings (if you don't need the SQLAS module loaded.

How I tested this on my laptop, was simply by shutting down the WMI service: Stop-Service winmgmt -Force. I then proceeded to edit the module files for SQLPS for my SQL Server 2012 local instance.

As soon as I load the module initially, I get the same warning messages you show in your question. The one that causes the specific problem is the SqlPsPostScript.PS1 file. This file is a nested module based on the module manifest, so everytime you load SQLPS this script is going to be executed.

This SqlPsPostScript.PS1 file is also why when the module is loaded it changes your location in your prompt. The first line of the file is Set-Location SQLSERVER:. [If you comment out that line it can also speed up the load time for the module a good bit.]

The lines that are causing the WMI warnings are from the two lines of code that will load the SQLAS module. Which if you look at the documentation for AS PowerShell in BOL it states it is loaded with SQLPS here. It also goes to mention when configuring remote administration for AS that in step 3 WMI Service has to be running. Which if you don't have access to WMI equates to the service not appear as running.

So in the end, as long as you don't need Analysis Services PowerShell module you can edit the SqlPsPostScript.PS1 file, found under:

C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\PowerShell\Modules\SQLPS\

And comment out the following lines:

$m = Get-Module -ListAvailable | where {$_.Name -eq "SQLASCmdlets"}
if($m -ne $null) { Import-Module $m -Global }

Once I did that and then saved the file, I no longer received the warnings when loading SQLPS.


How to Log IP Address for Inbound Connections in MySQL

If you have shell access to the server on which mysql is running, you can use tcpdump + pt-query-digest. First, do a tcpdump with the appropriate switches necessary for pt-query-digest: tcpdump -i [your interface] port [3306,etc] -s 65535 -x -nn -q -tttt > tcpdump.out

Then, after you are done with your packet capture, do this:

pt-query-digest tcpdump.out --type=tcpdump --group-by=fingerprint --print --no-report

That will give you the raw digest of all queries and the hosts from which they originated. More information can be found at: http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html


Searching for array elements nested in MongoDB Documents

Remember, MongoDB has a dynamic schema. So it is perfectly ok to store this document:

{
  "JobNumber" : "50001-01",
  "CustomerId" : "joe",
  "IdentifierNumber" : NumberLong(8812739),
  "TimesPrinted" : 0,
  "Packaging" : {"bundle":1200,"box":120,"pallet":3}
}

and this document

{
  "JobNumber" : "50001-02",
  "CustomerId" : "jane",
  "IdentifierNumber" : NumberLong(8812739),
  "TimesPrinted" : 0,
  "Packaging" : {"sack":200}
}

in the same collection.

Since, I wouldn't query for the Nth document, but for a given field in the subdocument, for example

 db.collection.find({"packaging.bundle":1200})

which would run just fine with MongoDB. The reason behind that is that if a field isn't present in a document, it is evaluated as null for a query. And null is definitely not equal to 1200.

As for the performance. It really depends on who big your collection is and how your queries look like. While the query as shown above may be rather slow in a collection containing hundred of thousands of documents (or even more) without an index, it can be extremely fast when you created an index on it, e.g.

    db.collection.ensureIndex({"packaging.bundle":1,"packaging.box":1,"packaging.pallet":1});

If you can create an index like this obviously depends on the question wether you really have arbitrary packaging or if you simply have a variety of packaging options. If the latter is the case, I'd create an index for each of the packaging options, utilizing sparse indices, e.g.

 db.collection.ensureIndex({"packaging.sack":1},{sparse:true})

This would reduce the index size, as only documents which hold the field "packaging.sack" would be contained in this index.

If you really have arbitrary fields in the documents, I wonder how you create a model for it ;)

When talking of just some ten thousands of documents, you might even get satisfying result without an index.


Is auto_update_stats_async on or off?

If this is off, it is possible that the query prepares, the stats are being discovered to be stale, and updated before the query executes.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017#auto_update_statistics_async


Indexed view not being used - SQL Server

This happens often if the optimiser thinks it can do better with the base tables.

If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it.

Example:

SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;
SET STATISTICS TIME ON;
SELECT * FROM MySchema.myIndexedView ACB WITH (NOEXPAND) WHERE ...;
SELECT * FROM MySchema.myIndexedView ACB WHERE ...;

Note that depending on version, you may suffer from predicate pushing issues.

Note that this answer is generic because we don't have code and queries to look at


Is it possible to make a SELECT wait until an ongoing UPDATE finishes?

"sufficient balance to perform an operation", this should be one transaction, the check and the operation, otherwise the check is a waste of time.  Plenty could happen between check and operation, if you separate them.


SQL Server 2008 R2 (Suspect) mode - how to repair?

You cannot repair this database, and the repair you've potentially made has not been complete. You should make sure to understand why your database is becoming corrupt - check your disk system as that's the most likely culprit.

Then restore your database from a backup - but make sure to run DBCC CHECKDB on that very backup to ensure it's not corrupt itself.

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is the very last resort you should ever go to. Before that, you should restore a valid backup. Once you do use REPAIR_ALLOW_DATA_LOSS, don't expect your database to magically come back to life again - most likely, irreparable damage has occurred - especially in this case since it complains about system table corruption.


How to remove the red squiggly line from IntelliSense for the newly created table or procedure without restarting the SSMS

Try pressing ctrl + Shift + r to refresh intellisense and remove the red squiggly underline.


You do not specify which RDBMS you are working with. Most what I write here should be quite independent but I mostly have experence in MySQL so maybe different systems allow some other optimizations.

The (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount is a dependent subquery - it will be executed for each row in your results. Even if one execution is fast, potencially thousands of them can make it slow.

The one in the JOIN is a derived table - it will be executed only once and materialized into a temporary table, which will then be joined to your other tables. If the query is fast (can use index on (to_user_id)), it is good. But in this case the count will be counted even for users which do not really show in the results. But.. you can just push the condition in there (to_user_id = 1 instead of the GROUP BY).

But to make things not so simple there exist some optimizations in newer versions. The dependent subquery can be made faster by using a subquery cache in MariaDB 10 (and IIRC MySQL 5.7, but I did not verify). That means that in your case all rows in result have u.id = 1 -> to_user_id = 1 and the subquery will be actually executed only once and then the cached results will be used. If that is available, the difference between both versions will be minimal.

Personally I prefer your second version most of the time but there are some cases when the first one will be faster - I once had a query where it was not simply possible to limit the rows in the JOINED subquery the right way, but switching to the dependent subquery only few unique combinations were actually read.


How to combine multiple EXEC statements into a single SELECT?

I don't think you can combine several logs in one EXEC, so you are most likely stuck with several executions. As suggested nu Mo64, you can put it all in a temp table and then query that temp table. I have a utility that does exactly that, here.


Is index_type ignored on MySQL primary keys in create table?

You cannot name a PRIMARY KEY. The name PRIMARY KEY is the designation of the arbitrary, or preferred, unique index (candidate key) for accessing the table. A table can have multiple unique keys, but only one PRIMARY KEY.

EXAMPLE: Employee table with three unique keys

EmployeeID
Driver's License Number
Social Security Number

You can pick a name out of a hat, flip a coin, or ask project managers which one of the three indexes should be the PRIMARY KEY.

You can create a unique index without it being the PRIMARY KEY as follows:

create table a (foo INTEGER, UNIQUE KEY foo_id (foo));

Check these other links about PRIMARY KEYs vs Unique Keys

Why do primary keys have names of their own?
What Is The Point of a Primary Key?

As for the other question: the Index Type

When it comes to the Index Type, here are the defaults:

The MEMORY storage engine and NDB (MySQL Cluster) use HASH as the index_type.

You could specify BTREE as the index type
It tends to bloat more than HASH indexes

The index_type for MyISAM and InnoDB is BTREE.

MyISAM and InnoDB DO NOT SUPPORT HASH INDEXES
You have to emulate HASH indexing

To find out the index type, run this query:

select index_name,index_type from information_schema.statistics
where table_schema='test' and table_name='a';

Here is an example:

mysql> use test
Database changed
mysql> create table a (foo INTEGER, PRIMARY KEY foo_id (foo));
Query OK, 0 rows affected (0.07 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `foo` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select index_name,index_type from information_schema.statistics
    -> where table_schema='test' and table_name='a';
+------------+------------+
| index_name | index_type |
+------------+------------+
| PRIMARY    | BTREE      |
+------------+------------+
1 row in set (0.00 sec)

mysql>


How to optimize count(*) using only foreign key index on PostgreSQL

PostgreSQL can only use an index to count the number of rows if the table has been vacuumed recently so that most table blocks are marked "all visible" in the visibility map. Otherwise it has to inspect the table to check ifbthe row is visible or not, and then an index scan is more efficient.

I think that the solution is not to count the rows. For a cheap estimate, use pg_class.reltuples. For a more in-depth treatment of the topic, read my blog.


What is Heap Fragmentation sans Indexes?

Imagine you have a heap and a record needs to be updated which causes it to grow.  The new record size cannot fit on the DB page anymore.  As a result a 'forwarding' record is created which is a pointer to which DB page the real record is.  This is now fragmentation in your heap.

Thus, when you do a bookmark lookup on a heap with indexes on it you will still hit the forwarders which will cause more IO, and your indexes need to store the long RID.  Removing that fragmentation based on the table will help reclaim size but almost always the internal storage engine prefers to work with a clustered index.

Background

Just for background there are a few kinds of fragmentation.  The heap table itself might not be ordered, but it might have non clustered indexes on it which then need to reference the heap through a physical record ID (RID) which is a combination of file id, row id, and a few others.  Basically where the record exists in the database page.  This is not a logical RID.  A logical RID would be like a unique clustered index and if your clustered index is not unique, it adds a uniqueifier similar to the RID.  The DB engine would use this unique "ID" to find the record logically.

This is one reason a heaps row is sometimes wider than a table with a unique clustered index as it adds that info per record in your indexes.


Splitting Tables in MySQL. Good practice?

I think everyone is over-complicating this.  The key point here is:

  Combined, the tables have probably 100,000 rows and the growth rate is relatively low.

This is a piece of cake for any RDBMS to handle.  Go with one table, index it properly, and consider it a solved problem.

You don't need to consider partitioning, whether "homemade" or otherwise, until you start handling extremely large volumes of data--think billions of rows and up.


renaming a sql server with installed named instances

You are only allowed to change the server name or hostname of a server running SQL server. Microsoft does not support renaming the instance without a reinstall. So if you have a default instance on the server that is referenced by the hostname you can rename the "instance" in that manner. However if you are wanting to go from "hostname\instance1" to "hostname\instance2" you will have to reinstall SQL Server.


Why can a SQL Server table not have more than one IDENTITY column?

I don't think there's any real "internals" reason. The metadata is stored at column level not table level. It would need a rethink though of scalar functions such as scope_identity() and pseudo column syntax such as $identity as there would now be ambiguities.

Philosophically if the purpose of identity is to produce something that uniquely identifies an entity why would you need two different arbitrary calculated values acting in that role?

And where is the benefit anyway? This is a cross site dupe so I'll repeat my example from SO.

  An Identity Column in SQL Server has a seed and an auto increment. We
  could always calculate what the 2nd hypothetical id value should be if
  we knew the value of the first id column anyway.

  e.g. If this was legal syntax

create table #foo
(
bar int identity(1,10),
baz int identity(1000,1)
)

  We wouldn't need to store baz as it could be calculated from bar as
  follows.

baz = 1000 + (bar-1)/10

Since SQL Server 2012 you can knock yourself out and add as many columns using sequence defaults as you want to a table though. For example:

CREATE SEQUENCE dbo.Sequence1
    AS integer
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 1000
    CYCLE
    CACHE 50;

CREATE SEQUENCE dbo.Sequence2
    AS decimal(5,0)
    START WITH 5
    INCREMENT BY 10
    MAXVALUE 250
    CYCLE
    CACHE 50;

CREATE TABLE dbo.T
(
    id integer NOT NULL DEFAULT NEXT VALUE FOR dbo.Sequence1,
    id2 decimal(5,0) NOT NULL DEFAULT NEXT VALUE FOR dbo.Sequence2,
);

INSERT dbo.T DEFAULT VALUES;
INSERT dbo.T DEFAULT VALUES;
INSERT dbo.T DEFAULT VALUES;

SELECT * FROM dbo.T;

DROP TABLE dbo.T;

DROP SEQUENCE
    dbo.Sequence1,
    dbo.Sequence2;


GIN indexes do not work with any jsonb operators EXCEPT ? ?& ?| @>. Clearly, your use of comparison operators >= and <= are not on that list. And all of those operators that it could help are also not on the list (meaning the index won't do anything) afaik.

That means needing indexed comparison operators, you'll need a btree.

CREATE INDEX ON public.influencers ((ig->'follower_count'));

All of that said, I think this is silly. Just put follower_count as a proper column on the actual table and enjoy the performance benefits.


Can spatial index help a "range - order by - limit" query

You may be able to achieve better performance by searching first in rows with higher frequencies. This can be achieved by 'granulating' the frequencies and then stepping through them procedurally, for example as follows:

--testbed and lexikon dummy data:

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create table lexikon( _id serial,
                      word text,
                      frequency integer,
                      lset integer,
                      width_granule integer);
--
insert into lexikon(word, frequency, lset)
select word, (1000000/row_number() over(order by random()))::integer as frequency, lset
from (select 'word'||generate_series(1,1000000) word, generate_series(1,1000000) lset) z;
--
update lexikon set width_granule=ln(frequency)::integer;
--
create index on lexikon(width_granule, lset);
create index on lexikon(lset);
-- the second index is not used with the function but is added to make the timings 'fair'

granule analysis (mostly for information and tuning):

create table granule as
select width_granule, count(*) as freq,
       min(frequency) as granule_start, max(frequency) as granule_end
from lexikon group by width_granule;
--
select * from granule order by 1;
/*
 width_granule |  freq  | granule_start | granule_end
---------------+--------+---------------+-------------
             0 | 500000 |             1 |           1
             1 | 300000 |             2 |           4
             2 | 123077 |             5 |          12
             3 |  47512 |            13 |          33
             4 |  18422 |            34 |          90
             5 |   6908 |            91 |         244
             6 |   2580 |           245 |         665
             7 |    949 |           666 |        1808
             8 |    349 |          1811 |        4901
             9 |    129 |          4926 |       13333
            10 |     47 |         13513 |       35714
            11 |     17 |         37037 |       90909
            12 |      7 |        100000 |      250000
            13 |      2 |        333333 |      500000
            14 |      1 |       1000000 |     1000000
*/
alter table granule drop column freq;
--

function for scanning high frequencies first:

create function f(p_lset_low in integer, p_lset_high in integer, p_limit in integer)
       returns setof lexikon language plpgsql set search_path to 'stack' as $$
declare
  m integer;
  n integer := 0;
  r record;
begin
  for r in (select width_granule from granule order by width_granule desc) loop
    return query( select *
                  from lexikon
                  where width_granule=r.width_granule
                        and lset>=p_lset_low and lset<=p_lset_high );
    get diagnostics m = row_count;
    n = n+m;
    exit when n>=p_limit;
  end loop;
end;$$;

results (timings should probably be taken with a pinch of salt but each query is run twice to counter any caching)

first using the function we've written:

\timing on
--
select * from f(20000, 30000, 5) order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 80.452 ms
*/
select * from f(20000, 30000, 5) order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 0.510 ms
*/

and then with a simple index scan:

select * from lexikon where lset between 20000 and 30000 order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 218.897 ms
*/
select * from lexikon where lset between 20000 and 30000 order by frequency desc limit 5;
/*
 _id |   word    | frequency | lset  | width_granule
-----+-----------+-----------+-------+---------------
 141 | word23237 |      7092 | 23237 |             9
 246 | word25112 |      4065 | 25112 |             8
 275 | word23825 |      3636 | 23825 |             8
 409 | word28660 |      2444 | 28660 |             8
 418 | word29923 |      2392 | 29923 |             8
Time: 51.250 ms
*/
\timing off
--
rollback;

Depending on your real-world data, you will probably want to vary the number of granules and the function used for putting rows into them. The actual distribution of frequencies is key here, as is the expected values for the limit clause and size of lset ranges sought.


What are best practices for storing many iterations of a product in an RDBMS?

It should work. But looking on your diagram I think you could merge products with product_changeset as you could have for every product version info who and when changed that. You could also consider to avoid to have dedicated table current_product and instead use some kind of flag (active or timestamp on main products table). There is lot of possibilities, but best solution needs to think about:

how many products do you expect to have?
how often such changes can be made?
do you want to change any attribute or only to specific ones?
what is usage of this product history? Do you need to be able to revert to some point of history? Or do you want to show it somewhere to the user? Or is just it should be in db just for some audits, just as kind of logs?

added as a response to comment, it doesn't allow for such long ones :)
So I see two solutions here:

your original one with that change that current_products should provide unique product id which would be then referenced by products directly. I think you could change relation of product_changesets, and connect that
to primary key of products. This seems like less used entity.

+ better queries for only current versions of product - which seems to be major of your needs
- more complicated when new version is provided
- more complicated when you need to revert to some previous version
- risk of desynchronize tables - you can have different actual versions in both tables; which one should be a source of truth?

alternatively you could have one table with immutable values (like product id or similar) which would be main table in fact for products. Then there could be kind of product_history or product_versions which would
have relation to main product table and would have all attributes plus some flag (like active) which would declaratively say that this only row should be considered as valid one.

+ easier than previously to add new version - you need only to have some trigger or similar to update flag of previous version, and some default on this flag, so modifying version would be only adding new rows in fact
+ no risk that you will have different values for current version in separate tables
- slightly more complicated standard queries - you need to take product by, say, its id and this active column - you can have view for that
- there is risk that after many years for many changes, this table will be quite big; so maybe you could store only previous version, and previous ones somehow remove?

In any case, you have just to connect this price_entries tables like you have already on diagram - using main unique id of product. Then, again, you have figure out how to say that specific entry is the current one - similarly like above you can
try to reflect that by some current_price entity, but it seems like overcomplicating. There some active columns seems like doing a job. Do you need to connect specific price entry with specific version of product?
And please make sure that products attributes are kind of lookups (with dedicated tables), not free text fields, as it easily brings a mess to db.


How to avoid full table scan when using OR operator on an Index

A common trick for optimizing OR is to turn it into UNION:

( SELECT * FROM A JOIN B USING(id) WHERE condition_1 )
UNION DISTINCT
( SELECT * FROM A JOIN B USING(id) WHERE condition_2 )

(I'm with Jack on being confused on what you really wanted, so I avoided spelling out the details of the conditions.)

Be sure to include index(es) that let the queries start with the conditions.


Given the following sample data:

DECLARE @Data AS table
(
    data integer PRIMARY KEY
);

INSERT @Data
    (data)
VALUES
    (1),
    (2),
    (3),
    (6),
    (7),
    (15),
    (16);

One way to achieve the result you are after is:

WITH Grouped AS
(
    -- Identify groups
    SELECT
        D.data,
        grp = D.data - ROW_NUMBER()
            OVER (ORDER BY D.data)
    FROM @Data AS D
)
SELECT
    STUFF
    (
        (
            -- Concatenate items in the current group
            SELECT
                [text()] = ',' + CONVERT(varchar(11), G2.data)
            FROM Grouped AS G2
            WHERE
                G2.grp = Grouped.grp
            ORDER BY
                G2.data
            FOR XML PATH ('')
        )
        -- Remove initial comma
        , 1, 1, ''
    )
FROM Grouped
GROUP BY
    Grouped.grp;

Test at SQLfiddle


Segmentation fault (core dumped) with self-complied mysql-5.1.9-beta.tar.gz

There are issues with this question that suggest a fundamental lack of familiarity with MySQL.

  After I input sudo bin/mysqld_safe --user=mysql &, I must wait for a very long time. Why is that?

What did you expect to happen?  Here are a couple of examples of startup behavior from other versions:

MySQL 5.0.45

$ sudo bin/mysqld_safe
Starting mysqld daemon with databases from /var/lib/mysql

MySQL 5.5.17

$ sudo bin/mysqld_safe
130320 20:04:20 mysqld_safe Logging to '/var/log/mysql/error.log'.
130320 20:04:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Nothing else is supposed to happen.  So... what you're seeing is normal, so far.  You can wait an infinite amount of time, and MySQL isn't normally going to say anything else on the console.  It's a server daemon, not an interactive program.

What does the MySQL error log say?  There's nothing here, yet, to tell us that your server isn't actually running just fine.

gaoyu@FlashGroup:~/mysql-5.1.9$ bin/mysql
Segmentation fault (core dumped)

Interesting, but unrelated to the other issue... bin/mysql is not MySQL Server, it's the MySQL command line client.  This could be a defect in the bundled or your local libedit, or curses, or SSL, or any other library that's involved... but if you can get the server working, you can connect to it with any newer version client, so the inability to compile a working client might not be particularly relevant, depending on the nature of what's happening here.

You also appear to be trying to start with a debug build, which seems like a backwards approach, since the debug instrumentation can change the behavior of the server.

But this discussion all seems academic, since, as has already been aptly stated, MySQL 5.1.9 is a fundamentally flawed codebase to try working from.  Your code might be great, but you're working against a base that was not production quality.

In fact, it's a beta version that came out almost 7 years ago, and it took two and a half years' worth of releases after that one before the MySQL 5.1 release series achieved General Availability status.

It seems inconceivable that 5.1.9 could be relevant to anyone, anywhere, when there were still major, major defects in that code... but granting, for a moment, the assumption that it is... then it seems like the bottom line is that if you have some 5.1.x-related add-on source code you're working with, there is not any intrinsic reason why it would not be equally usable with newer 5.1.x releases... 5.1.68 was released in 2013.

Accomplish what you want to accomplish with 5.1.67 (my current production choice for 5.1 constrained environments) or 5.1.68 and there should be very few incompatibilities when you back-port to prior releases as long as you stay within 5.1.x.

Testing whether you can actually compile the newer versions of 5.1 successfully might tell you something about your build environment, too.


IndexOptimize is successful, but sys.dm_db_index_physical_stats still shows huge fragmentation

This is from Ola Hallengren's SQL Server Index and Statistics Maintenance documentation:

  MinNumberOfPages

  Set a size, in pages; indexes with fewer number of pages are skipped
  for index maintenance. The default is 1000 pages. This is based on
  Microsoft’s recommendation.

  IndexOptimize checks page_count in sys.dm_db_index_physical_stats to
  determine the size of the index.

Therefore, the indexes you mentioned are still fragmented after executing IndexOptimize were most likely skipped for having fewer pages than the minimum of 1000 pages since you didn't change that value in your code.


The process could not execute 'sp_replcmds' on 'S15415940'. Cannot execute as the database principal because the principal "dbo" does not exist

In my experience I had this message

  The process could not execute 'sp_replcmds'

when two log reader processes where accidently trying to run.

The fix I had was to:
 connect at the publisher, look in master..sysprocesses for a a leftover process from the distributor
 kill it, and relaunch the log reader on the distributor.


The DELETE statement conflicted with the REFERENCE constraint

You don't need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete a person that is still referenced by an article. It succeeded, you were just trying to do this and was blocked, kudos to the designer.

Now go and talk with that somebody that designed the schema and knows the constraints and ask him how to properly delete the records you're trying to delete, in the correct order and taking the proper precautions to keep the database consistent.


How to define a one-to-many relationship without primary keys?

Here's the design that comes to mind:

4 tables: student, class, seat_class, class_student_seat.
Each table has an ID column which is an autoincrementing integer.
Typically the ID is the PK.
Some tables have compound primary keys (i.e., multiple columns make the PK).

This is approximately a third-normal form (3NF) design; from a relational database perspective, it is modular an efficient (minimizes repeating data):

students
  student_id, (PK)
  student_first_name,
  student_last_name

class
  class_id,
  class_name ('biology 111', etc.)

seat_class
  seat_id,  (PK)
  class_id, (PK)
  seat_name ('1a', '1b', etc.)

class_student_seat
  class_id,  (PK)
  student_id,(PK)
  seat_id    (PK)


If the query purpose is unclear, then you should make it clear.

You don't want to be relying on some relatively obscure ordering rules to get it right. You can't rely on them always staying the same throughout different versions of the same product, or if the underlying technology changes (e.g. Oracle -> MySQL).

If you want it to behave in one way, make it explicit how you want it to behave by using parentheses.


Dodgy T-SQL Query Execution driving me crazy

is there any way to get that TOP N to not be so heavy?

I think there's a little bit of a misunderstanding here about how execution plans work.

That number is just the estimated cost, which is a model SQL Server uses to determine what the most efficient execution plan will be.  It's not updated at runtime, so even if the operators used very little resources, the estimated costs are still displayed as they were when the plan was created.

Looking at the runtime plan you provided, Sentry One Plan Explorer makes it a little easier to see that some of those "expensive" operators didn't run at all (they are "greyed out"):

  I am having a heck of a time understanding why this query is taking 0.02 seconds to execute

I'm not sure how you're measuring the time here, but the execution plan indicates that the entire query took less than one milliseond to run, and took 9 milliseconds to compile.  See the excerpts from the plan XML:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="48" CompileTime="9" CompileCPU="9" CompileMemory="736">
...
<QueryTimeStats CpuTime="0" ElapsedTime="0" />

Max has some good suggestions on general improvements to the query, definitely check those out.  But I thought it was worth pointing this out as well.

You might be able to eliminate the TopN Sort with an indexed view, but I'm not sure it's worth the overhead at this point.


The best thing that you can do is fix your table so that you store a date instead of a string. With that said, the code that you posted in the question is on the right track. The problem is that you leave it as a string and don't explicitly cast it to a DATETIME. By the rules of data type precedence the data that you filter against also gets converted to a string which isn't what you want.

If you want to keep the SUBSTRING approach then you can try the following:

CREATE TABLE #T (NOT_A_DATE NVARCHAR(5));

INSERT INTO #T VALUES (N'01/30'), (N'01/31');

SELECT
    DATEFROMPARTS(
      datepart(year, getdate())
    , substring(NOT_A_DATE, 1, 2)
    , substring(NOT_A_DATE, 4, 5)
)
FROM #t;

The key is the use of the DATEFROMPARTS function to convert the string to a date that's safe in all locales.


root, assuming it is the SUPER user, should be very restricted.

'root'@'my.domain.com'

'root'@'11.22.33.44'

In other situations, you might want

'joe'@'192.168.%'

'sal'@'%.our-domain.com'


Only show results greater than a certain number when summing two values together

Try:

HAVING 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4

However you will still probably find that integer math is preventing you from getting the result you want. So this might be better:

HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4


I would say that SQL Server does care when you change the IP address of your server. It is all based on the configuration of the TCP/IP settings for that given instance.

Once you change the IP, go into SQL Server Configuration Manager and then the Network Configuration. Verify that the new IP was updated properly in the list of IPs for the TCP/IP protocol. If it does not show up, you will likely see the old IP still there, just update it to the new one.

After this you will need to restart the instance.


RDS Does Not Allow Restoring MS SQL DB from S3

The quick answer is "don't waste your time with AWS and RDS".  But there are nuances.  Those solutions might be appropriate for some things but trying to open a MS SQL backup file on a Mac is not one of them.  Here is what you want to do:

https://apple.stackexchange.com/questions/385430/how-to-read-ms-sql-backup-file-on-mac

The key to it all, for me, was this step by step article that walks you all the way through it:

https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-ver15


Using the special Dual table to read sequences

Note that 11g has support for sequence expressions directly in PL/SQL

Otherwise, from the Oracle Docs:

  Beginning with Oracle Database 10g Release 1, logical I/O is not
  performed on the DUAL table when computing an expression that does not
  include the DUMMY column. This optimization is listed as FAST DUAL in
  the execution plan.

So any performance gain will be slight. In the following tests I dropped and recreated the table and sequence between each execution:

testbed:

create sequence s;
create table t(id integer);

tests:

declare
  n integer;
begin
  for i in 1..100000 loop
    select s.nextval into n from dual;
    insert into t(id) values(n);
  end loop;
end;
/
--average about 5s

declare
  n integer;
begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval) returning id into n;
  end loop;
end;
/
--average about 4s

begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval);
  end loop;
end;
/
--average about 3s

You should probably rerun these tests or ones like them in you own environment.


selecting multiple instances of a record based on record lifespan over years

Am I over simplifying here?

CREATE TABLE Items (ItemID int, ItemName nvarchar(255), InstallYear int, Life int)

INSERT INTO Items VALUES (1,'test1',2010,2)
INSERT INTO Items VALUES (2,'test2',2011,2)
INSERT INTO Items VALUES (3,'test3',2012,5)
INSERT INTO Items VALUES (4,'test4',2013,3)

SELECT * FROM Items
GO

CREATE PROCEDURE GetItemLifetimeInfo
(
    @StartYear int,
    @EndYear int
)
AS
BEGIN
    SELECT *, InstallYear + Life AS ReplaceYear
    FROM Items
    WHERE InstallYear <= @EndYear AND (InstallYear + Life) >= @StartYear;
END
GO

EXEC GetItemLifetimeInfo 2011, 2022;

Returns:

ItemID  ItemName    InstallYear Life    ReplaceYear
1       test1       2010        2       2012
2       test2       2011        2       2013
3       test3       2012        5       2017
4       test4       2013        3       2016

This works:

CREATE PROCEDURE GetItemLifetimeInfo
(
    @StartYear int,
    @EndYear int
)
AS
BEGIN
    WITH ReplaceYears(ItemID, [Year])
    AS (
        SELECT ItemID, Life
        FROM Items
        UNION ALL
        SELECT Items.ItemID, Items.Life + ReplaceYears.Year
        FROM Items
            INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
        WHERE ReplaceYears.Year <= (@EndYear - @StartYear)
    )
    SELECT Items.ItemID, InstallYear + ReplaceYears.Year AS ReplaceYear
    FROM Items
        INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
    WHERE InstallYear + ReplaceYears.Year <= @EndYear
        AND (InstallYear + ReplaceYears.Year) >= @StartYear
    ORDER BY 1,2
END

EXEC GetItemLifetimeInfo 2011,2022;

ItemID  ReplaceYear
1   2012
1   2014
1   2016
1   2018
1   2020
1   2022
2   2013
2   2015
2   2017
2   2019
2   2021
3   2017
3   2022
4   2016
4   2019
4   2022


Restoring system databases on Availability group replicas

All of your server configuration is stored in the master database along with your users logins so I wouldn't recommend restoring the master database from the primary.

As for copying users and jobs, have a look at dbatools . Its a set of Powershell tools that include ones for copying users and jobs.


DBATools - pass through flat file configs to Agent Job Commands

Well, 4 hours ago self... if you had bothered to inspect the types of $foo and $bar, you might not have wasted a whole afternoon swearing loudly in your open office floor plan as you scroll fruitlessly through the documentation...

~> ($foo | Get-Member).TypeName[0]
System.Management.Automation.PSCustomObject

~> ($bar | Get-Member).TypeName[0]
System.Collections.Hashtable

There's even a convenient, generalized solution on this very Q&A Network for converting a PSCustomObject into a HashTable.

$foo.psobject.properties | foreach -begin {
    $foo=@{}
} -process {
    $foo."$($_.Name)" = $_.Value
} -end {$foo}

~> New-DbaAgentSchedule @foo -ServerInstance "." -SqlCredential $sa

Easy peasy, lemon squeezy.

So splat away, ya big crazy. And when you're done feeling stupid, maybe share the workaround online in case it helps someone else. And if you're feeling super proactive, maybe consider setting aside some time to contribute to the module.


Does SQL Server place shared locks on scanned records when using REPEATABLE READ

What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

That would be SERIALIZABLE READ, not REPEATABLE. REPEATABLE only guarantees that the rows that qualified for the WHERE clause the first time will not disappear or be modified. It doesn't say anything about other rows qualifying in a second read.


How to see last actions executed (and time) of a specific database in MongoDB?

As per MongoDB documentation here through getLog is an administrative command that returns the most recent 1024 logged mongod events.

The getLog does not read log data from the mongod log file. It instead reads data from a RAM cache of logged mongod events. To run getLog, use the db.runCommand( { command } ) method on the admin database.

The getLog command has the following syntax:

{ getLog: <value> }

The possible values for getLog are:

global - returns the combined output of all recent log entries.
rs - if the mongod is part of a replica set, getLog returns recent
notices related to replica set activity.
startupWarnings - returns logs that may contain errors or warnings
from MongoDB’s log from when the current process started. If mongod
started without warnings, this filter may return an empty array.

Retrieve Recent Events from Log

The following operation retrieves the most recent global events for the mongod

db.adminCommand( { getLog : "global" } )

For your further ref here


How can you split multiple delimited strings of varying sizes into rows?

If you can bear to have a function to supply functionality not (yet) built into the engine, you can create this function which returns an ordinal position alongside each value (there are dozens of different ways to write this splitting function):
CREATE FUNCTION dbo.SplitCSVInOrder
(
    @List  nvarchar(max)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
    (
      WITH L0(c) AS (SELECT 1 UNION ALL SELECT 1),
           L1(c) AS (SELECT 1 FROM L0, L0 AS B),
           L2(c) AS (SELECT 1 FROM L1, L1 AS B),
           L3(c) AS (SELECT 1 FROM L2, L2 AS B),
           L4(c) AS (SELECT 1 FROM L3, L3 AS B),
           L5(c) AS (SELECT 1 FROM L4, L4 AS B),
           N (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY c) FROM L5)
        SELECT
           [Position] = ROW_NUMBER() OVER (ORDER BY n),
           [Value] = LTRIM(RTRIM(SUBSTRING(@List, n,
                CHARINDEX(',', @List + ',', n) - n)))
        FROM N WHERE n <= LEN(@List)
            AND SUBSTRING(',' + @List, n, 1) = ','
    );

Then you can unpivot / re-pivot to line up the values from each position:
DECLARE @SourceTable TABLE
(
  ID INT,
  String1 varchar(100),
  String2 varchar(100),
  String3 varchar(100)
);

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2');

;WITH x AS
(
  SELECT * FROM @SourceTable
    UNPIVOT (s FOR strings IN (String1, String2, String3)) AS up
    CROSS APPLY dbo.SplitCSVInOrder(s)
),
p AS
(
  SELECT * FROM x PIVOT (MAX(Value)
    FOR strings IN ([String1],[String2],[String3])) AS p
)
SELECT ID, Position, S1_Transformed = COALESCE(MIN(String1),''),
                     S2_Transformed = COALESCE(MIN(String2),''),
                     S3_Transformed = COALESCE(MIN(String3),'')
FROM p
GROUP BY ID, Position
ORDER BY ID, Position;

Results:
ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3
1   4                           b4
1   5                           b5
1   6                           b6
2   1           d1              e1              f1
2   2           d2                              f2

I am sure there is a simpler way to do it but that's what my raised-mostly-on-SQL-Server-2005 brain came up with.
Do you wish that query could be easier to formulate? Me too! For one, it would be great if there were a native string splitting function that also returned ordinal position. That would negate the need for a function to ensure the output comes out in the same order as it's listed in the string. I've asked for everyone's help in getting Microsoft to add a function in the next version of SQL Server. Please vote and add a constructive comment that explains your business case here:

Add row position column to STRING_SPLIT

And see some background on what I'm asking for specifically here:

A way to improve STRING_SPLIT


Can I create alert on ora-20118 from Enterprise Manager?

You could use DBMS_SYSTEM.KSDWRT to write the error to the alert log when you catch the relevant exception.

Then you can configure Enterprise Manager to alert on that error code. You've two options here:

Modify the regex for the existing error log analysis to cover this error code. This would be much easier, but might be a little inflexible.
Write a Metric Extension to check the alert log for this error. This would be a lot more difficult, but would allow you to create customised error messages and responses to this specific error.


Real-time deduplication strategy for relational DB

Good news. There is a SQL command usually called MERGE or UPSERT that basically does this.

See this page for more information:
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

You probably want something like:

INSERT INTO messasges (id,version,timestamp) VALUES (1123,1,1/1/2017)
  ON DUPLICATE KEY UPDATE version=2;


How can I fix "Cannot perform a differential backup ... a current database backup doesn't exist"?

If something is taking a snapshot behind the scenes that breaks SQL differential backups causing a full to need to be taken before a new differential.  You can either stop whatever is taking the snapshot or ensure you're taking a full backup afterwards before you try to do a differential.  If you use Ola Hallengren's Maintenance Solution that can automatically change a diff to a full when it runs into this scenario.


Using CURSOR in Oracle 11g database to locate all matches for specifed string in DB

I'm hard-pressed to imagine how you'd accomplish that with a single cursor in SQL Server.

The same question has been asked and answered on StackOverflow.  If you want to get really tricky, you could also use an XML Query to search across objects with a single query.

In either case, the result is going to be insanely slow since you're full scanning every table at least if not once per character column.


I wrote a bug report to complain about the default value of that setting.


How to rollback after wrong update statement in sql server 2008R2?

Unless you have some kind of history table & trigger in place, to retain old values at every change, or you made a copy of the table before you ran the update, you will need to use the last backup that was taken before that update. Restore it (as some temp database) and extract the data.
You do have a backup available, right?

BTW, next time you're doing an update, I suggest you set it up like this:

SELECT *
-- UPDATE t SET Column1 = x, Column2 = y
FROM MyTable AS t
WHERE ...

Run the SELECT first to see exactly which rows will be updated. If needed, adjust the WHERE clause to get the rows you want to target. Only then mark the sentence from the UPDATE till the end and execute it. Much safer then running a "blind" update, as you did, forgetting the WHERE clause (I assume that was the problem).
Even safer - do it on some test database first :).


Ignore the joins without any result in SQL Server

I've been reading and re-reading the original question.  What I have come to is this:

Return records from tblProjecten where matches exist for parameters that have been supplied (are not null).  Where the parameters are not supplied (or null) then assume all matches for that child table

Effectively you're making your joins dynamic based on whether a parameter has a supplied value or not.

For that you can use:

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select p.*
from tblProjecten p
where p.naam like '%' + @trefwoord + '%'
and (
        @themaid is null
    or exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenOpleidingsType pot where p.projectId = pot.projectID and ps.studiegebiedid = @studiegebiedid
)
and (
        @opleidingdtypeid is null
    or exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid
)
and (
        @organisatorid is null
    or exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid
)
and (
        @regioid is null
    or exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid
)

I have changed things around a little.  Instead of doing joins we're doing an EXISTS with a correlated subquery.

Each one evaluates as:

If @parameter is null return true
Or... If @parameter is not null then check if there exists a matching record on the child table for that @parameter. If there is, return true

By doing this our joins effectively become optional (depending on whether we have a parameter value or not).

If I have your original intent correct, then an easy assumption is that you might want to use inner joins e.g.:

select distinct p.*
from tblProjecten p
inner join tblProjectenThema pt on p.projectId = pt.projectId
...etc...
where p.naam like '%' + @trefwoord + '%'
and (pt.themaId = coalesce(@themaid))

The reason you cannot do this is that tblProjectenThema could be empty and therefore no rows can ever be matched.  You would always get an empty result.

Alternative approach

The query above will start to suffer from performance as more tables need to be considered.  You could use OPTION(RECOMPILE) against the query.  Because you want optional, dynamic, parameters this can also be served by using dynamic SQL.

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select @trefwoord = '%' + @trefwoord + '%';
declare @sql nvarchar(max);

select @sql = '
select p.*
from tblProjecten p
where p.naam like @trefwoord
'

if @themaid is not null
select @sql += 'and exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)'

if @studiegebiedid is not null
select @sql += 'and exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid)'

if @opleidingdtypeid is not null
select @sql += 'and exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid)'

if @organisatorid is not null
select @sql += 'and exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid)'

if @regioid is not null
select @sql = +='and exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid)'

exec sp_executesql
    @stmt = @sql
    ,@params = N'@trefwoord nvarchar(max), @themaid int, @studiegebiedid int, @opleidingdtypeid int, @doelgroepid int, @organisatorid int, @regioid int'
    ,@trefwoord = @trefwoord
    ,@themaid = @themaid
    ,@studiegebiedid = @studiegebiedid
    ,@opleidingdtypeid = @opleidingdtypeid
    ,@doelgroepid = @doelgroepid
    ,@organisatorid = @organisatorid
    ,@regioid = @regioid
    ;


The schema wos_core is probably not in your search_path.

In that case, you need to prefix the operator with the schema name:

CREATE INDEX authors_full_name_idx
       ON wos_core.interface_table USING GIN (authors_full_name wos_core.gin_trgm_ops);

Alternatively you can change the search path of your user, to include wos_core:

alter user your_username set search_path = 'public,wos_core,...';


Finding the name of the supplier that supplies the most parts

You didn't specify your DBMS, so this is ANSI SQL:

select sid,
       num_products
from (
  select sid,
         count(*) as num_products,
         dense_rank() over (order by count(*) desc) as rnk
  from catalog
  group by sid
) t
where rnk = 1;

If you need the information from the supplier table as well, you need to join it:

select s.sname,
       t.num_products
from (
  select sid,
         count(*) as num_products,
         dense_rank() over (order by count(*) desc) as rnk
  from catalog
  group by sid
) t
  join suppliers s on s.sid = t.sid
where t.rnk = 1;

Online example: http://rextester.com/HGL10937


You need at least two. Don't mess around in production.


RIGHT() function in postgresql 9.1 not behaving as anticipated?

The CHAR data type is awful. Avoid it if at all possible, its padding rules are insane. PostgreSQL wouldn't support it at all if the standard didn't require support for it.

It's not clear whether this is intended behaviour or a bug. Usually, leading white-space gets stripped from CHAR before processing, and I suspect that for RIGHT, Pg is stripping the whitespace from the right, as if this were RTL text.

I suggest raising this for discussion on pgsql-general. If nothing else, it clearly warrants a documentation note.


Why is this very simple UPDATE so much slower than the SELECT it contains?

Change to:

SELECT @ct := COUNT(*) FROM photos;
UPDATE counts SET countValue = @ct WHERE countName = 'photos';

Either have autocommit = ON, or at least don't put both statements in the same transaction.  This is to avoid locking counts while counting.

Also, countName should be the PRIMARY KEY.


Need Help With BCNF Decomposition Problem

You solved your assignment, so not much more to add. However, because you mentioned that you are

  "super eager to learn database normalization and optimization so I can up my skill in full-stack development,"

and because I am mostly stuck indoors these days -- I will demonstrate a bit different method to achieve the goal. This method will serve you well for your personal and business projects, not so much for db-related exams and textbook assignments.

So here is the task, we have relvar (table) with attributes, and some mythical creature called (probably) database designer has provided us with functional dependencies.

lots { PROPERTY_ID
     , COUNTY_NAME
     , LOT_NO
     , AREA
     , PRICE
     , TAX_RATE
     }

COUNTY_NAME, LOT_NO -> PROPERTY_ID
PROPERTY_ID -> COUNTY_NAME, LOT_NO
PROPERTY_ID -> COUNTY_NAME, LOT_NO, AREA, PRICE, TAX_RATE
COUNTY_NAME -> TAX_RATE
AREA -> PRICE

The idea now is to verbalize this and write down (simple) predicates and associated constraints so that the problem can be reasoned about in a natural language using logic.

A simple predicate can not be decomposed without losing information, its matching relvar is in 6NF.

[p 1] County COUNTY_NAME exists.

(c 1.1) County is identified by COUNTY_NAME.

county {COUNTY_NAME}  -- p 1
   KEY {COUNTY_NAME}  -- c 1.1

[p 2] Lot number LOT_NO in county COUNTY_NAME exists.

(c 2.1) Lot is identified by COUNTY_NAME, LOT_NO pair.

county_lot {COUNTY_NAME, LOT_NO}  -- p 2
       KEY {COUNTY_NAME, LOT_NO}  -- c 2.1

[p 3] Lot number LOT_NO in county COUNTY_NAME is assigned property number PROPERTY_ID.

(c 3.1) For each lot and county pair, that lot lot and county pair is assigned exactly one property number.

(c 3.2) For each property number, that property number is assigned to exactly one lot and county pair.

property {COUNTY_NAME, LOT_NO, PROPERTY_ID} -- p 3
     KEY {COUNTY_NAME, LOT_NO}              -- c 3.1
     KEY {PROPERTY_ID}                      -- c 3.2

-- this one can be decomposed to
-- {PROPERTY_ID, COUNTY_NAME} {PROPERTY_ID, LOT_NO}
-- but leaving it as is to simplify.

[p 4] County COUNTY_NAME has tax rate of TAX_RATE.

(c 4.1) For each county, that county has exactly one tax rate; for each tax rate, it is possible that more than one county has that tax rate.

county_tax {COUNTY_NAME, TAX_RATE} -- p 4
       KEY {COUNTY_NAME}           -- c 4.1

[p 5] Lot area of AREA is priced at PRICE.

(c 5.1) For each lot area, that lot area is priced at exactly one price; for each price, more than one lot area may be priced at that price.

area_price {AREA, PRICE} -- p 5
       KEY {AREA}        -- c 5.1

[p 6] Property PROPERTY_ID has area of AREA.

(c 6.1) Each property is of exactly one area; for each area, it is possible that more than one property has that area.

property_area {PROPERTY_ID, AREA} -- p 6
          KEY {PROPERTY_ID}       -- c 6.1

At this point, all these relvars represent simple predicates and are in 6NF (well p3  in 5NF). Now I can decide to leave them as is (logically ok) or to combine some of them based on keys, but staying in 5NF.

county_tax {COUNTY_NAME, TAX_RATE}  -- p 1,   p 4
       KEY {COUNTY_NAME}            -- c 1.1, c 4.1

area_price {AREA, PRICE}  -- p 5
       KEY {AREA}         -- c 5.1

property_ { COUNTY_NAME   -- p 2, p 3, p 6
          , LOT_NO
          , PROPERTY_ID
          , AREA
          }
      KEY {COUNTY_NAME, LOT_NO}  -- c 2.1, c 3.1
      KEY {PROPERTY_ID}          -- c 3.2, c 6.1

First two are in 6NF, the third one in 5NF.

This method requires understanding of: 1NF, 6NF, 5NF; or 1NF only, as long as redundancy and lossless decomposition join is properly understood. In other words, you can have your DB in 5NF even if you do not know what that means, as long as you can clearly define predicates and constraints.

So how can one know that relvar is 5NF, if one does not know what it means? Simple, it is not abut being in  this-and-that NF, but about removing redundancy.

  The 5NF is final NF as far as removing redundancy is concerned.
  A relvar (table) is in 5NF iff decomposing it would not remove any redundancies.

Or (same meaning) if at least one of these holds:

There is no redundancy.
There is redundancy, but it can not be removed by decomposition.
It is not possible to decompose table without losing information.
It is not possible to decompose table at all.

Just make sure that when you reason abut redundancy, you reason about predicate and constraints, not about few rows of sample data.

To recap, instead of decomposing it by an algorithm or following functional dependencies, the method "explodes" the predicate of the initial relvar into set of simple predicates (6NF relvars) and then composes them, making sure to stay in 5NF.
It uses logic and natural language, a huge advantage when one needs to communicate with analysts and business people.


Meta Query - Select the SELECT expression as a string field

You can use the current_query function:
SELECT *, current_query() FROM generate_series(1, 10) AS n;

 n  |                        current_query
----+-------------------------------------------------------------
  1 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  2 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  3 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  4 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  5 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  6 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  7 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  8 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  9 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
 10 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
(10 rows)


Specific issue on data pump API in oracle

DETACH is not required, once you submit the job using dp api, it will run in the background. Once job starts, the dumpfile will remain unless job is killed. Once you run DBMS_DATAPUMP.START_JOB (handle => nhandle); the job will start executing. However, once you start and before DP begins (there is short delay when it prepares), if you detach during this time, the job is effectively cancelled and dumpfile will disappear.
Unless you need the dumpfile be backword compatible with certain prior versions of Oracle, you should leave this parameter alone, the default value is fine.


sp_msforeachdb in a stored procedure does not work well

sp_MSforeachdb has lots of known issues.

If you follow the link, you'll get to a post by Aaron Bertrand that details them, and also provides an alternate solution.


Meaning of "nonclustered located on primary"

This is the name of the filegroup or partition scheme that the index is created on. This can be specified when creating an index with a second ON clause.

The sp_help procedure calls sp_helpindex which retrieves the name from sys.data_spaces

  The primary filegroup contains the primary data file and any other
  files not specifically assigned to another filegroup. All pages for
  the system tables are allocated in the primary filegroup.

More info about Files and Filegroups here


How to validate and upgrade my knowledge of SQL Server with MCSA

Do I need this one at all ?

No. The MCSA (and MCSE) are aimed more at administering networks, not SQL Server. These particular 2 are aimed at administering Windows 2003 networks. I think you would be wasting your money taking them as I think these 2 certs will be retiring soon. The Windows 2008 versions use the newer MCTS/MCITP naming convention.

In general, the current naming convention progression is MCP (MC = "Microsoft Certified", P = "professional", one exam, of anything), MCTS (TS = "technical specialist", one introductory technical exam), MCITP (ITP = "IT Professional", the relevant MCTS plus one or more "pro" exams). There are MCM (M = "master") certifications, but those will cost between $10k and $20k, so only if your company will pay for them would they even remotely be practical.

  What is correct name in MS terminology for SQL Server Administrator?

SQL Server certs. Start with the relevant exam for the MCTS, that would most likely be 70-432. Then when you know more, take the 70-450 exam. That will give you the MCITP: Database Administrator SQL Server 2008 certification. You cannot get the equivalent SQL Server 2005 certs because they were retired in June.


What's your experience to deal with the offboarding account?

Central Management Server (or Local Server Groups) is another option for multi server administration.
https://docs.microsoft.com/en-us/sql/relational-databases/administer-multiple-servers-using-central-management-servers?view=sql-server-ver15


It depends on the version.
Look for "multi-source" replication.  This is where multiple Primaries can replicate to a single Replica.  But this does not say how you would restore a single Primary that dies.
Consider using a single Primary for everything, then have one (or more) Replicas.
Or you could have multiple Primary-Replica pairs sitting in VMs or Dockers.  The pair would, of course, have its parts on separate machines, preferably geographically separated (think storms, earthquakes, etc).


Adding new tables -- memory usage increases

Does it increase memory ??? Absolutely !!!

I wrote a post about INFORMATION_SCHEMA (Jun 15, 2011 : How is INFORMATION_SCHEMA implemented in MySQL?)

In that post I explained how metadata for MySQL tables are stored in temporary tables using the MEMORY storage engine. For example, when you create a table mydb.mytable, look at what gets populated into these tables:

INFORMATION_SCHEMA.TABLES (One row for mydb.mytable)
INFORMATION_SCHEMA.PARTITIONS (If mydb.mytable is partitioned, one row for each partition)
INFORMATION_SCHEMA.COLUMNS Info about every column in mydb.mytable
INFORMATION_SCHEMA.STATISTICS : Information about index statistics in mydb.mytable
INFORMATION_SCHEMA.KEY_COLUMN_USAGE : Information about a column's use in all indexes in mydb.mytable
See the full list of INFORMATION_SCHEMA tables

In short, all these tables have to increase in memory usage for each table you create. In your particular case, that would be multiplied by 200.


Subquery - return new row and only fill one column

Only way to format like that is to UNION the queries:

DECLARE @GetInstances TABLE
  (
     value         NVARCHAR(100),
     instancenames NVARCHAR(100),
     data          NVARCHAR(100)
  )

INSERT INTO @GetInstances
EXECUTE Xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

SELECT Serverproperty('ServerName') AS [Server],
       (SELECT Server_type = CASE
                               WHEN virtual_machine_type = 1 THEN 'Virtual'
                               ELSE 'Physical'
                             END
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical],
       CAST((SELECT ( cpu_count / hyperthread_ratio )
        FROM   sys.dm_os_sys_info)
        AS VARCHAR(10))
       AS  [CPU(s)],
       CAST((SELECT CASE
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count
                 WHEN ( ( cpu_count - hyperthread_ratio ) / (
                        cpu_count / hyperthread_ratio )
                      ) = 0 THEN 1
               END
        FROM   sys.dm_os_sys_info)
         AS VARCHAR(10)) AS [Core(s)],
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1)
       AS [Product Name],
       Serverproperty('ProductVersion') AS [Product Version],
       Serverproperty('ProductLevel') AS [Product Level],
       Serverproperty('Edition') AS [Edition]

UNION ALL

SELECT '', instancenames, '', '', '', '', '', ''
FROM   @GetInstances

We return blank strings '' for most columns in the second query as both queries must have the same number of columns. Note I've also CAST [CPU(s)] and [Core(s)] columns in the top query to VARCHAR, otherwise the second query returns 0 in those columns ('' cast to INT = 0).


Copying Oracle datafiles to a system clone

Of course you can, if you can cleanly shut down your old database and it is consistent. A little reading of the documentation or googele-fu would have helped. Searching this site would have provided this excellent answer.

But: what do you want to achieve? If it really is an issue with the patch and your hardware configuration, why do you expect your clone to be not affected?
Is only your SQL*Net communication not possible or does e.g. RDP not work, too?

So before cloning the database without knowing the root cause I would suggest some further investigation and searching MOS. If it really is an MS patch issue that stops SQL*Net from working, there will be almost certainly a note there.

Some peeking in your firewall configuration might help, too, perhaps it is a trivial think like the patch has (re)activated your internal windows firewall.


Migrated DB from 12c to 19c with IMPDP failed

This is a known problem (bug 26380126). The workaround is to exclude statistics, and the re-gather in the target database. Or export using DBMS_STATS.

Actually, Oracle recommends that you never export the statistics with datapump. Use one of the above two mentioned methods instead.


It will not answer your question directly, but I wanted to notice you about this ibdata issue in general. Unfortunately, there is no magic solution to decrease the ibdata file size... It is the central point of the InnoDB engine, removing it (even when database is shutdown properly) will cause data inconsistency/corruption.

The only way to restart from a healthy state (with a small ibdata) is to reinstall a brand new MySQL instance and load the data from your origin, and PLEASE use innodb_file_per_table to avoid having all you data in one single file (MySQL doc here).

To load you current data into your new instance, you can use mysqldump if your dataset is small (few GB) or use "Transporting tablespace" procedures to cleanly import table by table your dataset (example on Percona Blog here).

You can also read this article form Percona Blog which explain many things regarding these ibdata issues.

Max.


Sql Server rollback to some point in time

Use a database snapshot. Create a snapshot, do your worse. If things go ugly, revert to the snapshot, see Revert a Database to a Database Snapshot. Database snapshots are copy-on-write so they take almost no space on disk (the space taken will grow as you update the original db and the copy-on-write must save the previous data). They're fast to create and drop.

If you have to revert from a snapshot it will break the backup log chain:

  Reverting breaks the log backup chain. Therefore, before you can take log backups of the reverted database, you must first take a full database backup or file backup. We recommend a full database backup.

This will impact your maintenance and you must plan accordingly, eg. be prepared to re-seed the log chain with a new full backup. Unfortunately, there is no realistic alternative. Of course, you should test your migration scripts before attempting them on production. You are using scripts, aren't you?


I'd say it depends on how many parameters you have, and whether this list is expected to be largely static or if it's likely to change or expand.

I'm still a little fuzzy on exactly what you're doing, so I'm going to answer a slightly different question.  Let's say you're writing a query engine, searching for users that meet different requirements, and you want to store a set of predicates so it can be reused.  In a simple world, your table might look like this:

CREATE TABLE Searches
    (
    ID  INT NOT NULL PRIMARY KEY,
    CreatedBy   INT NOT NULL,  -- Prob also CreatedDate, LastModified, etc.
    FirstName   VARCHAR(100),
    LastName    VARCHAR(100),
    Sex CHAR(1),
    EMailAddress    VARCHAR(100),
    Country INT
    )

Some day you'll need to add a Region field when you start storing more precise geographic data.  Then you'll add PhoneNumber, and then BloodType, and then... eventually, you'll need something more flexible.  You need a table for searches and a table to store key-value pairs for each search:

CREATE TABLE Searches
    (
    ID  INT NOT NULL PRIMARY KEY,
    CreatedBy   INT NOT NULL
    )
CREATE TABLE SearchTerms
    (
    ID  INT NOT NULL PRIMARY KEY,
    SearchID    INT NOT NULL FOREIGN KEY REFERENCES Searches (ID),
    FieldName   VARCHAR(100) NOT NULL,
    Value   VARCHAR(100) NOT NULL,
    UNIQUE (SearchID, FieldName)
    )

The UNIQUE constraint may not be appropriate.  For example, it may be appropriate to have a search with Terms { FieldName = "FirstName", Value = "Bo%" } and { FieldName = "FirstName", Value = "ob%" }.

This more flexible approach loses type checking; searching for "NumArms = 2" now requires storing "2" in a VARCHAR field.  It also implies using dynamic SQL to construct your searches.


Is there a way to grant a user access to only all non system views of database?

There is no syntax such as

GRANT SELECT ON ALL::Views TO SomeUser

You can GRANT SELECT permissions on individual objects, schemas or the entire database but not filtered by object type to only include Views. For this sort of adhoc task I'd probably create a new role called ViewReader, add the user to that role then in SSMS run

SELECT 'GRANT SELECT ON ' +
          QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) +
      ' TO ViewReader;'
FROM sys.views
WHERE is_ms_shipped = 0

to generate a script to run to GRANT the required permissions.


Converting float4 to numeric in PostgreSQL is rounding the values

The Postgres manual (quoted below) does a good job of explaining some of the rounding issues you might have using floating-point types.
With that in mind, and if we simply accept that the least significant digits of any floating-point type are merely approximate, you have a couple of options for conversion including all the approximated low-precision digits. I've used float4/float8 to keep consistency with your question but we could equally use real/float.:

select '0.0199999996'::float4;

| float4 |
| :----- |
| 0.02   |

select '0.0199999996'::float4::numeric;

| numeric |
| ------: |
|    0.02 |

select '0.0199999996'::float4::float8::numeric;

|            numeric |
| -----------------: |
| 0.0199999995529652 |

select '0.0199999996'::float4::text::numeric;

| numeric |
| ------: |
|    0.02 |

set extra_float_digits = 3;
select '0.0199999996'::float4::text::numeric;

|      numeric |
| -----------: |
| 0.0199999996 |

db<>fiddle here

8.1.3. Floating-Point Types
The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.
Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.

Comparing two floating-point values for equality might not always work as expected.


The information about Eager writer process is available in BOL documents

  Eager writing

  The eager write process writes dirty data pages associated with
  minimally logged operations such as bulk insert and select into. This process
  allows creating and writing new pages to take place in parallel. That
  is, the calling operation does not have to wait until the entire
  operation finishes before writing the pages to disk.

Dirty pages: All pages that have changed in memory since they were read from disk or since the last checkpoint are dirty pages. When pages needs to be read or written onto they are brought from disk into memory.

The eager writing process do not wait for the I/O operation to complete. They always use asynchronous (or overlapped) I/O and continue with other work, checking for I/O success later. This allows SQL Server to maximize both CPU and I/O resources for the appropriate tasks.

  My understanding here is, that both eager and lazy writer are doing the same job - hardening dirty pages to disk. Is my understanding correct or am I missing any thing over here?

Yes they both write dirty pages but they are not same. About checkpoint I suggest you read What is checkpoint and what gets logged. Checkpoint writes all dirty pages to disk while eager writer process writes dirty data pages associated with minimally operations such as bulk insert and select into to disk. Checkpoint also periodically scans the buffer pool.

  there is no chance for roll forward operation in bulk logged recovery model as the trasactions are minimally logged.

What you are saying is correct but I would quote, Kalen Delaney who defined minimally logged operations as:

  ones that log only enough information to roll back the transaction, without supporting point-in-time recovery

What happens in BL recovery model is only enough information is logged to rollback the transaction no information is logged to roll forward the transaction this is clearly shown by Gail Shaw in this Article.

  If we take the case where a log backup consists of minimally logged transactions, how could this log restore can become successful without any roll forward operation?

First you must note that in every restore it is not necessary that you need to have a roll forward operation. The roll forward comes into picture when transaction changes page which has been read by backup operation and if that transaction commits while backup is going on then when such backup is restored a roll forward of transaction is required and that is done by reading transaction log.

In case of minimally logged operation the eager writer process guarantees that changes to extent/pages are hardened on disk before the transaction is complete. So the transaction log has information ONLY about the committed changes of the bulk logged operation and so if required it can rollback(it would just go and de-allocate the extents or pages allocated). Since the changes are already written to disk there is no need for roll forward.


Question about non-clustered index storage in SQL Server

Nonclustered indexes always include a row locator.

For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that the CI key be narrow and not frequently updated.

In the table shown in the question the Clustered index key is a 4 byte integer and potentially may also include a 4 byte uniqueifier for any duplicate key values.

In your case as the NCIs are not declared as unique the CI key will be appended to the NCI key.

For unique non clustered indexes the CI key would be added as included column(s) in the leaf pages unless explicitly made part of the key.

See Kalen Delaney: More About Nonclustered Index Keys for some additional information about how you can see this for yourself.

With these 4 rows of data all three indexes only consume a single 8KB data page.

SELECT index_id,
       index_level,
       page_count,
       record_count
FROM   sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('people'), NULL, NULL, 'DETAILED')

Returns

+----------+-------------+------------+--------------+
| index_id | index_level | page_count | record_count |
+----------+-------------+------------+--------------+
|        1 |           0 |          1 |            4 |
|        2 |           0 |          1 |            4 |
|        3 |           0 |          1 |            4 |
+----------+-------------+------------+--------------+

The additional page shown in use by sys.allocation_units.total_pages is an IAM page. This is not used for storing data but just for tracking the pages and extents comprising the index.


This is just a common answer.
Avoid MYSQL for OOP Concepts for the time being. OODBMS stil under research in some areas.If you are Maping OOP business/program logic to a RDBMS(ORDBMS)  and still expecting open source go for PostgreSQL(PGSQL). PGSQL is not a full OODBMS. But Inheritance can be achieved there.
Your methods may result in unusual processing cycles thus results in excessive workload to the system. If you are going with your methods (In between Helper Tables ), make sure you using correct Indexes.
And, still if you are preferring MYSQL, Use High level of Abstraction to achieve Specialization down the Inheritance hierarchy and link via Foreign keys with parents. Do Normalization  up to 3NF correctly. Yes! this is the usual Relational Way!!


how to find out what process(es) caused the autogrowth of a database or a database file?

The only way to find out what process caused the autogrowth is to use Extended events esp. EVENT --> sqlserver.database_file_size_change & sqlserver.databases_log_file_size_changed and ACTION --> sqlserver.sql_text.

Looks like @DBA_ANDY already did the hard work of writing an XEvent

-- Original Author : @DBA_ANDY http://nebraskasql.blogspot.com/2016/06/finding-file-growths-with-extended.html
-- Modified by : Kin to filter on tempdb autogrowth events
SET NOCOUNT ON
/* Create Extended Events Session */
IF EXISTS (SELECT 1 FROM master.sys.server_event_sessions WHERE name = 'DemoFileSize')
 DROP EVENT SESSION [DemoFileSize] ON SERVER
GO
CREATE EVENT SESSION [DemoFileSize] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
    ACTION(package0.collect_system_time,sqlos.task_time,
 sqlserver.client_app_name,sqlserver.client_hostname,
 sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
 sqlserver.server_instance_name,sqlserver.session_id,
 sqlserver.sql_text,sqlserver.username)
 WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
),
 /* Note -  predicate/filter - will collect only for tempdb */
ADD EVENT sqlserver.databases_log_file_size_changed(
    ACTION(package0.collect_system_time,sqlos.task_time,
 sqlserver.client_app_name,sqlserver.client_hostname,
 sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
 sqlserver.server_instance_name,sqlserver.session_id,
 sqlserver.sql_text,sqlserver.username)
 WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
  )
 /* Note -  predicate/filter - will collect only for tempdb */
ADD TARGET package0.event_file(SET filename=N'D:\XEvent_logs\DemoFileSize.xel',-- change HERE !!
 max_file_size=(500),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [DemoFileSize] ON SERVER
STATE = START;
GO

shred the XEvent xml ...

SELECT
Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
, database_name AS DatabaseName
, dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), timestamp1) as LocalTimeStamp
/* added the timestamp and in XE is UTC - this code converts it to local server time zone */
, file_names
, size_change_mb
, duration
, client_app_name AS Client_Application
, client_hostname
, session_id AS SessionID
, sql_txt   ----------------> This is the process TSQL text !
, sql_username
, Is_Automatic

FROM
(
SELECT
(n.value ('(data[@name="size_change_kb"]/value)[1]', 'int')/1024.0) AS size_change_mb
, n.value('(@timestamp)[1]', 'datetime2') as timestamp1
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
, n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
, n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
, n.value ('(action[@name="sql_text"]/value)[1]','nvarchar(500)') AS sql_txt
, n.value ('(action[@name="username"]/value)[1]','nvarchar(50)') AS sql_username

FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'D:\XEvent_logs\DemoFileSize*.xel' -- CHANGE HERE !!
, NULL
, NULL
, NULL
)
) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY timestamp1 desc

Below is the output:

Important things :

You can use my modified script for reporting on autogrowth events.
Properly size your data and log files, so that autogrowth events do not occur on a frequent basis. Autogrowth should be as an emergency helper !
Enable Instant file initialization, so that data file autogrowth events can be fast.
Use predicates in XEvent e.g. on certain important databases or on the size_change_mb to log only events that meet those predicates.
Use sensible target file size e.g. shredding a 1GB XML will cause you more trouble than shredding a 50 MB XML.
Refer to This is how to shred Extended Event XML faster or How can I shred this Extended Events XML as fast as possible?


Is it possible to change the SQL Server errorlog file number on Linux?

This is not possible at the moment, even on CTP 3.1 of SQL Server 2019 as I write this.
As you've discovered, it requires changing a registry setting, and trying to run xp_instance_regwrite results in the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Bob Ward's book Pro SQL Server on Linux doesn't mention anything either. It refers to filelocation.errorlogfile for setting the path using mssql-conf, but not the number of files.
If I were to hazard a guess, this will eventually find its way into the product, but it is not there now.