How to disable Read-Only routing in SQL Server

Ref:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql

Use ALTER AVAILABILITY GROUP command with MODIFY REPLICA ON option.

<modify_replica_spec>::=
  <server_instance> WITH
    (
       ENDPOINT_URL = 'TCP://system-address:port'
     | AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }
     | FAILOVER_MODE = { AUTOMATIC | MANUAL }
     | SEEDING_MODE = { AUTOMATIC | MANUAL }
     | BACKUP_PRIORITY = n
     | SECONDARY_ROLE ( {
          ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
        | READ_ONLY_ROUTING_URL = 'TCP://system-address:port'
          } )
     | PRIMARY_ROLE ( {
          ALLOW_CONNECTIONS = { READ_WRITE | ALL }
        | READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE }
          } )
     | SESSION_TIMEOUT = seconds
    )

  NONE

  Specifies that when this availability replica is the primary
  replica, read-only routing will not be supported. This is the default
  behavior. When used with MODIFY REPLICA ON, this value disables an
  existing list, if any.


In short - no

You can show stats; to get the total_received and total_sent per database. Assuming you can check the stats before query and after, calculating the difference won't give you the query result size. Even in ideal env, when no other parallel sessions exist. Eg:

t=# copy (select id from t) to '/tmp/1';
COPY 1043482
t=# \! du -h /tmp/1
7.0M    /tmp/1

checking received:

t=# select 40493353-40493297;
 ?column?
----------
       56
(1 row)

hm, so the size of a query result is 7MB, but received bytes are 56. Ah! I saved result of query on server! So pgbouncer technically indeed did not receive large set of data, ok - do it to client:

t=# \copy (select objectid from pond_user) to '/tmp/1';
COPY 1043482
t=# select 40493442-40493353;
 ?column?
----------
       89
(1 row)

Same story... Maybe lets check bytes sent?..

t# select (355612622-343149820)/(1024*1024);
 ?column?
----------
       11
(1 row)

11MB... Not particularly precise match.

SO:

pgbouncer does not log the result set size, You can use show stats assuming you can isolate sessions in time and your estimations are to get a very rough approximation for sizes.

also

People are using iptraf or such for the same, Somebody logs long taking queries with log_min_duration_statement and then repeat them with CREATE TABLE AS to get the size, some even write patches for client.

even also

Analytic approach here leads to even more complications:

https://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes

Measure the size of a PostgreSQL table row

Although mycrosoft and mysql offers such approximation in cosy way


Remove index fillfactor for Azure migration

Deploy to an Azure V12 server, which does support FILLFACTOR, and use SSMS 2014 or later to perform the Deploy to Azure DB task.

Using SSMS 2014 or later is necessary to generate the correct script, and to perform the right checks. To be clear: SSMS 2014 can be used to deploy a database from an earlier version of SQL Server. SSMS 2012 and earlier fail with the error shown, even when deploying to Azure V12.

I just tested deploying a table with a FILLFACTOR of 95 from a SQL Server 2012 instance to an Azure V12 server using SSMS 2014 and it succeeded.

There are other options, including using SSDT and yes, dropping and recreating all your existing indexes, but this is making the task harder than it needs to be. You can find those other options in this SO answer.


Probably depends on how quickly you want to be alerted.

If you just want to able to report on it then I'd probably suggest setting up a policy in Policy Based Management. You should be able to evaluate your instance on demand or on a schedule. You can generate an alert based off the SQL job it creates as well so you can be notified.

You could also look to just create a job that looked much like your query but either raise an error if there are databases that breach your policy (and append it to the alert)

The two options that would almost immediately let you know if someone had changed an autogrowth setting would be either via

DDL Server Scoped Trigger
SQL Alert using WMI to query ALTER_DATABASE Events

However, the latter would probably need to use tokens and would definitely require Service Broker to be enabled on all databases to work.


First query could be slow as the innodb buffer has been filled up with the queries required for the mysqldump and all the data needs to be fetched from disk.

You could run SET GLOBAL innodb_buffer_pool_dump_now=1 before the dump and SET GLOBAL innodb_buffer_pool_load_now=1 after the dump to ensure the buffer pool is what it was prior.


server running- mysqladmin error- Development Source Tree

OMG! I can't believe I solved it!

I started mysqld. However, client could not be started. When I gave the command 'mysql', the following error was shown:

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql

Why not create a user account in mysql?! Hence, I created a user account 'root' with 'root' as the password by writing the following in a text file named init-file and placing it where the user mysqlnew have access right. Note that they must be in different lines. Also, ensure that the server is not running.

  UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
  FLUSH PRIVILEGES;

I started the server by:

  ./mysqld --basedir=/usr/local/mysqlnew --datadir=/usr/local/mysqlnew/data --query_cache_type=1 --init-file=/home/mysqlnew/init-file &

I then started the client by

  mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql -u root -p
  Enter password:

Since I have now created a user account in mysql, I don't need to use --skip-grant-tables anymore for starting server.

However, its not yet finished!

  mysql> select 1+1;
  ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

So, now I need to rectify this!

  mysql> SET PASSWORD = PASSWORD('root');
  Query OK, 0 rows affected (0.28 sec)

And that is it! :)


Manual recovery of dropped collection in mongodb

We tried to run mongod --repair but instead of recovering the files it deleted them.

Repair and backup processes will (by design) ignore deleted data. Based on the filenames referenced in your question, you are using the MMAP storage engine. When a record is deleted in MMAP the first several bytes of the record are overwritten and the preallocated storage space is marked as available for re-use on the free list. There is a possibility you could write a custom script to try to recover useful bytes from deleted records, but there is no supported "undo" process for doing so.

Hopefully you took a backup of your data files before running --repair. Repairing MMAP data files results in a compete rebuild of your data files and would free up preallocated space used by deleted collections and documents. In any case where you are trying to recover or repair database files from a standalone MongoDB deployment, stopping your MongoDB instance and taking a file copy backup of your data files is a recommended starting point.

  Is there a way to recover these data? maybe partially?

Your best option would be to restore from your most recent MongoDB backup.

In a standalone MMAP deployment without backups, the only other possibility would be writing custom data recovery scripts to try to identify and salvage deleted documents. This involves some significant guesswork and manual effort to clean up data. Deleted records will be missing some essential information such as the size of the associated BSON document, and there may be variations in internal storage depending on the provenance of your data (for example, if all data was created in a specific version of MongoDB versus data files that have been upgraded through successive major releases).

See StackOverflow: Is there any way to recover deleted documents? for some possible approaches.


Yup, this is Bug 2391334 which has been around for long time, and probably will not be fixed in the near future.

One way of working around this is "know" the path for scripts without actually hard coding that path.  To do this in SQLPlus requires a trick - if you try to run a non-existent file, then you'll get an error message that includes the path name.

So here's a demo of that in action.  To mimic your scenario I've got:

c:\temp\demo
   script.sql
   maindir
      subdir
         call_script.sql
         script.sql

What we can do is add some commands to the front of call_script.sql which will pick up the path.  It looks a little odd, but you should not need to change it - its just a fixed thing you paste in

set termout off
spool _path_finder.sql
@@_nonexistent_script.sql
spool off;

var path varchar2(100);
set serverout on
declare
  output varchar2(1000) := regexp_replace(replace(q'{
@_path_finder.sql
}',chr(10)),'.*"(.*)".*','\1');
begin
  :path:=substr(output,1,length(output)-24);
end;
/
col path new_val path
select :path path from dual;
set termout on

What's happening here, is we're running a non-existent script, which returns:

"SP2-0310: unable to open file "path\_nonexistent_script.sql"

so with a little regexp we can extract the path, store it in a SQLPlus variable and then use from that point on.

So the final version of your call_script.sql would look like this

set termout off
spool _path_finder.sql
@@_nonexistent_script.sql
spool off;

var path varchar2(100);
set serverout on
declare
  output varchar2(1000) := regexp_replace(replace(q'{
@_path_finder.sql
}',chr(10)),'.*"(.*)".*','\1');
begin
  :path:=substr(output,1,length(output)-24);
end;
/
col path new_val path
select :path path from dual;
set termout on
prompt path was &path

@@&path\script.sql
@&path\script.sql

and when we run that, we get the following

SQL> @maindir\mainsubdir\call_script
path was maindir\mainsubdir
script in subdir
script in subdir

and there you go :-)


MySQL Query Optimization : Indexing and Pagination

At this point, you will have to create an index on three columns

ALTER TABLE news ADD INDEX authored_time_sid_ndx (authored,time,sid);

You must then refactor the query just slightly to reduce two things the MySQL Query Optimizer is expected to handle:

the number of rows read
the amount of data collected

Here is your original query:

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

What you can do is gather all the keys first:

SELECT sid FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

This will perform a full index scan rather than a full table scan because all three columns are in the index. That's a lot less baggage to walk around with for temp table generation because you are scanning just 3 columns in a smaller resource (the index) as opposed to 6 columns as from a bigger resource (the table) as well as not sticking url and bodytext (which are probably VARCHAR(300) and TEXT fields) into the mix just for the sake of gathering keys.

Next, make the SELECT sid query into an inline table and connect them back to the news table using only the fetched keys. EXAMPLE : Suppose your LIMIT variables are 200,10. This means you want to move to the 201st row of the news table and get 10 keys from that point. This means that no matter which page you are on, you want to collect 10 keys at a time, and only 10 keys at a time.

Here is the new and improved query:

SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url
FROM
news A INNER JOIN
(SELECT sid FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit) B
USING (sid);

The only real cost here is doing a full index scan for keys rather than a full table scan. The benefit is that once you get the 10 keys from the inline SELECT, then only 10 titles, bodytexts, authors and urls are retrieved.

Give it a Try !!!

CAVEAT

As a rule of thumb, anytime you index a column whose cardinality is very low (i.e., Male/Female (2), Single/Married/Divorced/Widowed (4), True/False (2), 0/1 (2)) and the number of rows for any one value exceeds 5% of the total rows in the table, the MySQL Query Optimizer will rule out any and all indexes and you will perform can full table scan or a bad index gets chosen and you end up with a full index scan. This is why it is very imperative that you find the right column distribution, or at the very least perform index scans instead of table scans.

UPDATE 2011-08-08 11:13 EDT

What was I thinking ? This was my original propsed index:

ALTER TABLE news ADD INDEX authored_time_sid_ndx (authored,time,sid);

The field should be approved not authored. This is what it should be:

ALTER TABLE news ADD INDEX approved_time_sid_ndx (approved,time,sid);

The original query you gave was

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

The index I am proposing (approved_time_sid_ndx) will include approved, time, and sid.

The answer that you just submitted has this query :

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved = 1 AND sid > 0 ORDER BY sid desc LIMIT 500, 10;

This being the case, the index you need now should be this:

ALTER TABLE news ADD INDEX approved_sid_ndx (approved,sid);

Both approved and sid should be together. IF they are not together, the MySQL Query Optimizer may decide to perform an internal index merge of the primary key and an index where approved is the first (or only) column. In fact, your new query should be refactored as follows:

SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url
FROM
news A INNER JOIN
(SELECT sid FROM news WHERE approved=1 and sid > 0 ORDER BY sid desc LIMIT 500,10) B
USING (sid);

Remember, you want an index that encompasses as many DB columns as possible that are embedded in WHERE and ORDER BY clauses.


How to disable all password requirements in Oracle?

In DBCA, It will mark the password weakness as an error, push through with the "NEXT" button.

You will be warned again in a pop up box that you are making a serious mistake and are you sure that you want to continue.

Answer "Yes".

It should move on from there.

It's the same pretty much everywhere.

DBCA:

Grid Infrastructure:


Can we change the database code page after the database creation?

You cannot change the database code set after creation.


How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables

Like Mike says, this is not something that you should be doing with the database. This is more of an algorithm question.

Your problem looks very similar to the knapsack problem, which is NP-Complete. Fortunately, you are not looking for the set that adds up to exactly some amount of calories, but a range, so it's not NP-Complete. Luckily for you, a few hundred recipes will easily fit in memory, so you can easily write an algorithm for it.

Here's a simple algorithm. I'm sure there's a better way to do it:

orderedRecipes = recipes sorted by calorie asc

def combinations(recipes, lowerBound, upperBound, recipeCount):
    first = recipes[0]
    if first.calorie < upperBound:
        for c in combinations(recipes[1:], lowerBound - first.calorie, upperBound - first.calorie, recipeCount - 1):
            if sum(r.calorie for r in [first] + c) > lowerBound:
                yield [first] + c
        for c in combinations(recipes[1:], lowerBound, upperBound, recipeCount):
            if sum(r.calorie for r in c) > lowerBound:
                yield c

combinationsOf3Between440and460 = list(combinations(orderedRecipes, 440, 460, 3))

The first loop takes the first item in the rest of the list, and the second loop does not. You don't want it to always take the first item, you want both take and no-take.


You could just simply get the previous and next measure values with LAG and LEAD, and filter based on them:

select road_id, vertex_index, measure_val from (
  select
    r.road_id, r.vertex_index, r.measure_val, e.from_measure, e.to_measure,
    lag(r.measure_val, 1, r.measure_val) over
      (partition by e.road_id order by r.vertex_index) as prev_measure_val,
    lead(r.measure_val, 1, r.measure_val) over
      (partition by e.road_id order by r.vertex_index) as next_measure_val
  from
    road r join event e on (r.road_id = e.road_id)
) where prev_measure_val <= to_measure and next_measure_val >= from_measure;


How to remove partial duplicates from a table

WITH duplicates as
(
    SELECT id_page, id_site,
           row_number() over (partition by id_site order by id_site, create_date) rn
    FROM   pages
    WHERE  label = '2018-12-15'
)
DELETE FROM pages
WHERE  id_page IN (SELECT id_page
                   FROM   duplicates
                   WHERE  rn > 1);


SHRINK FILE works with SA but not as a normal user

The last thing you want is allowing a normal user to run DBCC SHRINKxxx

This should be reserved for highly privileged users to run in a few rare circumstances. This should not be a regular or normal operation.

Permissions for both DBCC SHRINKDATABASE and DBCC SHRINKFILE are

  Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

If you really want to run this because you know better, then use a stored proc with EXECUTE AS

CREATE PROC dbo.IKnowBetter
WITH EXECUTE AS OWNER -- escalates to db_owner in dbo schema
AS
DBCC SHRINKFILE (1);
DBCC SHRINKFILE (2);
GO
GRANT EXECUTE ON dbo.IKnowBetter TO OikUser;


Why/how did the behavior of extract(epoch...) from "timestamp without time zone" fields change between PostgreSQL 8.4 and 9.2?

Testing this on my own machine, I get 1351749699.69961 as the result on both PostgreSQL 8.4.10 and 9.2.1.

With PostgreSQL 8.4.10, the result changes according to the session timezone. With 9.2, it does not.

Likely this is the effect of this change: Measure epoch of timestamp-without-time-zone from local not UTC midnight. which is noted as a 9.2 compatibility change.

The suggested workaround in the change notes is to cast the timestamp to a timestamptz first: not just a configuration change.

Personally, I find the 9.2 behaviour more comfortable. The Unix epoch is defined as UTC, so this effectively means that plain timestamp values are interpreted as UTC: which is how I use them anyway.


Sql Server 2017 Developers Edition install failure during configuration

I never solved it, but here is how I worked around it.

I don't know if it was necessary, but I started with SSMS and all previous versions of Sql Server uninstalled, as well as the Visual Studio C++ shared components (versions 2015 and 2017), and had deleted the Sql Server directories.

I was installing the database as the user I wanted to be the admin user.  When I ran the install, I right clicked and ran as admin.  When the installer asked for the admin user, I used the "Add Current User" button to add myself.

It still failed when trying to connect to configure the database.  However, the Configuration Manager had already been installed (in the Start Menu).  So I ran that, right clicked on the Service to get to properties, found the command line switches, and added -m to start the service in single user mode.  Because I had ran the installed as admin, that invokes the backdoor to allow a local admin user to connect without checking credentials, so the install script could configure the database and add the users. After installation was complete, I removed the -m switch using the configuration manager, restarted the service, and had a running instance of the database.

I hope nobody ever has to go through that again, but if you do, that's what worked for me.


What to name a field that includes Store and Version ID

You should not retain two (or more) different values, each from a distinct domain, within the same column, because that procedure would only introduce unnecessary complexity to the restriction, manipulation (insertion, modification, deletion and retrieval), general maintenance and interpretation of the data. See this relevant Stack Overflow post by @PerformanceDBA  for some related aspects.

I definitely would keep each contextually individual datum in its corresponding, dedicated, constrained and meaningful column. So, in your scenario, I would set (a) one specific column to hold the StoreId and (b) one separate column to hold the VersionId in all the pertinent tables, such as you have presented in your first sample. This approach avoids the manual extraction and validation of each of the individual values contained in a combined column, which is very important since said operations would undermine the system speed and, most likely, would also eventually compromise the data integrity.

In case you want to merge those columns to meet some particular information display requirements, you can concatenate their values on the fly by means of a SELECT statement or, perhaps, with the aid of your application program code, and then show them inside a single column to your data users. Some feasible names for this computed or derived column could be, for instance, StoreId and VersionId, StoreId & VersionId, StoreId – VersionId, StoreId / VersionId or VersionId for StoreId, since these denominations would express the —purely expository— blend of two distinct data points.


Performance effect of indexing foreign keys

It may still be useful to have indexes on the larger table to help the optimizer for specific SELECT queries, even if you will "never" change any of the values.

However, without a lot more knowledge of your system and the types of queries you will run, it's pretty difficult for any of us to able to tell you whether you should have indexes there or not. I'm afraid it's just something you're going to have to test.

Some background in this great article by Erin Stellato:

http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

You also might consider avoiding the joins in some cases - when you are sure the values will never change, and eliminating the join can simplify the execution plan, you can consider just inline constants, e.g.

SELECT CASE RatingID
  WHEN 1 THEN 'poor'
  WHEN 2 THEN 'average'
  ...
END
FROM dbo.BiggerTable
...

Also I suggest never naming something ID. If it's a RatingID, call it RatingID everywhere it exists in the model.


WARNING Error checking mysqldump version Error retrieving version from mysqldump.exe: mysqldump: unknown option '--no-beep' (exit 2)

--no-beep is an option for the mysql command-line query tool, not for mysqldump.

I would guess that you have entered the no-beep in your my.cnf configuration file under the [client] section, which applies to both mysql and mysqldump clients. You need to move no-beep to the [mysql] section of the config file, so it applies only to the mysql client.


use SET NOCOUNT ON

SET NOCOUNT ON
declare @t
   table (customerid varchar(100),[enrollment_Date] varchar(100))
insert into @t exec master.dbo.my_stored_procedure
select * from @t

http://www.bidn.com/blogs/dustinryan/ssis/835/ssis-ole-db-source-using-table-variables-returns-no-rows


Database mirroring on Rackspace or Xeround

I work for Xeround. With Xeround cloud database you do not need to mirror your database, you create a single database instance and direct your application servers to work with this instance. Hope this helps!

Avigail


Why does the order of parameters matter for sp_trace_create?

I believe this is because it is an extended stored procedure and the parameter names are actually entirely ignored. It just goes off position.

I have renamed them as below (and given them all the same name) and it still works fine.

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @rubbish = @new_trace_id OUTPUT,
  @rubbish = 0,
  @rubbish = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;

A similar documentation bug was filed by Aaron about sp_executesql.

Another annoying aspect of that stored procedure is that the @maxfilesize must be passed as 'bigint' and it doesn't accept a literal integer. I assume that this is also because it is an extended stored procedure.


I suggest you first determine whether its the stats or if its parameter sniffing that is hurting you.
Regardless of above I suggest you read Erland's article on the subject.
What to do about it is difficult to say. We don't know whether it is stats or sniffing.
But possibly adding OPTIMIZE FOR can be "the" solution. It is cheaper than RECOMPILE since you don't have to take the plan production hit on each execution. And it gives you predictability. This, of course, assumes that you don't have the case where the stats differ so much so the same parameter input yield different plans because of stats reasons.
Try to identify one query. See if you have one or many plans for the query. Test with OPTIMIZE FOR and/or RECOMPILE. The one "global" option at database scale you have is to disable parameter sniffing for the database. This means the optimizer optimizes as it doesn't have a clue of the value. All this and more in Erland's article.
Parameter sniffing does not only apply to stored procedures. It also applies to parameterized SQL (typically executed using sp_executesql), which likely is far more common nowadays than stored procedures.


Yes simple way to temporarily stop Logshipping is to disable all the jobs related to Logshipping, since LS is basically backup of transaction logs and restore you have control over it. You need to disable Backup, copy and restore jobs. Please also make sure you disable LS_Alert jobs because they would fire and unnecessarily give you false alerts.

Caution:

If you stop the jobs for longer duration your database recovery stands exposed so be careful with that.

You also should keep in mind that the transaction log backup which would be done after you start the job might be big in size and it would take time to copy it and restore it. If the size is too big you might get some out of sync alerts so be ready for it.


Trying to start a newly created cluster in PostgreSQL on Windows

Aside from the upgrade recommendation (which I second btw but will not solve your current problem) your basic issue is that PostgreSQL will not run as an administrative user.  This is a security feature.

Now, if this is a desktop not on a domain model, you need to create a limited user, and run initdb as that (or assign ownership to all files under the existing second cluster to that user if it already exists which it may).  Then read the runas documentation.

Then run something like:

 runas /user:<computername>\postgres postgres -D C:\my\new\cluster\data\directory

So if your computer name is mycomputer, it would look like:

runas /user:mycomputer\postgres postgres -D C:\my\new\cluster\data\directory

You can also:

pg_ctl register -N postgresqlc2 -U postgres -P [password] -D C:\my\new\cluster\data\directory ....


I use it. It isn't malware.

On the SQL Server forums its one of three useful tools/SSMS plug-ins that are widely used (and thus implicitly reviewed) by the community.

SentryOne Plan Explorer
SSMS Tools Pack
Red Gate SQL Search


Does a covering nonclustered index help to answer Select * queries?

The missing index suggestions are opportunistic entries added whenever the optimizer happens to notice that an exact-match index for the current set of predicates it is considering do not exist on the base object.

The information recorded in the DMVs is intended to be a helpful input to the normal activities of a skilled database tuner; it is not intended to be interpreted as the result of a comprehensive analysis. The data recorded is intentionally a complete set of the keys and other columns needed, together with a brief indication of the reason it was added (equality, inequality, include).

The "impact" recorded is also very basic: it is the difference in estimated cost, according to the cost model used by the optimizer. As usual, this needs to be treated with a healthy dose of skepticism, because it is very unlikely that the actual performance characteristics of your hardware match the optimizer's model, even vaguely.

Note also that cost estimations depend heavily on cardinality (row count), average row size, and distribution information. If these are not reasonably close to the actual values, the "cost" is an even less reliable metric. Nevertheless, right now, estimated cost is all we have, so that's the number reported. Again, SQL Server is just trying to be as helpful as it can; it is up to the person reviewing the information to interpret it correctly.

Tests based on estimated costs are potentially informative to an early assessment of target areas for improvement, but they are no substitute for actual measurements of whatever metrics are important in your environment. The relative importance of duration, CPU usage, physical reads, memory usage ... and so on ... will be different in different circumstances.

The point really is to measure where the real problems are, record a baseline, make improvements, then measure and assess the benefits against the costs.

For a more thorough analysis than the missing index feature provides, you would need to run a representative workload through the Database Engine Tuning Advisor. Even there, the recommendations will still need to be assessed by a human, and something similar to the above baseline-assess-change-measure protocol followed.

That said, not all final decisions will be based on performance comparisons. There may be other considerations, like the avoidance of a particularly inconvenient lookup-related deadlock. Index tuning is as much art as science sometimes.


One way to do this is using window functions:

SELECT DISTINCT
    type_id, book_id, title_id, page_id,
    first_value(lines)      OVER wmin AS min_lines,
    first_value(created_at) OVER wmin AS min_created_at,
    first_value(lines)      OVER wmax AS max_lines,
    first_value(created_at) OVER wmax AS max_created_at
FROM
    tableX
WINDOW
    wmin AS (PARTITION BY type_id, book_id, title_id, page_id
             ORDER BY lines ASC, created_at ASC),
    wmax AS (PARTITION BY type_id, book_id, title_id, page_id
             ORDER BY lines DESC, created_at ASC) ;

Tested at SQLfiddle.


How to create 3 columns from 2 columns with SQL query?

You need to use a pivot table to get colums out of rows for what you're after

select speed, idp.[1] as power600V,idp.[2] as power750V
from tableName PIVOT
(
MAX(power)
FOR id in([1],[2])
) as idp

will get you the results you're after

https://technet.microsoft.com/en-us/library/ms177410 for the full pivot reference


This question offers a usable answer on stackexchange from KernelM: Indexed Views in MySQL

Specifically this paragraph:

I don't think MySQL supports materialized views which is what you would need, but it wouldn't help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would still cause the write speed issues.

Your best bet would probably be to create summary tables that get updated periodically.


How to implement complex search functionality?

Use Fulltext:

FULLTEXT(postTitle, des)

and

AND MATCH(postTitle, des) AGAINST("+post +test" IN BOOLEAN MODE)

Construct, and optionally leave out, any tests that are not requested.  That is, don't include this line if there are only 20 rooms:

AND (room >= 0 and room <= 20)

Don't bother will bath >= 0 since it is UNSIGNED.

Gather typial queries.  Either by asking 'users' what they will ask about or by collecting queries on the fly.  Then construct the 'optimal' indexes.

But, first, note that if the MATCH clause is included, it will be the only index used.  That is, for cases where the user is not testing on postTitle and des, then we can look at indexing.

Clarification:  It will use the FT index first, then filter on any other WHERE clause, but without the benefit of INDEXes.  The assumption is that FT filters down to very few rows, and the subsequent filtering is not a problem.

The only two useful columns in the above example are those with =.  So:

INDEX(areaId, availableFrom)

But, after looking a user activity, you may find others.

See also Elasticsearch.


How to join values from multiple rows in a single column?

SELECT customer_id,
       GROUP_CONCAT(product_id) AS product_ids,
       ...
    FROM tbl
    GROUP BY customer_id;


For example, take a table T with an index on attributes a and b. Is the index useful for the query:
select * from T where a='foo'

There are two questions there:

Can an index on (a,b) be used at all for this query?
The answer to that is generally yes. Maybe not all databases have that capacity, but most mainstream ones do AFAIK. Replace the select with select a,b from ... and the engine could not only use the index, but not access the actual table at all to answer the query.

Will the optimizer choose to use the index?
That will depend on the database system and how much information the optimizer has about the data. If it can determine that the first column is "selective enough", then it will likely use it. If not, it likely won't.

Here's an illustration on Oracle XE 11g.
SQL> create table T
  2  as select object_name a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index T_ab on T(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from T where a = 'foo';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    27 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    27 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | T_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

The all_objects view contains information about all the objects (tables, views, procedures, etc.) present in the database. The object_name fields isn't unique, but there aren't a lot of duplicates (in this database at least), so that leading field itself is very selective.
The cost of doing a range scan on the index, then looking up the estimated one row by rowid is going to be much less than doing a full table scan, so the optimizer takes that route.
This is a fairly usual situation and plan, you'll likely come across it a lot (or something similar on other database engines).
Now here's a different scenario where the optimizer can use a detailed picture of the actual contents of the columns to optimize things differently:
SQL> create table Q
  2  as select 'a' a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index Q_ab on Q(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'Q');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from Q where a = 'a';

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 18085 |   176K|    15   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| Q    | 18085 |   176K|    15   (7)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select * from Q where a = 'z';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

The table is intentionally completely skewed, the first column has 'a' as its only value. If the optimizer knows that, then it can opt for different paths based on the actual queried key value, as see above.
If 'a' is requested, using the index is a bad move - you'd need to traverse the whole index and the whole table to get all the rows, which is (potentially a lot) more costly than just scanning the table.
If the value requested is not 'a', scanning the index is much more efficient since it will likely return no rows.
Here's something perhaps a bit more surprising: the index on (a,b) can actually be used when the where clause filters on b only.
SQL> select * from Q where b = 1000;

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN       | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

This works because the leading column has few distinct values, but the second column is essentially unique. Think of it as the optimizer partitioning the index by the first column, then doing a binary search on each partition. That's going to be efficient if the number of partitions is small and the other criteria are fairly selective. (i.e. doesn't work for table T in this example.)


MariaDB Galera Cluster on Ubuntu 16.04 does not start after rebooting

This is likely to be a network or firewall-related issue.

A few things to check:

Do you get the same error on both nodes?
Have the IP addresses changed?
Are the relevant ports open in the firewall? Relevant ports are 3306, 4567, 4568, 4444. See here for details: Galera Firewall Settings
Could access to any of the ports be blocked by AppArmor?


Always On Availability Group Failover Issue

This error may happen if your SQL Server service is running under user who does not have the needed permission on your servers or the account is locked. try to use another account and make sure account is unlocked.

If that did not fix the issue then you need to edit your regisery (make sure you take a backup first)

open start menu,write regedit then press ok
navigate to HKEY_LOCAL_MACHINE\Cluster\Resources\<GUID>\Parameters
Create the following registry values in the

Parameters registry key:

For a default instance of SQL Server:
InstanceName
Value Name: InstanceName
Value Type: REG_SZ

Value Data: MSSQLSERVER

VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ

Value Data:
For a named instance of SQL Server:

Value Name: InstanceName
Value Type: REG_SZ

Value Data:
VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data:

4- quit registery editor


Adding multiple foreign keys to single table

You have a syntax problem, try this :

Create Table If not exists CompanyRank
(
CID int, Year int, IndexYN int, SPRank int, FortuneRank int,
primary key (CID, Year), index (CID), Index(year),
foreign key (CID) references CompanyInfo (CID),
foreign key (Year) references TimePeriod (Year)
) engine = innodb;

the reference is "CompanyInfo (CID)", not "CompanyInfo.CID"


Deterministic function for getting today's date

You cannot use the function GETDATE() as the basis for a persisted view since the output of the function changes moment-by-moment.  This what SQL Server means in the error "because it is non-deterministic."  Function results MUST be predictably the same every time they are called in order for SQL Server to persist the results.

Luckily, there is an easy way for you to persist this data once-per-day using a scheduled job.  Perhaps using SQL Server Agent, or Windows Job Scheduler, etc.

Here I'm creating the numbers table, and the table to hold the next 7 days:

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL
        CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
);

;WITH cte AS
(
    SELECT TOP(100) Number = ROW_NUMBER() OVER (ORDER BY t1.num, t2.num)
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t1(num)
        , (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(num)
    ORDER BY t1.num * t2.num
)
INSERT INTO dbo.Numbers (Number)
SELECT cte.Number
FROM cte;

CREATE TABLE dbo.Next7Days
(
    SettingDate DATETIME NOT NULL
        CONSTRAINT PK_Next8Days
        PRIMARY KEY CLUSTERED
);

Schedule this to happen once per day:

TRUNCATE TABLE dbo.Next7Days;

INSERT INTO dbo.Next7Days(SettingDate)
SELECT SettingDate = DATEADD(DAY, n.Number, DATEDIFF(DAY, 0, GETDATE()))
FROM dbo.Numbers n
WHERE n.Number < 8;

Since the dates of the next 7 days only changes once per day, this solution should work well.

The dbo.Next7Days table contains the following after I ran the above code:


Postgres 9.6 (pgAdmin 4) - How to query daily statistics

The extension pg_stat_statements is what you are looking for.  It is not provided by PgAdmin4, but you can use PgAdmin4 to read from the view it creates.  It provides cumulative stats, so you either need to reset them every night, or just save daily snapshots and compute the differences.  You might want to use PgAgent to automate some of this.  (I have no experience with PgAgent, I just know that it exists.  I use crontab to schedule things)


What is the best approach to parsing a large amount of strings?

400 million names is a lot. Am I in there? ;-)

My gut level feeling says that using substring isn't going to be terribly much slower than coding up something via the CLR. I'm a SQL guy, I've done a fair amount of simple parsing in the past (2000 or 2005), and I was involved in what was going to be a very complicated parsing scheme (addresses, world-wide) written in c and called via an xproc until we found that out prototype "native" code wasn't any faster than the same thing written with tsql functions.

If you want to use a language other than tsql, I'd suggest writting a CLR in c# or vb.net. For simple things, it's not hard to code in CLR. I went from newb to having a couple of working directory and file utilities in less than one morning. There are plenty of examples of simple clr procedures on the net. and you wont have to learn anything (or install visual studio) to write it in tsql

No matter what, you will have to go through the table at least once. If you export, then parse and then put back what is not a small amount of data, that's a lot of time. Can you guarantee that your source isn't going to change in the mean time?

Here's the thing that always seems to sneak up on everyone:
What happens with the parsed data? Where does it wind up? Do you intend to update the row, perhaps there are lastname and firstname columns that you don't show in your example?

If you do, and those columns are currently null or have zero length stings in them, you may find that the update statement performance is very bad because sql may have to split pages to store the lastname.

In other words, your performance problem isn't parsing, it is storing the parsed data. Often, this is worse than inserting the data into another table. Also, all of those page splits will fragment your table and cause query performance to drop, which may enrage your dba because s/he will have to run a defrag procedure on the (large) table.

Here's one last thought:
Do you really need to store the parsed data? Can you get away with a computed column that calculates the last name on the fly? Those are indexable, with certain conditions, if you need that. Another approach would be a view that exposes the columns of the table as well as your "parsed lastname column".


UPDATE with subquery LIMIT 1 returns multiple rows

It's quite normal you get multiple rows.  The update selects all rows from folder, and for each of them selects a row from folder f2 (I guess the same, if id is PK). If locked = 'available', the record is updated.  On to the next row.

The correct syntax is here, Postgres UPDATE ... LIMIT 1,  and consists in using a CTE.

Without CTE, your result depends on how PostgresSQL decides to execute your update, starting with the outer (multiple rows are possible) or with the inner select.


Problems restoring backup from base backup + WAL with WAL-E

wal-e backup push

will NOT push any wal files to s3.

So this backup will be mostly unrecoverable.

Use

archive_command = '... wal-e wal-push %p'

on master to make this backup usable.


How to check other table for value during insert

This would work:

INSERT INTO car_model(model_name, car_make, date_released)
SELECT *
FROM  (SELECT 'Classic Car', 'Car Brand', '2000-01-01'::date)
  AS i(model_name, car_make, date_released)
WHERE  NOT EXISTS (
   SELECT FROM car_make m
   WHERE  m.company_name = i.car_make
   AND    m.date_incorperated <= i.date_released
   );

Or you can still keep the VALUES expression to insert multiple rows at once:

INSERT INTO car_model(model_name, car_make, date_released)
SELECT *
FROM  (
   VALUES
   ('Classic Car', 'Car Brand', '2000-01-01'::date)
    -- more rows?
   ) AS i(model_name, car_make, date_released)
WHERE  NOT EXISTS (
   SELECT FROM car_make m
   WHERE  m.company_name = i.car_make
   AND    m.date_incorperated <= i.date_released
   );

Note how it may now be necessary to add explicit type casts. See:

Casting NULL type when updating multiple rows

You had a couple of issues:

A VALUES expression does not take a WHERE clause. Needs to be a SELECT for the purpose.
Values are enclosed in single quotes (''). Double quotes ("") are for identifiers.
To refer to input values, you need to form a derived table and attach column aliases first - with a subquery (like I did) or a CTE.

Related:

How do I insert a row which contains a foreign key?
Select rows which are not present in other table


There are several applications for mysql access, below are free ones.

mac os -> sequel pro www.sequelpro.com
windows -> sqlyog community https://code.google.com/p/sqlyog/downloads/list


Replication Lag on Postgres AWS RDS Service

I had the same issue with our staging RDS. After quite a while I found a blog post that says:

  In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing.

To test this I wrote a simply script which updates a random record every 0.5s and the lag went to 0.

From the AWS docs under Read Replica Limitations with PostgreSQL -

  A PostgreSQL Read Replica reports a replication lag of up to five minutes if there are no user transactions occurring on the source DB instance


How can I measure the impact of queries with errors being rejected by SQL Server?

I see from your comment that the errors are not happening so frequently as to be a performance concern.
To answer the question asked, you could create a filtered XE trace to capture detail sql_batch_completed and rpc_completed events where result is Error. The events can then be summarized by a time interval for aggregated stats.
The example below summarizes the CPU and logical reads by one minute interval from a trace file target.
--example trace
CREATE EVENT SESSION [errors] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    WHERE ([result]=(1))),
ADD EVENT sqlserver.sql_batch_completed(
    WHERE ([result]=(1)))
ADD TARGET package0.event_file(SET filename=N'C:\TraceFiles\errors',max_rollover_files=(2))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

--example aggregation query
WITH
    event_data AS (
        SELECT CAST(event_data AS XML) AS event_data_xml
        FROM fn_xe_file_target_read_file('C:\TraceFiles\errors*.xel',NULL,NULL,NULL)
    )
    , event_data_fields AS (
        SELECT
              DATEADD(minute,DATEDIFF(minute,'',event_data_xml.value('(/event/@timestamp)[1]', 'datetime2')),'') AS interval
            , event_data_xml.value('(/event/data[@name="duration"]/value)[1]', 'bigint') AS duration
            , event_data_xml.value('(/event/data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time
            , event_data_xml.value('(/event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads
        FROM event_data
    )
SELECT
      interval
    , SUM(duration) AS duration
    , SUM(cpu_time) AS cpu_time
    , SUM(logical_reads) AS logical_reads
FROM event_data_fields
GROUP BY interval
ORDER BY interval;


Filtering Escape Characters in PostgreSQL

...how to filter out the serial with \n.

Add this in the subquery t1:
WHERE t1.serial_nbr !~ '\\n$'

You have to double \ to remove its special meaning. To identify actual newline characters (E'\n' - ASCII code 10), use a single \ instead.
$ signifies the end of the string.
That said, you could use cheaper LIKE expressions in your query:
SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr
FROM  (
   SELECT serial_nbr
        , CASE WHEN serial_nbr LIKE '_18%'      THEN regexp_replace(serial_nbr, '(...)(\w{{5}})','\\1 \\2')
               WHEN serial_nbr LIKE '_19______' THEN regexp_replace(serial_nbr, '(...)(\w{{6}})','\\1 \\2')
                                                ELSE serial_nbr END AS serial
   FROM   controller_returns
   WHERE  t1.serial_nbr !~ '\\n$'   -- !
   ) t1
LEFT   JOIN dblink('{param_db}', 'SELECT DISTINCT ctrlr_serl_nbr, insert_ts FROM ctrlr_basic_setng')
         AS q1(ctrlr_serl varchar, insert_ts timestamp)
         ON q1.ctrlr_serl_nbr = t1.serial
WHERE  t1.serial_nbr~'^[ABCD]([AC0-9]{{1,9}})'
ORDER  BY t1.serial_nbr, substring(t1.serial_nbr FROM 2 FOR 2), left(t1.serial_nbr, 1);


Insert into table select * from table vs bulk insert

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide.

At SSIS level, you can look into below things to speed up data read and data load :

Fast Parse Option along with its limitations.
Use the SQL Server Native Client 10.x OLE DB provider for an In-Memory, high performance connection
Set the Packet Size to 32767
Select the OLE DB Destination  Data Access mode “Table or View – fast load”  option

Refer to Speeding Up SSIS Bulk Inserts into SQL Server for more details.

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

If you are using SQL Server 2014 then SELECT ... INTO is parallel.

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Also refer to :

Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour


How do I create a user-input column in SSRS?

1) How to create a column to input user data and after that I need to
  perform some operations on data enter by the user which i need to
  display in another column

SSRS is not creating an application, just a report. It is not advisable to use SSRS to collect user input. A limited amount can be done using parameters but this is on a per report basis not per row as I think you're implying in the question. For example you could have a parameter to multiple values by a common factor and then display that result.

  2)how to make round a decimal to nearest integer ex 6.03 to 6 and 6.52
  to 7

Create an expression with the Round() function.

=Round(6.03)

this returns 6. Round also accepts a second value for number of decimal places. But instead of literal value, your fieldname would be in the parentheses:

=Round(myFieldName)


A SQL Server login is used to authenticate the principal at the server level, and to provide server level permissions.

A database user maps to zero or one login and is used to provide a security context within the database.  Database permissions are assigned to database principals, including roles, not directly to logins.

User names are generally not specified in connection strings.  The exception is when a user is authenticated at the database level, and therefore has no login.


Add a COUNT() column:

select emp_id,
  sum(case when leave_type = 'Planned' then 1 else 0 end) Planned,
  sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed,
  sum(case when leave_type = 'Informed' then 1 else 0 end) Informed,
  count(*) as Total
from t
where activity_type='Leave'
group by emp_id;

emp_id | planned | notinformed | informed | total
-----: | ------: | ----------: | -------: | ----:
   619 |       2 |           1 |        1 |     4
   567 |       0 |           3 |        0 |     3

db<>fiddle here


migrate db's - best way to mirror or replicate a full database

It sounds like you're trying to setup a disaster recovery environment, so that if your local machine blows-up you can switch over to the Azure machine.

There are a number of options, each with their own pros and cons. These are well documented elsewhere (see this link for an overview from Brent Ozar: http://www.brentozar.com/archive/2011/12/sql-server-high-availability-disaster-recovery-basics-webcast/). That said, you might want to look at Log Shipping and Asynchronous Database Mirroring particularly.

Log shipping is an automated process for taking your log backups and restoring them on another server (e.g. Azure), you can take log backups every five minutes so that you're Azure server is always within about five minutes of your local machine. The upside is that it's easy to configure and can be setup through the SSMS GUI.

Database Mirroring takes this to the next level, constantly sending completed transactions to the other server (i.e. Azure) so that it's within a minute or so of your local machine (this depends on your network connection, of course).

There are other options but if you're new to disaster recovery but I'd recommend looking at these to start with.


Question regarding flow control of SQL based jobs in SSIS

This actually is pretty easy to do within SSIS.  Taking your situation, I have 3 dataflows named A, B and C.

You can change whether it is on Success, Failure or Completion by right clicking on the lines and setting that value

I believe this gets you what you are looking for, at least for the scenario you have presented here.


I've never tried anything like that, but what if you UNION your three tables into one, and then do a "one-table hierarchical query" on the result of that?

WTIH alltabs as
    (Select 'T1' as src, Table1.ID, NULL AS ID_table1, NULL as ID_table2, Table1.name, Table1.size
    from Table1
    union
    Select 'T2' as src, Table2.ID, Table2.ID_table1, NULL as ID_table2, Table2.name, Table2.size
    from Table2
    union
    Select 'T3' as src, Table3.ID, Table3.ID_table1, Table3.ID_table2, Table3.name, Table3.size
    from Table3)
Select *
from alltabs
/*you can have an optional STARTING WITH clause here, but it's not clear from your question what that would be*/
connect by (id = id_table1 and (src = 'T1' or src = 'T2'))
        or (id = id_table2 AND (src = 'T2' or src = 'T3'))

(I'm assuming that the relationship is Table1 PARENT OF Table2 PARENT OF Table3).


If the master crashes, then it doesn't really matter what you do as far as stopping the slave, as long as you observe the slave once the master is back online, to verify that it has successfully started reading and executing events from the master again.

If you don't stop it, the slave should still be fine, and will sit and continually try to reconnect to the master based on master-retry-count and slave_net_timeout.  These retries are not going to have any significant performance impact on other queries running on the slave.  After enough retries, the slave will eventually give up, after which STOP SLAVE; followed by START SLAVE; should bring it back online again (or make it try again for the allocated number of retries).

If you take the master down for maintenance, the safest thing to do would arguably be to STOP SLAVE IO_THREAD; on the slave before shutting down the master, but if you don't do this, the master should shut down the slave's connection gracefully.  The slave will still continue to retry, but the retries as the slave attempts to reconnect aren't going to do any harm.

http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html


I wrote to the author, but I never received an answer. It seems the most recent version is for v9.7. The book can be obtained from db2hub or from the wayback machine. Anyway, I published all of them in my Google Drive:

7.2 https://drive.google.com/file/d/0B86nuTd5nMTKZzQtaTVTLUZ5ckE/edit?usp=docslist_api
8.2 https://drive.google.com/file/d/0B86nuTd5nMTKS3ItN25IX2djTGc/edit?usp=docslist_api
9.1 https://drive.google.com/file/d/0B86nuTd5nMTKNFR1SU9PZkN6MnM/edit?usp=docslist_api
9.5 https://drive.google.com/file/d/0B86nuTd5nMTKdzBkS01nUlZwYXc/edit?usp=docslist_api
9.7 https://drive.google.com/file/d/0B86nuTd5nMTKd190MFptUEtoYXc/edit?usp=docslist_api

For things like this, we, the DB2 community, should work on collaborative project with collaborative tools. Let's suppose Graeme Birchall does not longer work with DB2, no-one will continue this precious work.

I advise to all of you to create open things, like open documents, open source tools in public forges, etc. My personal work around db2 is published in GitHub, and I am convinced of creating free stuff.


The query processor could not produce a query plan

You could consider raising a connect item (Microsoft bug report) as you have a reliable "repro", but then there are a number of workarounds for the problem - eg there is  no real need for the APPLY, eg

;WITH TableA
AS
    (
    SELECT 101 as A_ID
    ), TableB
AS
    (SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses UNION ALL
    SELECT 2 , 101  , 'YYY'  UNION ALL
    SELECT 3 , 101  , 'ZZZ'  UNION ALL
    SELECT 4 , 102  , 'AAA'
    )
SELECT
     a.A_id,
    (
    SELECT ',' + B_Courses
    FROM TableB AS B
    WHERE B.B_A_ID  = A.A_ID
    FOR XML PATH(''),TYPE
    ).value('.', 'VARCHAR(20)')
FROM TableA A

NB These errors will also be generating 20MB stack dumps (.mdmp) files to your Log directory.


Pulling a username from table one to update credits on table 2 under the same username

Unless you only planning to hold one "Vote" (ever!), I'd suggest you're missing at least one table.

select * from Users ;
+----+----------+------------+
| id | forename | surname    |
+----+----------+------------+
|  1 | Fred     | Flintstone |
|  2 | Wilma    | Flintstone |
|  3 | Barney   | Rubble     |
|  4 | Betty    | Rubble     |
+----+----------+------------+

select * from Votes ;
+----+------------------------------+
| id | Vote Description             |
+----+------------------------------+
| 11 | Mid-Winter Festival Location |
+----+------------------------------+

select * from User_Votes ;
+-----+---------+---------+------------+
| id  | vote_id | user_id | Choice     |
+-----+---------+---------+------------+
| 111 |      11 |       1 | Bedrock    |
| 222 |      11 |       2 | Rock Vegas |
| 333 |      11 |       4 | Rock Vegas |
+-----+---------+---------+------------+

  I want SQL to pull the username from the "votes" table

The Username does not belong in the Votes table.
A User can exist without ever casting a vote, and so needs to exist in its own table.
Try to avoid using anything that might change as a Key.  Going around the database changing duplicated values all over the place is (a) Poor Design and (b) terrible in terms of performance.

  ... it will have to change the spaces to underscore and remove capital letters

You cannot query or join effectively if your data is "noisy".  This is another good reason to use a Surrogate Key (user_id) "inside" the database.  It eliminates all these Data Entry requirements (for this part of the job, anyway).
The User's name should be stored once and only once so that, when it needs changing, you change it in (one and only) one place.

  ... this keeps track of who voted ...

Using the tables I describe above, you get a list of who voted on this particular vote like this:

select u.forename, u.surname, v.choice
from Users u
inner join User_Votes uv
      on   u.id = uv.user_id
where uv.id = 11
order by 2, 1 ;

+----------+------------+------------+
| forename | surname    | choice     |
+----------+------------+------------+
| Fred     | Flintstone | Bedrock    |
| Wilma    | Flintstone | Rock Vegas |
| Betty    | Rubble     | Rock Vegas |
+----------+------------+------------+

(And, if you wanted those who haven't voted yet)

select u.forename, u.surname
from Users u
left join User_Votes uv
      on   u.id = uv.user_id
      and  uv.id = 11
where uv.user_id is null
order by 2, 1 ;

+----------+------------+
| forename | surname    |
+----------+------------+
| Barney   | Rubble     |
+----------+------------+


Where and how to store my weird datamart

My initial thoughts would be solution of two DBs per client, one for the datamart and one for the ODS. The ODS would be in full recovery mode as I don't think you can recreate the data here without retrieving it again from the upstream sources at your clients, if at all. The datamart may be in simple recovery mode as all the source data is coming from the ODS and can reloaded from scratch if needed, but the trickle-in fact table makes it complicated. I'm inclined to move the fact table into the ODS to have better options to enforce RI and other data cleansing needs before it goes to the datamart (via a DML trigger or seperate scheduled ETL perhaps). Also, if you can make the datamart DB read only in between ETL cycles, you'll benefit from not having SQL have to manage locks during the report requests.

I don't think you have an option to use a central schema/DB for the non-sensitive content. From a scalability perspective, having each client's data structures in their own containers allows you migration options to faster disks or another host if needed without impacting the other clients or sustaining a larger downtime. From a business logic perspective, if a particular client needs a business rule change that impacts the schema, will the central DB be able to accomodate it without negative impact? Also from scalability, think of the risks caused by locks on a table caused by one client impacting other clients concurrently connected.


What is the best practice location for Memorize Optimization (OLTP) files?

I think we are talking about In Memory OLTP ?
The files are necessary for disaster recovery
Once added they can't be removed without dropping the database !!!
You need x4 the amount of memory as disk files - if the disk files fill up your database will go suspect.
A fast disk subsystem is recommended.
I would advise careful testing before you deploy to production.
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/the-memory-optimized-filegroup?view=sql-server-ver15


Determine the source of user permissions in a database

Following is a partial solution that will return permissions assigned to objects in a given database to groups that a user is a member of either directly or indirectly.  The script loops through the groups the user is a member of, and gets all of the groups those groups are members of recursively to build a list that is used for a WHERE IN predicate.

The complexity of a full solution lies in the multiple ways permissions can be gained (through direct assignment to any number of groups, membership in roles, etc.)  For example, if you add an AD group to a custom role, then you have to check if that role has permissions.  Also this only shows assignments, not effective permissions, so if there are DENYs you have to take those into account to determine effective permissions.

You'll need the Active Directory PowerShell module installed for this, as well as the SQL Server client module.

$Username = "USERNAME"
$DomainName = "CONTOSO\"
$SqlServer = "SqlServerName"
$Database = "DatabaseName"

Function Get-ADGroupMemberOfRecursive{
Param([String]$Group)
    Begin{
        $Results += "'$DomainName" + $Group + "', "
    }
    Process{
            ForEach($Object in (get-adgroup $Group -properties memberof).memberof | get-adgroup | select name){
                Get-ADGroupMemberOfRecursive $Object.name
            }
    }
    End{
        $Results | Select -Unique | out-string
    }
}

ForEach($Object in (get-aduser $Username -properties memberof).memberof | get-adgroup){
    [string]$GroupList += Get-ADGroupMemberOfRecursive $Object.name
    }

$GroupList = $GroupList.substring(0, $GroupList.length - 4)

$Query = "SELECT `
        dp.class_desc `
       ,dp.permission_name `
       ,dp.state_desc `
       ,ObjectName = OBJECT_NAME(major_id) `
       ,GranteeName = grantee.name `
       ,GrantorName = grantor.name `
    FROM `
        sys.database_permissions dp `
        JOIN sys.database_principals grantee `
        ON dp.grantee_principal_id = grantee.principal_id `
        JOIN sys.database_principals grantor `
        ON dp.grantor_principal_id = grantor.principal_id `
    WHERE grantee.name IN (" + $GroupList + ")"

invoke-sqlcmd -serverinstance $SqlServer -Database $Database -Query $Query | ft


In the relation R2(A, G, I), since A, G is a candidate key of the original relation, a minimal cover of the dependencies is A, G -> I (there are no other non-trivial dependencies that hold in it).

Then, what happened to the dependencies C,G->I and C,G->H ? Well, they are lost in this decomposition (and if you try decomposing starting with dependencies different from A->B you can find that these or other dependencies are lost as well). From the practical point of view, this means that the constraints expressed by the lost dependencies cannot be enforced in the decomposed database.

On the other hand, it is a well known fact that the classical decomposition algorithms for  BCNF is not guaranteed to preserve the dependencies, differently from the synthesis algorithm for 3NF.


SQL Availability Groups: Can clients use cluster name instead of listener name(s)?

First, when using AGs, you generally are not using a clustered instance of SQL Server, you are using multiple standalone SQL Servers. You are able to technically connect to each individual SQL Server in the Windows Cluster Separately. Each having their own configuration and resources.

Secondly, each group has its own name in AD as a computer object and its own IP address. These are registered in DNS. These are different than the cluster name. The listener name is a special object to route traffic based on incoming query type. For instance, if you configured your AG to use a secondary node for read only queries, the listener name is what routes that query to the secondary node, not the cluster group. Backups are also attached to that listener name for routing traffic from the primary to the secondary node.

If you are monitoring AG health, it will be based off of the listener name/object, not the cluster name. It is possible to have a 3 node windows cluster, but only have an AG involving 2 nodes. The cluster name would not properly handle that.

Adding further complexity to this question. With SQL 2016, you have distributed availability groups which uses separate clusters on different networks or domains. Those separate clusters are clusters all to them selves and are only joined via the listener name for failover purposes.

Lastly, if you chose to extend your AG to Azure, you will need to use the listener when connecting to the "active" database so your query can route to the appropriate node. The cluster resource name does not know about the Azure secondary node.


SQL Query help: co-workers and employees from one query

select u.id, u.username, u.fullName
from user u
join user_employment e1 on e1.employee_id = u.id
join user_employment e2 on e2.employer_id = e1.employer_id
where (e1.end >= NOW() or e1.end is NULL)
       and (e2.end >= NOW() or e2.end is NULL)
       and e2.employee_id = 2
       and e1.status = "confirmed"
       and e2.status = "confirmed"
       and u.id <> 2
group by u.id;

http://sqlfiddle.com/#!9/b13e6/4

Find the employer of a given user and then find all (other) employees of that employer. Do not use left joins unless you need them (you do not in this case).


I have not personally tried this, but have a look the the Idera Permission Extractor:

Free Tool SQL Permissions Extractor

If you need a sql solution, there is nothing built in. However, you can query the sys.server_permissions and sys.database_permissions tables. They contain the information needed to build the grant statements yourself. sys.server_role_members and sys.database_role_members contain the information needed to generate role membership scripts.


Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well?

I don't think there is one for PG (though I think that pgAdmin will have something like this in the future).

If I need visualization of PostgreSQL data schema, I use the MySQL Workbench (I know, I wil burn in hell for this...). This works pretty good, though i throws some errors with SQL not known to MySQL (though this does not stop the process on the first error, it continues). This worked pretty well for me in the past.

Be warned though: many of the features only work with MySQL somewhere in the back or SQL generated by/for MySQL. This is only usefull if you want plain visualization.


GRANT permission ON database TO user not working on AWS

To user/privileges changes be reflected you need to run the following command:

FLUSH PRIVILEGES;

Then you can try SHOW GRANTS againg.


How to get aggregate data from a dynamic number of related rows in adjacent table

For a beginner in sql you sure picked some fairly advanced concept to start with ;-) Your question seems to boil down to what is known as island and gaps problems. I find it easiest to handle these kinds of problems by creating a group for consecutive events. One trick to accomplish this is to use two enumerations and calculate the difference:

row_number() over ( partition by player_id
                    order by match_id )

row_number() over ( partition by player_id, result
                    order by match_id )

if the difference between first and second grp changes it means that a change in result occurred. Since you will probably use this grp for several things I put it in a CTE:

with t as (
  select player_id, match_id, result, opponent_rank
       , row_number() over ( partition by player_id
                             order by match_id )
         -
         row_number() over ( partition by player_id, result
                             order by match_id ) as grp
  from matches
)
select player_id, match_id, result, opponent_rank, grp
     , count(1) over (partition by player_id, grp
                      order by match_id) as streak
     , avg(opponent_rank) over (partition by player_id, grp) as avg_rnk
from t
where result = 'w' and player_id = 82
order by player_id, match_id;

I believe this should give you a start, so I'll stop there. Welcome to the forum btw.


Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of data size?

There's nothing specifically quantifiable about the abstract concepts of Transform and Load, only their concrete implementations are measurable. To be able to comment on your case, we'd need to know specifically what your Transformation and Loading processes are actually doing. Obviously some Transformations can take multitudes longer than others.
But generally speaking, the amount of data being processed definitely should affect the overall runtime of an ETL process. If there's a significant difference in the amount of data between a 24 hour timeframe vs a 15 minute timeframe, yet your ETL process is executing at roughly the same average runtime for both cases, then something is definitely suspect, and that's not normal.
Even if an index scan is occuring in both cases, if there's a significant difference in the amount of data, the total runtime should certainly reflect it. The runtime of an index scan is linear (generally speaking) based on the number of rows in the index.


Is there a concept of an "include file" in SQL Server?

If you are using SQLCMD, you can use :r FileName to include a separate .sql file.

  :r FileName

  Parses additional T-SQL statements and SQLCMD commands from the file
  specified by FileName into the statement cache. FileName is read
  relative to the startup directory for Visual Studio. The file is read
  and executed after a batch terminator is encountered. You can issue
  multiple :r commands. The file can include any SQLCMD command,
  including the batch terminator that is defined in Tools, Options. You
  can use :r in a pre-deployment or post-deployment script to include
  other scripts.


What is the Maximum Merge Articles in Merge Replication

The first link you provided is official documentation for SQL Server 2012.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

The maximum number of articles supported in Merge Replication is 256.  This means that Microsoft will ensure up to 256 articles will work as expected.  I'm able to exceed 256 articles as well but I wouldn't do it in production.  You may want to consider splitting this up into multiple publications for production purposes.


Modifying the Contents of SQL Server Agent Alerts

There is no way to directly modify the subject and body fields of the email alerts generated by SQL Server Agent.

Having the alert run a job instead of directly generating an alert email would be the correct way of getting to your desired state.  Having said that, it's not necessary to use PowerShell for that if you don't want to.

Unfortunately, there does not appear to be a way to pass parameters into the job from the alert.  This means you'd need to define a separate job for each alert you have configured.


As you may know, pt-online-schema-change works in this way:

Creates a new table identical to the original one, but empty
Alters the new table
Creates triggers on the original table to start keeping the tables in sync (update/delete of rows already copied, insert of new rows)
Copies the rows in bunches
Swap the table names

So triggers are a fundamental part of the process (unless the table is readonly or you are definitely sure that no one is going to modify it now). The problem is that, before MySQL 5.8 and MariaDB 10.3, you can have only one triggers for each combination of table + timing + event. For example, your table can have only one trigger BEFORE UPDATE.

One thing you can try is, turn your existing BEFORE UPDATE triggers to AFTER UPDATE - unless you already have an AFTER UPDATE trigger, this will do the trick. And do the same with INSERT and DELETE triggers.

Or try to use gh-ost from GitHub, as someone already suggested. This would work because this tool doesn't create any triggers: instead, it reads the binary log and applies the changes to the new table manually.


(Moved my comment to an answer, and expanded a bit.)

  I cannot create indexed views as I've some calculations as well like SUM(Volume * CaseCount) AS SomeColumn which is not allowed in indexed views and also it's a dynamic query and grouping and joins are based on user input.

From the plan you provided, the clustered index scan is 3.5x overestimated, and the Sort is an order of magnitude underestimated which caused a spill to tempdb. Performance may improve after updating statistics.

However, based on your reply, if this schema is servicing "random" queries against that amount of data, it's going to be extremely challenging and/or expensive to get good performance for all the possible combinations.

For this type of workload, I think the only realistic way to meet the performance goal would be to abandon trying to find a database engine solution, and use Analysis Services instead.


Can an Oracle 19c database dblink to an Oracle 10.2.3 database?

Based on this document from Oracle Support, the answer appears to be "No". A 19c client (the owner of your DB Link) is only supported/compatible with a server version of 11.2 or greater.
Conversely, a 10.2 database can only support a client as new as 12.1, so any upgrade of your 12.1 database to any newer version will break your db link.


Why is SQL*Plus' desc table not showing not null constraints after using dbms_redefinition

This is a known bug (description is not public unfortunately):

Bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF

The NOT NULL constraints are copied as NOVALIDATE, and you have to set them to VALIDATE state manually, e.g:

ALTER TABLE t84_redefenition ENABLE VALIDATE CONSTRAINT constraint_name;

The primary key issue is similar, but there is no bug reported for that. However I have found that disabling and reenabling it solves the problem.

ALTER TABLE t84_redefinition DISABLE PRIMARY KEY;
ALTER TABLE t84_redefinition ENABLE PRIMARY KEY;


Facing error while working with Aggregate function in MDX (MultiDimensional-Expresson)

Look again at the details for the Aggregate() function. You got the parameters correct, but you need to look at what the function will return. It returns a numeric value.

So your MDX evaluates as SELECT some-numeric-value ON 0 FROM [Cube] which is not valid.

You need SELECT some-sort-of-set ON 0 FROM [Cube]

Try googling for examples of using this function (or see link above, which includes examples) to see how it can be used.


What options in /etc/mysql/my.cnf are applicable to percona-server-client-5.6?

Those other sections are there because they are included in the template cnf file from the package provider (Percona).

You can create a user specific cnf file in your home dir that looks like the one below and they will get used when you run the mysql command:

[client]
user=$myusername
password=$mypassword

n.b. if you're going to store your credentials in a cnf file make sure you  set the permissions to 400.


In your case you could not use transportable tablespace because the source database have to be Enterprise edition.

In general, I would prefer upgrade using rman. But in your case it is not so easy because direct upgrade from 10.2.0.4 to 12.1.0.2 is not supported.
You would have to upgrade to intermediate release (e.g. 10.2.0.5) that can be directly upgraded to 12.1.0.2.
See https://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD12359

So for you the best approach will be upgrade using old import (imp) or data pump import (impdp).

I have one recipe using old import. Upgrade using data pump import will be similar:

Assume the source database name is "sourcedb" and target "targetdb".

make full export on source database:

  set oracle_home=c:\oracle\product\10.2.0\db_1

  set oracle_sid=sourcedb

  c:\oracle\product\10.2.0\db_1\bin\exp system/pass buffer=1200000 file=d:\dump\full_sourcedb.dmp log=full_sourcedb.log full=y
  consistent=y

  copy full_sourcedb.dmp to the target machine

create new target database "targetdb" using dbca (custom database - select or remove all components what you need oracle spatial etc.)

choose the right character set. You can find out character set on your sourcedb by this query:

  select parameter, value from nls_database_parameters where parameter
  like '%CHARACTERSET%';

on the target machine run following command. It will produce the log with sql stamenets used by import, where you can find the create statements for creating tablespaces.
You have to edit them (with new datafiles locations) and run manually before import because old import cannot remap datafiles:

  create tablespace "TEST" blocksize 8192 datafile
  'd:\oradata\targetdb\test01.ora' size 2000m       extent management
  local  autoallocate  online permanent  nologging segment space
  management auto;

  etc.

after all tablespaces are created, you can run import on the target machine:

  imp system/pass full=y log=d:\dump\full_import_sourcedb.log
  file=d:\dump\full_sourcedb.dmp

check out d:\dump\full_import_sourcedb.log for errors

after sucessful import on the target machine:

  --recompile objects

  @%ORACLE_HOME%/rdbms/admin/utlrp.sql

  --check out all database components

  select  owner, object_type, object_name from dba_objects where status != 'VALID' order by 1,2,3;

  --gather statistics
       shutdown immediate startup

  begin
   dbms_stats.gather_database_stats(
            method_opt => 'FOR ALL COLUMNS SIZE AUTO',
            estimate_percent => 100,
            cascade => TRUE   );

  end;

  /

Note:
import using data pump import (expdp, impdp) may be easier:

instead of consistent=y you have to use flashback_time="to_timestamp('04-04-2016 11:55:00', 'DD-MM-YYYY HH24:MI:SS')" .........

During import you don't have create tablespaces manually and you can use remap_datafile parameter.


SSMS clean output window with sql instruction

This is not possible to do within the context of a single execution of whatever is in the Query pane. T-SQL commands are not interpreted by SSMS. They are sent to SQL Server to be processed. Only a GO [execution_count] statement (batch separator, GO being the default but can be changed) and a subset of the SQLCMD commands -- !!, :exit, :quit, :r, :setvar, :connect, :on error, :error, and :out -- are captured and processed by SSMS (and are not sent to SQL Server).

Also, there aren't even any internal SSMS commands to clear either the Results tab or the Messages tab.

So, if there are any results being returned, they will be in the Results tab at the end of the execution no matter what.

BUT, after that execution ends, you can manually clear both the "Results" and "Messages" tabs by highlighting an empty line (or even just a space on a non-empty line) and hitting F5 or Control + E.

OR, depending on why this is being requested, you can hide the "Results" and "Messages" tabs by hitting Control + R.


An option would be to create an after insert/update trigger which would join the base users table with the virtual inserted table and force the PII columns to be null (or other scrubbing logic).  Here is a very simple example:

--create test table
drop table if exists [dbo].[UserTable]
CREATE TABLE [dbo].[UserTable] (
    [id] [int] NULL
    ,[BirthDate] DATE NULL
    ,[Street1] VARCHAR(30) NULL
    ,[Street2] VARCHAR(30) NULL
    ,[SSN] VARCHAR(9) NULL
    ,[OtherColumn1] VARCHAR(100)
    ,[OtherColumn2] VARCHAR(100)
    ,[OtherColumn3] VARCHAR(100)
    ,
    ) ON [PRIMARY]
GO

--create after insert/update trigger to force PII fields to null
CREATE TRIGGER [dbo].[UserTable_InsertedOrUpdated] ON [dbo].[UserTable]
AFTER INSERT
    ,UPDATE
AS
BEGIN
    SET NOCOUNT ON

    UPDATE ut
    SET ut.birthdate = NULL
        ,ut.Street1 = NULL
        ,ut.Street2 = NULL
        ,ut.SSN = null
    FROM UserTable ut
    JOIN inserted i ON i.id = ut.id
END
GO

--test an insert
INSERT INTO UserTable (id,Birthdate,Street1,street2,SSN,OtherColumn1,OtherColumn2,OtherColumn3)
VALUES (1,'1960-01-01','Street1','Street2','111111111','OtherColumn1','OtherColumn2','OtherColumn3')
INSERT INTO UserTable (id,Birthdate,Street1,street2,SSN,OtherColumn1,OtherColumn2,OtherColumn3)
VALUES (2,'1960-01-01','Street1','Street2','111111111','OtherColumn1','OtherColumn2','OtherColumn3')

--test an update
UPDATE UserTable
SET SSN = '999' where id = 1

--check the contents of the table
SELECT *
FROM UserTable


Oracle Query execution finishes first time but hangs up from second run

This is quite typical on 11.2 because of a feature called "cardinality feedback". Check the execution plan of the slow runs, and if you see "cardinality feedback" in the Notes section of the execution plan, try setting the below parameter: alter session set "_optimizer_use_feedback"=false;, and run your tests again.

Some bugs related to this:

Bug 12557401 - Suboptimal plan on 2nd execution caused by cardinality feedback (Doc ID 12557401.8)

Bug 16837274 - Cardinality feedback produces poor subsequent plan (Doc ID 16837274.8)

Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback (Doc ID 8608703.8)

Bug 8521689 - SubOptimal execution plan on second execution of GROUP BY query (Doc ID 8521689.8)


How to use 2 auto increment columns in MySQL phpmyadmin

No, you can't. Not out of the box. Possible workarounds:

Triggers (an AFTER INSERT trigger).

Disadvantages:

Plain horror. Comes with all the other disadvantages of triggers, like maintenance and debugging nightmares.

Advantages:

You can have FOREIGN KEY constraints that reference this column.
You can update the first and the second id columns individually.

Views. If you only a value that is always +4000400 of the first auto incremented value, you can use a view, so basically not store this value at all, just calculate it when you need it:

    CREATE VIEW
        tablex_with_2nd_AI AS
    SELECT
        tablex_id,
        colA,                                 -- other columns
        --                                    -- you need
        tablex_ix + 4000400  AS second_id
    FROM
        tablex ;

Disadvantages:

You can't have a FOREIGN KEY constraint that references this (virtual) column.

Advantages:

Saves some space. Slightly faster inserts.

Don't do that, don't have a second auto incremented column at all. Do you really need a second auto incremented value? What for? A description of the actual problem you are trying to solve would help others help you better. I think you have only told here how you tried to solve a problem and not what the actual problem is.


I'd like to make this relational database into a star schema. Any help is appreciated

You could add a new row to your store dimension table for „website“. And add a column StoreType that says „Physical“ and „Web“ for instance. Then all sales go into the same fact table, no matter whether they occur in a physical store or online.


Do dormant databases impact server performance?

Yes, you are right. Memory is used by dormant databases

I mentioned this in the following posts over the years

Nov 13, 2015 : #1041 - Out of memory issue in mysql
Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
Apr 21, 2014 : Adding new tables -- memory usage increases

Lots of tables ? Lots of columns ? Of course, that is less memory for mysqld to work with.

You just asked

  Is there a way to ensure a database is using no memory?

If you never access a table, no data or index pages will ever touch the InnoDB Buffer Pool, the MyISAM Key Cache, or any session's read_buffer or read_rnd_buffer.

Notwithstanding, having too many tables with lots of columns will result in nothing more that a lot of dormant information_schema memory. Neither the number of rows in each table nor the size of any of the tables matter.


Auto Increment Index Field For Create and Update

You need to combine a sequence with some mechanism to trigger the increment for an update. For postgres

create table my_table(id serial primary key, description text, touched serial);

Then either:

create a trigger to set touched = default on an update
or
get all your updates going through an API that also actions the increment

auto-increment is implemented differently for SQLite so you will have to work around that - perhaps by inserting/deleting a new row each time you update a row, and using the rowid of that temporary row to update touched.


My suspicion here is that your database is still running in SQL Server 2000 compatibility mode. Right click on the database, go to properties and then options and see. If that is the case you'll have to look at the index usage statistics manually with queries, certain constructs used in these reports and using operators like the APPLY operator won't work against DBs in 2000 compatibility mode. You should consider bringing up to 2005/2008 mode but this needs to be a dev project and properly tested/vetted in a pre-production environment.

If it isn't the compatibility mode, I'd also look at what version of SSMS you are running and what version/SP the database engine is. If they aren't the same this could also be a part of the issue.. Help about on SSMS will show it's version and may show the instance version but you can also run Select @@version to get the build number of your instance for sure.


Insert missing rows to subscriber of Transactional Replication

You will need to run a validation to determine how out of sync your publisher and subscriber are.  Then, use the tablediff utility or Red Gate SQL Data Compare to generate the script(s) necessary to get the subscriber back in sync.  Then apply the script(s) at subscriber to bring the data back into convergence.


How to sort the result based on a certain column when using GROUP BY in sql statement

Try adding the Time field to your select list, as in:

SELECT Seller, Max(Time) AS MostRecentTime
FROM tFruit
WHERE Buyer = 'email@address.com'
GROUP BY Seller
ORDER BY Max(Time) DESC;

Max(Time) as MostRecentTime will display the most recent Time for the given Seller.  You don't really need this in the SELECT clause, you really only need MAX(Time) in the ORDER BY clause.  I've added it to the SELECT clause so you can see the results are sorted by Time in descending order.


Configure MariaDB to log row deletion ONLY

This is mostly copied from the docs:

Trigger Example - Logging deletions

  Scenario: We want to keep a log file containing data from rows that have been deleted from the BOOKS Table. Here's a Trigger definition that accomplishes this:

CREATE TRIGGER Books_Delete
AFTER DELETE ON Books                 /* See note 1 */
  REFERENCING OLD ROW AS Old          /* See note 2 */
FOR EACH ROW                          /* See note 3 */
  INSERT INTO Books_Deleted_Log       /* See note 4 */
      VALUES (Old.title);             /* See note 5 */

The Trigger action has to be AFTER, since the Trigger action includes a SQL-data change statement. You should also replace Books with your table name
It is conventional to use the alias "Old" or "Old_Row" for the old row.
No log will occur for a DELETE statement that affects zero rows.
Replace Books_Deleted_Log with the name of the table you're logging the deleted rows into. Make sure this table has all the columns you need to log the information you need.
OLD is an alias for a single old row, so OLD.TITLE is the scalar value derived from the TITLE Column of that old row. You will have to change Title to a column you want to track. You can also add more columns by following the Old.<column name> pattern using commas as separators.


Troubleshooting error message Invalid Object Name accessing MS SQL with python

Every table belongs to a schema
you yourself used an example - [Data].[Version]
Data is the Schema and Version is the Object name
So you need to find out which schema MyTable belongs to and supply it in your query.
[Data].[MyTable] for example.
(The Object browser in Management Studio is your friend)
Good to know. Every User has a Default Schema and the default default schema is dbo
Therefore if the Table belongs to the dbo schema and the user has the default default schema it is a shorthand to write MyTable.

The master database is a system database so looks like that might be where your problem is. You could try 3 part naming
dbname.schemaname.tablename
or try running a query "use dbname" which should change your context to the right db
But dig into why you are connecting to master instead of the database you specify in the connection string (might be a type, lack of permissions or many other things)


SQL 14 AG go into read only when cluster is down, how?

You have to set it up for all communication. If the WSFC is down, so is the AG and Listener. If you can directly access the secondary and it's set up, you can access it for readable.


How to merge consecutive record pairs over a table?

Basically, use common table expressions to isolate and number rows in 2 groups with one helper column NULL in each, and then join the two groups on row numbers.

WITH cte1 AS (
    SELECT [id], [data], [from] , ROW_NUMBER() OVER(ORDER BY [id], [data], [from]) AS [row]
        FROM #temp3
        WHERE [prevfrom] IS NULL),
    cte2 AS (
    SELECT [to], ROW_NUMBER() OVER(ORDER BY [id], [data], [from]) AS [row]
        FROM #temp3
        WHERE [nextto] IS NULL)
SELECT t1.id, t1.data, t1.from, t2.to
    FROM cte1 AS t1
    JOIN cte2 AS t2
    ON t1.[row]=t2.[row];


Found explanation.

Now() contains timestamp of transaction start.

It is necessary to use TIMEOFDAY().


Does mysqld wait for init-script code to complete before opening the instance?

Trying to Understand #1

If I put a procedure call (or whatever application code) in the init-file, will it be executed as expected (in my test case the sleep call seems to be skipped)?

Yes, it will. In the past, I have recommended using init-file to create dedicated keycaches for MyISAM tables:

Aug 28, 2012 : MySQL InnoDB Index in swap (See Suggestion #3)
Mar 20, 2012 : optimal table design mysql with primay key and varchar value

I am currently do this for clients at my employer's company.
Trying to Understand #2

Could I have some kind of logging of what gets executed (apart from modifying the code itself)?

Unfortunately, no. You would have one of the following:

Write changes to some MyISAM table to mark stages
Run something like SELECT 'DEBUG#1;' ... SELECT 'DEBUG#2;' to see if it echoes to the error file. Place these SELECT queries in different places within your code.

Trying to Understand #3

If the code in the init-file takes time, could that ? affect the availability of the MySQL instance right after the restart

Just from the example you posted in the question, they answer would still be no. However, in order for to be sure, you will have to let mysqld tell you this. Assuming /var/log/mysqld.log is your error log, here is how to check:

Open an SSH seesion (Session1) to DB Server
Open an SSH seesion (Session2) to DB Server
In Session1, service mysql stop
In Session1, tail -f /var/log/mysqld.log
In Session2, service mysql start

Watch the output of Session1. If you see something like
121018 17:59:09 [Note] /usr/sbin/mysqld: ready for connections.

in under 5 minutes (300 seconds), this then confirms that the answer is no, the availability of the MySQL instance right after the restart is not affected.
Why does SLEEP() not work ???
As to why the SLEEP function seems to be skipped, here is my best guess:
Way back in MySQL 5.0.12, SLEEP was getting inserted into the query cache via some optimization. It could be possible that the Stored Procedure's internal code is being optimized and mysqld decided not to let SLEEP interfere with the formation of the EXPLAIN plan and its execution.
The patch for it was applied when back in MySQL 5.0. Could that patch have been missed when going from MySQL 5.0 to MySQL 5.1 and beyond? That's one possibility. Another possibility is that SLEEP is optimized away in spite of the patch.
Some use SLEEP to imbed mutexing, perform stress testing, or fancy job scheduling

https://stackoverflow.com/q/4284524/491757
https://stackoverflow.com/q/949950/491757
http://forums.mysql.com/read.php?98,242758,242792#msg-242792
http://rpbouman.blogspot.com/2005/10/scheduling-procedure-execution-in.html

Using SLEEP does not always perform as expected.
UPDATE 2012-11-27 15:50 EDT
If you really want to control the SLEEP function, DO NOT USE THE FUNCTION !!!
Create a loop that checks for the passing of time
Instead of your code
delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  do sleep(300);
  insert into init_test_tab VALUES(p);
END
$$

imbed the loop like this:
delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  declare dt_now,dt_then datetime;
  declare x int;

  set dt_now = now();
  set dt_then = dt_now + interval 300 second;
  while dt_now < dt_then do
    set dt_now = now();
  end while;

  insert into init_test_tab VALUES(p);
END
$$

Please see if that construct work for you.