Test to see when table is candidate for compression (row or page)

Does anyone know a tool like this page https://columnscore.com/ where one can determine if a table is a good candidate for row or page compression.

Tool recommendations are off-topic here, primarily because there can be any number of answers, and they tend to go out-of-date quickly. That said, SQL Server does provide a built-in procedure to estimate space savings from compression, sp_estimate_data_compression_savings. It does not attempt to predict whether compression will be a good option for the workload as a whole, just whether space savings can be expected or not. It is essentially a wrapper around TABLESAMPLE, creating a new object and extrapolating full size from there.

Will compression make more 8KB fit in the buffer cache?

If compression is effective, then yes. Pages remain compressed in the buffer pool.

When does data become uncompressed i.e. in a select?

Data is decompressed when a SQL Server component above the level of the storage engine needs to work with the data. For example, an Index Scan will present decompressed data to a Filter operator in an execution plan.

The stack trace below shows one example of decompression before presenting data to the query processor (storage engine code highlighted):

You can read more about this in Compression Strategies by Sunil Agarwal (part of the SQL Server engineering team at the time).

will backups become faster?

Many people have reported that they do. The intuition is that compressed data on persistent storage requires fewer I/Os to read for backup. If your instance is typically CPU-bound, you may see slower backups as the extra processor work needed for decompression outweighs the I/O speedup.

Is the reason that compression ads cpu overhead because e.g. inserts need to be compressed before the insert?

Certainly compression adds CPU overhead, but this is typically manageable because the data compression algorithms were chosen for efficiency rather than ultimate size. Note that data compression and backup compression are very different. Backup compression is based on a variant of gzip compression, and is intended to achieve very good size reduction at the expense of more CPU usage.
Every insert and update will incur at least some data compression overhead. This is more noticeable with PAGE compression. SQL Server may also attempt to recompress an entire page after a certain number of modifications have been made to that page.

Is there any good reason to compress tables without investigating how the tables is being used (i.e. static vs. volatile).

This is subjective, and depends on your local priorities. In general, one might choose to compress without detailed testing where it is clear substantial (and necessary) space savings will be made, and the system has plenty of spare processor time available.

A colleague has started to compress many tables (page). I don't know the analysis behind this decision.

You should discuss the rationale with your colleague.


How does SQL Server's optimizer estimate the number of rows in a joined table?

I think my understanding of the second estimate is incorrect and the differing numbers seems to indicate that. What am I missing?

Using the SQL Server 2012 cardinality estimator, the selectivity of the join drives the estimated number of rows on the inner side of the nested loops join, and not the other way around.

The 11.4867 number is derived (for display in showplan) by dividing the computed estimated cardinality of the join output (30.0919) by the number of iterations (2.61972). The result, using single-precision floating-point arithmetic, is 11.4867.

It really is as simple as that. Note that the (logical) join selectivity is independent of the choice of physical join operator. It remains the same whether the join is ultimately performed using a Nested Loops, Hash, or Merge Join physical operator.

In SQL Server 2012 and earlier, the join selectivity (as a whole) is estimated using the SalesOrderID histograms from each table (computed for each histogram step, after step boundary alignment using linear interpolation as necessary). The SalesOrderID histogram associated with the SalesOrderHeader table is also adjusted for the scaling effect of the independent CustomerID filter.

That is not to say there is anything fundamentally 'wrong' with the alternate calculation proposed in the question; it just makes a different set of assumptions. There will always be different ways to compute or combine estimates for a given sequence of logical operations. There is no general guarantee that different statistical methods applied to the same data will produce the same answers, or that one method would always be superior to the other. Inconsistencies resulting from the application of different statistical methods can even appear within a single final execution plan, though they are rarely noticed.

As a side-note, the SQL Server 2014 cardinality estimator takes a different approach to combining the independent-filter-adjusted histogram information ("coarse alignment"), which results in a different final estimate of 10.1006 rows for this query:

Plan for computation:

  CSelCalcExpressionComparedToExpression
  (QCOL: [s].SalesOrderID x_cmpEq QCOL: [d].SalesOrderID)

Loaded histogram for column QCOL: [s].SalesOrderID from stats with id 1
Loaded histogram for column QCOL: [d].SalesOrderID from stats with id 1

Stats collection generated:

  CStCollJoin(ID=4, **CARD=10.1006** x_jtInner)
      CStCollFilter(ID=3, CARD=2.61972)
          CStCollBaseTable(ID=1, CARD=31465 TBL: Sales.SalesOrderHeader AS TBL: s)
      CStCollBaseTable(ID=2, CARD=121317 TBL: Sales.SalesOrderDetail AS TBL: d)

This happens to be the same result as the calculation in the question, though the detailed reasoning is different (i.e. it is not based on an assumed nested loops implementation).


Visualization of MySQL/PostgreSQL table structure as a graph/UML

A useful little protip - Visio is quite widely available within corporate I.T. suites and Visio Pro or higher can be used to reverse engineer and document a database.

Visio professional has a database reverse engineering tool that will read a database schema and create E-R models from it.  Both PostgreSQL and MySQL support ODBC INFORMATION_SCHEMA tables so the ODBC driver for the Visio DB modeller will work with them.  Start by creating a database diagram, and then look for 'Reverse Engineer' under the 'Database' menu.

As a bonus, if the physical DB has missing foreign key constraints you can add them back into the model for documentation purposes.  For a more complex schema you can also create several diagrams showing different subsystems.  I find myself using this to document systems at client sites on a semi-regular basis and it's often the only tool available.


Investigating high SQL waits, especically LCK_M_U

Locks tend to form chains and you are always interested mostly in what the process at the head of the chain is doing. Simply looking at lock wait times can be misleading because many processes can wait long times (thus increasing the wait times stats) but all be blocked by a single dog-slow process. What you likely see is a coalesce point: because locks granted have to be compatible with all current grantees and all pending waiters whenever a high level lock (X, or SCH_M) gets into the wait queue for a resource all subsequent requests queue up behind this.

To give an example: say you have a query running a report on a table, say it takes 5 mins. It hold an IS lock ont he table. This IS is compatible will all normal operations (reads and writes). A request comes in that wants SCH_M. Is incompatible with the IS so he's put in the queue. Now all of the sudden all other activity on the table goes into the queue, because every requests, reads or wites, are incompatible with this waiter. So all of the sudden all lock times spike. After 5 min the slow query finishes (it 'drains' in the DB jargon), the SCH_M is granted, it does its job in 5 ms and everyone else is free to proceed. This is just an (extreme) example, it doesn't have to be SCH_M. The idea is that just wait times don't tell the full story.

Luckily SQL Server itself can report blocking chains to you via an almost unknown feature called blocked process threshold. Mix in Blocked Process Report and DDL event notifications and you get a fully automated solution.

For an example see my answer at MS SQL Server third party transaction blocking monitor tools.

Another observation: You have 22.30% wait times on IX. This implies a object level lock occurred. Intent locks (IS, IX) are compatible so the fact that IX was blocked suggests that someone acquired an incompatible object level lock. A scan acquiring an S, or a big update escalating to X.


SQL Server stored procedures fast in SQL but slow when called by ASP.NET

Changing ARITHABORT inside the procedure causes it to recompile:

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

So most likely you're dealing with parameter sniffing. If you don't figure other ways to fix this, adding "... option (recompile)" into the end of the statement you're having trouble should solve the issue. That of course increases CPU usage (slightly) because it has to do a new plan for that statement every time.


Assuming a query like:

SELECT category, COUNT(*)
FROM T
GROUP BY category
HAVING COUNT(*) > 3

You can mimic that with:

SELECT category, cnt
FROM (
    SELECT x.category
       , ( SELECT COUNT(*) from T where T.category = x.category ) as cnt
    FROM (
        SELECT distinct category from T -- GROUP BY
    ) as x
) as y
WHERE y.cnt > 3 -- HAVING


How to check the syntax of pg_hba.conf and other postgresql conf files on ubuntu/debian/linux?

You can run:

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

.. from within psql. or kill -HUP the postmaster process.

Any config validation errors will then get put into the Postgres log file, and it won't reload the config. Example log:

2016-10-05 10:31:57 BST LOG:  received SIGHUP, reloading configuration files
2016-10-05 10:31:57 BST LOG:  invalid connection type "awdawdawdawd"
2016-10-05 10:31:57 BST CONTEXT:  line 1 of configuration file "/etc/postgresql/9.3/main/pg_hba.conf"
2016-10-05 10:31:57 BST WARNING:  pg_hba.conf not reloaded

I'm not aware of a tool that just checks config for validity, but there was a thread on the pgsql-hackers mailing list here that discussed a proposal for a validator.


What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

It's not duplicating of the information, depending on the requirement we could utilize anyone them:

DMV: The prefix sys.dm_ represents DMV the under lying information from Dynamic Management views
Catalog View: No prefix sys. represents catalog views. For more details..


What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL?

I particularly like this one which works from within your (firefox) browser. If you're not a firefox user, then here is a list of alternatives.


Full Text Search only performs 'starts with' searches

This is, I think, a common misconception when it comes to full text search in SQL Server.

Full Text Search enables searching for entire words as well as a number of other things.  From the CONTAINS documentation

  CONTAINS can search for:

  A word or phrase.
  The prefix of a word or phrase.
  A word near another word.
  A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
  A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

So just like with normal indexes on text strings, you can do prefix searching.

Neither full text search, nor normal SQL Server indexes, support "postfix" or "contains" searching within a word or text string.  So no leading wildcards, essentially.

Note that the same limitations apply to CONTAINSTABLE:

  CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.

I imagine these limitations exist because of the way indexes are logically stored in SQL Server - as b-trees, where it's very easy to "jump" or "seek" to the different parts of an index based on the start of the data in the indexed column.


In order to get the DB2 command window (DB2 CLP) in Windows, you can either Windows key + R and type db2cmd or Start > All Programs > IBM DB2 DB2COPY1 > DB2 Command Line Processor and then you can execute different db2 commands, like.

db2ilist    --> List instances
db2level    --> DB2 Version
db2licm -A  --> DB2 License

etc.

Regards,


You do not need to stand up new servers to apply a SP.

  1 - Will installation of SQL Server service pack affect any of our
  production databases? We will do this in test 1st but still wanted to
  ask

It should be fine but always apply them to a test environment and let them soak for a while just to be safe.  Always a good idea to dig into the SP fixes and see what, exactly, they fixed.

  2 - Do we need to restore the databases, after installation of a service pack? I will be doing a backup of the DB's before we apply the SP, just want to know how it affects the DB.

No, you apply the SP during a maintenance window, it will stop the instance, patch it, then restart the instance for you.  Good idea to give the server a reboot if possible, that's personal preference though.  There is no need to backup/restore any DBs.  There will be a 5-10 minute outage while the instance is down.

  3 - Are there any particular prerequisites we need to follow before the installation of a service pack? Backup, of course, but are there any others?

I'd take a good backup of your DBs just to be completely safe.  Other than that, make sure your company knows that server will be unreachable for the time that you are patching.

  4 - How can I determine if there are any known bugs in a given service pack? Should any Cumulative Updates be followed after service pack installation?

https://sqlserverupdates.com/ is a great resource for getting the latest SP and CU.  As far as bugs, 2012 SP3 is very solid and I haven't heard of any major issues or deal breakers.

  5 - Can I directly jump on to install SP3, instead of installing SP1
  or SP2?

Yes, you can apply SP3, it encompasses all previous CUs and SPs.  Then you'll need to apply the most recent CU for SP3 (CU6, currently).


Yes you can do that. The easiest way to do if your amount of rows is relatively small is:

update vertex set name = 'CCCC'
where latitude between 6.9271786 * 0.99 and 6.9271786 * 1.01 and
      longitude between 79.86224300000006 * 0.99 and 79.86224300000006 * 1.01;

With of course setting your threshold to the right level. The above example assumes 1% which is huge in terms of geo coordinates but you get the idea.

Since MySQL can only use one column for range queries from a composite key having a combined index won't help but separate index on latitude and longitude will. Then MySQL can choose the one which is more distinctive for the specific case.

create index lat_idx on vertex (latitude);
create index lat_idx on vertex (longitude);

For further optimizations if necessary you can look into spatial indexes (you haven't mentioned which MySQL version and engine you're using).

Also you can geohash the coordinates into a single numerical column and have proximity match on that then filter by the coordinates which I use on bigger database and works like a charm.

https://en.wikipedia.org/wiki/Z-order_curve
https://en.wikipedia.org/wiki/Geohash


Ok, so the documentation is not exactly stellar, but you must think this shouldn't work online because the doc says:

  Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

Which - if you invert it - states that an index that includes a MAX type, for example, can't be created online.

Now, the emphasis on the word created was put there by me - this stated restriction does not apply to rebuild (which I guess explains why rebuild isn't mentioned explicitly in that sentence). In your updated code sample, you actually have demonstrated that both rebuild and create, with the option to run online, run successfully and don't return an error. I have confirmed that this is the case (and at least in the create scenario, that conflicts with the sentence above). I have also received confirmation from Microsoft that the doc is wrong.

In fact, in the table below that (for both the 2012 doc and the 2014 one), there is a list of the types of indexes that are not supported by rebuild or create, and LOB types are no longer a constraint (whether part of the base table or included in the index being created or rebuilt). These are the only indexes that you can't rebuild online:

Disabled clustered index or disabled indexed view
XML index
Index on a local temp table

And these are the only indexes that you can't create online:

XML index
Initial unique clustered index on a view
Index on a local temp table

So this is kind of like reverse documentation - you didn't experience a problem, and this document didn't exactly lead you to believe that you should experience a problem. Well, not intentionally. I don't know that there is a document in MSDN that says, "Listen up yo, you can now create and rebuild indexes online when LOB columns are part of the equation!" - but as I pointed out above, others have blogged about it.

In the end, you are correct, and the documentation is wrong , as is the IntelliSense tooltip you get when you try to do the same - it will underline the index name as if there is an error, even though when executing the code, it works successfully. Click to enlarge:

Whether the creation or rebuild actually happens online, I did not test; only confirming that it did not generate an error, as IntelliSense (and the document above) would have you believe. But according to Microsoft and Remus' blog above, it should work as expected (forget what the doc currently says).

I've filed the following Connect item to deal with the IntelliSense problem:

SSMS : IntelliSense incorrectly warns index with MAX can't be created online

Back in June of 2012, I filed this item against the documentation, which at the time still had an entry in the table explaining that LOB types invalidated online operations.

[DOC] : ALTER INDEX topic still says no online rebuild with LOB

That Connect item was marked as fixed, because the table was corrected, but the sentence I highlight above still needs to be fixed. I'm waiting to hear if I should petition to have my existing item re-opened (we used to be able to do that ourselves), or create a new one. I'll update here when I hear back. The Connect item has been re-opened and the topic owner has received a detailed e-mail. :-)


Manually trigger "Write current results to disk" during query

No, PostgreSQL does not keep transactions in progress cached in memory. They are written to disk as usual.

All that happens at commit time is that the write ahead log (WAL) and the commit log are flushed to disk.

So you must investigate further to find the cause for your I/O overload.

Useful lines of investigation are:

use pg_stat_statements with pg_stat_statements.track = all to find the SQL statements that cause the load.
set track_io_timing = on to see which queries contribute most to the I/O load.
check if each function call runs in a separate transaction or not.

If yes, bundle several calls in a single transaction - perhaps the many WAL flushes are killing you.


How do I reclaim space in filestream data files for database in a SQL Server Availability Groups

I believe you are experiencing a sightly different scenario related to the bug described here, and officially reported here.

When a filestream table is dropped (or in your case rebuilt & moved), garbage collection will not clean up if the database is in an AG.

To work around the bug, you would need to:

Remove the database from the AG
take a log backup
manually run garbage collection
apply log backup(s) to secondary replicas WITH NORECOVERY
rejoin the database to the AG (by applying log backups, you can avoid a full resync)


logged on as sysdba, how to run dml script as another user if you do not have the passwrod

If you really need to log in as that user, use a proxy user:

SQL> conn / as sysdba
Connected.
SQL> select count(*) from dba_users where username = 'UNKNOWN_PASSWORD';

  COUNT(*)
----------
         1

SQL> conn UNKNOWN_PASSWORD/UNKNOWN_PASSWORD
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> create user proxy_user identified by proxy_password;

User created.

SQL> alter user UNKNOWN_PASSWORD grant connect through proxy_user;

User altered.

SQL> conn proxy_user[UNKNOWN_PASSWORD]/proxy_password
Connected.
SQL> show user
USER is "UNKNOWN_PASSWORD"
SQL>

Otherwise, you can run DML on objects in other schemas.


I'd suggest you another approach:

create table TrainStations
(
    RouteId int,
    OrderId int,
    StartStation varchar(50),
    EndStation varchar(50)
);

By adding Route and Order (Stops), you can get the whole journey of no matter what route.

    SELECT RouteId,
           OrderId,
           StartStation,
           EndStation
      FROM TrainStations
     WHERE RouteId = 3
  ORDER BY RouteId, OrderId;

ROUTEID | ORDERID | STARTSTATION  | ENDSTATION
------: | ------: | :------------ | :------------
      3 |       1 | SF            | Palo alto
      3 |       2 | Palo alto     | Mountain View
      3 |       3 | Mountain View | SF

dbfiddle here

Or if you want a single line:

    SELECT CONCAT(CONCAT (t1.StartStation, ', '),
                  (SELECT LISTAGG(t2.EndStation, ', ') WITHIN GROUP (ORDER BY t2.OrderId)
                   FROM TrainStations t2
                   WHERE t2.RouteId = t1.RouteId)) Route
    FROM   TrainStations t1
    WHERE  t1.RouteId = 3
    AND    t1.OrderID = 1;

| ROUTE                            |
| :------------------------------- |
| SF, Palo alto, Mountain View, SF |

dbfiddle here


How to handle errors in a transaction in a stored procedure?

Your problem stems from the fact that once the transaction becomes uncommittable (i.e. an error is raised) your loop does not honor the failure and continues to insert data into the table. When the DBE attempts to commit your subsequent changes it cannot because the transaction is no longer valid. Moving your TRY/CATCH outside of the WHILE loop resolves part of your problem.

CREATE PROCEDURE [dbo].[ErrorTest]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));
DECLARE @Try int = 0;
DECLARE @MaxTries int = 5;

BEGIN TRAN;
BEGIN TRY
    WHILE @Try < @MaxTries
    BEGIN

        print 'begin try - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
        INSERT INTO ErrorTestTable (a)
        VALUES (1);
        INSERT INTO ErrorLogTable(m,d)
        VALUES ('successfully inserted record!', sysutcdatetime());

        SET @Try += 1;
    END
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    PRINT 'begin catch - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
    INSERT INTO @error(m,d)
    VALUES ('pk violation!', sysutcdatetime());
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

INSERT ErrorLogTable(m,d)
SELECT *
  FROM @error;
END
GO

However, once the transaction is rolled back any numbers entered during the transaction will be lost.

Also, from experience I have found problems issuing an INSERT statement from inside a CATCH block. Sometimes this INSERT can also be rolled back with the transaction. However, as you can see from my answer there is a work-around which is to declare a table variable, insert your custom state into that and then perform the logging after you have correctly handled the transaction. Any data inserted into the table variable will still be available after the transaction is committed/rolled back.

You could use this to capture the numbers that are being entered, and then when the transaction encounters the primary key violation you could reinsert the numbers from the tracked table up to the number that failed, like so:

DECLARE @numbers TABLE (a INT);
DECLARE @hasError BIT = 0;
BEGIN TRAN;
BEGIN TRY
    WHILE @value < @max
    BEGIN
        INSERT ErrorTestTable VALUES (@value);
        INSERT @numbers VALUES (@value); -- this will not be reached if an error occurs...
        SET @value += 1;
    END
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    SET @hasError = 1;
END CATCH

-- re-insert the numbers that were successful
IF @hasError = 1
BEGIN
    INSERT ErrorTestTable
    SELECT a
      FROM @numbers;
END

If you want to ensure that the subsequent numbers are entered, even if one number fails you will need to add logic outside of the transaction to do it. Personally I would rewrite the stored procedure to use a set-based operation instead of the manual loop and also include logic to try and avoid the primary key violation altogether, like so:

ALTER PROCEDURE [dbo].[ErrorTest] (
    @value INT OUTPUT,
    @max INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));

BEGIN TRAN;
BEGIN TRY
WITH numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY o.object_id) AS RN
      FROM sys.objects AS o
CROSS JOIN sys.objects AS p
)
INSERT ErrorTestTable
SELECT RN
  FROM numbers
 WHERE rn between @value and @max
   AND NOT EXISTS (SELECT * FROM ErrorTestTable WHERE a = RN);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO @error(m,d)
VALUES ('pk violation!', sysutcdatetime());
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
THROW;
END CATCH

INSERT ErrorLogTable(m,d)
SELECT *
  FROM @error;
END
GO


Any ways to find past week blocking session

Unless you are collecting the data in some way (monitoring tool, etc), you aren't going to be able to find the blocking session from a week ago or any time in the past.

Start collecting current activity to a table using sp_WhoIsActive. You can use this method to do it (I wrote the post).


Best relationship database design to manage types for different entities

If your various types of individuals and organizations (employees, clients, suppliers) each have multiple phone numbers of various types, then you want to keep the phone numbers in a child table.

This child table should be an intersection between the individual/organization and a phone type table.

Since you have different allowable phone types for each type of legal entity, you can create a table for each of these lists of allowable phone types (your Case 1).  The only issue with this is that you may have duplication of data, for example having a "mobile" record in multiple allowable types tables.  There is a way to have distinct lists of allowable phone types while at the same time rationalizing these lists for management purposes.

Consider this ERD:

There is one master lists of phone types (PHONE_TYPE) which includes flags for each possible legal entity type, indicating whether a particular phone type is allowable for each type of legal entity.  The table might look like this:

create table PHONE_TYPE
( phone_type_id int primary key
, description varchar(10)
, is_client_type bit
, is_employee_type bit
, is_supplier_type bit
);

From this master list of phone types, you can create distinct views for each legal entity phone type list (i.e. CLIENT_PHONE_TYPE, EMPLOYEE_PHONE_TYPE, SUPPLIER_PHONE_TYPE).  One of these views might look like this:

create view CLIENT_PHONE_TYPE
as
select * from PHONE_TYPE
where is_client_type = 1;

This lets you maintain the phone types master list in one place while enforcing the list of allowable phone types for each of the different types of legal entity that you support.


Good example of MDX vs SQL for analytical queries

MDX and SQL are in no way the same, and often not even comparable, as they are querying multidimensional and relational databases respectively. You cannot query your existing relational database with MDX.

The main advantage of using a multidimensional model and using MDX to query it is that you are querying pre-aggregated data and that MDX is optimized to query in a statistical way rather than a relational way. You no longer query rows and tables to produce a flat result set but you are using tuples and sets to slice and aggregate a multidimensional cube.

Think of it like this: if you use a SQL query to get the total sales amount for a particular item group you would need to write a query that sums up all invoice lines for all items in the item group. If you are using a cube and have aggregations on the item group level the result is calculated during processing and the aggregations are stored for each item group, making queries instantaneous.

Multidimensional and MDX is an entirely different concept from relational set-based SQL.

Your example might become a lot simpler because you would be doing the transformations such as the date parsing during your data load process and your last month comparison could be a calculated measure. Your seoul average and today could be calculated members

If your cubes are well designed for your requirements I believe you could be slicing and dicing your example's data set without even needing to write queries but do it in a pivottable or another analysis tool.

Then again there is no "just rewriting SQL in MDX". It requires a fair bit of knowledge to do it right and a different mindset. Think venn-diagrams instead of result sets.

To provide you with an example using the adventureworks database, imagine the requirement to list the number of sales orders by customer in the category bikes.

If you did that using SQL you would need to write a query that counts the number of sales orders containing a line with a product that happens to be of the category bikes and join that to the customers table, so that would become a fairly complex query.

-- need distinct count, we're counting orders, not order lines
SELECT count(DISTINCT soh.salesorderid)
    ,pers.FirstName + ' ' + pers.LastName
FROM sales.SalesOrderDetail sod
-- we need product details to get to the category
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
-- but we need to pass via subcategories
INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
-- we finally get to the category
INNER JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
-- we also need the headers because that's where the customer is stored
INNER JOIN sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
-- finally the customer, but we don't have his name here
INNER JOIN sales.Customer c ON soh.CustomerID = c.CustomerID
-- customers
INNER JOIN Person.Person pers ON c.PersonID = pers.BusinessEntityID
-- filter on bikes
WHERE pc.Name = 'bikes'
-- but the customers table doesn't contain the concatenated name
GROUP BY pers.FirstName + ' ' + pers.LastName;

In MDX (provided your cube is well designed for this requirement) you could just write because the logic and complexity has moved elsewhere:

SELECT [Measures].[Internet Order Count] ON COLUMNS,
[Customer].[Customer].Members ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Category].[Bikes]


Two Databases on same server handle date differently

If I understand the problem correctly, when both users have British as default language, one user's request goes through fine because the language setting changes automatically to us_english.  But for another user, the language setting doesn't change automatically, hence the error.

The BOL states following regarding default language and how it can be overridden.

  The default language for a login can be overridden by using CREATE
  LOGIN or ALTER LOGIN. The default language for a session is the
  language for that session's login, unless overridden on a per-session
  basis by using the Open Database Connectivity (ODBC) or OLE DB APIs.
  Note that you can only set the default language option to a language
  ID defined in sys.syslanguages (0-32). When you are using contained
  databases, a default language can be set for a database by using
  CREATE DATABASE or ALTER DATABASE, and for contained database users by
  using CREATE USER or ALTER USER. Setting default languages in a
  contained database accepts langid value, the language name, or a
  language alias as listed in sys.syslanguages.

Based on this the following can affect the language setting.

Database default
User default
DSN setting, connection strings, etc..
SET LANGUAGE in session

If underlying statements can't be changed, is there a setting that can affect it in the third party app?  Or is there are trigger for a certain user login?  Though I believe it's one or a combination of 2 or more in the above list....


Is this work flow suitable for Amazon RDS?

The difference in RDS and MySQL on EC2 is that RDS is a managed deployment of MySQL (you can't change server settings) but not vanilla MySQL either.

So if you do not need to alter MySQL's server settings, RDS would be fine. Mostly due to it being EBS backed (Elastic Block Storage) EC2 has ephemeral storage (not persistent) unless you install it with an EBS backed volume (for all your data files) which is a best practice.

if you need to process the data in some other way, rather than just transfer and store it you will need a EC2 instance anyway. So, you could do it all on on EC2 instance, or separate the concerns.

As far as the 10MB data transfers you will be fine and potentially all free on Amazon's network, although if you start using their other services for data processing in and out of their network, make sure to have all your resources in a Availability Zone together, as data transfer costs go up from free to $/GB->TB a month you may be transferring across the wire.

Check their free tier out, you could possibly do all this for 12 months and never spend a dime.

EDIT:
To correct myself slightly, you can't change ALL the server variables but, you can change some of the more important ones still on RDS.
RDS Server Variables Article

Good luck.


Subtract TotalExpense from BudgetAmount In Budget Table for a specific date

To start with, JOIN and GROUP BY

select
    b.BudgetDate,
    sum(b.BudgetAmount) - coalesce(sum(ex.ExMount),0) as BudgetLessExpenses
from dbo.Budget b
left join dbo.Expense ex
on b.BudgetDate=ex.Date
group by b.BudgetDate

BTW. Shouldn't there be a UNIQUE constraint on [Budget].[BudgetDate] ?


Deleting duplicate rows with condition(Using SQL and LINQ)

WITH KeepLatest AS
(
  SELECT *,
     RANK() OVER (
       PARTITION BY CPT4,CPT4Mod
       ORDER BY Eff_Date DESC, ItemCharge) as Rnk
  FROM dbo.YourTable
 )
 DELETE KeepLatest
 WHERE Rnk > 1
 ;

By using RANK() instead of ROW_NUMBER() you will avoid deleting anything if you have a row which is identical across all four columns - although I'm not sure why you'd want that.

If you know the CPT4Mod value, include: AND CPT4Mod = @CPT4Mod


Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

I'm not sure what is the vanilla R-Tree but there is internal citations inside the source file you linked.  Also, the R-Tree inside of PostGIS is directly copied from PostgreSQL with these minor modifications (as in the FAQ),

  Why aren't PostgreSQL R-Tree indexes supported?

  Early versions of PostGIS used the PostgreSQL R-Tree indexes. However,
  PostgreSQL R-Trees have been completely discarded since version 0.6,
  and spatial indexing is provided with an R-Tree-over-GiST scheme.

  Our tests have shown search speed for native R-Tree and GiST to be
  comparable. Native PostgreSQL R-Trees have two limitations which make
  them undesirable for use with GIS features (note that these
  limitations are due to the current PostgreSQL native R-Tree
  implementation, not the R-Tree concept in general):

  R-Tree indexes in PostgreSQL cannot handle features which are larger than 8K in size. GiST indexes can, using the "lossy" trick of substituting the bounding box for the feature itself.
  R-Tree indexes in PostgreSQL are not "null safe", so building an index on a geometry column which contains null geometries will fail. [GiST indexes are null-safe]


Remove duplicates from different columns

This probably ain't going to be right answer, but this works for data you gave us.

;WITH TestData (ColA, ColB, ColC, ColD)
AS (
    SELECT 1, 1, 'ABC', 'DEF'
    UNION ALL
    SELECT 1, 1, 'DEF', 'ABC'
    UNION ALL
    SELECT 1, 1, 'GHJ', 'LKJ'
    UNION ALL
    SELECT 1, 1, 'LKJ', 'GHJ'
    UNION ALL
    SELECT 1, 1, 'ABC', 'HJK'
    UNION ALL
    SELECT 1, 1, 'HJK', 'ABC'
)
SELECT ColA, ColB, ColC, ColD
FROM TestData
EXCEPT
SELECT ColA, ColB, ColD, ColC
FROM TestData
WHERE ColC < ColD


Why does Sql Server makes this bad query plan choice?

That top being kind of removed from the order by makes is hard for the query optimizer
It does not need to just do a lousy 10 lookups as it need top 10  WHERE [Extent2].[id] IS NULL
With more statistics the query optimizer may get smarter
I know you are using an ORM but give this a try

SELECT  Top(10) 1 AS [C1],
        [Extent1].[SupplierNumber] AS [SupplierNumber],
        [Extent1].[ArticleNumber]  AS [ArticleNumber],
        [Extent1].[Id] AS [Id],
        [Extent1].[ArticleName] AS [ArticleName]
        FROM [dbo].[SalesEntry] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Article] AS [Extent2]
            ON [Extent1].[ArticleNumber]  = [Extent2].[ArticleNumber]
           AND [Extent1].[SupplierNumber] = [Extent2].[SupplierNumber]
WHERE [Extent2].[id] IS NULL
ORDER BY [Extent1].[SupplierNumber] ASC, [Extent1].[ArticleNumber] ASC


Importing large file while keeping table available for other operations

Holy cow, you've got a lot of questions in here:

  How the table will be affected? During this import (updates and inserts) what could happen to other processes trying to read or write data to this table?

With careful index design and partitioning, you can get away with minimal disruption to other queries.  Discussing how to implement partitioning is a little beyond what we can talk through here - check out Louis Davidson's book on SQL 2008 database design.  It covers concurrency pretty well.

  I know bulk insert functionality as being something extremely fast compared to normal inserts. Does SQL Server 2008 provides something similar for UPDATES?

Not really.  You can't update in bulk because you could be changing rows all over the table.  This is where index design comes in (as explained in Louis's book).

  All the row to be imported will be of the same UserId, so having this table partitioned by userid will improve the performance right? As all the other queries related to other users which doesnt belong to the affected partition will not be affected... Is this correct?

Only if your indexes are also partitioned by UserId, and that's rarely the case.  Usually you'd want indexes arranged in a different manner - but again, that's covered in the book.


I found a solution in comments of http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

SELECT DATE_ADD(day, INTERVAL (7 - DAYOFWEEK(day)) DAY) AS day, AVG(position) AS position FROM table


configure: error: SQLite3 library not found

I tried both, sudo apt-get install sqlite and building from source but problem persists. Then I tried the following command and it worked. May be it'll help someone else too.
sudo apt-get install libsqlite3-dev


Creating a proper unique constraint on your table will prevent duplicates amongst the columns specified.

Your naming of the constraint should be unique to the table itself.  All constraints for a database are listed under the pg_constraint table.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username_emp_name UNIQUE (USERNAME, EMP_NAME);

There are quite a few docs on the web you can find about standardized SQL naming formats.

To do a check against both the USERNAME and EMP_NAME, you would need to specify constraints individually for each.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username UNIQUE (USERNAME);
ALTER TABLE emp ADD CONSTRAINT uq_emp_emp_name UNIQUE (EMP_NAME);

This would prevent employees from creating multiple usernames under the same employee name.

I find that the PostgreSQL official documentation is super concise and well written.
https://www.postgresql.org/docs/9.5/static/ddl-constraints.html
Just make sure you have your appropriate Postgres version selected.


You can join the Result table to itself like this:

SELECT
  team
FROM
  Result A
  JOIN Result D ON D.team = A.team AND A.year < D.year
WHERE
  A.number=42
  AND
  A.situation='ASCENT'
  AND
  D.number=38
  AND
  D.situation='DESCENT'
  AND
  D.league='Premier league'


Do PostgreSQL toasted fields use any per row, in-table space?

Yes, see TOAST in Database Physical Storage

  TOAST usurps two bits of the varlena length word (the high-order bits
  on big-endian machines, the low-order bits on little-endian machines),
  thereby limiting the logical size of any value of a TOAST-able data
  type to 1 GB (230 - 1 bytes). When both bits are zero, the value is an
  ordinary un-TOASTed value of the data type, and the remaining bits of
  the length word give the total datum size (including length word) in
  bytes. When the highest-order or lowest-order bit is set, the value
  has only a single-byte header instead of the normal four-byte header,
  and the remaining bits give the total datum size (including length
  byte) in bytes. As a special case, if the remaining bits are all zero
  (which would be impossible for a self-inclusive length), the value
  is a pointer to out-of-line data stored in a separate TOAST table.
  (The size of a TOAST pointer is given in the second byte of the
  datum.)


Is `log_bin` necessary on slave in master-slave MySQL replication?

If the slave is not a master to other slaves, then you do not need binary logging on the slave

To reclaim the space of those logs on the Slave immediately, run this

mysql> STOP SLAVE;
mysql> RESET MASTER;
mysql> START SLAVE;

This will hose all binary logs on the Slave.

You could then remove or comment out log_bin from the my.cnf of the Slave and restart mysqld


Proper database structure for storing multiple foreign keys

The countries column should store country IDs like: 10,12,14.

This is a poor design, which you've already realised, (through your dislike of "LIKE").

Normalise your data and split these repeated values out into separate tables.

select * from tours ;
+------+
| id   | ...
+------+
| 1234 | ...
+------+

select * from countries ;
+----+--------+
| id | name   |
+----+--------+
| 10 | Brazil |
| 12 | Peru   |
| 14 | Chile  |
+----+--------+

select * from tour_countries ;
+---------+------------+
| tour_id | country_id |
+---------+------------+
|    1234 |         10 |
|    1234 |         12 |
|    1234 |         14 |
+---------+------------+

select * from themes ;
+-----+-----------+
| id  | name      |
+-----+-----------+
| 111 | Discovery |
+-----+-----------+

select * from tour_themes ;
+---------+----------+
| tour_id | theme_id |
+---------+----------+
|    1234 |      111 |
+---------+----------+

Finding data?

  SELECT ... WHERE COUNTRY = 'Brazil' AND THEME = 'Discovery'

select t.*
from       tours          t
inner join tour_countries tc  on  t.id = tc.tour_id
inner join countries      c   on  tc.country_id = c.id
inner join tour_themes    tt  on  t.id = tt.tour_id
inner join themes         t   on  tt.theme_id = t.id
where c.name in ( 'Brazil' )
and   t.name in ( 'Discovery' )
order by t.id ;

Regards,   Phill  W.


How do I echo data from two tables that are associated to one user in php

To answer this, I did the following (you can check out the
SQLFiddle here).

CREATE TABLE Company
(
  cid INT,
  cons_no VARCHAR(2),
  company_name VARCHAR(20),
  phone VARCHAR(15),
  address VARCHAR(20)
);

INSERT INTO Company
VALUES
(1, 'q1', 'Apple', '222222', 'New York'),
(2, 'q2', 'Samsung', '11111', 'Ohio'),
(3, 'q3', 'Nokia', '333333', 'London');

CREATE TABLE Company_Track
(
  id INT,
  cid INT,
  cons_no VARCHAR(2),
  branch_location VARCHAR(20),
  date_opened INT
);

INSERT INTO Company_Track
VALUES
(1, 1, 'q1', 'Stamford', 2010),
(2, 1, 'q1', 'Kingsway', 2011),
(3, 1, 'q1', 'Cornerstone', 2014),
(4, 2, 'q2', 'Madison', 2001),
(5, 3, 'q3', 'Lewiston', 2015);

And then ran the query

SELECT  c.company_name, c.cons_no, t.branch_location
    FROM  Company c
    INNER JOIN  Company_Track t ON c.cid = t.cid;

Which gives the result you want

company_name    cons_no branch_location
Apple           q1      Stamford
Apple           q1      Kingsway
Apple           q1      Cornerstone
Samsung         q2      Madison
Nokia           q3      Lewiston


Triggering a shell script from MySQL without any 3rd party libraries

Nothing native unless using the MySQL Client where you can issue \! {shell command}.

e.g.

mysql> \! pwd
/home/moore


PAGELATCH_XX - This indicates that SQL is waiting on access to a database page, but the page is not undergoing physical IO. This class of problem is caused by a large number of spids attempting to access the same physical page at the same time. The wait_resource is the page number (the format is dbid:file:pageno) that is being accessed. If diagnosing on a live server, the Performance Dashboard reports will run DBCC PAGE and display the output to tell you what object and type of page the contention is for (allocation, data, text, ...)

Since the pages that SQL is most frequently waiting on are in the tempdb database (page number in dbid 2), you may be facing tempdb allocation latch contention.

Tempdb allocation page latch contention can occur with workloads that create and destroy temp objects (including worktables/workfiles for sort or hash operations) hundreds or thousands of times per second.

Resolution -

1.Implement trace flag -T1118.

2.Increase the number of data files in tempdb to maximize disk bandwidth and to reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.


Migrate Jira to MySql - Unknown system variable 'storage_engine'

If you are using MySQL 5.6, InnoDB is already the default.

Please do not use storage-engine because it is deprecated and probably explains the error.

You should use default-storage-engine or default_storage_engine in my.cnf instead

[mysqld]
default-storage-engine = InnoDB

GIVE IT A TRY !!!


Oracle db_name, db_unique_name, and ORACLE_HOME

You can have 2 databases with the same db_name on the same system on linux in the same ORACLE_HOME. You just have to make sure that the instance name differs. EG different pfile/spfile,... It's just not advised to give them the same name...
When running the runinstaller you just give them the same global name and a different SID.
--> Not sure how Windows reacts on it.
The reason why the db_name has to be the same in a primary-standby setup is because the name is "imprinted" on the datafiles. The db_unique_name is mandatory in order to make a difference between the databases and to setup a syncing. The recovery would be impossible if you use a different db_name


Is there a way to measure replication lag in MySQL with a resolution less than 1 second?

Have you tried maatkit heartbeat ? This is the one I'm currently using. You will need another perl process running, but from my perspective, this is much more helpful than the current MySQL mechanism.

I'm also using mk-slave-delay to generate lag. I keep a 1 hour lag in one of the slave to have an extra layer of protection in case of SQL injection or user errors.

These two scripts do the trick.

Saludos


Yes. It's in the MSDN documentation pages: Foreign Key relationships

  A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

That page does not provide much more details but testing reveals that "same datatype" means same type and same size. Also, if one tries to make a FOREIGN KEY constraint between for example, a VARCHAR(20) and a VARCHAR(30) column,

CREATE TABLE a
( aid VARCHAR(20) PRIMARY KEY ) ;

CREATE TABLE b
( bid INT PRIMARY KEY,
  aID VARCHAR(30) ) ;

ALTER TABLE b
  ADD CONSTRAINT test_different_sizes
    FOREIGN KEY (aid)
    REFERENCES a (aid) ;

we get the explanatory error:

  Schema Creation Failed: Column a.aid is not the same length or scale as referencing column b.aid in foreign key test_different_sizes. Columns participating in a foreign key relationship must be defined with the same length and scale.

Also note that some DBMS (like Oracle, MySQL, Postgres) allow foreign keys between columns of same datatype (i.e. VARCHAR or CHAR) but different size.

Postgres is even less strict. It allows foreign keys between columns of CHAR, VARCHAR or TEXT datatypes in any combination.


I would consider it to be a bad practice unless you're 100% sure that it won't limit your effective concurrency. For example, I use TABLOCK all the time when I know that I'll be the only one accessing the table on a development database.

One technique that can help with the hypothetical issue that you called out is to write the transaction to use a single query instead of multiple queries. SQL Server will take a lock at the appropriate level (depends on the table structure) and I think that things will work out. Consider the following query:

BEGIN TRANSACTION

UPDATE Balance_table
SET balance = balance - 20
WHERE customer_id = ????
and balance >= 20;

COMMIT TRANSACTION;

For the default isolation level that query should prevent the situation that you called out from occurring.

For more complicated examples, you could consider using sp_getapplock to lock a resource at the account level. You could also try a UPDLOCK and HOLDLOCK pattern.


A materialized view requires extra storage because it is a table.

You want to make your materialized view to be refreshed on commit (for an immediate check) and to be fast refreshable, as a complete refresh may take too long to wait for.

General Restrictions on Fast Refresh

Assuming multiple items can be placed in a single box, the sum of item sizes needs to be accounted for (aggregation), so :

Restrictions on Fast Refresh on Materialized Views with Aggregates

A materialized view requires materialized view logs (which are also tables, more extra storage + administration) on the source tables for fast refreshes.

Maintaining extra tables means generating more redo and undo as well.

A materialized view provides more functionality than you need, which you will never use.

What a trigger would perform on your DML operations (aggregate sizes of inserted/updated/deleted items), a materialized view refresh would perform as well.

When you remove an item, the materialized view needs to be refreshed, but you do not even need to execute the trigger on deletions.

When you update an item, the materialized view needs to be refreshed, but you can skip the aggregation in the trigger if :new.box_id = :old.box_id and :new.size <= :old.size.

Or even better, you can cache the size of boxes in memory (result cache) and access them in the trigger without performing any actual SQL aggregation.

Yes, some of the above seem insignificant, and they usually are, the difference may be unnoticeable.
But with such a use case, a trigger can have the same effect as the materialized view combined with a check constraint, but with lower storage/administration overhead and requirements, so I would choose the trigger.


Fix Linked Server Without Restarting SQL Server Instance

Depending on the statement you are running and how you have your linked servers configured, you could be running into a situation where you have an orphaned distributed transaction sitting out there that you need to kill.  To identify if this is the case or not, first run the following query:

SELECT  DISTINCT request_owner_guid
FROM    sys.dm_tran_locks
WHERE   request_session_id = -2

If this returns records, chances are good that you are in fact running into this very situation.  To kill these orphaned transactions, you have to run the following statement for each record returned by the query above:

KILL 'UOW'

UOW is the request_owner_guid returned in the above query.

I've run into this situation with both linked server issues and jobs that are making calls externally from the SQL Server process.  In either case, I would find that an external process would interfere (e.g. network drop, process is killed or errors unexpectedly, etc.) and then the transaction wouldn't properly terminate at the SQL Server.  SQL Server won't know that the transaction has actually failed and things will just sit out there until you either walk through this process or bounce the server as you are already doing now.

Hope that helps.


Should I check for existence of temp tables in Stored Procedures?

On Checking for #tbl

It can't hurt to check for the table's existence (and drop it if it exists) at the beginning of the procedure, but it depends on how you want to handle that scenario, and in most cases it's not possible for it to exist already anyway (at least if we're talking about the same #temp table as defined within that stored procedure).

You check for a table's existence using:

IF OBJECT_ID('tempdb..#tablename') IS NOT NULL

You can't check tempdb.sys.tables because the actual name is #tablename__________some hex code, and you shouldn't use OBJECT_ID('...') > 0 because of this potential issue.

Of course, there are exceptions. Two that come to mind:

if you call everything #temp, or #t, or #x, then it's possible that such a table already exists from an outer scope prior to calling the procedure. You could make a case that you should just drop it and create your new one in that case, but you could also make the case that this is an error condition you want to know about - so maybe it's okay that CREATE TABLE #x fails.
you actually may want to use a #temp table created in an outer scope, and only create one if it hasn't been defined in that outer scope. This is possible and I use this technique all the time, but usually only when I want to capture data from system procedures that I can't easily manipulate myself (e.g. sp_helptext). So I might do this:

CREATE TABLE #x([Text] NVARCHAR(MAX));
GO
CREATE PROCEDURE dbo.myhelp @p SYSNAME
AS
  INSERT #x EXEC sp_helptext @p;
GO
EXEC dbo.myhelp N'dbo.myhelp'; -- inception
GO
SELECT [Text] FROM #x;

This works even though #x is defined outside. That's a terrible example because I'd rather use sys.sql_modules, but I'm sure you get the point and can envision how you might do that with your own procedures.

On DROP TABLE #tbl;

I think whether you should explicitly drop #temp tables at the end of the procedure is very much up for debate, and hence would be closed as primarily opinion-based; see these excellent blog posts by Paul White, read them thoroughly, and come back and formulate a specific question if all yours aren't answered:

Temporary Tables in Stored Procedures
Temporary Table Caching Explained


From Books Online (emphasis mine):

  FILLFACTOR =fillfactor
  Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12.

  Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. If fillfactor is 100, the Database Engine creates indexes with leaf pages filled to capacity.

  The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.

Fill factor applies to the leaf pages. PAD_INDEX will determine what happens to non-leaf pages. From the same BoL page:

  PAD_INDEX = { ON | OFF }

  ON
   The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

  OFF or fillfactor is not specified
   The intermediate-level pages are filled to near capacity

Page splitting can occur at all levels of the BTree, including the root level. Due to fan-out, however, the frequency at which intermediate pages split is (typically) an order of magnitude less than that at which leaves split. Also there are many fewer non-leaf pages than leaf pages. Consequently PAD_INDEX is less concerning than FILLFACTOR. Still worth thinking about, especially on randomly-inserted indexes.

During writes, pages will be filled up to 100%. Then they will split 50/50, or as close as possible to ensure each row is contained entirely on a single page. I believe the split is always 50/50. Subsequent writes may be skewed, however, so one of the new pages fills much quicker than the other.


improving join performance on sql server table

DDL & DML at the bottom

Standard indexes that could be created based on the information given

CREATE NONCLUSTERED INDEX IX_program_id_date_of_birth_process_date
ON dbo.TableB (program_id,date_of_birth,process_date);
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id);

Running the query

SET STATISTICS IO, TIME ON;
select
a.*,b.date_of_birth
from dbo.TableA a
left join ( select program_id,date_of_birth,max(process_date)  as maxprocess_date
            from dbo.TableB
            group by program_id,date_of_birth) b
    on a.program_id=b.program_id;

The NC index on TableA is used since ID is included in the NC index and no other columns are in TableA except the program_id coluln, which is a key column of that index.

But, since you are using select a.* you will probably have to add all these columns to the
NC index on TableA as included columns, which will increase your index size depending on the amount of columns in the table.

If we add a varchar(3) column and populate it with a value:

ALTER TABLE dbo.TableA ADD bla varchar(3)
DEFAULT ('bla')
WITH VALUES;

The index is no longer used:

Because of the additional bla column added:

To fix this, recreate the index with the bla column included.

DROP  INDEX IX_TableA ON dbo.TableA;
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id)
INCLUDE(bla);

Additionally

  None of these tables are indexed.

Heap tables are not ideal, you should consider adding a clustered index that makes sense and reading up on indexing.

More on the difference between heap tables and tables with a Clustered index here

  select  a.*

Using   Select * is not a good practice, consider naming all columns used in your select statement separately.

Bad habits to kick : using SELECT *

DDL + DML used for testing

CREATE TABLE
dbo.TableA(ID INT IDENTITY(1,1) PRIMARY KEY,
           program_id INT);
CREATE TABLE
dbo.TableB(ID INT IDENTITY(1,1) PRIMARY KEY,
           program_id INT,
           date_of_birth date,
           process_date datetime2)
-- Dataset reduced by / 10 ~= 3 588 378

INSERT INTO
dbo.TableA WITH(TABLOCK)  (program_id)
SELECT TOP(3588378) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 3588

-- Dataset reduced by / 10 ~= 2 734 333
INSERT INTO
dbo.TableB WITH(TABLOCK) (program_id,date_of_birth,process_date)
SELECT TOP(2734333) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000,
CAST('01/01/1990' AS date),
DATEADD(MINUTE,-ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),GETDATE()) -- one minute less to get distinct process_dates
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 2734


The leading zeroes before decimal point is missing in string formatting of decimal data in XML in db2 11.1

If your consumer is so sensitive to the number format and cannot interpret values without leading zeroes, you can format your numeric values explicitly:

...xmlattributes(varchar_format(interp,'S00.00000000') as "interp")


Migrating mysql.user table from MySQL 5.5 to MySQL 5.6

You could load the mysql schema into the 5.6 instance and run mysql_upgrade. I have a much cleaner method in mind: You could dump the grants as pure SQL from the MySQL 5.5. instance and then run it in the MySQL 5.6 instance.
You can do one of the following:

Extract the grants as SQL using pt-show-grants.
Run my personal emulation of pt-show-grants:

mysql -u... -p... --AN -e"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user<>''" | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' > MySQLUserGrants.sql

Once you create the script from the 5.5. instance, run the script in the 5.6 instance.
GIVE IT A TRY !!!
Note : I have a post where I helped someone manually fix mysql.user (MySQL service stops after trying to grant privileges to a user). Try this only when everything else fails ...


Changing feature on a database that is a member of SQL Server AlwaysOn availability group

There is no other way to enable service broker except taking that database out of AlwaysON Availability group, enabling service broker and then joining it back.

If you know that your source database has service broker enabled, then when you restore the database, use RESTORE .. with ENABLE_BROKER, so that you dont have to go with such trouble again.

If you try to enable service broker for a database involved in AG, you will get below error :

  Msg 1468, Level 16, State 1, Line 3
  The operation cannot be performed on database "AdventureWorks2012" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  Msg 5069, Level 16, State 1, Line 3
  ALTER DATABASE statement failed.


MariaDB - Replication - Unable to login remotely

I think this is a good clue to what is wrong:

I also noticed I am unable to login to the master from the master if I
specify its IP address rather than just localhost.

It seems as if you don't have the right host specified for the user you have created. In MariaDB and MySQL when you create a user you also need to specify a "host". This can e.g. be a specific IP address to allow connections from that IP address only, or it can be a wildcard to allow connections from all hosts, or a partial IP address combined with a wildcard:
CREATE USER replication_user@'%' IDENTIFIED BY 'super_secret_password';


How to remove a corrupted shard in MongoDB

You can use below commands to remove the shards, though I havn't tried ever but looks straight forward.

use admin
db.runCommand( { removeShard: "mongodb0" } )

--Response
{
    "msg" : "draining started successfully",
    "state" : "started",
    "shard" : "mongodb0",
    "ok" : 1
}

link: http://docs.mongodb.org/manual/tutorial/remove-shards-from-cluster


Cannot create foreign key on 2 VARCHAR(45) columns in MySQL Workbench 6.1

From the error message, what i can deduce is that a foreign key is trying to reference a table with a primary key of int, while it also has a primary key of int set on it's table, a bit confusing.

But try removing the primary  key on the referencing table like table two below, it should work fine.
so you could do something like

TABLE 1                   TABLE 2
Id(int)(pk)                   id(int)
name(varchar) ------ frontname(varchar)(fk)

or

TABLE 1                   TABLE 2
Id(int)(pk)                   id(int)(pk)
name(varchar)(pk2) ------ frontname(varchar)(pk2)(fk)

Not sure of the second one though


Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB

Install pg_dump and pg_restore or install entire PostgreSQL Binaries on your local machine or EC2, Droplet compute.
Create a dump file using pg_dump that contains the data to be loaded from GCP PG:
pg_dump -Fc -v --host=<IP or hostname> --username=<dbadmin> --dbname=<DB name> -f  dbname.dump

Restore the data into the target Database for PostgreSQL using pg_restore :
After you've created the target database, you can use the pg_restore command and the -d, --dbname parameter to restore the data into the target database from the dump file.
pg_restore -v --no-owner --host==<IP or hostname> --port=<port> --username=< dbadmin > --dbname=<target database name> dbname.dump

--no-owner parameter causes all objects created during the restore to be owned by the user specified with --username. For more information , click on pg_restore.


How to confirm if the backups are going to tape in SQL Server 2005 and 2008 R2

If you want to be sure that you're backups are going to tape, you need to periodically recall the tapes and restore the backups. We recall tapes once every three months to do this, it seems like a lot of effort but it is not overkill if you value having offsite backups.


Will creating reports on R using MySQL Database update my database on MySQL?

When you copy data from MySQL into rstudio (or your UI of choice), r pulls the data into memory.

When you then manipulate the data (by adding extra columns, for example), this happens entirely in memory & the source data (in your case a MySQL query, but it could just as easily have been from a csv file etc) is not modified in any way.

r is wonderful, but requires a slightly different way of thinking than SQL - hope you’re enjoying it.


adding autoincrement to id already part of foreign key

Try temporary disabling foreign keys (make sure no ones allowed to update the db meanwhile):

create table t1 (id int not null primary key) engine = innodb;
create table t2 (id int not null primary key
                ,t1_id int not null
                ,    constraint abc foreign key (t1_id)
                                    references t1 (id)
) engine = innodb;

set foreign_key_checks = 0;
alter table t1 change column id id int auto_increment;
set foreign_key_checks = 1;

Note that set foreign_key_checks = 1; does not validate foreign keys, so if someone manages to add invalid values whilst foreign keys are disables, you end up with an inconsistent db:

insert into t1 (id) values (1);
set foreign_key_checks = 0;
insert into t2 (id, t1_id) values (1,1);
insert into t2 (id, t1_id) values (2,2); -- invalid
set foreign_key_checks = 1; -- does not validate foreign keys

I vaguely remember that this was also the case when adding foreign keys, but this bug seems to have been fixed since:

select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.2.14-MariaDB |
+-----------------+
1 row in set (0.00 sec)

alter table t2 drop foreign key abc;
alter table t2 add constraint abc foreign key (t1_id)                                      references t1 (id);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails ("test"."#sql-5fa_a",
CONSTRAINT "abc" FOREIGN KEY ("t1_id")
REFERENCES "t1" ("id"))


Unresponsive database, sleeping processes

Q: It there a limit max amount of sleeping processes?

A: There's a limit on the number of connections to the MySQL server, that applies to all connections, not just connections in "Sleep" state.

The limit is specified in the max_connections user variable. (The default value is 151.)

There's also a limit on the number of connections from a single user account, either a non-zero value specified on the user account, otherwise derived from the max_user_connections system variable.

SHOW VARIABLES LIKE 'max%connections'

Variable_name         Value
--------------------  --------
max_connections       1000
max_user_connections  200

(On the Windows platform, there may be some additional limitations, beyond those imposed by MySQL itself, e.g. if you "churn" connections, you can temporarily exhaust the available ports, until Windows returns ports that are no longer being used.)

Reference:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections


Select artist_ref, SUM(week) as week,
SUM(month) as month,SUM(quarter) as quarter
FROM tracks LEFT JOIN popularity
USING (Filename)
group by artist_ref;

SAMPLE FIDDLE


Create foreign key on foreign table in postgresql

You can't create a FOREIGN KEY constraint that references a table in either a different database or through a foreign data wrapper. A foreign key must reference a (base) table* in the same database (a base table: not a view, not a foreign data wrapper table).

If you can change your design to have these 2 databases as 2 schemas in the same database then you can create the foreign keys without any problem.

*: there's also another restriction: a permanent table cannot reference a temporary table.


Slow While Loop, Query Improvment Assistance

I created the following minimally complete and verifiable example, based on the two tables in your original question.  It uses the LEAD T-SQL statement to obtain a time range from the dbo.Dim_Time table, which can be compared to the incoming rows quite easily.

IF OBJECT_ID(N'dbo.Stg_IncomingQueue', N'U') IS NOT NULL
DROP TABLE dbo.Stg_IncomingQueue;

IF OBJECT_ID(N'dbo.Dim_Time', N'U') IS NOT NULL
DROP TABLE dbo.Dim_time;

CREATE TABLE dbo.Dim_Time(
    TimeID int IDENTITY(1,1) NOT NULL,
    StartDateTime time(0) NULL,
  CONSTRAINT PK_Dim_Time PRIMARY KEY CLUSTERED
  (TimeID ASC)
  ) ON [PRIMARY]
GO

;WITH src AS
(
    SELECT TOP (10) sv.number
    FROM master.dbo.spt_values sv
    WHERE sv.type = N'P'
    ORDER BY sv.number
)
INSERT INTO dbo.Dim_Time (StartDateTime)
SELECT TOP(289) CONVERT(time(0), DATEADD(minute, (s3.number * 100 + s2.number * 10 + s1.number) * 5, CONVERT(time(0), '00:00:00')))
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
ORDER BY s3.number * 100 + s2.number * 10 + s1.number

CREATE TABLE dbo.Stg_IncomingQueue(
    IncomingID int IDENTITY(1,1) NOT NULL,
    CustomerID int NOT NULL,
    TimeID int NULL,
    InsertTime datetime NULL,
 CONSTRAINT PK_IncomingQueueMonitor PRIMARY KEY CLUSTERED
(IncomingID ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.Stg_IncomingQueue (CustomerID, InsertTime)
VALUES (1, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (2, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (3, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (4, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'));

This piece replaces your entire WHILE loop, with a single UPDATE statement, which is both more efficient, and easier to understand.

UPDATE dbo.Stg_IncomingQueue
SET TimeID = t.TimeID
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN (
        SELECT dt.TimeID
            , dt.StartDateTime
            , EndDateTime = LEAD(dt.StartDateTime, 1) OVER (ORDER BY dt.StartDateTime)
        FROM dbo.Dim_Time dt
        ) t ON CONVERT(time(0), iq.InsertTime) >= t.StartDateTime AND CONVERT(time(0), iq.InsertTime) < t.EndDateTime;

The results, compared side-by-side with the Dim_Time table:

SELECT *
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN dbo.Dim_Time dt ON iq.TimeID = dt.TimeID;

The output looks like:

╔════════════╦════════════╦════════╦═════════════════════════╦════════╦═══════════════╗
║ IncomingID ║ CustomerID ║ TimeID ║       InsertTime        ║ TimeID ║ StartDateTime ║
╠════════════╬════════════╬════════╬═════════════════════════╬════════╬═══════════════╣
║          1 ║          1 ║    271 ║ 1875-06-30 22:31:49.000 ║    271 ║ 22:30:00      ║
║          2 ║          2 ║    116 ║ 1857-07-01 09:38:59.000 ║    116 ║ 09:35:00      ║
║          3 ║          3 ║    218 ║ 1854-09-18 18:08:39.000 ║    218 ║ 18:05:00      ║
║          4 ║          4 ║    221 ║ 1860-05-31 18:22:25.000 ║    221 ║ 18:20:00      ║
╚════════════╩════════════╩════════╩═════════════════════════╩════════╩═══════════════╝

Assuming there isn't a massive amount of incoming rows, this may work fairly well.  Be aware, I'm using CONVERT() to convert the incoming datetime column into a time(0) value, which comes at a cost of the query optimizer not being able to use available statistics to help create a great plan.  The "actual" query plan for the insert statement shows this warning:

  Type conversion in expression (CONVERT(time(0),[iq].[InsertTime],0)>=[dt].[StartDateTime]) may affect "SeekPlan" in query plan choice, Type conversion in expression (CONVERT(time(0),[iq].[InsertTime],0)<[Expr1002]) may affect "SeekPlan" in query plan choice.

If you need to avoid the type-conversion during the update, you can move that workload to the insert operation by updating the definition of dbo.Stg_IncomingQueue to include a persisted computed column, as in:

CREATE TABLE dbo.Stg_IncomingQueue(
    IncomingID int IDENTITY(1,1) NOT NULL,
    CustomerID int NOT NULL,
    TimeID int NULL,
    InsertTime datetime NULL,
    InsertTime0 AS CONVERT(TIME(0), InsertTime) PERSISTED
 CONSTRAINT PK_IncomingQueueMonitor PRIMARY KEY CLUSTERED
(IncomingID ASC)
) ON [PRIMARY]
GO

The update statement then becomes:

UPDATE dbo.Stg_IncomingQueue
SET TimeID = t.TimeID
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN (
        SELECT dt.TimeID
            , dt.StartDateTime
            , EndDateTime = LEAD(dt.StartDateTime, 1) OVER (ORDER BY dt.StartDateTime)
        FROM dbo.Dim_Time dt
        ) t ON iq.InsertTime0 >= t.StartDateTime AND iq.InsertTime0 < t.EndDateTime;


SQL Server 2012 High Availability on VMware virtualization

It really depends on what you are trying to protect from and what your SLAs are.  If your goal is to protect against hardware failure and you can handle a few minutes of downtime in the event of a hardware failure then stick with the native VMware options.

If you need to protect yourself from blue screens then clustering on AlwaysOn AGs will be the way to go.  If you are just trying to protect yourself from hardware failures but you can't afford to have even a few minutes of downtime then clustering of AlwaysOn AGs will again be the way to go.

Choosing between FCI (Fully Clustered Instance) and AG will depend on what you are trying to do.  If you need to scale out reads for reporting then AG.  If storage costs are not an issue then AG.  Keep in mind that FCI will require RDMs if you are using fiber channel or iSCSI.  If you use RDMs then you have to deal with not having vMotion while the VMs are online.  With AGs you don't need RDMs so you don't have this issue.

There are lots of options available to you, it just depends on which way to go.

As for clustering under VMware, it works just fine.  I've got lots of clients who have virtualized most or all of their SQL Servers and have plenty of Windows clusters inside of VMware.


No, Oracle won't add an extra datafile.

Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

See Oracle® Database Administrator's Guide 11g to get an idea of regular sizing.


How to restrict table access to a application

For the security credential/context which the application uses to authenticate to the DB, set an EXPLICIT DENY to it on the table to not allow it to access it at all (e.g. SELECT, UDPATE, DELETE, etc.).

You should really have the application credential locked down in the DB which it uses anyway to ONLY allow it to ONLY have the EXPLICIT level of access it needs to each DB object specifically in the name of security.

So why the user table is a concern now, wonder what other tables, etc. it has access to due to poor security design.


What is the point of the Script Date in a stored procedure script generated by SSMS?

Why not? It allows you to see when the script was created if you save it & reload another time. The others you mention (created/last modified/last ran) aren't so useful as they're subject to frequent change. And can be grabbed from system views when/if you do need them.

You can turn it off in Options here if you really want to:


Looks like you want your current date to be before or on last show date. If that is the case try following HAVING clause.

HAVING MAX(CAST(CONCAT(shows.show_date, ' ', shows.show_time) AS DATETIME) >= Now()


PostgreSQL performance worse when adding CPUs

I often notice that PostgreSQL benchmarks run faster once more than half the CPUs are busy.  I think that what happens is Linux tries to keep the client and the server on different CPUs, which slows down the intense back-and-forth between them (which is the main thing your benchmark is benchmarking).  Once Linux is forced to run them on the same CPU, it gets faster.

Although I haven't seen the effect to be nearly as large as you report here.


Ascending keys getting bad plans in SQL 2016 (new CE), replacement for TF 4139 et al.?

If all of these are true:

You only need to fix a relatively limited number of queries (dozens to hundreds)
You know exactly what they are, and you can get the T-SQL
They don't change (end users can't dynamically build their own, or add search conditions)
You can guarantee they won't change
You can control which context they execute in (for example, they're not executing in TempDB with fully qualified object names pointing to user databases)
They're in a limited number of databases (as opposed to a software-as-a-service approach where every client is in their own database, and you want to fix all of the queries in all of the databases)

Then plan guides are a good fix. Just know that if you violate any of these rules (like if the queries start changing) then the plan guides won't work.


Where can I get the master.dacpac file for SQL Server 2012?

You should be able to add a Database Reference. Click Project-> Add Database Reference. Click the radio button for System and then master. Code with references to objects in the master db should now build correctly.


MongoDB: Privileges on objects created by me only?

A database does not need to exist at the time you grant a user permissions on it.

So the solution to this is simply to pre-grant a superset of all permissions you expect might be needed in the future. Then they will just sit there unless and until needed.

In my case the list of permissions needed changes fairly frequently, but the prefix-style patterns are predictable. (Namely, the user's name matches the first part of its databases' names.) So I use a config file like this, to which I add entries before they are needed. The script runs regularly, and grants new permissions, which just sit there until they are actually used.

[ { 'replica_set' : 'cluster0',
    'members' : [ 'server0', 'server1', ... 'serverN' ],
    'permissions' : [ 'user' : 'lizard_user', 'db_patterns' : [ 'lizard_' ] },
    ...
  },
  { 'replica_set' : 'cluster1',
  ...
  }
]

I'm posting this workaround primarily to note the misconception I had about granting permissions on not-yet-existent objects, in case others share it. It would still be cool if there were a privilege like 'readWriteAllMyDatabases'.


When in doubt ask Tom...

backup up and recovery generating extra redo
Which is good reason to use RMAN for backups rather than
scripted hot backups using begin backup/end backup.


MySQL: What happens to the row when skip-errors=1062 is set

Yes, it is equivalent, provided the slave error you are skipping is 1062 (Duplicate Key)

It would have to appear in the [mysqld] section of my.cnf like this

[mysqld]
slave-skip-errors=1062

All other error numbers will stop the SQL thread and would require

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

to have replication conitnue

If you have multiple error numbers to skip, it should be a comma-delimited list of error numbers

[mysqld]
slave-skip-errors=1062,1053

For more information, please read the MySQL Documentation on it.

You asked

  Does it get silently dropped?

Yes it does. Just remember that any non-key columns that were supposed to change will not change.


How to add a constraint to a table using a partial index?

You can't define a unique (or primary key) constraint based on a partial index.

However, to enforce uniqueness of the lan column for all non-deleted rows, the partial unique index you already have is enough.

There is no need to add a constraint.


MongoDB Production Amazon - 3 data nodes per replica set, or 2 data nodes plus a shared arbiter

Option 2 has no problems in theory; however the more limited redundancy may be a problem.

About the arbiters:

Yes, you can run multiple arbiters on a single lightweight machine. An arbiter is a very lightweight process, doing little but voting, so you can run multiple arbiters on a single box. Each one must be a separate mongod process.

About the redundancy:

If you have a 2-data-node replica set, and one of the data nodes fails, then the other one can function as primary (with the vote of the arbiter), so you have resilience there, but you no longer have redundancy.

Whether that is a severe problem or not depends on your ops, how quickly you can fix or replace the failed node. That depends on your monitoring capabilities, your ops staff competence and availability, etc; that's a decision you need to evaluate.


Your statistics are fine.

It is telling you that you have

exactly one row with value 1
1 row with value 213152
1 row with value 213151
213,144 distinct values that are > 1 and < 213,151 (so basically one row for each of the 213,149 possible integer values between that with 5 missing)


How to transpose rows to column in datatabse

I can't do hard PIVOTs, it's just one of those things that breaks my brain. You need @bluefeet for that.

Anyway, you can do it the oldskool way with case ... when and max() if the number of groups per col_id is finite (I'm assuming the Col_Group names are unknown, and there's a finite number:

select Col_Id, max(Group1),max(Price1),max(Group2),max(Price2),max(Group3),max(Price3),max(Group4),max(Price4),max(Group5),max(Price5)
from (
  select Col_Id, case when Col_NewGroup=1 then Col_Group else null end as Group1,
                 case when Col_NewGroup=1 then Col_Price else null end  as Price1,
                 case when Col_NewGroup=2 then Col_Group else null end as Group2,
                 case when Col_NewGroup=2 then Col_Price else null end as Price2,
                 case when Col_NewGroup=3 then Col_Group else null end as Group3,
                 case when Col_NewGroup=3 then Col_Price else null end as Price3,
                 case when Col_NewGroup=4 then Col_Group else null end as Group4,
                 case when Col_NewGroup=4 then Col_Price else null end as Price4,
                 case when Col_NewGroup=5 then Col_Group else null end as Group5,
                 case when Col_NewGroup=5 then Col_Price else null end as Price5
  from ( select Col_Id, Col_Price, Col_Group, row_number() over
       (
           partition by Col_Id order by Col_Group
       ) as Col_NewGroup
       from tbl1
     )
)
group by Col_Id;

Note that there's no way of ordering which groups end up where, as there's no ORDER given in your question.


The default length of a GROUP_CONCAT is 1024.

Please run this

mysql> SET group_concat_max_len = 1048576;

before the query. This will allow for a 1M string as the maximum GROUP_CONCAT.

If you want every session going forwards to have this, add this to my.cnf

[mysqld]
group_concat_max_len = 1M

and run this

mysql> SET GLOBAL group_concat_max_len = 1048576;

MySQL Restart not required

As for the query itself, avoid using newline characters. Change to this:

    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('CASE WHEN TX_STOP_NAME=''', TX_STOP_NAME, '''  THEN
    concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT
    else null  END AS `', concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT , '`'))
    INTO @sql
    FROM cmn_trip_statistics;
    SET @sql = CONCAT('select * from (SELECT s.FKYS_TRIP_ID,s.FKYS_REPORT_TYPE,sum(s.NO_HALT_DUR) as totalhalt, ', @sql);
    SET @sql = CONCAT(@sql,' FROM cmn_trip_statistics s group by');
    SET @sql = CONCAT(@sql,' s.FKYS_TRIP_ID,S.TX_STOP_NAME) T');
    SET @sql = CONCAT(@sql,' where FKYS_REPORT_TYPE=''HALT''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


How should JOINed columns be included in an index?

Including columns is done when you don't want to be able to search on them through the root and intermediate nodes of the index.

As a result only the leaf level pages have the data that is included.

Wether you put the join columns in the key columns depends on the filters used.

Filtered index

Based on your query, the best index in theory would be a filtered index (assuming no parameterization).

CREATE INDEX IX_PS_1 ON PS (PS_ID)
INCLUDE(PS_PER_ID_FIRST,PS_DATE_INSERTED )
WHERE PS_PER_ID_FIRST  = 2000
      AND PS_DATE_INSERTED >= '1/1/2019'
      AND PS_DATE_INSERTED < '1/1/2020';

CREATE INDEX IX_LIS_1 ON LIS (LIS_PS_ID)
INCLUDE(LIS_DATE_PUBLISHED)
WHERE LIS_DATE_PUBLISHED IS NOT NULL;

Depending on the data, a merge join (join that can use 2 sorted inputs) could be chosen.
This join will be best when comparing large datasets.
The stored index data matches the filters exactly.

If filtered indexes are a no go

DEPENDS ON YOUR DATA

Now, when comparing >= and < in your where clause, it will probably not matter whether PS_ID is included or not in terms of performance as it will not be able to search on it.

As such these indexes should do

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST,PS_DATE_INSERTED )
INCLUDE(PS_ID);

CREATE INDEX IX_LIS_1 ON LIS (LIS_DATE_PUBLISHED,LIS_PS_ID);

This gives the seeks on the filters, but a hash join instead of a merge join with my data.

Again, depending on your data & how much is filtered by the applied filters, these indexes could work too:

CREATE INDEX IX_PS_1_3 ON PS (PS_ID)
INCLUDE(PS_PER_ID_FIRST,PS_DATE_INSERTED);
CREATE INDEX IX_LIS_1_3 ON LIS (LIS_PS_ID)
INCLUDE(LIS_DATE_PUBLISHED);

With my data, nothing is filtered out and the optimizer knows to use a merge join with residual predicate on the filters.

This is an extreme example but shows the differences and why it depends™


Degree of parallelism and scheduler usage

Up to SQL Server 2000 yes; in SQL Server 2005 and later, no.
According to Craig Freedman's Parallel Query Execution Presentation, up to SQL Server 2000, any parallel worker thread could run on any CPU. That means there may be more active threads than DOP, so reducing DOP might not actually reduce CPU usage. To be clear, a single parallel query might indeed use more than x distinct schedulers if it had more than one parallel branch/zone in the execution plan.
The behaviour was changed from SQL Server 2005 onward so that the number of schedulers per query is limited to DOP. That means a single parallel query never uses more than DOP CPUs, and therefore reducing DOP does reduce CPU usage. This applies regardless of the number of parallel zones (branches) in the execution plan.
My understanding is that the pre-2005 behaviour was an implementation bug/oversight.

Note: The additional parallel worker threads are limited to DOP schedulers; e.g. a parallel query that runs at DOP 4 on an instance with 8 schedulers available would use 4 schedulers for the parallel workers. The query as a whole could use 5 schedulers: 1 provided by the session plus the 4 for the additional parallel workers.


Following to the Wikipedia page that you mentioned above (in the Database key section):

The NEWID function in Microsoft SQL Server version 4 Transact-SQL returns standard random version-4 UUIDs, while the NEWSEQUENTIALID function returns 128-bit identifiers similar to UUIDs which are committed to ascend in sequence until the next system reboot.


LOG_BACKUP wait type for Transaction log file of a simple recovery model database

Are you running SQL Server 2012?  Is your model database in SIMPLE recovery model?  Are you a bit behind with your patching?

This appears to have been the issue for a previous ask of this question, at SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

Fixed in KB2830400  which forms part of SQL Server 2012 CU7 / SQL Server 2012 SP1 CU 4


MySQL excessive tmp disk tables after Upgrade to 5.6

There are several reasons why that could be happening, and it is very difficult to point one specific cause -it could be because a change on the query optimizer, bad query statistics, parameters whose defaults changed from version to version, the changes on replication format, the data types you are handling, a change on the row format, etc.

The critical point here is to identify the differences on query execution: it would be nice if that would have been identified before the upgrade, but if you say that the master is not showing that behaviour, that may be enough. You need to discover which queries are creating those temporary tables. As you are using Percona Server 5.6, there are 2 main tools:

use the extended logging available on Percona and using pt-query-digest to identify and summary the offending queries, or
Use the performance_schema available since 5.6

Both tools can be used for identifying those bad queries. After that, you will be able to profile each query with the standard tools: EXPLAIN, the profiling metrics, the handler statistics, etc. In some cases, you may need to change the configuration, in other the queries, indexes or the structure itself of your database (for example, blobs usually cause problems because temporary tables using them cannot be on memory).

One thing that may actually be useful, independently of all of above is the replication format. If you are suffering from temporary table creation on the slave you are using STATEMENT or MIXED replication format (or your tables do not have primary keys). Row based replication, in some cases (when queries do not modify many records at the same time) may help with both query latency and avoiding slave drifting, so you should at least consider that.


minimum space is required on disk to restore a backup in SQl Server?

The backup file maintains metadata about the database and files that SQL Server uses during the restore process. Part of that metadata is a listing of data and log files that are in the database, and their respective sizes (as well as other information).

If you run RESTORE FILELISTONLY you can see the minimum size required for each data and log file in the database that will be restored. When SQL Server performs a normal restore it first checks these values and validates that the disk where each file is to be restore has sufficient space. If not, SQL Server throws an error indicating how much space is required and how much is available on the disk.


Is it possible to enlist a TSX to an MSX in a different domain?

The answer is yes, if

The two domains have sufficient trust relationships configured, or
You can figure out how to set it up using pass-through authentication (I believe this requires running SQL Agent under matching accounts).

Here is the Master Server setup screen:

In other words, there is no bypassing Windows authentication.

IMO, if there are no trusts between the domains, and there shouldn't be for business or security reasons, I think it would be a better idea to set up a new master server in the other domain, instead of circumventing security just for this.


How can "HierarchyID" type work when "CLR" is disabled?

The clr enabled server configuration option only controls whether user assemblies can be run by the SQL Server instance.

The hierarchyid, geometry and geography types are system CLR types. These are contained in system assemblies, so are available regardless of the clr enabled setting.

Similarly, other system features that rely on CLR integration, like FORMAT (SQL Server 2012 onward) are available even if clr enabled is off.

If you need to manipulate hierarchyid data in a custom CLR routine, you will need to enable user clr assemblies via this setting.

See also: Hierarchical Data (SQL Server)


Is there a way to delete duplicate from a cell value (value of a given row and column) using Postgresql

Assuming b is an array:

UPDATE mytab
SET b = (SELECT array_agg(DISTINCT x)
         FROM unnest(b) AS x);


IBM FlashSystem V9000 storage - should the os, mdf and ldf and tempdb be separated onto different volumes?

One reason for separate drives even with SAN is that a given disk or set of disks can only do so much I/O at once. Any of the things on the drive could saturate the I/O on the SAN by keeping OS, TempDB, logs, page file, and the user database on the same drive. Depending on the query and server configuration, TempDB, the page file, and the LDF could all be in contention for the I/O. By separating those, the I/O for those set of disks is exclusive to where they are allocated.

Another thing to consider is any one of those things could also use all the available space if sharing disk space and instead of one component shutting down, the whole server could be shut down.


MS-Access vba sql update where not working

Its happening because variable name is not correctly placed inside update code and as both column name and variable name are same so according to your current code you are updating all the records.

Try this

sqlcmd = "UPDATE 1_tbl_Memberships SET [1_tbl_Memberships].Member =
No WHERE [1_tbl_Memberships].GroupMembershipID = "& GroupMembershipID &""

http://msdn.microsoft.com/en-us/library/office/aa212163(v=office.11).aspx


SQL Server 2008: The login is from an untrusted domain and cannot be used with Windows authentication

If you are using Windows 7, you might have to "run as administrator".  This will allow the app to reuse your windows credentials and pass them to the SQL Server.  Otherwise, you will pass guest credentials to the SQL Server.


How to join to the same table multiple times?

The hierarchy_table has 5 columns that all reference the name_table, so you need 5 joins. It may be better to use LEFT joins instead of INNER, in case some of these columns are nullable and you still want the rows returned:

SELECT
    o.name  AS object,
    p1.name AS parent_1,
    p2.name AS parent_2,
    c1.name AS child_1,
    c2.name AS child_2
FROM
    hierarchy_table AS h
  LEFT JOIN name_table AS o   ON h.object_id   = o.name_id
  LEFT JOIN name_table AS p1  ON h.parent_id_1 = p1.name_id
  LEFT JOIN name_table AS p2  ON h.parent_id_2 = p2.name_id
  LEFT JOIN name_table AS c1  ON h.child_id_1  = c1.name_id
  LEFT JOIN name_table AS c2  ON h.child_id_2  = c2.name_id ;


Automated SQL backup on a timely fashion, & cleaup the database for the backed up data

Instead of rewriting your own solution, my suggestion is to use SQL Server Maintenance Solution - SQL Server Backup

Edit: Below script will help you once you deploy Ola's script and create required objects:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', -- put the databases you want to backup
@Directory = 'C:\Backup',      -- change the backup directory to suit your needs
@BackupType = 'FULL',          -- This will take FULL database backups
@Verify = 'Y',                 -- This will verify the backups
@Compress = 'Y',               -- Since you are using SQLServer2008R2, standard and enterprise both have backup compression available.
@CheckSum = 'Y',
@CleanupTime = 24              -- This will delete the backup older than 24 hrs !!


Upgrading the mysql server from 5.1 to 5.6?

I have a more direct way to upgrade from MySQL 5.1 to 5.6

The idea is to do the following:

Dump the mysql schema from MySQL 5.1 as pure SQL into a grants file

Use pt-show-grants
Emulate pt-show-grants

Dump the data from MySQL 5.1 without the mysql schema into a data file
Uninstall MySQL 5.1
Install MySQL 5.6
Load grants file into MySQL 5.6
Load data file into MySQL 5.6

CAPTURE DATA

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump ${MYSQL_CONN} --databases ${DBLIST} > MySQLData.sql

CAPTURE GRANTS

Here is my personal emulation of pt-show-grants to capture SQL Grants

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQL="${SQL} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}"|mysql ${MYSQL_CONN} -AN|sed 's/$/;/g' > MySQLGrants.sql

Here are my posts where I discussed these things before

Mar 26, 2015 : mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?
Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

GIVE IT A TRY !!!


Use the SHOW=Y option for imp

From the manual at http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2498

  When SHOW=y, the contents of the export dump file are listed to the display and not imported.
  The SQL statements contained in the export are displayed in the order in which Import will execute them.
  The SHOW parameter can be used only with the FULL=y, FROMUSER, TOUSER, or TABLES parameter.


Is the service account a domain account?  Sounds to me like the service account may in fact be an account that is local to the machine, not a domain account.

Try:

CREATE LOGIN [WINDOWS_MACHINE_NAME\ServiceAccount] FROM windows;

If this works, the account is not a domain account.


PostgreSQL - partial dump of large objects (pg_largeobject table)

I managed to resolve this issue as follow (2nd considered solution - revisited):

All in a while cicle, for every file:

select file_id , file_version, oid and put them in 3 variables
perform a [lo_export] of pg_largeobjects that require oid a path+filename to be defined. The filename will be the concatenation of the file_id and file_version.
during lo_import new OID will be generated. But using the filename defined above, I can perform the last stem:
update all the rows in the files table in order to make them point to the new imported pg_largeobject records and mantain the relation consistecy