SQL Server Maximum and Minimum memory configuration

Looking at your RAM availablity for this particular server and you are running several databases ranging from 30GB to 5 GB, you definitely need more RAM on this server.

You have not mentioned that this is a stand alone instance or this server is having more than one instance of sql server running.

Your MAX Memory settings seems OK for a server having 8GB RAM. See these suggested best practice settings from Glenn Berry.

I would highly recommend you to do a baseline of your environment using below PERFMON counters to get a good value of your memory configuration :

SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Memory\Available Mbytes
SQL Server: Memory Manager - Total Server Memory
SQL Server: Memory Manager - Target Server Memory

Total Server Memory: Amount of memory currently allocated to Buffer Pool and not the total amount of memory to SQL Server

Target Server memory: Ideal Size of the buffer pool corresponding to max memory for the instance.

Note: If Total Server Memory > Target Server memory, then it suggests memory pressure.

Below script will help you find LOW or HIGH Memory notifications  from sys.dm_os_ring_buffers - system health session :

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
--and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC

Some good references :

How much memory does my SQL Server actually need? - by Jonathan Kehayias
Performance Tuning using the Waits and Queue
Great SQL Server Debates: Lock Pages in Memory


What to have in the main table and what to have in meta_table?

the user_meta table should contain details about the fields that will be in the user table.

for example, user_meta will have rows for each field in the user table

user_meta
---------
user_field_name | data_type  | size  | extra params
---------------------------------------------------
user_name       | varchar    | 220   | primary key
password        |varchar     |220    |not null

while the user table will have rows containing every field stated in the user_meta
using the example above, the user table will have look like this

users
-----
user_name |password
-------------------
user1     |password1
user2     |password2
...


fetching externally hosted db's table from within the local mysql server

I normally would not recommend this but here it goes...

If the tables on the remote DB Server are MyISAM, try using the http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html">FEDERATED Stroage Engine. First starters, find out if the FEDERATED Storage Engine is Enabled. This what I get running SHOW ENGINES; in MySQL 5.5.12 for Windows:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

In MySQL 5.5.12 for Windows it is not enabled

So, I added this to my.ini

[mysqld]
federated

I then restarted MySQL on my machine

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.02 sec)

Now, it is running !!!

On your remote server perform this query:

SHOW CREATE TABLE C2\G

This will show you the create table statement for C2.

Now just append the URL of the remote table usign the CONNECTION clause.

According to the MySQL Documentation

For that link

  The format of the connection string is as follows:
  scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
  Where:

  • scheme: A recognized connection protocol. Only mysql is supported as
  the scheme value at this point.

  • user_name: The user name for the connection. This user must have
  been created on the remote server, and must have suitable privileges
  to perform the required actions (SELECT, INSERT, UPDATE, and so forth)
  on the remote table.

  • password: (Optional) The corresponding password for user_name.

  • host_name: The host name or IP address of the remote server.

  • port_num: (Optional) The port number for the remote server. The
  default is 3306.

  • db_name: The name of the database holding the
  remote table.

  • tbl_name: The name of the remote table. The name of the local and
  the remote table do not have to match.

Sample connection strings:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

Suppose that table C2 in the mydb database looks like this:

CREATE TABLE C2
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    PRIMARY KEY (id)
)
ENGINE=MyISAM;

Just append the CONNECTION clause and run it on the Local Server:

use mydb
CREATE TABLE C
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    PRIMARY KEY (id)
)
ENGINE=FEDERATED
CONNECTION='mysql://username:password@hostname/mydb/C2';

Finally, in MySQL on your local machine, you should be able to access the C2 table like this:

mysql> SELECT * FROM mydb.C;


Why do I need to backup the Database Master Key?

I primarily use them when I'm moving or copying a database to a new instance of SQL Server. In that case, the DMK in the database will be re-encrypted with the new instance's service master key, but the objects encrypted by the DMK (in your case, the symmetric keys) will not be updated accordingly.  You'll need to restore the DMK from a separate backup using the FORCE option to allow the objects to be automatically decrypted.

And yes, for the record: corruption of the keys is rare, and they will be verified by the same mechanisms as any other data in the database (i.e., CHECKDB).  However, I would still keep backups of all encryption keys you use - master keys, certificates, symmetric keys, etc.  In all of those cases, if you lose the key, you will never be able to decrypt the associated data.  It's not necessary to backup the keys with every database backup, but it's good insurance to back them up when they're created or modified.


Copying a DB or tables from a local Postgres database to Heroku

First, take a dump of the db under Windows as outlined here. This will be a human readable text file - worth a look to get a feel for what's going on "under the hood", i.e. in the background!

Then, ftp that file (as text) over to the Heroku box (Linux I presume) and do a restore from the dump as outlined here.

psql dbname < infile

You may have to create an empty schema - can't remember.

Then use psql to connect to your new db.

p.s. a word to the wise - if you're going to be using Python, Heroku and PostgreSQL, then you should be using a Linux box as your main machine - all three are Linux "natives" rather than Windows. Try it out - you'll wonder how you ever did things any other way!


Why does "GRANT SELECT ... TO Role;" not allow members of Role to SELECT?

In addition to ensuring you're in the right schema, try this one;
Once logged in as Alice, do: SET ROLE Viewer IDENTIFIED BY pwdviewer;

For more see here


Decrease Insert and Update performance on bigger database

Assuming you're using Microsoft SQL Server (as your tag implies), if you're using JMeter, ensure your tests run directly on SQL Server and don't involve any middleman that can affect results. I'm not personally familiar with JMeter but the biggest difference you have between your two tests is about 57 milliseconds which is little enough to be affected by any possible variable in your process.
Although the difference between your two InsertNewTransaction tests is about a 35% difference, it's still only 57 milliseconds, which is very small. It can even be just the difference of a single run of one test vs it's second run, or more outside factors like temporary hardware blips. You'd have to run a large number of instances of each test to average and really compare when you're dealing with such granular time measurements.
That being said, some general advise is as follows:

INSERTS take longer when there are more indexes on a Table. So when applicable, reducing the number of low-use or unused indexes can help your INSERT performance.

Partitioning isn't meant to improve performance from a SELECT or INSERT perspective. Rather it helps with better granularity on maintaining the data, and can be used smartly to improve maintenance tasks performance (such as index maintenance).

If the table with 56 columns is being slow, then normalizing it, if possible, can help your INSERT performance (especially if you're not always inserting data for all 56 fields) and can help your SELECT performance.

If you need further performance critical changes, you can look into if Memory-Optimized Tables fit your use cases.


How can I stop continuous failed logins on SQL Server

It sounds like those failed logins are coming from the Internet.

You really shouldn't have a firewall port open to SQL Server directly from the Internet.  That is just asking for trouble.

If the website and SQL Server are not on the same network, or are connected only via the Internet, you should look at providing a VPN connection between the two so that you don't provide an open port directly from the Internet to SQL Server.


How to create oracle linked server in oracle server itself?

The concept you are looking for on the Oracle side is a Database Link.  See this introductory information in the Concepts Guide.  Then follow the link at the end of the section to the Administrators Guide where you can learn more about it.  You will also want to look at the Database Gateway documentation for the OS you are on, specifically the section on Configuring Oracle Database Gateway for ODBC which will explain steps need to prepare the system for non-Oracle Database Links.

To answer your concerns...

Read only access is fine as long as you don't want to change the remote database.
Not being able to create remote views or stored procedures won't be a problem.  If you create Materialized Views then a remote Materialized View Log may be useful, but not necessary.
Using database links on the Oracle side you can create views of remote tables or even join local data with remote data.  Yes, the speed is comparable. It won't be as fast since the data has to flow over the network, and sometimes the query plan won't be ideal, but these may not be issues depending on your situation.

Here are the steps you will need to perform:

Create a system DSN.
Create an appropriate ORACLE_HOME\hs\admin\ .ora file.
Add an appropriate ORACLE_HOME\network\admin\listener.ora entry.
Add an appropriate ORACLE\network\admin\tnsnames.ora entry.
Reload the listener.
Create the database link.


Your query has 5 equality conditions in 5 different columns:

select *
from t_event
where create_user_id=7
  and event_create_date = '2012-12-18 00:00:00'
  and event_type_cd = 11
  and domain_id = 602
  and job_id =1

limit 1 ;                  -- why is this for?

Now you have 2 indexes (domain_id_event_type_cd and event_user_FK), which cover only 3 of the 5 column/conditions of the query.

So the plan that the optimizer chooses is an index_merge operation, e.g. it uses the two indexes to find rows that match the 2 and 1 conditions respectively, then intersects them (Using intersect) to find the common rows and then has to do an additional search on the table (Using where) for the rest 2 conditions that are not covered by the indices.

You could add a compound index on all those columns. The order does not matter at all for this query, it matters for other queries you may have and whether it could be used for them:

ALTER TABLE t_event
    ADD INDEX i5
        (domain_id, event_type_cd, create_user_id, event_create_date, job_id) ;

Then the Explain would show only Using index and only the above index would be used.


Just add the word DISTINCT in front of the first marks inside the GROUP_CONCAT function

SELECT id, marks, FIND_IN_SET( marks, (
SELECT GROUP_CONCAT( DISTINCT marks
ORDER BY marks DESC )
FROM exam )
) AS rank
FROM exam;

First here is the sample data

use test
DROP TABLE IF EXISTS exam;
CREATE TABLE exam
(
    id int not null auto_increment,
    marks int not null,
    primary key (id)
);
INSERT INTO exam (marks)
VALUES (7),(10),(7),(10),(9);
SELECT * FROM exam;

Let's load the sample data

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS exam;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> CREATE TABLE exam
    -> (
    ->     id int not null auto_increment,
    ->     marks int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO exam (marks)
    -> VALUES (7),(10),(7),(10),(9);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM exam;
+----+-------+
| id | marks |
+----+-------+
|  1 |     7 |
|  2 |    10 |
|  3 |     7 |
|  4 |    10 |
|  5 |     9 |
+----+-------+
5 rows in set (0.02 sec)

Now, watch the output of the proposed query:

mysql> SELECT id, marks, FIND_IN_SET( marks, (
    -> SELECT GROUP_CONCAT( DISTINCT marks
    -> ORDER BY marks DESC )
    -> FROM exam )
    -> ) AS rank
    -> FROM exam;
+----+-------+------+
| id | marks | rank |
+----+-------+------+
|  1 |     7 |    3 |
|  2 |    10 |    1 |
|  3 |     7 |    3 |
|  4 |    10 |    1 |
|  5 |     9 |    2 |
+----+-------+------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!


So your database is in full recovery model then?  You can script out the rebuild commands for the index and have a pause command to wait for the next log backup to complete.  This would rebuild at most one index at a time.  It would be slow but could be done during the evening and would get you caught up faster.


Why do I have to set MySQL user password with mysql_native_password to connect with Workbench?

MySQL Workbench 8 accepts, if mysql server is set to legacy mode, also native_passwords.
But it  also accepts users that use cashing_sha2_password or sh2 passwords.
So you must check the users roles and for which. first try to use % in LImit to host matching and DBA as role, so that your user has all privileges to test.


My recommendation is to start out with Postgres.  The advantage is that it has a full feature set and it teaches you good habits from the start.  Once you are familiar with it, it is easier to move to up to the Oracle/DB2 approach or down to the MySQL approach easily enough.  On the other hand it is an advanced database and you can take it extremely far if you want.  And it is open source to boot.


A pound sign in a mask suppresses zeros. Change your pound signs to 9's. Or just use Format([startdate],'Short Time') + ' - ' + Format([enddate],'Short Time')


How much data do you have?

Personally, I'd do one of

try it on a test system and check "before" and "after"
just remove unused data because by definition, it isn't needed

The questions is complicated when you have

variable length data: how much of the field is used on average?
page density (rows per page) may not increase therefore you save no space
indexes duplicate a column value, so you have to count some ones twice

So, I'd do it empirically rather than by calculation...


ORA-01722 Error when running stored procedure

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace

' ' || c.charges - c.payment || ' '

with

' ' || (c.charges - c.payment) || ' '

but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, something like

' ' || (nvl(c.charges, 0) - nvl(c.payment, 0)) || ' '

otherwise when any of charges, payment are null you'll get an empty string instead of a number (since any number - NULL is NULL and NULL is converted to an empty string when concatenated with other strings) and you probably don't want that.


I am not aware of a built-in function to do this. You need to roll your own. Here is how I do it:

SELECT SQL#.Math_Factorial(5); -- 120

The Math_Factorial function is in the Free version of the SQL# SQLCLR library (that I wrote).

OR

if you do not need it in function / UDF form, then it might be more efficient to do the following:

DECLARE @BaseNumber INT = 5,
        @Result BIGINT = 1;

;WITH cte AS
(
  SELECT TOP (@BaseNumber) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM   sys.columns
  ORDER  BY Num
)
SELECT  @Result *= [Num]
FROM    cte;

SELECT @Result;
-- 120

Both approaches shown above take into account the "special" condition of passing in 0 as the "BaseNumber" and it returning 1 instead of 0.

SELECT SQL#.Math_Factorial(0); -- 1

For the T-SQL approach, just make @BaseNumber = 0 and it will return 1 (no need to copy and paste it again here just for that).


Cannot connect after Availability Group automatic failover

If you cannot reach the listener from SSMS, then it seems to be an issue with networking or name resolution (DNS).
You can try do to a NSLOOKUP of your listener and make sure it point at the primary node. If not, it means you have an issue with DNS update. Your windows admin should be able to help you.
If it points to the right server, but you cannot ping it or connect to it, then I would provide that information to the network team, asking them to assist with this specific situation.


Use event schedule and stored procedure to automatically average every hour MySQL

Run this in MySQL

SET GLOBAL event_scheduler = ON;

Add this to my.cnf so the scheduler remains enabled upon mysql restart

[mysqld]
event_scheduler = ON

Start the Event on the Next Hour, Make it Recurring Every Hour, Adjusting WHERE clause

CREATE EVENT hourly_temp_avg
    ON SCHEDULE
    EVERY HOUR
    STARTS DATE(NOW()) + INTERVAL (HOUR(NOW())+1) HOUR
    ON COMPLETION PRESERVE
DO
INSERT INTO `table1` (`dateavg`, `avg_temperature`)
SELECT `DATE` , avg(`temperature`)
FROM `new_table`
WHERE `date` >= NOW() - INTERVAL 1 HOUR;

Masoud came up with the proper concept first. So, he gets +1 from me.


MySQL WHERE if NULL select another column

Generally speaking you can use COALESCE

SELECT *
FROM TABLE
WHERE COALESCE(date_active,date_created) = 'value'

However this is not sargable. Depending on indexes available you may find the following

SELECT *
FROM TABLE
WHERE date_active = 'value' OR
      (date_active IS NULL AND date_created = 'value')

Or even

SELECT *
FROM TABLE
WHERE date_active = 'value'
UNION ALL
SELECT *
FROM TABLE
WHERE  (date_active IS NULL AND date_created = 'value')

Performs better


Explain doesn't show indexed column as key

First, the possible_keys shows null because there is no index on any of the two columns used in the WHERE clause. So, there is no useful index to even be considered for this query.

But even when you add an index on (expirydatetime), the situation is still not good. Because when you place a column inside a function in a condition, like the DATE(games.expirydatetime)<CURDATE(), you are basically forbidding the use of indexes. While it might be possible to use the index on the column, it would still had to do a full index scan (because of the function use) and then access the table for the rows that matched. So the optimizer chooses to do a table scan instead.

To make the query use indexes and be more efficient, change the condition to:

WHERE expirydatetime < CURDATE()
  AND players_no = 2

and add a composite index on (players_no, expirydatetime)


Does Mysql locks table while insert operation

InnoDB will only lock the row.  But it will eventually have to update all the indexes; however this is mostly deferred.

Was something else going on while the INSERT was taking 36 seconds?  Perhaps a SELECT ... FOR UPDATE?  Or even a SELECT can block the INSERT in certain circumstances.

Next time it happens, quickly do SHOW FULL PROCESSLIST to see what else is running.  Then do SHOW ENGINE InnoDB STATUS to (hopefully) get some info specific to the INSERT and why it is blocked.

What version are you running?  Newer versions have tables to help get more info, even after the fact.


Can't restore Azure bacpac into localdb with SqlPackage

I encountered this problem and, after some frustration, solved it.

What had happened is that you have created, most likely by mistake, a user object in the model Database.  On Management Studio look under Databases\System Databases\model.   This is the template that is used when you create a new database, and is used when importing the bacpac.  If there are objects (such as a table) in the model, they are automatically created at the same time as the new database.  The bacpac import then encounters them in what it expects to be a blank database, and so immediately fails.

You'll also see these same objects created if you manually create a new database.

Deleting the object(s) from the model database solves the problem.


Troubleshooting spid suspended ASYNC_NETWORK_IO

ASYNC_NETWORK_IO generally indicates that SQLServer has results ready to return to the client, but the client is not requesting them as fast as SQLServer can deliver them, or the network is going slow.

A common scenario with C# is if you are consuming results via an IEnumerable<> with a bunch of slow processing per row coming from the database. If you're not returning millions of rows, try putting a .ToList() or something in there to buffer all the results from the database prior to processing them.


Fast local backup - Very slow backup via Network

You could attempt to backup up the database using compression instead. As long as you have the CPU avaliable, It will definately reduce the time (around 15%-20%) that it takes for your backups to complete. These backups also take less time to restore as well.

Good Luck,

Hope this helps.

Good resource: http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/testing-backup-compression-in-sql-server-2008/


Internal LOBs are stored within the database, as table columns. External LOBs are stored outside the database as operating system files.  Only a reference to the actual OS file is stored in the database.


Should I use Index or Unique Key for a non PK Uniqueidentifier?

Unique keys are indexes.  If your values are guaranteed to be unique, this is the best choice.

  Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.


App domain crashed due to not enough storage available to process this command

This is usually down to memory pressure, I assume that you're running SSMS on the SQL server?  Try opening SSMS from another location and connect in, does this still produce the error?

You might want to set your max memory setting to allow some breathing space for the OS and other apps.  I usually leave the OS 4GB to start with and then tune from there if I need to.


Multi-Subnet AG - DNS flips to secondary IP

This sounds a lot like you need to add MultiSubnetFailover=true to your connection strings.

See https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover

That article explains fully why you need it enabled and even why you should specify it even if only using one subnet.

The main reason is that by default SQL Client will select the first IP address it learns about.  If DNS returns more than one IP address, it'll pick the first one.  Its possible for the the order from DNS to shift.  This setting overrides that behaviour.  Now SQL Client will attempt connections to all IP addresses and resolve which is the correct one to use.

You only need to update the connection string. No other code changes are required.


Combining multiple rows into single column

Below is what you need

CREATE TABLE customers
    ([id 1] int, [status] varchar(4), [id 2] int, [name] varchar(4), [category] varchar(6))
;

INSERT INTO customers
    ([id 1], [status], [id 2], [name], [category])
VALUES
    (001, 'open', 011, 'john', 'person'),
    (001, 'open', 011, 'john', 'male')
;
select * from dbo.customers

---- You have to use STUFF function with FOR XML PATH

 select distinct cs.[id 1]
    ,cs.[status]
    ,cs.[id 2]
    ,cs.[name]
    ,stuff((
            select ',' + c.[category]
            from dbo.customers c
            where cs.[id 1] = c.[id 1]
            for xml path('')
            ), 1, 1, '') as [category]
     from dbo.customers cs


Changing Charset to utf8 for all the fields and tables in mysql

First backup the database. Sometimes character conversions can give unexpected results.

I have had good success using these instructions:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') INTO OUTFILE '/tmp/altertablesstatements.sql'
FROM
information_schema.tables
WHERE
    table_schema='<database-name>'
AND table_collation != 'utf8'
GROUP BY table_name;

Add disable foreign_key_checks to first line of sql.

echo 'SET foreign_key_checks = 0;' >> /tmp/altertablesstatements.sql

Import the changes

mysql DATABASE  < /tmp/altertablesstatements.sql

Converting Columns

SELECT CONCAT(  'ALTER TABLE <DATABASE>.', table_name,  ' CHANGE ',COLUMN_NAME,'  ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_general_ci',
IF(IS_NULLABLE='NO',' NOT NULL;',' NULL;')) INTO OUTFILE '/tmp/altercolumnsstatements.sql'
FROM information_schema.COLUMNS AS tb
WHERE TABLE_SCHEMA =  '<DATABASE>'
AND character_set_name != 'utf8';

Add disable foreign_key_checks to first line of sql.

echo 'SET foreign_key_checks = 0;' >> /tmp/altercolumnsstatements.sql

Import the changes

mysql DATABASE  < /tmp/altercolumnsstatements.sql

https://confluence.atlassian.com/jirakb/how-to-change-all-columns-and-tables-collation-to-utf8_bin-in-mysql-601456761.html


How to run analysis on a 16M row table faster

All your time is spent in a sequential scan that discards almost all the rows it scans. Unfortunately you have an OR in there, so the best you can do is to create three indexes and hope for a "bitmap or" of the three index scans:

CREATE INDEX transactions_type_idx ON transactions (lower(transaction_type::text));
CREATE INDEX transactions_name_idx ON transactions (lower(transaction_name::text));
CREATE INDEX transactions_id_length_idx ON transactions (length(transaction_id::text));


MDX misunderstanding: complex FROM clause

Recent versions of SSAS (IIRC 2008+) support subqueries in MDX in much the same way that SQL does.  What you see here is a subquery being used to produce a subcube that is then queried by the outer query.  There's an article on it here.


Resizing and reclaiming free space on Tablespaces

I know I am late for the party, but this script was gold for me at the time I found it:
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/

It will tell you smallest and current size of each datafile and it will tell you how much
storage you may gain by shrinking a datafile(s combined).
Once you find the smallest possible size of a datafile, you may simply shrink it with:
ALTER DATABASE DATAFILE 'Datafile_name_bla_bla' RESIZE 20M;


Fix line by line parameterization in a table

I think you might consider removing all the non-numeric values, then update it with the 'S' hyphen prefix.  Two transactions. I'm operating from the position that you don't really trust the mask/format that was used when inserting the data but the numeric values are okay (e.g. 'As you can see, there are a lot of different values').

Here's a function that removes non-numeric characters (from here: How can I strip non-numeric characters out of a string?)

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

Usage:

UPDATE Viaturas SET patrimonio = dbo.fnRemoveNonNumericCharacters(patrimonio)

Then:

UPDATE Viaturas SET patrimonio = CONCAT('S-', patrimonio)


Updates thanks to Martin. If you view the text for the procedure:

EXEC sys.sp_helptext N'sp_MSforeachdb';

You will see that it passes the @replacechar as NCHAR(1) (default N'?') into sys.sp_MSforeach_worker. Peeling back another layer of the onion, you will see really ugly code in that procedure that loops through every character in the command and rebuilds it. I didn't study every single line but I could not find any evidence of an ability to escape or preserve the actual question mark conditionally, unless - as Martin points out - you override it with a different character that can't naturally appear in the command. So perhaps something like:

EXEC sp_MSforeachdb N'SELECT ''Ƥ'', other_columns FROM [?].dbo.whatever
  WHERE DirectoryPath LIKE ''%?%'';', N'Ƥ';

-- Ƥ is NCHAR(420), chosen randomly

Since it is really looking for the literal ? character, a possibly easier way would be this:

EXEC sys.sp_MSforeachdb N'PRINT ''?'' + ''-'' +  CONVERT(VARCHAR(32), 0x3F)';

So extrapolating that, we can construct this ugly variation:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', other_columns FROM [?].dbo.whatever
  WHERE DirectoryPath LIKE ''%'' + CONVERT(VARCHAR(32), 0x3F) + ''%'';';

That all said, you could write your own version of sp_MSforeachdb that uses a different default value for @replacechar, so you don't have to override it manually. You probably should be writing your own version instead of relying on the built-in procedure anyway, as I point out in these posts - it is unsupported, undocumented, and actually has a very high frequency bug where it skips databases due to the cursor options used:

https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb
https://sqlblog.org/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/


Using CREATE TYPE within Stored Procedure Call

Ownership chaining from procedure does not work with DDL statements and TRUNCATE statement. You can use execute with impersonation (execute as) in your case.

The impersonation example for creating a stored procedure is -

Create procedure <your procedure>
with execute as owner
 as
   create type ...

Alternately, you can sign your procedure with certificates created with appropriate permissions on the database.


Add variable value in single quoted string. Expand variable within single quotes

Try this:

-- This will concatenate the values:
\set myvariable '/var/lib/pgsql95/' :myvariable '/pg_tblspc'

-- This will expand the variable single-quoted:
CREATE TABLESPACE aspire_data OWNER aspireapp LOCATION :'myvariable'


How to find out how much i/o resources a task, session, request or transaction is currently using?

If you are looking for the actual physical I/O for a query, you should look in the XML showplan for the 'ActualPhysicalReads' element:

schemas.microsoft.com/sqlserver/2004/07/showplan

However, those physical reads are likely only going to occur the first time the plan is cached. Subsequent runs are more likely to pull the pages from memory, not from disk.

HTH


SELECT * FROM table1 t1

  LEFT JOIN table2 t2 ON t1.field1 = t2.index_field

  WHERE

   t2.user_id = '2'

this will be optimized and will serve the purpose! although u can use EXIST as well!


I'm assuming there is a typo in the question as per Martin's comment.

A problem with a single range seek on either ipFrom or ipTo is that on average it will need to seek half the table. An alternative approach is

1) Add a computed column, on something like:

 ALTER TABLE MyTable
  ADD granule
AS CASE WHEN (ipTo-ipFrom)<16 THEN 1 WHEN (ipTo-ipFrom)<256 THEN 2 ELSE 3 END;

2) Index the computed column and ipFrom

CREATE ClUSTERED INDEX IX ON MyTable(granule, ipFrom );

3) Query like this:

SELECT url
FROM   MyTable
WHERE  granule = 1
       AND ipFrom BETWEEN @somevalue - 16 AND @somevalue
       AND ipTo >= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 2
       AND ipFrom BETWEEN @somevalue - 256 AND @somevalue
       AND ipTo >= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 3
       AND ipFrom <= @somevalue
       AND ipTo >= @somevalue;

Precisely how you define the 'granule' function will depend on the spead of ipto-ipfrom in your data.


What would be a optimal index for this query in mysql

I have a few suggestions that may help
SUGGESTION #1 : Use index with different column order
ALTER TABLE sales_reps ADD UNIQUE INDEX
(state,account_id,name,platform,client_group,from_date);

Note that state and account_id are reversed. That way, there is no need to traverse all rows for a given account_id that would separate enabled from disabled. Only enabled entries for account_id are read.
SUGGESTION #2 : Refactor the Query
Instead of
SELECT *
FROM `sales_reps`
WHERE `sales_reps`.`account_id` = 1
AND state = 'enabled'
AND (clients >= 1 OR revenue >= 100)
ORDER BY name,platform,client_group,from_date

Try this experiment
First run this query
SELECT enabled,accountid,name,platform,client_group,from_date
FROM sales_rep WHERE account_id=1 AND state='enabled'
ORDER BY enabled,accountid,name,platform,client_group,from_date

then run this query
SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
AND clients >= 1 OR revenue >= 100

If the performance of those two queries are fast, then JOIN them as subqueries and see
SELECT B.* FROM
(
    SELECT enabled,accountid,name,platform,client_group,from_date
    FROM sales_rep WHERE account_id=1 AND state='enabled'
    ORDER BY enabled,accountid,name,platform,client_group,from_date
) A LEFT JOIN
(
    SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
    AND clients >= 1 OR revenue >= 100
) B USING (enabled,accountid,name,platform,client_group,from_date)
WHERE B.enabled IS NOT NULL;

Give it a Try !!!


Update values from one table to another where key values are not unique


Simple insert statements are taking so long

The performance problem it's because of your clustered index. When you use clustered, and you add a new record, it needs to physically rearrange all records in the table to make space for it.

Drop the clustered index and create an unique non-clustered index instead. That should fix your performance problem.

As a general rule, I'd also recommend having a clustered index, but with only a numerical autoincremental id.


Can't create linked server SQL Server 2005

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

"


What is the deal with the "invisible schema" pg_catalog?

It's not a schema in my database.

Yes, it is. And it's neither "invisible" nor undocumented:
Quote from the manual

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path.

All views and tables in the pg_catalog schema are documented here


The semaphore timeout period has expired" is a Network error

Network issues between the source server and the server running SSIS?

A look in the Windows System logs of both machines might provide more information about any network issues. You will probably also find the specific windows error number, which will help in your research.

Locking/blocking ?

Using the dmvs dm_exec_connections, dm_exec_sessions and dm_exec_requests you can see if another query is blocking access to a resource your SSIS package is requesting from the source server.

select es.session_id, er.blocking_session_id, *
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id

Is the query too slow because of full table scans or lack of indexes?
Remote Query Timeout: Is the remote query timeout set on the source server set?

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

At 0 the default value, there is no timeout.


Postgres replication: Do I need to rsync pg_xlog files if I have archiving setup in master?

That tutorial appears to be old, and also says "work in progress" at the top. I'll amend it if I have time. Please refer to the main documentation.

If you have continuous archiving set up you don't need to manually rsync the WAL, because your archive_command will do that for you.

You may also wish to consider using pg_basebackup instead of manually rsync'ing the datadir. It'll take care of everything for you over the PostgreSQL replication protocol. If you weren't using WAL archiving you'd use -X stream mode to get it to copy the WAL for you too, but you don't need that.


Joining two tables with unrelated where clauses

LEFT JOIN one subquery to get the maximum of FTE per employee and another to get the maximum (or minimum, ...) assignment for each employee and FTE.

SELECT e1.*,
       a4.*
       FROM employee e1
       LEFT JOIN (SELECT a1.employeeid,
                         max(a1.ftepercentage) ftepercentage
                         FROM empassingments a1
                         GROUP BY (a1.employeeid)) a2
                 ON a2.employeeid = e1.employeeid
       LEFT JOIN (SELECT a3.employeeid,
                         a3.ftepercentage,
                         max(a3.assignment) assignment
                         FROM empassignments a3
                         GROUP BY a3.employeeid,
                                  a3.ftepercentage) a4
                 ON a4.employeeid = e1.employeeid
                    AND a4.ftepercentage = a2.ftepercentage
       WHERE e1.enabled = 1;


Which driver should I choose for ODBC connect from Windows 10 => SQL Server 2014

Found this post after doing some research on SQL ODBC drivers for a business user today and wanted to clarify. The SQL native client is not supported beyond 2012. The new hotness (in your case) is the MS ODBC driver 11 for SQL server. However, both SNAC11 and ODBC11 should work just fine for you.

MS - "ODBC Driver 11 for SQL Server contains new features as well as all the features that shipped with ODBC in SQL Server 2012 Native Client."

The "SQL Server" driver in ODBC drivers (SNAC6) is obviously very very old

https://technet.microsoft.com/en-us/library/ms131321(v=sql.120).aspx


Questions about PostgreSQL backup/restores

2) pg_dumpall is what you're looking for if you want all objects (globals, roles, etc.).

1) You noted that it is subjective. On my dev laptop (Win7 x64 .. i7 4 @ 2.2 ... 12 GB RAM ... normal, spinny disk @ 5400) I backup a 4GB pg cluster in about 15 seconds. I don't know if it scales linearly.

Finally, depending on your OS, I'd verify that it's not actually being backed up. I've seen pg on linux being backed up at the file system level using rsync. Without getting into a debate about the relative merits of these differing methods, I'd verify that this isn't already implemented.


Can I do a conditional JOIN or conditional SELECT based on the data in a table?

What you really want is a table full of nullable fields, EAV is hard to get right, and with EAV you literally need three columns, not 6. It would be much easier to just keep adding nullable columns for your needs at this time.

  So there should not be a performance and scalability issue having a table with 20 or 30 columns? What am I trading off when I make a 30 column table?

What are you trading off? Nothing really. Performance and scalability issues are much harder to interpret and diagnose with an EAV format over the traditional wide-table format. You really only end up with tables with lots of null values that you just need good indexes to find the things you want quickly, which is a lot easier than done with an EAV.

Running 1 EAV table per data type is just as horrid as the five+ column design you were describing before from a perf and maint standpoint. You really don't want to do this. You really want to just add a lot of nullable columns to one wide table. You're shooting yourself in the foot and asking us how to bandage it before you begin. EAV is not the design pattern you want right now, in part because you don't understand how the model works. Model your business data, then model your database.

In your case, you want a table for motherboards with vendor, board name/model number, etc. I've modeled a simple table below. EAV would then be used with this table to model things like "link for driver downloads" which are very rarely going to be needed. Think about the main table as being searching details, and the EAV being details that you only need when you show a secondary tab on the UI.

create table motherboards (
   id bigint not null auto_increment
 , vendor_name varchar(100) not null -- would also be good as a lookup column to another table, making this an int or bigint instead
 , board_name varchar(100) not null
 , revision_number varchar(100) null
 , cpu_socket_type not null -- can be an int lookup to another table or a text field, I leave this to you based on the schema you want, but either is acceptable, as is an int with an in-app enum lookup
 , cpu_socket_count int not null default 1
 , ram_socket_type not null -- again, can be one of so many values, you decide the best type of storage
 , ram_socket_count tinyint not null default 1 -- here's where things get tricky, it's possible on some systems (enterprise grade) to have different RAM sockets for different reasons, but we skip that because that's a lot of detail. Wait till you have to cross that bridge, mkay?
 , northbridge_chipset varchar(100) not null -- this makes the most sense to me, but see the cpu_socket_type comment. There are a handful of common ones, so text may be easier to deal with.
 , southbridge_chipset varchar(100) null -- see above

-- This is now the additional details for others
 , rear_port_usb tinyint not null default 2
 , usb_headers tinyint not null default 0
 , upc_number varchar(20) null
 , pcie_socket_count tinyint not null default 0
 , pcie_x2_socket_count tinyint not null default 0
 , pcie_x4_socket_count tinyint not null default 0
 , pcie_x8_socket_count tinyint not null default 0
 , pcie_x16_socket_count tinyint not null default 0
 , sata_headers_count tinyint not null default 0
 , pci_socket_count tinyint not null default 0
 , isa_socket_count tinyint not null default 0
 , rear_port_matrix -- some set of fields to indicate what is on the back panel where people can have access. I would probably either make up a standard "dictionary" of these and do it like an enum/lookup table, or list one per port. Where do you put parallel ports on this lookup list? How many USB are exposed via the backplate? How many USB are exposed on headers on the motherboard?
)

create table motherboards_details (
   id bigint not null (fk back to motherboards)
 , name varchar(100) not null
 , value varchar(100) not null
 , create_date datetime not null default CURRENT_TIMESTAMP -- always good to have for later tracking
)

And that's it. No more EAVs. If you need to use it as a float or an int, you do that in the application.

I have some other suggestions, if you are just hell-bent on using an EAV for tracking details, such as moving all of those socket bits to an EAV, but trust me when I tell you that will become much more tedious than what I gave you. This format lets you search for the details you want (like in a picker UI) and lets you build indexes for commonly searched features once you get a very large table. The only indexes I would suggest are on the board name, the socket type and count, and the ID for lookups. Everything else I would index after putting the system under load with actual data.

The EAV model really doesn't fit this business model right now, so without seeing an actual EAV schema intention, I would recommend you approach it from the style I've outlined above, and ignore the column widths.

The approach I've outlined above is about 500 bytes per record so far, which is relatively tiny. The indexes alone would be hard to maintain.

I hope this helps you reconsider the approach to your problem design, and we as always as a community are happy to help you flesh out the details.


Dynamic Partition Elimination when using "OR " or "IN"

Below a couple of techniques that should coerce partition elimination while providing the same functionality as the IN or OR methods.  These both use a JOIN instead, one with a row constructor and the other a table variable.  The table variable (which could be passed as a table-valued parameter) allows a variable number of container values without changing the query itself.

SELECT  *
FROM    Container.Container_Partitioned
JOIN ( VALUES ( @ContainerId1), ( @ContainerId2) ) AS containers ( ContainerId )
    ON containers.ContainerId = Container_Partitioned.ContainerId;
GO

DECLARE @Containers TABLE(ContainerId int PRIMARY KEY);
INSERT INTO @Containers VALUES(5);
INSERT INTO @Containers VALUES(30000000);

SELECT  *
FROM    Container.Container_Partitioned
JOIN @Containers AS containers ON Containers.ContainerId = Container_Partitioned.ContainerId;
GO

As to why, SQL Server generates a plan that can be reused regardless of the values that may be specified.  With an index on ContainerId, I get a seek with a partition range as the first seek predicate and the supplied container values as the second.  Remember that the partition number is treated much like the first column of an index an index in SQL 2008 and later.  The partition range seek must allow for all partitions in order to be able to return any possible container id values with that plan.

Note that even though the unneeded partitions are touched, the number of logical reads for each is just the number of index levels since no rows will be found for those.  Not ideal, especially if you have a lot of partitions, but not too expensive since those pages will likely remain memory resident if the query is executed often enough.

Interestingly, it seems SQL Server can eliminate partitions better in this case when the partitioning column is not the first column of an index.  Without an index on ContainerId, the plan I get scans with a nested loop operator using a RangePartitionNew function to only access the partition with the specified container ids.  One would think SQL Server could generate a similar plan for the index seek.  There seems to be room for improvement but keep in mind that partitioning adds another layer of optimizer on top of the already complex job to optimizer has to do.


Create database before restore multiple files

I think the main problem is that you think that creating the database first will allow you to bypass WITH MOVE. This is not the case.

If the destination file paths don't match those contained in the backup file, the presence of a database (and its file locations) aren't going to make a difference. RESTORE is going to look at the file locations specified in the backup file, completely ignoring the existing database. And this can only be overridden by using WITH MOVE.


Without an index on FirstName, SQL Server has to check every row to see if it qualifies for the UPDATE.

It takes an update U lock when reading each row to prevent a common deadlock scenario. It could take a shared S lock, but that would still be blocked by the exclusive X lock held by the first transaction.


SSIS keeps force changing excel source string to float

It appears there is no way to fix this issue. Excel looks at the first few rows of data in the column and will always FORCE the datatype to FLOAT no matter what. I was given a solution of creating a template with a few varchar characters in the first few rows (8+) so EXCEL will pick that up as the data type and then import my data. On thing that helped is since I am using Source Safe I am able to "Check In" a file so that it makes my package file "Read Only" so that Visual Studio can not change my pre defined data types.


Yes you can have MySQL as independent ZIP archive and it is also available for download in MySQL download page for windows, do the following steps

Download ZIP archive
Extract/UN-zip the archive
Change the default ini file if needed
You may also install service or start mysqld from cmd prompt using

cd \bin
  mysqld.exe

or if service is needed to be installed

cd \bin
   mysqld.exe --install MySQL Service Name --defaults-file=Absolute Path to .ini file

use mysql.exe -uroot to login MySQL

The ZIP archive is also available for UNIX* OS

Hope it helps


does SQL automatic backup runs even the machine/server is offline

No SQL Jobs cannot run without the SQL Server & the SQL Agent Service running


Odd error when stress testing against an AG

If we point the application directly to the primary server, we don't see any issues with the test.

Sounds like a problem with the name chosen for the Listener...

  error: 0 - You were not connected because a duplicate name exists on the network. If joining a domain, go to System in Control Panel to change the computer name and try again. If joining a workgroup, choose another workgroup name.

This tells me there is most likely a duplicate name in active directory which was added after this one. You'll need to check AD for the duplicate and find that server or item. It could be disabled, in an odd state, or could have already been removed. In AD, if another computer object is added with the same name (successfully) then it will overwrite the SID in the AD database with the new computer object and giving you this error for the old one. When the new one is removed, renamed, or deleted, nothing happens to the old one so it'd need to be re-added. In this case, the listener re-created though I'd expect the creation to error out...

  We have double checked and all of the WFC setting seem to be fine and we can't find any duplicate IPs on our network.

Right, the error is specifically talking about the NAME, not IP.


Same column present on two or more foreign key constraints

I'm not entirely clear on what the standard says about this, but clearly, there is a conflict between:

  a) FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE NO ACTION

and

  b) FOREIGN KEY (AID, CID) REFERENCES A_COPY(AID, CID) ON DELETE CASCADE

The final result may differ if a) is evaluated before b) or the other way around. There is a similar question in standard-behaviour-for-mix-of-on-delete-cascade-and-on-delete-restrict-const


Queries running parallel with default maxdop

The simple answer is that 64 is the DOP limit when zero is specified:

  To enable the server to determine the maximum degree of parallelism,
  set this option to 0, the default value. Setting maximum degree of
  parallelism to 0 allows SQL Server to use all the available processors
  up to 64 processors.

A single query can use more than 64 cores if you set that specific number (i.e. not zero):

  Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution

See also SQL Server MAX DOP Beyond 64 – Is That Possible?

As for how SQL Server will allocate threads, this blog post is the closest I've seen to official documentation:

SQL Server Parallel Query Placement Decision Logic


MySQL settings useful to speed up a mysqldump import

SUGGESTION #1

No need to run unique_checks = 0 and foreign_key_checks = 0. See my 3-year-old post Speeding up mysqldump / reload (ASPECT #2 shows a standard header of a mysqldump. Lines 13 and 14 handle the disabling of those checks for you)

SUGGESTION #2

Please note the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

If you want to reload a MySQL Instance you should temporarily disable the Double Write Buffer.

STEP #1

Login to the Target Server and run

SET GLOBAL innodb_fast_shutdown = 0;

STEP #2

Restart mysqld by setting to innodb_doublewrite to OFF

service mysql restart --innodb-doublewrite=OFF --innodb-fast-shutdown=0

STEP #3

Load the mysqldump into the Target Server

STEP #4

Restart mysqld normally (Double Write buffer will be enabled again)

service mysql restart

Since the name "Double Write Buffer" implies two writes, InnoDB will only write data and indexes straight to the table files and bypass writing to the Double Write Buffer within ibdata1. Maybe this will double the import time (pun intended)

SUGGESTION #3

The default innodb_log_buffer_size is 8M. You need a bigger Log Buffer.

Please add this line to my.cnf under the [mysqld] group header

[mysqld]
innodb_log_buffer_size = 128M

Then, restart mysqld before the reload of the mysqldump.

GIVE IT A TRY !!!


Is it better to add a data file or better to increase the existing files (MAXSIZE)

Is there anything special to keep in mind with fail over clusters?

No

  Is there any reason why to prefere one of the options?

Yes, especially depending on your IO subsystem.  SQL Server uses two algorithms when deciding which file to use for allocating new extent. Round robin and proportional fill. My recommendation will be to use your first option.

I could use ALTER DATABASE with MODIFY FILE to increase the size of the existing files (~1TB each at the moment).

  Round robin means that the SE will try to allocate from each file in a
  filegroup in succession. For instance, for a database with two files
  in the primary filegroup (with file IDs 1 and 3, as 2 is always the
  log file), the SE will try to allocate from file 1 then file 3 then
  file 1 then file 3, and so on.

  The twist in this mechanism is that the SE also has to consider how
  much free space is in each of the files in the filegroup, and allocate
  more extents from the file(s) with more free space. In other words,
  the SE will allocate proportionally more frequently from files in a
  filegroup with more free space. This twist is called proportional
  fill.

  Proportional fill works by assigning a number to each file in the
  filegroup, called a ‘skip target’. You can think of this as an inverse
  weighting, where the higher the value is above 1, the more times that
  file will be skipped when going round the round robin loop. During the
  round robin, the skip target for a file is examined, and if it’s equal
  to 1, an allocation takes place. If the skip target is higher than 1,
  it’s decremented by 1 (to a minimum value of 1), no allocation takes
  place, and consideration moves to the next file in the filegroup.

Paul Randal explains this in details with example,
Investigating the proportional fill algorithm.


What is log sequence number ? How it is used in MySQL?

From mysqlperformanceblog

  Log Sequence Number (LSN) : Log Sequence Numbers correspond to given
  position in the log files and typically incremented for each log
  record. Innodb uses number of bytes ever written to the log files
  however it could be something different. LSNs are often extensively
  used in recovery check pointing and buffer management operations. When
  checkpoint (both fuzzy and not) happens you get something like “all
  changes up to LSN=X are now flushed to the data space” this means you
  can discard or archive logs for LSN earlier than that. When doing log
  recovery checking LSN in the log record can tell if you this change
  needs to be applied or it already was applied (when doing recovery you
  do not know which dirty pages were flushed from the buffer pool).

  The LSN do not relate much to transactions – changes from different
  transactions are intermixed in the log files and many LSNs can
  correspond to changes from the same transaction.

For other references please have a look at

1 How to know the current log sequence number in 5.0.X.

2 How to check if Innodb log files are big enough


how to find maximum of a column and other corresponding columns values in that table

This is really basic SQL, anurag:

SELECT column1, MAX(column2)
FROM my_table
GROUP BY column1;

Suggest you read up on some more basics.


SQL Server Full-text search for keywords without prefix matching

Yes, it's possible to achieve what you described using CHARINDEX.
According to this remark:

  If CHARINDEX does not find expressionToFind within expressionToSearch,
  CHARINDEX returns 0.

Knowing that you just have to filter the rows like this:

DECLARE @expressionToFind varchar(50)  = 'an';

SELECT MiddleName
FROM AdventureWorks.Person.Person
WHERE CHARINDEX(@expressionToFind, MiddleName, 1) <> 0;

Here's the result:

As you can see, it brings not only the middle names that begin with an but also the ones with an in the middle of the text.

A little remark regarding this method is that differently from using the Full-Text Search it will scan your table. It might (and almost certainly will) have a big performance impact.


Should I create a multi-column UNIQUE index?

You would create the index if any of this is true:

There are queries or DML statements that benefit from this index.
You want to enforce uniqueness of the combination of the index columns.

You would not create the index if all of this is true:

There are no queries or DML statements that benefit from it.
You don't want to enforce uniqueness of these columns.
DML statement performance against this table is critical and the benefit of one fewer index offsets the loss (if any) of query performance.

This applies equally to non-multi-column indexes.


Getting the percentage of a result set for same ID rows

The datatype of a COUNT is a [BIG]INT and integer divison results in truncated values, either zero or one. Multiplying this times 100.0 returns  either 0.0 or 100.0, so you just need to change the order of calculation to multiply first:

100.0 * COUNT(CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) /COUNT(c.CaseID) AS 'Death Events',


I think you should look at locking mechanism of table, there are three types of them - AllPages, Datapages and Datrows, you can read more about them at:

Locking Mechanism in Sybase

If you alter locking of table, update/delete and select can take place together without any blocking on mentioned table.

Inside query, you can put noholdlock on a table or other options for other users to access/no access based on these options as mentioned in below link:

Controlling Isolation

In order to answer your question in detailed level, we need to have more clarity on question. Your question doesn't explain details of issue. I hope above helps.


Can NoSQL databases cause occasional data loss?

Though this is years old question...

In short, you can understand ACID as guarantee of data integrity/safety in any expected circumstances.
As like in generic programming, all the headaches comes from multi-threading.

The biggest issue on NoSQL is mostly ACI. D(urability) is usually a separated issue.

If your DB is single-threaded - so only one user can access at once -, that's natively ACI compliant. But I am sure virtually no server can have this luxury.

If your DB need to be multi-threaded - serve multiple users/clients simultaneously - you must need ACI-compliant transaction. Or you will get silent data corruption rather than simple data loss. Which is a lot more horrible. Simply, this is exactly same with generic multi-threaded programming. If you don't have proper mechanism such as lock, you will get undefined data. And the mechanism in DB called fully ACID compliance.

Many YesSQL/NoSQL databases advertises themselves ACID-complient, but actually, very few of them are really does.

No ACID compliance = You will get always undefined result under multi-user (client) environment. I don't even think what kind of DB does this.
Single row/key ACID compliant = You will get guaranteed result if you modify only single value at once. But undefined result (=silent data corruption) for simultaneous multi row/key update. Most of currently popular NoSQL DBs including Cassandra, MongoDB, CouchDB, … These kind of DBs are safe only for single-row transaction. So you need to guarantee your DB logic won't touch multiple rows in a transaction.
Multi row/key ACID compliance = You will always get guaranteed result for any operation. This is minimal requirements as a RDBMS. In NoSQL field, very few of them does this. Spanner, MarkLogic, VoltDB, FoundationDB. I am not even sure there's more solutions. These kind of DBs are really fresh and new, so mostly nothing is known about their ability or limitation.

Anyway, this is a comparison except D(urability). So don't forget to check durability attribute too. It's very hard to compare durability because range becomes too wide. I don't know this topic well…

No durability. You will lost data at any time.
Safely stored on disk. When you get COMMIT OK, then the data is guaranteed on disk. You lost data if disk break.

Also, there're difference even on ACID compliant DBs.

Sometimes ACID compliant / you need configuration / no automatic something.. / some components are not ACID-complient / very fast but you need to turn off something for this... / ACID-compliant if you use specific module... = we will not bundle data safety by default. That's an add-on, option or separated sold. Don't forget to download, assemble, setup and issuing proper command. Anyway, data safety may be ignored silently. Do it yourself. Check it yourself. Good luck not to make any mistake. Everyone in your team must be flawless DBA to use this kind of DB safely. MySQL.
Always ACID compliant = We don't trade data safety with performance or anything. Data safety is a forced bundle with this DB package. Most commercial RDBMS, PostgreSQL.

Above is typical DB's implementation. But still, any other hardware failure may corrupt the database. Such as memory error, data channel error, or any other possible errors. So you need extra redundancy, and real production-quality DB must offer fault tolerance features.

No redundancy. You lose all data if your data corrupted.
Backup. You make snapshot copy/restore. You lose data after last backup.
Online backup. You can do snapshot backup while the database is running.
Asynchronous replication. Backup for each second (or specified period). If machine down, this DB guaranteed to get the data back by just rebooting. You lose data after last second.
Synchronous replication. Backup immediately for each data update. You always have exact copy of original data. Use the copy if origin breaks.

Until now, I see many DB implementation lacks many of these. And I think if they lacks proper ACID and redundancy support, users will lose data eventually.


SQL Server Mirroring (with Witness) and Client Timeouts

When to failover is decided by the Witness server not but the Primary or Secondary servers in a mirroring session.

The Secondary Server will come online if the Witness Server cannot see the Primary Server. The Secondary server only gets transactions from the Primary server and nothing else. If the Primary Server stops sending Transactions the secondary server will just simply sit there in restoring mode and do nothing, But if Primary servers becomes invisible for Witness Server thats when the failover happens and the Witness server will make Secondary server to come online.

Actually this the whole purpose of having a Witness server(Auto-Failover). If the secondary server could make these decisions on it own we would not need a witness server at all :) .


Change permissions of postgres copy to exported file

You'll want to create a directory without the t (sticky) permissions bit set, and have PostgreSQL copy the file into there. You should also make the directory setgid (g+s), as that makes the group inherited from the directory, not the creating process; see setgid directories.

You could put this directory in /tmp but personally I'd put it some neutral location like /var/lib, or mounted volume somewhere, as /tmp gets cleaned out automatically so you'll need to have the directory re-created if it's missing.

Assuming you do use /tmp:

mkdir -p /tmp/pgcsv
chgrp users /tmp/pgcsv
chmod u=rwX,g=rwsX,o= /tmp/pgcsv

then create the csv in there. It'll be automatically created with group-owner users, which will make it readable by your users (unless PostgreSQL's umask is too restrictive). Because the directory is group-writable by users (or whatever group you use) and does not have the t (sticky) bit set your users can delete files they do not own from it.

The reason this doesn't work when using /tmp/ directly is that the sticky bit is set in /tmp for security, so that user1 can't delete a file created by user2 and replace it with a symlink that user2 then writes to, allowing user1 to steal their data / trick them into overwriting things.

Other options:

Run the cron job as user export, using psql's \copy command, so it writes the data over the client/server socket and the file is created as the export user in the first place. This is by far the simplest option, and only slightly slower than a direct COPY.
Have a cron job running as root invoke the PostgreSQL copy then move the data file and change its ownership once the COPY finishes.
Give the export user the ability to run a very restricted command as root via sudo to move/delete.
Use POSIX ACLs to grant the postgres user special permission to write to /home/export (if POSIX ACLs are enabled on your file system)
Set group-owner of /home/export to postgres and make its permissions g=wx. PostgreSQL can add new entries, and overwrite anything within that it has write permission to, but not list entries. I don't like this option much.


MySQL Workbench - Error Getting DDL Object (It doesn't exist?)

I discovered, after a long research, that it could be a process which wans't interrupted (somehow).

Then it was only:

show processlist;
kill [ID of the running process];

And then I was able to drop/alter that table.


"Mysql has gone away" heavy multiple insert

I usually blame the size of your MySQL Packet

I have written past posts on this

Sep 01, 2011 : MySQL server has gone away obstructing import of large dumps
Sep 25, 2011 : Fixing MySQL errors recorded in error logs
Jun 12, 2012 : MySQL Error Reading Communication Packets

Just raise your MySQL Packet size (max_allowed_packet) in /etc/my.cnf

[mysqld]
max_allowed_packet=256M

and restart mysql


Is there any way to save the execution plan in a file in PostgreSQL?

No, there is no way to save a query plan to disk, force a query plan, etc.

Fix your optimizer parameters and make sure your stats are accurate. You didn't bother to mention your version, show explain analyze output, etc so I can't help you with that in detail. Start here:

http://wiki.postgresql.org/wiki/Slow_Query_Questions

Check your rowcount estimates vs actual results in explain analyze output. http://explain.depesz.com/ makes this easier.

Check that cost estimates reflect reality. Consider adjusting random_page_cost / seq_page_cost if your random I/O is faster or slower than PostgreSQL's estimates. Also ensure effective_page_size reasonably reflects the amount of RAM available for caching.

If you continue to have issues after making appropriate changes per the manual and tuning guides, please report a detailed description of the problem and a self-contained test case to the pgsql-peform mailing list.

See:

Why PostgreSQL doesn't have query hints
Hinting at PostgreSQL
https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index
Why PostgreSQL actually already has hints
The wiki page on hints
a somewhat topical frothing rant on the topic

My personal opinion is that PostgreSQL's policy of having no hints (except actually there are hints, they're just uglier hacks than actual hints would be) is user-hostile and pointless. Most users will use whatever hacks they can to avoid actually understanding cost-based optimizers, so we're just forcing them to use uglier hacks. Users who want to understand and do it right will still find times the optimizer produces inappropriate results, and while it's good to fix those, it's also good for those users to have a workaround in the mean time. And users who're cost based optimizer purists don't have to use any hinting features that are there, including the not-hints that already exist.

This drives me especially nuts with common table expressions, where PostgreSQL's quirky interpretation of them as optimization fences is going to cause us pain and suffering down the road when we want to start optimizing into them, but discover everyone's been using them as a sort of query hint workaround for the query hints we don't have.

Examples of not-hints include:

The enable_ parameters
OFFSET 0
Optimization fence of CTE terms


How can I make my database more concise?

This is more of a comment, but since this is a lengthy response that's worth mentioning, I'm posting it as an answer.

Your database looks sufficient for your needs.  (Well, as best can be assumed from the diagram.)  I assume that the UserAccount table has a one-to-many relationship between the four tables Vehicle, Book, VideoGame and Movie (from here on, the V-B-V-M tables.)  Next, I assume that the Asset table has a one-to-one relationship with each link between the V-B-V-M table entries.  With this information, I would recommend moving the UserAccount_id from each V-B-V-M table, to the AssetTable.

Next, it seems that Asset is always associated with one of the four V-B-V-M tables.  Considering this type of setup, I would recommend creating an additional table-- AssetType.

+-----------+
| AssetType |
+-----------+
| id        |
| typeDesc  |
+-----------+

Next, update the Asset table and each V-B-V-M tables to include a AssetType_ID field.  the AssetType table should include one entry, for each type of asset.  In this case, Vehicle, Book, Video Game and Movie, each with a unique description and ID.

You may want to do this for a couple reasons-- First, you can now query the Asset table and discern what type of asset is stored in a specific record, without having to link the data to your four other V-B-V-M tables.  This will speed up your querying of the data, when all of the information from your four tables isn't needed.  Second, this also provides an easy means of linking in a textual description of the type of asset associated with the Asset entry. Again, without having to link to the V-B-V-M tables to figure that out.

Last, you may want to consider handling the barcode data differently.  If barcode is a simple look-up, you can add it to the asset table.  However, if barcode values have to be unique, keeping it in a secondary table is necessary because you'd have to put a constraint on the database.  You could do this if it was integrated in the Asset table, however, your vehicle data would cause problems because vehicles do not have barcodes.  To my knowledge most, if not all, major database engines won't allow you to define a unique field, that also allows null values because, then, the data is no longer unique.

You might be inclined to think that you could combine the Barcode field with the VIN.  Since VINs are unique and Barcodes are unique, and neither should be the same, why can't they be combined into one field on the Asset table, which allow you to drop the additional Barcode table.  I wouldn't recommend this because I think it gives the data less "focus".

Yes, it is a form of unique object ID but barcodes are barcodes and VINs are VINs-- the concept behind both is completely different and consolidating the two can become confusing with time.  Furthermore, if you expand your project in the future, it could cause collisions.  Technically, I don't see a big problem with that (in your case) but I'd still highly recommend avoiding that option.

Also, I would also add the assetType_id to the Barcode table, for the same reasons you added it to the Asset table.


sqlite: attached database doesn't work properly

Linux has case sensitive file names.

       main: /db/RecordingDb.sqlite
RecordingDb: /db/RecordingDB.sqlite
                           ^


One SQL statement different results by using SQLOLEDB or MSDASQL

OLEDB via ADO has always had the issue where DONE_IN_PROC messages (rows affected) confuse code that is not expecting them.  A workaround without app code changes is to specify SET NOCOUNT ON as the first statement in the batch:

SET NOCOUNT ON;
DECLARE @s varchar(1000), @ver int;
SET @ver = 10;
SELECT @s = case
WHEN
@ver > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END;
EXEC (@s)

EDIT:

A TDS protocol DONE, DONEINPROC, or DONEPROC message is returned to the client upon completion of each T-SQL statement and proc.  These messages can optionally include a row count (indicated by a DONE_COUNT flag).  Higher level APIs like ADO generally ignore these TDS messages except when a row count is included.  But ADO has the nasty habit of returning a result set with no columns or rows in this case.  Not only may this behavior vary by provider/driver, it is further complicated when informational and error messages are generated but not consumed by the client.  This can result in a multi-statement batch that does not run to completion and errors that are not detected.

SET NOCOUNT ON is a convenient way to avoid these issues rather than write code to handle all messages and skip these empty result sets.

I should add that this applies to unmanaged "classic" ADO, not ADO.NET.


How do I remove the excess white space from the values in my SQL table?

The first thing you should do is change the datatype of the column to nvarchar(200),

The char and nchar datatypes are intended for data that is more or less fixed length. Your example shows that you are not dealing with fixed length 200 character strings.

Currently each instance of the 'Logged in' string takes 400 bytes. Stored as nvarchar it will take 20 bytes (and 11 as varchar).

Then you can RTRIM in an UPDATE statement to remove the trailing spaces.

NB: In some circumstances you might find that creating a new table from scratch actually works better than the above as the insert can be minimally logged and it doesn't copy the data once and then trim it. It also ensures that no space is consumed by the dropped column. You can use the SSMS table designer to generate the script for this and then add a RTRIM to the result for something like the below.

SET XACT_ABORT ON;
BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_YourTable
  (
     /*Other columns*/
     YourColumn NVARCHAR(400) NULL
  )

GO

INSERT INTO dbo.Tmp_YourTable WITH (TABLOCKX)
            (YourColumn)
SELECT /*Other columns*/
       RTRIM(CONVERT(NVARCHAR(400), YourColumn))
FROM   dbo.YourTable WITH (HOLDLOCK TABLOCKX)

DROP TABLE dbo.YourTable

GO

EXECUTE sp_rename
  N'dbo.Tmp_YourTable',
  N'YourTable',
  'OBJECT'

GO

COMMIT


database design for optional association and temporal

If important attributes of Spray or/and Sweep are changing over the time, I'd rather introduce Spray_version and Sweep_version and make them immutable (every change in Spray or Sweep will create a copy in appropriate *_version table , so old values are never lost). Thus, you refer to *_version tables.

However, in real world I found it's less expensive (from maintenance and readability perspective) to ignore normalization rules and store extra data in Tasks.


Need to add a Table Level Constraint to prevent bad data - Constraint is not working

You might want to look up in some reliable resource (e.g. the f... manual, or in the SQL standard), when exactly CHECK constraints are verified.  And if it turns out to be "after the INSERT has been processed", then you might try to figure out how the count of existing matching rows can ever be zero.

Apart from that, most engines disallow referencing tables in CHECK constraints.  It's hopelessly annoying, but there is a very good reason for it !!!.  That reason is that the CHECK constraint should also be checked in the event of an update occurring to the referenced table, that somehow impacts the result of this CHECK constraint for some row of the table in which it is declared.

Engines typically do not know how to do this efficiently.  So they impose that limitation upon the users so that at least the integrity of the data is not compromised.  Do not make the mistake of thinking that you are smart when you discover that a UDF with some SELECT can circumvent the limitation.  You're only deluding yourself.  And a couple of months later, you'll just be the next one to ask yet another "CHECK constraint not working" question.


mySQL 5.1 changed innodb_log_file_size getting error on restart

Yup, you'll need to remove the log files to change their sizes.  They're in where ever your DATADIR is.  Just make sure you remove them when the server is shutdown.  Then restart and you'll be fine (it will recreate them).

DO NOT remove any ibdata files, though.  That has your actual persistent data.


How to add linked server SQL Server 2008 R2

Change the server type to SQL server, put the servername,port number in the Linked Server name; on the Security section, change to "Be made using this security context" and enter the user name/password, assuming they are SQL Authentication credentials.


How to select all data from specific tables for a given schema/prefix in MySQL Database

I think you actually want to use GROUP_CONCAT as per this StackOverflow answer.
This will allow you to prepare all rows into one statement that you can execute.


how to change mysql table auto_increment primary key

The auto_increment field must be an index, or be the first column of one. If you try to just drop the primary key, MySQL will complain. You have to delete the auto_increment property before moving it:

mysql> ALTER TABLE mytable MODIFY id int, DROP PRIMARY KEY, ADD PRIMARY KEY (ticker);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id                | int(11)      | YES  |     | NULL    |       |
| exchange_id       | int(11)      | YES  | MUL | NULL    |       |
| ticker            | varchar(32)  | NO   | PRI | NULL    |       |
| instrument        | varchar(64)  | NO   |     | NULL    |       |
| name              | varchar(255) | YES  |     | NULL    |       |
| sector            | varchar(255) | YES  |     | NULL    |       |
| currency          | varchar(32)  | YES  |     | NULL    |       |
| created_date      | datetime     | NO   |     | NULL    |       |
| last_updated_date | datetime     | NO   |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

If you want id to continue being auto_increment, you have to do also:

mysql> ALTER TABLE mytable ADD INDEX `id` (id), MODIFY id int auto_increment;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | MUL | NULL    | auto_increment |
| exchange_id       | int(11)      | YES  | MUL | NULL    |                |
| ticker            | varchar(32)  | NO   | PRI | NULL    |                |
| instrument        | varchar(64)  | NO   |     | NULL    |                |
| name              | varchar(255) | YES  |     | NULL    |                |
| sector            | varchar(255) | YES  |     | NULL    |                |
| currency          | varchar(32)  | YES  |     | NULL    |                |
| created_date      | datetime     | NO   |     | NULL    |                |
| last_updated_date | datetime     | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

Please make sure to create if with the right index (multiple, unique) and nullability (null, not null).

Please also note that, depending on the engine used and/or the queries to be performed, a character field for a primary key may be suboptimal.


Data validity and accuracy according to database integrity

Database integrity generally refers to the following conditions being satisfied:

All constraints on a table are satisfied (including unique constraints, foreign key constraints, primary key constraints, etc....)
All check constraints are satisfied (for example, in Oracle, I can have a check constraint that requires that a value be nonzero, but not otherwise unique.)
All unique indexes are satisfied
All child rows in child tables have parent rows in parent tables (foreign keys)
All data types are satisfied

Note that there's not really anything here that speaks to if the data is valid and/or accurate according to your business logic. This logic is anything you put into triggers, procedures, validation routines, etc., that you build yourself to try and make sure that everything going into the database is good. (It should be noted that Oracle's check constraints could be considered a form of business logic that is enforced like a unique or primary key constraint).

The database will happily reject data that doesn't fit into the above criteria, assuming they are defined, but will also happily accept invalid or inaccurate data that isn't otherwise in the above criteria. If I have a field that contains people's names, the database can't make the determination if "John Smith" or "Jonh Smiht" is accurate (notice the typo). So while the database will say everything's good, you know from a visual inspection that it isn't. (This example, while trivial to do, is hard to do anything about. After all, how do you know his name isn't really spelled this way? I use it only as an example of any data coming into the system. A field expecting percentages could easily have 5% as 50% -- how can the database tell which is valid? It can't without you writing lots of complicated business logic, and even then -- at some point, how do you know it isn't 50%? Or that it isn't 5%? Or that it was supposed to be 25%, and the typist got really confused!)

Use the database integrity mechanisms to your advantage, but don't assume that because there are no integrity errors that your data is good. It can still, easily, be bad and misleading. Further, don't assume that your business logic ensures good data either. Yes, you can prevent obvious errors (like, say, a field that shouldn't have values out of the range of 0 to 100), but once ranges are satisfied, at some point, you have to trust the user who is giving you the data. And at some point, the user will give it to you incorrectly. Poof, bad data, with perfectly valid database integrity.


What's the most appropriate NLS_SORT parameter to cover all/most European languages?

WEST_EUROPEAN would seem to be the obvious choice, but there is no default that lets you avoid setting it on an individual language basis.


Easiest way to import access db (.accdb) into SQL Server 2016?

1.Open MS SQL Server Import and Export Wizard.

2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”.

3.Click “Properties” enter the location of the .accdb file in the “Data Source” field. That is the physical location of the Access database file, like C:\Temp.

4.Under login information, choose a blank username and be sure to check “Blank Password”.

5.Test the connection. If it works, click OK.

6.For the “Data Destination” choose “Microsoft OLE DB Provider for SQL Server”.

7.Enter the MS SQL Server name or choose from the dropdown menu.

8.Enter your SQL credentials and select the database you wish to import the data to, the database should show up in the dropdown menu if your MS SQL info is correct.

9.Select “Copy data from one or more tables or views”.

10.Select the tables you want copied and edit the mappings if needed.

11.Continue through the wizard and hit finish to begin the import.


4m Rows in MySQL, ~5 Minutes to Run Query

SELECT  u.usrid, u.username, u.rank, u.extras, x.dtime
    FROM
      ( SELECT  userid, MAX(datetime) dtime
            FROM  log
            GROUP BY  userid
            HAVING  dtime < NOW() - INTERVAL 1 YEAR
      ) AS x
    JOIN  users u ON u.usrid = x.userid
    WHERE  ( u.rank = 'P'
              OR  u.extras LIKE '%W%'
           )
    ORDER BY  x.dtime DESC

Plus

 INDEX(usrid, datetime) -- in `log`

(I assume usrid is the PRIMARY KEY in users.)

Is size an issue?  Yes and no.  With the right indexes and queries, etc, billion-row tables can work fine.  In your case, it is dubious...  The subquery will probably produce 500K distinct users from its GROUP BY.  Then the HAVING will shrink the number.  What's left needs to be looked up in users and further filtered.  Finally another sort (ORDER BY) before delivering the results.

I think that newer versions of MySQL will leapfrog through the INDEX I suggested, landing on only 500K index rows, not all 4M.

If you think the rank and extras do a better filtering than MAX(datetime), then a different approach might be faster.


Why do you want to complicate it?

1). vi the backup.sql file.

2). /mydb -> find for line 'mydb'

3). For sure the first two line will be this.

 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` ;
    use `mydb`;

4). Just change mydb from both the lines to mydb2 and save the file
and import it.


Manually increase AUTO_INCREMENT column by one

I make no promises with this one, but you might get away with the following:

For a table mytable in the mydb database

SET @src_db = 'mydb';
SET @src_tb = 'mytable';
SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
INTO @sql FROM information_schema.tables
WHERE table_schema=@src_db AND table_name=@src_tb;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

After running this, you can verify with

SHOW CREATE TABLE mydb.mytable\G

I just tried this with a table ganeshbora.customer on my Windows laptop (5.7.12)

SAMPLE CODE

SET @src_db = 'ganeshbora';
SET @src_tb = 'customer';
SHOW CREATE TABLE ganeshbora.customer\G
SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
INTO @sql FROM information_schema.tables
WHERE table_schema=@src_db AND table_name=@src_tb;
SELECT @sql;
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
SHOW CREATE TABLE ganeshbora.customer\G

SAMPLE CODE EXECUTED

mysql> SET @src_db = 'ganeshbora';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @src_tb = 'customer';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE ganeshbora.customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
    -> INTO @sql FROM information_schema.tables
    -> WHERE table_schema=@src_db AND table_name=@src_tb;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @sql;
+---------------------------------------------------+
| @sql                                              |
+---------------------------------------------------+
| ALTER TABLE ganeshbora.customer AUTO_INCREMENT=15 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE ganeshbora.customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

As shown, the auto_increment of the table was incremented from 14 to 15. Of course, I did this with all privileges:

mysql> SELECT user(),current_user();
+---------------------------+---------------------------+
| user()                    | current_user()            |
+---------------------------+---------------------------+
| rolandomysqldba@localhost | rolandomysqldba@localhost |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------+
| Grants for rolandomysqldba@localhost                                           |
+--------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'rolandomysqldba'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

So, if you have the necessary grants to do this ...

GIVE IT A TRY !!!

UPDATE 2018-06-12 14:57 EDT

A comment from JohnathanFite brings up a valid point:

  How is this going to be safe for multiple users at a time? I can see using it to fix it when it's out of sync, but at least warn the OP that running this in a multi-user environment is going to cause problems

While this answer will fix the AUTO_INCREMENT in a perfect world, we don't live in a perfect world. You would need application downtime (1-2 min) to freeze writes and current auto_increment values. If you attempt my answer in a live system, you can quickly screw up auto_increment values because rollbacks of INSERTs against a table with auto_increment values will produce gaps. In many cases, the gap increment may be more than 1 due to bulk inserts that rollback.

Please read my old post from May 12, 2015 : MySQL auto increment column increases after insertion error occurs to see how auto_increment gaps are generated.