Shrinking T-Log file every weekend basis

I would recommend properly sizing the log file and not shrink it on a regular basis.

Because you are rebuilding the index you will grow your logfile as the index will be written to the transactionlog file. This means that your largest index should fit inside the transaction log file.

If your database is in full recovery mode, you can try to take logbackups more frequently to prevent it from growing out of hand.(Multiple index rebuilds stored in the transactionlog file)

If your database is in simple mode, this will not be the case.

When your transaction log file grows you will create something called Virtual Log Files (a structure inside the transaction log file) this can cause several problems. (more information: https://blogs.msdn.microsoft.com/saponsqlserver/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery/)

I would first shrink your file, and then grow it to a proper size to avoid the creation of too many VLF's. But do not shrink them on a weekly basis.


Because switching partition change address of all pages to target table, it also moves indexes (created on the source table) to target table. If available index on the table is not partitioned index will not move to target.

When we SWITCH partition, make sure both (source and target) tables has same schema (structure), indexes (should be partitioned) and CHECK constraint.

According to the Error

  Error: ALTER TABLE SWITCH' statement failed. The table
  'ApplicationLogs.dbo.SchedulerLog' is partitioned while index
  'staging_SchedulerLog_20160923-120700_pkSchedulerLogId' is not
  partitioned.

The index [pkSchedulerLogId] is not partitioned.

After you partition the index

DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog]
(
    [ID] ASC
    ,[Date]
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [sch_SchedulerLog]([Date])

AND

either add check constraint to both tables

ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1] CHECK  ([Date]<=N'2016-06-30T23:59:59')
ALTER TABLE [dbo].[SchedulerLog]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_2] CHECK  ([Date]<=N'2016-06-30T23:59:59')

OR

remove constraint from staging table.

ALTER TABLE [staging_SchedulerLog_20160923-120700] DROP CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1];

Now you will be able to SWITCH partition

ALTER TABLE [dbo].[SchedulerLog] SWITCH PARTITION 1 TO [dbo].[staging_SchedulerLog_20160923-120700];

Thanks


Changing Table Ownership in Postgres from Postgres User

The problem is, that the postgres user also owns all system catalogs and you can't reassign ownership for them.
You will need to do this manually for each table in each schema you have.
I would first do it for the schemas:
alter schema public owner to django_development;
... repeat for all schemas

Then do it for all tables in the schemas using something like the following (not tested):
do
$$
declare
  l_rec record;
  l_sql text;
begin
  for l_rec in select schemaname, tablename
               from pg_tables
               where schemaname in ('public', .... ) --<< adjust for your schemas
                 and tableowner = 'postgres'
  loop
    l_sql := format('alter table %I.%I owner to django_development', l_rec.schemaname, l_rec.tablename);
    raise notice 'Running %: ', l_sql;
    execute l_sql;
  end loop;
end;

If you have other objects (views, functions, etc) you will need to something equivalent for them as well.


Testing if an element in a PostgreSQL array?

You need to use the ANY construct!

To answer your question, I did the following:

Created a table:

CREATE TABLE arr_test
(
  my_name VARCHAR(15),
  my_int_array INTEGER []
);

Added a couple of sample records:

INSERT INTO arr_test VALUES ('fred', '{1, 2, 3, 5, 7, 9}');
INSERT INTO arr_test VALUES ('bill', '{34, 45, 56, 67}');

Ran my query:

SELECT * FROM arr_test
WHERE 34 = ANY (my_int_array);

Result:
my_name     my_int_array
   bill    {34,45,56,67}

The fiddle is available here. I hope this answers your question - if not, please let me know!


High availability SQL Server without Active Directory

SIOS makes an excellent product that you can use without every supported version of SQL Server and is also SANless. It does block by block transfers and David Klee is a big enough fan that he even does sessions for them, and doesn't get remuneration for it. He just loves the product that much.

I'm not sure if it meets all of your needs but it's definitely worth checking out.

http://us.sios.com/


Mysql replication rebuild dump database lock

--single-transaction is assuring the consistency of the whole instance so yes, across multiple database.

It assumes most tables are transactional (i.e. innodb). For myisam tables these are still read locked briefly for the time taken to dump them.

The mysqldump snapshot method is in the manual.


There are a couple of things to keep in mind when using Large Objects (LOBs) in DB2.

First, when you create a LOB column, this column only holds information about the related LOB, not the data of the LOB.  The table where you create the LOB column (the base table), must have a ROWID column created on it, which will be used to locate the LOB data.  If you do not explicitly create a ROWID column, DB2 will create a hidden ROWID column for you.  If you are adding columns to an existing table and you want to use an explicit ROWID column, then you should add the ROWID before the LOB.  Otherwise, when you create the LOB, DB2 will create the implicit ROWID, and then your second ALTER to add the ROWID will create a second ROWID column.  DB2 will ensure these two columns are always the same, but it will take up space.  :-)

Secondly, for every LOB column you create on a table, a separate LOB tablespace (sometimes called an auxiliary table space) must be defined.  So, if you plan on having 3 LOB columns on a table, you will have to create 3 LOB tablespaces to store your large objects. These tablespaces must be in the same database as the associated base table.

Also, if your base table is partitioned, then you will have to create one LOB tablespace and one auxilliary table per LOB column per partition.

A single LOB tablespace with a single partition can contain up to 16TB of LOB data.

Third, each auxilliary table must have an index defined on it.

Here's an example of creating an auxiliary table (anything that begins with YOUR_ should be replaced with the correct values):

CREATE LOB TABLESPACE YOUR_LOB_TABLESPACE
  IN YOUR_DATABASE
  LOG NO;
COMMIT;
CREATE AUXILIARY TABLE YOUR_AUX_TABLE
  IN YOUR_DATABASE.YOUR_LOB_TABLESPACE
  STORES YOUR_BASE_TABLE
  COLUMN YOUR_LOB;
CREATE UNIQUE INDEX XYOUR_LOB
  ON YOUR_AUX_TABLE;
COMMIT;

Finally, when you're loading data, if the total length of the LOB column and the base table row is less than 32KB, you can use the LOAD utility to put the data in.  Otherwise, you have to use SQL statements such as INSERT or MERGE.

IBM has some recommendations for page size in LOB table spaces.  Only one LOB can be stored per page in a LOB tablespace, so you will want to do some math to minimize lost disk space based on the average size of the files you're storing.  For example, if you have a 17KB average size LOB, you'd want to use 4KB page spaces.  You'd use five pages (for 20KB), which would only waste 3KB of disk space compared to 7KB with 8KB pages (24 - 17), or 15KB with 16 or 32 KB page sizes.

One pro of using a LOB versus the equivalent VARCHAR (FOR BIT DATA) is that, since the data is stored in a separate tablespace, tablespace scans against the base table will be much faster.

References:  1, 2

Having said all of that, if you're planning on most of your data being XML, then I might mention that there is an actual XML Data Type (and built in XML parsing engine called pureXML).

There is a whole guide you can view online (and in PDF) about using XML with DB2.


There are two different issues -- threads connected and threads running.

In a typical, well oiled, production server, you will see this in SHOW PROCESSLIST:

Lots of "Sleep" connections,
Fewer than max_connections lines in the processlist,
One, or a small number, of non-Sleep connections.
Most queries are so fast that they go back to Sleep before you notice it.  I often see "thousands of Queries per second", yet "nothing running".  This is good.

"Sleep" means that the connection is not running a query at the moment.  Instead it might be doing application processing, or it might be part of a "connection pool".

The reason you always see "1" is that you have to run a query (SHOW PROCESSLIST or something else) to get the number.  So the "1" is itself.

You said Threads_connected is always 1.  Did you mean Threads_running?  Note also the capitalization.

What is the value of thread_cache_size?  8 is typical, but the exact value does not matter a lot.

What about Max_used_connections and max_connections?

Is there any sort of "connection pooling"?  Are there multiple clients?

Consider providing me with the info mentioned in tuning here .


Speeding Inserts of longtext or longblob columns

InnoDB Architecture (from Percona CTO Vadim Tkachenko)

  What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)?

Increase innodb_log_buffer_size to 256M
Increase innodb_log_file_size to 2G
I wrote about increasing these to accommodate TEXT/BLOB fields in the past

Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?

  Is there any tuning recommended on my.cnf to improve the insert performance?

Increase the Write I/O Threads (For more info, see Configuring the Number of Background InnoDB I/O Threads)
Change innodb_thread_concurrency to default setting of 0

Proposed Changes

[mysqld]
innodb_log_buffer_size    = 256M
innodb_log_file_size      = 2G
innodb_write_io_threads   = 16
innodb_thread_concurrency = 0

GIVE IT A TRY !!!


Most Effificient Way to Store Ingredients in a Food Database

I think ingredients should go in a master table and another table to map ingredient to recipe. The following is the basic idea:

Create table recipe
(
    recipe_id int not null,
    recipe_name varchar2(50),
    constraint pk_recipe primary key (recipe_id)
);
Create table ingredient
(
    ingredient_id int not null,
    ingredient_name varchar2(50),
    constraint pk_ingredient primary key (ingredient_id)
);
Create table food_ingredient
(
    fk_recipe int not null,
    fk_ingredient int not null,
    measure number,
    unit_of_measurement varchar2(10)
);

Please note that the script above might have some errors due to syntax.

The advantage of this approach:

Finding food or recipe with similar ingredients
Finding food or recipe with a specific ingredient
Convert measurement, well you should incorporate some more tables to store unit of measurement and conversions. You've got the idea

Hopefully this would help.


single query using OR operator vs multiple queries using equals operator

It's a common optimization to use UNION in these cases (at least when you must use a single query):

select * from users where name='smith' /* using single index */
UNION
select * from users where nick='smith'; /* using single index */

In my experience, I don't like to rely on index_merge (union) because the performance is usually not as good as doing the explicit UNION trick like above.

But each case could be a little bit different, because the performance may depend on how many rows are matched by each condition.

@jynus is correct in his comment below that the issue is about a temporary table created for the UNION.

I also want to add that it's hard to make general rules for what type of query is faster. So much depends on how much data you're querying, the version of the software you use, your operating system and tuning, and how many queries per second are running concurrently.

The only reliable way to answer these sorts of questions is to test several solutions yourself, on your server, against your data.


Seek predicate not using all available columns

For a parameterised query It can't just do two seeks on

WHERE A=@P1 AND B=@P2 AND C=@P3 AND D=@P5

and

WHERE A=@P1 AND B=@P2 AND C=@P4 AND D=@P5

Because if @P3 = @P4 that would incorrectly bring back duplicate rows. So it would need an operator that removed duplicates from these first.

From a quick test this end it appears to be dependant upon the size of the table whether or not you get that. In the test below 245/246 rows is the cut off point between plans (this was also the cut off point between the index fitting all on one page and it becoming 2 leaf pages and a root page).

CREATE TABLE T(A INT,B INT,C INT,D INT)

INSERT INTO T
SELECT TOP (245) 1,2,3,5
FROM master..spt_values v1

CREATE CLUSTERED INDEX IX ON T(A, B, C, D)

SELECT index_level,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(),object_id('T'),1,NULL, 'DETAILED')

DECLARE @C1 INT = 3,
        @C2 INT = 4

 SELECT * FROM T WHERE A=1 AND B=2 AND (C=@C1 OR C=@C2) AND D=5

 DROP TABLE T

1 Pages / 245 rows

This plan has a seek on A=1 AND B=2 with a residual predicate on (C=@C1 OR C=@C2) AND D=5

2 leaf Pages / 246 rows

In the second plan the extra operators are responsible for removing any duplicates from @C1,@C2 first before performing the seek(s).

The seek in the second plan is actually a range seek between A=1 AND B=2 AND C > Expr1010 and A=1 AND B=2 AND C < Expr1011 with a residual predicate on D=5. It still isn't an equality seek on all 4 columns. More information about the additional plan operators can be found here.

Adding OPTION (RECOMPILE) does allow it to inspect the parameter values for duplicates at compile time and produces a plan with two equality seeks.

You could also achieve that with

;WITH CTE
     AS (SELECT DISTINCT ( C )
         FROM   (VALUES (@C1),
                        (@C2)) V(C))
SELECT CA.*
FROM   CTE
       CROSS APPLY (SELECT *
                    FROM   T
                    WHERE A=1 AND B=2 AND D=5  AND C = CTE.C) CA

But actually in this test case it would likely be counter productive as having two seeks into the single page index rather than one increases the logical IO.


Upgrade/Migration from SQL Server 2008 R2 to SQL Server 2016

It sounds like your plan is to do an in place upgrade of the OS and the SQL Server. You will experience much less pain and have greater chance for rollback in the event things go awry if you do a side by side upgrade.

Build up the new servers and then migrate to them. This keeps the existing servers working and allows you to migrate and test without affecting the users and data.

Keep in mind too, that there could be many changes that could adversely affect the applications supported by this SQL Server. Having the extra servers will provide the ability to test those applications and find out what mitigation tasks you may need to take.

Once you have the tasks and migration process detailed out, you can utilize log shipping from the main server to the new server to facilitate a low downtime migration.

Also consider using Always On Availability Groups instead of clustering as it may provide you a better HA/DR.

From a host name not changing perspective, utilize CNAME DNS entries. This way you can use new servers, and then when ready cut the DNS entry over to the new server name. To the users, they think nothing has changed.


PowerShell 'get-ClusterQuorum' output is not showing 'QuorumType' column

Perhaps the default formatting for the output of that command has been modified. You can override the default formatting with the Select-Object cmdlet.

Get-ClusterQuorum | Select-Object Cluster, QuorumResource, QuorumType

Using that command will ensure that those three columns are outputted to the console.


Need a solution to store cheaply a big MongoDB collection

Short answer - this is a tricky proposition and your available options really depend on how much effort you wish to put in versus the cost of your option 2 (which I will talk about below).  Having never done it, I can't speak to the ease (or lack thereof) of using another cloud based solution for storing the data elsewhere, but I can comment on some of the options on the MongoDB side.

First up, in terms of putting in the effort, a slight twist on your 2 proposals:

Create a less expensive instance in the cloud
Implement aging/removal on the live database (either manually, or with TTL or capped collections)
Use a tailable cursor to pull all operations out of your oplog and insert into your new instance(s).  Alternatively you could look at the Mongo Connector for this functionality to avoid writing it yourself, but that will not work out of the box with TTL (TTL deletes are replicated from the primary and would need to be filtered out manually)

Caveats:

Even though it might be less expensive, it still has to be able to keep up with your operations - you do not want to have it lag behind and end up missing data
This is essentially doing a form of replication yourself, it's not officially supported anywhere - you have to support this yourself
You will need to come up with your own method to detect if this is broken so you can stop deletes, recover from failure, and your own method to restore data etc.
Manual deletes, or TTL based deletes will eventually lead to fragmentation and inefficient re-use of space, so you will probably need to regularly compact and/or repair to reclaim space

The above is usually too much effort/hassle and too much of an unknown from a supportability perspective unless you plan to run this way long term and are very comfortable supporting such a customized solution.

Your option 2, is often far easier and there are ways to save on costs.  You can, for example, have a hidden secondary that is different from the rest to reduce costs.  Imagine this kind of set up:

Primary, Secondary - both equivalent spec, enough memory to hold working set for live site, generally expensive - uses capped collections to manage data storage
Hidden Secondary - less expensive spec (RAM, slower disk), never takes live traffic, keeps all data, perhaps no indexes also - good source for snapshot backups too usually

To get the hidden secondary set up correctly will take some work.  You need to configure it to not build indexes, you need to pre-create any Capped collections without the capped configuration so they do not delete data (they will then continue to grow).


Can I launch a stored procedure and immediately return without waiting for it to finish?

It looks like there are multiple ways to accomplish this, but I found the simplest way was Martin's suggestion of setting up the procedure in a SQL job, and starting it using the asynchronous sp_start_job command from my stored procedure.
EXEC msdb.dbo.sp_start_job @job_name='Run2ndStoredProcedure'

This only works for me because I don't need to specify any parameters for my stored procedure.
Other suggestions that may work depending on your situation are

Using the SQL Service Broker like Martin and Sebastian suggest. This is probably the best suggestion if you don't mind the complexity of setting it up and learning how it works.

Running the process asynchronously in the code that is responsible for executing the stored procedure, like Mr.Brownstone suggested.
Not a bad idea, however in my case the stored procedure gets called from multiple places, so finding all those places and ensuring they call the 2nd procedure too didn't seem that practical. Also, the 2nd stored procedure is fairly critical, and forgetting to run it could cause some major problems for our company.

Make the 1st procedure set a flag, and setup a recurring job to check for that flag and run if its set, like Jimbo suggested. I'm not a big fan of jobs that run constantly and check for changes every few minutes, but it certainly is an option worth considering depending on your situation.


Aurora postgres grant permission to pg_attribute

Only a superuser can do that, and you probably won't get a superuser in a hosted database.

You will have to drop the history tables and re-create them new as inheritance children (without specifying the duplicate columns!).


What will be best (for performance) transaction isolation level?

READ COMMITTED imposes fewer overheads as PostgreSQL does not have to do dependency tracking.

In practice the difference is likely to be minimal.

Isolation level should be selected based on correctness, not performance. It's not possible to determine whether READ COMMITTED is sufficient from the queries provided, but I don't see a strong reason to suspect it not to be.

There are almost certainly many other ways to improve performance before playing with things like isolation levels anyway.


Your problem can be solved with the following set of tables:

A table location, with attribute id, and attributes common to all location (like position, etc.)

A table for each type of location, with all the attributes specific with that table, and an attribute location_id which is a foreign key for the table location.

Then you could consider of adding, if necessary, an attribute to the table location that represent in a compact form all the types associated to that location (for instance an array if you use PostgreSQL, or something similar).


"show procedure status" displaying the non existing databases stored procedures in mysql server

Yes. There is not much else to say about your question. If you have recovered the mysql.proc table, but not the databases, you may see the procedures but not the databases.

This is because you can export and import them logically "--routines", which will create CREATE PROCEDURE statements, or physically, recovering the mysql.proc table. In any case, they are not real physical objects of each schema subdirectory (unlike tables, partitions and triggers).

If they annoy you, you can safely delete them from the table, as they are just rows.


Estimation of appropriate innodb buffer pool size

Bad!  The innodb_buffer_pool_size should never be set bigger than the amount of RAM you have.  An general rule is to set it to 70% of available RAM.  For 128GB of RAM, 100G is probably fine, assuming you don't have a lot of other applications running on it.

The buffer_pool is a cache.  If the data that you actively use is only 20G, then about 20G will work fine, almost as good as 100G.  And you don't need to increase your RAM.

If you are repeatedly reading all of your 307G of data and indexes, then you do need 350GB of RAM.  But probably you could make some simple changes to avoid such repeated reading, thereby getting down to a civilized "working set", such as 20G.  (No, there is no simple way to estimate the "working set size".)

Bottom line:  Don't get more RAM.  Set the buffer_pool to 100G.  Fix any slow queries.

More discussion.


Can mirror and witness have same port number?

As detailed in this MSDN document, the port number must be unique within any given computer:

  Note that if more than one server instance on a given computer requires a database mirroring endpoint, specify a different port number for each endpoint.

TCP ports can only be served by a single process.  When you try to create two endpoints with the same port number on the same computer, you are attempting to have two processes monitor the same port.

This image is contained on that page, and makes it quite clear:

If you are using unique computers for all machines in the mirroring configuration, you can configure each computer with the same port.


Does one of the normal forms require separation of repeating clusters of values into another table?

There is no normal form which would have you move those columns to a separate table. Normalization is a process in the logical design of tables that discusses functional dependency between a table's columns. If there is no un-resolved functional dependency there is no more normalization to perform.
Further, a logical table represents a class of objects, events or ideas from our business domain. There is a real-life thing that the table holds data for. What is that thing that your proposed "Sales Types" table would hold? Likely your business users would shrug at this question.
That said, during the physical implementation (distinct from the logical design before) you may choose to move columns between tables for reasons other than functional dependency. This can be confusing because the end results look the same - columns moved to new tables. The difference is why we move these columns. This will be important later when change happens. We need to know why we did someting so we will know why we may choose to undo it. We undo normalization if the business rules change. We undo physical implementation if the desired performance profile changes.
In the context of physical implementation your proposed table looks like a dictionary encoding scheme. There is nothing wrong with doing this. If your application benefits overall from this extra development work then go fot it.
Many DBMS offer compression natively. Column-oriented storage is particulary effective at this if your product offers this choice. By using native features you gain the performance benefits you seek without taking on the development and maintenance cost. I'd recommend you stress-test any native feature to see if it can deliver your desired performance.


What is the "Data partitioned, Shared-Nothing, Parallel Database" solution for PostgreSQL ?

There is pl/proxy, which provides a basic federation capability, but it's not an engine optimised for this sort of work.  Greenplum is a commercial product that does have a shared nothing engine that might do what you want.  However, it's not cheap - less expensive than Oracle but more expensive than SQL Server.

There are no pure open-source engines of that type for PostgreSQL.  However, you might get what you want from PL/Proxy.


Postgresql 10.x installer configures password wrong? (Windows)

If you are using the EDB installer, when it asks for a password, that is the password for the PostgreSQL user named postgres, not for the user named tom.

Perhaps there is a way to make our 3rd party app connect as postgres?

Or if you want it to use the database role whose name matches your Windows account name, you should be able to connect with

psql.exe -U postgres postgres

And then:

create user tom with password 'hi tom';


Partitioning with AUTO_INCREMENT primary key

Simply put, you need to put entry_ts in the PRIMARY KEY before you can partition the table.
The table needs to look like this:
CREATE TABLE `TABLE` (
  `HEADER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STATUS` int(11) NOT NULL DEFAULT '0',
  `CREATED_AT` varchar(45) NOT NULL,
  `TYPE` varchar(45) NOT NULL,
  `VERSION` varchar(45) NOT NULL,
  `ID` varchar(45) NOT NULL,
  `ORIGIN` varchar(45) NOT NULL,
  `PAYLOAD` json NOT NULL,
  `ENTRY_TS` datetime NOT NULL',
  PRIMARY KEY (`HEADER_ID`,`ENTRY_TS`)
);

So, run this:
ALTER TABLE `TABLE`
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (`HEADER_ID`,`ENTRY_TS`);

Then, you can partition the table since ENTRY_TS is part of the PRIMARY KEY.
ALTER TABLE TABLE
    PARTITION BY RANGE(MONTH(entry_ts)) (
    PARTITION JANUARY VALUES LESS THAN (2),
    PARTITION FEBRUARY VALUES LESS THAN (3),
    PARTITION MARCH VALUES LESS THAN (4),
    PARTITION APRIL VALUES LESS THAN (5),
    PARTITION MAY VALUES LESS THAN (6),
    PARTITION JUNE VALUES LESS THAN (7),
    PARTITION JULY VALUES LESS THAN (8),
    PARTITION AUGUST VALUES LESS THAN (9),
    PARTITION SEPTEMBER VALUES LESS THAN (10),
    PARTITION OCTOBER VALUES LESS THAN (11),
    PARTITION NOVEMBER VALUES LESS THAN (12),
    PARTITION DECEMBER VALUES LESS THAN (13)
    );

Please try this in Staging with an empty table.
GIVE IT A TRY !!!


Suggestion for a contact management database design

I'm seeing a few misses.
First, the role table.  If one user can belong to more than one role, you're going to need another table between role and user.  1 table to store the roll(like you have already) and another to store a junction table which will store the relation between a role and a user.  That table would probably only have two columns, role_id and user_id.  The user table would not need to identify the role unless it was a primary role or default role.  That could also be accomplished in the junction table.

Similar situation with Company - Member Type.  Since you only have a single column in the company table, you will only be able to store one member type per company.  If the requirement is to have companies be able to be assigned to more than one member type, you will need a junction table to store company|member_type relationships.

One last thing I noticed, is there is currently no relation between Employee and Company other than the address.  That will definitely get confusing.
Since you want contacts to be associated with multiple companies, you will need another junction table to store the contact_id | company_id relationship. There might be a clever solution using your employee table.  Since that essentially is the only direct connection between employee and company, maybe just change that to the junction table, company_employee.  Then add a boolean/bit column (IsEmployee) to indicate whether the contact is actually an employee of the company.


Using SELECT in the WHERE clause of another SELECT

Is this what you're aiming for? Make sure the fields that are being compared are comparable (i.e. both fields are numeric, text, boolean, etc).

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = (SELECT someID FROM table WHERE blahblahblah)

If you wish to select based on multiple values:

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId IN (SELECT someID FROM table WHERE blahblahblah)


If you wrap
select
  max(x.members_present),
  max(x.patient_category)
from
(
  <your query goes here>
) as x
group by
  x.clientvisit_id,
  x.client_id

around your current query you'll collapse those rows into one, removing the blanks.


You may do an OUTER JOIN directly with a list of values, like this:

SELECT v.id,tablename.* FROM tablename RIGHT JOIN (values (1),(3),...,(9)) as v(id)
  ON (tablename.field=v.id)


Normally you should never do so because other revisions may be deleted after compacting the database.
You should include all necessary fields to recompose(reprint) the invoice in the invoice document, like: product description, product price, total, and so on.

This is the same if you would store invoices in a rdbms.


Updating an indexed column performance impact in SQL server

Modifying the value of a field used in an index will certainly take more time than modifying the value of a field not involved in an index.  Having said that, the WHERE clause in your UPDATE statement could be used to speed the search for matching rows when the field in question is part of an index.  In this example, where you have a boolean field present in 100 million rows means the selectivity of the field is so poor that a table scan will likely be required anyway.  I would estimate that updating this particular field would be quicker if there was no index on it.

However, to answer your question of whether or not there will be performance impact on your system is really only answerable by you since there are a many other variables to consider such as storage speed, RAM availability, etc.


Take full and differential backup on secondary replica - SQL Server 2014

I need to setup full-backup and diff backup. Can I do it on Server C?

You can do a copy only full, but unless C is the primary you cannot do a differential database backup.

  Running below query on server C but it throwing 1 which means I can't take backup on that server.

A return value of 1 means it is "preferred" but doesn't necessarily mean you can run any type of backup on it.


How to subtract records from a table based on their existence in another

It seems the answer was hiding in plain sight:

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

or

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

depending on which is most appropriate.

I don't know how I failed to see it first time around!


On Windows, there isn't an exact equivalent to a .bash_history because cmd.exe is an intrinsically different type of shell. I think you're referring more to the .psql_history (see related question).

There is a "SQL Shell" available in the SQL Command Line Terminal of the EnterpriseDB Windows distribution of PostgreSQL that supports a command history. However, I prefer to use the Windows command prompt instead of the GUI.

The error message you are getting indicates that psql.exe was built without readline support (reference).

The best way I have found to run psql via the normal Windows command prompt is to create a shortcut:

Target: %SystemRoot%\system32\cmd.exe /U /c "chcp 1252 & C:\Progra~1\PostgreSQL\9.6\bin\psql.exe -U database_username -d database_name"

The command history can be controlled via Command history Buffer size in the Options pane:

And you can adjust the scrollable output via Screen Buffer Height in the Layout pane:

This will get you a history via the up and down arrows, but only within your current session. It will not be preserved across sessions.


enforcing data integrity for sequential relations

You can make use of an EXCLUSION constraint and tsrange datatype instead of two timestamps.

create table trip_segments (
  segment_id serial primary key,
  travel_ts tstzrange, -- departure time to arrival time
  trip_id integer references trips (trip_id),
  EXCLUDE USING gist (trip_id WITH =, travel_ts WITH &&)
);

-- first segment
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 12:00,2013-01-30 20:00)', 1);

-- second segment overlaps and fails
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 19:55,2013-01-30 22:00]', 1);

You get:

  ERROR:  conflicting key value violates exclusion constraint "trip_segments_travel_ts_excl"
  DETAIL:  Key (travel_ts)=(["2013-01-30 19:55:00+11","2013-01-30 22:00:00+11"]) conflicts with existing key (travel_ts)=(["2013-01-30 12:00:00+11","2013-01-30 20:00:00+11")).

The exclusion is set-up exclusively for time ranges specific to each trip_id.

insert into trips (trip_nm) values ('their trip'); -- add other trip

-- segment overlaps with trip_id=1, but the exclusion is setup specific to trips
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 19:55,2013-01-30 22:00)', 2);

Note that timestamp ranges cannot be specified backwards (ending timestamp must be equal or higher than the starting one), and their boundaries are either inclusive ([]) or exclusive (()) boundaries, being the default [), i.e. an inclusive starting and exclusive ending time.


overwrite table with data from another table - SQL

Best is to use SSIS (data Import/export) to do this. It has the functionality to just transfer selected tables from source to destination. Optionally, you can even save the package so that it can be reused for future/repeated transfers.

EDIT: Adding more screenshots for clarity.


Copying Db permission between same instance

I use powershell based Sync-DbaSqlLoginPermission

This command syncs SQL login permissions from one server to another including login permissions, roles, database permissions and more. By default, all logins are synced. The -Logins and -Excludes parameters is autopopulated for command-line completion and can be used to sync only specific logins.
If a matching login or securable does not exist on the destination, the permission sync will be skipped.
Credential removal and application role syncs not currently supported.

Or You can use TSQL based Script DB Level Permissions v3


UPDATE table element using data and id from another table

What you want is called an UPDATE JOIN

First you must create a query that has the MAX(b_foo) for every a_uid:

SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid;

Making this a subquery, you can perform the UPDATE JOIN as follows:

UPDATE A INNER JOIN
(SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
ON A.id = C.a_uid SET A.foo = C.max_b_foo;

or you can get fancy and do

UPDATE A INNER JOIN
(SELECT a_uid uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
USING (uid) SET A.foo = C.max_b_foo;

GIVE IT A TRY !!!


Is there a way to find out what queries are run the most against a particular table?

  T-SQL – Get a Historical Record of SQL Statements Hitting a Table

  This query checks the sys.dm_exec_query_stats system view and the
  sys.dm_exec_sql_text table valued function.

  Also because these are system tables, you will need to be logged into
  SQL Server Management Studio (SSMS) with an account that has
  sufficient access permissions.

  Here is the query:

 SELECT
 qryStats.last_execution_time AS [Time]
 ,qryText.TEXT AS [Query]
 ,DB_NAME(qryText.[dbid]) AS [Database]
 ,OBJECT_NAME(qryText.[objectid]) AS [TableName]
 FROM sys.dm_exec_query_stats AS qryStats
 CROSS APPLY sys.dm_exec_sql_text(qryStats.sql_handle) AS qryText
 WHERE qryText.TEXT LIKE '%TableName%'
 ORDER BY qryStats.last_execution_time DESC

  Running this query will show you in reverse chronological order
  what SQL was run against a particular table on your database server.

  If you are using stored procedures then you’ll see that rather than
  showing the EXEC statement, SQL Server will have logged the CREATE
  statement for the stored procedure itself. For our purposes this is
  useful since it allows us to also find stored procedures that are
  having an effect on the table that we are interested in.

  The downside of this query is that it will not show you what user
  account is running each query.

  source


Do Inactive MySQL Databases Consume Memory?

PROLOGUE
As I mentioned earlier in Adding new tables -- memory usage increases, adding new tables increases memory usage. That being the case, tables that are not in active use would still consume memory in the INFORMATION_SCHEMA.
Determining what is active is an arbitrary process. If you need to see when was the last time a table had written changes, you have to find out based on the Storage Engine of each table.
MyISAM
Learning the last time a MyISAM table had written changes is straightforward.
Suppose you need to see all MyISAM tables that have not been written to in over a month. Simply run this query again INFORMATION_SCHEMA.tables:
SELECT table_schema,table_name,update_time FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND engine='MyISAM' and update_time > (NOW() - INTERVAL 1 MONTH);

Note: update_time for a MyISAM table is the timestamp of the .MYD file. If you run ANALYZE TABLE mydb.mytable;, the index statistics are written to the .MYI file. The .MYI's file timestamp is not reflected in the INFORMATION_SCHEMA.
InnoDB
Getting InnoDB timestamps is quite a challenge because InnoDB does not store update_time in the INFORMATION_SCHEMA. If innodb_file_per_table is disabled, everything InnoDB and its grandmother is encased in the system tablespace file ibdata1. Gettting the timestamp of ibdata1 gives you the last time any write was received. That could be anything

INSERT, UPDATE, DELETE (altering data and index pages)
ANALYZE TABLE (altering index pages)
SELECT (data snapshots via MVCC)

Therefore, with innodb_file_per_table disabled, there is not way to know.
On the other hand, if innodb_file_per_table is enabled, the question remains: HOW DO YOU FIND OUT THE LAST TIME AN INNODB TABLE WAS WRITTEN ?
You must check the file timestamp of the .ibd.
Rather than reinvent the wheel, please read my posts on how to get that info

Dec 21, 2011 : Fastest way to check if InnoDB table has changed
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?

EPILOGUE
The main point to keep in mind is to find out timestamp of the table and subtract it from NOW() (or UNIX_TIMESTAMP(NOW()) from the OS point-of-view). You have to arbitrarily determine how old is too old for an inactive table.


Why would SQL Server automatically remove manually created indexes?

SQL Server does not automatically* remove indexes - neither does automatically execute any other DDL statement that alters the structure of your tables, views, functions, indexes, etc.

If your indexes are truly dropped, then some user or application or job is doing it.

*: SQL Server on Azure can automatically remove indexes if the option is enabled. it does so if they are duplicates or have been unused for 6 months or so. Not on version 2008 though or any other desktop version.

To find out who or what, you have various options:

write a DDL trigger to catch the DROP INDEX statements (and even disallow them from deleting your index). See Ken Kim's answer.
SQL Trace
enable Audit
use XEvents (Extended Events)
use SQL Profiler
read the transaction log


MongoDB: All commands spit out "not authorized on admin to execute command"

Let's try this:

Connect to your DO droplet.

In your /etc/mongod.conf, comment out 'security' and restore bindIp to value 127.0.0.1

Restart your mongod instance.

Connect to mongo (at this point without security).

Create you admin user in mongo with something like this:

use admin
db.createUser(
  {
    user: "myUserAdmin",
    pwd: "abc123",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
  }
)

Make sure the user was successfully created.

Go back to your /etc/mongod.conf. Set your bindIp value as: 127.0.0.1,YOUR_DO_DROPLET_IP (no spaces between).

Uncomment 'security', keep 'authentication' commented out, add below it 'authorization: enabled'

Restart your server's mongod instance.

Connect to mongod remotely by using:

mongo YOUR_DO_DROPLET_IP -u myUserAdmin -p --authenticationDatabase admin

References:
MongoDB Authentication
Enabling MongoDB Authentication


Does rebuilding an index update the last_ddl_time attribute in ALL_OBJECTS/DBA_OBJECTS?

Yes.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> create table tttt1(c1 number);

Table created.

SQL> create index iiii1 on tttt1(c1);

Index created.

SQL> select last_ddl_time from user_objects where object_name = 'IIII1';

LAST_DDL_TIME
-------------------
2018-05-07 16:32:46

SQL> alter index iiii1 rebuild;

Index altered.

SQL> select last_ddl_time from user_objects where object_name = 'IIII1';

LAST_DDL_TIME
-------------------
2018-05-07 16:33:10


Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

(This answer is probably more than you wanted.  It is an in-depth discussion of the source of the problem, plus multiple solutions.)

Long ago, when MySQL added CHARACTER SET utf8, it needed up to 3 bytes per character.  And indexing was limited to 767 bytes, enough for VARCHAR(255).

When utf8mb4 was added (because utf8 was incomplete), the encoding needed up to 4 bytes/char, but the index limit was not increased.  (Oops.)  So, indexing became limited (for utf8mb4) to 191 characters ((767-2)/4).

That problem lasted throughout 5.5 and 5.6 (MariaDB 5.5 - 10.1?).  5.7 cleaned things up.

There are many solutions to this issue:

Change back to utf8.  Most users can get away from this.  But, if you need Emoji or all of Chinese, you need utf8mb4.
Limit your indexed VARCHARs to 191 instead of 255.  This can usually be done safely, but you should check the data, and understand that it limits the strings that you can use.
(see jkavalik's comment) Use "prefix" indexing:  INDEX (col(191)).  This has two negative effects:  (1) If the index is really UNIQUE, you get the wrong interpretation of the uniqueness constraint. (2) Performance probably suffers because of the inefficiencies of prefixing.
In 5.5.14, 5.6.3, 5.7.7, MariaDB 5.5, the following combination raises the limit from 767 to 3072:  innodb_large_prefix=1 (defaulted on in 5.7.7), innodb_file_format=Barracuda, innodb_file_per_table=1, ROW_FORMAT = DYNAMIC or COMPRESSED.

It would be nice if WordPress proactively tackled the problem you encountered.

(Update)  In later versions, these are deprecated (5.7 and 10.2?) because they are no longer necessary: innodb_file_format, innodb_file_format_check, innodb_file_format_max and innodb_large_prefix.  If you get an error to that effect, simply don't bother setting them.  They are actually removed in 8.0 / 10.4(?)


I believe you made a mistake in your join predicate. Try:
WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo WHERE id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON p.parent_id = foo.id
    )

    SELECT id FROM chainIDsUpwards

It is a common mistake, and I often do it myself. I find it easiest to get it right if I start with the CTE:
    SELECT foo.id, foo.parent_id
    FROM chainIDsUpwards p
    JOIN foo p

and then think of the CTE as a linked list where I need to find the next element:
        ON p.parent_id = foo.id


Check constraint on nullable data, how to approach?

Use a CHECK constraint.

CHECK constraints do not fail when the value is null. If the value is null, the condition of the check constraints usually evaluates to UNKNOWN and the row is accepted. Only when the condition evaluates to FALSE, the row is rejected.

  Another option is to use a scalar UDF inside the check constraint.

No, no, no. User defined functions are allowed inside CHECK constraints but there are several problems, often because they are used to implement complex behaviour/constraints, like searching a whole other table. In your case, a UDF would not do anything that can't be done with standard date functions. So don't use a UDF.

  The final option I've thought of is to use an AFTER INSERT/UPDATE trigger to check the date and rollback if the date is invalid.

You could but a CHECK constraint is simpler. Triggers are far more difficult be tested.

  I've spoken to a colleague and he says technically I'm checking a business rule in the database, so this check should be pushed off to the application.

This is a rather "hot" debate about whether business rules should be in the database or in the applications. I find the approach "all business rules in the application" a bit naive. Primary and foreign key constraints implement business rules for example, should we abolish them from our database, too? Another reason for keeping business rules closer to the database is that we may have more than one application using it. We then find implementing the same rules again and again, in possibly different languages/frameworks when it could have been done once, in the database.

One problem exists though with the specific constraint, see below*.

You case could be something like below. Tested at rextester.com:

CREATE TABLE  person
( person_id INT NOT NULL PRIMARY KEY,
  birthdate DATE NULL,
  CONSTRAINT is_adult
    CHECK (birthdate <= DATEADD(year, -18, GETDATE()))
) ;

Inserting null and valid dates succeeds:

INSERT INTO person
    (person_id, birthdate)
VALUES
    (1, '19500101'),
    (2, NULL) ;

SELECT * FROM person ;

person_id  birthdate
1          01.01.1950
2          NULL

But invalid dates fail:

INSERT INTO person
    (person_id, birthdate)
VALUES
    (3, '20010101') ;

  The INSERT statement conflicted with the CHECK constraint "is_adult". The conflict occurred in database "rextester", table "dbo.person", column 'birthdate'.

*: One problem with this particular case/constraint - and not because the column is nullable: one of the function used - GETDATE() - is not deterministic. It won't - obviously - yield the same value if it is run a day or a year later. It's unlikely that you'll have a problem because once one is over 18, one stays over 18. Age increases by time.

Weird scenario: Someone changes the date of the system running the database and puts it in, say 1986. Suddenly the previously valid rows become invalid. What will happen then? (Frankly I've no idea, have to test that!)

Consider the above and it might be more appropriate to handle this constraint in a procedural way, either in the applications or by handling the table only through INSERT and UPDATE procedures that enforce the constraint.


Foreign key not able to get one to many working

For anyone else stuck this is how I had to add my foreign key on the actual points table and not the player table I no school boy error but thanks to all for helping.

CREATE TABLE [dbo].[player_points](
    [id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_player_points_id]  DEFAULT (newid()),
    [playerId] [uniqueidentifier] NOT NULL,
    [playerName] [nvarchar](255) NULL,
    [teamId] [uniqueidentifier] NULL,
    [points] [int] NULL,
    [pointsToRedeem] [int] NULL,
    [dateToBeAwarded] [date] NULL,
    [approvedBy] [varchar](50) NULL,
    [approved] [bit] NULL,
    [approvedDate] [date] NULL,
    [siteId] [uniqueidentifier] NULL,
    [sentApproveEmail] [bit] NULL,
    [isActivated] [bit] NULL,
    [authUserName] [nvarchar](50) NULL,
    [authCreatedDate] [date] NULL,
    [modifiedDate] [date] NULL,
    [modifiedBy] [nvarchar](255) NULL,
    [playerNumber] [nvarchar](50) NULL,
    [isProcessed] [bit] NULL,
    [sentEmailToAdmin] [bit] NULL,
    [sentEmailToUser] [bit] NULL,
 CONSTRAINT [PK_player_points] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[player_points]  WITH CHECK ADD  CONSTRAINT [FK_PlayerPoints] FOREIGN KEY([playerId])
REFERENCES [dbo].[player] ([id])
GO

ALTER TABLE [dbo].[player_points] CHECK CONSTRAINT [FK_PlayerPoints]
GO


OBSERVATIONS

I see there is nothing Innodb-related in the my.cnf.
Look for Innodb-related error messages expressing why mysqld failed to start in the error log.

My first guess would be look at the ib_logfile0 and ib_logfile1 in /var/lib/mysql.

If they have been set to size different from the default, that probably will prevent mysqld's startup. The default for innodb_log_file_size is 5M.

SUGGESTION #1

If RoundCube's installation overwrote the my.cnf, see if you have a physical backup of the previous my.cnf. Put that back in place and run

service mysql start

SUGGESTION #2

Take the filesize of ib_logfile0 and divide by 1048576. Let's say it is 12M.

Put that in my.cnf

[mysqld]
innodb_log_file_size = 12M

then run

service mysql start

Give it a Try !!!


In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

According to the Book

Page 198 says the following:

Paragraph 6 : A MyISAM B-tree consists of leaf and nonleaf nodes, or pages.
Paragraph 7 : Both leaf and nonleaf nodes key values and pointers to the record positions in the datafile. Nonleaf nodes additionally contains pointers to child nodes.

Given this description

a unique index would have more nonleaf nodes. This would lend itself mode towards ordering and searching.
nonunique index would requires less nonleaf nodes. This would lend itself mode towards doing range scans (tables and index)
A covering index (which would contain all needed columns for specific SELECT queries) would combine the best and worst of both. This would allow for a range scans that would need more nonleaf nodes  and provide for ordering/searching. The additional benefit would be bypassing the need for reading table data if all needed columns are present and accounted for in the index.
I mentioned other aspects in my past post Benefits of BTREE in MySQL

SPACE

Unique Indexes would take up more space for nonleaf nodes.
Covering indexes would need more space than a nonunqiue index but would have a higher multi-columns index cardinality that can approach the actual row count of the table.

PERFORMANCE (Heavy-Write Environment)

SELECTs would be very fast for exact keys
SELECTs that only touch a Covering Index would be extremely fast
Random INSERTs can be a nightmare due to rotations/rebalancing of nonleaf nodes
UPDATEs that change key values can be a nightmare due to rotations/rebalancing of nonleaf nodes
The higher the cardinality, the more nonleaf nodes, the better the performance
The lower the cardinality, the fewer the nonleaf nodes, the worse the performance
The more columns in the index increases the effects of the cardinality

MEMORY
Depends on Storage Engine

MyISAM : The key buffer only caches index pages. Covering indexs are your best friend here
InnoDB : Buffer Pool Needs to be Big Enough to Hold Data Pages, Index Pages, and Index Page Changes (See InnoDB Architecture, What are the main differences between InnoDB and MyISAM?)


SQL Server 2012 Performance - Reads per second

The question itself is too nebulous. You asked how many Selects per second the database can handle? Well, can handle before what happens? What level of performance is acceptable? Do you need results returned in milliseconds, seconds, minutes? What is acceptable? How many rows are being returned from these Selects? What does the query plan look like?

Ultimately no one here is going to be able to tell you what you want to know. There are too many details you didn't tell us. But you can certainly find out for yourself. It's not at all difficult to stress test your database, to find this out for yourself. If you are a developer, you could easily write a C# program that creates threads that performs these Selects. It would be quite easy to measure performance as you gradually increase the number of threads. If you are not a developer, I have to believe there are stress test apps you can buy, or find open source, that will enable you to measure this. If determining a rough idea of how many users your database can accept is that important, you need to be doing this anyway.


Database design for an E-commerce website

If you know most of the attributes your products will have ahead of time, you could hard-code them into the tables for different product types, such as:

books
-----
  id
  price
  title
  author
  isbn
  etc....

mobile_device
-------------
  id
  price
  size
  colour
  model
  etc...

You could also try something like this:

base_product
------------
  id
  base_price
  product_type

book_product_attributes
-----------------------
  base_product_id (FK to base_product)
  title
  author

mobile_dev_product_attributes
-----------------------------
  base_product_id (FK to base_product)
  model
  colour

This way, you can group your common attributes in a base product table, and more specific attributes are stored in other tables when necessary.

This will work if your product types are mostly static. On the other hand, if you have no idea what product types or attributes you'll need in the future, and entity-attribute-value based system might work better. The idea behind that model is that you have a table for the different attributes you might have, a table for the different entities in your system, and a table for the attribute values that an entity has. Example:

entities
--------
  id

attributes
----------
  id
  name

values
------
  id
  entity_id
  attribute_id
  value

The data might look like this:

entities
ID
----
  1

attributes
ID |  name
----------
1  |  title
2  |  author

values
ID  | attribute_id   | entity_id   | value
---------------------------------------------------------
1   | 1              | 1           | "Great Expectations"
2   | 2              | 1           | "Charles Dickens"

This data very briefly describes a product which has two attributes: title, and author, with the valus "Great Expectations" and "Charles Dickens", respectively.

Be aware that using an EAV database can make queries very awkward. With enough data and the wrong model design, it's also possible to run into performance problems. The example I gave was very simple but more realistic designs tend to be more complicated than that. It can take time to get this kind of database correct and it's not always the best solution.


Will deleting data in master automatically delete data in slave?

Yes, it will automatic apply the delete on the slave, with some restrictions:

If you have replicate-do-db=db_name enabled, it will automatically filter out the statements to the given database names
If you have replicate-ignore-db=db_name enabled, it will automatically filter out statements to the given table names


Postgresql - Multiple counts within a join

You were nearly there, you can do this with case when ... end

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount,
sum(case when token.type = 'LOCATION' then 1 else 0 end) AS locToken,
sum(case when token.type = 'PERSON' then 1 else 0 end) AS personToken
FROM        tweet
INNER JOIN  token on token.tweet_id = tweet.id
WHERE       tweet.process_id = 5
GROUP BY    tweet.id;


How to delete mdf file in use but not attached?

Try to set the database (returned by the query in sys.master_files) Offline with

Alter database [YourDB] set offline;

If the database is still seen in the dmv, it may still be possible to set it as offline (and then you'll be able to delete the file).

But if you want to delete the file, you could also just try to drop the database

Drop database [YourDB];


Since you have done the following:

SHOW VARIABLES LIKE 'innodb%';
No InnoDB-related variables exist in my.cnf

and nothing comes up, please place this in my.cnf
[mysqld]
innodb

and start mysql and see if it comes up
UPDATE 2012-05-15 15:59 EDT
Since it fails to start up, you may need uninstall MySQL 5.5.24 and go back to MySQL 5.5.23 or whichever version you were last running with.
UPDATE 2012-05-15 16:06 EDT
Please run tail -30 on the mysql error log and paste that in the question.f it is not configured, go to the data folder and do ls -l *.err to find it.
UPDATE 2012-05-15 17:54 EDT
Based on the display you pasted in, you evidently had MySQL 5.1 running before. It  just so happens that MySQL 5.1 has a table called mysql.plugin. It may have been populated before. It probably had something concerning the InnoDB plugin. MySQL 5.1.38 introduced the InnoDB plugin which has new features that are now native to MySQL 5.5's InnoDB. In other words, MySQL 5.5 does not need any plugin to run the new InnoDB.
Please run this query:
SELECT COUNT(1) FROM mysql.plugin;

If you get 1, that's probably the MySQL 5.1 InnoDB Plugin interfering. Try moving it over like this:
CREATE TABLE mysql.plugin_new LIKE mysql.plugin;
ALTER TABLE mysql.plugin RENAME mysql.plugin_old;
ALTER TABLE mysql.plugin_new RENAME mysql.plugin;

Afterwards, please restart mysql and see if InnoDB comes back


The card is UNF. Everything is one row.
3NF is when you make a list of the things, and each list of things has a unique item list, and you link them using thing-item-id.

[list] Form [items] FormID, FormName, FormTemplate (definition of    report)
[list] Address [items] AddressID, StreetNumber, StreetName...    (address data)
[list] Vet [items] VetID, VetName
[list] Species    [items] SpeciesID, SpeciesName
[list] ExaminationType [items]    ExaminationTypeID, ExaminationTypeName, Cost
[list] Patient [items]    PatientID, PatientName, PatientSpeciesID REFERENCES Species (SpeciesID)
[list] Appointment [items] PatientID REFERENCES Patient(PatientID), ExaminationTypeID REFERENCES ExaminationType(ExaminationTypeID), Date, VetID REFERENCES Vet(VetID)

Data is uniquely stored, referenced in table containing transactional data.
Form template would have RDL for creating the card, from the relevant list data.
The idea behind 3NF is store related information in the same list, only once, and reference that data wherever applicable.
Gets complicated when Vet changes name, but you want to reprint previous statement with Vet's previous name ... :)


How do I decompose ctid into page and row numbers?

SELECT (ctid::text::point)[0]::bigint AS page_number FROM t;

Your fiddle with my solution.

@bma already hinted something similar in a comment. Here is a ...

Rationale for the type

ctid is of type tid (tuple identifier), called ItemPointer in the C code. Per documentation:

  This is the data type of the system column ctid. A tuple ID is a
  pair (block number, tuple index within block) that identifies the
  physical location of the row within its table.

Bold emphasis mine. And:

  (ItemPointer, also known as CTID)

A block is 8 KB in standard installations. Maximum Table Size is 32 TB. It follows logically that block numbers must accommodate at least a maximum of (calculation fixed according to comment by @Daniel):

SELECT (2^45 / 2^13)::int      -- = 2^32 = 4294967294

Which would fit into an unsigned integer. On further investigation I found in the source code that ...

  blocks are numbered sequentially, 0 to 0xFFFFFFFE.

Bold emphasis mine. Which confirms the first calculation:

SELECT 'xFFFFFFFE'::bit(32)::int8 -- max page number: 4294967294

Postgres uses signed integer and is therefore one bit short. I couldn't pin down, yet, whether the text representation is shifted to accommodate signed integer. Until somebody can clear this up, I would fall back to bigint, which works in any case.

Cast

There is no cast registered for the tid type in Postgres 9.3:

SELECT *
FROM   pg_cast
WHERE  castsource = 'tid'::regtype
OR     casttarget = 'tid'::regtype;

 castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
(0 rows)

You can still cast to text. There is a text representation for everything in Postgres:

  Another important exception is that "automatic I/O conversion casts",
  those performed using a data type's own I/O functions to convert to or
  from text or other string types, are not explicitly represented in
  pg_cast.

The text representation matches that of a point, which consists of two float8 numbers, that cast is lossless.

You can access the first number of a point with index 0. Cast to bigint. Voilá.

Performance

I ran a quick test on a table with 30k rows (best of 5) on a couple of alternative expressions that came to mind, including your original:

SELECT (ctid::text::point)[0]::int                              --  25 ms
      ,right(split_part(ctid::text, ',', 1), -1)::int           --  28 ms
      ,ltrim(split_part(ctid::text, ',', 1), '(')::int          --  29 ms
      ,(ctid::text::t_tid).page_number                          --  31 ms
      ,(translate(ctid::text,'()', '{}')::int[])[1]             --  45 ms
      ,(replace(replace(ctid::text,'(','{'),')','}')::int[])[1] --  51 ms
      ,substring(right(ctid::text, -1), '^\d+')::int            --  52 ms
      ,substring(ctid::text, '^\((\d+),')::int                  -- 143 ms
FROM tbl;

int instead of bigint here, mostly irrelevant for the purpose of the test. I didn't repeat for bigint.
The cast to t_tid builds on a user-defined composite type, like @Jake commented.
The gist of it: Casting tends to be faster than string manipulation. Regular expressions are expensive. The above solution is shortest and fastest.


How to properly format sqlite shell output?

For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples:

sqlite> select * from foo;
234|kshitiz|dba.se

sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se

sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se


Does switching from INT to BIGINT entails significant downtime?

It depends on the size of the table and the speed of the disk.  Maybe an hour, maybe days.  Check out pt-online-schema-change; it may be able to avoid most of the downtime.
How often does a schema survive intact for more than a couple of years?  Sure, do the math to figure out how soon the id will overflow.  If there is even a small risk, go with a bigger size.

If you are talking about AUTO_INCREMENT, then be away that there are a number of ways in which code could skip numbers -- INSERT IGNORE, REPLACE, etc.  Also, if you are involved with Master-Master or cluster replication.

If you already have replication set up, there may be a technique (using failover) to avoid more than a little downtime.


Table shows id (auto-increment) is 0 in all rows

The problem is that you can not create AUTO_INCREMENT without a key so try something like this:

CREATE TABLE `project_application` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`project_title` varchar(255) NOT NULL,
`project_id` int(11) NOT NULL,
`team_leader` varchar(255) NOT NULL,
`team_member` text,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE     CURRENT_TIMESTAMP,
`createt_et` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

Hope this helps


Using SSIS to get record counts for tables that may not exist

You could try executing the offending statement using sp_executesql.


Split things in two --

Some table(s) would hold the 'current' info, such as the latest bid for a given item.
Some table(s) would keep the 'history', such as a list of all the bid values and who, when, what, etc.

When a new bid comes in, you would do two things: UPDATE a row in the first table and INSERT a row into the second table.

By separating "current" from "history" it may help you think through how to structure the data and formulate the queries.


Turning "Set Statistics IO" for stored procedure doesn't show all tables used in stored procedure - why?

There is a scenario where an estimated execution plan can contain an operator that doesn't get used at runtime, for example when a predicate renders the operator a no-op.
It's tricky to spot because there aren't many clues it's happened, other than the operator's actual execution count is 0. I helped drive a change in SentryOne Plan Explorer that makes it more obvious - the operator icon gets dimmed. You can read about this change in this blog post under the heading, "Apply opacity for operators with 0 executions + actual rows."
If a table is only touched by a single operator in the execution plan, and that operator gets eliminated at runtime (meaning the table does not get touched), that table will not show up in Statistics IO.


Circular references for tracking currently active entity

The ActiveShipId isn't neccesary, because you already have an indicator at Ship table that says if the Ship is active or not. Assuming that a Pirate could own more than one Ship, and a Ship belongs only to a Pirate, then the you're modelling correctly (except the ActiveShipId workaround, it needs to be removed).

Also, by applying that workaround, you're ensuring that a Ship could belong to multiple Pirates. However, in case this not fit your needs, then give us more information about your modelling requirements.


In MySQL, how to manually edit values of a BLOB column in few tuples?

When deciding to store the field as BLOB or TEXT, this paragraph might be helpful:

BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set. [src]

The difference outlined here being in the way values are compared and sorted.
You can use the UPDATE statement to modify the values.
Now, from some of the comments, I garnered that this field is a primary key. If this table is InnoDB, and this table has secondary indexes (other indexes that are not primary key), and you expect the table to become very large, then be aware how InnoDB handles secondary indexes.
InnoDB will store the primary key along with the value of the secondary indexes, so your index will grow much larger than if you used an autoincremented int column as the primary key.


Relating a variable number of rows in one table to one in another?

The classic way to realize many to many relation is with intermediate table.
Like
Products(ID) - ProductImages(ProductID, ImageID) - Images(ID)
What is the reason to make these "sets"?


Get the marketdata based on present date and previous date


Select rows from table using conditions across multiple rows in left joined table

Another approach (Bits and Booleans)
If the "options" are exactly yes/no (not "unknown" or "maybe"), then you could use a "bit" and put those bits into a SET or INT.  This would lead to a much more compact dataset, though the WHERE code would be messier.
INT would allow Boolean operations with which you could compute things that you seem to need:

OptionA was "yes" for any visit by person-1.
OptionA was "yes" for all visits by person-1.

options BIGINT UNSIGNED lets you handle 64 options, no more.  You could (with added complexity) use more than one column if you need more than 64 options.
The gist of the code is to GROUP BY person and do BIT_AND(options) to combine every column in order to test for "yes" at all visits.  BIT_OR would give you yes at any visit.  Other expressions would handle any/all "no".
Masking would let you select the, say, 3 options that the query is interested in.
To find visits with a particular set of yes/no values, just masking and equality suffices.  So (option_a, option_b, option_c) IN (('yes', 'no', 'no')) maps to (assuming a is bit 0, b is bit 1, etc):
WHERE options
    & ((1<<0) | (1<<1) | (1<<2))  -- check bits 0,1,2
    =  (1<<0)                     -- for 0 being YES; the rest being NO

Back to what might be the desired query:
WHERE BIT_OR(options) & (1<<0)  -- option_a is YES for _any_ visit
  AND BIT_AND(options) & (1<<1) = 0  -- b was NO for _any_ visit
  AND BIT_AND(options) & (1<<2) = 0  -- c was NO for _any_ visit

Tips
LEFT seems to be 'wrong', use a regular JOIN.
"Row constructors", in the past (pre 5.7.3), were quite inefficient.  Rewrite the WHERE if you are running an old version.
Please qualify column names so we know which table(s) they are in.
You show 4 options now.  Might there be 5 tomorrow?  And 6 the day after?  This leads to a maintenance question for the schema.


Joining on a field with different values


How to Design Name and Address with Multiple Addresses

If there is something in the software or data entry that mandates choosing the Address Type before successful submission, then I don't think you'd need the Address Type ID because you could just index and query the Address Type column.

If the Address type being blank is a concern, then I'm not sure that an Address Type ID would help because you'd still be dealing with nulls or empty fields.


You find the explanation of your question in the docs: "If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored."

For your purpose it would be sufficient to leave auto_increment_increment and auto_increment_offset on their default values and simply run:
ALTER TABLE test AUTO_INCREMENT=30000 on the slave.

This will result in values like 30001,30002,...,N on your slave.


You tried

ssh MachineD -L 15219:MachineB:15220 -L 15220:MachineA:1521

this did not work but you think in the right direction, you want to join two tunnels.

login to MachineD and make a tunnel from your MachineB port 15219 to MachineB port 15220

ssh -L 15219:MachineD:15220 MachineD

So If you now send something to MachineC port 15219 then your ssh session (from MachineC to MachineD) sends it to MachineD port 15220

if you do a

tnsping  '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = MachineC)(PORT = 15219))(CONNECT_DATA = (SERVICE_NAME = ORCL)))'

you will get an error message

TNS-12541: TNS:no listener

or something similar, if you try to connect with SQL Developer.

Because if you now send something to MachineC port 15219 then your ssh session (from MachineC to MachineD) sends it to MachineD port 15220. And on  MachineD port 15220 there is actually nobody listening.

Now in your ssh session on MachineD execute the following command

ssh -L 15220:MachineA:1521 MachineB

Now you have opened an ssh session from MachineC to MachineB and data sent to MachineD port 15220 is read by this second ssh session and sent to MachineA port 1521. So you have two ssh sessions and the second one extends the first one.

But instead of opening one ssh session and in this ssh session opneing the other one you can do this in one command

ssh -L 15219:MachineD:15220 MachineD ssh -L 15220:MachineA:1521 MachineB

by juxtaposing both commands. If after an ssh command follows another comman this command is executed after login.
To avoid an error message like

Pseudo-terminal will not be allocated because stdin is not a terminal.

you can use the -T option for the second `ssh`` command.

ssh -L 15219:MachineD:15220 MachineD ssh -T -L 15220:MachineA:1521 MachineB

If you get some

bind: Address already in use
channel_setup_fwd_listener: cannot listen to port: 15220

messages then change the adresses.

e.g. use  15221 instead of 15220. Of course you must replace it on both posisitions:

 ssh -L 15219:MachineD:15221 MachineD ssh -T -L 15221:MachineA:1521 MachineB

I hope this works. It is possible to configure the ssh daemons such that local forwarding is not allowed.

You cann concatenate an arbitrary number of tunnels .

I cannot test the -W option because I get

SSH-2.0-OpenSSH_5.3

Protocol mismatch.


ODBC Connections locking tables in SQL Server

Unfortunately, we've dealt with too may issues with this where I work. In cases where this has been a problem, people who don't know databases, literally open a SQL table "live" in MS Access. At first things can appear transient because they may open and close Access quickly. But then there are those moments where they leave MS Access open on the live SQL table while they go away for lunch or perhaps through the long weekend--blocking applications that try to use the database. Just to state the obvious--blocking can adversely affect any application trying to use the blocked SQL row, table, or database.

All we really had to do to prove anything was capture data from sp_who2 to find the blocking chains.

That being said, when I'm away from the console, I still use the following code I wrote over a decade ago (I mean I'm still using DBCC InputBuffer) to find these pesky issues -- if needed (please run in a test/dev environment to see if it meets your needs). Though certainly not the Rolls Royce of routines it still works for me.

This code essentially creates a table, BlockingProcesses, in a new database DBADMIN to store the blocking information. Two stored procedures sp__Maint_BlockWatch and sp__Maint_Blockingprocesses work together to find the blockers. And a SQL job runs once a minute to check for blocks. You may need to change minor things in the code below like the user who needs to execute the job, possible scheduling, etc.

CREATE DATABASE DBADMIN
GO

USE [dbadmin]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlockingProcesses]
(
    [PK] [int] IDENTITY(1,1) NOT NULL,
    [last_batch] [datetime] NULL,
    [spid] [int] NULL,
    [BlockedTotal] [int] NULL,
    [LoginName] [varchar](128) NULL,
    [DBName] [varchar](128) NULL,
    [HostName] [varchar](128) NULL,
    [Program_Name] [varchar](255) NULL,
    [CPU] [bigint] NULL,
    [Physical_IO] [bigint] NULL,
    [Memusage] [bigint] NULL,
    [Open_tran] [int] NULL,
    [EventInfo] [varchar](max) NULL,
    [InsertTime] [smalldatetime] NULL,
    [Params] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlockingProcesses]
   ADD DEFAULT (getdate()) FOR [InsertTime]
GO

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp__Maint_BlockWatch]
AS
    declare @blocker smallint

    DECLARE blocker_cursor CURSOR FOR
         select distinct blocked
         from sysprocesses
         where blocked != 0

    OPEN blocker_cursor
    FETCH NEXT FROM blocker_cursor INTO @blocker

    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status = -2)
      BEGIN
  FETCH NEXT FROM blocker_cursor INTO @blocker
           CONTINUE
      END

  exec sp__Maint_Blockingprocesses @blocker

 FETCH NEXT FROM blocker_cursor INTO @blocker
    END
DEALLOCATE blocker_cursor

GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp__Maint_BlockingProcesses]    Script Date: 09/20/2016 13:31:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
@spid smallint
as
declare
@blockedTotal int,
@loginame varchar(128),
@dbid int,
@hostname varchar(128),
@program_name varchar(128),
@cpu bigint,
@physical_io bigint,
@memusage bigint,
@open_tran int,
@dbname varchar(128),
@EventInfo varchar(255),
@Last_batch datetime
create table #temp
(
 EventType varchar(50),
 Parameters int,
 EventInfo varchar(255)
)

insert into #temp
exec ('dbcc inputbuffer('+@spid+')')

select @EventInfo=eventinfo from #temp
select @blockedTotal= count(*) from sysprocesses where blocked = @spid
select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
from sysprocesses where spid=@spid
select @dbname = name from sysdatabases where dbid=@dbid
insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
drop table #temp

GO

USE [msdb]
GO

/****** Object:  Job [_Monitor Blocks]    Script Date: 09/20/2016 13:29:34 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2016 13:29:34 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks',
        @enabled=1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Look For Blocking]    Script Date: 09/20/2016 13:29:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=1,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec sp__Maint_BlockWatch',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20060918,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959
        --, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

I can think of a couple of cases where this approach might be beneficial.

Sometimes you can end up with an execution plan which does a load of lookups for rows that are then ultimately filtered out downstream (I've noticed this especially with pagination queries). If you only store the rows post filtering there will be fewer "manual lookups" to resolve. This scenario can generally be addressed with a self join and does not need intermediate materialisation.
Lookups always use nested loops. The "manual lookup" might use a different join type - the cardinality estimates for rows to lookup will be spot on when materialised and may differ from the original estimates encouraging this.

For the case where neither of the above apply (and you are just materialising into a temp table and getting the same number of nested loops lookups as you would have got without this step and not benefiting from improved cardinality estimates) I would expect this to generally be slower than the original query without the intermediate step (as on the face of it you are doing the same work with some additional overhead added) but haven't tested this.


Is using multiple unique constrains on a single table considered bad design?

There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.

I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.


Connecting to database using Toad in windows

It's not clear from your question what brand of database you are connecting to.  I will assume it's Oracle.
You need this information:

name of the server the database is on
name of the database  --sometimes called a service name
is the database parameter db_domain configured with a value?  (usually the domain name)
the port the database listener is using (usually port 1521)
a user and password, be good and don't connect as sys

Let's try the easiest way, connecting directly

open toad
select session/new connection from the menu
select the "Direct" tab from the right side and enter

User and password
Host = machine name of the database server
Port = 1521
Service Name = Name of the database

connect as normal if you are not connecting as sys
ensure that the Installed clients picks up the correct install of your Oracle client

and....press connect.

This is the easiest way.  More involved is adding the name of the database to the TNSNames in your oracle client.
Another good check is to log onto the server where the database is and start a DOS command line window.  Enter lsnrctl status  .  This will give you the database name and port and if the listener is ready to receive connections.


SQL Server : how to decide if FULL backup or DIFFERENTIAL

to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup.

The closest you can get is to use sp_SQLskillsDIFForFULL - from SQLSkills.com which will tell how much of the database has changed since the last full backup.

You can decide a threshold based on your company's backup policy and then have a job that executes a FULL or differential backup based on the threshold.

Remember that a FULL backup (without COPY_ONLY) will reset the differential base, so be cautious for any adhoc FULL backups taken and make sure that you test your restore policy to ensure that data loss is acceptable !

From Paul's blog

The basic idea behind the code is as follows:

For each online data file in the database
   For each GAM interval in the file
      Crack the DIFF map page using DBCC PAGE
      Interpret the DIFF bitmap to aggregate the changed extents
      Add the sum to the total changed extents for the database
   End
End
Report results


How can I model a medical scenario in an entity-relationship diagram?

Consideration about multiple relationships between entities

In real life, Patients and Physicians interact in several ways. The most obvious one is that you go to see your doctor when you're ill (that's one kind of relationship between Patient and Physician). But you, as a Patient, may refer one Physician to a friend of yours (who is also ill, and, as such a Patient), because you liked how it works. This is a ternary relationship involving two Patients (the referer, and the refered) and one Physician.

Non-binary relationships

Not all relationships are between two tables (such as in the previous case). Another instance: a Diagnosis is performed by a Physician to a Patient (and, you could add: at some time, in some place, after x, y and z tests, etc. as attributes to this relationship). Having one ternary relationship is not the same as having two relationships Physician makes Diagnostic and Patient diagnosed Diagnostic. It you ever only visited one doctor, no doubt about who diagnosed. As soon as you visit two doctors... who diagnosed what is much less clear.

This is not answering all your questions, but hope it helps.


MySQL connection timeout on connection spike

What caught my eye immediately was the following

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 32M
join_buffer_size = 128M

The sum of these buffer sizes is 176M.

This would be the maximum amount of RAM used per connection.

If you multiply that by your max_connections (61440) you get

11331331686400 Bytes
11065753600 KB
10806400 MB
10553.125 GB
10.3057861328125 TB

This means that you would need to have 10.5 TB of RAM to accommodate 61440 open connections.

I have mentioned this formula before

May 09, 2013 : Should I increase max_connections in AWS RDS t1-micro for MySQL?
Mar 28, 2014 : MySQL not releasing memory

Here is a query you can run to see these metrics

SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) BBB) AA) A,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) B;

You can run this one to test out changing max_connections (Example: What if you wanted to lower max_connections to 76):

SET @conn = 76;
SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT @conn maxconn) BBB) AA) A,
(SELECT @conn maxconn) B;

YOUR CURRENT SETTINGS

Let's compare how much RAM can potentially be consumed given your current settings

Look at your InnoDB Buffer Pool (innodb_buffer_pool_size). It's 160G.
Look at your MyISAM Key Cache (key_buffer_size). It's 2048M or 2G.
256G - 160G - 2G = 94G available to the OS
94G / 176M (per connection) = 546 Connections
You will never reach 546 connections because mysqld is competing with the OS for RAM

SUGGESTIONS

You need to lower your max_connections so that it does not eat the 94G of leftover RAM.
You may need to consider lowering join_buffer_size if your joins are fast.
You could probably lower read_buffer_size and read_rnd_buffer_size if you are not reading from MyISAM tables.

GIVE IT A TRY !!!


Newer versions of ASMLib support oracleasm querydisk -p DATA01 which displays the underlying physicaldisk.

oracleasm-querydisk

Example:

# oracleasm querydisk -p SSDDATA1
Disk "SSDDATA1" is a valid ASM disk
/dev/sdl1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sdar1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sdbn1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sddf1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/mapper/mpathqp1: LABEL="SSDDATA1" TYPE="oracleasm"
#

Older versions may not have the -p option and they display only the major and minor numbers which you can use to find the physical disk:

Mapping ASM disks to Physical Devices

Example:

[root@orcldb2 ~]# /etc/init.d/oracleasm querydisk VOL1
Disk "VOL1" is a valid ASM disk on device [8, 97]

[root@orcldb2 ~]# ls -l /dev | grep 8, | grep 97
brw-rw----   1 root disk     8,      81 Nov  4 13:02 sdg1

This example I copied from the above blog post seems to be not totally accurate (grep 97 returns minor number 81?), the concept however works.


PostgreSQL, get statements of a running transaction

No, you can't do that.  Unless you have very verbose logging turned on (like log_statement=all) and can mine that data out of the server log file.

I think it would be a nice feature to optionally store some limited amount of data (like 4096 bytes, but configurable) of "prior transaction context" for each backend and then exposing it through pg_stat_actvity, and maybe logging it for log_lock_waits or deadlock errors.  But as far as I know there is no active work going on to add such a feature.


How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument?

So how do I get ALL rows if nothing is passed into IN clause?

You have to use dynamic sql.

e.g. Below is a simple example to get you started.

set nocount on
declare @sqltext nvarchar(max) = N''
declare @countryInput varchar(30)

select @sqltext = N'
    select * -- good practice to use explicit column names
        from dbo.Table
        where 1 =1 '
        + case when @countryInput is null or @countryInput ='' then ''
        else ' and country in (''USA'',''UK'')'
        end

        print @sqltext


Problem creating Oracle database manually

edit was a 16-bit application and 16-bit applications don't run on 64-bit Windows, therefore it is not included in 64-bit Windows.

https://support.microsoft.com/kb/896458

Just use notepad.

You should run this with elevated privileges, for example in a command prompt started with "Run as Administrator".


Why might the tempdb log grow
The usual suspect for delaying tempdb log file truncation is a long-running transaction.  You can run a query like this, while the problem is occurring, to see why the log can't be truncated:
SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases d
WHERE database_id = 2;

The reasons are described on this MS docs page.
Monitoring and alerting
Questions 2 - 5 can all be solved by buying a dedicated monitoring tool (like Sentry One or SolarWinds, etc).
You can certainly accomplish all of these things with "home grown" methods, but they will inevitably have gaps and failures that the monitoring tool vendors have had years to think of and address.
For instance, you can use Extended Events (XE) to monitor for file growth events - Aaron Bertrand has a post about doing that with the sqlserver.database_file_size_change event here: Removing the default trace – Part 2.
To alert on file growths, you could have a SQL Agent job that queries this event data (either directly, or through Aaron's views) periodically, like every 1 / 5 / 15 minutes, and then emails you if there have been any in that time frame.
Getting the space used inside data and log files, as well as disk space used / available, is discussed in a different Q&A on this site, so I won't duplicate it here: Query to report disk space allocation and used space
Again, the low-tech way of alerting on this would likely be through an Agent job.
SQL Server doesn't keep a log of who or what was using tempdb at a given time - you'd have to write DMV query results to a table, check them periodically, etc.
Current tempdb usage
This is included in the results of sp_WhoIsActive.  You might consider using that directly, or at least basing the calculation on the relevant part of that proc.
You'll need to add together user_objects_alloc_page_count and internal_objects_alloc_page_count (which includes LOB variables and spills), then subtract to the dealloc version of both for the "current" state of things.
You'll note that sp_WhoIsActive also includes the data from dm_db_task_space_usage for this, as some allocations will show up there and not in the other view.  You can see a detailed example on my blog, where a LOB parameter being streamed from a .NET application will show up in this view: Invisible tempdb Killer


Rule of thumb: AUTOALLOCATE or UNIFORM size for tablespaces

I agree with Tom Kyte's 2005+ assessment of this:

  I actually am a fan of system allocated extents now.  It grows the
  extent allocation size as the table grows.

  Autoallocate is not going to cause widespread fragmentation.

  IF you know PRECISELY how big the object is, will be or will grow by -- go
  ahead, do the math, feel  free to use uniform.  Else, use autoallocate...


Accessing mysql database without logging in (from android java application) (using php server side)

Applications often allow anonymous user access but relational databases should be accessed using a minimally privileged account as a best practice.

With a public web application, end users do not access the database directly but through the remote web server, middleware, or other service. The data access layer should then connect to the database using a minimally privileged account.

In MySQL, execute CREATE USER and GRANT to setup the service account. Use those credentials to connect to the database in the data access layer.


what AD groups logins my user belongs to?

what AD groups, from the list above, does my login belong to?

All you need to do is execute the following command:

EXEC xp_logininfo 'domain\useraccount','all';
GO

If the account does not have access via any group on that server, AND is a legit account in the domain, you will get no records returned. If the user is found to have permissions you can identify the group they have access from by checking the permission path. This will return the group domain\groupname that is giving the domain user access.


Archive log backup error and backup completed with errors

This happens when one processes cleans the archives and an other finds the need to backup it. Things to check:

do you have a archive log clean script other than rman? (hope not)
do you run archive log backups during the full database backup? if so, don't make archive backups in the db backup script but only in the archivelog backup script.


The problem is in the FK constraint, because they are not enabled. I am using DB Browser for SQLite, so you only have to go here in order to enable it:

Then just check that Foreign Keys is enabled:


Index physical stats DMV - Object ID/table not found

You are in the wrong database context.

Try:

select avg_fragmentation_in_percent
from databasename.sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), 1, NULL, 'limited')

Alternatively you can just throw a

USE Databasename

on the first line.

I'm guessing that it's executing in the context of master or something similar, which won't have the OBJECTID you are looking for.


Best practices for creating partition schemes and functions

If you have a single schema and function and you alter that to move data around all the tables will be moved in a single shot instead of having to change all of them.


What would be an appropriate schema for this real life scenario?

I think this is a case where a super type and subtype should be considered.

The fields that are specific to an organisation go in a separate table to the main organisation, and if there are any feels that are specific to say a sub-org they are stored in a separate table and joined for crud operations.

This approach means any validation for say addresses applies to all organisations, and the validations (access permissions) for sub types apply to all entities in the relevant table.

This approach also eliminated excessive quantities of null fields.

Another possibility is the parent-child model using the same table. This is where the table records a parent which refers back to an I'd of the same table. Only reason I suggest this as a possible option is as far as I'm aware the easiest way to enable unlimited sub-organisation levels. Querying this model is relatively complicated.


How to check with SQL whether a column is an identity column for an Access database?

There is a solution with DAO:

Dim db As DAO.Database, tbl As DAO.TableDef
Dim f As DAO.Field
Set db = CurrentDb()

For Each tbl In db.TableDefs
    If tbl.Name = "Table1" Then
        For Each f In tbl.Fields
            If f.Attributes = 17 And f.Type = 4 Then    '(17 = dbAutoIncrField (16) + dbFixedField(1),  4 = dbLong)
                MsgBox ("Table1 idenity column: " & f.Name)
            End If
        Next f
    End If
Next tbl

This checks, if there is a column with Type = 4 [dbLong] and an Attributes value of 17 (16 [dbAutoIncrField] + 1 [dbFixedField]).

This worked fine for me so far.


http://docs.mongodb.org/manual/reference/sql-comparison/

check out full documentation. everything is perfectly explained.