Can I connect to the database as the user "dbo"?

The first part to understand is the difference between a User and a Login in SQL Server. This article provides a brief rundown to understand the difference, but to put it simply, a Login is a server-level principal that provides authentication and authorisation for server-level access. A User is a database-level principal that provides authorisation for database-level access.

There are many scenarios, but typically a Login is mapped to one or more Users, but only one User per database. A User is only ever mapped to one Login (except for contained database users, but that is a different scenario that can be ignored for now).

The dbo User is a built-in database user in every single database that represents the Database Owner. This user has full unrestricted access to the database. While you cannot login to a server as "dbo", you can login with a login that is mapped to the dbo user in one or more databases. When the database context of the connection is a database where that login is mapped to dbo, then the identity in that context is as "dbo".

In addition, as per comments, any login that has the sysadmin role will appear as dbo in every database as well, even if they're not explicitly set as the database owner.

To answer your specific questions:

  During security audit we are being asked if the dbo login is
  interactive. My understanding is that the account is automatically
  created and managed by SQL server itself and is not directly
  interactive.

Yes, the dbo account is built-in and cannot be deleted, locked, disabled or removed. It also cannot be logged into directly, i.e. you cannot login as user "dbo" with a password.

  I have tried to the demonstrate that a sysadmin users would appear in
  SESSION_USER as dbo when authenticating but they're not fully
  convinced. Their question is now if it is possible to directly
  authenticate to dbo by logging in with its password.

  Is this possible? I don't even know where I would find dbo's password if it has one.

No, it is not possible. There is no "password" that exists for dbo. There may be a password for the mapped Login if it is a SQL Authentication login, but no direct login for dbo.

This article provides an explanation of dbo and also some useful queries for identifying your logins that are mapped to dbo in each DB. I would suggest you advise the auditors that it is not possible in SQL Server to login directly as dbo, nor can you adjust any permissions or disable/remove this account, then provide them the list of mappings and a list of sysadmins or logins with CONTROL SERVER permissions to illustrate who can assume the dbo user context in each database.


Unless otherwise specified, MySQL uses Port 3306.  That can be changed in various config files, CHANGE MASTER, startup command, etc.


This is a great example of how the Window Functions can be used. Based on your data, I'm assuming you want to do this for every unique LINE_NO. I'll leave it to you to format the percentage column.

SELECT      LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY,
            COUNT(*) AS CT,
            COUNT(*) / SUM(COUNT(*) * 1.0) OVER (PARTITION BY LINE_NO)

FROM        TX_HSA_SUMM

GROUP BY    LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY


how to deal with mysql 500 million data per month?

Plan A:  ENGINE=InnoDB, with these specific suggestions:

Since you are purging after 6 months, I recommend PARTITION BY RANGE (TO_DAYS(...)) with weekly partitions.  Every week, drop the oldest and REORGANIZE "future" into "next week" and "future".  See Details.

Partitioning by day leads to so many partitions that it is inefficient.  And, dropping a week's worth of data is just as easy as a day's worth.

a_vlad is right that partitioning does not necessarily help with queries.  But it is excellent for that time-based purge.

200M * 300K is more like 2T.  Where is the math error?  Perhaps 300B?  Are you implying a fixed-length row?  Don't.

300KB for one record seems terribly big -- what are you keeping in it?  Seriously consider normalizing out any repetitive columns.

Provide a tentative SHOW CREATE TABLE and the main queries (insert/update/delete).  It is unclear that there need to be any updates or deletes for your application.

166 writes/sec is pushing the limits of a spinning drive.  Either use hardware RAID-5 with a Battery Backed Write Cache or use SSDs.

I suggest you adapt the suggestions in my High speed ingestion blog.  That discusses a technique for getting well past 166 writes/sec.  (Keep in mind that you will have peak loads that are much higher than 166.)

Plan B: NDB Cluster.  (Sorry, I don't have specific suggestions.  But NDB was designed for Telecom.)

Redis?  That adds another moving part -- more things to go wrong.  And, assuming that your traffic is reasonably consistent, there is little to be gained by any buffering scheme.

The MySQL box should be separate from the Client box(es).  You should design the client boxes (web servers?) to be scalable -- any number of them, add more as needed.


Is my table is stored as heap or b-tree?

If your table has a clustered index it is stored as a b-tree otherwise it is a heap.


Fastest query for selecting arrays that contain duplicates

Among the fastest ways to run such a query is with a specialized index:

create index on user_info ((anyarray_uniq(phones) <> phones)) where
    anyarray_uniq(phones) <> phones;

That way the work of doing the comparisons happens when the records are inserted or updated, not when they are selected.  (To get this to work, you will have to mark as anyarray_uniq as IMMUTABLE, but as far as I can tell this is an accurate way to mark it)


MySQL consumes 64% is not a very big deal if the load is very high and the memory is low on the machine. Typically I would say its better that it should not consume more than 20 to 25% of the total memory available.

Anyways for your question, just try to find out the bottlenecks.
1 You should be able to find out if there are any long running queries - Say for example you are fetching some 1 million rows or so in a InnoDB engine (where transaction is supported) and your query has lot of joins.
2 Then you can tweak the actual config file of MySQL to increase the number of connections that is allowed and the max memory that is assigned to MySQL.
3 Also make sure you close all the sql connections in your application if you have not done it.
4 If long running queries are the bottleneck, then you can change some of the tables to MyIsam if it doesn't matter with transactions.

I hope it helps !


Killing a process without login to MySQL

For cases like this, there is "one extra connection available".  Anyone with SUPER priv (eg. "root") can get in to kill the DROP, which would have uncorked the mess.

However, a prerequisite for doing that is to not have users and applications log in as "root".  Instead they should have their own logins, preferable (for security reasons) limited to the database that they need access to.

Reserve "root" for 'admin' actions such as the one you needed in this case.


Monitoring Maintenance Tasks in AlwaysOn Clusters

SQL Server Maintenance Plans do not have in-built functionality to detect the AG replica state for a particular DB and act accordingly. To do this, you must implement custom functionality.

Depending on how you're implementing your maintenance tasks, there are a few options for this.

Option #1:

If you're using SQL Maintenance Plans, then create one set of maintenance plans per availability group and target only the DBs in that group. Remove the schedules for these plans and then create a SQL Agent job that runs on the schedule you want. Have that Agent job check sys.dm_hadr_availability_replica_states to confirm the AG in question is in the primary state and if so, execute the appropriate Agent jobs to run the maintenance plan tasks.

Option #2:

If you're using SQL Agent jobs with T-SQL scripts to perform your maintenance tasks, add in some logic to your scripts to check sys.dm_hadr_database_replica_states prior to executing to validate that the database in question is indeed the primary.

Option #3:

Redesign your maintenance processes to utilise Ola Hallengren's excellent maintenance solution. This has the logic for handling Availability Groups already built in, so this saves you time in designing, testing and deploying a solution yourself. You simply deploy an identical set of Agent jobs to run the stored procedures on every replica. They will all execute, but only the ones you want to perform any work will do so.

Notes:

Some tasks in Ola's solution run on all replicas all the time, such as Integrity Checks and server-level maintenance (log cycling etc). These should be run on all replicas (yes, even integrity checks), so you shouldn't be concerned about this.
Ola's solution checks the preferred backup replica using sys.fn_hadr_backup_is_preferred_replica before backing up, so make sure your backup preferences are correctly set on your AGs.
Maintenance Plans, in general, are not recommended by most DBAs because of the inflexibility they often impose. T-SQL or PowerShell based solutions are usually preferred as they're much more robust.
Options #2 and #3 are essentially the same, but option #3 means someone else has done the hard work for you. Additionally, the widespread use of this solution means there is lots of help and advice in the SQL community if you run into issues.

Any of these options should be deployed in a fashion where the same jobs are deployed on all replicas and run at the same time. The only differences being that some tasks are only performed on the primary replica, while others just run the job and exit quietly performing no work because of the replica state.

Your management team may not like the idea of refactoring the current maintenance processes, but the reality is they have to if they want to automate this to handle failover, otherwise, the maintenance tasks have to be managed manually post-failover.


This job can be done using the BCP utility.

Pre-requisites:

You would need to configure your server, to enable xp_cmdshell:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Permissions, which account xp_cmdshell is running as?

xp_cmdshell 'whoami'

Create a new folder, where you will save the files, and grant Full access on this folder to the user of step 2.

I recommend you first try with a few tables, add a TOP criteria to the cursor selection, just to verify this script runs well in your server.

select TOP 3 TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.Tables
where TABLE_TYPE = 'BASE TABLE';

Basically the script gets a list of tables from INFORMATION_SCHEMA, and executes the BCP utility for each element in the list.

XML files will be named as: DATABASE_SCHEMA_TABLE.XML

This is the full script, before to run it, replace the enclosed <...> values according to your system configuration:

use <your_database>
go

declare @ServerInstance nvarchar(50),
        @Database sysname,
        @Schema sysname,
        @Table sysname,
        @RootFolder nvarchar(165),
        @BcpParams nvarchar(100),
        @cmdBCP nvarchar(500),
        @FQI varchar(600),
        @FileName varchar(600),
        @retExec int;

    set @ServerInstance = '<server\instance>';
    set @BcpParams = '-t -T -w';
    set @RootFolder = '<folder_name>';

    declare curXml cursor fast_forward for
        select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
        from INFORMATION_SCHEMA.Tables
        where TABLE_TYPE = 'BASE TABLE';

    open curXml;
    fetch next from curXml into @Database, @Schema, @Table

    while @@FETCH_STATUS = 0
    begin

        set @FQI = @Database + '.' + @Schema + '.' + @Table;
        set @FileName = @RootFolder + @Database + '_' + @Schema + '_' + @Table + '.xml';

        select @cmdBCP = ' bcp "SELECT * FROM '
                         + @FQI
                         + ' row FOR XML AUTO, ROOT(''' + @Table + '''), elements"'
                         + ' queryout "' + @FileName + '" '
                         + @BcpParams
                         + ' -S ' + @ServerInstance;

        print @cmdBCP;

        EXEC @retExec = xp_cmdshell @cmdBCP;

        if @retExec <> 0
        begin
            close curXml;
            deallocate curXml;
            raiserror('BCP Error', 16, 1);
        end

        fetch next from curXml into @Database, @Schema, @Table;
    end

    close curXml;
    deallocate curXml;


How to store chess positions in a relational database (in particular, Postgres)

If you want to hold the positions as text that's fine. But there are better text editors than Postgres. Holding each game as a file and each position as a row within that file, in FEN notation, would be a good approach. The files can be read and searched using the tools of your choice (grep; Python). Expanding the spaces as Daniel Vérité suggests would make life easier.

If you really want to use a DBMS Elasticsearch has a good reputation for finding patterns within textual data.

If you really want to use a relational database, however, I'd suggest designing a data structure using relations (i.e. tables) rather than relying on SQL's poorer text parsing capabilities.

One must make the mental step from the rules of chess as a game to those of relational schema design. In the latter we define entity types, and facts that are known about them. The entity types can be actual real-world items, concepts or events. There are many possible ontologies for any given problem. Specifically we must separate the database internal representation of information and how that information is formatted for human consumption. I do not present my solution as "correct" in an absolutist sense, only that it comes from a relational perspective on data modelling. Other equally relational approaches are possible.

What tables are required? Most obviously there's Game:

Game
  GameId        int  primary key
  WhitePlayer   varchar(50)
  BlackPlayer   varchar(50)
  .. others

I'd model each piece as a distinct row. Pawn 1 will be distinct from pawn 2 and so forth. I still need to record colour and type to reproduce a position. When a pawn is promoted the new piece must have an PieceId distinct from those pieces present at the start of a game. This is required by how I re-create a position below.

Piece
  PieceId    smallint  primary key
  Type       char(1)              -- (P)awn, (R)ook, (K)ing etc.
  Colour     char(1)              -- (W)hite, (B)lack;

Finally there will be a table to store moves. The choice here is between storing a move (like PGN) or storing a whole position (like FEN). Storing the positions introduces much redundant data - all the blank squares and the pieces which did not move on this turn. So I would store the moves individually.

Move
  MoveId       int
  GameId       int foreign key referencing Game.GameId
  PieceId      int foreign key referencing Piece.PieceId
  ToRow        char(1)
  ToColumn     smallint
  IsRemoved    boolean           -- set when a piece is taken from the board

  primary key (MoveId, PieceId)

The pair (ToRow, ToColumn) is where the piece ends up after a move. I'm thinking of the a1-h8 format. I do not need FromRow/ FromColumn. If we need this it can be recreated from PieceId and the history of moves. Neither will I check that the rules of chess are obeyed. That is a job for a different tier of the application.

Some chess moves will result in two rows in this table. For example during capture there will be one row for the taking piece and another for the taken. The latter will have IsRemoved set.

Each game starts with the same 32 rows in Move for the initial positions of the pieces. This may seem redundant but is required to reproduce any position. If not explicit it would have to be implied in each query as some pieces may not move during a game. This also allows for puzzle setup.

To reproduce a position we observe that each piece will be placed wherever it was most recently moved before that position. Take the following as pseudo-code; I've not tested it.

select p.GameId, p.MoveId, pc.Colour, pc.Type, q.ToRow, Q.ToColumn
from Move as p
inner join Move as q
   on q.GameId = p.GameId
   and q.MoveId <= p.MoveId
inner join Piece as pc
  on pc.PieceId = q.PieceId
where not exists
( select 1
  from Move as c
  where c.GameId = q.GameId
  and c.PieceId = q.PieceId
  and c.MoveId <= p.MoveId
  and c.MoveId >= q.MoveId
)
and not exists
( select 1
  from Move as d
  where d.GameId = q.GameId
  and d.PieceId = q.PieceId
  and d.MoveId <= p.MoveId
  and d.IsRemoved = True
)

Read this as for every move of every game (aliased as p) i.e. every position, find all moves in the same game which happened before this move (aliased as q). Omit those moves which have a subsequent move for the same piece in the current game, or which have been captured.

Once a position can be re-created as above, it is a simple extension to find positions where certain pieces (or types of pieces) sit in given configurations. It would be possible to structure the query to look for certain pieces in certain positions, then ensure they're in place at the same time, then retrieve the remaining pieces at that point. I would note that white pawns may occupy e4 and e5 for a large portion of a great many games so a lot of positions would satisfy this criteria.

Whether this is faster than the simple-but-dumb text approach only testing can tell.

This question sparked my interest. I wondered what a "relation-y" answer would look like, since an RDBMS was specified. I approached it somewhat as a Gedankenexperiment. Having worked through some queries, however, I'm not sure I have the ontology quite right. If the objective is to identify positions, I think positions are the objects that should be stored. This leads back to the OP's original suggestion. Yes, there is redundancy in the storage as most pieces do not change between positions. Practically speaking, though, at 64 bytes per position, say 50 moves per game, then a database of 10,000 games needs about 30MB. This is trivial. Although SQL is not great at text parsing it is certainly up to deciding if given offsets in a string contain specific values e.g. e4 and e5 hold white pawns. On balance I think the most practical implementation will be FEN with explicit spaces.


If you enable tracing for this specific error, a session trace file will be generated whenever it occurs. This will allow you to view the SQL that causes the error. You will probably need the cooperation of your DBA to do this, as it's likely only they will be able to activate the trace and access the trace dump directory.

Here's a link showing how to do this:

http://oracle.erkansaka.org/2011/09/how-to-trace-oracle-error.html

So for you that would be:

alter system set events '942 trace name errorstack level 1';


How to fix high memory usage of MariaDB?

It's not critical So don't worry It's a normal Behaviour of Innodb Engine.

If your data size is bigger than or equal the Buffer pool size, Innodb will fill all of buffer size at warm up time. So when you start MySQL after some hours buffer pool will full (Only Buffer pull Not all Ram's capacity)

You'are using multiply Engines in your system. It can complicate config, because you have to controll important variables (such as Memory Configs) of each Engine.

Do you have a reason to use the TokuDB? I think it's better to use one of these engines (InnoDB or TokuDB)

So if you prefer to use innodb, you can set buffer pool size to about 70% of RAM unless your data/index size is small


RETURN value directly from INSERT with RETURNING clause

You can simplify the whole function and use an OUT parameter, so the value is returned at the end of the function automatically:

CREATE OR REPLACE FUNCTION make_date_from_configuration(
          wdc_id uuid
        , from_date date
        , OUT ret_id int) AS
$func$
BEGIN
   INSERT INTO configuration_dates (
          weekly_date_configuration_id, "from", "to", price, activity_configuration_id)
   SELECT wdc_id
       , (from_date || ' ' || wdc.start_time)::timestamptz
       , (from_date || ' ' || wdc.start_time)::timestamptz + wdc.duration
       , wdc.price, wdc.activity_configuration_id
   FROM  weekly_date_configurations wdc
   WHERE wdc.id = wdc_id
   AND   wdc.valid_through @> from_date                        -- starts in the date range
   AND   get_bit(wdc.weekdays
               , EXTRACT(isodow FROM from_date)::int - 1) = 1  -- valid day of the week
   RETURNING id
   INTO  ret_id;  -- returned at the end automatically
END
$func$  LANGUAGE plpgsql;

All the variables and assignments and conditional expressions are just needless noise. Can be a single SQL command: shorter and faster.

You still need the INTO clause, though. (And I fail to see why you try to avoid that.)

Or you can use a plain SQL function instead, to return the value directly:

CREATE OR REPLACE FUNCTION make_date_from_configuration(wdc_id uuid, from_date date)
  RETURNS int AS
$func$
   INSERT INTO configuration_dates (
               weekly_date_configuration_id, "from", "to", price, activity_configuration_id )
   SELECT wdc_id
       , (from_date || ' ' || wdc.start_time)::timestamptz
       , (from_date || ' ' || wdc.start_time)::timestamptz + wdc.duration
       , wdc.price, wdc.activity_configuration_id
   FROM  weekly_date_configurations wdc
   WHERE wdc.id = wdc_id
   AND   wdc.valid_through @> from_date                      -- starts in the date range
   AND   get_bit(wdc.weekdays
               , EXTRACT(isodow FROM from_date)::int- 1) = 1 -- valid day of the week
   RETURNING id  -- return directly
$func$  LANGUAGE sql;


Upgrade to 5.5, then 5.6, then 5.7.  You will be able to run more simultaneous queries at each upgrade.  Do you really mean "5.1.1"?  That was a pre-release for 5.1 from about 2005.
Use summary tables for old, static, data.  (It is static after it is INSERTed, correct?)  Perhaps summarizing (eg AVG, MAX, MIN) daily readings can be done just after midnight?
Use InnoDB, not MyISAM.  Much better sharing of the table.  (And it gets better with each version upgrade.)
innodb_buffer_pool_size should be about 5500M for your 8G machine.  Is the dataset, including indexes, bigger than that?  If so, get more RAM and/or RAID with striping and/or SSDs.
And Summary tables are the main cure for Data Warehouse performance issues.  Further discussion .  It's not uncommon to speed up queries 10-fold, especially I/O-bound ones.

"How does one solve..." -- Most applications are not doing lots of 3.5-second queries.  They are doing 'point queries', which might be 3.5 milliseconds.


SSMA from oracle xe to sql server express PK violation

I figured it out. The difference was casing of the varchar PK. Oracle sees casing as different but SQL must not. There was some value with a capital 'S' and a lowercase 's' where everything else was the same. That's interesting.


How do MySQL 5.7 virtual columns differ from views?

Views and virtual columns are similar, in that neither are materialized.  i.e. neither store any data or indexes, hence why your index_length+data_length remain the same.

Where they differ is:

Virtual columns are created on base tables, and will co-exist with columns which will be material.  This can be useful as it has more of a mixing/matching behavior than views.
Virtual columns permit indexes (which are materialized).  Views do not.

As a side note, another commonly used feature with some overlap here is triggers.  It is often better to have a virtual column than a trigger updating legacy columns on insert/update.

In response to this specific question:

  How do you find the true footprint or overhead for using these?

There is no footprint, and you will likely not see overhead.  Sure, there are hypothetical cases: If you have many virtual columns and SELECT * from a client then more data will be sent across the network (since rows are sent in full; clients don't know they can reconstruct certain data from virtual column definitions).


When you want to add words involving special characters that you want treated as a single word, you will want to create a custom dictionary.

Creating Custom Dictionaries for special terms to be indexed ‘as-is’ in SQL Server 2008 Full-Text Indexes

Be aware that custom dictionaries are limited in size, plus the larger the dictionary the slower fulltext can become.

Custom dictionaries are great when you have a limited set of words to add like AT&T or M&Ms which are words with special characters that might be used over and over that you wanted treated as a single word instead of the default word breaking behavior.

Custom dictionaries are not great for for words like part numbers (ex. JY-14562, PTW-14432, etc.) where every row in a table would result in a new entry in the custom dictionary. You could potentially have several hundred thousand or more unique part numbers. That would not be a good use for custom dictionary.  If you need this type of behavior, you are better off writing your own word breaker. Creating word breakers is not well documented nor easy to create, but it does allow you to decide how you want words broken when you encounter special characters.


Expired archived logs not deleted from RMAN catalogue

Doing a LIST EXPIRED ARCHIVELOG and then a DELETE EXPIRED ARCHIVELOG should cause a new LIST EXPIRED ARCHIVELOG to not show any entries in most situations.  Here are a few situations that could cause this to not be the case.

If FORCE is not specified on the delete command then it follows the archived log deletion policy which determines when archived redo log files are eligible for deletion.
The deletion policy does not apply to foreign archived redo log files, which are logs received by a logical standby database for a LogMiner session.

These possibilities are from this documentation.  If neither is applicable then perhaps you are running into a bug and should open an SR with support.

I assume you are running commands like this:

   list expired archivelog all;
   crosscheck archivelog all;
   delete expired archivelog all;
   list expired archivelog all;

Perhaps the LIST EXPIRED entries are for items not being deleted by the specific delete command you are doing.  For example, a DELETE EXPIRED BACKUP will not delete expired archivelogs or expired copies.

Since the problem is sporadic, you shouldn't be having the problem referenced in MOS Doc ID 1189883.1 in which the backup command follows the form backup archivelog all archivelog...


I agree that there are no real benefits in terms of storage space as long as you are comparing DECIMAL(9, 0) vs INT or DECIMAL(18, 0) vs BIGINT. (Within a single byte.)

In terms of processing, like @Andriy says the DECIMAL will naturally divide into a type that doesn't lose the fractional part, if that's important to you.

On the other hand, working with native INT types is much faster from a numerical standpoint if you are doing a lot of SUM()s or comparisons (such as searching on the values) as they are pipelined more efficiently by the CPU. An int comparison is two assembly opcodes (MOV, CMP) but any decimal comparison will be many, many more.


MySQL error code 134 = Record was already deleted (or record file crashed)

Solution : You need to run a table(s) repair on the database

Identifying table corruption

Checking tables

There are three ways to check tables. All of these work with MyISAM tables, the default, non-transactional table type, and one with InnoDB, the most mature of the MySQL transactional table types. Fortunately, MySQL now allows you to check tables while the server is still running, so corruption in a minor table need not affect everything on the server.

The CHECK TABLE SQL statement (obviously the server must be running
for this)
Running the mysqlcheck command-line utility (the server can be
running)
Running the myisamchk command-line utility (the server must be down,
or the tables inactive)

Checking tables with CHECK TABLE

The first method for checking tables is to run the CHECK TABLE SQL statement while connected to the server. The syntax is:

CHECK TABLE tablename[,tablename2...] [option][,option2...]

QUICK

The quickest option, and does not scan the rows to check for incorrect links. Often used when you do not suspect an error.

FAST

Only checks tables if they have not been closed properly. Often used when you do not suspect an error, from a cron, or after a power failure that seems to have had no ill-effects.

CHANGED

Same as FAST, but also checks tables that have been changed since the last check.

MEDIUM

The default if no option is supplied. Scans rows to check that deleted links are correct, and verifies a calculated checksum for all keys with a calculated a key checksum for the rows.

EXTENDED

The slowest option, only used if the other checks report no errors but you still suspect corruption. Very slow, as it does a full key lookup for all keys for every row. Increasing the key-buffer-size variable in the MySQL config. file can help this go quicker.

Note that CHECK TABLE only works with MyISAM and InnoDB tables. If CHECK finds corruption, it will mark the table as corrupt, and it will be unusable. See the Repairing tables section below for how to handle this

Checking tables with mysqlcheck

The second method is to run the mysqlcheck command-line utility. The syntax is:
mysqlcheck [options] dbname tablename [tablename2... ].

The following options pertain to checking (mysqlcheck can also repair, as well as analyze and optimize.

--auto-repair

Used together with a check option, it will automatically begin repairing if corruption is found.

--check, -c

Checks tables (only needed if using mysqlcheck under another name, such as mysqlrepair. See the manual for more details)

--check-only-changed, -C

Same as the CHECK TABLE ... CHANGED option above.

--extended, -e

Same as the CHECK TABLE ... EXTENDED option above.

--fast, -F

Same as the CHECK TABLE ... FAST option above.

--medium-check, -m

Same as the CHECK TABLE ... MEDIUM option above.

--quick, -q

Same as the CHECK TABLE ... QUICK option above.

Note that you can specify multiple tables, and that mysqlcheck only works with MyISAM tables.

Checking tables with myisamchk

Finally, there is the myisamchk command-line utility. The server must be down, or the tables inactive (which is ensured if the --skip-external-locking option is not in use). The syntax is: myisamchk [options] tablename.MYI, and you must be in, or specify, the path to the relevant .MYI files (each MyISAM database is stored in its own directory). These are the available check options:

--check, -c

The default option

--check-only-changed, -C

Same as the CHECK TABLE ... CHANGED option above.

--extend-check, -e

Same as the CHECK TABLE ... EXTENDED option above.

--fast, -F

Same as the CHECK TABLE ... FAST option above.

--force, -f

Will run the myisamchk repair option if any errors are found

--information, -i

Display statistics about the checked table

--medium-check, -m

Same as the CHECK TABLE ... MEDIUM option above.

--read-only, -T

Does not mark the table as checked

--update-state, -U

This option stores when the table was checked, and the time of crash, in .MYI file.

You can also use wildcard to check all the .MYI tables at the same time, for example:

% myisamchk *.MYI

Note that myisamchk only works with MyISAM tables. For those of you still using the old ISAM table types, there is also isamchk, though there is really little reason not to upgrade to MyISAM.

Repairing tables

In most cases, only the index will be corrupted (the index is a separate, smaller, file with records that point to the main data file) - actual data corruption is extremely rare. Fixing most forms of corruption is relatively easy. As with checking, there are three ways to repair tables. These all only work with MyISAM tables - to repair corruption of the other table types, you will need to restore from backup:

The REPAIR TABLE SQL statement (obviously the server must be running
for this)
The mysqlcheck command-line utility (the server can be running)
The myisamchk command-line utility (the server must be down, or the
tables inactive)

Repairing a table requires twice as much disk space as the original table (a copy of the data is made), so make sure you are not going to run out of disk space before you start.

Repairing a table with REPAIR TABLE

The syntax is, as would be expected, REPAIR TABLE tablename[,tablename1...] [options]. This method only works with MyISAM tables. The following options are available.

QUICK

The quickest, as the data file is not modified.

EXTENDED

Will attempt to recover every possible data row file, which can result in garbage rows. Use as a last resort.

USE_FRM

To be used if the .MYI file is missing or has a corrupted header. Uses the .frm file definitions to rebuild the indexes.

Repairing tables with mysqlcheck

The mysqlcheck command-line utility can be used while the server is running, and, like all the methods of repair, only works with MyISAM tables. The syntax is:

%mysqlcheck -r sports_results fixtures -uuser -ppass

Repairing tables with myisamchk

The server must be down, or the tables inactive (which is ensured if the --skip-external-locking option is not in use). The syntax is myisamchk [options[ [tablenames]. Remember again that you must be in, or specify, the path to the relevant .MYI files. The following options are available:

--backup, -B

Makes a .BAK backup of the table before repairing it

--correct-checksum

Corrects the checksum

--data-file-length=#, -D #

Specifies the maximum length of the data file, when recreating

--extend-check, -e

Attempts to recover every possible row from the data file. This option should not be used except as a last resort, as it may produce garbage rows.

--force, -f

Overwrites old temporary .TMD files instead of aborting if it encounters a pre-existing one.

keys-used=#, -k #

Can make the process faster by specifying which keys to use. Each binary bit stands for one key starting at 0 for the first key.

--recover, -r

The most commonly used option, which repairs most corruption. If you have enough memory, increase the sort_buffer_size to make the recover go more quickly. Will not recover from the rare form of corruption where a unique key is not unique.

--safe-recover, -o

More thorough, yet slower repair option than -r, usually only used only if -r fails. Reads through all rows and rebuilds the indexes based on the rows. This also uses slightly less disk space than a -r repair since a sort buffer is not created. You should increase the key_buffer_size value to improve repair speed if there is available memory.

--sort-recover, -n

MySQL uses sorting to resolve the indexes, even if the resulting temporary files are very large.

--character-sets-dir=...

The directory containing the character sets

--set-character-set=name

Specifies a new character set for the index

--tmpdir=path, -t

Passes a new path for storing temporary files if you dont want to use the contents of the TMPDIR environment variable

--quick, -q

The fastest repair, since the data file is not modified. A second -q will modify the data file if there are duplicate keys. Also uses much less disk space since the data file is not modified.

--unpack, -u

Unpacks a file that has been packed with the myisampack utility.

An example of its usage:

% myisamchk -r fixtures
- recovering (with keycache) MyISAM-table 'fixtures.MYI'
Data records: 0

  All above information was taken from the following reference

Repairing Data Base Corruption
How to Repair MyISAM Tables - MySQL

Pls refer the articles if anything that I have mentioned above is unclear.Hope my answer helps!


Does mysql use deleted rows space in a table?

Here is something to consider: you need to determine how much fragmentation exists before deciding to defragment the table or not.

The most efficient way is to measure the space usage from the OS and from INFORMATION_SCHEMA

For this example, assume the following

datadir is /var/lib/mysql
database is mydb
table is mytable

OS

TABLE_SIZE_OS=`ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'`
echo ${TABLE_SIZE_OS}

INFORMATION_SCHEMA

SELECT (data_length+index_length) INTO @table_size_inf
FROM information_schema.tables
WHERE table_schema'mydb'
AND table_name='mytable';
SELECT @table_size_inf;

Simply subtract @table_size_inf from TABLE_SIZE_OS to get the number of bytes unused

Since you deleted 85.7142 % of the table (60GB out of 70GB), you could then run

OPTIMIZE TABLE mydb.mtable;

innodb_file_per_table is disabled

You could do this against an entire database

cd /var/lib/mysql
IBDATA_SIZE=0
for X in `ls -l ibdata* | awk '{print $5}'` ; do (( IBDATA_SIZE += X )) ; done
SQLSTMT="SELECT SUM(data_length+index_length)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE engine='InnoDB'"
INNODB_SIZE=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( IBDATA_FRAG = IBDATA_SIZE - INNODB_SIZE ))

I have suggested stuff like this before

Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Jul 03, 2013 : Information about Disk Storage MySQL

UPDATE 2017-01-31 07:40 EST

You just asked the following

  so i am assuming , if i don't do optimization and keep on inserting data , newly inserted records will use the space which is left over by previously deleted records and table size won't grow. However it depends on the newly inserted data size and contiguous space left over by previously deleted data . Am i correct?

My answer to that is Yes. Deleted rows are simply marked and are available for reuse.

However, each 16K block would have its own internal fragmentation. Even my answer does not compute the granularity of fragmentation within 16K blocks. The more sparse a 16K block is due to deleted space, the more disk I/O InnoDB will experience in traversing 16K blocks in search of free space wide enough for row insertion.

If you are satisfied with the rate of your bulk insertions, then there is no need to OPTIMIZE TABLE that one table. If your index statistics against that table make SELECT queries bad, just run ANALYZE TABLE instead of OPTIMIZE TABLE.


Optimizing a simple query joining two big tables

Composite indices including statut might help.
Since only rows with statut = 'selected' are of interest, partial indexes might help some more:

-- CREATE INDEX ON traitements.zones_sites (pkid_site)
-- WHERE statut = 'selected';  -- not useful for you!

CREATE INDEX ON traitements.sites_candidats (pkid)
WHERE statut = 'selected';     -- probably also not useful, yet

Smaller indices, less write load and fragmentation.

However, since most rows in zones_sites have statut = 'selected', and since you fetch several columns from that table, and (almost) all rows, Postgres will stick to a sequential scan as fastest option and the only index that can help is the one on sites_candidats - where also ~ 70 % of all rows qualify. So a bitmap index scan using the above index would most likely still be more expensive than another sequential scan on zones_sites.

The remaining option to improve performance is an index-only scan. Consider appending the only other column of interest from sites_candidats to the index:

CREATE INDEX ON traitements.sites_candidats (pkid, geom)
WHERE statut = 'selected';

Only useful if preconditions for index-only scans are met, and
it becomes more attractive if the table is big and the appended column is small. Related:

Does PostgreSQL use an index-only scan in this JOIN?

More on index-only scans in my answer to your last question:

Optimizing query with type casting

Don't expect much, retrieving 210708 rows (according to your EXPLAIN output) will still take some time. If you don't actually need all of them, consider LIMIT (combined with ORDER BY) or additional predicates to fetch fewer rows and make it much faster.

Asides:

I would not use accented letters in identifiers (like: précision_contour). While that works, it introduces potential encoding problems.

Why different data types for statut? varchar(255), varchar(100). Seems like those should be the same. If only a handful of states are possible, make that an enum or a tiny FK column pointing to a statut table - to make everything smaller and faster, yet. And less error-prone.

You had Postgres 9.5 in your last question yesterday, but Postgres 9.6 today. Did you just upgrade? If so, did you run ANALYZE after the upgrade? Table statistics are not carried over. Related:

Optimizing slow performance of simple SELECT query


Suggest to record the rankings, as they occur, in a third table where the primary key links the user and the item. Table only records as many rankings as are made available. Each user's rankings are kept separate.

This records a ranking as an ordered triple: (user, item, rank).

    CREATE TABLE `user_item_rank` (
      `fk_user` ... NOT NULL,
      `fk_item` ... NOT NULL,
      `rank` ... NOT NULL,
      PRIMARY KEY (`fk_user`,`fk_item`),
      KEY `fk_user` (`user_table`),
      KEY `fk_item` (`item_table`),
      CONSTRAINT `...` FOREIGN KEY (`fk_user`) REFERENCES ...
      CONSTRAINT `...` FOREIGN KEY (`fk_item`) REFERENCES ...
      ...
      );


SQL Server best option for low-cost high-availability and queryable secondary?

Log shipping is what we end up using. However licensing dictates that we pay for additional licenses if we use the secondary in any way at all (even to take backups from)...


Separating working hours with non-working hours SQL

If I can understand what you are asking I wrote this code for you. This is when your workweek will be from monday till friday and your workhours will be from 8 to 17

select *
      from (
           select
           case when datename(dw,[date]) in ('Saturday','Sunday') then 'Weekend' else 'Week' end as WeekendOrWeek,
           datepart(HOUR,[date]) as HourDate,
           *
           from Electricalconsumptionhalfhourview
           where meterID = 17635382
           )WeekDays
where WeekDays.WeekendOrWeek = 'Week' and WeekDays.Hourdate > 8 and WeekDays.HourDate < 17

This is written for T-SQL. (SQL Server) If you want to get the dates which are not in the week just edit the where clause.


How to transfer data using expdp and impdp commands?

impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want.

Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE privileges specifically for this type of thing, you'll need to grant access to the Oracle directory object(s) too.)

A full schema export (metadata and contents) would indeed look like:

expdp user/pass schemas=<schemaname> directory=dumpdir \
      dumpfile=<schemaname>.dmp \
      logfile=expdp_<schemaname>.log

If you want to import to a different user/schema (the target database can be the same as the source), you can use:

impdp user/pass schemas=schema1 directory=dumpdir \
      remap_schema=schema1:schema2 \
      dumpfile=schema1.dmp \
      logfile=impdp_schema2.log

If you don't want a complete import, you can set some filters both on data and metadata. See Filtering During Import Operations.

The Utilities Guide has all the details, I strongly recommend reading at least the overview part.


SELECT type, channel, teilnr, starttime, endtime, usedtime, host
FROM online_time
WHERE starttime > (CURRENT - 15 UNITS MINUTE)
ORDER BY starttime DESC


From what I can tell, you'll have to create the 'some_value' in table3. Here's a link to the manual page on foreign key constraints

There is an example with a 'complex' foreign key constraint that is similar to your setup, which duplicates the content in the third table. There is (minimal) comfort in knowing that hey, at least it's handled by a Foreign Key.


PostgreSQL \COPY fails "ERROR: missing data for column "column name2""

The PostgreSQL server \COPY command is very simple and just aborts on a single failure. You might think that it could do far better (I know I do), but there's a reason that the PostgreSQL codebase is so compact with respect to MySQL's (by a factor of ~ 10/1).

However, there is the (very) nice pgloader programme which compensates for this at the price of having to run a separate utility.

Of course, if you're good at the PL/pgSQL language (internal to the the server), then maybe you could explore that route - but why reinvent the wheel? Python and Perl also have internal PostgreSQL options. Then of course, there's all the languages under the sun external to the server.

From the manual:

  PgLoader Reference Manual

  pgloader loads data from various sources into PostgreSQL. It can
  transform the data it reads on the fly and submit raw SQL before and
  after the loading. It uses the COPY PostgreSQL protocol to stream the
  data into the server, and manages errors by filling a pair of
  reject.dat and reject.log files.

which appears to be right up your alley?

The way it works is: (sorry for the long quote)

TL;DR  - pgloader loads a batch (configurable) at a time. On failure, it "marks the spot", uses \COPY again up until that point, stops, then puts the bad record into a file and continues from bad-record + 1.

  Batches And Retry Behaviour

  To load data to PostgreSQL, pgloader uses the COPY streaming protocol.
  While this is the faster way to load data, COPY has an important
  drawback: as soon as PostgreSQL emits an error with any bit of data
  sent to it, whatever the problem is, the whole data set is rejected by
  PostgreSQL.

  To work around that, pgloader cuts the data into batches of 25000 rows
  each, so that when a problem occurs it's only impacting that many rows
  of data. Each batch is kept in memory while the COPY streaming
  happens, in order to be able to handle errors should some happen.

  When PostgreSQL rejects the whole batch, pgloader logs the error
  message then isolates the bad row(s) from the accepted ones by
  retrying the batched rows in smaller batches. To do that, pgloader
  parses the CONTEXT error message from the failed COPY, as the message
  contains the line number where the error was found in the batch, as in
  the following example:

  CONTEXT: COPY errors, line 3, column b: "2006-13-11"

  Using that information, pgloader will reload all rows in the batch
  before the erroneous one, log the erroneous one as rejected, then try
  loading the remaining of the batch in a single attempt, which may or
  may not contain other erroneous data.

  At the end of a load containing rejected rows, you will find two files
  in the root-dir location, under a directory named the same as the
  target database of your setup. The filenames are the target table, and
  their extensions are .dat for the rejected data and .log for the file
  containing the full PostgreSQL client side logs about the rejected
  data.


I am not an Oracle expert, but I can answer from DB2 perspective, you may look for similar features/commands in Oracle. In DB2, you can achieve this by:

Using Import command with INSERT_UPDATE option.
You also have a choice to use MERGE statement to merge data between two tables.

Hope it helps you.


Max Server Memory configured but it increases more of its configuration

MAX SERVER MEMORY worked a little differently before SQL Server 2012:

Starting with SQL Server 2012, Multi-Page allocations and CLR
allocations are also included in memory limits that are controlled by
max server memory (MB) and min server memory (MB).

Memory configuration and sizing considerations in SQL Server 2012 and later versions


Why is dbo the only grantor in Microsoft SQL Server?

Probably because all your logins are sysadmin, and sysadmin always connects as dbo.

Try something like:

create user fred without login

create user joe without login

grant select to fred with grant option

execute as user='fred'
  grant select to joe
revert

select * from sys.database_permissions


SQL 2014 SP2 CU1 - AlwaysOn Cluster trouble synching to one node

SPID -3 is a deferred transaction or BOL Definition.

  A deferred transaction is one where the transaction could not be recovered fully because of an I/O error or a gross file system problem like an unavailable file.

Check your disk subsystem and run a CHECKDB in your database.

To kill the negative spid, use

SELECT DISTINCT 'KILL ''' + CONVERT(VARCHAR(100),request_owner_guid) + ''';'
FROM   sys.dm_tran_locks
WHERE  request_session_id = -3
AND    database_id = DB_ID('Your_DBName');


What databases or styles of database can use git (or another dvcs architecture) to manage cross-site replication?

For simple key/value storage, you might like to consider Berkeley DB, though I would not think Git would be the best for 'version control'. You can use Ruby bindings with BDB.

Since your edit, I'm left wondering If you want an open-source implementation of something like Amazon S3


Create a form solely for inputting data?

If you enter design mode on your entry form, open up your properties window and select the form object. You should find a property called "Data Entry" which you should set to "Yes." This removes the link to all data from the related table and will only display blank controls. You can then have your user enter data directly into the form. I believe the default adds the data to the table when they exit the last control in the tab order.

There are also options in the same properties pane for the form that allow you to switch off the record navigation buttons.


How to get currently inserted rows and sum them within Mysql trigger before insert

BEFORE inserting, use OLD.

Is this building something like a "Summary table" for a Data Warehousing application?  If so, consider doing:

INSERT INTO tmp VALUES ...;
INSERT INTO summary_table  SELECT SUM(...),... FROM tmp GROUP BY ...;
INSERT INTO real SELECT * FROM tmp;

There are many variants on that flow.  But note that you get a chance to transform data, do multiple inserts, etc.  And, quite probably the GROUP BY will be faster than doing one the rows one at a time, as you are doing with the TRIGGER.


Change SQL Server 2000 from Active/Active to Active/Passive

You can review this MSSQLTip to get more information on what active/active and active/passive mean in regards to SQL Server Clusters:
SQL Server Clustering Active vs Passive

In your situation, and based on your comments, the only thing you need to do is configure the dependencies for your resources. So if I have a cluster resource for the the SQL Server Cluster Name and it fails over, then all of the dependent resources will follow suite.

I have not messed with or ever configured a SQL Server 2000 cluster so I do not know what how this was done during installation. I am aware that starting with SQL Server 2005 the installation did more of this for you: Failover cluster resource dependencies in SQL Server


FLUSH LOGS command introduces a long sleep

There was issue with the log-error file to which I was suppose to log mysql errors.
I had this line in my.cnf

log-error=/var/lib/mysqld.log

Due to permission issue mysql was not able to create this log file nor an error was thrown suggesting the same.

I selectively removed and toggled the settings which helped me reach to the problem.


Is there a reliable way to determine how long it took the optimizer to generate a query plan?

There are many ways :

If you can run the query

you can use set statistics time on which will give you:

  SQL Server parse and compile time:
  CPU time = xx ms, elapsed time = xx ms.

If you cant run the query, then mine the plan cache to check for query compile time along with many other significant insights.

There are many other ways like profiler, server side trace or XEvents - but they are expensive and might have negative perf impact on your sql server.

You can use sp_BlitzCache to find out Query Compilation Timeout from plan cache.


Do unused columns in MySQL/MariaDB notably affect performance?

Unused columns do carry a cost, but rarely one that most people will notice or care about in systems with well-written, targeted queries. If you are curious about testing performance and contributing in a meaningful way to the WordPress project, you might want to consider doing something like this:

Make a workable backup of your WordPress database. For example:
sudo mysqldump wordpress > wordpress.sql

Note: This assumes you would create backups via a command-line. If you prefer GUIs, make a backup in your preferred way.

Drop the columns that you think you can live without:
ALTER TABLE wp_posts DROP COLUMN comment_status;
ALTER TABLE wp_posts DROP COLUMN ping_status;
ALTER TABLE wp_posts DROP COLUMN to_ping;
ALTER TABLE wp_posts DROP COLUMN pinged;
ALTER TABLE wp_posts DROP COLUMN comment_count;

See what breaks over the next few days. If WordPress is unhappy about something, it is not shy about telling the world that something is wrong.
Then, if nothing bad happens...

Begin modifying the WordPress API to handle those fields differently (if at all) and tweak the database to be more performant.

Working on a large project like WordPress can bring a great deal of satisfaction, particularly if any of your modifications are adopted by the core project.


selecting table1 and table2 with single data row and table 3 with multiple data rows

The answer to my question is, all I need to do is add the GROUP BY.

SELECT a.payment_id, a.payment_arp_number, a.payment_pin, b.property_id, b.property_arp_number,
b.property_location, c.property_id, group_concat(c.pa_classification separator ', ')
as pa_classification, sum(c.pa_market_value) as pa_market_value
FROM `property.payments` a, properties b, `property.assessments` c
WHERE a.payment_arp_number = b.property_arp_number
AND b.property_id = c.property_id
GROUP BY a.payment_id


Is it worth to update to an x64 environment?

Well, memory management will be much better in a 64bit platform.  This is because all the memory is real memory and there's no concept of extended memory any more.

This means that you can now have much more than 1 Gig of execution plans in memory at any one time.  If you've got lots of stored procedures you are probably generating new plans for queries all the time because you can't store all the plans in memory.

Granted this ability to grow the plan cache beyond 1 Gig will take away from the buffer pool, so that's something to keep in mind (no you can't control how big the plan cache is).

Joins of 4-5 tables with 100k records each isn't all that scary.  When you get into the 100M range that's when things start to get more interesting.


self referencing tables with SQL and entity framework

Relational modelling starts from the identification of classes of "things." To these we apply normalisation to remove data update anomolies.  The results are implemented as tables with a column for each attribute we identify.  We do not start from a bunch of domains, look around for "things" with the same bunch of domains, shoehorn them into the same table and call it an Entity.  This is an anti-pattern generally called "One True look-up Table".  Implementing it compromises what constraints can be defined & how foreign keys can be established.  Tables can become bloated with NULL-able columns.  This increases the IO required to fulfil queries.  Index depth increases, slowing even key lookups.

Now, at some stage in every project the science of normalisation bumps up against the art of model design.  Decisions have to be made and compromises have to be accepted.  In your case I can see one of three models emerging.

The first is the normalised one you have currently.  This is how RDBMS's are supposed to work.  Mappings are obvious; queries are fast.  Exansion, however, requires schema changes and these can be slow to implement, depending on your company's abilities.

The second is a super type / sub type model.  You treat Company, Contact etc. as specialisations of another type, say LegalPerson.  This super type contains the common attriubtes.  Each sub type has the attributes specific to it.  There is a one-to-one relationship between the super type and the sub type i.e. a given "thing" must have a row in the super type and exactly one sub type.  This can be difficult to enforce in current SQL products.  This allows recognition of common columns between entity types.  Relationships between the types are held in specific foreign key columns or intersection entities. The insertion and manipulation of data is more complex. Again, expansion of the schema will reqire development work.

Finally there is the entity-attribute-value (EAV) model.  Here you are effectively building a database-within-a-databse.  The objects you model are "Table" and "Column" instead of "Customer" and "Company".  Thus your application can ammend the logical schema at will.  The price is application complexity and run-time performance.

EAVs have a bad reputation.  There are many posts explaining why perdition will descend upon you simply by uttering the name aloud.  Others defend the practice.  I myself have an EAV systems holding 300M values.  It works well for us.  But I did this intentionally, in full understanding of the implications, with a DBA team to support me, to solve a specific problem we could not address otherwise.  It was not done because some of the tables looked a bit like others.


MySQL TRUNCATE TABLE blocks queries in other databases

TRUNCATE would have to invalidate all dependent queries in the query cache, so that's probably why it's taking a lock on it. If your query cache is sufficiently large, invalidation might take some time. Since MySQL "databases" are more like schemas, a single query cache is maintained for all databases in the MySQL instance, so there really is no isolation between them.

You may want to consider disabling query cache or, better yet, using different MySQL instances for testing and production.


How to restore mysql table from .frm files

Permissions is an OS problem.  The files need to be owned by "mysql".  Were you 'root' when copying the files?  Did you preserve the ownership and permissions during the copy?  What OS are you using?


...if the order of the partition id is not in sequence with the partition key order

SQL Server always maintains partition ids in partition boundary order. No matter how you split or merge partitions, the order of boundary values always matches the order of partition ids. SQL Server renumbers as necessary.

Concerning performance of the split operation itself, you will want to organize things so that the minimum (ideally, none at all) physical data movement occurs. This typically involves careful thought and testing in advance, using SWITCH and CREATE INDEX…WITH DROP_EXISTING = ON.

Review the following all by Dan Guzman:

Table Partitioning Best Practices
Remediating a Table Partitioning Mess
Move a Partition to a Different File Group Efficiently

If you need help designing the process, ask a new question with DDL for the table, indexes, partitioning scheme, function, and file groups. Also include a clear representation of the start and end states i.e. which data should go where.


Schedule SQL Jobs from Central Management Server

There is a separate way to control multi server jobs as described in the following link.
It is a shame that these were never integrated together by MS
https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-master-job?view=sql-server-ver15


What are database statistics, and how can I benefit from them?

Statistics simply are a form of dynamic metadata that assists the query optimizer in making better decisions. For example, if there are only a dozen rows in a table, then there's no point going to an index to do a lookup; you will always be better off doing a full table scan. But if that same table grows to a million rows, then you will probably be better off using the index. But if you query that table on a column that only has very few unique values (e.g. it might be a "sex" column containing only "M" or "F"), well actually a FTS might be better that that case because you would need to retrieve the block anyway to build the result set. Now say your table is 99% "M" and only 1% "F", we should FTS in one case or use an index in the other.  Same table, same query, potentially four different query plans depending on the contents of the table. These kinds of things are the "statistics" and they are individual to each database - even two databases with identical table and index structure will have different statistics.

In summary, in a modern database engine there are two kinds of query optimization: rewriting the SQL (rule-based optimization, like a compiler rewrites your C to make it more efficient) and choosing the right path the the data (cost-based optimization, like a JIT compiler identifying hotspots at runtime). You only need to worry about this if you spot the query optimizer doing something obviously wrong (e.g. choosing FTS when you know an index would be better).


Configure Always On to use secondary site as DR

Standard Edition availability groups are limited to one replica, which is clearly stated in  the documentation at Basic Always On availability groups for a single database.  So if you need to maintain a another copy, you'll need to use a different option.

Some possibilities are:

Log shipping
Replicate the entire server at the virtual server's host layer with something like Zerto
Replicate the storage at the storage layer
Replicate the database files with a third party product that performs host-level file replication (e.g., Carbonite Double-Take).


How to Disable Windows Authentication in SQL Server

To stop Windows users from logging in, don't create logins for them in SQL Server.

By default, just because someone has a Windows login doesn't mean they have permissions in SQL Server. You have to use the CREATE LOGIN command to map Windows users to SQL Server permissions.

If you don't do that, they can't log in.

Your organization may also be asking for a list of people and groups who already have logins, and who need to be removed. In SQL Server Management Studio, go into Security, Logins as described in the documentation, and you can review the list of logins from there, and disable (or drop) logins who shouldn't have access.


Lock out of Oracle based on Windows username

The reason why your trigger does not work for users like SYS or SYSTEM is because they have the ADMINISTER DATABASE TRIGGER privilege.

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe.

So, the answer is: no, you cannot prevent login for such users - at least not with a login trigger.


"Unable to Open BCP host data-file" using xp_cmdshell from SSMS but command line works

I just got your code to work on my end.

Try this:

Option A

Assign Full Control NTFS permissions to 'C:\BCPFiles\bcpFile.dat to
the user you will use in SQL Server Management Studio to run the
query
Re-run your query in SQL Server Management Studio

Option B

Or, just to get past the following error try:

Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file

Delete/move your bcpfile.dat file
on the command line type (to create a dummy file--yes there will be import errors, but just to get past opening the file...)

echo .> c:\bcpfiles\bcpfile.dat
Re-run your query in SQL Server Management Studio

If option A works, then it's a permissions issue. If option B works (getting past the original error) then you have an open file--even though it doesn't appear to be open. There are utilities that help out with finding these open files, but a cheap and easy way to test is just to reboot the server--hopefully it's a dev/test box.


MySQL: carriage-return in query breaks my import

If CR or CRLF is the line terminator, so state.  Don't assume LF is smart enough to include CR.


query for each row from another arbitrary query, and union results

How about:

with cte as (%(query))
select unique table.* from table join cte using (num,sub)
    where %(query_time_part);

I'm assuming here that "%(query_time_part)" is written such that it applies to the literal "table", while "%(query)" already contains the analogous code which applies to the "table" underlying "cte".

The rows that make up the cte should automatically meet the join condition to self-join, so should not need any special steps, like a UNION, for them to be emitted in their own right.  That is, the version of them that get emitted is the version that comes from "table", not from "cte", but since they are identical it doesn't matter.


How SQL Server knows when to implicitly convert the values

My question is why SQL uses '201701' as a number but uses implicit conversion on the second value: '201702A'
what I'm interested in is the internal mechanism how SQL server looks into these two values. Does it know the first one is a number and the second one is not?

SQL Server attempts to convert both strings to the correct type (according to the data type precedence rules) for comparison with the integer Skey column during the Constant Folding phase of query compilation. This activity occurs very early in the process, well before even the simplest of query plans is considered.
When constant folding is successful, the input tree contains the derived literal value (as the correct type) and optimization continues, just as if the query writer had used a constant rather than an expression.
When constant folding is unsuccessful (for example because the conversion would throw an error) the tree contains a conversion function. It would not be correct to throw an error at compilation time; an error should only occur when the query is executed, and the problematic expression is actually evaluated (if at all).
So, in your case, '201701' is constant-folded to integer 201701, but '201702A' becomes CONVERT_IMPLICIT(int,'201702A',0).
Constant folding is much more powerful and complete than the above simple example would suggest. For example:
LastName LIKE SUBSTRING(LEFT(NCHAR(UNICODE(NCHAR(68))), 1) + N'%', 1, 2)

is constant-folded to:
LastName LIKE N'D%'

In SQL Server 2012 and later, even deterministic SQLCLR scalar functions can be constant-folded.


Creating a Persisted Computed Column with a function

Still don't understand why this needs to be a column in a table, never mind a persisted one.

Why not just create a table-valued function that you cross apply when (and only when) the query actually needs it? Since the old key will never change it doesn't need to be computed or persisted anyway.

If you really want the old key to live in multiple places (and it sounds like people who shouldn't be making this kind of decision have already made this kind of decision), just do the lookup in a trigger and populate it at write time. Then it's just a static column in a table.

I still highly recommend a table-valued function to facilitate this, so that you can write the trigger in such a way that it handles multi-row operations... without having to write a loop, or call a scalar-valued function over and over again for every row.

Just to show how similar these things really are (and question your lead developer who "doesn't like it"):

-- bad, slow, painful row-by-row
CREATE FUNCTION dbo.GetIDByGUID
(
  @GuidKey uniqueidentifier
)
RETURNS int
AS
BEGIN
  RETURN (SELECT $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
END
GO

-- in the trigger:
UPDATE r SET oldkey = dbo.GetIDByGUID(i.guid_column)
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column;

Now, if you have a table-valued function, the code is quite similar, but you'll find the performance is much better in multi-row operations, and close to identical for single-row operations.

-- ah, much better
ALTER FUNCTION dbo.GetIDByGUID_TVF
(
  @GuidKey uniqueidentifier
)
RETURNS TABLE
AS
  RETURN (SELECT id = $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
GO

-- in the trigger:
UPDATE r SET oldkey = f.id
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column
  CROSS APPLY dbo.GetIDByGUID_TVF(i.guid_column) AS f;


Where do you get Example/Sample Databases from?

For MySQL, there are sample databases used (frequently in examples) and they are to be found here (rather confusingly) in the "documentation" section, under "other docs" - here. I'm afraid that I can't help with SQL Server, but I'm sure that there are others here who can.

Example Databases Title  Download DB HTML Setup Guide    PDF Setup Guide
employee data (large dataset, includes data and test/verification suite)      Launchpad
world database
sakila database
menagerie database

You might also want to check out my reply here to a poster who was looking for NoSQL datasets (shouldn't make a difference whether it's NoSQL or not).


Another way, using the LATERAL syntax, available from 9.3+ versions::

WITH t AS
  ( SELECT f1, f2,
           MAX(f2) OVER (PARTITION BY f1) AS range_to
    FROM dbTable
    GROUP BY f1, f2
  )
SELECT t.f1,
       -- t.f2 AS range_from, t.range_to,
       t.f2 || ' - ' || t.range_to AS RNG,
       x.f3
FROM t
  CROSS JOIN LATERAL
    ( SELECT f3
      FROM dbTable
      WHERE f1 = t.f1 AND f2 >= t.f2
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
ORDER BY t.f1, t.f2, x.f3 ;

Test at SQLfiddle.

Or without the CTE:

SELECT t.f1,
       -- t.f2 AS range_from,
       -- MAX(t.f2) OVER (PARTITION BY t.f1) AS range_to,
       t.f2 || ' - ' || MAX(t.f2) OVER (PARTITION BY t.f1) AS RNG,
       x.f3
FROM dbTable AS t
  CROSS JOIN LATERAL
    ( SELECT f3
      FROM dbTable
      WHERE f1 = t.f1 AND f2 >= t.f2
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
GROUP BY t.f1, t.f2, x.f3
ORDER BY t.f1, t.f2, x.f3 ;


The install/oraparam.ini contains the following lines (even in a fresh download):

#MSVCREDIST_LOC flag will provide the name of the exe that is being shipped in stage/ext/bin
MSVCREDIST_LOC=vcredist_x64.exe

This is incorrect because you need the 32-bit version (x86).

Now if you check stage/ext/bin, there is a file vcredist_x86.exe, and obviously no x64, since it is a 32-bit installer. Try editing oraparam.init and fixing the above line to:

MSVCREDIST_LOC=vcredist_x86.exe

Another possibility is that you need to install the Microsoft C++ 2010 Redistributable manually: http://www.microsoft.com/en-us/download/confirmation.aspx?id=5555


Reducing table size during initial load with ADR in SQL Server

I don't see any significant difference in this test.

create database nonadr
ALTER DATABASE nonadr SET ACCELERATED_DATABASE_RECOVERY = OFF

create database adr
ALTER DATABASE adr SET ACCELERATED_DATABASE_RECOVERY = ON;

go

with q as
(select top (1000*1000*10) *
 from sys.messages m, sys.objects o
)
select *
into nonadr.dbo.test
from q;

with q as
(select top (1000*1000*10) *
 from sys.messages m, sys.objects o
)
select *
into adr.dbo.test
from q;

go

exec nonadr.sys.sp_spaceused 'test'
exec adr.sys.sp_spaceused 'test'

SELECT DB_NAME(database_id) AS database_name,
       (persistent_version_store_size_kb / 1024.) AS persistent_version_store_size_mb
FROM sys.dm_tran_persistent_version_store_stats
WHERE persistent_version_store_size_kb > 0;

outputs

name                 rows                 reserved           data               index_size         unused
-------------------- -------------------- ------------------ ------------------ ------------------ ------------------
test                 10000000             3352392 KB         3351944 KB         8 KB               440 KB

name                 rows                 reserved           data               index_size         unused
-------------------- -------------------- ------------------ ------------------ ------------------ ------------------
test                 10000000             3494856 KB         3494384 KB         8 KB               464 KB

database_name        persistent_version_store_size_mb
-------------------- ---------------------------------------
adr                  0.070312


why are my Database backups occasionally failing?

I have not solved the problem The problem reamains that bad backups
  are being taken. I am worried that something is wrong with our prod
  server in General or those databases in particular. I am afraid that
  my work around will only hide the problem and that we have a train
  wreck coming down the line.

Yes.  You should be afraid, and proceed on the assumption that your storage solution is not reliable.  So you should immediately ensure that you are landing good backups on a reliable storage device, and proceed with troubleshooting/support of your production database's main storage and/or backup storage solution.


Reorder missing numbers in a sequencein two different tables using MySQL

drop procedure IF EXISTS ALTERTABLEVALUES;
delimiter $$
CREATE PROCEDURE ALTERTABLEVALUES()
BEGIN
DECLARE finished INT DEFAULT 1;
DECLARE total_count INT DEFAULT 1;
DECLARE val varchar(5);
DECLARE i INT DEFAULT 1;
DECLARE table_num INT;
DECLARE table_tab varchar(5);
DECLARE number_cursor CURSOR FOR select num,tab from tempnumbers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

drop temporary table if exists tempnumbers;
CREATE TEMPORARY TABLE tempnumbers as
select num,'temp' as tab from numbers union select num,'audit' as tab from numbers1 order by num;
SET val = (select num from tempnumbers limit 1);
SET total_count = (select count(*) from tempnumbers);

OPEN number_cursor;
get_values: LOOP
FETCH number_cursor INTO table_num,table_tab;
IF finished = 0 THEN
 LEAVE get_values;
 END IF;
IF table_tab = 'temp' THEN
    update numbers set numbers.num = val where numbers.num = table_num;
ELSE
    update numbers1 set numbers1.num = val where numbers1.num = table_num;
END IF;
SET val = val +1;
END LOOP get_values;
CLOSE number_cursor;
DROP TEMPORARY TABLE tempnumbers;
END;
$$
delimiter ;

After running the procedure I got numbers table as
n
--
1
2
3
5
6

and numbers1 table as

n
--
4
7
8
9

This is how i expected for the result.


How to deal with id gap when your query is like from id > XX to id < XXX

I aaume you want to extend the range of your search so it returns 50 rows even if there are gaps. Then you can use this:

SELECT TOP (50)
    id, name, address city
FROM table_name
WHERE id > 5000000
  AND deleted_at IS NULL
ORDER BY id ;

A partial index on (id) WHERE (deleted_at IS NULL) would improve efficiency.


Postgresql 9.6 Error trying to have a function return a string without first casting it as a string.

This has been fixed in version 10, as seen in the docs, Type Conversions:

SELECT Output Columns
The rules given in the preceding sections will result in assignment of non-unknown data types to all expressions in a SQL query, except for unspecified-type literals that appear as simple output columns of a SELECT command. For example, in
 SELECT 'Hello World';

there is nothing to identify what type the string literal should be taken as. In this situation PostgreSQL will fall back to resolving the literal's type as text.
...
Note
Prior to PostgreSQL 10, this rule did not exist, and unspecified-type literals in a SELECT output list were left as type unknown. That had assorted bad consequences, so it's been changed.

The solutions for you are pretty straight forward:
Either cast the values to text or upgrade to version 10.


Does calling a stored procedure implicitly start a new transaction?

Let's find out:

In session 1:

DELIMITER ;;
CREATE PROCEDURE p1() BEGIN SELECT 1; END;;
DELIMITER ;

CREATE TABLE t1 (id int unsigned auto_increment PRIMARY KEY);
START TRANSACTION;
INSERT INTO t1() VALUES ();
CALL p1();

In session 2:

SELECT * FROM t1;

which gives:

  Empty set (0.000 sec)

If the procedure call had caused the current transaction to be committed in order to start a new one, then we would have expected to see a row from the above SELECT.

So, back in session 1:

COMMIT;

And then session 2, run the SELECT again:

SELECT * FROM t1;

which now gives the expected result:

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.001 sec)

Conclusion: No, calling a stored procedure does not commit the current transaction and does not start a new transaction.


Synchronize two MySQL databases between two servers

Use replication. Triggers aren't needed.

There are replication filters and replicate_do_db will limit the replication to a single database. This operates differently depending on binlog_format=row or statement in the same way mysql does. For simplicity binlog_format=row gives the most expected behaviour.


Improve speed of COPY COMMAND in postgres

No, COPY does not need a lot of memory.

There are two things to speed up COPY:

Remove any indexes and constraints on the table before you COPY into it.
Increase max_wal_size so that you don't get more checkpoints than necessary.

Of course, if you COPY into an UNLOGGED table, it will be even faster.


Size limit of character varying Postgresql

The maximum size of limited character types (e.g. varchar(n)) in Postgres is 10485760. You can check this in that way:

create table test(id serial primary key, str varchar(10485761));

ERROR:  length for type varchar cannot exceed 10485760

The limit is defined in the following fragment of source code (htup_details.h), however is not explicitly mentioned in the official documentation:

/*
 * MaxAttrSize is a somewhat arbitrary upper limit on the declared size of
 * data fields of char(n) and similar types.  It need not have anything
 * directly to do with the *actual* upper limit of varlena values, which
 * is currently 1Gb (see TOAST structures in postgres.h).  I've set it
 * at 10Mb which seems like a reasonable number --- tgl 8/6/00.
 */
#define MaxAttrSize     (10 * 1024 * 1024)

The maximum number of characters for variable unlimited length types (text, varchar) is undefined. There is a limit of size in bytes for all string types:

  In any case, the longest possible character string that can be stored is about 1 GB.


pgadmin / postgresql - no pg_hba.conf entry for host

You're editing the wrong pg_hba.conf, connect locally with psql and run this

SHOW hba_file;

Then from outside edit that file. Perhaps you have two versions of PostgreSQL or you built your own. Or, you're restarting the wrong PostgreSQL. Perhaps make sure you're restarting the right one by connecting, again locally over psql, and running

SELECT pg_reload_conf();

Make sure you're editing the pg_hba.conf on the SERVER you're trying to connect to and not the CLIENT. (per @joanolo)


Tablestructure for fast inserts/deletes with foreign keys

Don't be scared to create a foreign key in a database. One of the biggest reasons why you should use a foreign key is to enforce referential integrity. Depending on your foreign key it can be possible to use the CASCADE option to delete all competitors for a product when you delete a product (so it makes it easy to manage).

If you set your tables and foreign key up properly the DELETE statement to delete a product and its competitors should something like:

DELETE FROM Product WHERE Product.Product_identifier = [insert your identifier here];

For an example about the DELETE CASCASE option of a foreign key I suggest you read the following article: http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

To list a product and all competitors you can use:

SELECT Product.product_identifier, Competitors.[some infos about competitor]
FROM Product
LEFT JOIN Competitors
   ON Product.Product_identifier = Competitors.Product
WHERE Product.Product_identifier = [insert your identifier here];


I would go for a variant somewhat between your second and third options:

Products (Id, Name, ...)

ProductVotes (Id, CustomerId, ProductId, Vote, CreatedOn)

The column Vote should be constrained to only allow the values Like and Dislike or, if you prefer, 1 and -1 -- the latter having the advantage of a simpler possibility to do the sum() of the likes and dislikes to get an overall vote.


This script will provide you with detailed information on SQL Server Agent Jobs and their schedules. This doesn't include any scheduled task that may exist in Scheduled Tasks for Windows.

USE msdb
Go

SELECT dbo.sysjobs.Name AS 'Job Name',
    'Job Enabled' = CASE dbo.sysjobs.Enabled
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END,
    'Frequency' = CASE dbo.sysschedules.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQLServer Agent starts'
    END,
    'Start Date' = CASE next_run_date
        WHEN 0 THEN null
        ELSE
        substring(convert(varchar(15),next_run_date),1,4) + '/' +
        substring(convert(varchar(15),next_run_date),5,2) + '/' +
        substring(convert(varchar(15),next_run_date),7,2)
    END,
    'Start Time' = CASE len(next_run_time)
        WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
        WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
        WHEN 3 THEN cast('00:0'
                + Left(right(next_run_time,3),1)
                +':' + right(next_run_time,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(next_run_time,5),1)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 6 THEN cast(Left(right(next_run_time,6),2)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
    END,
--  active_start_time as 'Start Time',
    CASE len(run_duration)
        WHEN 1 THEN cast('00:00:0'
                + cast(run_duration as char) as char (8))
        WHEN 2 THEN cast('00:00:'
                + cast(run_duration as char) as char (8))
        WHEN 3 THEN cast('00:0'
                + Left(right(run_duration,3),1)
                +':' + right(run_duration,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(run_duration,5),1)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 6 THEN cast(Left(right(run_duration,6),2)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
    END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
        WHEN 0 THEN 'Once'
        ELSE cast('Every '
                + right(dbo.sysschedules.freq_subday_interval,2)
                + ' '
                +     CASE(dbo.sysschedules.freq_subday_type)
                            WHEN 1 THEN 'Once'
                            WHEN 4 THEN 'Minutes'
                            WHEN 8 THEN 'Hours'
                        END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
        FROM dbo.sysjobhistory
        GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name',
    'Job Enabled' = CASE dbo.sysjobs.Enabled
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END,
    'Frequency' = CASE dbo.sysschedules.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQLServer Agent starts'
    END,
    'Start Date' = CASE next_run_date
        WHEN 0 THEN null
        ELSE
        substring(convert(varchar(15),next_run_date),1,4) + '/' +
        substring(convert(varchar(15),next_run_date),5,2) + '/' +
        substring(convert(varchar(15),next_run_date),7,2)
    END,
    'Start Time' = CASE len(next_run_time)
        WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
        WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
        WHEN 3 THEN cast('00:0'
                + Left(right(next_run_time,3),1)
                +':' + right(next_run_time,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 6 THEN cast(Left(right(next_run_time,6),2)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
    END,
--  next_run_time as 'Start Time',
    CASE len(run_duration)
        WHEN 1 THEN cast('00:00:0'
                + cast(run_duration as char) as char (8))
        WHEN 2 THEN cast('00:00:'
                + cast(run_duration as char) as char (8))
        WHEN 3 THEN cast('00:0'
                + Left(right(run_duration,3),1)
                +':' + right(run_duration,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(run_duration,5),1)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
        WHEN 6 THEN cast(Left(right(run_duration,6),2)
                +':' + Left(right(run_duration,4),2)
                +':' + right(run_duration,2) as char (8))
    END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
        WHEN 0 THEN 'Once'
        ELSE cast('Every '
                + right(dbo.sysschedules.freq_subday_interval,2)
                + ' '
                +     CASE(dbo.sysschedules.freq_subday_type)
                            WHEN 1 THEN 'Once'
                            WHEN 4 THEN 'Minutes'
                            WHEN 8 THEN 'Hours'
                        END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
        FROM dbo.sysjobhistory
        GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]

Update 11/4/2013

Here is another script that associates plans with their associated jobs

SELECT
s.name as [PlanName],
spl.start_time AS [StartTime],
spl.end_time AS [EndTime],
spl.succeeded AS [Succeeded],
sysjobs.name as [JobName],
'Frequency' = CASE sysschedules.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQLServer Agent starts'
END,
    'Start Date' = CASE next_run_date
        WHEN 0 THEN null
        ELSE
        substring(convert(varchar(15),next_run_date),1,4) + '/' +
        substring(convert(varchar(15),next_run_date),5,2) + '/' +
        substring(convert(varchar(15),next_run_date),7,2)
END,
    'Start Time' = CASE len(next_run_time)
        WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
        WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
        WHEN 3 THEN cast('00:0'
                + Left(right(next_run_time,3),1)
                +':' + right(next_run_time,2) as char (8))
        WHEN 4 THEN cast('00:'
                + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 5 THEN cast('0'
                + Left(right(next_run_time,5),1)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
        WHEN 6 THEN cast(Left(right(next_run_time,6),2)
                +':' + Left(right(next_run_time,4),2)
                +':' + right(next_run_time,2) as char (8))
    END
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
LEFT OUTER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id
INNER JOIN msdb.dbo.sysjobs sysjobs on sysjobs.job_id = sp.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id


How can I perform a mongoimport to a host that requires an SSL Certificate?

With mongoimport you must separate host address and authentication DB

  --host host_address:port --authenticationDatabase admin


Your code requires little tweaking. Where you are doing getdate you just need to do getdate() -6. The changed code is below.

Please note that ring buffer capacity is limited so it would not store information for complete day it would have information about specific time.

DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
SELECT top 20 record_id, EventTime,
  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
  SELECT
    record.value('(Record/@id)[1]', 'int') AS record_id,
    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()-6) AS EventTime,--changed here
    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
  FROM (
    SELECT timestamp, CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
      AND record LIKE '%<SystemHealth>%') AS t
) AS t
ORDER BY record_id desc


MySQL has huge limits -- many terabytes.

You may run out of disk space.

Your app code may run out of RAM.  But only if you foolishly fetch all the rows at once.  Instead, use SQL to filter/summarize the data you fetch.

But otherwise, don't worry.


Managing Permissions on a server with new DBs being added

One possible option would be to:

Create a Database Role in each DB (i.e. this should be in your template / source DB) that contains the desired set of permissions.
Create an Instance / Server -level DDL Trigger that fires on the CREATE_DATABASE event and does the following:

Checks the name of the Database
If new DB name ends with ABC then:

Adds the Login to the new Database as a User
Add the new User to the Database Role that has the desired permissions

This will likely require some Dynamic SQL, but it should work. And honestly, I'm not sure what I think of using a DDL Trigger in this manner, but it certainly does automate it ;-).


Automatic conversion of anonymous records returned from an UDF to well known table-type

There is a way.

Given a table t and a function f() that returns an anonymous record that would match that table type:

CREATE TABLE t (id int, d date);

You cannot just cast the anonymous record, since a column definition list is required for

SELECT * FROM f()

Quoting the manual on the SELECT command:

  If the function has been defined as returning the record data type,
  then an alias or the key word AS must be present, followed by a column
  definition list in the form ...

Bold emphasis mine.

So, while all of these queries work:

SELECT '(1,2013-11-11)'::t;
SELECT ('(1,2013-11-11)'::t).*;
SELECT f();                      -- returning anonymous record
SELECT * FROM f() AS f(id int, d date);

Neither of these do:

SELECT * FROM f();
SELECT * FROM f()::t;

The latter raising an exception:

  ERROR:  cannot cast type record to t

You could wrap the SELECT with column definition list into a VIEW or function like @a_horse and @deszo suggested. That would work just fine:

CREATE OR REPLACE VIEW v1 AS
SELECT * FROM f() AS f(id int, d date);

But that wouldn't answer your question:

  convert the record (or setof record return type) into the T rowtype
  without listing the attributes?

Solution for single row

While a cast fails, an assignment in plpgsql works.

CREATE OR REPLACE function f1(OUT rec t) AS
$func$
BEGIN
rec := f();   -- assignment succeeds where cast failed (!)
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f1();

While you can also write a set returning function with that pattern I did not find a way to SELECT from a set returning function without supplying a column definition list ...

->SQLfiddle


Most suitable database design for multi store inventory?

This is not a challenge for one, modern RDBMS. You will realize you do not want to have one database per store the first time executive management asks for "How many iPhone 7s do we have on hand across all stores ahead of the new Apple launch?"
I have seen retail systems managing several hundred thousand SKUs across several hundred stores. The master inventory was one ~10 million row table with columns like:

Store ID
SKU
Units on hand

If you use partitioning on your table, queries can be sub-second.
Edit:
There is an open retail data model that handles multiple stores that you can see here:
http://www.omg.org/retail-depository/arts-odm-73/


Replay trace file and filter on error>0?

"I have captured a trace file using the replay template"

I believe this is your problem. Create a server-side trace using the stored procs rather than the replay template. Then execute:

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)

To see your columns. I used the same method as you and had exactly the same issue. There are other tools available to replay your workload outside of profiler, and I would recommend you look into SQLNexus or Distributed Replay.


Elevate transaction isolation in Maria DB

In this case we can acquire a range lock using SELECT FOR UPDATE even while in repeatable read isolation. The documentation is not very clear that it will provide a lock for the entire range or just the selected rows.

I tested it by first starting a transaction with a SELECT FOR UPDATE

START TRANSACTION;
SELECT * FROM `Foo` FOR UPDATE;

I purposely left the transaction hang. Then using a second connection attempted to INSERT INTO the locked table.

INSERT INTO `Foo` (`Bar`) VALUES ('Bar');

The insert would not complete until the transaction was committed.


Selecting entries with the same count as in another table

If I understand your requirement correctly, You can try something like this.

This will get count per ID from table1 and table2 and then return only those records where T1.ID count matches the second table

SELECT T1.ID FROM
(
    SELECT ID,Count(*) c
    From Table1 GROUP BY ID
) T1
INNER JOIN
(
     SELECT ID,Count(*) c
     From Table2 GROUP BY ID
) T2
ON T1.ID = T2.ID AND T1.c = T2.c


I don't have an easy way to test this theory right now, but I'd bet that there are some validation checks of the OPENROWSET parameters going on when the batch compiles (the whole batch, regardless of IF / ELSE and whether or not those blocks eventually get executed).
Whenever I encounter this problem, using dynamic SQL is usually a solution.  Give that a shot:
IF (SELECT COUNT(1) FROM  #listavarejo) > 0
        EXEC sys.sp_executesql N'SELECT *  FROM OPENROWSET(''Microsoft.ACE.OLEDB.15.0'',''Excel.0;Database=C:\folder\excelfile.xlsx'', [Planilha1$])';
ELSE
        RAISERROR('NO FILES INSIDE FOLDER.',16,10);

This allows the code inside the dynamic SQL to be compiled only if it's executed.


Secure database access from particular applications

This is a prime reason why different applications should be using different principals to authenticate on.  If you use Login1 for 5 applications, then it would become a nightmare to manage security if you ever need those 5 different applications to have different security schemes.

My recommendation?  Have a separate login for both of those applications.  And then grant the specific permission sets to each of those logins (either directly or through roles).

It is worth noting that a common solution to your problem would be by implementing logic to check for the application name that is passed to SQL Server, but I'm not even going to expand on that solution because it is very easily circumvented.  You can basically tell SQL Server that you are Application1 in an ad hoc manner.  That approach isn't secure whatsoever.  Because of that, I don't recommend you implement this logic.


It is very recommendable to do a dump in another media of all your database before trying anything else, because your filesystem is probably not going well.

You will need to restart the server and run fsck not only because of that file is corrupted, but because the risk of more data corruption, then try to run sqlplus again, if it does not help, you will need the file libclntsh.so.12.1 for an another installation.

But dont try to simple replace the file, it is not a good idea, first you will need to run fsck to ensure the file system is ok or something worse can happen.


Where to start to understand a unknown database

Your best bet to start is to document your database using SQL Power Doc

SQL Server & Windows Documentation Using Windows PowerShell
SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2014, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 R2 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.

Note: I have used it and it will give you a really good start in documenting and understanding your database server instance.