Minimal permissions required to read index size in SQL Server

There are couple ways to achieve what you are after.

I will show you two, but there are other ways as well.

The first one is signing a procedure, and allowing other users to use it with specifying grant execute on object::yourprocedure only.

First of all you need a master key, which i assume you already have, and you can check it using sys.symmetric_keys DMV, and looking for record that has '##' as a prefix. If not simply create a DB master key

'CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@$$W0RD'

This is very importaint key, and make sure you back it up using this guide

Now you are going to create a certificate based on which you will create module signing user. Note it is only for singing modules and you cannot use it to test privileges etc. So first things first - Certificate :

CREATE CERTIFICATE SignModules
WITH SUBJECT = 'Certificate for signing modules'

Just like the master key, you need to back up this certificate as well. guide

Now we are creating a user with certificate which will have all the rights:

CREATE USER UnlockCertifiedProcedures FOR CERTIFICATE SignModules

There are two ways of providing necessary rights:

becoming a db_reader as exec sp_addrolemember 'db_datareader',UnlockCertifiedProcedures
giving appropriate rights GRANT VIEW DEFINITION TO UnlockCertifiedProcedures, GRANT VIEW DATABASE STATE TO  UnlockCertifiedProcedures

Once this is done, the next step is to actually sign this procedure, so whoever has rights to 'execute' the procedure will inherit rights from the certificate user.

ADD SIGNATURE TO Schema.YourProcedure
BY CERTIFICATE SignModules

To test:

create user TestUser WITHOUT LOGIN
grant execute on OBJECT::YourProcedure to TestUser
execute as user = 'TestUser'
exec Schema.YourProcedure

The second option is creating a user and executing procedure as that specified user.

CREATE USER AnotherTest WITHOUT LOGIN
GRANT VIEW DEFINITION TO AnotherTest
GRANT VIEW DATABASE STATE To AnotherTest
GRANT EXECUTE ON OBJECT::YourProcedure To AnotherTest

Create procedure executing as that user:

CREATE proc  Schema.YourProcedure WITH EXECUTE AS 'AnotherTest'
AS
--your code--

Now simply give execute rights on a procedure to a specified user :

GRANT EXECUTE ON OBJECT::YourProcedure To SomeUser

Like i said there are other ways, including roles but these two could get you a job done. Just remember you cannot track a user that executed procedure by specifying 'execute as'(login is possible tho). Also creating a certificate/database keys can be a headache when you`re migrating DB, or simply restoring it somewhere else.


Is there another/better way to create and maintain large amounts of procedures?

I'm trying some ways to make something that could work for you but MySQL has limitations as I see. I'm trying to make a Stored Procedure that you could read all the information of the procedures from a table.

Example:

The Stored Procedure:

    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
    IN eProcedureID int(11),
    IN eUser VARCHAR(50),
    IN ePass VARCHAR(50),
    IN eHost VARCHAR(50))
    sp_np:BEGIN
    /*  | Variable Declaration | */
        DECLARE vProcedureID int(11) DEFAULT 0;
        DECLARE vUser VARCHAR(50) DEFAULT '';
        DECLARE vPass VARCHAR(50) DEFAULT '';
        DECLARE vHost VARCHAR(50) DEFAULT '';
        DECLARE vReg INT(8) DEFAULT 0;
    /*  | Variable Asignation | */
        SET vProcedureID=eProcedureID;
        SET vUser       =eUser;
        SET vPass       =ePass;
        SET vHost       =eHost;
    /*  | WorkPlace |   */
    SET vReg=(SELECT COUNT(*) FROM PRUEBA.new_table WHERE new_table.id=vProcedureID);
    IF (vReg>0) THEN # If Exists EXECUTE
        SET @DROP_USER=CONCAT(" DROP USER ","'",vUser,"'@'",vHost,"';");
        SET @CREATE_USER=CONCAT("CREATE USER ","'",vUser,"'@'",vHost,"'"," IDENTIFIED BY '",vPass,"';");
        SELECT
            new_table.create,
            new_table.proname,
            new_table.prodb
        INTO @PROCEDURE_CREATION,@PRONAME,@PRODB
        FROM PRUEBA.new_table WHERE new_table.id=vProcedureID;
        SET @GPRIVILEGES=CONCAT(" GRANT EXECUTE ON PROCEDURE ",@PRODB,'.',@PRONAME," TO '",vUser,"'@'",vHost,"';");

        SELECT vProcedureID,vUser,vPass,vHost,@PRODB,@PRONAME,@DROP_USER,@CREATE_USER,@GPRIVILEGES;

        PREPARE DROPUSER FROM @DROP_USER; EXECUTE DROPUSER; DEALLOCATE PREPARE DROPUSER;
        PREPARE CREATE_USER FROM @CREATE_USER; EXECUTE CREATE_USER; DEALLOCATE PREPARE CREATE_USER;
        PREPARE PROCEDURE_CREATION FROM @PROCEDURE_CREATION; EXECUTE PROCEDURE_CREATION; DEALLOCATE PREPARE PROCEDURE_CREATION;
        PREPARE GPRIVILEGES FROM @GPRIVILEGES; EXECUTE GPRIVILEGES; DEALLOCATE PREPARE GPRIVILEGES;

        LEAVE sp_np;
    ELSE
        SELECT '01' AS sERROR,'ProcedureID doesnt exists.' AS sDESC_ERROR;
        LEAVE sp_np;
    END IF;
    END;

As MySQL documentation says:

  SQL syntax for prepared statements cannot be used within stored routines (procedures or functions), or triggers. This restriction is lifted as of MySQL 5.0.13 for stored procedures, but not for stored functions or triggers. However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.

And all you had to do it was:

    CALL PRUEBA.new_procedure(
    '0000000001', #ProcedureID int(11),
    'guestuser', #User VARCHAR(50),
    'Password', #Pass VARCHAR(50),
    'appserver'); #Host VARCHAR(50)

Hope the SP helps you to find one better way.


Normalizing data with multiple products to a single ID?

There is no redundancy. You have the FK back to the sales order, how many items were ordered, the per item charge and the total for the line. You are, however, missing something rather important. What item was ordered? The top line show an order of 10 somethings and the next line shows an order for 5 something elses.

Another feature you may want to add is a way to uniquely identify each order detail. There first three items are details for order number 1111. Beyond that, there is no way to differentiate one line from another. That is to say, there in no natural key.

Such detail tables create a differentiator in several ways. One way is to add an incremental value on each line. This can also be done in a couple of ways.

Give the first line inserted for each order a value of one and increment each additional line by one. The disadvantage of this is, when another line must be added later, the highest value so far must be read in to see where to restart incrementing.
Implement a global incremental value. This has the advantage it can usually be automatically generated by the system as a new record is inserted. Of course, line 1 of order 1111 may be given the value 1, line 1 of the order 1112 may be given the value 2, line 2 of order 1111 the value 3, line 1 of order 1113 the value 4 and so on. Essentially, the line value would be a semi-random value. This is fine as these serve as differentiators only. They are only required to be distinct, not grouped in any logical manner. When using this method, use the composite (SO_ID, Increment) as the key even though Increment will be unique on its own. The indexing will improve queries.

Another way is to use the missing field, the item part number or SKU or whatever. The advantage over a generated value is

an additional increment field need not be created.
you can't accidentally enter the same part number more than once. The disadvantage is you can't deliberately enter the same part number more than once -- or instance, if the customer later orders more of the same item. You would have to update the existing line for that item with the new quantity. However, this may be exactly the behavior you want.


A foreign key can reference a unique constraint, not necessarily only a primary key, so if you want to be a purist, include FinishedAt (and whatever else you need) in the parent table's unique constraint and use that to define the foreign key.


You can add a WHERE clause to the constraint, making it a partial one. It will be checked only for row that have deleted IS NULL. Rows that have deleted populated will be completely ignored by the constraint checks:

ALTER TABLE foo
    ADD CONSTRAINT no_overlapping_times_for_event_type EXCLUDE
    USING gist (event_type WITH =, tstzrange("from", "to", '[)') WITH &&)
    WHERE (deleted IS NULL) ;


Is it worth duplicating a table for different types of an entity when one should be unique?

Some people might wince at this, but if you have at least one column that distinguishes sale opportunities from customer prospects you could make this column nullable and create a unique index that includes the customer ID and this column.

Db2 allows only one NULL value in a column in a unique index (in other words, from the uniqueness point of view all NULL values are considered equal). Thus you will store a NULL in this column for "possible customer" and something unique for each "possible sale", and Db2 will ensure that only one "possible customer" (i.e. NULL) value is present for a given customer ID.


Relationship that are optionally more specific

Instead of:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

I would do this:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(EventID, LocationID) REFERENCES Locations(EventID, ID)

That would guarantee that "if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID".

That done, your approach makes sense.


SQL Server 2008, cpu 100% until recreate an index

My gut guess is that the CPU usage dropping after recreating the PK is coincidental, especially since you believe the index is not fragmented. My guess is there's nothing wrong with the index, per se. More likely, it's the lack of indexes to satisfy the particular queries / procs executing against that table.

What you need to do is monitor the server. This is 2008 so you have many tools available to you and you can go about this a couple different ways.

It sounds like this is happening frequently throughout the day, so observing a Profiler trace and sorting on CPU can be the quickest way to identify what queries / procs are hammering CPU at that particular moment. (SQL Trace is the server side version of the Profiler GUI - this might be a better option if you need to trace for an extended period of time, but that is a different conversation.)

Along the same lines and a bit more accurate would be to correlate a Perfmon trace with a Profiler trace and then you can see exactly what was running when your CPU spiked. (You can google that up for instructions.)

Another approach would be to look at the execution DMV's (see link below) and again sort descending on total_worker_time as that relates to CPU cost.

These approaches will narrow down the queries / procs you need to focus on and from there you need to look at the excecution plan and determine if you can add indexes, break up the query into smaller pieces, etc...

Here are some links to get you started:

https://www.sqlskills.com/blogs/glenn/category/dmv-queries/

http://whoisactive.com/


When creating a view how do I separate one column into two depending on a foreign key value?

you need to use CASE construction in the query SELECT list:

SELECT
   CASE
      WHEN aty.address_type_id IN (1,3) THEN a.Address1 ELSE NULL END as mail_address,
   CASE
      WHEN aty.address_type_id =2 THEN a.Address1 ELSE NULL END as physical_address
....
FROM ...


How to create an additional boolean column in a view whose value depends on a subquery?

The most important thing is that you don't need to assume that performance will get worse. SQL server can rewrite some subqueries to avoid the kind of row by row operation that I think you're assuming will happen. It's certainly possible that your subquery will slow down the view quite a bit but you can test this yourself. In fact, you can test this much more effectively than us because we have no idea about your table structures, indexes, statistics, size of the tables, etc.

Run queries that use the view with and without the bit column. Does the query with the new column get exponentially slower? Get an actual execution plan and analyze that. Run your queries after SET STATISTICS IO, TIME ON and see how IO, CPU time, and elapsed time change with different queries.

I'll give you one way to rewrite that query without a subquery. I'm assuming that [MasterID] is unique for [MasterTable].

Let's work through the logic of your subquery. One way to express it is you want to start with [MasterTable].[MasterID] and get all of the associated [DetailID] values from [DetailTable]. If there is at least one row in [ErrorTable] with a matching DetailID then the bit column has a value of 1. Otherwise it has a value of 0.

You can rewrite that to be a derived table. If the derived table has all of the distinct [MasterID] that have a corresponding entry in [ErrorTable] then you can do a LEFT OUTER JOIN to it. If the join column is not NULL then there was a match so your bit column has a value of 1. Otherwise it has a value of 0.

Here is one implementation:

SELECT
[MasterID],
[MasterColumnData],
[DetailID],
[DetailColumnData],
[SomeOtherID],
[SomeOtherColumnData],
CASE WHEN bool.[MasterID] IS NOT NULL THEN 1 ELSE 0 END
FROM [MasterTable]
LEFT OUTER JOIN [DetailTable] ON [MasterTable].[DetailID]= [DetailTable].[DetailID]
LEFT OUTER JOIN [SomeOtherTable] ON [MasterTable].[SomeOtherID]= [SomeOtherTable].[SomeOtherID]
LEFT OUTER JOIN
(
    SELECT DISTINCT dt.[MasterID]
    FROM [DetailTable] dt
    WHERE EXISTS
    (
        SELECT 1
        FROM [ErrorTable] et
        WHERE dt.DetailID = et.DetailID
    )
) bool ON [MasterTable].[MasterID] = bool.[MasterID];


SQL Server 2012 mirror setup - how to reduce transaction log files

Direct answer to your question

  What would be the best course of action to reduce the log file sizes?

In full recovery model only transaction log backup, perhaps frequent transaction log backup in your case, would truncate the logs and keep check on log file size. There is no other way to truncate logs. I strongly suggest you to read Why transaction log keeps growing and run out of space

  Should I go ahead and break the mirror, switch from Full -> Simple then back? Does anyone have experience with this kind of setup?

No please dont do that this will break the mirroring and you would have to re-establish it and it would be big hassle for you if you have got big database and mirror is located far from principal. Anyways if you do that and somehow configure mirroring after few days you would again face this issue because you actually did not solved it.


How should I config the my.ini(or my.cnf) file for mysql?

Percona has an extremely awesome wizard for initial server configuration files (I've used it on our own extremely busy production servers). It requires registration, but usage is free.


Why is there a difference in the character limit for VARCHAR2 in SQL and PL/SQL?

It is just a fact of life.  Different history, different beliefs, different goals.

Your might find this post on PL/SQL and its goals helpful in seeing some of the differences:
http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/03_types.htm

It amusingly starts with:

  Like--but oh how different! --William Wordsworth

There are ANSI Standard data types, but I believe that VARCHAR2 is an Oracle standard, not a part of ANSI.  It appears from history that Oracle's VARCHAR2 was designed to overcome the limitations of their earlier VARCHAR implementation.

Having said that, practically every SQL dialect has some unique datatype that is not accepted by all other SQL dialects. (I primarily work with MS SQL Server, but do at times need to absorb data from other systems.)

Fortunately there are a number of online resources to help you in mapping one datatype to another as you move between the products of multiple vendors.  Usually it is not too difficult.  Note: it is not only strings (such as CHAR,VARCHAR) that can have conflicting definitions.


Does a DDL in Oracle affect other transactions?

This article explains how DDL statements work and how they need exclusive table locks, which plays out nicely when there are transactions running against the table. To summarize:

If the table cannot be locked due to another transaction having a lock on the table

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

would be thrown. If the DDL manages to acquire the lock, obviously, any other transaction will block at the point where it needs a lock on the table, until the DDL finishes.

Additionally, since 11g, it is possible to enqueue DDL statements in FIFO order with other statements to get a lock on a table:

declare
begin
      -- We really want to drop that column and so wait for 1800 seconds.
      execute immediate 'alter session set ddl_lock_timeout=1800';
      execute immediate 'alter table t1 drop column n5 ';
exception
  when others then
     dbms_output.put_line(' No luck ');
     dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
end;
/

In older versions of Oracle, it could be a challenge to perform this task on a heavily used table.


All one has to do is run these queries:

MyISAM

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

InnoDB

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

These will recommend the right sizes for the MyISAM Key Cache for existing MyISAM and the InnoDB Buffer Pool for InnoDB data and indexes.

While running these SQL queries recommend the buffer sizes, the human factor must still kick in. We must plan data usage based on hardware and frequently accessed data. We must also ask: Of the amount of buffer recommended, how much of my working set will actually reside in memory?

If you configure MySQL to use 75% of RAM for InnoDB, even on a dedicated MySQL server, the OS will get a busy paging to disk. Just because MySQL can use huge numbers for buffer sizes does not mean it's OK to push the limits. Two have two basic storage engines to consider.

Use of commodity hardware merely demonstrates MySQL configurability with noticeable results. Unless you are using 32-bit RHEL (if you are, please stop using it right now, get plenty of coffee, go upgrade), raising buffer sizes in storage beasts can be well trusted.

For example, my employer has a client with 3 DB servers dedicated to MySQL only. Each has 192GB of RAM of which 162GB is the InnoDB Buffer Pool. The working set is actually 170GB. While there is a little swapping and paging going on, the DB performance is astounding. OS has about 30GB of RAM for itself (16GB of RAM disk for temp tables). I am sure that these servers are just little lizards compared to the komodo dragon-sized DB servers out there. MySQL can be up to the task if configured properly.

Keep in mind that InnoDB puts its own checks and balances in place because the current source code limits InnoDB log files to 4GB. This is the case because InnoDB was originally designed with commodity hardware in mind. In light of this, ACID transaction throughput could possibly bottleneck there regardless of the storage beast's hardware setup and the version of MySQL you choose to run.

CONCLUSION

It's OK to push limits on Big Beefy DB Servers. You should always do so with consideration to the OS in terms of Memory, Swap Space, Working Dataset, and number of transactions expected.


'Column reference is ambiguous' when upserting element into table

From the docs,

  conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table. SELECT privilege is required on any column in the target table where corresponding excluded columns are read.

So instead, try this per ypercubeᵀᴹ

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(accounts.affiliate_code, excluded.affiliate_code);


Several optimizations tips:

Left joins to inner joins

You can probably simplify the views a little bit by converting some LEFT JOINs into INNER JOINs. For instance, for the first view, you have a WHERE clause of c.tct_voided = 0, this will make the involved join behave like an inner join instead of a left one. If the row on c were not found, the c.tct_voided = 0 won't be true, and won't be returned. Explicitly changing the LEFT to INNER will help the database some. That might improve a little bit the execution time.

Some of the other LEFT JOINs might already by INNER JOINs just because of referential integrity, or the nature of your data. If you know for sure that there's always a corresponding row on your right table, change the LEFT to INNER.
Indexes on the expressions that are part of a join

If you have a join condition that is

(ht_counters_sales_items_b a
LEFT JOIN ht_counters_sales_b b
    ON a.tcsi_tcs_id = b.tcs_id)
LEFT JOIN ht_counters_trans_b c
    ON b.tcs_no = c.tct_ref_no

You are likely to gain a lot of speed if there is a KEY(tct_ref_no) on table ht_counters_trans_b. The same might apply to KEY(tcs_no) on table ht_counters_sales_b (if the LEFT JOIN were an INNER JOIN and MySQL would decide to process it right-to-left, instead of left-to-right).
Not forcing order of join execution

In some occasions (and this really needs to be tested, I don't know how good is MySQL at it), not putting any (...) on the JOINs gives the database more freedom to process them in the order it seems more appropriate. This is normally true for INNER JOINs, but shouldn't have a significant effect on LEFT JOINs.
WHERE clauses

SELECT * FROM v_summary WHERE MONTH(trx_date) = 7

If you want to look for all the data for July this year, you're more likely to get a much faster result if trx_date is indexed, and you write your query like:

SELECT * FROM v_summary WHERE trx_date BETWEEN '2017-07-01' and '2017-07-31'

This will allow the database to use the index. Having a function applied to the column will prevent it from using it, and force a sequential scan on all the results. And that's not what you want.


How to query status changes in time series?

It seems like you want something like

select *
  from (select person_id,
               rating_date,
               rating,
               lag(rating) over (partition by person_id
                                     order by rating_date) prior_rating
         from rating)
 where rating != prior_rating
    or prior_rating is null
 order by person_id, rating_date

which yields the following

SQL> ed
Wrote file afiedt.buf

  1  select person_id,
  2         rating_date,
  3         rating
  4    from (select person_id,
  5                 rating_date,
  6                 rating,
  7                 lag(rating) over (partition by person_id
  8                                       order by rating_date) prior_rating
  9           from rating)
 10   where rating != prior_rating
 11      or prior_rating is null
 12*  order by person_id, rating_date
SQL> /

 PERSON_ID RATING_DAT RATING
---------- ---------- --------
         1 2010-08-04 A3
         1 2010-08-14 A1
         2 2010-08-04 G2
         2 2010-08-14 G1
         2 2010-08-20 G4
         3 2010-08-04 G1
         3 2010-08-14 G3

7 rows selected.


How do I figure out what is consuming space in my Zenoss DB (mysql)

1.) Identify the largest tables

Find out the largest tables on your DB MySQL Server - run this query as DB "root":

SELECT CONCAT(table_schema, '_', table_name)                                          DB_DBtable,
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    data_size,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   index_size,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

2.) Find the reason/solution of the largest tables

It depends on the largest table. Probably the largest table will be zep_events*. In this case you should to tune your Zenoss events settings.

Note 1: You have a size problem with the InnoDB tables, so it wont be easy to release InnoDB space. You can delete rows from the table, but space won't be released. See Deleting & Reclaiming space from InnoDB table

Note 2: I recommend you to use http://www.zenoss.org/forum or IRC #zenoss for Zenoss specific questions


SQL Server : create table within stored procedure

You can add this before the select ... into ...:

If Exists(Select 1 From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'xyz' and TABLE_SCHEMA = 'test')
Begin
    EXEC sp_executesql N'Drop Table test.xyz'
End

It will drop the table if it already exists before the select into try to create it.


Suitable database software for small business

For something simple like what you have, I would highly recommend Google Docs or one of variants. It's basically a spreadsheet where everyone that you give permissions to can edit at the same time. Everyone can see what everyone else is doing, so there aren't many conflicts.

A SQL database would certainly support simultaneous accesses, but I think it's overkill for what you're looking for. You would need to build an application on top of the database just to use it. If none of you are willing to spend the time to write and maintain this application, I would not do it.


Percona 5.7.24 : How to fix disk slave storage full

#sql files are temp tables generated by ALTER and other maintenance commands.  You will need to find what that command is.  At that point, you will probably find that it is in a tight loop, either spawning off process after process, or crashing and restarting.

Other clues:  The table name (for many of them) includes "ib916".  The table is about 7GB in size (disk footprint).


How to pass a table type with an array field to a function in postgresql

This sort of thing gets complicated.  I am working on some related projects right now.  The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to represent literal values, so:

SELECT save_book('(179,the art of war,fiction,"{190,220}")'::book);

should work.  In essence a neat trick is creating a csv and enclosing in tuple or array identifiers.  The big issue is that you have to deal with escaping (doubling quotes at every level as needed).  So the following is exactly equivalent:

SELECT save_book('(179,"the art of war","fiction","{""190"",""220""}")'::book);

The second approach is to use a row constructor:

SELECT save_book(row(179,'the art of war','fiction', array[190,220])::book);

The first solution has the obvious advantage of being able to take advantage of existing programming frameworks for CSV generation and escaping.  The second is cleanest in SQL.  They can be mixed and matched.


MySQL group replication - how to find queries causing replication lag?

Turn on the slowlog on both machines.  What version of MySQL are you using?  Here are good settings to have for a relatively new version:
log_output = FILE
slow_query_log = ON
slow_query_log_file = (fullpath to some file)
long_query_time = 1
log_slow_admin_statements = ON
log_queries_not_using_indexes = OFF
log_slow_slave_statements
log_slow_extra = ON

More:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
A possible cause of what you are seeing:  An ALTER that takes some time and hits tableA is replicated.  Meanwhile, other things are happening (read or write) to tableA.  The above settings will probably catch the ALTER.
Another thing to look for (but hard to see) in the slowlog:  A bunch of queries ending at about the same second.  This usually means that one of them took a long time and was blocking the others (either directly or indirectly).
In any case, use pt-query-digest; its default sort is to put the "worst" queries first.  Then...

The blocker may be among the first few.
The first few are likely to be queries that you should work on improving -- better indexing, reformulation, etc.
Sometimes a "worst" query is a very fast query that was blocked by the blockage; that is it was a "victim", not a "villain".  Example:  UPDATE ... WHERE primary_key = 123 taking second(s) or even minute(s), not milliseconds.


Create GIN index in a schema with the pg_trgm extension

You can adjust the search_path before running the DDL statements:
set search_path = myschema,public;


Understanding datetime formats in SQL Server

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are:

yyyyMMdd
yyyyMMdd hh:nn:ss[.mmmmmmm]
yyyy-MM-ddThh:nn:ss[.mmmmmmm]
----------^ yes, that T is important!

Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:*

SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2013-11-13');

Result:

  Le paramètre de langue est passé à Français.
  Msg 242, Level 16, State 3, Line 2
  La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Just changing the language (which any of your user sessions can do) forced SQL Server to interpret that as YYYY-DD-MM instead of YYYY-MM-DD. Similar things can happen with setting like DATEFORMAT. But these settings are literally ignored when using the above two formats.

Always, always, always use one of the above two formats. If you are passing a variable as a string, stop doing that. If you can't, check to make sure it passes ISDATE() first. If you are letting people type any date string into a form field, stop doing that, too. Use a date-picker or calendar control and dictate the format of the string before you pass it to SQL Server. Well, depending on the language, just keep it as a datetime value and don't convert it to a string at all.

I can't reproduce your problem at all, btw...

Please read this post:

Bad Habits to Kick : Mis-handling date/range queries

There is an exception: SELECT CONVERT(DATE, 'yyyy-mm-dd'); will not break. But I err on the side of consistency rather than using a format only in the one place where I know it doesn't break, and having to use a safer format everywhere else.


Mysql compatibility between client and server of different versions

I do not know of any tables or charts that document this. In general, MySQL Client is backwards compatible, and MySQL support  indicates that 5.x clients can communicate with 4.x and 3.x servers.


select regexp_replace('name1-01.name2-02.1','(.*)-(\d+)\.(.*)-(.*)','\1.\2-\3.\4');

Assuming:

Everything before the first '-' can contain anything but '-'.
There's always a number after the first '-'
'name2' doesn't contain a '-'

Just guessed your requirements, basically. Might be wise for you to read a regular expression tutorial.


MySQL query performance of Date sorted data

Your query is presumably something like:

select a.*
from activities a
where user_is = 'abc' and time > somedate

or

where user_is = 'abc' and time > somedate and type = xyz

The best index for the first query is activities(user_is, time) and for the second activities(user_id, type, time).


You can use only columns from the joined tables or in your case concatenated  version of it
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;

INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;

CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;

INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO

first_name | last_name | address_1_zip | CombinedName
:--------- | :-------- | ------------: | :-----------
Maria      | Munoz     |         33122 | Munoz,Maria
John       | Glenn     |         10003 | Glenn,John

db<>fiddle here

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;

INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;
GO

CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;

INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;
GO

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON PATINDEX( CONCAT( LE.last_name,', ',LE.first_name,'%'),C.style) > 0
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO

first_name | last_name | address_1_zip | CombinedName
:--------- | :-------- | ------------: | :--------------
Maria      | Munoz     |         33122 | Munoz,Maria
John       | Glenn     |         10003 | Glenn,John
Melissa    | Johnson   |         90210 | Johnson,Melissa

db<>fiddle here


JOINing 3 tables with ORDER, LIMIT and WHERE

Using LEFT JOIN LATERAL

I think what you want is something like,

SELECT *
FROM rpo.organization_identifier_entries
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_address_entries
  ORDER BY updated_at DESC
  FETCH FIRST ROW ONLY
) AS oae
  USING (organization_id)
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_name_entries
  ORDER BY effective_to DESC NULLS FIRST
  FETCH FIRST ROW ONLY
) AS one
  USING (organization_id)
WHERE ipo = '********'
FETCH FIRST ROW ONLY;

Oh, and btw, never double-quote things in PostgreSQL unless you want to enable a very stupid capability to make identifiers caps-sensitive. This is a bad idea, unless that third party is also doing it and they have capital letters in their identifiers.


ALL permission is deprecated and maintained only for compatibility?

Always best to check the documentation.
GRANT

This option (ALL) is deprecated and maintained only for backward
compatibility. It does not grant all possible permissions. Granting
ALL is equivalent to granting the following permissions:
If the securable is a table, ALL means DELETE, INSERT, REFERENCES,
SELECT, and UPDATE.

So in theory you have executed these commands.
GRANT DELETE ON tablename to Users;
GRANT INSERT ON tablename to Users;
GRANT UPDATE ON tablename to Users;
GRANT SELECT ON tablename to Users;
GRANT REFERENCES ON tablename to Users;

Best to execute them separately in the future & avoid using deprecated statements.


I think You can perform update You need using 2 statements.

1) Update all records in decreasing order. Single-table UPDATE allows ordering.

UPDATE tbl
SET rank=rank+1
ORDER BY rank DESC;

2) Restore values for first 2 records.

UPDATE tbl
SET rank=rank-1
ORDER BY rank ASC LIMIT 2;


Why would writing the CTE to an actual table provide a performance improvement?

Even though they are estimated plans and not the actual ones, the difference to me looks like it is coming from joining the tables:
RATING_CONTRIB_LOSS,
RATING_CONTRIB_USR,
cmbgrp,
rating_revision_set,
and the Rating table from the ratings CTE.

Joining these tables spawn huge table spools (lazy spool), that  could be even bigger on the actual plan.

For every row in the top part of the nested loops operator, the bottom result set is scanned resulting in

3361 rows * 3359 in the first table spool --> Nested loops --> Top(1)

and

1541 Rows * 3359 in the second table spool --> Nested loops --> Top(1)

These table spools are not present in either of the two plans in the temp table queries.

What I am getting at is that the query part that inserts into the table is alike to the corresponding query part of the CTE. There are some differences but I think that the major cause of the slowdown is because of the table spools.

An example of a difference is that the full table scan on  RATING_CONTRIB_LOSS in the CTE plan is changed to a scan with a residual predicate in the temp table plan.

CTE

Temp table

And you could argue that the second part of the query that uses the temp table might access the RATING_CONTRIB_LOSS table again. And I would have to agree because it will be doing an expensive key lookup.

However, the second table spool in the CTE plan is also based on a nested loops join with theRATING_CONTRIB_LOSS table, which is not present in the temp table plan, and that is a big plus.

Conclusion

I do believe that the difference in execution time comes from the query using the temp table's result in such a way that costly operators are not used. More specifically the Table spools. I would need the actual plan to be 100% sure.

As a sidenote, rewriting CTE's into temp tables or just splitting up queries in general can in many cases give better execution times.

It is easier to guess how many apples are in a bag with apples and pears than guess how many apples are in a bag with apples, pears, mango's, oranges, peaches, .... (Yes the fruit = tables).

I would also use actual #temp tables to store your intermediary results in.

Comparison CTE & Temp table insert query parts

Plan with CTE part 1

Plan with CTE part 2

Plan with temp table part 1

Difference on RATING_CONTRIB_LOSS & Hash match join <> Nested Loops Join. Rest is very much alike, Same operations (2x) on Rating table


You could always do something like this:

Create a users table to store all of your user info:

create table users
(
  id int,
  user_name varchar(50)
);

insert into users values
(1, 'John'),
(2, 'Jill'),
(3, 'Bob'),
(4, 'Sam');

You state that you might need to apply this type of tracking for multiple applications. So you can create an application table to store the app name and any details.

create table applications
(
  id int,
  app_name varchar(50)
);

insert into applications values
(1, 'App 1'),
(2, 'App 2'),
(3, 'App 3');

Next create a categories table to store the info on each of the categories that you want to track by the users.  This table will include a column that will contain the application id. This table will contain a foreign key relationship to the applications table. This allows you to have some flexibility in having the same category in multiple apps.

create table categories
(
  id int,
  cat_name varchar(100),
  a_id int
);

insert into categories values
(1, 'Cat 1', 1),
(2, 'Cat 2', 1),
(3, 'Cat 3', 2),
(4, 'Cat 4', 3);

Finally, create the users_categories table which will contain the stats that you want to track, including user, category and then the action_date (the date the event took place).

create table users_categories
(
  u_id int,
  c_id int,
  action_date datetime
);
insert into users_categories values
(1, 1, current_timestamp()),
(2, 3, '2012-01-15 10:15'),
(3, 4, '2012-08-24 12:48'),
(1, 2, '2012-06-08 08:50');

Then to query the data, you would use something similar to this:

select *
from users u
left join users_categories uc
  on u.id = uc.u_id
left join categories c
  on uc.c_id = c.id
left join applications a
  on c.a_id = a.id

And the results would contain (See SQL Fiddle with Demo):

ID | USER_NAME | U_ID   | C_ID   | ACTION_DATE           | CAT_NAME | A_ID    | APP_NAME
------------------------------------------------------------------------------------------
1  | John      | 1      | 1      | '2012-09-28 06:47:53' | Cat 1    | 1       | App 1
1  | John      | 1      | 2      | '2012-06-08 08:50:00' | Cat 2    | 1       | App 1
2  | Jill      | 2      | 3      | '2012-01-15 10:15:00' | Cat 3    | 2       | App 2
3  | Bob       | 3      | 4      | '2012-08-24 12:48:00' | Cat 4    | 3       | App 3
4  | Sam       | (null) | (null) | (null)                | (null)   | (null)  | (null)

A few other suggestions, you could technically remove the a_id from the categories table and create a join table between categories and applications, similar to this:

create table categories_applications
(
  c_id int,
  a_id int
);
insert into categories_applications values
(1, 1),
(2, 3),
(3, 1),
(1, 2);

This would lead to one additional join in your queries, but the results would be the same:

select *
from users u
left join users_categories uc
  on u.id = uc.u_id
left join categories c
  on uc.c_id = c.id
left join categories_applications ca
  on c.id = ca.c_id
left join applications a
  on ca.a_id = a.id


How to remove the second repetition of combination in mySQL

Try this

DELETE n1
    FROM tablename n1, tablename n2
    WHERE n1.id > n2.id
      AND n1.exam = n2.exam
      AND n1.name = n2.name
      AND n1.subject = n2.subject


The SQL Server (MSSQLSERVER) service terminated unexpectedly. Event ID: 7034

Error Event Id : 7034

Service Control Manager (SCM) stops services and driver services. It also reports when services terminate unexpectedly or fail to restart after it takes corrective action.

How to Resolve

Change the recovery actions

To resolve this issue, change the recovery actions that the Service Control Manager (SCM) will take when a service fails.
To perform this procedure, you must have membership in Administrators, or you must have been delegated the appropriate authority.

To change the recovery actions for a service:

Open the Services snap-in by clicking the Start button, Control
Panel, and Administrative Tools, then double-clicking Services.
Note: For Windows Vista, use the Classic View display option in
Control Panel to see the Administration Tools.
In the details pane of the Services snap-in, right-click on the name
    of the service you want to stop and select Properties.
Click the Recovery tab and specify the recovery actions for the
    service (for example, restart the service or the computer, take no
    action, or run a program).

For your further ref Here and Here


PS_CUSTOMFIELDS.getStudentsCF returns varchar2.

Convert to date, then convert back to the expected format.

select to_char(to_date(PS_CUSTOMFIELDS.getStudentsCF(s.id,'CA_LastIEPDate'), 'DD-MON-YYYY'), 'MM/DD/YYYY') AnnDate ...


What happens when the index size of my Mysql instance exceeds the innodb_buffer_pool_size?

It isn't the index size... it's the data and indexes.  The InnoDB Buffer Pool:

...holds cached InnoDB data for both tables and indexes
— http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_buffer_pool
The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.
— http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html

Conversely, the smaller the buffer pool, the less InnoDB acts like an in-memory database.
In an ideal world, your buffer pool is larger than data size plus index size plus a little bit of overhead, because InnoDB does nothing to a page from a tablespace without storing that page in the buffer pool, it follows that when the pool is full, something less recently used gets evicted from the pool.  Practicality, of course, often necessitates something smaller than the ideal, and the key to appropriate sizing is the working data set.  The more infrequently-accessed data you have on your server, the fewer potential performance issues you should expect from an undersized pool.
There is, of course, no formula for this... familiarity with your unique working data set is the only "tool" you can use... but SHOW STATUS LIKE '%buffer%'; will give you the counters.
Note that Innodb_buffer_pool_pages_free is only an indication of completely unused space in the pool... it's not an emergency situation or even intensely meaningful if this value is very low or 0, because there is no process to spontaneously evict pages from the pool even when they are old and idle... but as long as they aren't "dirty" then replacing one page with something else is essentially no different in cost than loading a page into free space.  Zero free only means that at some point since the server was started, the total data and indexes read into the pool exceeded the size of the pool and there have not been enough deletions or tables dropped since the last peak to free up any pages.  Note also that running a backup will tend to fill an undersized pool, relatively harmlessly, depending on what's been loaded into the pool by the prior workload.


import geonames allCountries.txt into MySQL 5.7 using LOAD INFILE - ERROR 1300 (HY000)

(Caution: TMI coming...)

There are definitely utf8mb4 characters in that data.

The LOAD DATA needed CHARACTER SET utf8mb4.

The alternatenames column has 46 cases of 4-byte UTF-8 codes.  Here's one:

mysql> SELECT * FROM allcountries WHERE geonameid = 6962506\G
*************************** 1. row ***************************
        geonameid: 6962506
             name: Shidanbian
        asciiname: Shidanbian
   alternatenames: Shidanbian,shi dan pian,石旦
         latitude: 31.08403
        longitude: 107.27187
    feature_class: P
     feature_code: PPL
     country_code: CN
              cc2:
      admin1_code: 32
      admin2_code:
      admin3_code:
      admin4_code:
       population: 0
        elevation: 0
              dem: 378
         timezone: Asia/Shanghai
modification_date: 2014-10-02

I found such rows with WHERE HEX(alternatenames) REGEXP '^(..)*F0'

The last character is HEX F0A18E9A, which needs utf8mb4.

Another example is id 281184, where  is the "Gothic" writing for Jerusalem.

FYI, here is the table and the LOAD DATA I used:

CREATE TABLE AllCountries (
    geonameid MEDIUMINT UNSIGNED NOT NULL  COMMENT "integer id of record in geonames database",
    name     VARCHAR(200) CHARACTER SET utf8mb4  COMMENT "name of geographical point (utf8) varchar(200)",
    asciiname  VARCHAR(200)    COMMENT "name of geographical point in plain ascii characters, varchar(200)",
    alternatenames TEXT CHARACTER SET utf8mb4   COMMENT "alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000)",
    latitude   DECIMAL(7,5)    COMMENT "latitude in decimal degrees (wgs84)",
    longitude  DECIMAL(8,5)    COMMENT "longitude in decimal degrees (wgs84)",
    feature_class CHAR(1)      COMMENT "see http://www.geonames.org/export/codes.html, char(1)",
    feature_code  VARCHAR(10)  COMMENT "see http://www.geonames.org/export/codes.html, varchar(10)",
    country_code  CHAR(2)      COMMENT "ISO-3166 2-letter country code, 2 characters",
    cc2          VARCHAR(200)  COMMENT "alternate country codes, comma separated, ISO-3166 2-letter country code, 200 characters",
    admin1_code  VARCHAR(20)   COMMENT "fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)",
    admin2_code  VARCHAR(80)   COMMENT "code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) ",
    admin3_code  VARCHAR(20)   COMMENT "code for third level administrative division, varchar(20)",
    admin4_code  VARCHAR(20)   COMMENT "code for fourth level administrative division, varchar(20)",
    population  DECIMAL(11,0)  COMMENT "bigint (8 byte int) ",
    elevation  SMALLINT UNSIGNED  COMMENT "in meters, integer",
    dem        SMALLINT UNSIGNED  COMMENT "digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.",
    timezone   VARCHAR(40)     COMMENT "the timezone id (see file timeZone.txt) varchar(40)",
    modification_date DATE     COMMENT "date of last modification in yyyy-MM-dd format",
    PRIMARY KEY(geonameid)
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii COMMENT 'http://download.geonames.org/export/dump/';

LOAD DATA INFILE 'C:/htdocs/misc/allcountries.csv'
    INTO TABLE allcountries
    CHARACTER SET utf8mb4
    FIELDS TERMINATED BY "\t"
    LINES TERMINATED BY '\n';

Note the careful picking of ascii vs utf8mb4 for charset.

A SHOW WARNINGS indicated that that is still not 'correct':

Incorrect integer value: '' for column 'elevation' at row ...

That can be solved via use of an @variable, and a SET in the LOAD DATA.

You were stopped at least by Afghanistan in Gothic:  .  That's almost readable!

(That was fun.)


What could be preventing updates and inserts to a table without anything visibly blocking the process?

I will prefer to track blocking with any of 2 below rather than SP_who2 or Activity monitor:

Use Sp_whoisactive which is a procedure written by Adam Machanic. Once created , you can gather the data to analyse the blocking using:

a. Create one destination table

b.Create Your Loop to Periodically Log Data

c.Set Up Your Query to Look at the Results

On how to get these details captured, read here.

You can use SQL Server Blocked Process Report Viewer written by Michael.J swart which apart from finding help you in analyzing the blocking root caused in very helpful manner.

You can read the article Okay, You’ve Found Blocking, Now What? to get more detailed info.

Hope these 2 will help as they have been doing us for quite long.


Is it possible to change the Access data source to SQL Server?

Yes.

When you "upsize", Access creates "linked tables".
The queries and forms on top of this don't know any difference

Some links:

https://stackoverflow.com/q/184406/27535 (SO)
http://support.microsoft.com/kb/2279654/en-us (MS KB)

Edit:

You'll need to deploy a new mdb/accdb to each client, along with a DSN (Control Panel, Admin tools, "Data Sources (ODBC)")


SQL Server 2008 Express shouldn't have a SQL Server Agent, so I'm assuming that the 2014 Agent is what was uninstalled.

I would run a repair from the SQL Server 2014 install media, not the arcserv media.  It's a good idea to make a backup of all of the SQL Server databases in the 2014 instance before doing this.


Tested it out myself and yes the stats are counted from an encrypted stored procedure if anyone else is looking for the answer.


Will replication commands build up if the distribution agent is stopped?

Yes, the transactional replication data will build up while waiting for distribution to start again.  Of course, if not tracked it is possible that a long delayed set of transactional replication data can fill the log space that you have available, thus causing you problems.

Replication is hostage to the resources available, including the log space since the replication data is read from the transaction log.

For what it is worth, we once kept a replication open for a couple of days until a network problem could be resolved.  (Plenty of log space at that time.)

If the replication process fails (for any reason) that would cause loss of data then you need to redo the steps to establish the replication landscape.


Detect data spikes (loops) in MySQL database and ignore these

Acoording to your pattern, you could do something like this (change your column names and url pattern accordingly):

mysql> SET @max_redirects := 10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) as `# redirects`, ip, min(url) as url,
              time_event - INTERVAL SECOND(time_event) SECOND AS minute
       FROM log WHERE url like '/login?redirect=%'
       GROUP BY ip, minute
       HAVING `# redirects` > @max_redirects;
+-------------+-----------------+----------------------+---------------------+
| # redirects | ip              | url                  | minute              |
+-------------+-----------------+----------------------+---------------------+
|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |
|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |
|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |
+-------------+-----------------+----------------------+---------------------+
3 rows in set (0.01 sec)

Expect this to be slow on a tall table, so add an index on your timestamp column and filter by date to avoid huge sorts.


In your blood_types table,

CREATE TABLE blood_types
(
 blo_id serial NOT NULL,
 blo_name character varying(10),
 CONSTRAINT blood_types_pkey PRIMARY KEY (blo_id)
)

You correctly have blo_id as a PRIMARY KEY.

And then in the persons table, have the per_blood_type integer as a FOREIGN KEY referencing blo_id in table blood_types (as you appear to have - can't check exact syntax - don't have running PostgreSQL system at the minute). The way you have have it appears to be the correct way - blood type depends on the person and only the person - not the other way round.


How can I pivot a table then join multiple tables and change values based on information between tables

Nevermind, I solved it myself using a short series of joins from the pivot fields:

SELECT
    I.ProductID,
    I.ProductName,
    A.ID as Category,
    B.ID as ProductType,
    C.ID as Sale,
    D.ID as [Group]
FROM InventoryProperty
PIVOT
    (
    MAX(PropertyValue)
    FOR PropertyID IN ([592],[595],[598],[600])
    ) as P
INNER JOIN Inventory as I on P.ProductID = I.ProductID
INNER JOIN PropertyChoice as A on P.[592] = A.ID
INNER JOIN PropertyChoice as B on P.[595] = B.ID
INNER JOIN PropertyChoice as D on P.[598] = C.ID
INNER JOIN PropertyChoice as E on P.[600] = D.ID


If dbo.DatabaseIntegrityCheck fails, what happens, i.e. how am I notified?

The job will continue. And it will fail (in the end), so it is up to you to get notified when a (this) job fails. For instance send an email on job failure.


Writing data to SSISDB.Catalog.Executions

The question asks about inserting into catalog.executions. The entire catalog schema is just views over the internal schema tables which have security role checks in place.

If you look at the definition for catalog.create_execution after doing some permission checks, it will use an CLR proc to insert into internal.operations to generate an ID and then the actual insertion into internal.executions

-- Parameters
DECLARE
    @folder_name nvarchar(128) = (SELECT TOP 1 F.name FROM internal.folders AS F ORDER BY 1)
,   @project_name nvarchar(128) = (SELECT TOP 1 P.name FROM internal.folders AS F INNER JOIN internal.projects AS P ON P.folder_id = F.folder_id ORDER BY F.name)
,   @package_name nvarchar(260) = (SELECT TOP 1 PKG.name FROM internal.folders AS F INNER JOIN internal.projects AS P ON P.folder_id = F.folder_id INNER JOIN internal.packages AS PKG ON PKG.project_id = P.project_id ORDER BY F.name)
,   @reference_id bigint = NULL
,   @use32bitruntime bit = 0
,   @execution_id bigint;

DECLARE @caller_name internal.adt_sname = (SELECT USER_NAME());
DECLARE @caller_sid internal.adt_sid = (SELECT USER_ID());
DECLARE @created_time datetimeoffset;
DECLARE @return_value int;
DECLARE @operation_id bigint;
DECLARE @result bit;
DECLARE @environment_id bigint;
DECLARE @environment_found bit;
DECLARE @environment_name nvarchar(128)
DECLARE @environment_folder_name nvarchar(128)
DECLARE @reference_type char(1)
DECLARE @version_id bigint

SET @created_time = SYSDATETIMEOFFSET();
SET @operation_id = -1;

INSERT INTO
internal.operations
(
    operation_type
,   created_time
,   object_type
,   object_id
,   object_name
,   status
,   start_time
,   end_time
,   caller_sid
,   caller_name
,   process_id
,   stopped_by_sid
,   stopped_by_name
,   operation_guid
,   server_name
,   machine_name
)
VALUES
(
    200
,   SYSDATETIMEOFFSET()  -- created_time - datetimeoffset
,   20
,   0
,   N'Demo'  -- object_name - nvarchar(260)
,   1  -- status - int
,   SYSDATETIMEOFFSET()  -- start_time - datetimeoffset
,   SYSDATETIMEOFFSET()  -- end_time - datetimeoffset
,   @caller_sid
,   @caller_name
,   0  -- process_id - int
,   NULL  -- stopped_by_sid - varbinary(85)
,   N''  -- stopped_by_name - nvarchar(128)
,   NULL  -- operation_guid - uniqueidentifier
,   @@SERVERNAME
,   @@SERVERNAME
)

SELECT
    @operation_id = SCOPE_IDENTITY();

INSERT into internal.executions
(
    execution_id
,   folder_name
,   project_name
,   package_name
,   reference_id
,   reference_type
,   environment_folder_name
,   environment_name
,   project_lsn
,   executed_as_sid
,   executed_as_name
,   use32bitruntime
)
VALUES
(
    @operation_id
,   @folder_name
,   @project_name
,   @package_name
,   @reference_id
,   @reference_type
,   @environment_folder_name
,   @environment_name
,   @version_id
,   @caller_sid
,   @caller_name
,   @use32bitruntime
);

-- Look at what I've done
SELECT @operation_id AS OperationId;


Are there any benefits in certain ordering of columns when defining indexes

Yes, the benefit comes when you want to query on a part of the index.  If you put the part-used predicates first the index can be used for queries that involve those predicates but not all of the columns in the index.

Also, unless you have other requirements it can help to put the most selective predicates first, as this can trim down index seek operations faster.

In your case IDX_2 is not necessarily redundant depending on the nature of the queries on the table.  However, it may not be necessary to include all of the columns.  If, for example, you do a lot of queries by location and date then IDX_2 may be useful to help resolve those queries as IDX_1 is not in the right order to be useful for that.  You might, however, find that item is redundant on IDX_2.

From 9i, Oracle introduced a 'skip scan' operator where trailing index columns can be queried more efficiently, which can reduce the need for supplementary indexes of this sort.

In a more specific case, if you are querying item by location and date and don't need any other columns then the query could be completely resolved though the index without having to read anything from the table.  You can also build covering indexes that have non-indexed columns attached.  If all of the needed columns can be resolved from the covering index the query doesn't need to touch the main table at all.

Finally, in answer to your last question: If you have a set of regularly used queries that are using up a lot of resources and could be tuned using an index then it's certainly worth considering.  However, maintaining indexes comes with an overhead on inserts, so you will have to trade query performance off against the overhead that the indexes place on insert or update operations.


Cannot run my template in .dbt extension in Oracle 12c DBCA

For anyone facing the same problem: the reason of the error "The template data is not in the expected format" may be different interpretation of negative values between the different versions of DBCA.

Deletion of the following tags with negative values in the Testing.dbt attached by OP does the trick:

initSize
blockSize
increment
incrementPercent
minExtendsSize
minExtends
maxExtends
maxSize
uniAllocSize
id


You can choose a WAL segment size with initdb when you create a new cluster. So you would have to dump and restore the data, which means down time. Jeff Janes' answer explains that this can also be done with pg_resetwal.

The only disadvantage of a larger WAL segment size I can think of is that the amount of WAL you lose when the file system with pg_wal on it is lost will increase. But that is a small problem and can be worked around with pg_receivewal.

I cannot think of a reason to change max_wal_size or min_wal_size because of a different WAL segment size, but perhaps a value of wal_buffers that matches the segment size would be beneficial.


You're lacking the -E flag for identity columns, which allows you to preserve the values instead of re-seeding them on the import.

bcp DocControl.dbo.Documents in Documents.dat -n -E -S SERVER\SQLEXPRESS -T

From MSDN,

  [-E] Specifies that identity value or values in the imported data file
  are to be used for the identity column. If -E is not given, the
  identity values for this column in the data file being imported are
  ignored, and SQL Server automatically assigns unique values based on
  the seed and increment values specified during table creation.

  If the
  data file does not contain values for the identity column in the table
  or view, use a format file to specify that the identity column in the
  table or view should be skipped when importing data; SQL Server
  automatically assigns unique values for the column.


Get maximum number with combination of columns

If I'm understanding you correctly then the following should give you what you want.

  create table xt (name NVARCHAR(50) NULL,
  city nvarchar(2) NULL,
  state nvarchar(2) NULL,
  country nvarchar(3) NULL)

  INSERT into xt (name, city, state, country)
  values(N'smith', N'ny', N'ny', N'usa'),
  (N'john', N'ny', N'ny', N'usa'),
  (N'john', N'nj', N'ny', N'usa')

SELECT name,
       city,
       state,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY (SELECT 0)) AS RN
  FROM xt
   GROUP BY name, city, state
   order by rn

drop table xt

This should provide the following output

name    city    state   RN
john    nj  ny  1
smith   ny  ny  1
john    ny  ny  2


Finding the application behind ".net sqlclient data provider"

sp_who2 (MSDN) is always a good start, and you can query the sys.processes table or sys.dm_exec_connections (MSDN) DMV.

Alternatively something like Adam Machanic's sp_WhoIsActive can help find problem processes and queries.

If you can't find what you're looking for directly in SQL Server, you'll be able to find things like the hostname/IP address and loginame that will help you track down the culprit.

Sample code for querying DMV/sys.processes (you'll have to chop it about if you want to do more):

Select spid,hostname,hostprocess,program_name,nt_username, blocked, waittime, waittype, loginame,cmd,spid,waittype,waittime,lastwaittype,cpu,physical_io,memusage,login_time,last_batch,open_tran,status,net_address, t.text
from sys.sysprocesses sp
--JOIN sys.dm_exec_connections con ON con.session_id = sp.sid
CROSS APPLY( select text from sys.dm_exec_sql_text(sp.sql_handle))t
--where hostname
order by sp.spid


When is a row added to DBA_HIST_SQLSTAT?

Oracle collects SQL statistics for each SQL execution plan/sql_id (plan_hash_value), rather than just for a specific sql_id.

To verify that this is indeed the case, this query should return 0 rows:

select
  count(*),
  snap_id,
  sql_id,
  plan_hash_value
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id,
  plan_hash_value
having
  count(*) > 1
order by
  count(*) desc;


N'Șc' considered duplicate key of N'C' using Latin1_General_CI_AS collation

The problem is that the old SQL Server Collations (i.e. those with names starting with SQL_) and the first two versions of the Windows Collations (the 80 series which came with SQL Server 2000 and do not have a version number in the name, and the 90 series which came with SQL Server 2005) are missing the sort weights for a great many characters. This was mostly corrected starting with the 100 series Collations that came with SQL Server 2008.

As you can see in the below examples, the Ș character matches an empty string when using non-binary, version 80 or 90 Collations (and SQL Server Collations) since they both have the same sort weight: 0. Nothing. Nada. This means that when comparing N'Șc' with N'C' (using pre-series 100 Collations), you are really comparing N'c' with N'C' (test #1):

SELECT 1 WHERE N'Șc' = N'C' COLLATE Latin1_General_CS_AS;
-- no result (due to "c" and "C" being different case)

SELECT 2 WHERE N'Ș' = N'' COLLATE SQL_Latin1_General_CP1_CI_AS;
SELECT 3 WHERE N'Ș' = N'' COLLATE Latin1_General_CI_AS;

SELECT 4 WHERE N'Ș' = N'' COLLATE Latin1_General_BIN2;
-- no result (due to "Ș" still being a code point and empty string has no code points)

SELECT 5 WHERE N'Ș' = N'' COLLATE Latin1_General_100_CI_AS;
-- no result (due to "Ș" finally having a sort weight in 100 series Collations)

SELECT 6 WHERE N'Ș' = N'' COLLATE Chinese_PRC_CI_AI;
SELECT 7 WHERE N'Ș' = N'' COLLATE Chinese_PRC_90_CI_AI;

SELECT 8 WHERE N'Ș' = N'' COLLATE Indic_General_90_CI_AI;
SELECT 9 WHERE N'Ș' = N'' COLLATE Indic_General_100_CI_AI;
-- no result (due to "Ș" finally having a sort weight in 100 series Collations)

So, unfortunately you will need to drop the PK, alter the column to have a 100-level Collation (e.g. Latin1_General_100_CI_AS_SC), and then recreate the PK. Please note that the difference in that suggested Collation from the current Collation is both the 100 and the _SC at the end, which allows it to properly handle supplementary characters.

This does not mean that you need to:

change the Collations of other tables (unless they have the same setup of NVARCHAR in the PK)
change the Database's default Collation. The main issue with not changing the DB's Collation is that there will be a behavior difference between doing table.column = N'Ș' and @variable = N'Ș' since variables and string literals use the Database's default Collation.

For more examples of this behavior, please see the "Supplementary Characters" section of the following blog post of mine:

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Identifiers, Part 3 of 2 (Delimited Identifiers)


The backup of the file or filegroup "fileStream" is not permitted, because it is not online

If the file is still present, you should be able to do this:

RESTORE DATABASE YourDB FILEGROUP=YourFG WITH RECOVERY;

If the file is no longer present (or present but not eligible to recover the data), you're in for a rough weekend. Depending on the complexity of the database, I would probably start making a backup plan (no pun intended):

create a new database
use the "Generate scripts" option against the old database to script the objects and data (or use a 3rd party tool and do a schema/data compare)
make sure all database settings, logins etc. are mapped the same to the new database (this can get complex if you're using TDE, certificates, service broker, etc.)
rename the old database
rename the new database

(You could also skip 4. and 5. if you can adjust your applications to point to the new copy of the database.)


SQL Server 2008 R2 – Track Database Growth (Production Database)

Refer the link which details as creating a table, stored procedure and a SQL Agent job to execute that sp and save details in that table everyday

http://sqljourney.wordpress.com/2013/02/13/sql-server-track-database-size-growth-trend/


Is two separate index equals to a composite index?

No. Two separate index is not the same as a composite. Although MySQL now has index merge other than analytical queries it's not very efficient.
In your case the best to have the unique key and a single column index.
Start the composite with the higher cardinality column. If pid has more distinct values than it's good as it is. If cid has more than I would suggest to use UNIQUE KEY (cid, pid). Also create a separate index which is only the column that is the second in the unique key.
For example
UNIQUE KEY (cid, pid), KEY (pid)
or
UNIQUE KEY (pid, cid), KEY (cid)
This way:

where pid=$pid and cid=$cid [and deleted = 1] will use the UNIQUE KEY

One of these will use the composite unique key the other will use the single column index.

where pid=$pid [and deleted = 1];
where cid=$pid [and deleted = 1];

 Alternative solution
You can make the composite index the PRIMARY KEY and have a single key on the other column:
Pros:

Very fast lookup on PRIMARY KEY
No need for an extra ID
Rows will naturally be clustered around the first column of the PK:

Since you have queries where you only have one of the column cid = X or pid = X these rows will be close(r) to each other so InnoDB needs to read less pages. Which can significantly speed up multiple rows resulting queries.
Cons:

Every index will have the PK concatenated to it so if you have a lot of secondary indexes this can consume some space.
If neither pid or cid are in incrementing order your INSERT throughput will suffer


I'd recommend storing everything in Planck Lengths.  That way you get the most accuracy possible.

The Planck length, is the unit of length approximately 1.6 * 10^-35 metres. It is in the system of units known as Planck units. The Planck length is deemed "natural" because it can be defined from three fundamental physical constants: the speed of light, Planck's constant, and the gravitational constant. Max Planck was the first to propose the Planck length, a base unit in a system of measurement he called "natural units". Although quantum mechanics and general relativity were unknown when Planck proposed his natural units, it later became clear that at a distance equal to the Planck length, gravity begins to display quantum effects, whose understanding would require a theory of quantum gravity.

Meter lengths are an easy conversion of "x" meters divided by 1.6e+35.

Of course, you going to need a fairly large data type to store your measurements.

All kidding aside, why not store the unit-of-measurement with the actual measurement (in a separate column of course, probably as a foreign key).  This way when you are displaying the measurement you do not need to convert it at all.  Simply display the measurement, along with the unit of measure.  The only time you'll need to convert the measurement is if you want to compare it to other measurements in a different unit.


Best architecture solution for heavy data mining system, that can run on cloud

The question is quite open to discussion with such few details. What kind of calculations are performed with what density? What kind of an application server are you using? What amount and what kind of data comes in and goes out? What's the acceptable latency between the application server and the database / clients and application server? Thus, are there any requirements to keep the application server in-house / will it need to be clustered? Will there be any business logic implemented in the database as stored procedures and stored functions? If so, will it be in the native SQL dialect, or will it be in any external language like C/C++/Python/Java etc?

I won't be able to single out and suggest a provider or strategy, due to the lack of details and due to the fact that (for the last few years) I'm generally managing DB servers deployed in-house or on dedicated servers on the cloud.
But first of all, I strongly suggest you phase out MySQL.

Having the power of Oracle or the ease of use of MSSQL would be nice, of course, but I won't push such a proprietary system with (possibly) high cost of licencing.

Instead I strongly suggest moving towards PostgreSQL. Especially for stability and scalability issues.
But the strategy to pick (will it be rented service, a platform or rented dedicated servers? Will there be a cluster, and if yes, which kind? What should be the population of the cluster, and what kind of network topology should be implemented) is all up to the details.
It's kind of hard to suggest more than saying "build a cluster of PostgreSQL DB servers, consisting of a couple of dedicated hardware, within the compounds of a trusted infrastructure provider" with so few details. And maybe: "Keep an acceptable amount of business logic in stored functions and procedures if the application server will be residing in-house and the network latency between it and the DB cluster will be an issue."
Maybe you need more, or maybe you don't even need all that hassle.

And if by an unlucky decision, you end up sticking with MySQL, do not even think about using MyISAM.


Amazon RDS Postgresql adding new extensions

You can't add your own extensions to RDS, at least not ones that require superuser rights (like anything with C code). This is one of the downsides you accept in exchange for convenient management.

If the extension only includes simple plpgsql and sql functions you can add the functions manually. That is not possible with anything requiring superuser privileges, including any extension with C code.

jsonbx adds functions and operators. The source contains C code, so there is no way you can load this on RDS. Ever. At all. RDS doesn't allow you to run anything that might give you superuser access or access to the underlying VM that PostgreSQL runs on, nor does it provide a way to upload extensions even if it would permit you to load them.

You will need to switch to running a standalone PostgreSQL server (say, on EC2), persuade Amazon support to make the extension available as part of their supported set of extensions, or make do without.


How do I include nulls during comparisons in SQL Server?

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx

I get what you're trying to do, and to make a counter point I would ask if you've seen any queries generated by reporting services or something like Cognos? If so, you'll see exactly what you're describing you don't want to do. Why? Well because with a schema that allows nulls, that's the way to do it. I'm not saying it's a super awesome and great idea but it works all of the time.

What your designer could do is check to see if that column could even be null and if so the appropriate logic could step it and create the correct query. You could also have options such as "This column may be null, do you want those values?". I don't know the end-game per-se and writing your own dynamic querying tool is quite the deat when the logical consistencies are all factored in (such as this).

I would continue to do explicit null checking on columns that could possibly be null, sure it doesn't look the best but it works all of the time.

ANSI_NULL set option will work for now but NOT a good idea especially if you don't control the environment, plus it will be forced set ON later and cause errors where you'll need to re-write your application logic anyway. This is the way SQL Server works with NULLs.


Preparing query in postgreSQL with variable table name?

To write procedural code and use variables with PostgreSQL, the most common way is to use the plpgsql language, in a function or in a DO block.
Executing Dynamic Commands in the documentation has all the details you need.

Note that a dynamic SQL statement does not require a PREPARE like in your MySQL example. It's possible to EXECUTE a dynamic string directly containing the SQL query, and in most cases that's how it's done.

What might be confusing to a beginner in Postgres is that it has two distinct forms of EXECUTE:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] in a plpgsql code block is a plpgsql instruction.
EXECUTE name [ ( parameter [, ...] ) ] is an SQL query, where name is the name of prepared statement previously declared with PREPARE.


Package DBMS_REDEFINITION missing from Oracle 11g?

Privileges Required for the DBMS_REDEFINITION Package 11.2

You do not need that role, that is just an example. I would not use that role, as it grants execute privileges on a lot of other packages as well.

You need the following privileges:

grant execute on dbms_redefiniton to your_user;
grant create table to your_user;
grant create materialized view to your_user;
grant create trigger to your_user;

The above is enough, if you want to redefine tables in your own schema. If you want to redefine tables in other schemas, you will need the "ANY" privileges listed below:

CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
CREATE ANY TRIGGER
CREATE ANY INDEX


Oracle Enterprise Manager read-only access for non-administrative users

You allow a non-admin user access to the Performance area of OEM by granting them the OEM_MONITOR role. However, be aware that this role does have a few additional priviliges that you may not want to provide. In that case I would create a similar role to OEM_MONITOR but with a reduced set of privileges.


How to IFNULL or COALESCE rewrite in a SQL query?

There are several ways to rewrite the query and even more because both subqueries use the same base table. Not sure why the error is thrown and who is to blame, the JDBC drivers, the Foxpro or something else, so here are a few alternatives:

(1) using one query for both searches:

SELECT COALESCE(SUM(CASE WHEN t.btyp = 5 THEN t.netto
                         WHEN t.btyp = 6 THEN - t.netto
                                         ELSE 0
                    END), 0) AS shipManagementResult
FROM tckopf AS t
WHERE t.referenzid = 1 ;

(2) pulling the COALESCE() or IFNULL() function call in the external level, either using the original query:

SELECT
  COALESCE( ( SELECT SUM(i.netto)
              FROM tckopf AS i
              WHERE i.referenzid = 1
                AND i.btyp = 5
            ), 0)
- COALESCE( ( SELECT SUM(c.netto)
              FROM tckopf AS c
              WHERE c.referenzid = 1
                AND c.btyp = 6
            ), 0)
  AS shipManagementResult ;

(2b) or the above (1st) query:

SELECT COALESCE(
  ( SELECT SUM(CASE WHEN t.btyp = 5 THEN t.netto
                    WHEN t.btyp = 6 THEN - t.netto
                                    ELSE 0
                END)
    FROM tckopf AS t
    WHERE t.referenzid = 1
  ), 0) AS shipManagementResult ;


Migration path to unify column names in backward compatible way

There is no easy way to do this, but the only way I can think of, is to use a layer of views to keep the old names.

Create two schemas e.g. "old" and "new" or something alike.

The "new" schema contains the real tables with the new column (and maybe table) names. Have applications that can deal with that set the search_path to only the new schema.

For every table that has renamed columns (or if the table itself got renamed), create a view in the "old" schema with the old names, e.g.

create view old.table_name
as
select new_column_name as column_name,
       some_other_column
from table_name;

This view will be automatically updateable without the need to write triggers for it.

Make applications that can only deal with the old names set their search_path to old, new. An unqualified reference to table_name will then use the view old.table_name with the columns that the code expects. As the view is updateable, this will work for DML just as well.

Obviously this won't work if the application code checks for the presence of real tables for some reason. Or tries to detect constraints for those tables.

It also requires that you can change the search path when connecting to the database (e.g. when using JDBC, you can achieve this by passing it in the connection URL)


Does querying fragmented tables in SQL Server Affect the database?

The link you posted refers to rebuilding the indexes, which can have a performance impact as they may take indexes offline and they also cause a high amount of I.o when rebuilding.

As far as your query there will be a slight performance impact from the amount of I.o you are doing but I doubt it will be noticeable.
In your query --

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)

Specifies LIMITED mode which is the fastest mode and scans the smallest number of pages, as this is the default run level.

https://msdn.microsoft.com/en-us/library/ms188917.aspx

Paul Randal has a good post that shows the performance impact and how you can monitor if anything is happening on your system when you kick off this job.
http://www.sqlskills.com/blogs/paul/inside-sys-dm_db_index_physical_stats/

One other thing, it does look like you are going to be filtering out heaps in your database based on your join to index_id. I always like to confirm what is happening with heap tables and how many I have when looking at a database from a fragmentation level.

This query will return the schema name and table name for each heap table that you have...

SELECT SCH.name AS [Schema Name] ,
OBJ.name AS [Table_Name]
FROM sys.indexes INX
JOIN sys.objects OBJ
ON INX.object_id = OBJ.object_id
JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.is_ms_shipped = 0 --filters SQL objects
AND INX.index_id = 0 --meaning it has none
AND OBJ.type = 'U'; --user table
GO


Here is some recommentations:

Make sure all the columns used in the joins are indexed properly.

if possible, replace subqueries with indexed temporary tables.

Move when possible the filtering clause from the join statement to the where statement (e.g. Where  pf.pathLength BETWEEN 2 AND 5)

Try to move the joins order to start filtering with the table which reduce the more efficiently the returned dataset.


Not sure if only that, but your are not closing the parenthesis correctly.

Same code with better indentation:

case
  when
    (product_name = 'premium' AND SUM(answered_calls)>0
  then
    (SUM(total_duration)/60.0)/SUM(answered_calls))
  else
    0
end AS Duration

See the problems:

case
  when
    (product_name = 'premium' AND SUM(answered_calls)>0
--  ^----------------------------- should close here --^
  then
    (SUM(total_duration)/60.0)/SUM(answered_calls))
--                      remove that closing one --^
  else
    0
end AS Duration


How to get same value in a unique column but differentiated by other column values?

I think the problem here is that you were unaware that UNIQUE constraints can contain more than one field. Based on your question, I suggested 2 fields, i.e. both product and manufacturer.

It appears now (following comments) that 3 are required, as originally suggested by @ypercube.

From the link I originally posted here, the syntax you require is as follows:

CREATE UNIQUE INDEX 'my_product_uq'
ON my_product (product_name, manufacturer_id , package_id);


Command line tool / script to backup a remote SQL Server database to local disk

There are two common ways to do this:

Easy way - with "normal" database backups - when you run the BACKUP DATABASE command, the SQL Server service on the remote box needs to write the backup to a file. The service needs to be able to write to the destination path. To do this:

Set up a shared folder on your local machine
Give the SQL Server service permissions to write to that share (either by specifically granting it, or by letting everyone write to it - this isn't a security lecture, obviously)
Run the SQL Server backup, and use the UNC path to your local machine's share for the backup target, like:

If you're doing it via T-SQL, it might look like:

BACKUP DATABASE MyReallyImportantDB TO DISK = '\\MyDesktop\SharedFolderName\MyBackup.bak'

Hard way - exporting the data - if the remote SQL Server can't write to your local machine for some reason, like network firewalls or permissions, then you can export the data. A couple of common tools are:

Exporting a BACPAC file
SQL Server Import and Export Wizard

But be aware that those methods can be much slower and not transactionally consistent. They're best for smaller databases - say, under 10GB.

Updated requirements - you noted no GUI, no file system access, and no backups. Given those requirements, you'll be doing some manual work:

Use an ETL tool like SSIS to export the data
Build a custom app (C#, Java, whatever) to export the data
Use a data sync tool like Redgate SQL Data Compare


How to add the aliases to the group by clause

Using the with clause will help keep your queries readable:

with <some_meaningful_name> as (
  select
    <complex_expression1> as <alias1>,
    <complex_expression2> as <alias2>,
    <other_columns>
  from
    ...
)
select
  <some_columns>,
  <aggregate_expression>
from
  some_meaningful_name
group by
  <some_columns>;


get count of subjects with at least one record in posts table

Try:

select count(*) from subjects where exists (select 1 from posts where subjects.id = posts.subject_id);


Postgres ignoring index on join, but all planner estimates are correct?

The problem is that the string literals in the subquery / VALUES statement are of type text, while the users.id column is of type citext, and the citext index can't be used when comparing citext = text.

When the input type is citext the index is used:

=> explain select * from users where hash = 'abc123'::citext;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.56..8.57 rows=1 width=1033)
   Index Cond: ((hash)::citext = 'abc123'::citext)
(2 rows)

But the index isn't used when the input type is text:

=> explain select * from users where hash = 'abc123'::text;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on users  (cost=0.00..397795.08 rows=1 width=1033)
   Filter: ((hash)::text = 'abc123'::text)
(2 rows)


How do I configure my SQL Server instance to use a different Edition with minimal side effects?

You could possibly follow these steps to "migrate" the databases to a "new" SQL Server 20xx instance.

detach the databases in the running SQL Server Express instance and leave them where they are.
Script out any logins that you require.
Don't delete/drop any databases.
De-install the SQL Server 2008 R2 Express Edition.
Install the SQL Server 20xx Standard Edition. (you can even use the instance name SQLEXPRESS; how clever is that?)
Add the SQL Server Logins to your "new" SQL Server 20xx Standard Edition instance.
Once the "new" SQL Server 20xx Standard Edition is running attach the "old" databases to your new SQL Server 20xx instance.
Change the database compatibility level to 110 (if required).

Enjoy a "new" SQL Server 20xx Standard Edition instance hosting your "old" databases.


1/ I was wondering how the database schema will look like for this
  particular operation?

To manage upload file, I use a simple table to save info of those files such as file_name, file_path, upload_date and more.
The point here, I don't save those files in PG. I will upload them to other storage (Google Storage) and save path to file_path.
For example:

file_name   file_path                           upload_date
csv_01      www.storage.com/bucket/csv_01       2017-22-22 11:10:00
csv_02      www.storage.com/bucket/csv_02       2017-22-22 11:15:00

  2/ If the user could able to save 3,4,5 and n columns of csv, how the UI
  is so quick and response enough to get back the data?

In terms of app, there are some libraries to handle UI (depends on your language).
This app will retrieve CSV info from PG, base on that it will load data from storage and show to user.

  3/ Are there any techniques for handling CSV data in Postgres sql?

You can use COPY command to process CSV data. But note that

  COPY naming a file or command is only allowed to database superusers,
  since it allows reading or writing any file that the server has
  privileges to access

Please read this article How to import CSV file data into a PostgreSQL table to know more .


How to do 10 G Database recovery without archive logs but available redo logs, ctl files and .dbf files

Alright, let's assume you have full backup without archivelog. And your backup is output from rman backup such as rman> BACKUP DATABASE;.

So, I'll explain how to restore it.

1) Make sure your DBID is same as your database. (Note : this process is used if you have full backup datafiles,controlfiles,spfile, and copy archivelog if you have.)

SQL> SELECT DBID,OPEN_MODE FROM V$DATABASE; (if you can access your database)
RMAN> SHUTDOWN IMMEDIATE;STARTUP NOMOUNT;
RMAN> SET DBID=your_db_id

ex : G:\rman_backup is your backup location.
If you just to restore your controlfile and datafile, ignore process number 1, continue to process 2.

2) Make sure your autobackup location of CONTROLFILE, SPFILE and DATAFILE also ARCHIVELOG ex : "D:\FLASH_RECOVERY_AREA\your_sid\AUTOBACKUP\2016_06_16\O1_MF_S_914709345_CP5HHNCS_.BKP" or"G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP".

In this example i assume you have activate your autobackup controlfile, if you don't need to restore your spfile skip this. you can next to restore control file.

RMAN> RESTORE SPFILE TO 'C:\app\Administrator\SPFILESIMKARI.ORA' FROM 'G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP';
RMAN> SHUTDOWN IMMEDIATE;STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM 'G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP';
RMAN> SHUTDOWN IMMEDIATE;STARTUP MOUNT;
RMAN> LIST BACKUP;
RMAN> CATALOG START WITH 'G:\rman_backup'; -- This is optional, if your control file can show "list backup" you don't need to run this. If not, you must to run this script.
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN; -- If you need to reset your sequence, or error you can "alter database open resetlogs", if not "alter database open noresetlogs";
RMAN> exit

For additional information, you can startup unmount if your SPFILE is configured properly. And `startup mount' if your controlfile is configured properly.
Hope this help you out, please comment if you've trouble.


Update column of a large set of records with a calculated value

Try this:

update prediction
set points =
    (
        0
        +
        CASE WHEN prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB
        THEN 3 ELSE 0 END
        +
        CASE WHEN prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB
        THEN 1 ELSE 0 END
    )
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

AFAIK - if function is not depend on data in query, then it calls one time per query,

AND

if the function is deterministic and schema bound - it will not call multiple times for the same data, BUT there has to be an index on argument data AND optimizer must want to use that index

If you want per-row locking, use ROWLOCK hint


Drop partition and rebuild global indexes "online"

Not in 11g.

Not even in 12c, but 12c makes life a bit easier, you can delay the "rebuild" part until a more appropriate time.

In 12c there is a new feature, called Asynchronous Global Index Maintenance. You can drop the partition, and that still leaves the global index usable, and marks the no longer needed index entries orphaned. Then later, you can clean up this index (DBMS_PART.CLEANUP_GIDX, or ALTER INDEX COALESCE CLEANUP, or the database can do it automatically for you as well (there is a pre-defined job for it called PMO_DEFERRED_GIDX_MAINT_JOB).


Run Multiple Scripts In One Transaction Across Multiple Servers

I've run into a problem like this.  I used ADO.NET 4 with VB.

Here's what I did:

Created a .txt file with list of servers.
Dump the .txt file into a List Box.
Wrote the .NET application to scan through the list box, line by line, dumping each line into a text box on the form.
Replace the connection string on the SQL connection with the database currently in the text box.
Wrote the SQL scripts inside the command clause.
Execute the SQL command on the current database.
Next line.

Example:

    'Loop through the server names.
    For i = 0 To lbxServers.SelectedItems.Count - 1
        txtServer.Text = ""
        txtServer.Text &= lbxServers.SelectedItems.Item(i).ToString() & " "
        'Call your sub procedure which contains your sql connection and sql command.
    Next

That will get your loop going, then you need to write some sub procedures for your queries.
Like so:

    'Declare SQL connection variables.
    Dim connection As New Data.SqlClient.SqlConnection
    Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
    Dim command As New Data.SqlClient.SqlCommand

    'Connect to the server next in the list box.
    connection.ConnectionString = "Server= " & txtServer.Text & "; integrated security=true"

    'SQL Statement to run on the server.
    command.CommandText = "TSQL"
    connection.Open()
    command.Connection = "connection"
    dataAdapter.SelectCommand = "command"

I put all of this inside of a try/catch loop so if there is an error it will rollback.

The TRY/CATCH loop was inside of my CommandText.

    BEGIN TRANSACTION
        BEGIN TRY
        --Run your Statements
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        DECLARE @Msg NVARCHAR(MAX)
        SELECT @Msg=ERROR_MESSAGE()
        RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
    END CATCH

I used my application to grab and dump that local variable for each session into a datagridview. That way if error happened, I saw it in the application.


Checking @@ROWCOUNT isn't valuable here because RESTORE VERIFONLY doesn't return any results. It either prints status messages or error messages. So, a simple TRY/CATCH should do:

BEGIN TRY
  RESTORE VERIFYONLY ...
  RESTORE DATABASE   ...
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH

If the VERIFYONLY fails, it will raise an exception, and skip the RESTORE.

Of course, there are still going to be scenarios where VERIFYONLY will succeed but the RESTORE will fail (for example, something swoops in and takes all your disk space).


You could verify the backup history with the following script:

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,

   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE 1 = 1
ORDER BY   2,3 desc

The script will give you an overview of the backup media and files and if you look at the last column is_copy_only you will see if the backup is COPY_ONLY (1) or not (0).

If they are COPY_ONLY then they will not break the backup chain. Otherwise you might face issues as explained in the following article:

COPY_ONLY Backups with SQL Server

  Starting with SQL Server 2005, a new backup option has been added that allows you to take full and transaction log backups in between your regularly scheduled backups without affecting the LSNs and therefore the sequence of files that would need to be restored.  Since Differential backups backup all data pages since the last full backup, these types of backups do not affect the LSNs and there is no difference when using the COPY_ONLY feature.

You might want to check out my answer here which was posted in response to the question How can I backup an SQL Server database using Windows Server Backup?.

It is related to Windows Server Backup and VSS, but touches on some points you might encounter with the VSS backup strategy that has been implemented at your shop.


INSERT AFTER trigger failing with multiple rows

Try taking out the VALUES clause in your final insert (See the commented line below which give your error) and replace it with a simple insert (see the final insert below).

Declare @Tb_Permissions table (LnkUserID int)
Declare @Inserted table (ID int)
insert into @Inserted(id) values(1),(2)
--INSERT INTO @Tb_Permissions ([LnkUserID]) VALUES ((SELECT [ID] FROM @Inserted));
INSERT INTO @Tb_Permissions ([LnkUserID]) SELECT [ID] FROM @Inserted;


SQL Server Failover Cluster Instance compatible with VMware HA?

There are so many factors in place here as you can have multiple failover paths defined both in VMWare and the failover cluster. So the answers are almost as many as there are configuration options.

But in general if a single EXI host stops. VMWare will migrate it's VM's to another and as all the virtual machines will have shut down the second windows failover cluster node will become the primary node.


SSMS 2014 "Deploy Database" feature has old SQL Azure settings

I wouldn't have been able to find the solution to this problem if you hadn't decided to open the VM on azure and check the version numbers, so thank you!

After I saw your post, I googled "sql 12.0.2430.0" and found a hotfix from microsoft that fixed the problem.

You can find the hotfix Here

The update added full support for the new Azure SQL database tiers.

Cheers!


What is the fastest way to export a table to a text file

Looping through all those articles is an option if you want to get old watching it execute.

Some different options that you should try are:

the bcp utility (mentioned above, too, it's a command line tool to import and export data to/from an SQL Server);
BULK INSERT statement - the T-SQL language sugar to work with data imports from scripts;
SqlBulkCopy class from .NET - if you want to work from inside the app. A nice example can be seen here: Linchi Shea's Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy .

And you can try all these options while looping in another session, just for fun :-).