Adding columns from joined table slows down query incredibly

A RID Lookup is a lookup into a heap table using a Row ID. The Row ID is included in a non-clustered index in order to find the rest of a table's data in the heap table. Since a heap table is a table without a clustered index and is sorted unordered, a Row ID is required for the correlation.

My guess is that view3.UID is found easily via a non-clustered index with that column.  When you actually start asking for other columns (by specifying them in the SELECT list that are not part of that non-clustered index, Sql Server has to go find the other data items in the unordered heap - that's the RID Lookup - which can be quite expensive depending on the amount of data you're dealing with.

You 'might' see improved performance if you can identify the 'source' tables referenced in view3 and possibly 'including' your SELECT columns in supporting indexes on those source tables - this is called a 'covering index' in that the index is able to 'cover' or retrieve all of the information you're asking for without having to go elsewhere (clustered-index or heap)

This link has some good information on RID Lookups.

Here is a brief summary of the main points of the link:

  When you find an RID Lookup in a query plan, it’s a symptom. It
  indicates a database schema that breaks several rules of thumb. Those
  rules-of-thumb are:

  Each table should have a clustered index (of course there are    exceptions but we’re dealing with rules-of-thumb here).
  A non-clustered index has been created indicating that someone somewhere identified an ordering on one or more columns that made
  sense for that data.
  There is at least one query (i.e. the one that generated the RID Lookup) that needs columns that are not covered by the non-clustered
  index.

Something else to 'try' - use a Common Table Expression (CTE) in an attempt to force Sql Server to 'materialize' view3.  Before your main SELECT, add the CTE - this 'assumes' that no more than 2147483648 (max int value) could be returned - adjust as needed.

with View3Materialzed as
(
select top 2147483648 UID from view3 order by UID
)

Then, use the CTE View3Materialzed in the regular joins.  I have used this technique with some success.  It may or may not help you.


Why does MongoDB still support MapReduce, but actively discourage people from using it?

The answer is no, the aggregation framework does not replace "every conceivable" MapReduce configuration. Without becoming a general purpose MapReduce framework with fully customizable functions, it can't. That's what you get with the existing MapReduce functionality - any functions you want (as long as they can be written in Javascripts of course).
I haven't used it in a long time (years), but it is not just because of the use of the javascript engine that the MapReduce funcionality is slower, it still does things like taking a global write lock (docs) when writing output which will block all other operations on a mongod - ouch. Even several version prior to this, when the Aggregation Framework was nowhere near as featureful as it is now the advice was: if you can use the aggregation framework, do so, only use MapReduce if you have no other choice.


MySQL: Shouldn't these two DATE queries be the same?

AND  DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR)

is a DATETIME, which might be in the middle of today.
BETWEEN (CURDATE() - INTERVAL 5 DAY) AND CURDATE()

excludes anything after midnight this morning.
Please provide the order that is miscategorized.


Use data from Mysql to ElasticSearch with Logstash

I found that ELK LogStash 6.X have document_id deprecated, so if I use my configuration without document_id it works.


How can I lock row for reading/updating in DB2?

Why are you using a table?  The IBM i provides a native data queue object...

If your application is running off platform in a language that doesn't offer direct access to the native objects, you could always build a stored procedure or user defined function on the I to provide access to the data queue.

Having said that, the answer to your question is the same for any RDBMS, use isolation levels.

In your case, you want to use Read Stability

  Like level RR, level Read Stability (RS) ensures that:

  Any row read during a unit of work is not changed by other activation
  groups that use different commitment definitions until the unit of
  work is complete. 1 Any row changed (or a row that is currently locked
  with an UPDATE row lock) by another activation group using a different
  commitment definition cannot be read until it is committed.

Simply add WITH RS to your statements...

SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL WITH RS LIMIT 5
UPDATE JOBS LOCKED_BY = 'Thread#1' WHERE ID IN (?) WITH RS
COMMIT

The first statement will lock (up to) 5 rows..till the COMMIT is done.

Note that the table will need to be journaled if it is not already.


MYSQL count the number of members in a group

How about this?

UPDATE clanlist AS c SET members =
(
  SELECT COUNT(*) FROM clanusers AS u WHERE u.clanname = c.clanname
)


SELECT COUNT in same table based on current row value within a single query


The certificate, asymmetric key, or private key file is not valid or does not exist

Here are the steps that I had to take to restore the encrypted database onto a different server(localhost).

1.Create the certificates :

CREATE CERTIFICATE [CertficateName]
FROM FILE = 'C:\FolderName\NameOfCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\FolderName\NameOfCertKey.key' ,
    DECRYPTION BY PASSWORD = 'YourPassword'
);

2.

USE Master ;
Open Master Key Decryption by password = 'YourPassword'
Backup master key to file = 'C:\SQL FodlerName\MasterKeyName.key'
        ENCRYPTION BY PASSWORD = 'YourPassword';
    GO

3.Restore Master Key

Use master
    restore master key
    FROM FILE = 'C:\FolderName\MasterKeyName.key'
    DECRYPTION BY PASSWORD = 'YourPassword'
    ENCRYPTION BY PASSWORD = 'YourPassword'

4.
This is the last step you ,be careful at this stage as it took me a while to get that each command needs to be run separately :

Alter Database [DatabaseName]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

Next run it separatly

USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';

Next command run separatly

RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\Folder\FULL\NameoftheBakFilethat ourAreRestoring.BAK'
WITH Replace , STATS = 5

Do not forget to set back to Multi-User mode the Dabase.


Best approach to maintain many dump for a single structure

Always the best practice is to take each database's backup with a separate file.
Why?

You can restore any database to anywhere.
It'll help to refresh you Dev database.
It'll help in PITR.
You can restore any specific table from the specific database backup.
Finally it will save a lot of time while restoring a single Database.

Recomendation:
Keep you database backup in multiple locations. This will help you in case of any disaster.

SAN
NFS
S3
Or any other location apart from the Database server.


how would i merge a column where matches a different column?

You need to apply update with join if you want to update the cust table

update cust c
join user u on u.customers_firstname = c.customers_firstname
and u.customers_lastname = c.customers_lastname
set c.customers_id = u.customers_id


Performance of bitwise operators on byte column vs individual bit columns

The answer to any performance question is "it depends."  Discrete columns can be fast and byte column flags can be fast too.  In absolute terms you can probably save a bitwise operation by having discrete columns here and there so discrete columns are theoretically faster.

A theoretical bump shouldn't be the main reason to choose a strategy. To paraphrase Jeff Atwood "storage is cheap and BDA's are expensive."  I would avoid the flags compressed into a byte column for the simple reason that it makes your life more complicated by making your data more cryptic.  Discrete columns will be straightforward to query, filter properly, debug, and pass on to future teammates.

For completeness there are other options than bit flags, or discrete columns.  One other option is the EAV (entity-attribute-value) model, even though it doesn't feel like this is your best option based on your description.

  Entity–attribute–value model (EAV) is a data model to describe
  entities where the number of attributes (properties, parameters) that
  can be used to describe them is potentially vast, but the number that
  will actually apply to a given entity is relatively modest.


Another level of indirection to the rescue!

Add a Week table, containing the date of the first day of the week and whatever else seems sensible (week number, Year, etc), with one row per week. The Paycheck table has an FK pointing to the Week table.

Add a Day table. This has one row for each day, and contains the Date and an FK to the relevant Week table. It might also have indicators that this is a weekend or national holiday, and so different pay-rates might apply.

Add a table PaycheckDay. This has FK's for the Paycheck row and the appropriate Day row. These two FK's should also be a unique non-clustered index. Pay details for each day are stored on this row. For extra security there could also be constraint that the Day belongs to the same Week that the Paycheck does (also this might be enforced by a Trigger rather than a constraint depending on physical database type).


IMHO the easiest way to get an OWA application to work is by installing a standard Apache distribution and the open source mod_owa.

This is easiest on modern 64bit Windows, downloading Apache 2.4 from http://www.apachelounge.com/download/ and mod_owa from https://oss.oracle.com/projects/mod_owa/dist/documentation/modowa.htm


SQL query to get the syntax of an existing table

You can use SHOW CREATE TABLE <table_name>; to get CREATE TABLE statement.
  Eg. SHOW CREATE TABLE acl_user_role; This will show you the table name and CREATE TABLE statement, something like:

CREATE TABLE `acl_user_role` (
  `UserId` INT(10) UNSIGNED NOT NULL,
  `RoleId` SMALLINT(5) UNSIGNED NOT NULL,
  PRIMARY KEY (`UserId`,`RoleId`),
  KEY `FK_UserRole_Role` (`RoleId`),
  CONSTRAINT `FK_UserRole_Role` FOREIGN KEY (`RoleId`) REFERENCES `acl_role` (`RoleId`),
  CONSTRAINT `FK_UserRole_User` FOREIGN KEY (`UserId`) REFERENCES `user` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

  If you want to know about Keys for a particular table, use something like SHOW KEYS FROM acl_user_role;

And if you want to get all keys and constraints referencing to other table (like foreign key references), use:

SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = '<your_table_name>' AND TABLE_SCHEMA = '<your_database_name>';

-- Example
SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = 'acl_user_role' AND TABLE_SCHEMA = 'db_shop';

Hope this may help you...


Designing a database tables for the following scenario

Assuming a company can be "attended to" by more than one employee, and likewise for client, you would model like this:

So both relations are 'many:many'. As gbn also said, adding "comma separated ids" would be something of a disaster in database design: you would regret it many times over.


Database for efficient retrieval of data from large data set

All you need here is to make the username a unique index and MySQL will (generally) handle this very well.

Unfortunately, I had to write 'generally' because there can be many many other factors on your system that could slow this down. I recommend having MySQL 5.5 and the table be InnoDB. Proper indexing and configuration will go a long way in helping you.

The short of it is, MySQL/InnoDB is easily able to handle 10 million rows.


what's extsort.* (8 - 11 MB) in MongoDB dbpath? Directory _tmp

These files are used when the sort you are attempting in an aggregation exceeds the maximum allowed memory and you have allowDiskUse : true specified.

The relevant pieces of the code are here (where the SortedFileWriter is called) and here (where the extsorter.# files are written to temp by SortedFileWriter). Basically, this is the number of files required by MongoDB to perform the sorting that is being specified in your aggregations.


What is the best way to upgrade a MySQL version in a Linux Server

In most cases, in-place binary upgrade of MySQL 5.5 to MySQL 5.6 is possible without many problems. This is too small to detail all potential issues while performing the upgrade, but I will leave you the general steps:

Clone the MySQL server to a testing environment. I do not believe that you do not have 15GB on your laptop to test the upgrade. Worst case scenario, you could test it on the production machine, on a separate instance running on a different datadir, socket and port. Percona XtraBackup can be useful to create a MySQL clone with (almost) no downtime in a fast an efficient way.
Perform a test upgrade on your test machine. This is as "easy" as stopping the server, installing the new binary and restarting your environment. Just with this, you will find out about deprecated options that you will have to change in order avoid errors and warnings. Monitor the error log for that.
Once MySQL is up and running, you will need to execute mysql_upgrade from the 5.6 binaries. This is essential, or MySQL may crash. This will fix the system and privilege tables, and check if some tables need repair. Do as such (REPAIR TABLE ... for MyISAM, ALTER TABLE ENGINE=InnoDB, ALGORITHM=COPY for InnoDB tables). This will probably be the case at least for some time-related tables. You may even want to do that in some cases to get advantage of some of the new MySQL features. Make sure you read the documentation in order to know which variables and table structure may want to change on the new MySQL version. pt-config-diff is a great tool to identify differences on variable values between servers.
Now you can test freely for application incompatibilities: syntax changes and performance regressions (specially optimizer changes). As with any non-trivial upgrade, for every 10 queries that perform automagically better, you will find one that perform worse. Identify the underlying cause and fix your indexes/queries/table structure/configuration. You will find that 5.6 will perform generally better for subqueries, but I have identified certain default configuration change that may affect negatively your performance in some cases. pt-upgrade is a great open source tool to detect performance regressions and incompatibilities.
If you had enough hardware, maintaining the clone up-to-date with the MySQL replication would be ideal. Not only makes the process less painful, it allows to validate the upgrade by applying the master changes one by one. Remember that you still have the option of the 2 instances on the same machine, so do not just discard this option!
Once all testing has been successfully done, and assuming you have documented all the gotchas, you can proceed to perform the real upgrade. No surprises should happen now, if you have done your homework. Just 2 warnings:

Make sure that you perform a backup just after going into maintenance mode
Make sure you change the configuration so that a version rollback is possible (innodb_checksums, binlog options)

If everything works ok, you should just set mysql as read_only (and make the application fail gracefully), stop mysql, binaries upgrade, change configuration with the tested one, start mysql, mysql_upgrade, table repairs, additional fixes needed, battery of tests, monitor the error and slow log, read_only =0, and up again. Be careful, becase after a restart, the buffer pool will be completely empty (something that was fixed in 5.6 :-)), making the new queries very slow. It may take hours for the queries to fill the buffer again- there are ways to mitigate that problem.

There are cases in which an SQL dump may be needed or a better options, and there are some very-well-know issues that you may find on upgrade, but it is impossible to tell you absolutely everything you may find. Just read the official documentation on this and give me a ping if you need additional help.


Large difference in disk space used by partitions

What might cause the big discrepancy in disk space?

Typically this is caused by rows that have been deleted. Heaps do not reclaim space for data pages emptied by DELETE operations unless a table lock is taken at the time of the deletion. Even then, other factors like the possibility of row-overflow data, or an enabled row-versioning isolation level can prevent space being reclaimed.

Another factor that usually has a much smaller impact on space usage is that rows with variable-length columns that expand may not fit on the original heap page, in which case the row is forwarded to another page. Forwarded rows are more usually associated with scan performance issues than pure space usage, however.

Overall, tables that experience significant deletions (without the space being reused quickly by new rows) and/or variable column updates that do not fit in place are often better configured with a clustered index. Clustered tables usually deallocate empty pages very quickly.

You can use the sys.dm_db_index_physical_stats DMV to see physical details for a heap or index:

SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.avg_fragmentation_in_percent,
    DDIPS.fragment_count,
    DDIPS.avg_fragment_size_in_pages,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent,
    DDIPS.record_count,
    DDIPS.avg_record_size_in_bytes,
    DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.t1', N'U'),
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;

The following (trivial) example shows a heap DELETE not releasing any empty pages:

SET NOCOUNT ON;
CREATE TABLE t1 (c1 char(8000) DEFAULT 'a');
GO
INSERT INTO t1 DEFAULT VALUES;
GO 1000

-- 1000 pages allocated
SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.t1', N'U'),
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;

-- Delete all the data from the heap
DELETE FROM t1;

-- Still 1000 pages allocated
-- Even though the table is empty
SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.t1', N'U'),
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;
GO
DROP TABLE dbo.t1;

If you try the example again, but with a table lock (DELETE FROM t1 WITH (TABLOCK)) the deletion frees all empty pages (assuming the database does not have the READ_COMMITTED_SNAPSHOT option set to ON etc.)


How to have row values as column headers with the associated values in postgresql?

I've used this good answer of Erwin Brandstetter

But keep in mind that your solution requires a dynamic query because I'm sure that creator table has different records from these you has posted here.

create table creator (id int primary key, name text);
insert into creator values (1, 'jack'),(2, 'doge'),(3, 'cardano');
create table roads(id int primary key, creator_id int references creator(id), length int);
insert into roads values (1, 1, 3), (2, 1, 2), (3, 3, 9), (4, 1, 3);
create table blocks(id int primary key, creator_id int references creator(id), length int);
insert into blocks values (1, 1, 4), (2, 1, 4), (3, 3, 5), (4, 1, 3);

I've used a view to get results for roads and blocks.

create view my_view
as
select 'roads' as subject, c.name,
       coalesce((select sum(length) from roads where creator_id = c.id), 0)::int as length
from   creator c
union all
select 'blocks' as subject, c.name,
       coalesce((select sum(length) from blocks where creator_id = c.id), 0)::int as length
from   creator c;

✓

select * from
crosstab
(
  $$select subject, name, length
    from my_view
    union all
    select subject, 'total'::text as name, coalesce(sum(length),0)::bigint as length
    from my_view
    group by subject
    order by 1,2$$
)
as t ("subject" text, "cardano" bigint, "doge" bigint, "jack" bigint, "total" bigint)

subject | cardano | doge | jack | total
:------ | ------: | ---: | ---: | ----:
blocks  |       5 |    0 |   11 |    16
roads   |       9 |    0 |    8 |    17

dbfiddle here


SQL Server 2017 Enterprise - how to fast update entire columns

maybe some sort of metadata trick is possible?

Adding a new column with a default can be a metadata-only operation.
Dropping a column can be a metadata-only operation.
Renaming a column can be a metadata-only operation.

I'm not certian that all of these always are metadata-only for every type of table, but  try something like:
  use AdventureWorksDW2017
  go
  drop table if exists DimCustomer2
  go
  select c.*
  into DimCustomer2
  from DimCustomer c
  cross join (select top 100 * from sys.messages) d
  go
  set statistics io on
  go
  begin transaction
  alter table DimCustomer2 add LastName2 nvarchar(200) not null default 'HIDDEN'
  alter table DimCustomer2 drop column LastName
  exec sp_rename 'Dbo.DimCustomer2.LastName2', 'LastName', 'COLUMN'
  commit
  go
  set statistics io off
  go
  select top 100 *
  from DimCustomer2


Adding the domain account to a security group on the SQL Server computer that has sufficient privileges to log on as a service

To answer your questions directly:

2) In order to add a login to the sysadmin fixed server role you can utilize ALTER SERVER ROLE ... ADD MEMBER (2012) or sp_addsrvrolemember (pre-2012).  Here would be an example:

exec sp_addsrvrolemember 'yourLoginName', 'sysadmin';

3) Without having to reiterate what's found in the following documentation, take a look at this MSDN reference on the service permissions for each SQL Server service.


Variable substitution in psql when using \copy

You can circumvent this limitation by piping the whole command to psql:

echo "\copy tbl FROM '/path/to/myfile.pgsql'" | psql mydb


SQL server log shipping - dry-run data modification script on secondary then resume log shipping

On server B, is there any way to instead effectively rollback to a
  previous transaction log (before I tried the script), and then resume
  log shipping?

No there is no way to do this.  You can only bring a log shipped secondary to standby mode.  Which allow read_only operations.  This is done by keeping an undo file.  When you need to restore log files again contents from the undo files are applied first.

More details about undo file here:

In SQL server, How to see the standby file path when we restore the database in standby mode by Harsh Chawla.

For testing DDL you need to bring database to read_write mode which will undo uncommitted transactions without keeping a log of those pages. There is no way to revert to previous state as you can do in case of standby.


How can MySQL be optimized for fast reads?

90% of Production servers are humming along nicely because they are not "pushing the envelope".

9% of Production servers are humming along nicely because they pushed the envelope, but then found a way around the problem(s) they encountered.

(I made up the numbers, based loosely on years of experience.)

My point is that you probably fall into the 90% and don't need to worry.  But when you get close to hundreds of writes/second or thousands of reads/second, you may migrate to the 9%.  Then bring your question to us, together with SHOW CREATE TABLE and some info about the queries.

As for Flickr, that article was written before their "Ticket server" melted down.  It was a server with exactly one purpose:  Produce the next photo_id.  This experience has little to do with 80/20 or random reads.  Most of the rest of Flickr is sharded because of high read and write load.  Are you anywhere near needing sharding?  And it is possible to essentially eliminate that "ticket server" by a minor architectural change.

The other article starts with "several hundred update and inserts on them per minute".  Well, that is not a problem unless you are sloppy with indexes, etc.  Several hundred per second is likely to be a problem.

UUIDs on huge tables are a known source of trouble due to the randomness of access for updating.

So, what is MySQL -- InnoDB in particular -- optimized for?  I would say "some of everything".  But then I would be quick to point out that this forum (and stackoverflow) are littered with inefficient SQL that can easily be worked around.

Read: Many novices don't really understand indexes, especially 'composite' indexes.
Read: Certain design patterns are prone to inefficiency:  EAV, UUID.
Write:  Inserting one row at a time is literally 10 times as slow as at least two alternatives.

Most of the optimizations (read or write) are focused around the 16KB blocks that comprise the BTrees that both data and indexes are stored in.  Discussing the details there would take hours.

Another anecdote:  Later, Flickr sped up most reads by 50-100% by making essentially one index change.


MySQL "bad handshake" after entering password

There is old Bug Report about MySQL Workbench connecting to older databases.

Bug #91828 : Bad handshake since 8.0.12

Others have come across this

Can't connect to MySQL server on MySQLCC ERROR 1043 Bad Handshake
SOLVED: Bad Handshake – MySQL Workbench Failed To Connect To SQL
Devart Forums : Bad Handshake

When you google the subject of "mysql bad handshake", you will see two basic issues:

Older Versions of MySQL
Older Versions of ODBC/JDBC Drivers

The second post I referred to actually recommends "dummying down" and using an older product (MySQL Administrator for you Old School DBAs like myself).
Main thing to do is to make sure your drivers match the version of Workbench you are using. BTW Don't feel bad about using MySQL 5.1. There are still a lot of die-hards out there that are using (I hope you sitting down), MySQL 3.23.58.
WHAT TO DO
Use Older Versions of MySQL Workbench, Navicat, SquirrelSQL.
Hey, that worked in the second post (Click Here for Those Tools)
Please remember that MySQL 5.1 made allowances for MySQL 4.1 password authentication. Newer ODBC/JDBC drivers may not understand MySQL 5.1's way of authenticating and other network-related things, colloquially called "BAD HANDSHAKE".


Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance?

Quite honestly, I think your easiest approach will be:

backup your user databases
uninstall SQL Server
reinstall SQL Server with the right collation
restore your databases
fix the collation on the user databases

Also you know that SQL Server 2000 is well out of mainstream maintenance, right? And that quite soon there will have been FOUR major releases since then?


Get equivalent SQL query for any Postgres meta-command

Easy and very useful: you can launch psql with the proper switch (-E) to get the information.

me@mystation:~/ > psql -E
psql (9.3.11)
Type "help" for help.
me@mystation # \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Have a look at http://www.postgresql.org/docs/current/static/app-psql.html for more information. Once in psql, you could also set the ECHO_HIDDEN variable.


How to order by then start at first non null value and select a limit for number of values

Step 1:  Write a query that fetches only the outsidetemp values:  Use WHERE outsidetemp IS NOT NULL.  Put the results in a TEMPORARY TABLE.

Step 2:  Tack on an AUTO_INCREMENT

Step 3:  Fetch from it with WHERE (id % 5) = 1 to get every 5th, starting with the first available.

Step 4:  If you want other columns, then JOIN back to the original table USING(datetime).


Same query hash for totally different queries

While hash collisions are possible, I suspect what's happening is you're pulling the text for the whole batch, not the particular statement. A procedure with multiple statements can get a cache entry for each statement. And if some statements are identical across procedures, they'll have the same query hash. Let me demonstrate:

USE TestDB
GO

--DROP TABLE dbo.employees
CREATE TABLE dbo.employees (
ID INT IDENTITY PRIMARY KEY,
title VARCHAR(20),
salary TINYINT,
has_been_fired_yet BIT)

INSERT dbo.employees
VALUES ('Senior DBA',30,0),('Grumpy DBA',50,0)
GO

CREATE OR ALTER PROC dbo.Payroll
AS

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

CREATE OR ALTER PROC dbo.RightSizing
AS

UPDATE dbo.employees
SET has_been_fired_yet = 1
WHERE salary > 40

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

--EXEC to get them in the cache
EXEC dbo.Payroll
EXEC dbo.RightSizing

Use the below query to see the how the same statement that is in each proc shows up separately.

SELECT
st.text AS batch_text,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1
) AS statement_text,
qs.execution_count,
qs.query_hash
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE st.objectid IN (OBJECT_ID('dbo.Payroll'),OBJECT_ID('dbo.RightSizing'))

If you use the query in your question, it fails to pick up the identical statement, since it looks at only the batch text.


Microsoft SQL Server: Prepared Statements

Prepared statements are supported in SQL Server, but you are probably having a hard time finding examples of using them because they aren't really necessary.

You could call sp_prepare to get a handle and then pass that to sp_execute. Or you can call either sp_prepexec or sp_prepexecrpc, both of which combine the "prepare" and "exec" steps. In all cases, you would end by calling (eventually) sp_unprepare, passing in the handle returned by sp_prepare, sp_prepexec, and sp_prepexecrpc.

However, as stated previously, this pattern is obsolete / unnecessary in Microsoft SQL Server as queries and execution plans are naturally cached. Queries are cached, and looked up, based on a hash of the query text. Ad hoc queries are cached based on the exact, full text of the query (binary comparison), including values concatenated into the query to use for filtering, etc. Those plans are less likely to get re-used, unless the exact same query (with respect to white-space and everything else) is executed again. Parameterized queries are cached based on the query text which does not include the parameter values, and can be re-used for different values of the parameters -- just like when doing a "prepare". Hence, no need to call prepare / exec / unprepare.

If you execute queries using Stored Procedures, sp_executesql (for Dynamic SQL), or a properly parameterized query from app code, then SQL Server is already doing a lookup to see if that parameterized query has already been parsed and compiled (i.e. "prepared") and if so, then it will use the cached info.

I believe that the most wide-spread usage of this pattern in SQL Server is by ODBC drivers. When writing an application that can connect natively to SQL Server, one would not employ this pattern. And in fact, I would recommend against using this pattern (whenever possible) as it has the downside of taking up more memory per each Session: it stores the query definitions so that the execution plan can be regenerated in cases where the plan was, for whatever reason, removed from the plan cache. For more details on this behavior, including sample .NET / C# code, please see my other answer here on DBA.StackExchange:

What is the sense and benefit of using SqlCommand.Prepare()?


Limits of testing HA with a single server replica set?

You can certainly test standard replica configuration and deployment issues on a single server, but some scenarios are easier to set up with multiple environments (even if those happen to be VMs or containers on the same host server). The rs.freeze() and rs.stepDown() commands help control election outcomes; for HA testing I expect you'd be randomly shutting down or firewalling mongod processes to confirm the outcome is as you anticipated.

  Are there specific scenarios that I wouldn't able to simulate with things like rs.stepDown() and rs.freeze()? Or by stopping services?

It depends how thoroughly you are trying to test. VMs definitely provide more flexibility -- for example, if you want to test possible effects of clock skew or resource limitations.

To simulate more real-world deployments you should also consider network simulation using a tool like mongodb-bridge. There are common network issues like latency (particularly for geographically distributed deployments) as well as more insidious problems like asymmetric routing or connectivity.

If you are considering HA from the application point of view, you should test using an application/driver with an appropriate write concern. For example, you may want to ensure that an application using a write concern of w:majority can still get acknowledged writes in the event of node failure. With a Primary/Secondary/Secondary replica set any single node can fail and there will still be two data-bearing nodes to satisfy w:majority; if you have a Primary/Secondary/Arbiter replica set, the failure of a single data-bearing node will still allow a primary to be elected, but w:majority write concern cannot be satisfied.

If you want some ideas on replication scenarios to test, an interesting starting point might be the JavaScript test suite used in the MongoDB code base: https://github.com/mongodb/mongo/tree/master/jstests/replsets.


Mirror failure after windows updates - possible cause?

We had to bring MS in on this one via a paid support call. After a week of dumps, xperf analysis, driver updates, playig with Virtual infrastructure we ruled out all the basics (or so we thought) - over a week later we found the problem - there was over 500,000 sys.server_event_notifications defined - these all looked as follows:

name    object_id   parent_class    parent_class_desc   parent_id   create_date modify_date service_name    broker_instance creator_sid principal_id
SQLWEP_E0B9EA66_1E73_45B7_B528_75166DC7A15D 337640  100 SERVER  0   2013-06-14 01:56:27.077 2013-06-14 01:56:27.077 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_6492D14E_99D2_49F2_9CD9_43F4B7DC7F63 338711  100 SERVER  0   2013-02-12 17:50:55.897 2013-02-12 17:50:55.897 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_C9871B51_F866_48A6_862E_08EC4F92DAFA 351457  100 SERVER  0   2013-03-06 02:04:18.070 2013-03-06 02:04:18.070 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_0E5D8FAF_1A9C_43FA_8EDF_A5713A8C7825 362061  100 SERVER  0   2014-02-08 04:57:17.217 2014-02-08 04:57:17.217 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_712ED252_E529_4022_9EA3_C785BAEB8147 364203  100 SERVER  0   2013-04-05 15:52:11.190 2013-04-05 15:52:11.190 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_E2D31BC2_E416_4D12_8B4A_88F4183FE021 374807  100 SERVER  0   2014-03-15 13:31:25.390 2014-03-15 13:31:25.390 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_7B389ACB_EA1F_434C_8B2A_7B93D4D0F098 376949  100 SERVER  0   2013-05-12 05:02:12.450 2013-05-12 05:02:12.450 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_4A828CDE_D72D_4277_97C0_030686F86377 378020  100 SERVER  0   2013-01-21 16:49:04.780 2013-01-21 16:49:04.780 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_4EA3C2AD_ACCE_4A66_83E9_0676D2250C34 387553  100 SERVER  0   2014-04-24 20:26:31.053 2014-04-24 20:26:31.053 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_6EA3C0FB_6EDA_4B9D_A3FE_D2B07799212C 389695  100 SERVER  0   2013-06-16 18:07:06.043 2013-06-16 18:07:06.043 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259

These had all been created gradually over 5 years, what they are, where they come from, no one could say & MS couldn't find any related alerts to justify them either. Dropping 1 notification took 4 seconds so we would have been looking at 22 days to drop them all - suffice to say we ended up nuking the SQL instance & re-installing SQL from scratch.


What will be the best mysql configuration for 12 core processor 128 gb ram ssd 160gb hdd

Check out http://tools.percona.com. It has a wizard where you type in your server's hardware configuration, and it recommends MySQL tuning parameters.

It also asks questions about your workload and size of database, and so on. You didn't consider these details in your question above, but they are important things to consider when tuning MySQL.


Include scripts for Indexes on Alter Views

O.K. lets create a nice example for you and show you how to script the index of your indexed view.

first, lets create a temp table called T1 and add some meaningful data into it

lets create a procedure that generates random strings first:

USE TEMPDB
GO

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ''

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

now lets add some data into T1 and test some selects:

use tempdb
go

create table t1 (i int not null identity(1,1) primary key clustered,
                    the_name varchar (50) NOT NULL)

declare @i int
declare @randomString varchar(50)

select @i = 1

while @i  < 1008 begin

exec SpGenerateRandomString 50, @randomString output

insert into t1(the_name) values ( @randomString)

select @i = @i + 1

end

when running some selects from T1:

running selects after clearing the cache for this query plan and this database
collecting statistics io and time, in order to compare performance.

DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
                FROM master.dbo.sysdatabases
                WHERE name = 'TempDB');

print @intDBID
-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%order by the_name desc%';

DBCC FREEPROCCACHE (0x060002002065C706F0BE22630400000001000000000000000000000000000000000000000000000000000000);

set statistics io on
set statistics time on

select * from t1
  where ([i]% 2) = 0
order by the_name desc

This is the query plan generated:

> Now creating an indexed view:

this will be used later on to run the same select and compare the performance

--DROP VIEW VM_01
--GO

create view vm_01
with schemabinding
as

select
   i, the_name
   from DBO.t1
  where ([i]% 2) = 0

GO
CREATE UNIQUE CLUSTERED INDEX PK_VM_01 ON DBO.VM_01 (THE_NAME,I)
GO

Now testing the select and comparing:

DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
                FROM master.dbo.sysdatabases
                WHERE name = 'TempDB');

print @intDBID
-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%order by the_name desc%';

DBCC FREEPROCCACHE (0x060002002065C706F0BE22630400000001000000000000000000000000000000000000000000000000000000);

    set statistics io on
    set statistics time on
    select * from vm_01 WITH (NOEXPAND)
      where ([i]% 2) = 0
    order by the_name desc

set statistics io off
set statistics time off

the query plan generated:

as it was demonstrated the query using the indexed view performed much better, so (because we don't worry about the writes in this example) we want to keep the index.

Here a select to show you the index creation script for the clustered index created to the view VM_01:

SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @TABLEID int
declare @index_or_table_name varchar(256)

select @TABLEID = OBJECT_ID('VM_01')
      , @index_or_table_name = 'vm_01'

BEGIN TRY

        SELECT

        i.object_id,
        i.index_id,
        index_name=i.name,

        CASE WHEN I.is_primary_key = 1 THEN
             ' ALTER TABLE ' +
             QUOTENAME(Schema_name(T.Schema_id))+'.'+ QUOTENAME(T.name) +
             ' ADD  CONSTRAINT ' + QUOTENAME(i.name) + ' PRIMARY KEY ' +
             I.type_desc +
             ' ( ' +  KeyColumns + ' )  '

        ELSE

        ' CREATE ' +
            CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +
            I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
            I.name  + ' ON '  +
            QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name)
            + ' ( ' + KeyColumns + ' )  ' +
            ISNULL(' INCLUDE ('+IncludedColumns+' ) ','')
            +  ISNULL(' WHERE  '+I.Filter_definition,'') --sql2005

         END -- case primary key or not

            + ' WITH ( ' +
            CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +
            'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +
            -- default value
            'SORT_IN_TEMPDB = OFF '  + ','  +
            CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +
            CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +

            CASE WHEN I.is_primary_key = 1 THEN

                    -- default value
                    ' ONLINE = OFF '  + ','

            ELSE

                    -- default value
                    ' ONLINE = OFF '  + ','  +

                    -- default value
                    ' DROP_EXISTING = ON '  + ','

            END +

           CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +
           CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +
           DS.name + ' ] '

           AS [CreateIndexScript]

        FROM sys.indexes I

        INNER JOIN (
                      SELECT  Object_id
                             ,Schema_id
                             ,NAME
                             FROM sys.tables

                      UNION ALL

                      SELECT  Object_id
                             ,Schema_id
                             ,NAME
                             FROM sys.views

                    ) T

                   ON T.Object_id = I.Object_id
         INNER JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid

         INNER JOIN (SELECT *
                 FROM (

                        SELECT IC2.object_id , IC2.index_id ,
                            STUFF((SELECT ' , ' + QUOTENAME(C.name) + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
                        FROM sys.index_columns IC1
                        JOIN sys.columns C
                            ON C.object_id = IC1.object_id
                            AND C.column_id = IC1.column_id
                            AND IC1.is_included_column = 0
                        WHERE IC1.object_id = IC2.object_id
                            AND IC1.index_id = IC2.index_id
                        GROUP BY IC1.object_id,C.name,index_id
                        ORDER BY MAX(IC1.key_ordinal)
                            FOR XML PATH('')), 1, 2, '') KeyColumns

                        FROM sys.index_columns IC2
                        --WHERE IC2.Object_id = object_id('xtbUApplication') --Comment for all tables
                        GROUP BY IC2.object_id ,IC2.index_id

                      ) radhe3
                ) radhe4

          ON I.object_id = radhe4.object_id AND I.Index_id = radhe4.index_id
         JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
         JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
         JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id

         LEFT JOIN (SELECT * FROM (
            SELECT IC2.object_id , IC2.index_id ,
                STUFF((SELECT ' , ' + QUOTENAME(C.name)
            FROM sys.index_columns IC1
            JOIN sys.columns C
               ON C.object_id = IC1.object_id
               AND C.column_id = IC1.column_id
               AND IC1.is_included_column = 1
            WHERE IC1.object_id = IC2.object_id
               AND IC1.index_id = IC2.index_id
            GROUP BY IC1.object_id,C.name,index_id
               FOR XML PATH('')), 1, 2, '') IncludedColumns
           FROM sys.index_columns IC2
           GROUP BY IC2.object_id ,IC2.index_id) tmp1
           WHERE IncludedColumns IS NOT NULL ) tmp2
        ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id

        ---------------------------------------------------------------------------------------------------
        -- when @TABLEID = 0 -> all tables
        -- when @TABLEID = -1 -> only the index named = @index_or_table_name
        -- when @TABLEID has the object id of a particular table -> only shows that table
        ---------------------------------------------------------------------------------------------------

        WHERE 1 = CASE WHEN @TABLEID = 0 THEN 1 ELSE
                     CASE WHEN @TABLEID = -1 AND UPPER(I.name) = @index_or_table_name THEN 1 ELSE
                          CASE WHEN @TABLEID = I.object_id THEN 1 ELSE 0
                          END
                     END
                  END

        ORDER BY i.name

END TRY
BEGIN CATCH

        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13)

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH;

when you run the above script you get:

and a copy and paste shows us the script:

CREATE  UNIQUE CLUSTERED INDEX PK_VM_01
ON [dbo].[vm_01] (  [the_name] ASC  , [i] ASC  )
WITH (  PAD_INDEX = OFF ,
FILLFACTOR = 100  ,
SORT_IN_TEMPDB = OFF ,
IGNORE_DUP_KEY = OFF ,
STATISTICS_NORECOMPUTE = OFF ,
ONLINE = OFF ,
DROP_EXISTING = ON ,
ALLOW_ROW_LOCKS = ON ,
ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ]

  there are still some bugs to fix, like formatting, and the
  drop_existing=on,  but hopefully this will help.


SQL Server 2008 R2 - remove line below heading (which has ----,----) - dont have SSIS

Unfortunately, there is no way directly from BCP to get the header as well as the rows out in a pretty format, even with the out parameter

HOWEVER, there is a workaround. You can do this:

SELECT 'HeaderCol1Name', 'HeadCol2Name'
UNION ALL
SELECT CAST(C.Col1 AS VARCHAR(255)), CAST(C.Col2 AS VARCHAR(255)) ...
FROM (SELECT <Your original query>) AS C

You can now:

BCP <above query in quotes> out Foo.txt

Yes, it is ugly. And yes, it works. You can even control things like date formatting this way.


How to add new column based on calculation of another column in postgresql?

I would like to add a new column ...
... I am filtering the data before hand based on a where statement

ALTER'ing a table changes the whole table.
There is no possibility for "filtering"; either every single row gets itself a shiny, new column or none of them do.
alter table t1
add column date_start date ;

update t1
set date_start = time_start::date ;

But, as others have said, why create a whole new column to store data that is readily available within the table already?


Default binary log position for change_master_to in MySQL circular replication?

(-sorry if I have misunderstood)

(-assuming you are using binlog file and pos).

The key things to make sure of are that your servers have unique server-ids (if you have replication running already then this should be ok).

You also probably want to set:

auto_increment_increment
auto_increment_offset

values so that the two Databases don't generate overlapping primary keys.

http://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html

After that on your Slave DB (B), simply run SHOW MASTER STATUS.

Use those details to issue the CHANGE MASTER statement on DB A.

What you should find is that anything executed on A is then replicated to B and vice versa.

However anything executed on A shouldn't be replicated back from B to A.

And as such, the only queries written back to A will be those that are executed directly on B.

Finally, you will want to set B to read_only to prevent anyone accidentally writing to it, until such time as you want them to.


In Job Step > Properties, only see two tabs: Package and Configuration

It's on the Configuration which then has 3 subtabs: Parameters, Connection Managers and Advanced

Advanced allows you to specify 32 bit vs 64 bit runtime, change the logging levels, dump on errors and override package properties.


Questions on database table and index partitioning in SQL Server

When you partition an index all its data will be split according your partitions.
The partitioning is applied to each particular index independently others.
From the previous point - yes, if you need a partitioned table with partitioned indexes you have to apply the partitioning to the table and each of the indexes.
Too broad to be answered. You could start with SQL Server Table Partitioning: Resources


Insert in child table prevents lock on parent table

The first statement,

insert into TBL_ActionHistory(ActionHistoryID, Message)
    values (201, 'Something');

puts TM locks on TBL_TASK and TBL_ACTIONHISTORY tables to enforce FK constraint.
The second DELETE successfully locks row in TBL_ORDERLINE, but it also has to lock the whole TBL_TASK table (because you don't have index on TBL_TASK.OrderLineID) .  From docs :

  Locks and Unindexed Foreign Keys When both of the following conditions
  are true, the database acquires a full table lock on the child table:

  No index exists on the foreign key column of the child table.

  A session modifies a primary key in the parent table (for example,
  deletes a row or modifies primary key attributes) or merges rows into
  the parent table. Inserts into the parent table do not acquire table
  locks on the child table.

  Suppose that hr.departments table is a parent of hr.employees, which
  contains the unindexed foreign key department_id. Figure 9-3 shows a
  session modifying the primary key attributes of department 60 in the
  departments table.


What problems would using the reserved keywords DATE and TIME as column names cause in Oracle?

The answer to your question is that it won't let you.

Oracle is stricter than other RDBMSes and you'll get an ORA-00904: invalid identifier if you try a CREATE TABLE with a reserved word.

However, you can force it to do so by surrounding the name in quotes, for example:

CREATE TABLE a
(
  "date" date
);

... But in doing so, you're essentially admitting you're doing something wrong (plus it makes the name case-sensitive).

Best practice would be to avoid case-sensitive object names, and avoid using reserved words.

Documentation link here, with a list of reserved words, which backs up what I've stated:

  You cannot use Oracle SQL reserved words as nonquoted identifiers.
  Quoted identifiers can be reserved words, although this is not
  recommended

In summary: Just don't.


Insert/update data into relational database tables only after administrator approval - how to approach it?

You basically have two options:

Extend each table with a 'Status' column, with possible values 'New', 'Approved' and 'Rejected'. Maybe another column for remarks by the administrator, and one to identify the owner of the record (which might already exist). All modules except for the import module should always filter on Status = Approved, which also influences your indices. This approach minimizes the impact on your data model, but that's probably the only advantage.
Build separate import_* tables for each table. They'll have the same structure as the tables in 1. Once a record is approved, it's copied over verbatim to the 'real' table. Creating and maintaining the tables is more work, but data is better separated and you don't need to change the logic of your core application.

There's no built-in waiting queue for INSERT statements in MySQL (or any database for that matter). What you describe is generally handled in application logic, and the database is just there for storage.


Redo lag on AG readable secondaries: is there a way to overcome it?

It sounds like you have a few different questions in here:

Could a query be modified to check to see if redo is current on the underlying data is done, and if not, stall execution until redo catches up? This seems like a really dangerous idea because when replication gets behind, you'll have more and more queries using up worker threads, waiting for replication to catch up. I can easily see worker thread exhaustion hitting here and causing threadpool waits because it's essentially the same scenario that blocking causes.

Could queries be redirected to the most current secondary? Yes, by using a load balancer between your app and the SQL Servers. You can build logic into load balancers to run periodic health checks, and when a server fails the load check, it's taken out of the available connection list until it's able to pass health checks again. This is a pretty common strategy for web servers, but fairly rare for database servers (since it's a lot of work to build out.)

Can redo be done faster? Yes, test to see whether serial or parallel redo is a better fit for your workloads, and make sure you're getting the one you want. Microsoft has blogged about the gotchas with parallel redo, including gems like only the first databases in your AG (by database ID) get parallel redo.


SQL Server 2005 still running after in place upgrade to sql 2012

Any uninstall of SQL Server will not delete the data or log directories used by the particular version.

The reason your data and log files stayed in the same spot is because you did an inplace upgrade. It will not move the databases to the "new" folders SQL Server 2012 would create (eg. "..\MSSQL11.MSSQLSERVER\DATA").

The only issue you could hit when removing SQL Server is with the shared tools (e.g. SSMS, etc.), but since 2012 changed to using full VS it should not cause any issues.


Difference between OLTP and Inmon Data Warehouse

The difference is more to the subject of the database than anything else. By the way, Inmon approach focus on normalizing as much as possible to make the ETL process easier and less error-prone; but non-normalized data are acceptable on data marts, even to Inmon.

OLTP: it is a full normalized database (commonly on 3NF) focused on a specific application. Usually, the data changes are small and happen on the row level.

Inmon DW: this is the central information database for the whole organization. The modeling is focused on business needs and this database centralize all the information from every system in your organization. IE: you can have Customer information in many of your systems, where each system brings you a complementary information. Following the Inmon approach, you have to have only one Customer entity on the DW, and this entity will have all the information from every system, in a conformed way (so it is not only copying tables, you should efficiently relate data from different systems inside your DW). Comparing with the Kimball approach, where classifications (hierarchies) can be simple columns in a dimension table, Inmon would create separate tables to "normalize" this info and make the ETL process easier.


How can I set columns for an entire table in one UPDATE based on the SUM of a join table column?

update companies c set c.score = (select sum(e.score) from employees e where e.company_id = c.id);


Set or limit number of connections a single pymongo client will open

Use the following parameter to set the number of maximum connections from your pymongo app.

client = MongoClient(host, port, maxPoolSize=10)

Here is documentation with more details.


You can run this script if it happens again:

SELECT pr.spid, pr.loginame, pr.status, pr.blocked, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
        ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
        ON txt.objectid = obj.object_id
WHERE pr.blocked <> 0
    and pr.spid >= 50

It should give you a list of processes which were blocked. After that you can try to find the process which blocks all of these processes. You can try (if you have the right for it) to kill the process using kill yourSPID.

But beware, just try the kill if the spid is above or equal to 50! Otherwise it's an internal process!

After a restart every process is either rolled forward or rolled back. Your tempdb is recreated and all internal processes are restarted.


How often should I run integrity and consistency checks on SQL Server 2008?

For me, it depends on your maintenance window and usage. It takes time and resources to run the DBCC checks so you want to minimise imnpact on a running app

If you can run it every night, do so. Otherwise weekly.

Or monthly if you really are 24/7.. but then I'd be running DBCC on a restored database as an extra check.

And you have to consider other maintenance too: indexes and statistics in your window


You could try subqueries

However, a subquery that returns more than one value will fail

This works but you have to consider that it can only return one value, and it could break easily

DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)

INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(3,4,5,1)

DECLARE @Parameter int = 0

SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter  = 0

UNION ALL

SELECT col1,col2, (select col3  FROM @table WHERE some_condition = 0 ) as col3
FROM @table
WHERE @Parameter  = 1

Result:

col1    col2    col3
1       2   5
3       4   5

5 will be returned for each col1 and col2 value, because parameter = 0.

This fails

DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)

INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(2,3,4,0),(3,4,5,1),(6,7,8,1)

DECLARE @Parameter int = 0

SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter  = 0

UNION ALL

SELECT col1,col2, (select col3  FROM @table WHERE some_condition = 0 ) as col3
FROM @table
WHERE @Parameter  = 1

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.


Does "Connect" statement for every query indicate broken connection pooling?

Three aspects must be considered
ASPECT #1 : PHP
PHP has a setting for Persistent Connections
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent=On

Make sure this is on in php.ini if you want to use Persistent Connections
ASPECT #2 : Your Code
Since mysql_pconnect is deprecated, make sure you are using one of the following

mysqli-connect with p: before the host name
PDO::__construct() with PDO::ATTR_PERSISTENT as a driver option

ASPECT #3 : MySQL
When mysqld sees a connection attempt, it records it statistically as one of the following

Connections : Connections Attempts (Successful or Not)
Aborted_connects : Failed Connection Attempts

If the general log is echoing just the word Connect as an event, chances are that's just a Connection attempt. Any subsequent events on that connection would be operating on a established thread (DB Connection). The general log will not tell you whether the DB Connection is newly created or already established.
To actually know if the connections are coming and going, you should monitor

Threads_connected : The number of currently open connections
Threads_created : The number of threads created to handle connections

To actually see them in a single query, please run this
SELECT * FROM information_schema.global_status WHERE variable_name IN
('Connections','Aborted_connects','Threads_connected','Threads_created');


A), B) - You performed the same in the test environment, so you have an estimation of how long it takes. If it is short enough to do it during operation, go for it. If not, you can do it during a scheduled downtime. Typically users do not log in every second or so, sessions get created then they are reused by applications or connection pools. If you have frequent logins to the database, that is flawed design. Also, what is more important? The audit or the users being able to log in. You can temporary disable auditing.

C) - COMPRESS HIGH requires the Advanced Compression option to be licensed. If you have the necessary license, go for it. If not, then you will have some explaining to do.

Generally about audit: instead of moving the audit inside the database, you should be working on removing it from the database. Storing and handling audit data inside the source database is a pain in the ass. The amount of audit data stored inside the database should be kept as low as possible. You have 500 GB of unneeded data that is included in your backups, copied to clone databases. You should regularly archive then delete audit data from the database. In my opinion, instead of storing any audit data in the database, sending the audit data to a remote server through syslog is a much better solution.


Count number of writes to different tables

This information is available through the Statistics Collector.

The information you are looking for is stored in pg_stat_bgwriter or pg_stat_database. Maybe you are also interested in the values from pg_stat_all_tables that shows how many tuples have been inserted, updated or deleted.

Pay attention to the configuration properties listed in the section Statistics Collection Configuration

All those views show values since the instances has been started or since the last time pg_stat_reset() has been called.

If you want to monitor the change over time, you need to sample those views and store the values somewhere (e.g. a text file or just another table that has a timestamp to indicate when the sample was taken).


Ola Hallengren scripts: after a job failure, how to skip the databases that worked and backup only the remaining ones?

Usually when one or more backups fails, you have to do some kind of manual check.
Check the log, fix the problem and the rerun the backup.
Focusing on the last step, I suggest you to collect the database list of failed backups from the commandlog table populated by ola scripts. Then you should use it as @databases parameter for the ola script.
In an daily backup jobs, you can use the ALL_DATABASES standard ola parameter as step 1 and the job ends there on success.
You can have a step2 as recovery step with the dynamically generated database list. You run it only to recover missing backups.


How to insert data in table with constraint on column? - in parallel queries

Commit as soon as possible. Otherwise, just to give you an idea:

create table t(n number, CONSTRAINT n_unq UNIQUE (n));

create or replace function f(pn number) return number
is
  pret number;
  l_ret number;
begin
  select n into pret from t where n = pn;
  return pret;
exception when no_data_found then
  l_ret := dbms_lock.request(id => pn, timeout => 0, release_on_commit => true);
  dbms_output.put_line('lock return code: ' || l_ret);
  if l_ret = 0 then
    insert into t(n) values (pn);
    return pn;
  else
    return -1; -- can not insert
  end if;
end;
/

Session 1:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 0
f return code: 1

select * from t;

         N
----------
         1

Session 2:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 1
f return code: -1

select * from t;

no rows selected

Session 1:

commit;

Session 2:

select * from t;

         N
----------
         1

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

f return code: 1

I am not sure this will really help you, the ideal solution would be to prevent multiple sessions trying to insert the same value in long transactions.


Make MySQL database accessed by just one user

You need an 'admin' for maintenance, etc.  You don't have to call it 'root' and you don't have to let everyone know the password.

It is 'best practice' to grant user(s) access to one (maybe more) database(s).  In this context, a "user" could be a human, or the login for an app.  Example:

 GRANT ALL PRIVILEGES ON dbname.* TO username@"1.2.3.4" IDENTIFIED BY "...";

That implicitly does not give that user (from that host) access to any other databases, nor privileges such as CREATE DATABASE or GRANT.

When writing Stored Routines, carefully think about the use of SECURITY INVOKER versus SECURITY DEFINER.  INVOKER means that the routine cannot do more than what the user could manually do.  DEFINER lets the routine be more powerful.  This can be useful for providing some otherwise-restricted operation to mere users.

(Caveat:  Newer versions of MySQL have "Plugins" and "Roles" that are more involved than the old GRANT; these let you have more involved security models.)


Query returning unused stored procedures

Whenever a proc is executed, you'll see the full CREATE PROCEDURE recorded in the DMVs, which SP_askbrent will be querying.

Executions of each individual statement is also recorded, in sys.dm_exec_query_stats, and you can retrieve the exact text of what has been executed, by CROSS APPLY sys.dm_exec_sql_text(sql_handle) and applying the start and end offsets from the sys.dm_exec_query_stats DMV.

This is the code I use to identify what queries on my server are placing quite a load on the database in terms of IO, Duration and CPU.

It only works on SQL Server 2008+ though (unless you exclude the plan_hash and query_hash), but gives a nice indication of where I should start to look in terms of refactoring.

Each individual query within a stored procedure has the performance recorded (providing it completes and does not timeout)

SELECT
     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,DB_NAME(dest.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(dest.objectid, dest.dbid) AS SchemaName
    ,OBJECT_NAME(dest.objectid, dest.dbid) AS ObjectName
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_elapsed_time) AS SumDuration
    ,CAST(100.0 * SUM(total_elapsed_time)
                / SUM(SUM(total_elapsed_time)) OVER() AS NUMERIC(5,2)) AS PctDuration
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUM(total_logical_reads + total_logical_writes + total_physical_reads) AS [SumIO]
    ,CAST(100.0 * SUM(total_logical_reads + total_logical_writes + total_physical_reads)
                / SUM(SUM(total_logical_reads + total_logical_writes + total_physical_reads)) OVER() AS NUMERIC(5,2)) AS PctIO
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext
    ,deqs.query_plan_hash AS QueryPlanHash
  FROM sys.dm_exec_query_stats AS deqs
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE())
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')
  GROUP BY
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time,
          deqs.query_hash, dbid, dest.objectid, deqs.query_hash, deqs.query_plan_hash


We were able to determine that only one table in the database in question had not had it's statistics updated. We did that by running the following

select object_name (i.id)as objectname,i.name as indexname,i.origfillfactor,i.rowcnt,i.rowmodctr ,STATS_DATE(i.id, i.indid) as ix_Statistics_Date,o.instrig,o.updtrig,o.deltrig,o.seltrig
from sysindexes i INNER JOIN dbo.sysobjects o ON i.id = o.id
where rowcnt >1000 and i.name not like'sys%'and object_name(i.id)not like'sys%'
order by objectname desc
go

So we ran the update statistics on just the table in question. And we were able to replicate the scenario in question as it was still waiting and not finishing. So then knowing the session ID of my waiting process I took a look at the locks table

select * from sys.dm_tran_locks

Looking at the results I found a different session ID associated with the Object_ID of my table in question. I was able to then go kill that session ID and the update statistics finished no problem.

What I think happened here was that a user left a program open that had the exclusive lock on my table, and perhaps that along with auto statistics in sync mode may have caused a log jam when doing our full scan update stats.


How to select data out of an Oracle collection/array?

You can create a pipelined function.
Some examples here.


Best way to get current year minus 5 years

There are different ways to achieve the goal.
SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual;

OR
SQL>  select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'YYYYMM') from dual;

Regarding the second one, what happens if the SYSDATE or the current date supplied happens to be a leap day?
SQL> select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
    from dual;
select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
from dual
                                                        *
ERROR at line 1:
ORA-01839: date not valid for month specified

What happens if we use the first method?
SQL> select to_char(trunc(add_months(to_Date('2016-02-29','YYYY-MM-DD'),-12*5),'YEAR'),'YYYYMM')
     from dual;

TO_CHA
------
201101


Yes, it's atomic; your domain is the ISBN.  If you were storing the domains for ISBNPrefix, ISBNRegistrationGroup, ISBNRegistrant, ISBNPublication and ISBNCheckDigit, then you'd split it up.

Consider the North American phone number +1-234-567-8901 ext 234567.  Many systems choose to store such a number under two domains, such as PhoneNumber and PhoneNumberExtension, taking perhaps 2345678901 under the PhoneNumber domain and 234567 under the PhoneNumberExtension domain, ignoring the Phone Number Country Code altogether ( largely because it is always 1 ).  Since the domains are defined as such, it is not incorrect to store them in such a manner. An example of violating 1NF in this case would be to store multiple extension numbers in the same row for a singular entity existing in the PhoneNumber domain.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567, 890123

Instead, to preserve 1NF, the PhoneNumberExtension attributes are stored separately.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567
  2345678901    890123

A system with more granular domains may choose to store phone numbers by PhoneNumberCountryCode, PhoneNumberAreaCode, PhoneNumberPrefix, PhoneNumberLineNumber and PhoneNumberExtension

| CountryCode | AreaCode | Prefix | LineNumber | Extension |
  1             234        567      8901         234567

Since the domains are defined in such a way, it is again not incorrect to store the data as such.  The same logic applies to various "compound attributes," like your ISBN; geographical coordinate data and municipal mailing addresses are some common examples, but such "Domain grouping" happens even in basic data types - consider DATETIME, a raw datatype for an example - one part date, one part time.

The way you choose to define your domains will largely be a decision on how you intend to use them.  In the telephone example, there tends to be little value in breaking apart AreaCode since other more appropriate demographics are often available, such as City, though splitting the domain apart is not a "wrong" choice.  If there is little value for your purposes to split apart the ISBN, you are not violating 1NF by defining the domain on a less granular level - you just can't store two of them in the same row for the same entity.


MySQL workbench alter table is incomplete

There seems to be an anomaly or bug when a timestamp column has a default value of CURRENT_TIMESTAMP() - with the parenthesis. 5.7 documentation says it is synonymous with CURRENT_TIMESTAMP but workbench must disagree - sort of.

Changing the definition in workbench by removing the parenthesis provides a message that nothing changed. However changing to "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" fixes the problems mentioned in the question. And subsequently changing it "CURRENT_TIMESTAMP" only also works.

I'll report this as a workbench 6.3.6 bug.


This can be considerably simpler and faster with DISTINCT ON:

SELECT DISTINCT ON (r.unit_id)
       r.*
FROM   reports r
JOIN   units   u ON u.id = r.unit_id
WHERE  r.unit_id IN (1111, 1112, 1113)
AND    r.time_secs <> 0
AND    r.latitude  <> 0.0
AND    r.longitude <> 0.0
ORDER  BY r.unit_id, r.time DESC;

More explanation in these related answers:
How do I efficiently get "the most recent corresponding row"?
Select first row in each GROUP BY group? (on SO)

Minor detail: the standard SQL operator for "is not equal" is <>. Use that rather than != (which is accepted as well in Postgres).

The only possible purpose of joining to the table units in this query is to verify that one or more related rows exist. If reports.unit_id is bound to unit.id by foreign key (as the naming suggests) referential integrity is guaranteed and you can drop the table unit from the query completely. Just add: unit_id IS NOT NULL.

SELECT DISTINCT ON (unit_id) *
FROM   reports
WHERE  unit_id IN (1111, 1112, 1113)
AND    time_secs <> 0
AND    latitude  <> 0.0
AND    longitude <> 0.0
AND    unit_id IS NOT NULL
ORDER  BY unit_id, time DESC;


Are there alternatives to the TOAD tool for SQL Server?

SQL Server Management Studio that comes with SQL Server?

If you need to run on non-Windows, then look at SQuireeL


How to retrieve elements from another table in a SQL query?

SELECT
p.ID,
p.post_title,
ProductImage1.product_img1,
ProductImage2.product_img2,
ProductImage3.product_img3
FROM wp_posts p
cross apply (
        select top(1) pi1.product_img1
        from wp_postmeta pi1
        where p.ID = pi1.post_id
        and pi1.meta_key = 'product_img1'
        ) as ProductImage1
cross apply (
        select top(1) pi2.product_img2
        from wp_postmeta pi2
        where p.ID = pi2.post_id
        and pi2.meta_key = 'product_img2'
        ) as ProductImage2
cross apply (
        select top(1) pi3.product_img1
        from wp_postmeta pi3
        where p.ID = pi3.post_id
        and pi3.meta_key = 'product_img3'
        ) as ProductImage3
WHERE p.post_type = 'wpcproduct'
AND p.ID = 103


SSRS error - A scope is required for all aggregates in the page header or footer which reference fields

The reason I was getting the error message is because I was supplying the scope (dataset) in the wrong place.

The scope needs to be supplied as the second argument of the aggregate function. I was incorrrectly supplying the scope as part of the left function (which is not an aggregate function) instead of the SUM function.

Below is what the working expression looks like. The only difference from my original syntax is the location of the scope/dataset name (PPR) in the expression.

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,1)),"PPR") / sum(CINT(right(fields!Points.value,1)),"PPR"))*100) & "%"


Non-clustered Index Update not displayed in execution plan

Try generating an actual plan in SentryOne Plan Explorer.

For a narrow plan the actual costs are not included in the XML, so you won't see those, but you at least have the ability to see that non-clustered indexes are affected.

A lot more information found in the duplicate question:

Nonclustered Index Insert

As well as these blog posts that Martin pointed out previously:

Wide vs. Narrow Plans
Optimized Non-clustered Index Maintenance in Per-Index Plans


Fast way to import 2GB csv file into SQL Server 2008

You need to use bcp.exe or SSIS. Read Import and Export Bulk Data by Using the bcp Utility (SQL Server) and The Data Loading Performance Guide. Also, read How to Analyze SQL Server Performance to be able to tell why something is slow.

After you've read those, come back with a more actionable question, including structure of table, csv structure, repeatability of the job, access issues etc.


When should extended events be used instead of SQL Profiler/perfmon?

Is it worth getting past the steep learning curve to take advantage of the extended events?

Definitely yes. Extended Events is a new platform with better performance, scalability than SQL Trace and the functionality that is in SQL Server 2008 is kind of limited when compared to SQL Trace and also there is some learning curve. Rest assured, Extended Events is getting a major over haul in SQL Server DENALI and there will be lot of options & use cases available in Extended Events that won't be in SQL Trace. Extended Events is the future of monitoring & troubleshooting going forward and some day in the future SQL Trace will be removed.

Note that MSFT won't deprecate a feature right away when a new platform is introduced. Extended Events is in Version 1 (SQL Server 2008) and there are lot of things that need to be improved, modified before removing SQL Trace which was there in the product for a long time.

To get over the learning curve, practice examples from blog posts written by Jonathan Kehayias and few of mine too (shameless plug).

http://www.sqlskills.com/blogs/jonathan/category/Extended-Events.aspx

http://sankarreddy.com/tag/extended-events/

To Sandy:
a
I don't think Michael has asked too many different questions on various topics in this thread. Yes there are many questions but they are on the same topic. I don't think he should open up several threads to split this one.


This failure occured while attempting to connect to the Principle server

You mention two key things in your post:

Mirroring
Work is being performed against the failover-server

I suspect what is going on is actually expected behavior.  Mirroring connection strings define both a Server (e.g. the Principal Server) and a Failover_Partner (e.g. the Mirror Server).  When establishing a connection to a mirrored database, if a connection to the Principal Server cannot be attained, an attempt to connect to the Mirror Server is then performed.

Per Connect Clients to a Database Mirroring Session (SQL Server):

  If a connection attempt fails or the retry time expires before it succeeds, the data access provider tries the other partner. If a connection is not opened by this point, the provider alternately tries the initial and failover partner names, until a connection is opened or the login period times out.

What I suspect the error is showing is the failed attempt to connect to the Principal Server because the database is actually active on the Mirror Server.  The error is thrown to your logs and then a successful connection is established against the Mirror Server because that's where the database is open/active.

This is expected behavior in a mirror configuration, and if you're able to (eventually) connect to the database, that means mirroring is working.  Take note that once you failover the database back to the Principal server, this error should no longer occur because the initial connection attempt to the Principal Server will once again succeed.


How to update a point table when a polygon table is modified

Would make a comment, but don't have enough reputation, sorry.

Shouldn't  NEW and OLD be B and ? B_new or something like this? Where is the new geometry stored to make the ST_Within check?

If I missed something and you want to compare geometries as in NEW.geom <> OLD.geom, consider ST_Equals or ST_OrderingEquals like ST_OrderingEquals(NEW.geom, OLD.geom) = 0


Try this out

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
            cp.execution_count AS ExecutionCount,
            cp.plan_handle,
            st.text AS QueryText,
            qp.query_plan AS QueryPlan
FROM        sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE       1 = 1
--AND st.text LIKE '%FormSearchModel%'
AND         qp.query_plan.exist('//p:RelOp/@Parallel[ .= 0]') = 1
ORDER BY    cp.creation_time DESC;

If you want to find plans where all operations are serial, you'd have to do something like this:

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ),
relop AS
(
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
            cp.execution_count AS ExecutionCount,
            cp.plan_handle,
            st.text AS QueryText,
            qp.query_plan AS QueryPlan,
            qp.query_plan.value('count(//p:RelOp)', 'INT') AS total_relop,
            qp.query_plan.value('count(//p:RelOp/@Parallel[ .= 0])', 'INT') AS serial_relop
FROM        sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE  qp.query_plan.exist('//p:RelOp/@Parallel[ .= 0]') = 1
)
SELECT *
FROM relop
WHERE relop.total_relop = relop.serial_relop


How can I convert the first 100 million positive integers to strings?

Your solution runs for 35 seconds on my laptop. The following code takes 26 seconds (including creating and populating the temporary tables):
Temporary tables
DROP TABLE IF EXISTS #T1, #T2, #T3, #T4;

CREATE TABLE #T1 (string varchar(6) NOT NULL PRIMARY KEY);
CREATE TABLE #T2 (string varchar(6) NOT NULL PRIMARY KEY);
CREATE TABLE #T3 (string varchar(6) NOT NULL PRIMARY KEY);
CREATE TABLE #T4 (string varchar(6) NOT NULL PRIMARY KEY);

INSERT #T1 (string)
VALUES
    ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'),
    ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'),
    ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), ('U'),
    ('V'), ('W'), ('X'), ('Y'), ('Z');

INSERT #T2 (string)
SELECT T1a.string + T1b.string
FROM #T1 AS T1a, #T1 AS T1b;

INSERT #T3 (string)
SELECT #T2.string + #T1.string
FROM #T2, #T1;

INSERT #T4 (string)
SELECT #T3.string + #T1.string
FROM #T3, #T1;

The idea there is to pre-populate ordered combinations of up to four characters.
Main code
SELECT TOP (100000000)
    UA.string + UA.string2
FROM
(
    SELECT U.Size, U.string, string2 = '' FROM
    (
        SELECT Size = 1, string FROM #T1
        UNION ALL
        SELECT Size = 2, string FROM #T2
        UNION ALL
        SELECT Size = 3, string FROM #T3
        UNION ALL
        SELECT Size = 4, string FROM #T4
    ) AS U
    UNION ALL
    SELECT Size = 5, #T1.string, string2 = #T4.string
    FROM #T1, #T4
    UNION ALL
    SELECT Size = 6, #T2.string, #T4.string
    FROM #T2, #T4
) AS UA
ORDER BY
    UA.Size,
    UA.string,
    UA.string2
OPTION (NO_PERFORMANCE_SPOOL, MAXDOP 1);

That is a simple order-preserving union* of the four precalculated tables, with 5-character and 6-character strings derived as needed. Separating the prefix from the suffix avoids sorting.
Execution plan

* There's nothing in the SQL above that specifies an order-preserving union directly. The optimizer chooses physical operators with properties that match the SQL query specification, including top-level order by. Here, it chooses concatenation implemented by the merge join physical operator to avoid sorting.
The guarantee is that the execution plan delivers the query semantic and top-level order by specification. Knowing that merge join concat preserves order allows the query writer to anticipate an execution plan, but the optimizer will only deliver if the expectation is valid.


Is there a UNION ALL equivalent for columns?

You are talking about the usage of a UNION in a CTE to generate dates. Here I show how you can generate dates in columns.

You can use a PIVOT Clause. However, there is a limit: The column names must be known in advance. If, for instance, you want to display the data per month, you can use the day numbers as column names:

SELECT *
FROM
    (SELECT DAY([Date]) AS d,
            MONTH([Date]) AS m,
            YEAR([Date]) AS y,
            Amount
     FROM MyTable) src
PIVOT (
    SUM(Amount)
    FOR d IN ( [1],  [2],  [3],  [4],  [5],  [6],  [7],  [8],  [9], [10],
              [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
              [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) piv;

The result looks like this (I clipped the image at 11 days but in reality, there are columns up to 31):

If you must display more dates, you can use the number of days since the start date as column names instead. If you create a report (using some reporting tool) based on this query, you can add this number to the start date to get real dates again for the columns headers.

Since you must specify the column names explicitly, no CTE is necessary for this part, but you can combine the pivot query with a CTE to generate the rows.


Do I need to change the delimiter when creating a procedure using PHP MyAdmin?

You don’t need to specify the DELIMITER via the Routines UI tool/form.


How to upgrade PostgreSQL from 9.5 to 9.6.1 without losing data?

After 9 months, I updated to PostgreSQL 10 from 9.6.5 with pg_upgrade:

1) Stop postgresql:

brew services stop postgresql

2) Initialize postgresql10 database:

initdb /usr/local/var/postgres10.0 -E utf8

3) Double check the newly created db:

ls /usr/local/Cellar/postgresql/

4) Run pq_upgrade to move data to new database:

pg_upgrade \
  -d /usr/local/var/postgres \
  -D /usr/local/var/postgres10.0 \
  -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
  -B /usr/local/Cellar/postgresql/10.0/bin/ \
  -v

5) Rename old database:

mv /usr/local/var/postgres /usr/local/var/postgres9.6.5

6) Rename new database to postgres:

mv /usr/local/var/postgres10.0 /usr/local/var/postgres

7) Restart postgresql:

brew services start postgresql


Difference between full and level 0 rman backups?

The RMAN backup concepts guide (link for 11gR2, but the concept hasn't changed):

  The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

Full and level zero backups copy all blocks that have data (and more if you're doing image copies), so they are both "full" in that sense.
 But if you want to do cumulative or differential backups, the starting point for those must be a level zero incremental backup.


multiple rows with the same maximum value

Yours appears to be a "greatest N per group" problem. What you can do is get the maximum seats per engine results:

SELECT
  engines,
  MAX(seats) AS max_seats
FROM
  planes
GROUP BY
  engines

and, using them as a derived table, join them back to the source to get the rows matching the maximums:

SELECT
  p.engines,
  p.manufacturer,
  p.model,
  es.max_seats
FROM
  planes AS p
  INNER JOIN
  (
    SELECT
      engines,
      MAX(seats) AS max_seats
    FROM
      planes
    GROUP BY
      engines
  ) AS es
  ON p.engines = es.engines
  AND p.seats = es.max_seats
;


What is your requirement? we cannot index "data" as it is not a column in table, It can defined with multiple columns within one table .

Few things to be considered while using Fulltext.
Its only supported for MyISAM engine,
You can also set few variables "ft_min_word_len" and "ft_max_word_len" to search,
Some stopwords are ignored we can set change values at "ft_stopword_file", and
Its not case sensitive search.


PLSQL Package - Catching Errors in procedure (Oracle)

This is happening because the division by zero error is being raised before the procedure is even executed - It's happening when the arguments are actually passed in to the procedure.

You can verify this with a small proc that doesn't do anything:

create or replace procedure do_nothing(divbyzero number)
IS
BEGIN
  NULL;
END;
/

Test:

SQL> exec do_nothing(1/0);
BEGIN do_nothing(1/0); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 1

SQL>


Query optimization when multiplying by zero

Change your query to -

select  *

from    MyTable

where   case
            when    a <> 0
            then    case
                        when    a * expensive_function() > 0
                        then    1
                    end
        end = 1

As for the questions -
An optimizer does what optimizer does.
It is an application with features that are supported and features that are not supported as any other application.
No application is being created right away with all possible features and no development team has unlimited resources and motivation to develop any possible feature including those with insignificant business value.

For @a_vlad

create function dbo.myfunction (@i int) returns int as begin return 0 end
GO

declare @x int = 0;
select 1 where dbo.myfunction(@x) = 1 and 1/@x = 1

  Msg 8134, Level 16, State 1, Line 6
  Divide by zero error encountered.

For @Myberg

create function dbo.myfunction (@i int) returns int as begin return 0 end
GO

create table #t (i int);
insert into #t (i) values (0);

select  1

from   (select  *
        from    #t
        where   dbo.myfunction(i) = 1
        ) t

where   1/i = 1

  Msg 8134, Level 16, State 1, Line 9
  Divide by zero error encountered.


Why does MySQL ignore the index even on force for this order by?

Actually, the problem here is that this looks like a prefix index. I don't see the table definition in the question, but sub_part = 700?  You haven't indexed the whole column, so the index can't be used for sorting and is not useful as a covering index, either.  It could only be used to find the rows that "might" match a WHERE and the server layer (above the storage engine) would have to further filter the rows matched.  Do you really need 1000 characters for a last name?

update to illustrate:  I have a table test table with a litle over 500 rows in it, each with the domain name of a web site in a column domain_name VARCHAR(254) NOT NULL and no indexes.
mysql> alter table keydemo add key(domain_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

With the full column indexed, the query uses the index:
mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | keydemo | index | NULL          | domain_name | 764     | NULL |  541 | Using index |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.01 sec)

So, now, I'll drop that index, and just index the first 200 characters of domain_name.
mysql> alter table keydemo drop key domain_name;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table keydemo add key(domain_name(200));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | keydemo | ALL  | NULL          | NULL | NULL    | NULL |  541 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql>

Voila.
Note also, that the index, at 200 characters, is longer than the longest value in the column...
mysql> select max(length(domain_name)) from keydemo;
+--------------------------+
| max(length(domain_name)) |
+--------------------------+
|                       43 |
+--------------------------+
1 row in set (0.04 sec)

...but that doesn't make any difference.  An index declared with a prefix length can only be used for lookups, not for sorting, and not as a covering index, since it doesn't contain the full column value, by definition.
Also, the above queries were run on an InnoDB table, but running them on a MyISAM table yields virtually identical results.  The only difference in this case is that the InnoDB count for rows is slightly off (541) while MyISAM shows the exact number of rows (563) which is normal behavior since the two storage engines handle index dives very differently.
I would still assert that the last_name column is likely larger than needed, but it still is possible to index the entire column, if you are using InnoDB and running MySQL 5.5 or 5.6:

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.
— http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html


Entity Relationship Design; The best scenario

If I really have to choose, I'd say your 2nd choice (build nth bridge) will be my favorite as it is clean and stick to rules. While your 3rd choice, though it reduces the # of tables, it actually brings "redundant" columns with null values in the Phone table and this design is "volatile" in the sense that if you need to add a new phone owner type, you need to add an additional column in the [Phone] table.

In an alternative way, I would create a "bridge" table like the following (using t-sql as an example):

    create table PhoneOwner (ID int identity primary key
    , OwnerID int
    , OwnerType varchar(20));

So for the [Phone] table, I will add one column PhoneOwnerID which references to [PhoneOwner] table's [ID] column. OwnerType column can have values as "Client", "Employer" or "Practitioner" in this case, or other values in future. (Of course, we can create [OwnerType] table to hold these values, and put a FK in [PhoneOwner] table to reference this [OwnerType] table)

The problem with this alternative design here is the integrity check, for example if OwnerType = 'Client', we need to ensure OwnerID's value does exists in [Client] table, though this can be done via application layer or via trigger to implement such business rules.

But I have to admit this is a compromise to strict integrity (via FKs) rule, the good side is that it somehow achieves better flexibility, and less mess in table design which will cause many null values in "redundant" columns.


Backing up / restoring a 20GB database with images stored as binary code

Instead of using SQL Server SMO code to do a backup of the database, use the T-SQL BACKUP DATABASE command by connecting to the source SQL Server instance using either SQL Server Management Studio (free download) or sqlcmd.exe.  Use the optional WITH COMPRESSION parameter to ensure SQL Server compresses the backup, and use the INIT option to ensure the target backup file only contains a single backup.

Once you have the database backup completed, use 7Zip or WinRAR to compress the backup file even further.  Send the file to your host using a reliable FTP program, or preferably SecureFTP or SSH, ensuring you set the session to allow binary transfers prior to sending the file.  Do not even attempt to restore the database if the FTP transfer does not complete, since SQL Server will not be able to read the backup file.

Once you have the zip/rar file copied to your host, uncompress it using your compression utility of choice, then run RESTORE VERIFYONLY to ensure the target SQL Server can read the entire backup.  If that completes, run a RESTORE DATABASE command to actually restore the database onto the target SQL Server.