Is it possible to edit a partition function on MSSQL 2016?

The implicit conversion of the Unicode literal to varchar is a one-time operation done when the DDL is executed. The "typo" ('N' national character prefix) is not part of the partition function definition nor used by SQL Server after creation. The Unicode literal is generated by SMO when the function definition scripted.
The actual boundary values are a SQL_VARIANT type and match the partition function data type definition regardless of the specified literal. The catalog view query below shows this:
SELECT
      prv.value AS BoundaryValue
    , SQL_VARIANT_PROPERTY(value, 'BaseType') AS DataType
    , SQL_VARIANT_PROPERTY(value, 'MaxLength') AS Length
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
WHERE pf.name = N'Partition_by_customer';

Results:
+---------------+----------+--------+
| BoundaryValue | DataType | Length |
+---------------+----------+--------+
| DMA           | varchar  |     10 |
| DMB           | varchar  |     10 |
| DMC           | varchar  |     10 |
| DMD           | varchar  |     10 |
| DME           | varchar  |     10 |
+---------------+----------+--------+


Is it necessary for a foreign key to be a primary key in another table? Why?

The referencing column may not be unique, as you point out. If the referenced column is also not unique, you have created a many-to-many relationship, which is a right pain to implement and can cause problems maintaining data integrity.
For example, I have two rows in table 1 with the same value in the referenced column. I also have two rows in table 2 with the same value in the referencing column. I then change one of the rows in table 1; which rows in table 2 should also be updated?
If the referenced column is unique, but is not the primary key, then your table may be in an "abnormal form" and the additional unique column may be redundant. The test for this is to ask yourself two questions:

If I make the unique column(s) the new primary key, and drop the old primary key column(s), do I lose any data about the entity? If not, your table is in an abnormal form.
Can the unique column(s) change during the lifespan of the entity? If not, then the column(s) constitute a key candidate key.

In practice, there are sometimes very good reasons for denormalizing tables, but these must be considered carefully on a case-by-case basis.
Note that it is possible to reference a unique non-primary-key column or column group. However, since conceptually the primary key is "The" canonical identifier for the row, it makes more sense to reference the primary key.


SSAS Cube for tracking changes in parent child relationship over time

Parent-child relationship in SSAS isn't straight forward as we might wish. From my experience in SSAS 2008 linking them both together wasn't simple at all.
In those type of cases the most recommended solution would be to flatten the relationship with a (SQL) view in which you link the parent to the child with the date attribute. That view becomes your dimension and on which you check the changes over time.
you can find more data about this in here:
http://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/
and here:
http://www.kimballgroup.com/2012/05/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/
Alternatively:
If you need both the Parent id and the Child in the fact table (because you need to query on both) then in that case I would go on a different direction and include both id's in my fact table. This way you can monitor changes across time as you would for any other dimension.


The ORDER BY, particularly in the absence of an index on column D, will require a sort operation. If there is not enough memory allocated in the sort heap for the statement, it will cause an overflow of data being sorted into a temporary table. The table width will be the sum of the column widths of the result set being sorted, plus some overhead.

There must be a system temporary tablespace with the page size appropriate to accomodate the temporary table width, and the error says that there is no such tablespace.

To create it you could run the following statement, as an example (assuming you use automatic storage in your database):

create system temporary tablespace TMPSYS32 pagesize 32 k

It may also happen that you don't have a bufferpool with that page size (each tablespace must have a bufferpool with matching page size). If so, you will need to create the bufferpool first:

create bufferpool BP32 pagesize 32 k
create system temporary tablespace TMPSYS32 pagesize 32 k bufferpool BP32


Does varchar(n) affect the number of records which can be stored per page even if you store less than n?

I can only speculate what the people meant. Maybe it's related to how MySQL computes index statistics depending on n. The only case that comes to my mind is whether n is higher than 127 or not.

As you may know InnoDB stores a string with its length. The length is stored in so called offset fields. The offset can be either one or two bytes. In REDUNDANT format if the maximum length is less than 128 bytes the offset field will be one byte. Otherwise - two bytes.
In COMPACT and DYNAMIC format the actual field length is taken into account. So, if your field is VARCHAR(5000), but you store a short string like abc the offset field will be one byte.

@jkavalik made a good point about MEMORY tables. VARCHAR(5000) is effectively TEXT i.e. it would need off-index pages to store long values.
If a SELECT query ever needs a temporary table it will be of MEMORY type. Because it doesn't support variable length types it's going to be CHAR(5000). So even if you store only short values the temporary table will be stored on disk. It will be a big performance hit.


Why would a table with a Clustered Columnstore Index have many open rowgroups?

With constant trickle inserts, you very well may end up with numerous open deltastore rowgroups.  The reason for this is that when an insert starts, a new rowgroup is created if all of the existing ones are locked.  From Stairway to Columnstore Indexes Level 5: Adding New Data To Columnstore Indexes

  Any insert of 102,399 or fewer rows is considered a "trickle insert". These rows are added to an open deltastore if one is available (and not locked), or else a new deltastore rowgroup is created for them.

In general, the columnstore index design is optimized for bulk inserts, and when using trickle inserts you'll need to run the reorg on a periodic basis.

Another option, recommended in the Microsoft documentation, is to trickle into a staging table (heap), and when it gets over 102,400 rows, insert those rows into the columstore index.  See Columnstore indexes - Data loading guidance

In any case, after deleting a lot of data, a reorg is recommended on a columnstore index so that the data will actually be deleted, and the resulting deltastore rowgroups will get cleaned up.


Collation conflicts between views and functions when using utf8mb4_unicode_ci collation

Before defining Stored Routines, etc, establish the desired CHARACTER SET and COLLATION.  One way to do that is via SET NAMES.
Also check
SHOW CREATE DATABASE

utf8mb4_unicode_520_ci is newer than utf8mb4_unicode_ci.  (I don't know what collation changes were made by Unicode between their 4.0.0 and 5.2.0 versions.  You may never notice a difference.)


I want to reset a column of the table after a month

Try using a transaction,

BEGIN;
  INSERT INTO t2
    SELECT *
    FROM t1
    WHERE status=0
    FOR UPDATE;

  DELETE FROM t1
    WHERE status=0;

COMMIT;


What is the best database design to keep track of fields with multiple values?

Your new design constraint is such that there's a many-to-many relationship between pages and fields, and a many-to-many relationship between page-fields and field values.  That means you'd have to do this:

Page
* Page_Id

Field
* Field_Id
* Field_Type_Id
* Field_Name

Page_Field
* Page_Field_ID
* Page_Id
* Field_Id

Page_Field_FieldValue
* Page_Field_FieldValue_ID
* Page_Field_ID
* FieldValue_Id

FieldValue
* FieldValue_Id
* FieldValue_Value
* Current_Value

"That's really gross" you're thinking.  Yes, it is.  I'd strongly suggest creating views (indexed or materialized if possible) to make retrieval easier.  It doesn't really solve your problem of having to define everything for each new page, but at least you won't be storing the same data repeatedly.

The Field_Type table doesn't change at all.  It's relation to Field is the same.  I did not list it in order to reduce confusion.

I'd also caution about using the FieldValue.Current_Value field as you seem to be suggesting, as that will not really work if your application allows multiple users.  Application state needs to be stored separately from application data, typically.


How to SELECT multi-columns from a column?

SELECT t0.book_id, t0.select_id, t0.note_id, t1.TEXT_number_1, t2.TEXT_number_2
FROM (SELECT DISTINCT book_id, select_id, note_id
      FROM `table`) t0
LEFT JOIN `table` t1 ON t0.book_id=t1.book_id
                     AND t1.item_number=1
                  /* AND t0.select_id=t1.select_id
                     AND t0.note_id=t1.note_id */
LEFT JOIN `table` t2 ON t0.book_id=t2.book_id
                     AND t2.item_number=2
                  /* AND t0.select_id=t2.select_id
                     AND t0.note_id=t2.note_id */


6 datacenters running the same mysql database, replication layout design question

20 commits/second is not very fast.

A Master can have any number of Slaves.  Those Slaves can be local to the same datacenter or remote, in the other datacenters.

You can have "relay" servers that act like a Slave to the Master, but then act like a Master to 'downstream' Slaves.  With this kind of topology, you can have an unlimited number of Slaves.  (I have not seen it more than 3 level deep including the Master.  Nor have I seen more than a few dozen Slaves total.  But no "limit" was being hit.)

Multiple Masters is a much different matter.  Usually the best topology is to have two Masters, one being written to and the other being a hot backup.  This is "Dual Master, single writer".  Each of them can have Slave(s).

Even better is Galera Clustering -- See MariaDB or Percona (PXC).  It allows for 3 or more writable masters and handles most of the nasties that led me to say "single writer" in the previous paragraph.  Again, these "nodes" can have "Slave(s)" hanging off them.

Keep in mind that a Slave can only be read from.  To "write" you need to hit a Master.  If the master is on the "other side of the country (or world)", there are speed-of-light delays, upwards of 100ms to go across the US.

The delays do not necessarily impact the "20 commits/sec".  At 100ms, you cannot perform more than 10 commits/sec from a single connection.  But, you probably have multiple connections, correct?

At 100 commits/sec, some disk tuning issues come into play.  At 1000/sec, other issues raise their ugly heads.  Some carefully crafted benchmarks have run at 1M/sec.

You have not explained what you mean by "ID consistency".  One thing comes to mind:  When writing to both Masters in a Dual-Master setup, you are asking for this kind of trouble.


Is there a name for this database schema of key values?

It's called Entity-Attribute-Value (also sometimes 'name-value pairs') and it's a classic case of "a round peg in a square hole" when people use the EAV pattern in a relational database.

Here's a list of why you shouldn't use EAV:

You can't use data types. It doesn't matter if the value is a date, a number or money (decimal). It's always going to be coerced to varchar. This can be anything from a minor performance problem to a massive gut-ache (ever had to chase down a one-cent variation in a monthly roll-up report?).
You can't (easily) enforce constraints. It requires a ridiculous amount of code to enforce "Everyone needs to have a height between 0 and 3 metres" or "Age must be not null and >= 0", as opposed to the 1-2 lines that each of those constraints would be in a properly-modelled system.
Related to above, you can't easily guarantee that you get the information you need for each client (age might be missing from one, then the next might be missing their height etc.). You can do it, but it's a hell of a lot more difficult than SELECT height, weight, age FROM Client where height is null or weight is null.
Related again, duplicate data is a lot harder to detect (what happens if they give you two ages for one client? De-EAVing the data, as below, will give you two rows of results if you have one attribute doubled. If one client has two separate entries for two attributes, you'll get four rows from the query below).
You can't even guarantee that the attribute names are consistent. "Age_yr" might become "AGE_IN_YEARS" or "age". (Admittedly this is less of a problem when you're receiving an extract versus when people are inserting data, but still.)
Any sort of nontrivial query is a complete disaster. To relationalise a three-attribute EAV system so you can query it in a rational fashion requires three joins of the EAV table.

Compare:

SELECT cID.ID AS [ID], cH.Value AS [Height], cW.Value AS [Weight], cA.Value AS [Age]
FROM (SELECT DISTINCT ID FROM Client) cID
      LEFT OUTER JOIN
    Client cW ON cID.ID = cW.ID AND cW.Metric = "Wt_kg"
      LEFT OUTER JOIN
    Client cH ON cID.ID = cH.ID AND cW.Metric = "Ht_cm"
      LEFT OUTER JOIN
    Client cA ON cID.ID = cA.ID AND cW.Metric = "Age_yr"

To:

SELECT c.ID, c.Ht_cm, c.Wt_kg, c.Age_yr
FROM Client c

Here's a (very short) list of when you should use EAV:

When there's absolutely no way around it and you have to support schema-less data in your database.
When you just need to store "stuff" and don't expect to have to need it in a more structured form. Beware, though, the monster called "changing requirements".

I know I just spent this entire post detailing why EAV is a terrible idea in most cases - but there are a few cases where it's needed/unavoidable. however, most of the time (including the example above), it's going to be far more hassle than it's worth. If you have a requirement for wide support of EAV-type data input, you should look at storing them in a key-value system, e.g. Hadoop/HBase, CouchDB, MongoDB, Cassandra, BerkeleyDB.


How do I get the row as column and one of the column as rows under the first column data

Use Inner Join to display the three queries horizontally. Then use Row_number for setting join conditions,

As a demo:-

create table #temp (Tname varchar(10) , Scode varchar(10))
insert into #temp values ('SVC','code1')
insert into #temp values ('SVC','code2')
insert into #temp values ('SVC','code3')
insert into #temp values ('AKV','code1')
insert into #temp values ('AKV','code12')
insert into #temp values ('AKV','code3')
insert into #temp values ('AKS','code3')
insert into #temp values ('AKS','code2')
insert into #temp values ('AKS','code1')

go

SELECT SVC , AKV , AKS
FROM (
    select Scode as SVC , row_number() over(partition by Tname order by (select 1)) rn
    from #temp where Tname = 'SVC')  t1
INNER JOIN (
    select  Scode as AKV , row_number() over(partition by Tname order by (select 1)) rn
    from #temp where Tname = 'AKV' )  t2
    on t1.rn = t2.rn
INNER JOIN (
    select  Scode as AKS , row_number() over(partition by Tname order by (select 1)) rn
    from #temp where Tname = 'AKS' ) t3
    on t2.rn = t3.rn

--drop table #temp

Result:-

| SVC       | AKV       | AKS       |
|-------    |--------   |-------    |
| code2     | code1     | code3     |
| code3     | code12    | code2     |
| code3     | code3     | code1     |


This is a complex question and you will get a complex answer.

There is no reason you can't put a tablespace on an encrypted filesystem.  Additionally pgcrypto gives you a toolkit for doing data encryption but data encryption in the db is hard and there are too many ways keys can be disclosed to do this without review and assistance.  This isn't at the table level but rather at the value level, and pgcrypto gives you a plethora of options there.  Please see the pgcrypto documentation for details.


ORA-01045 when attempting to connect to PDB. User has CREATE SESSION privilege, but I Oracle says they don't

Why do you want to create a common user instead of creating a user in PDB?
For a common user C##test from CDB
grant create session to c##test container=all;

or
From PDB login as sysdba/dba/pdbadmin
grant create session to c##test;


When a date is represented as 8 numerals without any delimiters, SQL Server will always try to interpret it as YYYYMMDD. That format has deliberately been chosen as locale- or culture-independent (being also one of ISO 8601 standard date formats, as a matter of fact), and there is no way to have the server interpret it as DDMMYYYY or in any other way.

So, either rearrange the date parts of your DDMMYYYY string to make it YYYYMMDD:

CONVERT(
  date,
  SUBSTRING(@datestring, 5, 4) + SUBSTRING(@datestring, 3, 2) + SUBSTRING(@datestring, 1, 2)
)

or insert delimiters to make it match one of the known delimited DMY formats, for instance DD/MM/YYYY:

CONVERT(
  date,
  STUFF(STUFF(@datestring, 5, 0, '/'), 3, 0, '/'),
  103  -- DD/MM/YYYY
)


Structuring SQLite database with 2 relationships?

If I get you right then one post is linked to one user but one user can have many posts.
I think this is a one to many relationship. So, put a field user_id in your table post then you know which post is linked to which user.

It's a german page but the first image would help you.


Incorrect "Sensitive data columns should be classified" report

Basically it will not fade away when we approve it as a baseline. Sometimes it does. But in most cases we will get baseline mismatch where we should go inside the database to classify those data that we approved as the baseline in the azure portal.
For more details on how to classify the data inside SQL please follow the below links.Data discovery and classification
RDBMS security


We connected with Tibco support to resolve this issue.

Steps:

Change jar from mysql-connector-java-8.0.11.jar to mysql-connector-java-5.1.48.jar. Place the jar file "mysql-connector-java-5.1.48.jar" in location
"\conf\adapters\system\mysql_5_5". Then restart the TDV server.
Create a new MySql 5.5 datasource with same credentials. Open the advanced settings panel for the new datasource. Then change the connection format to "mysql://xx.xx.xx.xx:3306/DBName?useSSL=false&allowPublicKeyRetrieval=true"

Connection is successful.


Out of operation records in local data manager (increase MaxNoOfLocalOperations) from NDBCLUSTER

vi config.ini, /var/lib/mysql-cluster/XX/config.ini or /var/lib/mysql-cluster/config.ini
※XX : id of ndb_mgmd(MGM) node
add MaxNoOfConcurrentOperations=2000000
restart ndb cluster


MySQL Documentation Under Natural Language Full-Text Searches

Paragraph 1

  By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). *For each row in the table, **MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list***.

Paragraph 3

  When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

When doing Boolean Searches, MATCH() will return 0 or 1.

NOTE TO MyISAM USERS

You can use the myisam_ftdump utility against the MyISAM table to get a complete listing of words and their frequency.

NOTE TO InnoDB USERS

There is a wonderful explanation of how InnoDB calculates Relevance in Boolean Fulltext Searching under the subheading Relevancy Rankings for InnoDB Boolean Mode Search in the MySQL Documentation (Boolean Full-Text Searches)


The database system then writes out enough information to disk

The concept of Transaction Journalisation has been release in the 70's by Bernstein and Gray. The algorithm used is based on ARIES for all the Relational DBMS.
The way that does it is quite simple. Every write that must occurs in the database (INSERT, UPDATE, DELETE, but also CREATE, ALTER, DROP, GRANT...) need a safety net, until the all work is done. To do this, the values before modifiying the database state is physically copyed into a special storage called "transaction log". After the data has been modified in memory, this transaction is marked as "done" in the transaction log, if no exception is raised. That is the COMMIT. In case of an exception (or a voluntary cancel of the writes) that's the ROLLBACK, and previous values are replaced in the memory.
I say that data are written in memory... Not on disk. But the problem is that a server has generally not enough memory to store inside the RAM all the datas.. Also, on a regular tempo, a process called CHECKPOINT is started in order to transcribe, on the data storage, the data modified in memory between two passages.
CHECKPOINT occurs generally on different objectives : a limit of amount of data, a limit of time and a limit on memory pressure...
Let me know if I am not clear or you want some complementary explanations
I am writing a french book about SQL and relational databases that explains all that folks...
To be complet, please read "Database Management Systems 3rd Edition by Johannes Ramakrishnan Raghu page 536"


Several things are wrong:

While the UPDATE statement can be inside a CASE statement, you don't really need it. You only want to update a single row. And you have access to that row with the OLD and NEW keywords (values before and after the update. You can use SET NEW.price = ...
To modify a value in an update trigger, it has to be an BEFORE trigger, not AFTER.
The CASE statement (that has a SET) can be converted to a CASE expression (inside a SET)
Finally, if the columns are not nullable, it would be simpler to use the function LEAST() instead of a CASE expression.

The trigger code:

DELIMITER //
CREATE TRIGGER t__price_final
  BEFORE UPDATE ON market__combination_price
  FOR EACH ROW
  BEGIN
    SET NEW.price_final = LEAST(NEW.price, NEW.offer) ;
                     -- if price or offer are nullable:
                     -- = CASE WHEN offer < price OR price IS NULL
                     --       THEN offer
                     --       ELSE price
                     --   END ;
  END ;
//


Insert JSONL with two columns of comma separated fields into separate rows in mysql

The parsing consists from 2 stages/queries.
First stage parses root object properties.
WITH
cte1 AS (SELECT @json AS val)
SELECT val->>"$.propertyId.ea" + 0 AS ea,
       val->>"$.propertyId.h" AS h,
       val->>"$.propertyId.vo" AS vo,
       val->>"$.thumbnail.width" + 0 AS t_width,
       val->>"$.thumbnail.height" + 0 AS t_height,
       val->>"$.thumbnail.link" AS t_link,
       val->>"$.hero.title" AS h_title,
       val->>"$.hero.width" + 0 AS h_width,
       val->>"$.hero.height" + 0 AS h_height,
       val->>"$.hero.link" AS h_link
FROM cte1

Output:

ea
h
vo
t_width
t_height
t_link
h_title
h_width
h_height
h_link

12345678

1000
500
xxx.jpg
Featured Image
1000
669
yyy.jpg

Second step parses its subobjects.
WITH
cte1 AS (SELECT @json AS val),
cte2 AS (SELECT val->>"$.propertyId.ea" + 0 AS ea,
                val->"$.images.VIEWS" AS views,
                val->"$.images.ROOMS" AS rooms,
                val->"$.images.EXTERIOR" AS exterior,
                val->"$.images.AMENITIES" AS amenities,
                val->"$.images.POOL" AS pool
         FROM cte1),
cte3 AS (SELECT cte2.ea, 'views' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.views AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.views IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'rooms' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.rooms AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.rooms IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'exterior' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.exterior AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.exterior IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'amenities' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.amenities AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.amenities IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'pool' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.pool AS JSON),
                               "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                               width INT PATH "$.width",
                                               height INT PATH "$.height",
                                               link VARCHAR(255) PATH "$.link")) jsontable
         WHERE cte2.pool IS NOT NULL)
SELECT * FROM cte3;

Output:

ea
type
title
width
height
link

12345678
views
View from Property
1000
666
a1.jpg

12345678
views
View from Property
1000
666
a2.jpg

12345678
rooms
Room
1000
669
b1.jpg

12345678
rooms
Room
1000
662
b2.jpg

12345678
exterior
Terrace
1000
666
c1.jpg

12345678
exterior
Terrace
1000
666
c2.jpg

12345678
amenities
Property Amenity
1000
666
d1.jpg

12345678
pool
Outdoor Pool
1000
666
e1.jpg

12345678
pool
Outdoor Pool
1000
666
e2.jpg

You may use these queries as a rowsource for INSERT statement, combine these queries if needed.
If "$.propertyId.ea" is not enough for unique root object identifying then add necessary columns into the last step query.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=81ebab30922db5f99abd90c8b39622e5
PS. + 0 converts extracted string value to numeric datatype.


Importing 40 gb mysql file via phpmyadmin

If you really want to restore it with PHPmyadmin you have to split the files into little chunks, because PHP will block too large files, depending on the serverconfig.

Dump just the structure of your tables
Import them
Dump the data of your tables into a csv
split the csv (on windows you can do this with gsplit for example and on unix with the split command
Import the little chunks of data

But in general i'll recommend the commandline or contact your ISP


Tabular and matrix are not formats of the report.

Try to create a report server project instead of report project wizard in SSRS. Create a new report item (through add->new item) and check the toolbox. You'll find labels, charts, tablices which you can arrange to your personal taste, and if you are using C# for the application you can use ReportingService library to connect reports with your app.


Rebuild dataguard configuration when standby is broken

I wouldn't try to "restore" it.  I'd just blow it away and rebuild from scratch, just like you did when you built it the first time.

And take steps to prevent the problem from repeating.


Are there tools for SQL-Server helping to add the semicolons?

Start typing and don't trust code generators.

As you bring up procedures and code to edit it for other reasons, just add the semi-colon in then.  You wouldn't want to deploy all your stored procedures in one batch just to add in the semi-colons only.


Why would I not specify ROWLOCK to get rid of page locks?

Assuming the availability of an index to provide the requested sort order (SomeColumn ASC) the sample query you posted will in all likelihood take a row lock, even where the index in question is not covering for the query.

The query processor is smart enough to know that reading a single row from one end of an index (possibly followed by a lookup to retrieve additional columns) is a low-cost enterprise that suits a row locking strategy.

If there is no such index, or the execution plan is more complex than the sample query suggests (perhaps because the table is partitioned, or there is a WHERE clause), so that a significant amount of data must be read (and probably sorted) then the engine may start with page locks.

Note that ROWLOCK hints are suggestions. The engine can and will ignore the hint in some circumstances. This differs from other 'hints' like FORCESEEK that will throw an error if it cannot be honoured.

One reason not to use ROWLOCK is that it runs the risk of a very large number of locks being taken (and possibly held to the end of the transaction, depending on the exact circumstances). If there are conflicting locks on the table (or partition), these row locks would be prevented from escalating to a partition or table lock to release memory, which could affect the entire instance.

Ultimately, the details depend on localized details that are not provided in the question. That simple query should not require a ROWLOCK hint, given correct indexing. Nevertheless, the evidence suggests that page locks are currently being taken, which READPAST cannot skip.

If this query is being used to implement some sort of queuing mechanism, you would probably benefit from reading Remus Rusanu's excellent article on Using Tables As Queues.


COPY command only works as command line argument, not as part of script

The answer was to be found in this pgsql-sql mailing list archive:

psql's interpretation of "stdin" when reading from a file
is that the COPY data is to come from that same file (look at the script
produced by pg_dump for an example).  So it reads to the end of the
file, which is right away --- otherwise you'd get some incorrect-data
errors.  The data sourced from the cat command is never noticed at all.

The thread also lists 4 solutions for achieving the same thing:
1) using COPY FROM STDIN
cat event.csv | psql -c "$(cat event.sql)"

2) using COPY FROM STDIN
psql -f  <(cat event.sql event.csv)

3) using \COPY FROM PSTDIN
cat event.csv | psql -f event.sql

4) using \COPY FROM STDIN
psql -f  <(cat event.sql event.csv <(echo "\."))


How best to store multi-dimensional timeseries data in a (R)DBMS environment?

I think you're over-complicating things.  For this example

Modeling this as a relational table is simple:

create table Forecast
(
  reference_time datetime,
  latitude float,
  longitude float,
  precipitation float,
  temperature float,
  constraint primary key (reference_time,latitude,longitude)
)

It may not be extremely efficient for storage space and processing, depending on data structures and algorithms used by
the RDBMS that implements it.  But it's logically very straightforward to model this as a relation.


Cannot restore MySQL database from .frm and .ibd files

Overall your method/steps look good.

Step3: Change you CREATE TABLE statement

CREATE TABLE sunflower.#mysql50#USDT@002dNXT_hour

This should create the table the way you expect it to be i.e. no 0040 is added to table name.

Step 6: This shouldn't be required after change to Step3 as mentioned above

Give this a try.


its resourseId in your table definition and you are using resourceId in query.

This will work

SELECT e.resourceName FROM resources_in_room r join resource e on r.resourseId = e.resourceId where r.roomId=2;


What can be used with SQL Server 2014 native tools, for Business Inteligence?

If SQL Server 2016 is on your road-map it provides significantly improved SSRS visuals. This should address the "pretty" factor of reports while also incorporating Mobile support and a few additional out-of-the-box charts/graphics/indicators for users. Report Builder is still available for the elusive "power user" to create their own reports.

The "Power" suite of applications (Power View, Power Map) can be incorporated into Excel and SharePoint to mixed degrees and effects and are free depending on version(s). PowerBI is the evolution, combination and refinement of these tools.

PowerBI is a Microsoft technology that works with a variety of data sources with strong emphasis on both "pretty" and actual insight. This is another tool that has placed emphasis on ease of use to help reduce IT load and specialization.

There are plenty of competing non Microsoft solutions that will undoubtedly work with SQL Server 2014.

How to implement? Show a pretty screen cap to senior management and money may free up? Never under-estimate the value of a prototype. Or note the fringe benefits that may become paramount investment points later: SQL Server 2016 offers increased performance, security... oh and they also have some nifty reporting that's included in the license costs.


How to get all create for index and Constraints in PostgreSQL using just SQL

To show all the indexes of a table, use pg_indexes  PostgreSQL List Indexes

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'table_name';

Replace <table name> with the name of your table.

Constraints can be retrieved via pg_catalog.pg_constraint. pg_constraint

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

Replace <schema name> with the name of your schema and <table name> with the name of your table.


Getting the name of the column that was used in a join?

There is no sys object or DMV that will give you such information.  You'll have to build it yourself, for which you can have a look at the example below.

Let's prepare some tables and dummy data.

CREATE TABLE ROOTTABLE (FIRST_ID INT, SECOND_ID INT, THIRD_ID INT, ONESETTING_ID INT);
CREATE TABLE SECONDTABLE (SECOND_ID INT, SECONDSETTING_ID INT);
CREATE TABLE THIRDTABLE (THIRD_ID INT, FOURTH_ID INT, RANDSETTING_ID INT, THIRDSETTING_ID INT);
CREATE TABLE FOURTHTABLE (FOURTH_ID INT, ONESETTING_ID INT);
CREATE TABLE SETTINGTABLE (ID INT, VALUE Varchar(10));

INSERT ROOTTABLE VALUES (1,2,3,15),(1,1,1,12),(11,5,6,12);
INSERT SECONDTABLE VALUES (1,11),(2,15),(5,13);
INSERT THIRDTABLE VALUES (1,5,6,15),(6,7,8,15),(3,7,8,15);
INSERT FOURTHTABLE VALUES (5,15),(6,16),(7,17),(1,18);
INSERT SETTINGTABLE VALUES
  (11,'one'),
  (12,'two'),
  (13,'three'),
  (14,'four'),
  (15,'five'),
  (16,'six'),
  (17,'seven');

Here's the query with the MATCHES column to show where the JOIN condition was fulfilled from (SETS.ID = ?).

SELECT FIRST.FIRST_ID, SEC.SECOND_ID, TRD.THIRD_ID, FRTH.FOURTH_ID, SETS.ID, SETS.VALUE,
       STUFF((SELECT ','+SOURCE_TABLE
              FROM (VALUES
                    ('ROOTTABLE',   FIRST.ONESETTING_ID),  -- 1
                    ('SECONDTABLE', SEC.SECONDSETTING_ID), -- 2
                    ('THIRDTABLE',  TRD.THIRDSETTING_ID),  -- 3
                    ('THIRDTABLE',  TRD.RANDSETTING_ID),   -- 4
                    ('FOURTHTABLE', FRTH.ONESETTING_ID))   -- 5
                   X (SOURCE_TABLE, SOURCE_ID)
              WHERE X.SOURCE_ID = SETS.ID
              FOR XML PATH('')),1,1,'') MATCHES
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECOND_ID = FIRST.SECOND_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRD_ID = FIRST.THIRD_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTH_ID = TRD.FOURTH_ID
LEFT JOIN SETTINGTABLE SETS
     ON (SETS.ID = FIRST.ONESETTING_ID    -- 1
      OR SETS.ID = SEC.SECONDSETTING_ID   -- 2
      OR SETS.ID = TRD.THIRDSETTING_ID    -- 3
      OR SETS.ID = TRD.RANDSETTING_ID     -- 4
      OR SETS.ID = FRTH.ONESETTING_ID);   -- 5

Note that I have laid out the sources that could make the match (1-5) in both the VALUES rows constructor and ON clauses so that you can follow the pattern.

Results:

| FIRST_ID | SECOND_ID | THIRD_ID | FOURTH_ID | ID | VALUE |                          MATCHES |
-----------------------------------------------------------------------------------------------
|        1 |         2 |        3 |         7 | 15 |  five | ROOTTABLE,SECONDTABLE,THIRDTABLE |
|        1 |         2 |        3 |         7 | 17 | seven |                      FOURTHTABLE |
|        1 |         1 |        1 |         5 | 11 |   one |                      SECONDTABLE |
|        1 |         1 |        1 |         5 | 12 |   two |                        ROOTTABLE |
|        1 |         1 |        1 |         5 | 15 |  five |           THIRDTABLE,FOURTHTABLE |
|       11 |         5 |        6 |         7 | 12 |   two |                        ROOTTABLE |
|       11 |         5 |        6 |         7 | 13 | three |                      SECONDTABLE |
|       11 |         5 |        6 |         7 | 15 |  five |                       THIRDTABLE |
|       11 |         5 |        6 |         7 | 17 | seven |                      FOURTHTABLE |

Some things to note:

The multiple ORs in the JOIN mean that SETS.ID could be matched by any of the sources - the query shows them all comma separated.
Rows 3-5 with the leading columns (1, 1, 1, 5) match SETS.ID three times.

SQL Fiddle

For clarity, this other SQL Fiddle shows the additional *SETTING_ID columns that are used to match against SETS.ID.


Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

The error is related to the difference between Innodb definition and the frm file definition.

It probably occurred because of a crash during an ALTER TABLE or similar statement at some point.

To restore the consistency an ALTER TABLE wtb_device_auths FORCE will recreate the table (while exclusively locking it). This recreation should make both the innodb and frm definitions consistent.


Is there a table size above which SELECT performance degrades significantly?

Not sure what version of TokuDB you are running, or what MySQL/MariaDB distribution, but there is an index condition pushdown bug that occurs on queries similar to the one you've provided. We are still waiting for a fix, the only current workaround is to disable ICP when running the problematic query.

Try running the query with ICP turned off. To disable it, issue the following command in the session just before running the query:

SET optimizer_switch='index_condition_pushdown=off';

And please report back success/failure, and the particular versions of TokuDB and MySQL/MariaDB.

(full disclosure, I'm an employee of Tokutek, makers of TokuDB)


Comparing records from same table and deleting the old ones

You don't need PL/SQL for this, you can delete using a sub-query:
delete from table1 where ID in (
  select ID from (
    select ID
      ,count(*) over(partition by POSITION_NBR, EFFDT) as cnt
      from table1
    )
  where cnt > 1 --more than one row matches the grouping condition
);


AWS VS Azure , which one is preferable for SQL Server 2012 standard version?

With Azure SQL Database you have the option of Geo-Replication. Active geo-replication enables you to configure up to four readable secondary databases in the same or different data center locations (regions). Secondary databases are available for querying and for failover if there is a data center outage or the inability to connect to the primary database. The failover must be initiated manually by the application of the user. After failover, the new primary has a different connection end point. You can learn more about this Azure SQL Database feature here.


ER Diagram - View Intergration/Centralised approach

Centralized approach

Requirements for each  user view are merged into a single list of requirements for the new database system. A logical data model representing all user views is created during the database design stage. The centralized approach involves collating the requirements for different user views into a single list of requirements. A data model representing all user views is created in the database design stage.

view integration

Requirements for each user view remain as separate lists. Data models representing each user view are created and then merged later during the database design stage. The view integration approach involves leaving the requirements for each user view as separate lists of requirements. We create data models representing each user view. A data model that represents a single user view is called a local logical data model. We then merge the local data models to create a global logical data model representing all user views of the company


The SHOW ENGINE INNODB STATUS \G command will show data from the last time the command was run till now - not sure what it will be the first time. I can't shut down the server here, but if you run the command rapidly twice in succession, you'll see the time since last run - set your

mysql> pager less;

mysql> show engine innodb status \G

=====================================
2014-07-04 15:44:42 7f0a2d9c9700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds

Take a look here - averages of less than ~30 seconds are worthless
according to Peter Zaitsev. You could also take a look at Baron Schwartz's
innotop tool - available here.


PostgreSQL data files have size more than data itself

Size can differ due to several reasons:

indexes take some disk space,
there can be several copies of the same record on disk,
slack space in pages.

Indexes take up disk space in order to facilitate faster lookup. The more indexes you have, the more disk space your DB will take up. GIN indexes are usually smaller, but aren't useful if you use range queries.

PostgreSQL supports concurrent access, which is implemented so, that updates and deletes on records only change the visibility of the records and not actually delete or overwrite the data, since one record may be used by another transaction. With update, a new (updated) copy gets then added. Both mean that old data is still written on disk. To free it up, PostgreSQL periodically performs vacuuming, which really removes the deleted records (after no transaction is using them).

PostgreSQL has default block size of 8KB. If your records are big, say 5KB, you can only get one record in one block, having fairly large (~3KB) slack.

Some possible solutions are:

Be sure to know how to check for disk usage in PostgreSQL,
reevaluate if you really need all the indexes,
think about what your data access pattern is - if it's append only table, then there should be very little invisible records. If on the other hand your data is changed for a time and gets archived after a period, then it's quote possible, that multiple records are on disk. If this is the case, then partitioning your table could help, since you can vacuum partitions separately.
you can vacuum the table manually. Don't use VACUUM FULL, since it will write a new copy of the table and requires appropriate amounts of disk space.
you can use some of the online (online as in you don't need to shut down the DB) repacking tools, such as pg_repack.

Edit:

pg_dump

pg_dump is okay, if you specify to dump data, as you've said you did. If you use custom format (-Fc flags), pg_restore will be able to do some extra stuff with it, such as load only specified tables, also see pg_dump's man page. Custom format gzips dump by default. This may slow your dumping, so you may want to disable that, and if you still want data gzipped, there's a parallel gzip (pigz).

Backups

When you backup, you backup data in tables. Indexes get recreated from data in tables. If you can afford heavy IO on the database when you are doing backups and restores, then pg_dump and pg_restore may suit your needs. When dumping, pg_dump only dumps tables, and when restoring, indexes get rebuilt automatically as part of restore. So to make it clear: backups don't care about indexes.

If heavy IO and degraded performance is not acceptable, then you might want to have a secondary replication server, which will have a copy of data, but won't serve your ordinary queries, so you can use it to make dumps.

If you need point in time restore capability, you could setup WAL log (write-ahead log) archiving, which then enables you to restore to a specific transaction, but this is quite advanced. There are tools to help, such as Barman.

Big indexes

As you've already discovered, indexes can take up a lot of space. If you add up your data and indexes, you get your DB size: 28GB + 42GB = 70GB.

Having big indexes means there's a lot of extra disk space used. Index data is also cached in memory, so having big indexes means you may have two copies of indexed data in RAM, which means there's less RAM for caching data on disk and you get more cache misses. There are some options to evaluate to make indexes smaller:

partial indexes: If the application only queries where a one field is set to a specific value, you can limit which rows are indexed with a WHERE clause on CREATE INDEX.
make use of index scan: If you can include all the queried data in an index, PostgreSQL can return by reading index data only. Works since PostgreSQL 9.2, see more about index scans on wiki.
use another type of index: If your application only does equality lookup (WHERE a = 4), GIN indexes can be a lot smaller, see btree_gin extension. PostgreSQL 9.5 will also bring BRIN indexes, which are really useful for fields, which have a monotonous increasing or decreasing pattern, such as timestamps and they are really really small, so you can free up both disk space for more storage and RAM for caching more data.

But ... a lot of this depends on how data is accessed, which is often only known by developer of the application.


Must declare the scalar variable question

Mostly to eliminate all the SQL injection and string handling issues, and remove things unused:

CREATE PROCEDURE [dbo].[SP_getAvg]
    @projectId INT
    ,@carrierId INT
    ,@fetchType VARCHAR(20)
AS
BEGIN
  DECLARE @recAvg FLOAT,
          @FACCT VARCHAR(20),
          @counter INT = 1,
          @carrierAlias VARCHAR(20),
          @sql NVARCHAR(MAX);

  IF @projectId > 0
  BEGIN
    SELECT @FACCT = FACCT FROM projects WHERE projectId = @projectId;
  END

  SELECT @carrierAlias = carrierAlias FROM carriers WHERE carrierId = @carrierId;

  SET @sql = N'SELECT @recAvg = ISNULL(AVG(cast(FWEIGHT as float)/case
      when CAST(FPIECES as float) = 0 then 1 else cast(FPIECES as float) end),0)
      from psorderh where DATEPART(MONTH, manifestDate) = @counter
        AND DATEPART(YEAR, manifestDate) = DATEPART(YEAR, GETDATE())'
    + CASE WHEN @projectId > 0 THEN N' AND FACCT = @FACCT' ELSE N'' END
    + CASE WHEN @carrierId > 0 THEN N' AND FCARRIER = @carrierAlias' ELSE N'' END;

    DECLARE @params nvarchar(max)
      = N'@counter int,@FACCT varchar(20),@carrierAlias varchar(20),@recAvg float OUTPUT';

    EXEC sys.sp_executesql @sql, @params, @counter, @FACCT, @carrierAlias, @recAvg OUTPUT;

    PRINT @recAvg;
END
GO


Designing a database for Reading Comprehension

1st things first:

Use TEXT for all character fields including small ones! In SQLite, VARCHAR is just TEXT behind the scenes anyway (same is true for PostgreSQL if ever you want to upgrade to a multi-user RDBMS).

Note on style (others may differ - but I know best! :-) ):

Use lower case for your identifiers (table and column names) and UPPER CASE for your SQL KEYWORDS (SELECT, FROM, ORDER BY... &c.). Use underscores_to_separate_identifier_parts! Use singular names for identifiers - a table is a set anyway, so why pluralise it? If you have a problem with any of this, there are plenty of style guides (this one is good) out there, but if you choose a convention, then stick to it! This will make life much easier. Finally, make use of the comments facility - in SQLite, it's not great, but it's there! It's easier to just issue a CLI client command or SQL statement than go searching for docco!

This schema should get you started (you could add fields to these tables as appropriate for  your particular situation).

The Passage table:

CREATE TABLE passage
(
  passage_id INTEGER PRIMARY KEY, -- this will automatically auto increment
  passage_text TEXT NOT NULL, -- could check for a minimum length here!
  source_name TEXT NOT NULL, --
  ISBN TEXT, -- note, no NOT NULL - a URL doesn't have an ISBN for example
  author TEXT -- maybe author not known or it's an institutional website?
  passage_date -- if known/applicable.. other stuff...
);

If you have multiple authors, you could have an author table and then a passage_author table as an associative entity (or joing table - there are other names - see Wiki).

Question (each one refers to its own passage - that's what the FOREIGN KEYs are for!

CREATE TABLE question -- or quiz, but I'm keeping it singular!
(
  question_id INTEGER PRIMARY KEY,
  q_passage_id INTEGER NOT NULL,
  question_text TEXT,
  CONSTRAINT question_passage_fk FOREIGN KEY (q_passage_id)
    REFERENCES passage (passage_id) -- meaningful names are very helpful in error messages!
);

Replies to the questions (each option refers to its own question and passage):

CREATE TABLE option
(
  option_id INTEGER PRIMARY KEY,
  o_passage_id INTEGER NOT NULL,
  o_question_id INTEGER NOT NULL,
  option_number TINYINT, -- CHECK here for < 10?
  option_text TEXT NOT NULL,
  CONSTRAINT option_passage_fk FOREIGN KEY (o_passage_id)
    REFERENCES passage (passage_id),
  CONSTRAINT option_question_fk FOREIGN KEY (o_question_id)
    REFERENCES question (question_id),
);

All this is available here on a fiddle!


replicate-ignore-table setup and usage for excluding table from binlog backup

The replicate-ignore-table filter applies only the streaming over replication protocol (specifically in this case the SQL thread Slave), it doesn't affect if you apply log changes manually as you are doing. The detailed information about replication filtering can be complemented with the replication rule application if you want to understand fully the mechanisms for replication filtering.

A very easy step-by-step tutorial will allow you to setup a standard replica using no other tools than the ones included in the official distribution.

Although you don't want to replicate the table, I recommend you to create the table structure but using BLACKHOLE engine for avoiding some scenarios issues.


Here's what the current manual says in Rules Versus Triggers:

  If checks for valid values are required, and in the case of an invalid
  value an error message should be generated, it must be done by a
  trigger.

Consider implementing the check in a trigger, not a rule. The trigger is the perfect tool for the job in your case. On the other hand, it is a known fact that rules are touchy and much harder to master.


Why do cross-database statements break replication when using filters?

I don't know the rationale, but it is a problem that has bitten many users.

It is not just DDL statements, but also DML:

replicate_do_db = A  or  binlog_do_db = A  or ... (several syntaxes possible)
USE B;
INSERT INTO A ...;

Won't perform the INSERT.


MySQL dump restore - how to retain comments

OMG I know your problem !!!

Here is the problem: The mysql client filters out comments.

-c, --comments      Preserve comments. Send comments to the server. The
                    default is --skip-comments (discard comments), enable
                    with --comments.

Just load the mysqldump file like this:

mysql -u... -p... --comments < dumpfile.sql

Give it a Try !!!


Conditional Connection in SSIS based on Availability Group status

Let's assume that you have an availability group with only one secondary replica (you could easily expand this sample for more replicas)

1. Connections

You will need 3 connections:

AG listener (to run the query that will return which server is holding the primary an secondary)
Primary (to be parameterized with the primary server)
Secondary (to be parameterized with the secondary server)

2. Variables

Create 2 variables with data type String

ServernamePrimary
ServernameSecondary

3. Query

To map a query result set to a variable using Execute SQL Task, your query needs to return only one row. You will map each column to the variable that you wish. Knowing that, I've changed you query to the following:

WITH CTE_AGStatus as (
    select cs.replica_server_name, rs.role
    from sys.dm_hadr_availability_replica_states rs
    join sys.dm_hadr_availability_replica_cluster_states cs
        on rs.replica_id = cs.replica_id
    join sys.dm_hadr_name_id_map n
        on rs.group_id = n.ag_id
    where n.ag_name = 'DVAG001'
)

select  ServernamePrimary = MAX(case when role = 1 then replica_server_name end)
,       ServernameSecondary = MAX(case when role = 2 then replica_server_name end)
from CTE_AGStatus

(Feel free to change it as you wish, just remember to return only one row)

4. Mapping the result set to variables

On the execute SQL Task Editor, paste the query above, set the OLEDB connection 'AG listener' and change the result set to 'Single row'

Then go to the 'Result Set' tab and map the columns to the variables

5. Change the connection strings

Click on the 'Primary' connection manager and go to the 'Properties' tab, then click on 'Expressions', select the property 'ServerName' and add your variable on the Expression.

Repeat the process to the 'Secondary' connection manager

6. Check the process

To validate, you could add other steps and use breakpoints to check how your variables are running. The next steps are up to you. Now you have one connection pointing to each server of your AG.


Is it safe, and how can I delete mysql.sock before mysqld start?

The only way to avoid manual deletion is relocate mysql.sock
Add this to /etc/my.cnf
[mysqld]
socket=/tmp/mysql.sock

/tmp would be a good choice since /tmp gets wiped out on each server reboot
Make sure mysqld has chmod permission to write mysql.sock to /tmp
Give it a Try !!!
UPDATE 2014-03-21 11:37 EDT
If you cannot get /tmp/mysql.sock to be made, there is a hack you can try.
Run which mysqld_safe
This will tell you where the mysqld_safe is located. When I run it I get
sh-4.1# which mysqld_safe
/usr/bin/mysqld_safe

Open the file in vi, got lines 612-620. I see this
safe_mysql_unix_port=${mysql_unix_port:-${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}}
# Make sure that directory for $safe_mysql_unix_port exists
mysql_unix_port_dir=`dirname $safe_mysql_unix_port`
if [ ! -d $mysql_unix_port_dir ]
then
  mkdir $mysql_unix_port_dir
  chown $user $mysql_unix_port_dir
  chmod 755 $mysql_unix_port_dir
fi

You could add this line before it
rm -f /var/lib/mysql/mysql.sock

This might do it for you
CAVEAT : Remember, this is a hack. Should mysql not be able to start for you, I will disavow any knowledge of your actions going forward.


Is there a name for this "restricted one to many" relationship? Can it be enforced in the DB?

I don't know that there's an official or technical name for the relationship you're describing (I also don't know if you'll find many pet lovers who would like to be restricted to one dog, one cat, etc.), but here is how I would probably approach this:

CREATE TABLE dbo.People
(
  PersonID INT PRIMARY KEY
  --, ... name, etc.
);

CREATE TABLE dbo.PetTypes
(
  PetTypeID INT PRIMARY KEY
  --, ... species name, etc.
);

CREATE TABLE dbo.PeoplePets
(
  PersonID INT, -- FK to dbo.People
  PetTypeID INT, -- FK to dbo.PetTypes
  --, ... pet name etc.
  PRIMARY KEY (PersonID, PetTypeID)
);

Now whether this is simple enough, I'm not sure. You might have different attributes for different types of pets, and so you may need to extend this, but I think in general the concept is sound.


Look at this line
[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G

The sum of all your MyISAM indexes is 131.6G
You should lower key_buffer_size to 140G. This will give back 110G.
I have a little suggestion for you. You may want to consider using a dedicate keycache for each large table. See my Nov 16, 2012 post Cache all mysql table
Look at this line
[!!] InnoDB data size / buffer pool: 2.2G/2.0G

innodb_buffer_pool_size could be raised to 2560M (That's 2.5G)
Look at these lines
read_buffer_size=2G
sort_buffer_size = 2G

Why is so big? If you change both of these to 32M, you can increase max_connections. In fact, do the following right now:
SET @ThrityTwoMeg = 1024 * 1024 * 32
SET GLOBAL read_buffer_size = @ThrityTwoMeg;
SET GLOBAL sort_buffer_size = @ThrityTwoMeg;
SET GLOBAL join_buffer_size = @ThrityTwoMeg;

Then, go rerun mysqltuner.pl and you will see a dramatic difference from 4.1G per thread
BTW go change join_buffer_size to 32M in my.cnf
Give it a Try !!!
UPDATE 2013-04-11 16:53 EDT
I just thought of another change you can make to increase SELECT speed against MyISAM tables. For every MyISAM you need to read a lot from, do this to the table:
ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

Believe it or not, this will bloat the table 80-100% in size, but SELECTs will go 20-25% faster. I have written about this many times. Here is just few of my posts:

Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?


Speaking from experience which is humble but I think worth sharing, the major bottleneck with SQL databases (Sybase and SQL server here) is storage.

But I think it's only fair that someone first benchmarks their setup before making any wrong assumptions. In my case, CPU usage has never risen high enough to justify upgrading the CPU any time soon. Instead, I have upgraded from single drive to RAID 1 and then to RAID 10 + a bump from 8GB to 16GB of RAM.

All these RAID upgrades helped to reduce previous wait times by a factor of 2 to 6. I suspect upgrading to SSDs would be even better. If you think about it, it can all be reduced to (theoretical) bandwidth. Your [(RAM Speed + RAM Size + Memory controller) link to CPU] combo has a bandwidth limit which would be the most important factor in read operations when your data should always be cached, your particular (RAID) storage has a bandwidth limit (affects reads when cache missed and writes when flushing or with many clients writing lots of data combined).

Normalize all those limits as much as possible (bring them closer such that you don't have wasted resources) and raise them as much as possible (upgrade where needed and only if needed, don't let resources become wasted if the system won't be able to use them because some other bottleneck is in the way). In the end, your worst bottleneck will be the least performing (with least bandwidth) server subsystem in your particular configuration.

I might also add that, in the process of upgrading, I have created separate RAID configurations for the database files and the database log files. The reason was that database log files tend to be write intensive. A log file is used to recover a database from a crash and it's always written to immediately as a transaction is committed before any data is written to the database file.

A log file is also used by some database replication servers but most replication is not done instantly but frequently so the read performance impact is minimal here. At least I think so. I have done minimal benchmarking while making this upgrade so again, I recommend anyone first benchmarks their different configurations and first upgrades their storage, then RAM and network link, before thinking about upgrading their CPUs.

After more extensive upgrades on more than 5 servers, I came back to share my experience. I definitely still advocate for first upgrading storage, then RAM and then CPU. Reason is the disparity of bandwidths in the system between storage, RAM and CPU, in order of lowest to highest. So I upgraded a bunch of servers from RAID10 and dual RAID1s to SSDs.

The way I did it because of cost concerns (I have 20 more servers to upgrade) is to move the database data and objects files to an SSD (yes, just one SSD in RAID0) and move the transaction log plus tempdb to a 4xHDD RAID10 configuration. I tested with the tempdb on the SSD as well with great results (in fact very nice results with more than 15 times faster queries sometimes, yielding some reports taking seconds instead of minutes in the past) but later moved the tempdb to the disk RAID10 because of intensive write-wear concerns for the SSD.

So now basically I have observed 10-15 times faster response times per some of the longest queries. The SSD is great for reading data into RAM fast because SQL Server does not bring data into RAM until requested to and of course data first needs to be loaded into RAM to be processed by the CPU (later, in L1,L2,L3 cache), so SSDs help reduce that initial wait time by a huge factor. And SSDs also help reduce swapping times... clearing out RAM and loading new data, especially if your database is larger than can fit into RAM.

All in all, we're very pleased and been running like so for several months in a sort of slow migration process to allow the servers to run so I can collect wear-level information before I switch all my servers to this configuration. And this is just SQL Server Express! :D - Just make sure your SSDs can provide constant IOPS because that's another thing that makes a huge difference (just google it). That's why I chose Intel DC (DataCenter) series SSDs.


I cannot figure out how to get the Windows Owner associated to that PID.

You can get the owner using PowerShell:

# Identify the name of the remote computer you want to query
$computerName = 'SomeRemoteComputerName'
# Invoke the command on the remote system to get the information that is necessary
Invoke-Command -ComputerName $computerName -ScriptBlock {
    # Build a hashtable that associates process ids with owners
    $processOwners = @{}
    Get-WmiObject -Class Win32_Process | ForEach-Object {
        $processOwner = $_.GetOwner()
        # Combine the domain and user information together to get the process owner
        $processOwners[[int]$_.ProcessId] = $processOwner.Domain + '\' + $processOwner.User
    }
    # Now get all processes and add the owner information to them
    Get-Process | ForEach-Object {
        $processOwner = $null
        # If we have process owner information for the process, look up the owner in the table
        if ($processOwners.ContainsKey($_.Id)) {
            $processOwner = $processOwners[$_.Id]
        }
        # Add the owner information to the current process object
        Add-Member -InputObject $_ -MemberType NoteProperty -Name Owner -Value $processOwner
        # Return the current process object from the script block
        $_
    } | Select-Object Name,Owner,Description
}

script source


Need database of categories for products and services

Classification is a really tricky thing -- a company that specializes in cell phones might have many categories of cell phones, while ones that don't might stop at a broader category.  Even within a given company, they might have multiple ways of looking at things, and so have multiple classification schemes / taxonomies / thesauri / ontologies / knowledge organization systems in use.

Just like any database, you have to know what sort of questions you're going to ask of your classification scheme, and from there, you can design what's going to fit it best.

A few registries / repositories / lists that I know about :

Taxonomy Warehouse
TaxoBank
JISC Directory of Metadata Vocabularies
TemaTres Vocabulary Server
Controlled Vocabulary Examples

You might also check to see if whatever organizations there are for your industry have any standard classification scheme / thesauri / ontologies / whatever.

Update : I don't know if the Global Product Classification might help or not.


I downloaded the latest sp_BlitzIndex and in the comments in the SP i found this:

  The @OutputDatabaseName parameters are not functional yet. To check the    status of this enhancement request, visit:
  https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/221

So you are not able to use that parameter.


How to get return value from pl sql procedure and assign it to variable?

You can create it like this:

CREATE OR REPLACE PROCEDURE procedurename(param1 NUMBER, param2 varchar(20), returnvalue OUT NUMBER);
IS
BEGIN
    ... your code
END;

And then use it like this:

returnvalue NUMBER;
procedurename(0, 'xxx', returnvalue);
dbms_output.putline(returnvalue);

You can look at CREATE PROCEDURE  documentation at Oracle website.


NVARCHAR column as PRIMARY KEY or as UNIQUE column

Yes, absolutely there are negative consequences for using a string instead of a numeric type for a Primary Key, and even more so if that PK is Clustered (which it indeed is in your case). However, the degree to which you see the effect(s) of using a string field is a function of a) how many rows are in this table, and b) how many rows in other tables are Foreign Keyed to this PK. If you only have 10k rows in this table and 100k rows in a few other tables that FK to this table via that field, then maybe it won't be so noticeable. But those effects certainly become more noticeable as row counts increase.

You need to consider that the fields in a Clustered Index are carried over to Non-Clustered Indexes. So you aren't just looking at up to 40 bytes per row, but (40 * some_number) bytes. And in any FK tables you have those same 40 bytes in the row plus more often than not there will be a Non-Clustered index on that field as it is being used in JOINs, so now it is really doubled in any tables that FK to this one. If one is inclined to think that 40 bytes * 1 million rows * 10 copies of it is nothing to be concerned about, please see my article Disk Is Cheap! ORLY? which details all (or at least most) of the areas impacted by this decision.

The other thing to consider is that filtering and sorting on strings, especially when not using a binary Collation (I assume you are using the database default which is typically case-insensitive) is far less efficient (i.e. takes longer) than when using INT / BIGINT. This impacts all queries that filter / join / sort on this field.

Hence, using something like CHAR(5) would probably be OK for a Clustered PK, but mostly if it was also defined with COLLATE Latin1_General_100_BIN2 (or something like that).

And can the value of [CODE] ever change? If yes then that is even more reason to not use it as a PK (even if you do set the FKs to ON UPDATE CASCADE). If it can't or won't ever change that is fine, but still there is more than enough reason already to not use it as a Clustered PK.

Of course, the question might be incorrectly phrased as it appears that you currently already have this field in your PK.

Regardless, your best option, by far, is to use [ID_CODE] as the Clustered PK, use that field in related tables as the FK, and keep [CODE] as a UNIQUE INDEX (which means it is an "alternate key").

Update
A little more info based on this question in a comment on this answer:

  Is [ID_CODE], as PRIMARY KEY, the best option if I use [CODE] column to look up the table?

This all depends on a great many factors, some of which I have already mentioned but will restate:

A Primary Key is how the individual row is identified, whether or not it is referenced by any Foreign Keys. How your system internally identifies the row is related to, but not necessarily the same as, how your users identify themselves / that row. Any NOT NULL column with unique data could work, but there are practicality issues to consider, especially if the PK is, in fact, referenced by any FKs. For example GUIDs are unique and some people really like using them for various reasons, but they are quite bad for Clustered Indexes (NEWSEQUENTIALID is better, but not perfect). On the other hand, GUIDs are just fine as alternate keys and used by the app to look up the row, but the JOINs are still done using an INT (or similar) PK.

So far you haven't told us how the [CODE] field fits into the system from all angles, outside of now mentioning that this is how you look up rows, but is that for all queries or just some? Hence:

Regarding the [CODE] value:

How is it generated?
Is it incremental or psuedo-random?
Is it uniform length or varying length?
What characters are used?
If using alphabetical characters: is it case-sensitive or insensitive?
Can it ever change after being inserted?

Regarding this table:

Do any other tables FK to this table? Or are these fields ([CODE] or [ID_CODE]) used in other tables, even if not explicitly Foreign Keyed?
If [CODE] is the only field used to get individual rows, then what purpose does the [ID_CODE] field serve? If it isn't used, why have it in the first place (which might depend on the answer to "Can the [CODE] field ever change?")?
How many rows in this table?
If other tables to reference this table, how many and how many rows in each of them?
What are the indexes for this table?

This decision can't be made purely on the question of "NVARCHAR yes or no?". I again will say that generally speaking I do not find it to be a good idea, but there are certainly times when it is fine. Given so few fields in this table it is not likely that there are any more, or at least not many, indexes. So you might be fine either way to have [CODE] as the Clustered Index. And if no other tables reference this table then you might also be fine making it the PK. But, if other tables do reference this table then I would opt for the [ID_CODE] field as the PK, even if Non-Clustered.


MySql Vs. Postgresql for a blog application

MySQL's home territory is in CMS.  It will be good enough.  I like PostgreSQL as much as the next guy but a blog will work on either, and MyISAM tables might actually be really nice there.

As for where PostgreSQL does better than MySQL, here is my take. There are two big areas:

Advanced data modelling and
Multi-application databases

The first means that PostgreSQL is far more mature at doing complex data modelling than MySQL is.  It has a very mature extensible type system, a wide range of procedural languages, and a great deal of flexibility in how these languages can be plugged into existing queries.

If that wasn't enough, the fact is that you can essentially build your data model in PostgreSQL based not only on what information you are storing but what information is commonly derived from what you are storing.  This makes things like not-first-normal-form actually sane to use where they are needed.  Add collections and multiple inheritance in table structure and you have a very sophisticated data modelling platform.  For examples, see my recent blog entries.

On to the other side.  MySQL developers tend to see the application as the bottom tier of the application stack, while PostgreSQL developers tend to see the application as the center of the data environment.  Besides the content management system market, MySQL's other major market is in applications where data is not expected to be exposed to more than one writing application at a time.  This leads to a significant difference in handling data validation, etc.

In PostgreSQL validation is always equally strict.  If the app expects special error treatment it had better call functions or casts to handle this explicitly.  For example, if I want to store up to a maximum of 144 characters in a text field, I can:  insert into mytable values (?::varchar(144)); and absent such, PostgreSQL will throw an error if it isn't crystal clear what the application wants, and whether it will fit into the data schema as defined.

MySQL however places the application in charge of defining the data validation rules.  The sql_mode setting (which includes such things as whether '0000-00-00' is a valid date or whether '2008-02-30' can be stored in a date field) is something any application can set without restriction, and the DBA has no way of restricting this.  So while PostgreSQL allows the relational and object-relational interface to serve as a public API, it is essentially intended largely to be a private API for applications in MySQL.  This is a huge difference and not readily understood by many people trying to make the choice.  This leads to major differences in application design.

MySQL is a data storage and reporting solution for your application.  PostgreSQL is a data centralization, modelling, and reporting solution for your organization.  The two are remarkably different.


How to build the database for subdomain multisite?

The problem lies on the border of the DBA and the software engineering.

There are multiple solutions, all depending on, exactly how close database structures are you wanting.*

As the site databases (thus, also the frontend engine using your DB) are more closer and closer to eachother, so should you use the more and more closer options also for the DB.

Likely (but not sure), that you will have at least a little part of your database, which will be the same, for example the user data.

Your options depend also on the applied database. For example, mysql has no schemas, but knows the cross-database queries. PostgreSQL can make cross-db queries only by extensions, but you can configure schemas for it.

The options, in the decreasing order of "close-ness":

Having a multi-tenant DB. It essentially means, you will have the same DB for all the sites. On this way, your database structure will be more complex, with foreign keys directed by each per-db record to the corresponding db data. It makes also your queries more complex. Note, queries using many "WHERE"-conditions become quickly sub-optimal.
Having a multi-schema solution: using a single cross-site schema, added by a per-site schema for all the sites. This can't be done with MySQL, but it would work well with PostgrSQL.
Having a single cross-site DB and a per-site DB for all the sites. This either requires to have cross-db queries, which may be hard to synthetize with foreign keys. However, you may have different database versions in the per-site dbs.
You may have also entirely different databases. In this case, you will likely need to have some replication/synchronization, either by triggers or by scripts.

Changing your decision in a late phase of the development, or during the maintenance, is a major refactoring work, so decide wisely as early as possible.

Note, if you want to use different software versions simultanously, you will likely need to have same database versioning and (1) falls out.

*A remark: the Stack Exchange has the same situation: they have a multi-tenant website, all with nearly the same engine and probably with the same database format. Probably they decided behind (3).


Why is context_info sometimes blank in sys.dm_exec_sessions?

CONTEXT_INFO needs to be set by the application and Dynamics AX sets the value for the connections included in the session. If CONTEXT_INFO is blank it really is because the application didn't set the property.

When AX uses some connections for a specific session one can see the setting of the CONTEXT_INFO in ETW traces like this:

I did several tests and I couldn't create a situation where the CONTEXT_INFO is not set correctly, even when constructing my own ODBCconnection or using the Connection() class from within AX.

Since you have a situation where for some sessions CONTEXT_INFO is empty it really looks like you have an Object Server somewhere that isn't configured to set the context information for it's sessions.

If you look closely at your results, you will see that both empty rows have the same host process id, and you don't have any rows where that process id does return a context_info.

According to the documentation on sys.dm_exec_sessions the host_process_id is the process id of the client application.

  Process ID of the client program that initiated the session. The value
  is NULL for internal sessions. Is nullable.

What I think is happening is that you have either multiple AX environments on this server, or you have multiple AOS instances where one of them isn't configured according to the article you linked.

For example I have a set-up with a couple of instances on one machine, connecting to a SQL Server and when querying the SQL Server sys.dm_exec_sessions the CONTEXT_INFO is blank for the instances where I didn't configure it, and never blank for the instance I did configure correctly.

These processes:

Show up in the sessions on SQL Server:

However, since I only configured one of the instances if I look at the context info query I get this result (with a bunch of blanks)

My conclusion is that you likely have an instance somewhere where this registry configuration is not properly activated.


re 1)

With standard SQL the only way to do that is:

select min(cnt)
from (
  select x, count(*) as cnt
  from y
  group by x
) t

re 2)

yes you can join to a query, but you need to give the derived table an alias

Select x
From y
  natural join (select z AS y
                from foo) as t;

That assumes that the table y also has a column y - otherwise there wouldn't be two identical columns that the natural join could use.

But in general you should avoid the natural join. Use an explicit join instead:

select x
from y
  join (select z AS y
       from foo
  ) as t on t.y = y.id;

re 3)

No, from * is never allowed. But I have no clue what you intend to do with that. The sub-select used with > (or < or =) has to return exactly one row and exactly one column so you would need something like:

Select x
From y
Group by x
Having avg(x) > (select count(*) -- no idea what you would want to do here
                 from y
                 where x > 1);

If the subselect returns more then one row you would need to use ANY

Select x
From y
Group by x
Having avg(x) > ANY (select x -- still only ONE column allowed
                     from y
                     where x > 1);


Execute Stored Procedure with SET LOCK_TIMEOUT

LOCK_TIMEOUT is not only a session-level setting, it also needs to be set in a separate batch. But, it cannot be set via a variable. So, this can be accomplished by using Dynamic SQL in the "master" Stored Procedure. This will allow for setting LOCK_TIMEOUT and then executing whatever other Stored Procedure that should run within this particular setting. It has to be a single execution of Dynamic SQL since the setting will revert back to the value of the top-most / outer-most process. For example:

Run this in one query tab in SSMS:

GO
CREATE PROCEDURE ##ShouldTimeout
AS
INSERT INTO ##LockedTable ([ID]) VALUES (1);
GO

GO
CREATE PROCEDURE ##TimeoutTest
(
    @SecondsUntilTimeout INT = 2
)
AS
SET NOCOUNT ON;

SET @SecondsUntilTimeout = ISNULL(@SecondsUntilTimeout, 2); -- enforce default

DECLARE @SQL NVARCHAR(MAX) = N'SET LOCK_TIMEOUT ';
SET @SQL += CONVERT(NVARCHAR(MAX), @SecondsUntilTimeout * 1000) + N';
';

SET @SQL += N'EXEC ##ShouldTimeout;'; -- use CASE / IF to decide what to exec

RAISERROR(@SQL, 10, 1) WITH NOWAIT; -- print Dynamic SQL in "Messages" tab

EXEC (@SQL);
GO

Run the following in another query tab in SSMS:

BEGIN TRAN;
CREATE TABLE ##LockedTable (ID INT);

-- ROLLBACK

Now go back to the first query tab (where you created the two temporary Stored Procedures) and run the following two tests:

EXEC ##TimeoutTest;
-- this will timeout after 2 seconds (the default)

EXEC ##TimeoutTest 5;
-- this will timeout after 5 seconds

Now go back to the second query tab and execute the ROLLBACK ;-).


What is the difference between 'two way transactional replication' and 'peer to peer replication'?

generally transactional has a publisher and a subscriber, with some updating from the subscriber, peer to peer replication shares the same data in more of a node environment (often over long distances) and would be more suitable for load balancing.

I'd go straight to technet for the overview    http://technet.microsoft.com/en-us/library/ms151196.aspx


Error 909 restoring a database on Standard Edition

According to the error message, your database contains Enterprise Edition features in the table Country. First of all, check the following on the original (old) server:

SELECT SERVERPROPERTY('Edition');

That should tell you if you're running on Standard, Enterprise or Developer Edition. Developer Edition supports all of the Enterprise features (with different licensing terms). Could that be the issue?

Check if compression, partitioning or vardecimal is enabled on any object:

SELECT s.[name]+'.'+o.[name] AS [object], i.[type_desc] COLLATE database_default+ISNULL(' '+i.[name], '') AS index_name,
       (CASE WHEN COUNT(DISTINCT p.partition_number)>1 THEN 'Is partitioned' ELSE '' END) AS [partitioned?],
       ISNULL(MIN(NULLIF(p.data_compression_desc, 'NONE'))+' compression', '') AS [compressed?],
       (CASE WHEN ISNULL(OBJECTPROPERTY(p.[object_id], 'TableHasVarDecimalStorageFormat'), 0)=0 THEN '' ELSE 'vardecimal' END) AS [vardecimal?]
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.[object_id]=i.[object_id] AND p.index_id=i.index_id
INNER JOIN sys.objects AS o ON i.[object_id]=o.[object_id]
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
GROUP BY p.[object_id], s.[name], o.[name], i.index_id, i.[type_desc], i.[name]
ORDER BY s.[name], o.[name], i.index_id

Finally, see if there are any partition functions or partition schemes that you haven't cleaned out:

SELECT [name] FROM sys.partition_functions;
SELECT [name] FROM sys.partition_schemes;

You may also consult the following link (originally provided by Shanky in a comment):

How to identify compressed tables before restoring/migrating a database to any edition other than Enterprise Edition of SQL Server 2008


A few days ago I updated the server to SQL Server 2016 SP2 and the problem disappeared. Now when the querystore hits 90% it is cleaned up till it reaches 80%.

I don't know if SP2 was the solution or was it the restart of the SQL Service due to the update.


Where is the source code of the Postgres txid_current function?

include/catalog/pg_proc.dat maps txid_current to src/backend/utils/adt/xid8funcs.c:pg_current_xact_id which leads the majority of the implementation in GetTopFullTransactionId and deeper it goes, but easy to follow.


How do I have RMAN treat my restored data files like they are originals?


Recovery with Postgresql logical replication

Logical replication doesn't have anything to offer you here.  PostgreSQL doesn't offer "AS OF" queries, but even if it did you could just do them in the master, rather than in the replica.
PITR is the thing you want.  It is its own thing, it is not particularly tied to physical replication.  About the only thing they have in common is that they both involve WAL files.  I think most modern implementations of physical replication would use replication slots, not a WAL archive, which means the WAL needed to do PITR is likely not even available unless you have gone out of your way to design specifically for PITR as well as for replication.
The logical replica records all changes into its own WAL stream, including changes driven by logical replication.  So if you keep a WAL archive and base backups of the replica, you could restore a copy of the replica and put it through PITR if you wanted to (but again, why not just do PITR on a copy of the master?  What does doing it on a copy of the replica instead earn you?)
There are way to do in-table or out-of-table logging of row changes, and these can be used to restore tables to a prior state.  But they don't have anything to do with the logical replication feature.


Executing the query "UPDATE STATISTICS [dbo].[Contact] WITH FULLSCAN ..." failed

there could be many reasons for this error

it can be a corrupted index or data corruption in your database

check this post https://stackoverflow.com/questions/1175244/sql-server-error-on-update-command-a-severe-error-occurred-on-the-current-com

and this post

http://sqlanddotnetdevelopment.blogspot.com/2011/03/severe-error-occurred-on-current.html

Microsoft has a FIX for this problem, they say if you get this error while "You update the statistics of the table." then this fix is for you... they say This issue occurs because the database engine is trying to load dangling statistics, check this link its a Cumulative update

http://support.microsoft.com/kb/2498796


SELECTing multiple columns through a subquery

SELECT DISTINCT petid, userid, lastComDate, lastPosterId
FROM
    pet
    LEFT JOIN comments ON pet.id = comments.petid
    LEFT JOIN (
        SELECT MAX(comDate), userid, petid FROM comments GROUP BY userid
    ) a ON a.petid = pet.id
WHERE
    userid='ABC'
    AND deviceID!='ABC'
    AND comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH)
;

You can also pull your subquery out into a temp table if performance becomes impacted somewhere down the road.


The maintenance plan is usually executed with another user account.  Check the properties of SQL Agent.  If the Connection tab specifies Use Windows Authentication, it runs under the same user as the SQL Server Agent service.  You can see that in the Local Services control panel, under the Log On tab.

Once you figure out the user account, try to run the script as that user.


Upgrade lock from mongo 2.4 to 2.6 wont go away

The upgrade lock record is not removed post upgrade. A "state" of 0 means the lock is not being held. You can ignore or remove manually.

The lock definitions for MongoDB 2.6 are available here and are:

  0: Unlocked | 1: Locks in contention | 2: Lock held


How to change the datatype of column without deleting it indexes;

The data type of columns included in an index cannot be changed unless
the column is a varchar, nvarchar, or varbinary data type, and the new
size is equal to or larger than the old size.

With this info from the knowledge base a suggestion could be to alter column to a nvarchar(4000) or something like that.
Remember that you can't index a varchar(max) or nvarchar(max) field.


Column not like any value in column in other table - function

While your solution should basically do the job:

SELECT type FROM all_types WHERE type NOT LIKE ALL(SELECT type||'%' FROM completed_types)

Note some corner-case problems:

If completed_types.type isn't defined NOT NULL and you introduce even a single NULL, your query will not return any rows. So make sure it's NOT NULL.
If all_types.type allows NULL, those rows are never returned. So make sure that one is NOT NULL as well or adapt the query like below.
If one of the special characters %\_ is allowed, you may want to escape their special meaning when transforming the string to a LIKE pattern. See:

Escape function for regular expression or LIKE patterns

I suggest (using the simple function f_like_escape() outlined in the link above):

SELECT type
FROM   all_types a
WHERE  NOT EXISTS (
   SELECT FROM completed_types c
   WHERE  a.type LIKE f_like_escape(c.type) || %
   );

Also returns rows with all_types.type IS NULL - as opposed to your original.

Or save the readily escaped pattern as additional column in completed_types to avoid the escaping over and over.

Or, if completed_types.type always holds the leading 3 characters like your data sample suggests:

SELECT type
FROM   all_types a
WHERE  NOT EXISTS (
   SELECT FROM completed_types c
   WHERE  a.type = left(c.type, 3)
   );


I've always done this by making a step's next action on failure be a specific step that sends an e-mail about that step only, then that step's on complete action is to continue to {failure step + 1}. Yes it requires some manual interaction with the jobs up front, but I can't think of any way to change the defaults without some level of manual tinkering.


How much disk space was made available to Postgres by VACUUM that is not available to the OS?

The two extensions pgstattuple and pg_freespacemap might be helpful.

pg_freespacemap is faster, and gives a view of free-space which matches how PostgreSQL itself hunts for freespace when it is doing a tuple insertion.

SELECT sum(avail) FROM pg_freespace('foo');

But it doesn't give you view of the average tuple length, which would be needed to convert space into tuples (on the assumption new tuples will be the same length as existing ones).  pgstattuples gives you info to compute the current average length.

Neither one of them accounts for the possibility that your new rows will be an awkward size which does not fit into the holes currently available.

Also, neither works very well for indexes.  New entries have to go into an index where they belong, even if some other page has plenty of free space in it. So the reusability of index space depends on how the new indexed values relate to the deleted away values.


The command you show generates a text backup file.  You can use "head" or "less" on it to verify that it looks like a PostgreSQL text backup file, same as mysql.  I'd also run "tail", just to make sure it didn't get truncated.  It should end with a sign-off like:

--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database cluster dump complete
--

Of course to be really sure, you would just set up a dummy database server, and restore it.


BULK INSERT runs fine in window, but fails inside sql job

When you run that query interactively then SQL Server impersonates you when accessing the share. Normally this would fall into double-hop Kerberos Constrained Delegation and fail unless set up appropriately. It works because probably one of the hops is short-circuited (eg. you log on from a session on Server A or Server B).

When the query is run as a job the query impersonates the SQL Server agent service, and it all depends on how the SQL Server Agent is configured to run and connect to SQL Server. Different answers apply depending on whether the connection is made using a SQL Server login (eg. sa), a domain account, a virtual account, a local built-in account (eg. LocalService) and so on and so forth.

We here cannot guess what the problem is. To document it, please provide the details of the accounts involved. Are Server A/B in a domain? In the same domain? Is SQL Server running as a domain account, as a virtual account, as a local account, as local service, or what? Is SQL Server Agent running as who?

When you granted access to Everyone, did you grant access to the share or did you grant access to the folder?

Ideally you should set up an audit policy and then simply consult the event log, which will tell you exactly which account attempted the access. See Auditing File Access on File Servers

Finally, you may be interested in reading up about SQL Server Agent proxies.


I would do a point in time recovery to a different location, restoring to the desired time, and pg_dump the problem database.  I would drop the one database on the normal location, create it again, and load the pg_dump output.

Make sure you make and save a file-system level copy of the cluster's data directory tree before you start anything like this.


Multi-server administration - force poll on target fails, server needs to be registered locally without FQDN

I know it's been a while, but I have the same issue trying to manage multiple domains. (Avoid using the same server name in more than one domain.)  The Multi-server feature seems to be very limited, but it could be so much better with a little effort.  I figure it will be removed from the product one of these years, fixed, and then added back as a great "new" feature in the ENT edition.

I think the limitation is in the extended procs such as xp_msx_enlist code.  They determine the name of the local instance.  If it was T-SQL code, it might have been easier to fix.  As far as I can tell, the only place the target name is stored is in msdb.dbo.systargetservers table on the master server.  If the targets could be tricked into requesting a sync for the FSDN, then the table could have be updated (hacked) directly to use the FQDN.

If it was open source, it would have been fixed years ago.  It would have had more features by now too.


After bulk load, how to ensure an updated visibility map?

You can set old_snapshot_threshold and then wait out that amount of time before retrying the VACUUM.

(Note that this is a last resort.  First resort would be just to identify the long running connections, and making them go away if they are not really needed, or downgrading their isolation level).


You also need to have execute otherwise you cannot "CD" into the directory nor MySQL will be able to.

Run this to fix directories recursively:

for dir in $(find /var/lib/mysql -type d); do chmod u+x $dir; done


how to extract difference between timestamps

You can use Windows Functions which provides calculation across a set of table rows that are somehow related to the current row. The list of functions are in General-Purpose Window Functions section of the documentation and The lead() and lag() functions are answer of your question. Your query will should like this;

SELECT *,
  "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
  lead("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") - "RecognitionTimestamp" AS delta_next
FROM "table"
WHERE
  "RecognitionTimestamp" >= '2018-10-03 00:00:00' AND
  "RecognitionTimestamp" <= '2018-10-04 9:00:00'


Reset my root password, now I can't see my main database

MySQL --initialize installs a clean data directory. It seems like that's what you got.

There may be a previous idb file there but its not connected to the database.


Does SQL Server have a reference partitioning equivalent?

Not exactly.

In SQL Server, you create a partition function and a partition scheme, which are objects separate from the tables themselves, which define the partitioning characteristics. Then, tables are created referencing the partition scheme.

So, yes, there is a central definition of the partitioning characteristics, but the reference is made to the partition scheme (which could be anything), not to any particular table or relationship.


Optimize PostgreSQL server setting for extremely wide tables

How many of those columns to you use for grouping? If it's relatively few, then I would recommend restructuring the data to be in a long format, where each grouping (category) column is maintained, and is each grouped-by (metric) column is instead jammed into two columns variable and value, similar to how R's reshape2::melt function works. For instance, a table:

id|cat1|cat2|metric1|metric2|

Would become:

id|cat1|cat2|variable|value
id|cat1|cat2|metric1|value of metric 1 column
...
id|cat1|cat2|metric2|value of metric 2 column

The table would become K times longer, with K being the number of metrics you'd like to melt. This can actually improve query performance if you add indexes on your category columns.

If that doesn't speed up performance, then I'd recommend using a different tool than Postgres, such as Apache Spark.


SQL Server Resource Pool and Workload Groups

Yes, those two scenarios will result in the same resource allocation - in theory.

In scenario 1, if there is CPU pressure, the two workloads running in the "20% resource pool" will share that CPU "equally."  How equally they are able to share the 20% can depend on a lot of things that are workload specific, given how SQL Server's scheduling algorithms work.

In scenario 2, if there is CPU pressure, the two workloads running in the "10% resource pool"s will each get a minimum of 10% of CPU time.

You have a better chance of a "fair" split of CPU in scenario 2, but scenario 1 could work depending on your specific workload.


Restore Database on Active Passive Failover Cluster

Welcome to StackExchange.

The SQL database will only need to be installed on 1 node, as the nodes share the storage already since SQL Server is 'cluster aware'.  However if you use something that is not 'cluster aware' such as SSIS, then packages will have to be installed on both nodes.

The reason you need to do 2 installations is because the nodes do not share installation files found on the local disk of the machine, but anything you do on shared storage and within SQL Server will be retained when failing over.  It's 1 database engine and 2 nodes.

Off topic, but a thought might be to have scripts that copy backup files and such so I make my 'backups' drive a cluster resource, and point all of my scripts to the virtual cluster address so even in case of a failover, they work.


Add Check Constraint On Varchar Column to enforce no duplicates

Ordinarily, you'd just create a unique constraint on that column. But you can't put a unique constraint on a varchar(max) column. That could be close to 2GB per index entry.

In SQL Server, the maximum size of an index key is 900 bytes. I believe SQL Server enforces unique constraints by using a unique index, so I'd expect that limitation to apply to your problem, too.

In any case, I think this is an XY problem.


Issue with SQL Server Aliases on a multi-instance installation

If you want aliases to work from the client then you have to create them on the client. Install the SQL Server client (from memory the feature is called Client Tools) and use Configuration Manager to create the aliases (32-bit or 64-bit to match the client application).


You can use the CASE statement and return a value every time the condition is true. In the end you just have to sum those values.

SELECT
  DEALER_ID,
  SUM(CASE SUBJECT_TYPE WHEN 'Marketing' THEN 1 ELSE 0 END) AS COUNT_MARKETING,
  SUM(CASE SUBJECT_TYPE WHEN 'Random' THEN 1 ELSE 0 END) AS COUNT_RANDOM,
  SUM(CASE SUBJECT_TYPE WHEN 'Alert' THEN 1 ELSE 0 END) AS COUNT_ALERT,
  COUNT(*) AS COUNT_CLICKED
FROM t
WHERE CLICKSKEY IS NOT NULL
GROUP BY DEALER_ID;