how to query SSISDB to find out the errors in the packages?

I have a handful of queries I use. The general concepts are that the table with the information is catalog.operation_messages and you are interested in events with a 120 (error) type.

Depending on how robust of a query you want to build out, the following two derived tables might also be of interest.

--- http://technet.microsoft.com/en-us/library/ff877994.aspx
-- This query translates the message_type from SSISDB.catalog.operation_messages
-- into useful text
SELECT
    D.message_type
,   D.message_desc
FROM
(
    VALUES
        (-1,'Unknown')
    ,   (120,'Error')
    ,   (110,'Warning')
    ,   (70,'Information')
    ,   (10,'Pre-validate')
    ,   (20,'Post-validate')
    ,   (30,'Pre-execute')
    ,   (40,'Post-execute')
    ,   (60,'Progress')
    ,   (50,'StatusChange')
    ,   (100,'QueryCancel')
    ,   (130,'TaskFailed')
    ,   (90,'Diagnostic')
    ,   (200,'Custom')
    ,   (140,'DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.  The value of the message column for DiagnosticEx is XML text.')
    ,   (400,'NonDiagnostic')
    ,   (80,'VariableValueChanged')
) D (message_type, message_desc);

-- Where was the error message generated?
SELECT
    D.message_source_type
,   D.message_source_desc
FROM
(
    VALUES
        (10,'Entry APIs, such as T-SQL and CLR Stored procedures')
    ,   (20,'External process used to run package (ISServerExec.exe)')
    ,   (30,'Package-level objects')
    ,   (40,'Control Flow tasks')
    ,   (50,'Control Flow containers')
    ,   (60,'Data Flow task')
) D (message_source_type, message_source_desc);

I use queries like this to find information about the errors. Maybe I only care about what the errors were (query 1). Other times, I want to know all the activities of all the operations that failed (query 2). Generally, I'm lazy and want to see all the information about the last failing operation (query 3 and note the caveat).

-- http://msdn.microsoft.com/en-us/library/ff877994.aspx
-- Find all error messages
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
,   OM.extended_info_id
FROM
    catalog.operation_messages AS OM
WHERE
    OM.message_type = 120;

-- Generate all the messages associated to failing operations
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
,   OM.extended_info_id
FROM
    catalog.operation_messages AS OM
    INNER JOIN
    (
        -- Find failing operations
        SELECT DISTINCT
            OM.operation_id
        FROM
            catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    ) D
    ON D.operation_id = OM.operation_id;

-- Find all messages associated to the last failing run
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
,   OM.extended_info_id
FROM
    catalog.operation_messages AS OM
WHERE
    OM.operation_id =
    (
        -- Find the last failing operation
        -- lazy assumption that biggest operation
        -- id is last. Could be incorrect if a long
        -- running process fails after a quick process
        -- has also failed
        SELECT
            MAX(OM.operation_id)
        FROM
            catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    );

Perhaps I'm lazy and don't want to go look this information up in the event of a failure, much as your team appears to have done. I have a SQL Agent job that runs on demand and I have jobs that run SSIS packages set to run that job in the event of failure.

DECLARE
    @profile_name sysname = 'SQLAdmins'
,   @recipients varchar(max) = 'billinkc@kfc.com'
,   @copy_recipients varchar(max) = NULL
,   @blind_copy_recipients varchar(max) = NULL
,   @subject nvarchar(255) = 'failed package test'
,   @body nvarchar(max) = 'Stuff has failed, fix please'
,   @body_format varchar(20) = NULL
,   @importance varchar(6) = 'NORMAL'
,   @sensitivity varchar(12) = 'NORMAL'
,   @file_attachments nvarchar(max) = NULL
,   @query nvarchar(max) = N'
SELECT
    O.object_name AS FailingPackageName
,   O.object_id
,   O.caller_name
,   O.server_name
,   O.operation_id
,   OM.message_time
,   EM.message_desc
,   D.message_source_desc
,   OM.message
FROM
    SSISDB.catalog.operation_messages AS OM
    INNER JOIN
        SSISDB.catalog.operations AS O
        ON O.operation_id = OM.operation_id
    INNER JOIN
    (
        VALUES
            (-1,''Unknown'')
        ,   (120,''Error'')
        ,   (110,''Warning'')
        ,   (70,''Information'')
        ,   (10,''Pre-validate'')
        ,   (20,''Post-validate'')
        ,   (30,''Pre-execute'')
        ,   (40,''Post-execute'')
        ,   (60,''Progress'')
        ,   (50,''StatusChange'')
        ,   (100,''QueryCancel'')
        ,   (130,''TaskFailed'')
        ,   (90,''Diagnostic'')
        ,   (200,''Custom'')
        ,   (140,''DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.  The value of the message column for DiagnosticEx is XML text.'')
        ,   (400,''NonDiagnostic'')
        ,   (80,''VariableValueChanged'')
    ) EM (message_type, message_desc)
        ON EM.message_type = OM.message_type
    INNER JOIN
    (
        VALUES
            (10,''Entry APIs, such as T-SQL and CLR Stored procedures'')
        ,   (20,''External process used to run package (ISServerExec.exe)'')
        ,   (30,''Package-level objects'')
        ,   (40,''Control Flow tasks'')
        ,   (50,''Control Flow containers'')
        ,   (60,''Data Flow task'')
    ) D (message_source_type, message_source_desc)
        ON D.message_source_type = OM.message_source_type
WHERE
    OM.operation_id =
    (
        SELECT
            MAX(OM.operation_id)
        FROM
            SSISDB.catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    )
    AND OM.message_type IN (120, 130);
'
,   @execute_query_database sysname = NULL
,   @attach_query_result_as_file bit = 0
,   @query_attachment_filename nvarchar(260) = NULL
,   @query_result_header bit = 1
,   @query_result_width int = 256
,   @query_result_separator char(1) = char(13)
,   @exclude_query_output bit  = 0
,   @append_query_error bit = 0
,   @query_no_truncate bit = 0
,   @query_result_no_padding bit = 0
,   @mailitem_id int = NULL
,   @from_address varchar(max) = NULL
,   @reply_to varchar(max) = NULL;

-- Send email about the failure
EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name
,   @recipients
,   @copy_recipients
,   @blind_copy_recipients
,   @subject
,   @body
,   @body_format
,   @importance
,   @sensitivity
,   @file_attachments
,   @query
,   @execute_query_database
,   @attach_query_result_as_file
,   @query_attachment_filename
,   @query_result_header
,   @query_result_width
,   @query_result_separator
,   @exclude_query_output
,   @append_query_error
,   @query_no_truncate
,   @query_result_no_padding
,   @mailitem_id OUTPUT
,   @from_address
,   @reply_to;

Adjust as you like


Question about the workflow/design process/tools used by professional database designers

In my experience it varies from one company/situation to the next.

As a dba I loved toad and sql developer because of the ease of use.

However there are times where you're required to script things using the lowest common denominator. For example writing installation or upgrade scripts that may be delivered to a number of customers. Some may have sophisticated tools others may not. But they all have SQL or PL/SQL.

My advice is do whatever you feel is right for you. Keep focused on your objective. Is the end result more important than how you get there? Or is the quality of your code and learning experience more important?


I am in the same exam as you, I have asked Paolo what is meant by this:
Minimal key and candidate key determine all other attributes in a relationship.

a primary key is a candidate key chosen as the smallest number of unique attributes.

He states that a minimal key is the fewest number of attributes so he means a primary key when he says minimal


First, lets rewrite the query by dropping the ANSI-89 implicit join syntax and removing the derived tables:

SELECT
       al.log_id , al.user_id , al.page_name ,
       al.section_name , al.submit_time , al.url  ,
       alfm1.value as 'source_env'  ,
       alfm2.value as 'website_id'  ,
       alfm3.value as 'index_id'
FROM
    admin_logs AS al
  LEFT OUTER JOIN
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id
  LEFT OUTER JOIN
    admin_log_field_map AS alfm2
      ON  alfm2.field_id = 911
      AND alfm2.log_id = al.log_id
  INNER JOIN
    admin_log_field_map AS alfm3
      ON  alfm3.field_id = 891
      AND alfm3.value = 'jewelry'
      AND alfm3.log_id = al.log_id
WHERE
    al.page_name = 'Index Management'
ORDER BY
    al.log_id DESC ;

I've also move some of the conditions from the WHERE clause to the responding ON clause, so it is more apparent which indexes you need.

From the two outer joins it's clear that from table admin_log_field_map, you only need the rows with a fixed field_id (either 3351 or 911) and then only the columns log_id (to do the join) and the value (to be returned in the SELECT list):

SELECT
--
       alfm1.value as 'source_env'  ,
--
  LEFT OUTER JOIN
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id

So, a good index for these 2 left joins would be (field_id, log_id, value)

The third (inner) join to admin_log_field_map, is different. You only need the rows with a fixed field_id (891) and fixed value ('jewelry') and then log_id (to do the join). After that, nothing is really needed for the SELECT list (the value is already known and fixed):

SELECT
--
       alfm3.value as 'index_id'
--
  INNER JOIN
    admin_log_field_map AS alfm3
      ON  alfm3.field_id = 891
      AND alfm3.value = 'jewelry'
      AND alfm3.log_id = al.log_id

So, a good index for these 2 left joins would be (field_id, value, log_id)

For the admin_logs table, you already have an index on (page_name) so it will probably be used for the WHERE condition.

So, what you can do is add these two indexes:

ALTER TABLE admin_log_field_map
    ADD INDEX idx__field_id__log_id__value
        (field_id, log_id, value)
  , ADD INDEX idx__field_id__value__log_id
        (field_id, value, log_id) ;


SSIS changes SQL Server source data type from date to WSTR

You are using SQLOLEDB.1 which is a pre-SQL 2008 version of the driver (and deprecated), and the date data type was introduced in SQL 2008.

I suspect the provider isn't detecting the data type as it should, and you should probably switch to the more recent SQL Native client provider.

To illustrate this point, I created an SSIS package with OLE DB connection managers using both SQLNCLI11.1 and SQLOLEDB.1. The Data Flows will both use a  query of SELECT CAST('2017-02-27' AS date) AS DateType;

Looking at the metadata, we see SQLNCLI correctly determines the date datatypes

The SQLOLEDB is unable to determine the date datatype and falls back to the "safe" type of unicode string


It's not an operator in its own right. It is an output column from the table scan operator on the heap. It is the "bookmark" that contains the physical address of the row (this is the same bookmark as is referred to in the phrase "bookmark lookup").

This is passed along the pipeline into the update operator so it knows the row it should be updating.

In SQL Server 2016 you can see the actual values for this column by using the query_trace_column_values extended event

Plugging those into sys.fn_PhysLocFormatter shows the file:page:slot

SELECT sys.fn_PhysLocFormatter(0xB002000001),
       sys.fn_PhysLocFormatter(0xB0020000010001)

Returns

+------------------+------------------+
| (No column name) | (No column name) |
+------------------+------------------+
| (1:688:0)        | (1:688:1)        |
+------------------+------------------+


Is storing values of a unique field shared between many web forms bad practice?

I think that what you're really asking is twofold:

  Is it general practice to make a table with a few custom fields that are accessed by many pages in the app specifically for those few custom fields on many pages.

While it's unusual, this is rather part of the whole intent of having databases in the first place. A central location to store data that can be retrieved from consistently and easily, and that is abstracted from the design of the site.

  Is it going to cause performance issues to have [many of these tables|tables with such small sets of data]?

No, not at all. Database engine designers know that these things will happen, and they're usually smarter than you or I. We can discuss this more if you like, by discussing the mechanics of database engine design, but it should be sufficient to say that "the smart guys thought of all this already".


Table orders regarding Nested loop join in DB2

You are right that for Nested Loop Join, the choice of which table is the inner and which the outer table matters for perforamnce.

However, there is nothing in the documentation, in the link you provided, that implies that for a query that has a INNER JOIN b, the table a will be used as inner and b as outer table when the Nested Loop Join algorithm is selected.

Any decent optimizer evaluates many different combinations of algorithms, placing of tables and order of execution, so I don't think there is any difference if you write a INNER JOIN b or b INNER JOIN a, the chosen execution plans should be the same in both cases. If there are exceptions to this, I would expect them to be for very complex queries with tens of joined tables and/or multiple groupings.

Testing and checking the actual execution plans is one way to confirm this. Another would be to analyze the source code of the query optimizer.

The general guidance when writing SQL (in whatever DBMS), is not to care at all about the table join orders. SQL code describes what you want as result, it doesn't tell the DBMS how to get it. And many optimizers now are really smarter and faster than most of us in choosing the best execution plan most of the time.

Unless documentation shows that the optimizer is naive or in a very early version and the way the queries are written, really affects the chosen execution plan.

Or testing/running of a specific query shows that it's slow and some obviously good plan was not chosen. Then you can experiment with hints (if the DBMS has such feature), try rewriting the query in different ways, check if statistics are updated, etc.


Which type of repair level is “DBCC CHECKDB (databasename, repair)”?

Running this on a sufficiently corrupted database results in the following error message:

Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.

This leads me to estimate that REPAIR is simply the short form of REPAIR_ALLOW_DATA_LOSS.  In the very least, I would treat it as the same when it comes to expectations around recoverability of the database in question.  In other words, this should be considered the operation of last resort, only to be tried when all else fails.  Make sure you make a physical copy of the database before running this process.


The reason that localhost is acceptable would be because it is using a default instance of SQL Server. The default instance, by name, would be MSSQLServer, however you do not need to provide that. Previously the express was a named instance, which requires you to pass along the instance name (or port) in order to connect.
The database name is MyDatabase. The assignment of MyDatabase_Data is for the data file inside the database. In this example a data and log file are designated and named. You can name these however you like (although you cannot have duplicate names within a database). You can also rename them after the database has been created should you so wish using an ALTER DATABASE statement.
Filegrowth is dependant upon data being written to the database. Log files will continue to grow if the database is in full or bulk-logged recovery unless you take log backups. However if in simple recovery then the log will grow as large as is required to support open transactions that happen, or to prevent a loss of data with CDC or replication (if a log continues to grow you can check for reasons using the log_reuse_desc column of sys.databases). For the data file, that will grow as you insert data, as an when needed. It is generally recommended to not use percentage based filegrowths as this can lead to unexpectedly large file growths, not to mention performance problems.

A further note to filegrowth. If you do not have IFI enabled then you will have IO stalls on writes to the data file that cause it to grow. Those stalls will last as long as it takes to grow out the file and zero it out to ensure the space is clear (2016 does not use zeroes, however the concept is the same). With IFI enabled (requires Perform Volume Maintenance Tasks permission for the service account, this can be done at install time with SQL 2016) then the data file can grow instantly and does not need zeroing out.
Log files do not have the option for IFI (for security and data safety reasons), so any growth on that file will cause IO stalls while that completes, impacting performance.

The MAXSIZE settings in the create database statement will prevent the files from growing beyond those values. This would mean that you could not store more an a total of 10MB of data (including system data), and that no individual transaction could be larger than 5MB in size. Both of these values can be adjusted using an ALTER DATABASE statement.


Format timezone to +-#### instead of +-##

The to_char function, as the first choice to format dates, is quite limited with time zones. According to Template Patterns for Date/Time Formatting in the documentation we have TZ and tz and they'll output a time zone name, not a offset against GMT.
You may use extract(timezone_hour from timestamptz_expression) to get the offset as a signed number of hours and extract(timezone_minutes from timestamptz_expression) to get the number of minutes that comes with it. This offset is documented as:

The time zone offset from UTC, measured in seconds. Positive values
correspond to time zones east of UTC, negative values to zones west of
UTC. (Technically, PostgreSQL uses UT1 because leap seconds are not
handled.)

Be aware that there are two conflicting conventions for the sign, east of Greenwich or west of Greenwich. You want to check that the sign is what you're expecting, otherwise invert it.
Another word of caution related to your edit:

How can I return the time as it was inserted (with it's timezone).

Postgres will never know the offset that the timestamp had when it was inserted, it's lost on insertion. What is returned to a SQL client is always the current offset depending on its  current time zone. In order to keep the original time zone, you'd need to store it in an additional column.


How to specify localhost for SSIS package source server?

You can reference the local server with a period .

If you have an instance name, too, then use .\instancename


You have already damaged the database by running REPAIR_ALLOW_DATA_LOSS. You need a professional to asses the loss in your database and see what is recoverable, and how. Contact product support.

Next time know that the most appropriate action is to restore now from a recent non-damaged backup.


How can I keep “approximate” date data points?

There is no "standard" way.  You need to invent something, then write app code to handle it.  Some thoughts:

Strings like what you suggest.  Pro: displays nicely; Con: hard to parse.
Strings, but more like 2018Q1, 201809, or whatever.  Pros/cons:  Somewhat readable; somewhat parsable.
DATE, with the day of the beginning (or end?) of the period, plus another column that says day or month or quarter or annual.  That could be an ENUM or some other technique.  Pro: somewhat sortable.


MySQL Replication: Seconds Behind Master keep growing

The solution was to add the following to my.cnf:

innodb_flush_log_at_trx_commit = 2

Our server was slow in I/O and the row above reduced the disk usage.

It is not recommended for bank databases, but recommended to all others.

Phrased differently:  =1 flushes at then end of each transaction, thereby making it ACID-compliant.  =2 flushes to disk every second, thereby being much less I/O-intensive.


Difference between alert log and archive log in oracle databases

Alert log is a database log file where you can find various messages about your database, like time of startup, some db parameters, log file switch, possible errors that can occur etc.

Archive logs are archived redo(online) log files. In redo log files are all changes that happened to your data. If your database is in archivelog mode, than redo logs can't be overwritten, they are archived in some other location when they are full. Redo and archive logs are used in various database recovery scenarios.

So your Oracle expert probably used LogMiner(or some similar tool) to search through archive log files to find something like truncate table some_table;


Missing Edit Privileges Option In PhpMyAdmin

It appears as if the user you are logged in as does not have the privileges needed to edit access. The edit links are only shown if your user is able to modify permissions.


How to install pgstattuple extension in postgres 10.6?

To install a extension in PostgreSQL exist CREATE EXTENSION command.

Try:

CREATE EXTENSION pgstattuple;

In your database


SQL Server AlwaysOn cluster having issues seeding large availability database creating strange behavior

Please have a look at the links given below. They all talk about similar situations, though not identical, and can help answer a few of your questions.

Many BACKGROUND Threads with Command VDI_CLIENT_WORKER
SQL Server 2016 AlwaysOn VDI_CLIENT_WORKER command
VDI_CLIENT_OTHER wait type

Apart from this you might want to check the following -

Check the SQL Server error log for any related messages - xp_readerrorlog
Check if the log movement is "Suspended" for the databases that show as not synchronized on the secondary replica? - If yes, then you will need to "Resume" the log movement.
You might want to remove these databases (the ones that are not synchronizing) from the AG and then add them again.


Do I need a Primary Key if it's already an Index

Table usage patterns are the main driver of if you need them or not. In OLTP workloads, you usually do want them. If you're just dumping data into these tables and reporting off them in big ol' scans, they're likely less valuable.

And while you may not need a Primary Key at all, adding a clustered index can help you avoid problems that arise when tables are Heaps (tables without clustered indexes).

Again, usage patterns will matter most: if you never update or delete data stored in heaps, you won't hit these specific problems:

Forwarded Fetches

How To Fix Forwarded Records
Identify and Correct SQL Server Forwarded Records
How Forwarded Records are Read and Processed in a SQL Server Heap
Forwarding and forwarded records, and the back-pointer size

Captive Pages

DELETE on a heap table
Heaps, Deletes, and Optimistic Isolation Levels
SQL Server: Inside a DELETE operation in Heaps
SQLskills SQL101: Why does my heap have a bunch of empty pages?


Getting aggregate sub-values in aggregate query

SELECT  ...,
        SUM(s.status_id = 'OPEN') AS total_open_credited,
        ...
    FROM ...
    WHERE ...
    GROUP BY DATE(s.created)   -- a simplification
    ORDER BY ...

Explanation:  s.status_id = 'OPEN' is a boolean expression that evaluates to TRUE or FALSE.  TRUE is 1; FALSE is 0.  Then SUM correctly tallies what you asked for.


Best way for processing over 150 million rows MySql

Big Updates (and Deletes) are problematic.
Plan A:  Avoid the big update.  If value1 should always be 'somevalue' when idRo2 = 1, then don't store it in the table; store it elsewhere and use a JOIN.  Then, instead of checking 150M rows, you are changing exactly 1 row.
Plan B:  Do the Update in chunks of 1K rows at a time.  This avoids timeouts, and a number of other potential problems.  Details:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks .  Even 10K can be problematic, hence my recommendation of 1K.  Anyway, going beyond 1K is getting into "diminishing returns".
Plan C:  Tells us what value1 and idRo2 are really called.  That might lead to some specific suggestions.
Minor issues with datatypes.  Shrinking the table size will help performance some.

INT SIGNED has a limit of 2 billion.  150M is getting kinda close to it.  Keep your eye on any AUTO_INCREMENTs.
Similarly, INT for idCurrencyPair may be wasting space.  (INT takes 4 bytes; there are smaller datatypes)
DOUBLE can lead to rounding errors.  And it take 8 bytes.  Consider DECIMAL(...).
platformTimestamp DOUBLE -- Huh?
I'll bet that LONGTEXT is rarely used?  We should discuss better ways to store it.


Minimum distance of a location; one table of points compared to a set of points SQL 2012

You may have better performance if you convert the function into a table-valued-function.

Here, I setup the test-bed:

USE tempdb;
CREATE TABLE dbo.TableA
(
    LAT DECIMAL(10,5)
    , LON DECIMAL(10,5)
);

CREATE TABLE TableB
(
    Geolocation GEOGRAPHY NOT NULL
);
GO

Here is the table-valued-function, which is basically your function except this returns a table.

CREATE FUNCTION dbo.fxn_distance
(
    @pointTableA GEOGRAPHY
)
returns table
as return
(
    SELECT TOP 1 Distance = b.GeoLocation.STDistance(@pointTableA)
    FROM TableB b
    WHERE geolocation.STDistance(@pointTableA) IS NOT NULL
    ORDER BY geolocation.STDistance(@pointTableA)
)
GO

Insert a simple test-row into each of the two tables:

INSERT INTO dbo.TableA(LAT, LON)
VALUES (49.0,170.0);

INSERT INTO dbo.TableB(Geolocation)
VALUES (geography::STGeomFromText(
    'LINESTRING(-122.360 47.656, -122.343 47.656)', 4326)
);

The query that uses the inline TVF to determine the closest point:

SELECT a.*
    , d.Distance --This is the distance calculated by the TVF.
FROM dbo.TableA a
CROSS APPLY dbo.fxn_distance(geography::STPointFromText('POINT(' +
       CAST(A.LON AS VARCHAR(20)) + ' ' + CAST(A.LAT AS
       VARCHAR(20)) + ')', 4326)) d ;


Add leading zeros to integers less than 7 digits and ignore anything 7+

Ugly (and won't perform all that great), but

SELECT
CASE WHEN len(Column) > 7 THEN CAST(Column AS nvarchar(20))
ELSE RIGHT(rtrim('0000000' + CAST(column as nvarchar(20)), 7) END
FROM table


What is the best way to monitor long running queries in SQL Server?

Following Gaius post: You can create an .SQL script which does what you need with use db in front of the script -> create a SQL Agent job of Operating system type which calls the script:

sqlcmd -E -S SERVERNAME -i"c:\YOURSCRIPT.sql" -o"C:\YOURSCRIPT_LOG.log"

Add new step and use msdb.dbo.sp_send_dbmail procedure to send email. This feature can be customized to display inside the mail a specific query from SQL tables to confirm the execution of the script... for example dbcc showcontig of your rebuild indexes.


Is there a Postgres admin GUI that can execute a master file containing multiple sql files

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

Add an index file in 01, something like ./01/10_tables.psql
Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql


Short Answer.  Not a problem, except for slowing down.

Long Answer: (InnoDB-specific)

innodb_buffer_pool_size controls the InnoDB "cache".  It is broken into 16KB "blocks", each of which comes and goes from RAM based, roughly, on a Least-Recently-Used algorithm.  (See "1 page granularity" mentioned in a Comment.)

So, when you fetch something from a table, the query execution probably needs to look in an index to find where to find the something, and proceeds to fetch blocks as needed to find it.  For simple queries, this is a few blocks, for a full table scan of your 231GB, it will involve fetching all the approx 14 million blocks.

When a block needs to be fetched, but the cache is full, then some block must be bumped out of the cache.  (Hence the reference to LRU.)

If the "working set" of your data is a lot smaller than 231GB, then there won't be much I/O, and the size of the cache (buffer_pool) is not critical.  But if you do table scans, or use UUIDs, then queries will still run, but they will run slower due to the I/O due to [re]loading blocks into the cache.

All operations are done in the cache; almost nothing bypasses the cache.  Indexes and Data are each stored in BTrees that are composed of 16KB blocks.


How to force/require TLS connections on SQL Azure

"Regardless of the client library chosen, data is transferred using tabular data stream (TDS) over a secure sockets layer (SSL). Windows Azure SQL Database doesn’t support unencrypted connections. This will ensure data transmission is secure and can help prevent man-in-the-middle Jump attacks."

I don't know if this is true, I have it from "https://social.technet.microsoft.com/wiki/contents/articles/2763.windows-azure-sql-database-connection-encryption.aspx", but the connection was not secure (!!!!)


Why do I need witness in WSFC with two nodes?

Straight from the docs, with a two-node cluster without a witness:

  One node's vote is zeroed, so the majority vote is determined out of a
  total of 1 vote. If the non-voting node goes down unexpectedly, the
  survivor has 1/1 and the cluster survives. If the voting node goes
  down unexpectedly, the survivor has 0/1 and the cluster goes down. If
  the voting node is gracefully powered down, the vote is transferred to
  the other node, and the cluster survives. This is why it's critical to
  configure a witness.

Understanding cluster and pool quorum

And you can simply use Cloud Witness, so you don't need a shared disk, third node, or file share.


I managed to export the couchbase data to CSV, after converting to CSV I discovered the files were corrupted and there were some entries I had to give up on.

When I first tried there was no "Ignore Errors" option in BigQuery so I couldn't import the files at all, after several weeks I tried again and found the "Ignore Errors" option, I immediately imported my files, first each one of them to a single DB, later on combined them together, it looks great now but it was not tested for compliance yet, I lost several records (a little less than 20) out of 80 gigs of data.


setup table level Autovacuuming on postgres database

Just leave all the default settings as they are. Autovacuum is enabled by default and will usually do the right thing. In my professional life I find that newbies who are trying to tune the autovacuum parameters usually do more harm than good.

One parameter I'd recommend changing is autovacuum_vacuum_cost_delay, which I would lower to 2ms (which is the default setting from v12 on). That way, you run less risk of having a too lazy autovacuum.

If you ask a more specific question, you can get a more specific answer.


Commands creating format files to import txt file into sql server

You need to put in the full path for your format file.  Use the table of the database where you will be importing the data to create your format file:

bcp Database.dbo.table format nul -f c:\txt1.fmt -c -T

Once the format file is created, then you can import the data from the txt file into your database using the format file:

bcp Database.dbo.table in c:\data.txt -f c:\txt1.fmt -T

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


How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator?

I noticed in the execution plan, it's using a Remote Scan showplan operator. I know when possible the Remote Query showplan operator is more efficient...

There's nothing inherently inefficient about the Remote Scan operator. It is used, for example, with BULK INSERT, which is extremely efficient. The performance of the Remote Scan depends on what lies behind it, in this case a linked server, various drivers, networking etc.

A Remote Query operator can be more efficient, for example if a predicate or aggregation operation can be pushed to the remote server, rather than retrieving all rows and doing the work locally.

  ...but I'm not sure if it's possible in this case?

There is no choice except to use Remote Scan to read data from a T-SQL remote stored procedure call.


Converting multiple SQL implicit joins into explicit joins

This is how I would rewrite it:

SELECT *
FROM e
RIGHT OUTER JOIN a ON
  e.p_id = a.p_id AND
  e.o_id = a.o_id
INNER JOIN b ON
a.p_id = b._pid
INNER JOIN c ON
a.p_id = c.p_id
INNER JOIN d ON
c.c_id = d.c_id
INNER JOIN f ON
a.o_id = f.e_id
INNER JOIN g ON
a.o_id = g.o_id
LEFT OUTER JOIN h ON
c.c_id = h.c_id
AND c.b_id = h.d_id
LEFT OUTER JOIN g ON
h.d_id = g.d_id
AND h.o_id = g.o_id

The (+) denote optional tables and end up being converted to either LEFT  or RIGHT OUTER joins.  In "c.c_id = h.c_id(+)" the "h" side is the optional one so this becomes "c LEFT OUTER JOIN h".  If the "c" side had the plus instead it would be a "c RIGHT OUTER JOIN h", although I typically just swap the order and make it a LEFT OUTER JOIN since I think it is easier to read.  I did that in a few cases (LEFT OUTER JOIN g).

If you have multiple conditions for the same set of tables those get combined in the ON clause:

e.p_id(+) = a.p_id
AND e.o_id(+) = a.o_id

becomes

e RIGHT OUTER JOIN a ON
e.p_id = a.p_id
AND e.o_id = a.o_id


Optimizer choose incorrect index for query according to rows number in mysql 5.6

In general, the only way to force the optimizer to take a specific path is to add code (like index hints, as you mention) to explicitly tell the optimizer what it should (or shouldn't) use.

Beyond that, one generally has to look at the specific query and query plan, the information the optimizer has, and the reason why it would have chosen the path it did. In most cases, the optimizer chooses a sub-optimal path because either its information is bad (missing or out-of-date statistics), or because of a cached plan that worked well when cached, but not well with the current parameters. Of course, sometimes the best path available is still bad.

In these cases, you can look at the query plan, see where the query was having the most problems, and figure out a way to give the optimizer better options. This can be creating a covering index; adding or updating statistics (assuming that can be done under MySQL); breaking the query into multiple steps on which the optimizer can make accurate estimates, etc.


Installing SQL Server 2008 DB files on network shares really isn't fully supported properly until you get to SQL 2012.  I'm not sure how it'll work as I've never tried that in prod but you can do it.
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/

To answer your security question though, no, the user authenticating against SQL Server does not need access to that file location, that file, or anything like that.  The SQL Server service account that manages that DB does need access to it though across the network.

Also, No, the remote storage server does not need SQL Server engine or client installed.


Thing i would do to make the transactionlog faster.

Check NTFS block size, and reformat drive to 64k
Check VLF's (should warn in EXEC xp_readerrorlog) if you above 1k.. shrink/grow file to fix.
Set sensible log-file growth rates (not 1% or 1MB)
Consider using Delayed Durability where its possible (be mindfull of Data integrity/DR)

The all-knowing K. Tripp has an article on this
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/


Why MySQL Database is Bigger After Dump/Import?

The first thing that caught my eye was the versions of MySQL you are using.
You said

The source DB is version 5.5.47
The receiving DB is version 5.7.13

These two versions of MySQL use different default values for innodb_file_format

MySQL 5.5 has Antelope as the default
MySQL 5.7 has Barracuda as the default

MySQL 5.7 has new ways of storing InnoDB as mentioned in the Documentation. Please note:

The Barracuda file format is required to use Compressed or Dynamic row formats and associated features such as compression, off-page storage for large variable-length columns, and large index key prefixes (see innodb_large_prefix). This restriction does not apply to tables stored in general tablespaces.

If your data has large variable-length columns, the result of reloading could be the storage of data and index info outside of your BTree Indexes and extra splitting of pages. See DYNAMIC and COMPRESSED Row Formats for more details on this. Many of the new InnoDB file format features are deprecated in MySQL 5.7 and will eventually disappear in future releases.
SUGGESTION #1
Even though deprecated, you could set innodb_file_format to Antelope on the MySQL 5.7 server, restart MySQL, and reload the mysqldump.
SUGGESTION #2
Use MySQL 5.5/5.6 as the Master instead of MySQL 5.7. Restart MySQL on the Master, and reload mysqldump into the Master.
SUGGESTION #3
Run ALTER TABLE innodbtable ROW_FORMAT=COMPRESSED; on all the MySQL 5.7 InnoDB tables.
GIVE IT A TRY !!!


Why configuring automatic failover for SQL availibility group feature on sql failover cluster instance not allowed

Automatic failover is not allowed if availability replica is hosted on SQL Server FCI. Quoting MS Arvind Shyamsundar

This is simply because the SQL Server team did not wanted to add more complexity to failovers scenarios. You already have automatic failover provided in SQL Server FCI and introducing automatic failover with availability group in FCI would make it too complex and may result in unwanted situations.

If you go ahead and try to configure automatic failover you would get below error message. Copied from this blog

  Msg 35215, Level 16, State 17, Line 1
  The Alter operation is not allowed on availability replica 'INST1',
  because automatic failover mode is an invalid configuration on a SQL
  Server Failover Cluster Instance.  Retry the operation by specifying
  manual failover mode.

In the same blog you can see Arvind Shyamsundar writing

  This is expected and by-design, the logic being that the FCI already has automatic failover configured between the nodes. The current implementation of AG doe not allow a failover outside of the FCI nodes.


Migrating MySQL server to MariaDb or Percona?

The overall performance of MySQL/MariaDB/Percona and InnoDB/XtraDB etc, is minimal.  There are specific cases where one shines over another.  You should not expect a magical improvement.

Instead...  You should look at what is specifically causing trouble and tackle that.

High CPU:  Add composite index / revise query -- but first locate the slowly running query.

High I/O:  Same as high CPU, plus poor Joins, excessively sized datatypes (eg, BIGINT where SMALLINT UNSIGNED would suffice), etc.

Swapping:  This is terrible -- probably caused by carelessly increasing values in my.cnf

Come back with worst query, together with SHOW CREATE TABLE.

Adding RAM won't help -- your dataset is significantly smaller than RAM.  But do check innodb_buffer_pool_size.


How can I remove a bad execution plan from Azure SQL Database?

Azure SQL now directly supports this

Azure SQL Database directly supports clearing the proc cache of the current user database without any hacks:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Additional Information

The following script (by Shannon Gowen) can be used to watch the process step-by-step:

-- run this script against a user database, not master
-- count number of plans currently in cache
select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache
select count(*) from sys.dm_exec_cached_plans;

-- list available plans
select * from sys.dm_exec_cached_plans;


"Entity Attribute code uniqueness" error in PowerDesigner

The experts don't always agree on the exact meaning of the terms used in conceptual data modeling, or even necessarily on what conceptual data modeling really is.

But Powerdesigner's restriction in this case is not unique to this product.  Many experts assert that an attribute can only be an attribute of one entity.  So if an attribute, say "color" could be an attribute of hair, or of a car, it's necessary to qualify them as "hair color" and "car color" In order to identify each attribute uniquely.

Notice that there is no such restriction in the relational data model.  An attribute could be a component of more than one relation with no problem at all.

In this sense, conceptual data modeling isn't just "relational lite" at least as far as this school of thought is concerned.  It's a whole different way of looking at the world.

There are other schools of thought, with their own experts, where conceptual data modeling is, in effect, "relational lite".  Powerdesigner must seem jarring to those who follow that school of thought.


MySQL will not serve you stale data (if that is your question).  The index is maintained and kept up to date as part of every operation (whether be INSERT, UPDATE, DELETE etc.).


What is the different between GDR security update and CU security update

You should install the latest CU for SQL 2012 SP3.

As of today (10 May 2017) it is CU8

The SQL version number will give you a good indication of what is more recent.

An explanation of what GDR means can be found here.


Use the FORMAT function, which works for floating point numbers as well as for integers:

Floating point numbers:

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'

Integers:

mysql> SELECT FORMAT(12334, 0);
        -> 12,334


The problem should be with the string literal (i.e. the ''SERVERA'') as that takes on the Collation of the database where that statement is executing.

So try the following for the top part of your query:

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'' COLLATE Latin1_General_CI_AS, [PhoneNum]
...

And, the bottom part of the UNION ALL (the part for [SERVERB]) should probably select "SERVERB" instead of "SERVERA" as the string literal :).

And to be technical, it is taking on the Collation of the DB, not the server, though if the default connection is to master (or any system DB), then that would naturally be the same as the Server-level Collation since the Server-level Collation is used to create the system DBs.

UPDATE:

The new error you are getting after adding the COLLATE clause to the 2nd query in the UNION ALL is due to adding the COLLATE clause to too many of the fields. It needs to go only to the literal string, and not on the resourcephone.[PhoneExt] field. Doing that one extra step put the fields that were already in agreement into conflict. Since both of those table fields are in Latin1_General_BIN, there is no need to override the Collation on the resourcephone.[PhoneExt] field from either Server. It is only the string literal "field" that needs to be overriden, and only from one of the two servers: you just need to make one match the other.

Adding the COLLATE clause to all fields, especially ones that are already working as expected (i.e. the resourcephone.[PhoneExt] field) is unnecessary and is over-complicating the query.


SQL Server stored procedure validity checking

It depends on how strictly you are defining "programmatically". If you mean a process that knows nothing of the proc it is evaluating, then you aren't going to get very far. In addition to the obstacles mentioned by @Aaron in his answer (i.e. Linked Servers, Dynamic SQL, OPENQUERY / OPENROWSET, deferred name resolution, etc), you also have the issue of permissions. The table names being correct doesn't help if the user cannot access them :).

An even bigger issue is the conditional execution of blocks of code. Consider the following test:

First we run it such that the conditional block is skipped.

DECLARE @RandomCondition INT,
        @Test INT;

SET @RandomCondition = 1;

IF (@RandomCondition = 2)
BEGIN
    SET @Test = 'a';
END;
SELECT @RandomCondition AS [RandomCondition];
GO

This encounters no errors and returns a result set of 1 row.

Second time around we run it such that the conditional block is entered.

DECLARE @RandomCondition INT,
        @Test INT;

SET @RandomCondition = 2;

IF (@RandomCondition = 2)
BEGIN
    SET @Test = 'a';
END;
SELECT @RandomCondition AS [RandomCondition];
GO

This encounters the following error (as expected):

  Msg 245, Level 16, State 1, Line 9
  Conversion failed when converting the varchar value 'a' to data type int.

So you really can't even loop through a list of procs and run them by passing NULL in for all input params as different values for a particular parameter might cause it to take several different, and non-overlapping, paths.

On the other hand, if you are defining "programmatically" to mean "an automated process", then there is hope.

You really need to set up integration testing where you can set up the environment (if need be), execute it with specific parameter values, and test the output and/or any expected changes to the environment. AND you can set up as many tests for the same stored procedure as is required by the nature of each stored procedure.

The tool I prefer to use for such testing is DbFit. It has been a few years since I have used it, but it was soooo easy to use. Unlike some of the other SQL testing frameworks, it is completely unobtrusive, requiring no changes to your database(s). The tests are defined in a wiki so it is easy to create and update them. You can group tests together and even use variables across several pages (such as connection string info, etc). You can also automate it via command-line.

Even if you don't use DbFit, you should still have some testing framework as anything you can test for programmatically (in a strict sense) isn't worth the time it will take to set it up; it will catch such a small percentage of problems that you will still need something else to catch the other 80% - 90%.


Documentation on usage of brackets() in mysql queries

Putting parentheses in a BETWEEN leads to strangenesses:

date between (x and y) and filter1 = filter2

is treated as

(date between (x and y) and filter1) = filter2

Why?

BETWEEN expects 2 'arguments'; you have given it (1) (x and y), which returns true or false, which are numerically 1 or 0, and filter1.
The result of that (probably 0) is then compared:  0 = filter, which is likely to be false (0).

Recommendations:

Do not use parens that way in BETWEEN.  These are OK:

(date between x and y)

(date) between (x) and (y)  -- but this is clutter

When mixing AND and OR use parens to make it clear what you want to do.  AND will be done before OR, but too many SQL programmers fail know that, so be explicit:

a AND (b OR c)  -- Without parens, you get  (a AND b) OR c

x = 1 AND ( y = 2 OR z = 3 )

Don't clutter:  Rarely do you need (x) instead lf x.
When in doubt, add parens, even if it clutters.
When using function calls, no space before the (; there are cases where the parser decides whether it is a function call versus something else by using that spacing:  DATE(foo), not DATE (foo).

The documentation spells all of this out, but not necessarily in an obvious way.  Here are clues:

The BETWEEN syntax does not show the parens you asked about.
Any expression can have parens around it.
There is a "precedence" that says what happens when mixing AND and OR -- which is just like * and +.  (You do understand that 1+2*3 is 7, not 9 because * is evaluated before +?)


Give SQL Sentry Plan Explorer a try.  I am not sure it does exactly what you are asking about, but it is a great tool and free!

SQL Sentry Plan Explorer


How to force users to specify a filegroup when creating tables/indexes

You could possible create a new default filegroup with the Read_Only property set.

Add database file:

USE [StackExchange]
GO
ALTER DATABASE [StackExchange] ADD FILE ( NAME = N'StackExchange_DefRO', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeRO.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [DEFAULTRO]
GO

Modify the new filegroup to READONLY:

USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'DEFAULTRO'
if(@readonly=0)
    ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] READONLY
GO

Modify new filegroup to be DEFAULT:

GO
USE [StackExchange]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'DEFAULTRO') ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] DEFAULT
GO

If a user now creates an object without specifying a filegroup:

USE [StackExchange]
GO

/****** Object:  Table [dbo].[NewTable]    Script Date: 21.12.2017 14:12:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NewTable](
    [ID] [int] NULL,
    [Test] [nchar](10) NULL
)

GO

They will receive the error message:

Msg 1924, Level 16, State 2, Line 11
Filegroup 'DEFAULTRO' is read-only.


Storing a json string that is 10 kb in size in a SQL Server CE cell

Just because something is marked as deprecated doesn't meant that it's disappearing soon - there are features that have been marked deprecated for several versions which haven't been removed yet. That said, you're right to move away from deprecated features if the opportunity presents itself (but don't make changes simply for the sake of change), and you should definitely avoid implementing anything new using a deprecated feature.

Per Microsoft's documentation on ntext, the replacement for ntext is nvarchar(max).

You may also be interested in reading about SQL Server 2016's support for JSON data.


PostgreSQL Error: server closed the connection unexpectedly

It looks like broken data file or broken indexes. If this problem grows, then it can shows some deep system problems - problems with hw (memory, CPU), os (antivirus)... Try to use a commands executed from command prompt for database access repairing a) reindexdb - refresh all indexes, b) pg_dump - recheck a readability of data files.


This behaviour is documented, but counter-intuitive:

  Current identity value is set to the new_reseed_value. If no rows have
  been inserted into the table since the table was created, or if all
  rows have been removed by using the TRUNCATE TABLE statement, the
  first row inserted after you run DBCC CHECKIDENT uses new_reseed_value
  as the identity. Otherwise, the next row inserted uses
  new_reseed_value + the current increment value.

It was acknowledged as a bug (on the now-defunct Microsoft Connect site), but not one that will be fixed. Workarounds include avoiding using the RESEED option on newly-created tables, or to use a higher value that will work in all cases but may result in gaps. Gaps are all but inevitable with IDENTITY anyway. If you must do the reseed, wrap it in a check like:

IF EXISTS
(
    SELECT 1
    FROM sys.identity_columns
    WHERE
        [object_id] = OBJECT_ID(N'dbo.T1', N'U')
        AND last_value IS NOT NULL
)
BEGIN
    DBCC CHECKIDENT('dbo.T1', 'RESEED', 0)
END;

To be clear, creating a table and adding a row will result in the first identity value being used:

-- Tested on SQL Server 2008 SP3 CU8
-- build 10.0.5828
CREATE TABLE dbo.T1 (id int IDENTITY(1, 1));
INSERT dbo.T1 DEFAULT VALUES;
SELECT id FROM dbo.T1 AS t;


Getting two or more column values through a Cursor SQL Server

I think I have a solution for you... but!!! IMPORTANT: You are not considering having more than 1 record in DETAIL that equals the Min Value...
You also are missing the TENSION column in TEST table

Anyway, to answer your question try replacing this:

SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID Order by rID)
, @temp=temp

        FROM dbo.ExperimentDetails
            WHERE rid='041'

        Insert into dbo.Test(rid,lat,lon,min,temp) values   (@rid,@latBegin,@longBegin,@minV,@temp)

With this:

    SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID)
    FROM dbo.ExperimentDetails  WHERE rid=@rid

   Insert into dbo.Test(rid,lat,lon,min,temp,tension)
   select rid, @latBegin,@longBegin,@minV,temp,tension from
   dbo.ExperimentDetails  WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @minV

Good Luck!

---FOR STEP 5 -----

First of all,
I will assume that you already have created the other table
let's say: Temp360

So, include another 2 variables in the DECLARE zone...
let's say: @min360 decimal(15,10) and @avgtemp decimal(15,10)

DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10), @min360 decimal(15,10),@avgtemp decimal(15,10)

then use this code inside your cursor loop:

//part 1... what we already know + JUST getting the AvgTemp

        SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude), @avgtemp = AVG(temp)
        FROM dbo.ExperimentDetails  WHERE rid=@rid

       Insert into dbo.Test(rid,lat,lon,min,temp,tension)
       select rid, @latBegin,@longBegin,@minV,temp,tension from
       dbo.ExperimentDetails  WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @minV

//part 2... something similar + the Average Temp we already got.

        SELECT @min360=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID)
        FROM dbo.ExperimentDetails  WHERE rid=@rid  and
Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) >=360

       Insert into dbo.Test360(rid,lat,lon,min,avgtemp,tension)
       select rid, @latBegin,@longBegin,@min360,@avgtemp,tension from
       dbo.ExperimentDetails  WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @min360


Oracle's official documentation library has some great resources, including:

The Database Concepts guide, which is essential reading for beginner and Oracle ninja alike.
The 2 Day DBA
The 2 Day Developer's Guide

Oracle 12c official PL/SQL documentation:

PL/SQL Language Reference
PL/SQL Packages and Types Reference
Chapter 5 Developing Stored Subprograms and Packages from 2 Day Developer's Guide

PL/SQL 101

This is a twelve part series of articles on understanding and using PL/SQL. It is written by Steven Feuerstein and published in Oracle Magazine.

Building with Blocks
Controlling the Flow of Execution
Working with Strings
Working with Numbers in PL/SQL
Working with Dates in PL/SQL
Error Management
Working with Records
Working with Collections
Bulk Processing with BULK COLLECT and FORALL
The Data Dictionary: Make Views Work for You
Wrap Your Code in a Neat Package
Working with Cursors


Just having the table definition and a few rough examples is probably not enough information to get you the answer you want. You need to know which queries are going to be the most heavily used.

For example, you only get 1 clustered index, and that's the most powerful index, so you only have one option for that.

Are all/most of your queries likely to have a particular column (i?) involved? Do you join to other tables? On a common column? That would lead me to think i should be the first column in the clustered index. The subsequent columns in that index, should be the next most-commonly used columns (taking cardinality into consideration). You can put all the columns in the clustered index if you like, as that's effectively the order the data is stored in. So the order of the columns is NB.

Then, single column indexes can reference the clustered index, so that helps. But your choice of those non-clustered indexes and the number/order of columns also depends on the expected usage.

Also, is this a data entry or reporting table? Data entry = more indexes make it slower to enter data. Reporting = index the hell out of it, if needed!


Define log shipping file locations when using AWS for the secondary server

My preferred solution here is to write the backups to a file share managed by a separate Windows file server (likely an EC2 VM). This way, if your SQL Server goes down, the backups are instantly already accessible on that EC2 VM (as opposed to doing backups locally on the SQL Server, which are inaccessible when the SQL Server goes down.)

Then, on that EC2 VM file server, use an S3 folder sync tool that automatically uploads your local backup folder up to an S3 folder.

On the subscriber side (which can be in a different AZ or region), use another EC2 VM file server with the same cloud file sync tool, which then makes the files accessible via UNC path for your subscribing SQL Servers.

Use the native log shipping jobs, but don't use the file-copy job (since that's handled by the two EC2 VMs syncing to S3.) The two log shipping jobs will have different targets, and that's fine.


You can do the following:

CREATE TABLE tablename_temp (id int AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB
SELECT * FROM tablename;

ALTER TABLE b
ADD UNIQUE INDEX some_index_name (your_original_key);

You can find the documentation here: http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html
And a small example on SQLFiddle.


Corruption on MS SQL Database due to 1 failed disk on RAID 6

Firstly you should change your database page verification setting from tornpage to checksum to have more reliable identification of database corruption.
secondly SQL Server has detected an I/O error - not surprising if an entire disk failed (or was removed while you tested your test db). - Anyway you won't know if the database is corrupt until you complete the recommended action - DBCC CHECKDB


Working of window functions and idea window size for window function

The elementary difference is that window functions are applied to all rows in a result set to compute additional columns after the rest of the result set has been determined. No row is dropped. They are available since PostgreSQL 8.4.

The LIMIT and OFFSET clauses of the SELECT command on the other hand do not compute additional columns. They just pick a certain "window" of rows from the result set (in cooperation with the ORDER BY clause) and discard the rest. Have been there like for ever.

While certain tasks can be tackled with either of these tools, they are very different in nature.

For your simple task

  sorting data on date and then bring the latest data first

you don't need either of them. Just add:

ORDER BY date_col DESC

According to your comment, you would need:

SELECT col1, col2
FROM   tbl
ORDER  BY date_col DESC
LIMIT  100   -- 100 latest rows
OFFSET 0;    -- just noise, but may be easier to code

Retrieve more:

...
LIMIT  100
OFFSET 100;  -- next page of 100 rows ...

Be sure to have an index on date_col in either case!


migration by cloning a Production SQL Server host into a Dev environment

Ideally you should have an SSIS package that runs at night and copy the new data from your Production environment to your Developer/Test environment.

This is a guide that will help you doing it right
This is a schema to check if you are doing it right
This is a post that will help you avoid troubles to production


In my experience it's slightly faster to let oracle check it and throw an excpetion if the constraint is violated. If you want to do something special when that happens, you can catch the exception and handle it.

For instance:

EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         UPDATE ...


Postgres 9.5 xlog fills all disk space in replication setup with replication slot

If you just want a hot standby master/slave setup, without, eg point in time recovery, just set archive_mode to false or remove it from your postgres.conf. Otherwise you'll have to come up with a storage plan so you have enough space which might involve setting up archive_command to copy the archive to a disk with enough space.


There is no urgency of getting that setting made.  After the install is finished,

Locate my.cnf
my.cnf may have some "includes" at the end.  One of those places is the 'proper' place to do the next step.
Add any extra settings at the end of the [mysqld] section of the file.
Restart mysqld by restarting the MariaDB 'service'.

You can also set that without restarting, but the setting would be lost at the next restart:

Connect to MariaDB as 'root'.  (I like to use the mysql commandline tool, but there are other ways.)
SET GLOBAL innodb_file_per_table=0;

Now any CREATE TABLE will put the data and indexes in ibdata1.

Note:  To move a table in ibdata1 from or to its own .ibd file (tablespace), do the SET and do ALTER TABLE name ENGINE=InnoDB;


I know that doing COALESCE on a few columns and joining on them isn't a good practice.

Generating good cardinality and distribution estimates is hard enough when the schema is 3NF+ (with keys and constraints) and the query is relational and primarily SPJG (selection-projection-join-group by). The CE model is built on those principles. The more unusual or non-relational features there are in a query, the closer one gets to the boundaries of what the cardinality and selectivity framework can handle. Go too far and CE will give up and guess.
Most of the MCVE example is simple SPJ (no G), albeit with predominantly outer equijoins (modelled as inner join plus anti-semijoin) rather than the simpler inner equijoin (or semijoin). All the relations have keys, though no foreign keys or other constraints. All but one of the joins are one-to-many, which is good.
The exception is the many-to-many outer join between X_DETAIL_1 and X_DETAIL_LINK. The only function of this join in the MCVE is to potentially duplicate rows in X_DETAIL_1. This is an unusual sort of a thing.
Simple equality predicates (selections) and scalar operators are also better. For example attribute compare-equal attribute/constant normally works well in the model. It is relatively "easy" to modify histograms and frequency statistics to reflect the application of such predicates.
COALESCE is built on CASE, which is in turn implemented internally as IIF (and this was true well before IIF appeared in the Transact-SQL language). The CE models IIF as a UNION with two mutually-exclusive children, each consisting of a project on a selection on the input relation. Each of the listed components has model support, so combining them is relatively straightforward. Even so, the more one layers abstractions, the less accurate the end result tends to be - a reason why larger execution plans tend to be less stable and reliable.
ISNULL, on the other hand, is intrinsic to the engine. It is not built up using any more basic components. Applying the effect of ISNULL to a histogram, for example, is as simple as replacing the step for NULL values (and compacting as necessary). It is still relatively opaque, as scalar operators go, and so best avoided where possible. Nevertheless, it is generally speaking more optimizer-friendly (less optimizer-unfriendly) than a CASE-based alternate.
The CE (70 and 120+) is very complex, even by SQL Server standards. It is not a case of applying simple logic (with a secret formula) to each operator. The CE knows about keys and functional dependencies; it knows how to estimate using frequencies, multivariate statistics, and histograms; and there is an absolute ton of special cases, refinements, checks & balances, and supporting structures. It often estimates e.g. joins in multiple ways (frequency, histogram) and decides on an outcome or adjustment based on the differences between the two.
One last basic thing to cover: Initial cardinality estimation runs for every operation in the query tree, from the bottom up. Selectivity and cardinality is derived for leaf operators first (base relations). Modified histograms and density/frequency information is derived for parent operators. The further up the tree we go, the lower the quality of estimates tends to be as errors tend to accumulate.
This single initial comprehensive estimation provides a starting point, and occurs well before any consideration is given to a final execution plan (it happens a way before even the trivial plan compilation stage). The query tree at this point tends to reflect the written form of the query fairly closely (though with subqueries removed, and simplifications applied etc.)
Immediately after initial estimation, SQL Server performs heuristic join reordering, which loosely speaking tries to reorder the tree to place smaller tables and high-selectivity joins first. It also tries to position inner joins before outer joins and cross products. Its capabilities are not extensive; its efforts are not exhaustive; and it does not consider physical costs (since they do not exist yet - only statistical information and metadata information are present). Heuristic reorder is most successful on simple inner equijoin trees. It exists to provide a "better" starting point for cost-based optimization.

Why is this join cardinality estimate so large?

The MCVE has an "unusual" mostly-redundant many-to-many join, and an equi join with COALESCE in the predicate. The operator tree also has an inner join last, which heuristic join reorder was unable to move up the tree to a more preferred position. Leaving aside all the scalars and projections, the join tree is:
LogOp_Join [ Card=4.52803e+009 ]
    LogOp_LeftOuterJoin [ Card=481577 ]
        LogOp_LeftOuterJoin [ Card=481577 ]
            LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_Get TBL: X_DRIVING_TABLE(alias TBL: dt) [ Card=481577 ]
                LogOp_Get TBL: X_DETAIL_1(alias TBL: d1) [ Card=70 ]
                LogOp_Get TBL: X_DETAIL_LINK(alias TBL: lnk) [ Card=47 ]
            LogOp_Get TBL: X_DETAIL_2(alias TBL: d2) X_DETAIL_2 [ Card=119 ]
        LogOp_Get TBL: X_DETAIL_3(alias TBL: d3) X_DETAIL_3 [ Card=281 ]
    LogOp_Get TBL: X_LAST_TABLE(alias TBL: lst) X_LAST_TABLE [ Card=94025 ]

Note the faulty final estimate is already in place. It is printed as Card=4.52803e+009 and stored internally as the double precision floating point value 4.5280277425e+9 (4528027742.5 in decimal).
The derived table in the original query has been removed, and projections normalized. A SQL representation of the tree on which initial cardinality and selectivity estimation was performed is:
SELECT
    PRIMARY_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID)
FROM X_DRIVING_TABLE dt
LEFT OUTER JOIN X_DETAIL_1 d1
    ON dt.ID = d1.ID
LEFT OUTER JOIN X_DETAIL_LINK lnk
    ON d1.LINK_ID = lnk.LINK_ID
LEFT OUTER JOIN X_DETAIL_2 d2
    ON dt.ID = d2.ID
LEFT OUTER JOIN X_DETAIL_3 d3
    ON dt.ID = d3.ID
INNER JOIN X_LAST_TABLE lst
    ON lst.JOIN_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID)

(As a aside, the repeated COALESCE is also present in the final plan - once in the final Compute Scalar, and once on the inner side of the inner join).
Notice the final join. This inner join is (by definition) the cartesian product of X_LAST_TABLE and the preceding join output, with a selection (join predicate) of lst.JOIN_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) applied. The cardinality of the cartesian product is simply 481577 * 94025 = 45280277425.
To that, we need to determine and apply the selectivity of the predicate. The combination of the opaque expanded COALESCE tree (in terms of UNION and IIF, remember) together with the impact on key information, derived histograms and frequencies of the earlier "unusual" mostly-redundant many-to-many outer join combined means the CE is unable to derive an acceptable estimate in any of the normal ways.
As a result, it enters the Guess Logic. The guess logic is moderately complex, with layers of "educated" guess and "not-so educated" guess algorithms tried. If no better basis for a guess is found, the model uses a guess of last resort, which for an equality comparison is: sqllang!x_Selectivity_Equal = fixed 0.1 selectivity (10% guess):

-- the moment of doom
movsd xmm0,mmword ptr [sqllang!x_Selectivity_Equal

The result is 0.1 selectivity on the cartesian product: 481577 * 94025 * 0.1 = 4528027742.5 (~4.52803e+009) as mentioned before.
Rewrites
When the problematic join is commented out, a better estimate is produced because the fixed-selectivity "guess of last resort" is avoided (key information is retained by the 1-M joins). The quality of the estimate is still low-confidence, because a COALESCE join predicate is not at all CE-friendly. The revised estimate does at least look more reasonable to humans, I suppose.
When the query is written with the outer join to X_DETAIL_LINK placed last, heuristic reorder is able to swap it with the final inner join to X_LAST_TABLE. Putting the inner join right next to the problem outer join gives the limited abilities of early reorder the opportunity to improve the final estimate, since the effects of the mostly-redundant "unusual" many-to-many outer join come after the tricky selectivity estimation for COALESCE. Again, the estimates are little better than fixed guesses, and probably would not stand up to determined cross-examination in a court of law.
Reordering a mixture of inner and outer joins is hard and time-consuming (even stage 2 full optimization only attempts a limited subset of theoretical moves).
The nested ISNULL suggested in Max Vernon's answer manages to avoid the bail-out fixed guess, but the final estimate is an improbable zero rows (uplifted to one row for decency). This might as well be a fixed guess of 1 row, for all the statistical basis the calculation has.

I would expect a join cardinality estimate between 0 and 481577 rows.

This is a reasonable expectation, even if one accepts that cardinality estimation can occur at different times (during cost-based optimization) on physically different, but logically and semantically identical subtrees - with the final plan being a sort of stitched-together best of the best (per memo group). The lack of a plan-wide consistency guarantee does not mean that an individual join should be able to flout respectability, I get that.
On the other hand, if we end up at the guess of last resort, hope is already lost, so why bother. We tried all the tricks we knew, and gave up. If nothing else, the wild final estimate is a great warning sign that not everything went well inside the CE during the compilation and optimization of this query.
When I tried the MCVE, the 120+ CE produced a zero (= 1) row final estimate (like the nested ISNULL) for the original query, which is just as unacceptable to my way of thinking.
The real solution probably involves a design change, to allow simple equi-joins without COALESCE or ISNULL, and ideally foreign keys & other constraints useful for query compilation.


If PK is Non-Clustered index, how to identify clustered index candidate?

that GUID primary key should be Non Clustered always

This is not necessarily true. Indexing over effectively random data, which most GUID/UUID values are, can lead to excess page splitting and fragmentation which can be a concern for rapidly growing data. Depending on how your data grows, simple index maintenance run regularly (but not too often) my alleviate all of that practically, or it may not be a significant problem for your data at all anyway.

If you are generating the GUIDs in the database using a default constraint, and you aren't using UUIDs instead of smaller values (such as integers) for your keys for privacy reasons, you could instead use NEWSEQUENTIALID() instead of NEWID() in your defaults to reduce the amount of randomness such that it is insignificant.

If you are generating your GUIDs in your application, then to get the same effect you could use UuidCreateSequential or equivalent (many UUID related libraries wrap this function, it is the function that NEWSEQUENTIALID() itself is based upon. It is possible, though requires an inefficient little bit or hackery, to use NEWSEQUENTIALID() in a stored procedure or trigger too (though you can't use it in functions).


Raid 5 is slower than raid 10, especially for write-heavy workloads.  As such, it is not generally recommended for SQL server and certainly not for tempdb.  This alone could easily explain the performance difference.

My recommendation would be to move tempdb to raid 10.


MySQL, by default, only allows 151 connections (plus one more for a super user).

In your MATLAB output, you show 149 then 150 threads.  I presume that the majority of those are connecting to the database.  150 MATLAB threads will generate 150 connections.

There are few ways you could try to fix this.

One possibility is to limit the number of threads in MATLAB.  You can do that using the maxNumberCompThreads function.  Note that this is only available in R2007b through R2010a (and it will give you warning as of R2009b).  Alternatively, the -singleCompThread option will work as well, but limits you to one thread.
Try to limit the time that you have a connection open to the absolute shortest interval possible:  Open the connection, process the database work, close the connection.  This may work, depending upon how much other (non-database) work each thread is doing.
Finally, you could increase the number of connections allowed to the database.  You will need to change the max_connections server option.  Just use the command  "SET MAX_CONNECTIONS = 500".


How do I safely generate scripts for an existing MS SQL instance?

Look into dbatools (which uses PowerShell to do everything imaginable)

simplifying disaster recovery with dbatools – dbatools

getting started – dbatools

command index – dbatools

As for dummy data - do you need to only copy schema/objects, or data inside as well and scrub/randomize?


I think this query will work for you :

select
  order_item_id,
  order_id,
  invoice_id,
  invoice_type
from your_table
where order_item_id in
(select order_item_id from your_table where invoice_type = 'final' group by order_item_id)


Querying True Physical Database File Sizes

Here you go.  You can use this to get file sizes by file and filegroup.  It includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well.  It includes the full path of the file being evaluated.

SELECT fg.data_space_id AS FGID,
   (f.file_id) AS File_Id,
   -- As provided by OP, size on disk in bytes.
   CAST(f.size AS FLOAT) * 8.00 * 1024 AS Size_On_Disk_Bytes,
   ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
   ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
   ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
   ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
   f.name,
   f.physical_name
FROM sys.database_files f
    LEFT JOIN sys.filegroups fg
    ON f.data_space_id = fg.data_space_id

For more information on the difference between 'Size_On_Disk_Bytes' and 'Actual_File_Size', go here: https://superuser.com/questions/66825/what-is-the-difference-between-size-and-size-on-disk

Thanks


Required hardware to build a development environment in Sql Server

Ideally, you'd have the same configuration in development as you do in production.  That way, you can have a higher probability that if it's slow in dev, it will be slow in prod, and if it's fast in dev, it will be fast in prod.  I'm guessing that this would be a pretty significant drop from what you have in production.  But given that few places have the budget to have dev match prod, I understand dealing with what you have.

With 10 instances, the part I'd be concerned about is the RAM.  Figuring that somewhere around  4-6 GB should be held back for the OS, any other software on that server (A/V, etc.), and an emergency buffer, this means you have about 26-28 GB of RAM for 10 instances, or under 3 per instance.  I don't know what you're averaging now, but it's probably a good bit more.

As such, I'd try to max out that server's RAM and set the maximum memory per instance equal to what it is in production.  Even though there won't be many people using the server, skimping on RAM will give you false negatives with performance and will make it harder for developers to get accurate performance tests.

Unless you have dozens of developers constantly going at the databases, you'll probably find CPU to be adequate (though not really good).  The low disk speed is a bit concerning, but get enough RAM and the disk speed shouldn't be too big of an issue for a development server.  Again, you'll likely find additional performance-related problems in dev that you won't in prod (if you have more CPUs per server and faster disks and more spindles available per server), but when you're on a budget, those are the trade-offs you have to make sometimes.

Edit

I would be totally remiss if I didn't point out Glenn Berry's book on the topic.


This is a dump file produced by pg_dump -Fc.

To create a text file containing SQL commands from it, use pg_restore.

The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).


Specifying MySQL user hosts with IPv6 addresses

(Sorry, I don't have definite answers, but these seem likely:)

:: is shorthand for any number of 0000 segments so as to end up with 8 groups.  So, ::% is ill-defined.

% is the wildcard available in MySQL, not _.

Perhaps % is only applicable for a 16-bit chunk, not part of such.

With ipv4, a mask is given using /.  Example:  198.51.100.0/255.255.255.0: Any host on the 198.51.100 class C network.  I suspect that ipv6 would be similar.  -- https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/account-names.html

So, I would guess

2001:DB8:111:6600::/FFFF:FFFF:FFFF:FF00::


Which MySQL files in InnoDB are made of pages and how are they structred?

With innodb_file_per_table = ON and 5.7 or older:

Each DATABASE is manifested on disk as a directory (with the database name).
In that directory are a few files for each TABLE in that Database.
The .frm file contains (effectively) the schema for the table, and essentially nothing else.
The .ibd file contains one or more B+Tree.  One B+Tree contains the data, organized by the PRIMARY KEY.  Each secondary key lives in a similarly structured B+Tree, but organized by the key, and containing a copy of the PK in the leaf nodes.

Meanwhile, there is some meta information about the table in the common ibdata1 file.  (This implies that moving the .ibd file by itself will screw up the integrity of the database.)

File_per_table=OFF and "tablespaces" are variants on the above.  In the old days, before .ibd files, all tables were in ibdata1.

MySQL 8 moves a lot of the meta info, plus the schema, into the "Data Dictionary".  This is implemented using InnoDB.  (I cringe at the bootstrap issues.)

To find the row uniquely identified by PRIMARY KEY = 1234, here is what happens:

If not already opened, the table is "opened" and put in a cache of open tables.  Some information from the .frm (or DD) is copied there.  The .ibd file is located (or the table is found in ibdata1), etc.
Locate the PK in the table.  Get its 'root' node.
Drill down that B+Tree until you find the 'row' in the leaf node (aka page, aka block) that has id=1234.  On average, there will be 100 rows in that block, some before 1234, some after.  So, in a million-row table, the "drill-down" might hit 3 blocks.  Since blocks are cached in the buffer_pool, this involves between 0 and 3 disk hits (usually toward the 0 end).
Deliver all the columns (or whatever is requested).

Note:  If the lookup is by a secondary key (that is, not 'clustered'), there are more steps.

Also contained in each data block are a few things to facilitate traversing the rows and the blocks.  (Think the + in B+Tree; read Wikipedia if necessary.)

Within each row in the block there are other things:

Fields, and their lengths, possibly pointers to off-record large chunks for TEXT and BLOB.  (This gets around the 8K pseudo-limit on a row.)
Transaction ids for MVCC
A "history" of the revisions (again think MVCC) of each row that has been modified, but has not yet been commited/resolved (think ROLLBACK and transaction_isolation).  The Undo log is also involved in these things.

Each block anywhere in the running MySQL system is uniquely identified identified by a rather large number.  Part of it the 'tablespace' (ibdata1, foo.ibd, etc.)  Part of it is the block number.

("Tuples" is a rather general term; I don't know what you are referring to here.)


Does the Query Optimizer Prefer to Query on Constants before Columns?

Assuming there are no other queries of concern, the general answer is that you want more selective criteria in the leading columns of the index (note that selectivity here is in relation to the predicate rather than a measure of how unique the particular values in the column are).  In general, you want the optimizer to eliminate as many rows as possible as quickly as possible.

Assuming that CustomerID in Shipment is NOT NULL and has a foreign key to Customer, meaning that the inner join is guaranteed not to eliminate any rows from Supplier, then s.IsShipped = 1 is the only selective predicate and it would make sense for it to be the leading column.  If, on the other hand, the inner join was more selective (imagine that someone moved rows from Customer to Customer_Archive periodically and Shipment could join to either of those tables), it would make sense to have CustomerID as the leading column.


MySQL: Is it safe to have a table with multiple millions of rows?

If your data can be pre-calculated and is static intra-day then why not?
After all, isn't this what a data warehouse does?

As for having up to 50 million rows, this is peanuts.


OK.. It's look like that you have managed to mix things. So, you have working mongodb at "C:\program files\mongodb\data" and now you want to convert that mongodb to three node replica set?!?
First after you have start that mongod process with --replSet replicaSetMGL parameter. Log in (with mongo -program) and give command rs.initiate().

Then remove all files from directory "C:\Program Files\MongoDB\replicaSet1" (because I can see from log file that there is mixed db files; mmapv1 and wiredTiger). Start that node with those parameters.

Go back to first nodes mongo and give command rs.add("localhost:27018") to add that node to replica set. With command rs.status() you can see your replica sets status.

Repeat those steps to add third node to RS.


Hint to force locks between 2 Select clauses

Have you considered using BEGIN TRANS ... COMMIT TRANS to encapsulate your data?

You can combine that with the WITH (rowlock) syntax:

Begin Transaction Queue1
Begin Try
  update [...]

  Select [...] From [...]
  With (RowLock)

  Commit Transaction Queue1
End Try
Begin Catch
  Rollback Transaction Queue1
End Catch

Select With clause information here.

Exhaustive discussion on SQL Server locking strategies here.


Should I update to sql server 2008 sp2 before restoring the db's or do the update after?

That does not matter. When you upgrade an existing instance with a database attached SQL Server automatically upgrades the database too. If you attach the database after the upgrade, it goes through the same process.

In either case make sure that the database is at the right compatibility level. While usually nothing happens, you might run into problems if the database is still at 80. Changing the compatibility level however will change behavior, so make sure you test you application well.


Fuzzystrmatch Postgresql similarity func with array

The similarity function does not take in array as argument so it will not do what you want out of the box

You have a couple of options create a wrapper function that takes in array that the calls Similarity and returns a value to compare against.

2nd option is do a cross join to the array like so

SELECT denomination, is_active , unnest
    FROM mytable  cross join (
        select * from
        unnest(array['coke', 'coqe'])
    ) dd
    where SIMILARITY(denomination, unnest) > 0.4;


Saving SSIS package from server to local computer

TECHNICALLY, it can be done through the SaveToXML method. However, for this to work, the process on the SQL Server has to have write access to a share on your destination machine, and you have to specify the share when you give the filename.

Other than that, I can't see a way to do what you are looking for. I was hoping that there was some way to get the package into an object in memory that you could manipulate - which, again technically, I guess you could do by saving the package to XML, then reading it from disk into an object, then saving the object locally. Neither of these options are really what you are looking for, but I believe that they are your only real options available.


This solution applies when you have repeated data in the CSV and you want to control what to do in every case. Since, there is nothing in your question about the following fields, I'm not including any logic for them:

customer_id INT,
in_use     BIT

0 - create the tables you already declared in your question

1 - create a t_Bulk table having the exact structure of your cvs and execute the bulk copy

CREATE TABLE T_BULK (
Customer_Name VARCHAR(100),
Contact_First_Name VARCHAR(50),
Contact_Last_Name VARCHAR(50),
Phone1 VARCHAR(15),
Phone2 VARCHAR(15),
Fax VARCHAR(15)
);
GO

2 - With the T_BULK table already full, let's process each record:

DECLARE @ContactId int, @FirstName varchar(50), @LastName varchar(50), @Phone1 varchar(50), @Phone2 varchar(50), @Fax varchar(50)

    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT Contact_First_Name, Contact_Last_Name, Phone1, Phone2, Fax
    FROM dbo.T_BULK

    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax
    WHILE @@FETCH_STATUS = 0
    BEGIN

    //if the contact already exists we get the identity
    IF EXISTS (select contact_id from customer_contact where first_name=@FirstName and last_name=@LastName)
        select @ContactId = contact_id from customer_contact where first_name=@FirstName and last_name=@LastName

    //if the contact does not exit we need to insert and retrieve the new Identity value
    ELSE
        BEGIN
            insert customer_contact (first_name,last_name) values (@FirstName,@LastName)
            set @ContactId = SCOPE_IDENTITY()
        END

    //now we continue with the phones and fax
    //insert only new phones
    INSERT contact_phone (contact_id,phone_num)
    SELECT Id, Phone
    FROM (select @ContactId as Id, @Phone1 as Phone UNION select @ContactId ,@Phone2) P
    WHERE NOT EXISTS (SELECT * FROM contact_phone C WHERE C.contact_id = P.Id and C.phone_num = P.Phone);

    //insert only new fax
    INSERT contact_fax (contact_id,fax_num)
    SELECT Id, fax
    FROM (select @ContactId as Id, @Fax as fax) F
    WHERE NOT EXISTS (SELECT * FROM contact_fax C WHERE C.contact_id = F.Id and C.fax_num = F.fax);

        FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax
    END

    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR

Good luck

In order to include an additional field:

in the declare zone include an aditional variable:
@Extension.

which you must include in both FETCH NEXT lines. Then you need to include that variable when inserting the phones, but in this case you need to check that phone2 is not equeal to phone1, just to ensure you don't have repeated phones:

    (select @contactId as Id, @Phone1 as Phone, @Extension as Extension
UNION select @ContactId, @Phone2, null where @Phone2 <>@Phone1) P


There are some Open Source Change Data Capture tools that support MySQL you can find some of them in this answer: Does MySQL have a version of Change Data Capture?

I would personally recommend option #2 if you choose to deal with it yourself. A few simple triggers can automatically copy the new/modified rows into the other table and you can add a few columns in the history table to help track what is going on without having to touch any of the queries that use the primary table. This solution is extensible and is easy to deal with from a querying point of view.


Database transaction log growth who is the cuplrit?

Are you running transaction log backups?

If so, you may need to increase their frequency.
If not, you need to either start taking them, or switch to SIMPLE recovery mode.

If you are taking frequent tran log backups, and the logs are still growing, you might have a problem with bad application code, if they never properly commit/close transactions. In that case have your developers (or the vendor) fix the app.

You can use a query like this to find all open transactions:

Select st.session_id, st.is_user_transaction, st.is_local,
    at.transaction_type, at.transaction_state,
    sp.dbid, db_name(sp.dbid) as dbname, sp.cpu, sp.physical_io,
    sp.login_time, sp.last_batch, sp.open_tran, sp.status,
    rtrim(sp.hostname) as hostname, rtrim(sp.program_name) as program,
    rtrim(sp.cmd) as command, rtrim(sp.loginame) as loginname
from sys.dm_tran_active_transactions at
INNER JOIN sys.dm_tran_session_transactions st
    ON at.transaction_id = st.transaction_id
INNER JOIN sys.sysprocesses sp ON st.session_id = sp.spid
ORDER BY at.transaction_begin_time ASC

EDIT: Some further thoughts, based on your comments:

Make sure you are using a fixed growth size, for a large database I usually use 2000MB, 4000MB, or 8000MB, depending on what I expect the total size to be.
Follow Kim's instructions here to shrink and then re-grow the log, so you have reasonably-sized VLFs.
Is this database involved in replication? Was it ever?
Is rebooting the server the only time the log clears? Do tran log backups clear it?
When was the last full backup? You need a proper full to "restart" the log backup chain.

Run the following query to distinguish between used and free space within the log file, and monitor it throughout the day:

 SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;


Why does SQL Developer update Oracle Data Dictionaries while SQL*Plus does not?

SQL Developer automatically sets the below:

alter session set plscope_settings='IDENTIFIERS:ALL';

You can use the same in SQL*Plus.

(The default is IDENTIFIERS:NONE.)


Designing a database for a business context involving orders, payment methods and shipping addresses

So you have several tables each of which holds an address. You're wondering if these common address-related columns should be in their own table.

The typical answer is "yes" - a separate would be a good idea. There are any number of posts on this subject on this site.

If you do make a common address table will you compare user input to existing rows so you can re-use an existing address_id in one of your tables? If the answer is "no", or you don't have a plan to handle small variations in how the user spells or formats the address, then why bother?


Minimum SQL Server rights that allow viewing column default values

This will allow the defaults to show up in the lower pane of the table designer:

GRANT VIEW DEFINITION ON dbo.tablename TO [username];

Of course this doesn't give them the ability to make any changes to the table, and in general I discourage the use of the designers anyway. They're littered with bugs and inconsistencies. With VIEW DEFINITION rights, your user can also pull this information from the catalog views:

SELECT
  [constraint] = d.name,
  [column] = c.name,
  [default] = d.definition
FROM sys.default_constraints AS d
  INNER JOIN sys.tables AS t
  ON d.parent_object_id = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE s.name = N'dbo'
    AND t.name = N'tablename';

  Is there a way to do something similar for whole tables of database? I mean, about the GRANT VIEW DEFINITION ON dbo.tablename TO [username];.

Sure, dynamic SQL is very handy here.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N''GRANT VIEW DEFINITION ON '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
  + N' TO [username];' + CHAR(13) + CHAR(10)
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id];

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


Storing small array of many possibilities

The MySQL SET datatype is a disaster - as are any type of array datatypes (supported unfortunately by many RDBMSs). Check out my answer to another question here. As I note, MySQLs SET is a breach of Codd's second rule - no repeating group datatypes. It is also completely non-portable should you wish to change RDBMS.

If you wish to store that data this way is to have a BOOK table with book_id, name, author, country_of_publication, subject.... Then have a CHAPTER table with book_id, chapter_id, chapter_name, chapter_sub_subject.... and then a PAGE chapter with book_id, chapter_id, page_id, pager_number, ... images... other stuff... then LINE book_id, chapter_id, page_id, line_id, line_text...

This conforms to the relational model. I'm not clear on why you'd want to do this. What's wrong with TEXT datatype? Also, you might want to look into FTS (Full Text Search) - MySQL's native FTS (available on both MyISAM and InnoDB now). There's also Lucene, Sphinx and Spider (there may well be others - Google is your friend).

[EDIT in response to OP's comment]

  I had the feeling it was wrong to make identically-structured tables
  for each book

The whole point is that you should have identically structured tables for all books - you make the structure general enough to cope with (virtually) every possibility.

  but I also though it would be slower to search through larger table
  (containing ALL sentences not just 1book's worth) to (re)construct a
  page, as well as doing an extra WHERE book_id = 'N' match...

Use indexes on book_id, chapter_id and maybe even page_id. Also, if you're going to be searching in your entire corpus for a particular word, then condsider Full Text Indexing.

  Presumably this extra overhead is near-identical to me first sending
  mysql to the single-book table. This leaves me with the query of how
  to store a small array for use in PHP --- I'm leaning towards VARCHAR
  with (un)serialize.

Be aware that the maximum size of VARHCHAR is 65535 bytes - some of James Joyce's sentences might go beyond that :-), but it's unlikely that even Joyce went beyond the 4GB of a TEXT datatype :-)

I Googled "storing books in MySQL" and came up with this which you may find of interest, and interestingly up popped this on the first page of the same search.


SQL Server : How to make "Update Statistics" faster?

I would start by seeing if you have any statistics that aren't being used.  Unfortunately that isn't possible directly but you could start by looking at your indexes and seeing if any of them are worth getting rid of.  If you can eliminate some of your indexes and/or statistics that will improve your run time for the UPDATE STATISTICS.  You can find out which indexes are actually in use by looking at sys.dm_db_index_usage_stats.

Here are a couple of links I used for reference although you can do a quick google search and find dozens.

SQL SERVER – Identifying Statistics Used by Query
SQL SERVER – 2008 – Find If Index is Being Used in Database

Also as a general suggestion if you partition your table using a "partitioned view" so you have "old data" and "new data" then you can schedule your statistcs updates separetly.  For example let's say you partition your data by year.  You run the daily UPDATE STATISTICS on just the current years partition.  If there are no updates on previous years data then you don't have to worry about statistics updates on those partitions at all.  If there are updates on previous years data you can either

Let them update themselves using the auto update functionality or
Update them say once a week
And/or update them during an off hour time.