Will an index that is a partial key of the begining of another index perform any better than the longer index?

There really isn't even any guarantee that the optimizer will use the index in the first place.  The difference of only one column between the two indexes is (for the most part) trivial.  But if it did, any performance gain you would see (if any) would be trivial.

The reason for this is in how the B-Tree index is implemented by SQL Server.  Both indexes are equally capable of satisfying the predicate (WHERE clause) and therefore locating only the rows that meet the search parameters.  The only difference between the two would be how many pages in the index SQL Server would have to read in order to locate the required rows.  And how many pages will ultimately be determined by the size of the index rows (Storage size in bytes of (TransactionDate, ClientID) vs. the alternative, with only the size of the State column being the difference between the two.

Just as an example, if a single index row was 60 bytes, SQL Server would only have to read 2 pages in order to locate a row up until there were around 2.5 million rows in the index.  Then it would only require 3 pages to be read until the table reached more than 300 million rows.

So, what does that mean?  Whether it is looking through an index of 2 or all 3 columns, SQL Server is still only looking through the same number of pages to locate the rows, unless the State column is so large as to create a significant difference in the size of a single row, thus causing the index pages to fill considerably faster.  Any noticeable performance difference would only be caused if SQL Server had to read through more levels of the index to satisfy the query (3 levels vs. 4 levels, etc) and a single column of text, especially if State is a 2 char state abbreviation, simply won't be enough to make a significant difference.

If you really want to boost performance of the query, and the table is quite large, you might be better off exploring table partitioning, or if not on Enterprise Edition perhaps a partitioned view, all depending on whether there are certain ranges that are primarily searched and others that are searched much less often.


Does a numeric field always use the same disk space?

MySQL's integers take 4 bytes, regardless of the value you store.  (Data storage requirements)

MyISAM supports compression, but only with a compressed row format, which makes the table read-only. I don't think I've ever worked on an application where a read-only table was appropriate.


Numbering a rows positional index relative to its siblings within a parent context

In SQL Server and other systems that support ROW_NUMBER...

SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY GrpID
        ORDER BY PKID
    ) AS Idx
FROM YourTable;


ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

I wouldn't want to have 200 data flows in a single package. The time it'd take just to open up and validate would make you old before your time.

EzAPI is fun but if you're new to .NET and SSIS, oh hell no, you don't want that. I think you'll spend far more time learning about the SSIS object model and possibly dealing with COM than actually getting work done.

Since I'm lazy, I'll plug BIML as a free option you didn't list. From an answer on SO https://stackoverflow.com/questions/13809491/generating-several-similar-ssis-packages-file-data-source-to-db/13809604#13809604

Biml is an interesting beast. Varigence will be happy to sell you a license to Mist but it's not needed. All you would need is BIDSHelper and then browse through BimlScript and look for a recipe that approximates your needs. Once you have that, click the context sensitive menu button in BIDSHelper and whoosh, it generates packages.

I think it might be an approach for you as well. You define your BIML that describes how your packages should behave and then generate them. In the scenario you describe where you make a change and have to fix N packages, nope, you fix your definition of the problem and regenerate packages.

Or if you've gained sufficient familiarity with the framework then use something like EzAPI to go and fix all the broken stuff. Heck, since you've tagged this as 2005, you could also give PacMan a try if you're in need of making mass modifications to existing packages.

SSIS Design considerations

Generally speaking, I try to make my packages focus on solving a single task (load sales data). If that requires 2 data flows, so be it. What I hate inheriting is a package from the import export wizard with many un-related data flows in a single package. Decompose them into something that solves a very specific problem. It makes future enhancements less risky as the surface area is reduced. An additional benefit is that I can be working on loading DimProducts while my minion is dealing with loading SnowflakeFromHell package.

Then use master package(s) to orchestrate the child work flows. I know you're on 2005 but SQL Server 2012's release of SSIS is the cat's pajamas. I love the project deployment model and the tight integration it allows between packages.

TSQL vs SSIS (my story)

As for the pure TSQL approach, in a previous job, they used a 73 step job for replicating all of their Informix data into SQL Server. It generally took about 9 hours but could stretch to 12 or so. After they bought a new SAN, it went down to about 7+ hours. Same logical process, rewritten in SSIS was a consistent sub 2 hours. Easily the biggest factor in driving down that time was the "free" parallelization we got using SSIS. The Agent job ran all of those tasks in serial. The master package basically divided the tables into processing units (5 parallel sets of serialized tasks of "run replicate table 1", table 2, etc) where I tried to divide the buckets into quasi equal sized units of work. This allowed the 60 or so lookup reference tables to get populated quickly and then the processing slowed down as it got into the "real" tables.

Other pluses for me using SSIS is that I get "free" configuration, logging and access to the .NET libraries for square data I need to bash into a round hole. I think it can be easier to maintain (pass off maintenance) an SSIS package than a pure TSQL approach by virtue of the graphical nature of the beast.

As always, your mileage may vary.


You can remove /p:Storage=File. This worked for me.

/p:Storage=File : is used to redirect the backing storage for the schema model used during extraction, this helpful with large databases that may cause out-of-memory exception if the default memory location is used.

If this is not an issue for you, just omitting the parameter should fix your issue.


SQL Server 2012 Service Master Key Regenerate

In order to regenerate your master key, you need to open it with the old password.  Then, once it's open, you can use the REGENERATE syntax to change it to a new password:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '0ldP@55w0rd';

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'N3wP@55w0rd';

CLOSE MASTER KEY;

Which is why you should always have your encryption passwords safely secured.  Without them, you could be in a place where you can't decrypt your databases.


Let's say you have a database named wildlife, kick the users off and detach the database.

This can be done via the SSMS GUI, right click, under all tasks.

-- Kick off users, roll back current work
ALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Detach database
sp_detach_db 'WILDLIFE'
GO

However, the *.mdf and *.ldf files are still there.  Shift delete them so that they do not go to the recycle bin.

Good luck!

PS:  Check out my blog for more information.
http://craftydba.com/?p=1753


How to pick a chunk from the text column in postgresql

create table abc (id int primary key, name text);
insert into abc values
(1, 'Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) **56 tablet** 4 x 14 tablets'),
(2, 'Zocor 10mg tablets (You & Me Ltd) **28 tablet** 2 x 14 tablets'),
(3, 'Atenolol 100mg tablets (NoName Pharmaceuticals Ltd) **20 tablet** 2 x 10 tablets');

If there is a pattern, for example: (1 or more digits) plus the word tablet, you can use a regular expression like this:
select
    substring(name, '([0-9]+ tablet)')
from
    abc;

| substring |
| :-------- |
| 56 tablet |
| 28 tablet |
| 20 tablet |

db<>fiddle here


Create new function by code if it doesn't exist

Update Jan 2017 - SQL Server 2016+ / Azure SQL Database

SQL Server 2016 and the current version of Azure SQL Database now has the following syntax for functions, procedures, tables, databases, etc. (DROP IF EXISTS):

DROP FUNCTION IF EXISTS dbo.fn_myfunc;

And SQL Server 2016 Service Pack 1 adds even better functionality for modules (functions, procedures, triggers, views) which means no losing of permissions or dependencies (CREATE OR ALTER):

CREATE OR ALTER FUNCTION dbo.fn_myfunc ...

Both of these syntax enhancements can lead to much simpler scripts used for source control, deployments, etc.

But, if you're using...

Older versions

You need to do what SQL Server does when you script this from Management Studio:

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'FN' AND name = 'fn_myfunc')
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'CREATE FUNCTION ...';
    EXEC sp_executesql @sql;
END

Or you can say:

BEGIN TRY
    DROP FUNCTION dbo.fn_myfunc;
END TRY
BEGIN CATCH
    PRINT 'Function did not exist.';
END CATCH
GO
CREATE FUNCTION...

Or you can just say:

DROP FUNCTION dbo.fn_myfunc;
GO
CREATE FUNCTION...

(Here you will get an error message if the function doesn't already exist, but the script will continue from the next GO, so whether the drop worked or not, the function will still be (re-)created.)

Note that if you drop the function and re-create it, you will lose permissions and potentially dependency information as well.


PROPOSED SOLUTION

Using the IF() function to check for consecutive num values

SET @x = 0;
SET @n = 0;
SELECT num,consecutive FROM
(SELECT
    id,num,
    (@x:=IF(num=@n,@x+1,1)) consecutive,
    (@n:=num) inc
FROM logs) A
WHERE consecutive >= 3;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS tonychen;
CREATE DATABASE tonychen;
USE tonychen
CREATE TABLE logs
(
  id INT NOT NULL AUTO_INCREMENT,
  num INT NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
SELECT * FROM logs;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS tonychen;
Query OK, 1 row affected (0.25 sec)

mysql> CREATE DATABASE tonychen;
Query OK, 1 row affected (0.00 sec)

mysql> USE tonychen
Database changed
mysql> CREATE TABLE logs
    -> (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   num INT NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

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

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

mysql> SELECT num,consecutive FROM
    -> (SELECT
    ->     id,num,
    ->     (@x:=IF(num=@n,@x+1,1)) consecutive,
    ->     (@n:=num) inc
    -> FROM logs) A
    -> WHERE consecutive >= 3;
+-----+-------------+
| num | consecutive |
+-----+-------------+
|   1 |           3 |
+-----+-------------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


SQL Server Compact - Will it perform well with heavier traffic sites?

I have not used SQL Server Compact, but because of its design, there are warnings in the SQL Server 2012 documentation about concurrency issues.

See: http://technet.microsoft.com/en-us/library/ms171845(v=sql.110).aspx  for warnings on:

Lost updates.
Inconsistent analysis (nonrepeatable reads).
Phantom reads.

If you need a free and much more functional version of SQL Server, consider using SQL Server Express.  Its limits are considerable above the Compact edition.  You can lookup its limitations.


Don't overlook the "weird error lookup" tools. Most of the generic errors in oracle (i.e. ora-00600) have a lookup tool in Metalink that given the right parameters from the dump may actually help you more than you would realize.


You can find the min value for RecordTime where there are 6 consecutive rows by using a correlated query doing top(6) and then count the number of rows that is greater than or equal to 200.

Use that min value for RecordTime against the table to get the 6 consecutive rows that you want.

-- Setup table to test on
declare @T table
(
  RecordTime datetime primary key,
  ValueAsReal decimal(7, 4)
);

-- Add some data
insert into @T(RecordTime, ValueAsReal)
select '2015-11-30 12:59:00',  195.6907 union all
select '2015-11-30 13:00:00',  203.8667 union all
select '2015-11-30 13:01:00',  198.0907 union all
select '2015-11-30 13:02:00',  198.3573 union all
select '2015-11-30 13:03:00',  208.064  union all
select '2015-11-30 13:04:00',  211.3066 union all
select '2015-11-30 13:05:00',  219.36   union all
select '2015-11-30 13:06:00',  233.4453 union all
select '2015-11-30 13:07:00',  232.8106 union all
select '2015-11-30 13:08:00',  217.872  union all
select '2015-11-30 13:09:00',  214.464  union all
select '2015-11-30 13:10:00',  200.0587 union all
select '2015-11-30 13:11:00',  173.776  union all
select '2015-11-30 13:12:00',  181.1787;

-- Desired number of rows
declare @NumRows int;
set @NumRows = 6;

-- Min value to look for
declare @ValueAsReal decimal(7, 4);
set @ValueAsReal = 200;

-- Get the first @NumRows rows where RecordTime is greater than or equal to
-- the min value for RecordTime where there are 6
-- consecutive values for ValueAsReal greater than or equal to @ValueAsReal
select top(@NumRows)
  T.RecordTime,
  T.ValueAsReal
from @T as T
where T.RecordTime >= (
                      select top(1) T1.RecordTime
                      from @T as T1
                      where (
                            select count(*)
                            from (
                                 select top(@NumRows)
                                   T2.RecordTime,
                                   T2.ValueAsReal
                                 from @T as T2
                                 where T1.RecordTime <= T2.RecordTime
                                 order by T2.RecordTime
                                 ) as T3
                            where T3.ValueAsReal >= @ValueAsReal
                            ) = @NumRows and
                            T1.ValueAsReal >= @ValueAsReal
                      order by T1.RecordTime
                      )
order by T.RecordTime;

Query plan:


Statistics needs to be captured for query analysis

I assume you are talking about the same query, executing with different indexes.  You can't just arbitrarily compare 2 totally different queries that do 2 different things.  As Aaron stated in the comments, I can compare my 2001 VW to a 2015 Camaro and there will be no correlation, the Camaro may go faster but that's because it has a bigger engine.  Conversely, my VW will get better gas mileage because the engine is smaller.

That being said, I use the SET options: SET STATISTICS TIME, IO ON

Once those are on, the query will kick out all the CPU usage and reads that have been performed.  See below:

I then use www.statisticsparser.com to consolidate the data into an easy to read format.  That takes care of CPU/IO.

Execution plan can be pulled from the "Get Actual Execution plan" option in SSMS .  The execution plan will also have the memory grant (how much memory is allocated to the query).


Return Most Recent Entry But Sum One Field

You can use a combination of ROW_NUMBER and SUM() OVER():

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY custid
                                    ORDER BY rdsdate),
            total_amt = SUM(amt) OVER(PARTITION BY custid)
    FROM @Helper1
)
SELECT  name,
        inputid,
        custid,
        rdsdate,
        total_amt
FROM CTE
WHERE RN = 1;


Lots of "FETCH API_CURSOR0000..." on sp_WhoIsActive ( SQL Server 2008 R2 )

It's a simple select. Why is this using fetch_cursor?

The SELECT is system-generated by the Distributed Query framework, and is associated with the UPDATE you found.

The Remote Update query plan operator uses the sp_cursor model to fetch rows from the remote data source. This is the cause of all the cursor API calls.

I believe the cursor plan you show in your question is the internal cursor opened by the engine as part of this process, but I have not had time to try to reproduce this yet.


Understanding how to choose between fields and tags in InfluxDB

I just read a tutorial that said fields are data and tags are metadata. That is a very intuitive definition.

The example had pressure and temperature fields and a weather station tag. Again, crystal clear and perfectly matches the description.

Unfortunately, they then said that if you are querying on pressure or temperature and not weather station, you should flip the field and tag designations around. In other words the definitions provided for field and tag are meaningless.

The simple solution is to stipulate that fields can either be indexed or not indexed. Fields that are indexed are called tags. Use tag when you need to index a field (to dramatically improve query speed for example).


Error when restoring Postgres Database on RDS

These errors are harmless and can be ignored.

They stem from COMMENT ON EXTENSION statements in the dump.


1. Why Statistics IO show higher reads than Profiler?

No idea, sorry. There are often differences because they are measuring different things as alluded to in the knowledge base article. There is no additional documentation on this that I am aware of. You might be able to infer some things through detailed testing, but there's really no guarantee whatever you find will remain consistent across versions and builds. The intended behaviour is inconsistent enough, before accounting for possible bugs.

2. What is difference between "Worktable" and "Workfile"

Both are internal objects; otherwise it is pretty much what the names suggest: A work table has a table-like structure, a work file is file-like. The detailed structure is not visible, though some broad features can be discerned by inspecting their methods and following their execution paths with a debugger.

3. Why there is "Worktable" in this particular case?

A work table is always needed for a (row mode) hashing operation. It is used internally in distributing the input into hash partitions (an overloaded term, not related to table partitioning) and to keep track of status. I have never seen a hashing work table report anything other than zero in statistics output, but then I've never really looked into it.

4. What "Physical reads" on Workfile means?

Work files are part of the mechanism employed when a hash partition spills. Though not documented, the physical and read-ahead reads occur when the execution engine retrieves spilled hash partitions from the work files.

5. One "workfile" means exactly one table that is used for one purpose?

As I recall, there may be multiple instances. There is no way I know of to relate a particular STATISTICS IO line to a particular object or plan node. This is a long-standing limitation. There may be a change to this in SQL Server 2016, though I have not tested to see if it applies to internal temporary objects.

Ultimately, there's not that much useful to be learned from STATISTICS IO output for work files and work tables beyond that which you can learn by looking at other information in a post-execution plan (or via DMVs, Extended Events etc.). I apologise for the incomplete answer, but it's the best I can offer off the top of my head.


Running the following command retrieves the body of a mysql event:

SELECT EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_name'


SQL Server to Oracle Export - Data conversion error

I tried your script and recreated your environment completely and i didn't see any error.

Ran perfectly and created all tables and transferred all data successfully

i tried running under a sql agent job and still no error was generated

but i guess you can update your insert script by making sure the column gets converted under all conditions

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END

or

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE Null END

change your script to

INSERT INTO OPENQUERY (
    ZMMS_ORA
    'SELECT
        "COMPANYID",
        "BO_ID",
        "AS CM",
        "AS RSM",
        "AS SE",
        "TS CM",
        "TS RAE",
        "TS RSM",
        "TS SE",
        "BBONE AM",
        "ASM",
        "WCT ASM",
        "LS TSM",
        "WCT TSM",
        "TS TSM",
        "AS PM",
        "AS DEV",
        "TS PM",
        "TS DEV",
        "TS TECH",
        "BB EXEC",
        "CHM",
        "ANGEL Client Manager (CM)",
        "ANGEL Engagement Team",
        "BbOne Client Manager (CM)",
        "Blackboard Executive",
        "CE/Vista Hosting Mgr (ASM)",
        "CE/Vista Tech Support (TSM)",
        "Complex Hosting Manager (CHM)",
        "Learn Client Manager (CM)",
        "Learn Hosting Mgr (ASM)",
        "Learn Reg Sales Mgr (RSM)",
        "Learn Sales Engineer (SE)",
        "Learn Services Developer",
        "Learn Services ICM Mgr",
        "Learn Services Project Manager",
        "Learn Tech Support (TSM)",
        "Transact Client Manager (CM)",
        "Transact Project Manager",
        "Transact Reg Acct Exec (RAE)",
        "Transact Reg Sales Mgr (RSM)",
        "Transact Sales Engineer (SE)",
        "Transact Services Technician",
        "Transact Tech Support (TSM)",
        "Xythos Tech Support (TSM)"  FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
    )
SELECT "COMPANYID"
    ,[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
    ,"AS CM"
    ,"AS RSM"
    ,"AS SE"
    ,"TS CM"
    ,"TS RAE"
    ,"TS RSM"
    ,"TS SE"
    ,"BBONE AM"
    ,"ASM"
    ,"WCT ASM"
    ,"LS TSM"
    ,"WCT TSM"
    ,"TS TSM"
    ,"AS PM"
    ,"AS DEV"
    ,"TS PM"
    ,"TS DEV"
    ,"TS TECH"
    ,"BB EXEC"
    ,"CHM"
    ,"ANGEL Client Manager (CM)"
    ,"ANGEL Engagement Team"
    ,"BbOne Client Manager (CM)"
    ,"Blackboard Executive"
    ,"CE/Vista Hosting Mgr (ASM)"
    ,"CE/Vista Tech Support (TSM)"
    ,"Complex Hosting Manager (CHM)"
    ,"Learn Client Manager (CM)"
    ,"Learn Hosting Mgr (ASM)"
    ,"Learn Reg Sales Mgr (RSM)"
    ,"Learn Sales Engineer (SE)"
    ,"Learn Services Developer"
    ,"Learn Services ICM Mgr"
    ,"Learn Services Project Manager"
    ,"Learn Tech Support (TSM)"
    ,"Transact Client Manager (CM)"
    ,"Transact Project Manager"
    ,"Transact Reg Acct Exec (RAE)"
    ,"Transact Reg Sales Mgr (RSM)"
    ,"Transact Sales Engineer (SE)"
    ,"Transact Services Technician"
    ,"Transact Tech Support (TSM)"
    ,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;


SQL Server If statement assigning values

At a really basic level it would look like this

UPDATE TableName
SET [level] = CASE WHEN AcctNo = '1' THEN '1'
                    WHEN AcctNo = '2' THEN '2'
                    ELSE 'DefaultVal' END

or

UPDATE TableName
SET [level] = CASE AcctNo WHEN '1' THEN '1'
                    WHEN '2' THEN '2'
                    ELSE 'DefaultVal' END

Depending on if you only need the one column in your conditions or if it's more complicated than that.

Edit  On looking at your expected data it looks like what you want is this:

WITH Levels AS (SELECT AcctNo, Level,
        MIN(CASE WHEN code IN (1,2,3,4,5) THEN code ELSE NULL END)
                OVER (PARTITION BY AcctNo) AS MinCode
        FROM Test)
UPDATE Levels
SET level = MinCode

Note: This assumes that you want the minimum code for a given AcctNo.

If you just want a select statement to test then change it to this:

WITH Levels AS (SELECT AcctNo, Code, Charge, Level,
        MIN(CASE WHEN code IN (1,2,3,4,5) THEN code ELSE NULL END)
                OVER (PARTITION BY AcctNo) AS MinCode
        FROM Test)
SELECT * FROM Levels


Using PHP form to insert geometry from WKT and SRID into SQL Server 2017

The answer was provided by Zhorov in Stack Overflow here

Reposting the answer here in the hopes it will benefit someone:

You should include geometry::STGeomFromText in your T-SQL statement and bind the value for $wkt parameter without using PDO::quote:

<?php
    if ($_SERVER['REQUEST_METHOD']=="POST") {
        $wkt = $_POST['wkt'];
        $srid = $_POST['srid'];
        $name = $_POST['name'];
        try {
            $sql = "
               INSERT INTO Upload_WKT_Test (Name, GeomCol1)
               VALUES (:name, geometry::STGeomFromText(:wkt, :srid))";
            $stmnt = $pdo->prepare($sql);
            $theData = [':name'=>$name, ':wkt'=>$wkt, ':srid'=>$srid];
            $stmnt->execute($theData);
        } catch(PDOException $e) {
            echo "Error: ".$e->getMessage();
        }
    } else {
        $wkt="";
        $alignment="";
        $srid="";
    }
?>


I had the same exact problem - I had a new user which wanted to add jobs. I added this user to msdb db and added him to sqlAgentOperatorRole and db_datareader, db_datawriter. Still, when trying to add a new job, the user was getting the same error:

  Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft Server, Error: 15517)

I traced it down and it looks like when trying to add a new job (or doing many other things...) the following query is run:

exec master.dbo.sp_Msdbuserpriv 'serv'

So, obviously I had to add the new user to Master as well:

use master
create user [youNewUser] for login [yourNewUserLogin]
alter user [youNewUser] with default_schema=[dbo]

And now it works like charm...


Is it safe to use "NOT BLOCKING" option on HSQL backup?

Yes, you can assume it is safe to use NOT BLOCKING as a valid means of backing up your HSQLDB.

I don't believe the backup would contain "partial" transactions, more likely all versions encountered during the backup operations, hence the larger backup size and more complicated restore process.


How can we get table name from inside trigger?

This might not be the best approach to dealing for your problem, but it will certainly achieve the stated goal.

ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON  [dbo].[Projects]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    declare @TableName sysname
    select @tablename = object_schema_name(parent_id) + '.' + object_name(parent_id)
    from sys.triggers where object_id = @@PROCID

    exec dbo.myProcedure @Tablename
END
GO

A better option may be to change the way the trigger is created, and putting the name down manually (if it fits your scenario), for e.g.:

declare @tablename sysname, @sql nvarchar(max)
set @tablename = '[dbo].[myTableName]'
set @sql = 'ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON  ' + @tablename + '
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    exec dbo.myProcedure ' + @tablename + '
END
GO'
exec sp_executeSQL @sql


Postgres ignoring GIN index for LIKE query?

If you want to index for LIKE which are not prefix queries, or for ILIKE queries, then you should look into the pg_trgm extension, giving an index that would look like:

"title_pty_add_15f367_gin" gin (pty_addr gin_trgm_ops)

Also, there have been substantial improvements since 9.4.  You will be doing yourself a favor to upgrade to at least 9.6.


There isn't anything you can do to improve the failover time, but you can add a read replica to have read access to your data while your instance is recovering.

If you aren't locked down to MySQL, you should also try the Postgres version of Cloud SQL - it uses a faster method for recovery and from my personal experience is generally down for ~30-60 seconds. Each version has it's own page on HA, which talks about how they work: MySQL, Postgres


Date dimensions are pretty standard in a data warehouse, and are highly recommended by Kimball as most facts tie to a date. Typically, the key is an integer.  It can be a meaningless surrogate key, or it can be a "smart" key where the integer is in the form yyyymmdd; e.g., the key for August 2, 2014 would be 20140802.

Date dimensions provide a set of contiguous dates in multiple formats and allow you to do date calculations once rather than in each query. They make it very easy to do time period comparisons.  You can add other fields that could be analytically relevant such as holidays, indicators of work days, fiscal calendars (where different from standard calendar years).

There are lots of scripts available online to create and populate a date dimension.  Many tools will create the date dimension for you. I'm not sure what you are using for your underlying data source, so here are a couple of examples of date dimensions.  Hopefully you can convert these to the appropriate format for your needs.

T-SQL Date Dim
Oracle Date Dim
MySQL Date Dim
Excel Date Dim


You can tune performance using Parameter Groups. Some (unfortunately, not all) variables can be configured there.

Also, if you used AWS Database Migration make sure you have all the keys you need because they're not transferred by default.


How can I eliminate this scalar function or make it faster?

Rewrite the scalar function as an in-line table-valued function, or upgrade to SQL Server 2019, which will automatically perform that translation for you.

Machine Translation
The following is the most direct T-SQL representation of the in-lined function produced by SQL Server 2019 for your scalar user-defined function. One wouldn't rewrite the function this way; I present it for its educational and entertainment value:
DROP FUNCTION IF EXISTS dbo.ufn_StockUomQuantityToOrder;
GO
CREATE FUNCTION dbo.ufn_StockUomQuantityToOrder
(
    @priorityLevel decimal(38, 10),
    @monthlyUsageRate decimal(38,10),
    @minimumPurchaseUomOrderQuantity decimal(38, 10),
    @purchaseUomConversion decimal(38, 10),
    @orderFrequencyDays decimal(38,10),
    @boxQuantity int
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Node0.Expr1019
    FROM
    (
        SELECT Expr1002 = CONVERT(decimal(38,10), NULL)
    ) AS Node7
    CROSS APPLY
    (
        SELECT Node10.Expr1007
        FROM
        (
            SELECT Expr1006 =
                CASE
                    WHEN @orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL
                    THEN 1
                    ELSE 0
                END
        ) AS Node9
        OUTER APPLY
        (
            SELECT Expr1007 =
                CASE
                    WHEN Node9.Expr1006 = 1
                    THEN CONVERT(decimal(38,10),@monthlyUsageRate * @orderFrequencyDays/(30.0),0)
                    ELSE Node7.Expr1002
                END
        ) AS Node10
    ) AS Node6
    CROSS APPLY
    (
        SELECT
            Expr1004 = CONVERT(decimal(38,10), -(1.0 * @monthlyUsageRate * @priorityLevel), 0),
            Expr1005 = CONVERT(decimal(38,10), @minimumPurchaseUomOrderQuantity * @purchaseUomConversion, 0),
            Node6.Expr1007
    ) AS Node5
    CROSS APPLY
    (
        SELECT Node13.Expr1009
        FROM
        (
            SELECT Expr1008 =
                CASE
                    WHEN Node5.Expr1004 IS NULL OR Node5.Expr1004 < Node5.Expr1005
                    THEN 1
                    ELSE 0
                END
        ) AS Node12
        OUTER APPLY
        (
            SELECT Expr1009 =
                CASE
                    WHEN Node12.Expr1008 = 1
                    THEN Node5.Expr1005
                    ELSE Node5.Expr1004
                END
        ) AS Node13
    ) AS Node4
    CROSS APPLY
    (
        SELECT Node16.Expr1011
        FROM
        (
            SELECT Expr1010 =
                CASE
                    WHEN Node4.Expr1009 IS NULL OR Node4.Expr1009 < Node5.Expr1007
                    THEN 1
                    ELSE 0
                END
        ) AS Node15
        OUTER APPLY
        (
            SELECT Expr1011 =
                CASE
                    WHEN Node15.Expr1010 = 1
                    THEN Node5.Expr1007
                    ELSE Node4.Expr1009
                END
        ) AS Node16
    ) AS Node3
    CROSS APPLY
    (
        SELECT Expr1012 = CONVERT(int, CEILING(Node3.Expr1011), 0)
    ) AS Node2
    CROSS APPLY
    (
        SELECT Node17.Expr1016
        FROM
        (
            SELECT Expr1013 =
                CASE
                    WHEN Node2.Expr1012 IS NOT NULL AND @boxQuantity > 0
                    THEN 1
                    ELSE 0
                END
        ) AS Node18
        OUTER APPLY
        (
            SELECT Node19.Expr1016
            FROM
            (
                SELECT Expr1014 =
                    CASE
                        WHEN Node18.Expr1013 = 1
                        THEN Node2.Expr1012 % @boxQuantity
                        ELSE NULL
                    END
            ) AS Node20
            CROSS APPLY
            (
                SELECT Node21.Expr1016
                FROM
                (
                    SELECT Expr1015 =
                        CASE
                            WHEN Node20.Expr1014 <> 0
                            THEN 1
                            ELSE 0
                        END
                ) AS Node22
                OUTER APPLY
                (
                    SELECT Expr1016 =
                        CASE
                            WHEN Node18.Expr1013 = 1 AND Node22.Expr1015 = 1
                            THEN Node2.Expr1012 - Node20.Expr1014 + @boxQuantity
                            ELSE Node2.Expr1012
                        END
                ) AS Node21
            ) AS Node19
        ) AS Node17
    ) AS Node1
    CROSS APPLY
    (
        SELECT
            Expr1019 = CONVERT(int, Node1.Expr1016, 0)
    ) AS Node0;
GO

Example call:
DECLARE
    @priorityLevel decimal(38, 10),
    @monthlyUsageRate decimal(38,10),
    @minimumPurchaseUomOrderQuantity decimal(38, 10),
    @purchaseUomConversion decimal(38, 10),
    @orderFrequencyDays decimal(38,10),
    @boxQuantity int

SELECT
    stockUomQuantityToOrder = FN.Expr1019
FROM dbo.ufn_StockUomQuantityToOrder
(
    @priorityLevel,
    @monthlyUsageRate,
    @minimumPurchaseUomOrderQuantity,
    @purchaseUomConversion,
    @orderFrequencyDays,
    @boxQuantity
) AS FN;

The execution plan is:

Human Translation
This human would rewrite your scalar function as:
CREATE FUNCTION dbo.ufn_StockUomQuantityToOrder
(
    @priorityLevel decimal(38, 10),
    @monthlyUsageRate decimal(38,10),
    @minimumPurchaseUomOrderQuantity decimal(38, 10),
    @purchaseUomConversion decimal(38, 10),
    @orderFrequencyDays decimal(38,10),
    @boxQuantity int
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        stockUomQuantityToOrder = Step6.quantityToOrderInt
    FROM
    (
        SELECT
            --set the default order quantity
            quantityToOrderDecimal =
                CONVERT
                (
                    decimal(38,10),
                    -1.0 * @monthlyUsageRate * @priorityLevel
                ),
            --get the minimum order quantity in stock UOM
            minimumStockUomOrderQuantity =
                CONVERT
                (
                    decimal(38,10),
                    @minimumPurchaseUomOrderQuantity * @purchaseUomConversion
                ),
            --calculate the order frequency minimum
            orderFrequencyMinimumQuantity =
                CONVERT
                (
                    decimal(38,10),
                    IIF
                    (
                        @orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL,
                        @monthlyUsageRate * @orderFrequencyDays / 30.0,
                        NULL
                    )
                )
    ) AS Step1
    CROSS APPLY
    (
        SELECT
            --do we need to meet a vendor minimum
            quantityToOrderDecimal =
                IIF
                (
                    Step1.quantityToOrderDecimal IS NULL OR Step1.quantityToOrderDecimal < Step1.minimumStockUomOrderQuantity,
                    Step1.minimumStockUomOrderQuantity,
                    Step1.quantityToOrderDecimal
                ),
            Step1.orderFrequencyMinimumQuantity
    ) AS Step2
    CROSS APPLY
    (
        SELECT
            --do we need to meet an order frequency minimum
            quantityToOrderDecimal =
                IIF
                (
                    Step2.quantityToOrderDecimal IS NULL OR Step2.quantityToOrderDecimal < Step2.orderFrequencyMinimumQuantity,
                    Step2.orderFrequencyMinimumQuantity,
                    Step2.quantityToOrderDecimal
                )
    ) AS Step3
    CROSS APPLY
    (
        SELECT
            --convert to the int
            quantityToOrderInt = CAST(CEILING(Step3.quantityToOrderDecimal) AS integer)
    ) AS Step4
    CROSS APPLY
    (
        SELECT
            Step4.quantityToOrderInt,
            --get the partial box quantity if any
            partialBox = CONVERT(integer, Step4.quantityToOrderInt % @boxQuantity)
    ) AS Step5
    CROSS APPLY
    (
        SELECT
           --did we come up with a number that needs to be an increment of boxQuantity
           quantityToOrderInt =
           IIF
           (
                Step5.quantityToOrderInt IS NOT NULL AND @boxQuantity > 0,
                --remove the partial box and add a full one (if we are not of box increments)
                IIF
                (
                    Step5.partialBox != 0,
                    Step5.quantityToOrderInt - Step5.partialBox + @boxQuantity,
                    Step5.quantityToOrderInt
                ),
                Step5.quantityToOrderInt
            )
    ) AS Step6;

The original view in the question becomes:
SELECT
    VPP.vendorpartid,
    FN.stockUomQuantityToOrder
FROM dbo.vendorpartpriority AS VPP
CROSS APPLY dbo.ufn_StockUomQuantityToOrder
(
    VPP.priorityLevel,
    VPP.monthlyUsageRate,
    VPP.minimumPurchaseUomOrderQuantity,
    VPP.purchaseUomConversionFactor,
    VPP.orderFrequencyDays,
    VPP.boxQuantity
) AS FN;


Large No of MySQL running threads killing response times

Query cache off -- good

Large history list -- Innodb stumbling over itself.

Latency suddenly shot up -- ditto

Thread count suddenly shot up -- ditto.

Possible causes:

A long-running UPDATE or ALTER blocking lots of other threads.  Or even a SELECT that was poorly written.  If the slowlog was on, you may be able to discover what it was.  long_query_time = 0.5 implies that (if the slowlog was turned on) it will have caught the naughty query.  And there would be a lot of normally-fast queries in the log, too.
Too many threads.  What is "too many"?  When this happens.  How to control it?  Decrease the limit on number of connections, either in the clients, or with max_connections.  I know that sounds like a bad remedy, but when MySQL gets into the state you described, it may take a restart to uncork it.  Sharing resources among 300-400 threads means that no one will 'ever' get finished.  How many clients?  Web server(s)?  What is the limit on each?  Example: Apache's MaxClients.
Swapping.  Do you have any monitoring that would say that there was swapping? 90G/128G sounds fine, but perhaps something else was eating into RAM.  Swapping is terrible for MySQL.


You can keep taking log backups on the primary server during full backup and restore to secondary. When you set up log shipping restore full with no recovery and all the log backups (taken during full backup restore) with no recovery.
When you are caught up you initialize log shipping.


Return Results From Select Statement and Store Count In Variable

You would need to select the @@ROWCOUNT variable

CREATE TABLE dba_152082 (field1 int NOT NULL);

INSERT INTO dba_152082 (field1) VALUES (1),(1), (2);

DECLARE @numberofrows int;

SELECT * FROM dba_152082;

SELECT @numberofrows = @@ROWCOUNT;

PRINT @numberofrows;

DROP TABLE dba_152082;

You should be able to verify the value of your variable in the messages tab after you run that.


What does exchangevent indicate in SQL deadlock graph?

Exchange Events provide details and context on the deadlock itself. Specifically the WaitType e_waitPipeGetRow is a resource that one SELECT statement owns and another SELECT statement is waiting on, ergo being blocked by the first SELECT (for example due to resource contention). This StackOverflow answer provides more context on it and deadlocks specifically.
I'm not an expert on reading deadlock graph data, but my research seems to indicate that kind of WaitType is potentially related to intra-query parallelism deadlocks (as mentioned in the previously linked StackOverflow answer).
You may find SentryOne's higher level read on deadlocks helpful in understanding what's going on.


What are the side effects of raising the PROCESSES system parameter?

At least it has you have to increase most of the OS kernel parameters(like number of file handles). Most of them have a formula where use PROCESSES is an input. Also each server process allocates some RAM - the about is not big, but when multiplied by one thousand ...

Idle processes do not have any major overhead, but when DB server starts swapping RAM it's over. All the performance is away.


To capture client API transactions, add the TM: Begin Tran completed, TM: Commit Tran completed, and TM: Rollback Tran completed events in the trace along with the TransactionID column. Queries with the same TransactionID are part of the same transaction.

Note that Profiler is deprecated so I suggest you move to Extended Event tracing. The events are similarly named.

Use datetime2(7) to avoid loss of fractional second accuracy.


MySQL 5.5 to 5.7 doesn't work, apparently this is a feature?

The problem is that MySQL 5.5 does not support GTIDs, but MySQL 5.7 does and have it activated by default.

As the MySQL manual suggests, you can disable GTIDs on the 5.7 slaves to perform your tests, and later, when all your servers will be upgraded to 5.7, activate GTIDs on both master and slaves.

see http://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.html for more information.

  If you are upgrading an existing replication setup from a version of
  MySQL that does not support global transaction identifiers to a
  version that does, you should not enable GTIDs on either the master or
  the slave before making sure that the setup meets all the requirements
  for GTID-based replication. For example server_uuid, which was added
  in MySQL 5.6, must exist for GTIDs to function correctly. See Section
  18.1.3.2, “Setting Up Replication Using GTIDs”, which contains information about converting existing replication setups to use
  GTID-based replication.


Launch this command on OS prompt and will obtain:

[oracle@datab ~]$ oerr ORA 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.

You can try to increase values as help says, or go and take a look here, for example.


Reading CSV files located on LINUX server and updating the tables in SQL Server Database

I think there are at least two generic options for this kind of thing:

have a network share that the SQL Server host can connect to on the Linux server.
sync the file from Linux server to the DB host (by e.g. using a network share on the DB host, or any other way of copying files over network).

Remark: The way Windows handles mapped network drives, it might be that the login session running the DB processes would not see the drive if it was mapped by some other user account. Thus the first option is preferred (maybe also from the pov of dbas?).


Cardinality estimate outside the histogram

Based on my testing, the out-of-bounds cardinality estimate is simply the square root of the row count, bounded below by the number of added rows since the last stats update, and bounded above by the average rows per value.

In your case, 1,934.99 = SQRT(3744192)

Testing setup below:

--setup
USE TestDB
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS OFF
GO

DROP TABLE IF EXISTS dbo.Hist

CREATE TABLE dbo.Hist (
ID int identity primary key,
Num int
)

INSERT dbo.Hist
SELECT TOP 300
(ROW_NUMBER() OVER(ORDER BY(SELECT 1/0)))%3
FROM master..spt_values a
CROSS JOIN master..spt_values b

--Get estimated plan
--don't forget to run right after setup to auto-create stats
SELECT *
FROM dbo.Hist
WHERE Num = 1000

--gradually add rows, then rerun estimate above
INSERT dbo.Hist
SELECT TOP 100
-1
FROM master..spt_values a

--I sure hope you weren't testing this in prod (cleanup)
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
GO

Surprisingly even row estimates were generated from this approach:
20 at 400 total rows,
30 at 900,
40 at 1600, etc.

Past 10000 though, the row estimate maxes out at 100, which is the number of rows per value in the existing stats. Adding only 10 rows will set the estimate to 10, since sqrt(300) > 10.

Thus the estimates could be expressed using this formula:

Estimate = MIN(SQRT(AC), MIN(AR, MC))

Note that if statistics are sampled, then MC is not considered. So the formula becomes:

Estimate = MIN(SQRT(AC), AR))

Where

MC is the "modification count" (# of modifications since the statistics were created)
AC is the "adjusted cardinality" (# of rows from the statistics plus MC),
AR is the average rows per value (# of rows from the statistics divided by distinct values in the column)

The formulas for these estimates, and other details about the calculator, can be found in this blog post: Analyzing Estimates from the CSelCalcAscendingKeyFilter Calculator


Vendors will need to catch up, as the add-in model and API have changed. This will lead to better extensibility, but for now you'll need to wait for the vendors to do this work. There is usually not much advantage to doing all of that work while the product is still in beta and the add-in model is still changing (the version of SSMS released on Friday is on a completely different VS shell and is radically different, which vendors knew would happen, and that would have rendered any work done against earlier RCs and CTPs a waste).

Please wait for RTM before expecting all vendors to have perfect integration. If you need these add-ins in the meantime, keep a supported version of SSMS installed too.

Or, in the case of Plan Explorer, use a simple workaround - all our add-in really does is provide a shortcut to right-clicking a plan, saving as .sqlplan, and then double-clicking in file explorer or using file/open in Plan Explorer. Or better yet, generate actual plans inside Plan Explorer, where you get much more useful metrics anyway.


Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup

As Kin implied in the comments, this is a problem with communication between the primary and secondary nodes of your sync-mode availability group.

There are a number of different causes for this.  It's possible that the availability group failed over in the middle of the operation (which would cause the transaction to be rolled back and then that error occurs).

More generally, the error message:

  Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed

Means that the transaction log record associated with log sequence number 2599205:10500:111 either

couldn't be sent to the secondary, or
wasn't acknowledged by the secondary

You should be able to find clues regarding what is causing this communication failure in

the cluster log (see here for how to get that),
the SQL Server error log (look for messages about availability group role changes), and
the AlwaysOn_health extended event session.


MySQLWorkbench with MariaDB 10.0 Performance tab not working

This is on purpose and has nothing to do with the performance schema. MySQL Workbench is designed for MySQL and is only guaranteed (and tested) to work with MySQL servers (and checks this explicitly in certain places). And even then there are sometimes problems, caused by features not available in older versions. Let alone derivative work like MariaDB.


What's the difference between Conditionals and Predicates

that is a good question.
Depending in the definitions, you can exclude them or not.
In my opion :
A WHERE Clause is conditional, because it decides at the end which rows are selected depending on the conditions.
For a predicate the result only can be True or False, and a WHERE clause can't return this result.
I thought about that you need predicates to decide if a row is in the result set or not, but the WHERE clause as total doesn't return True or False


Sharding state recovery process found document - contains wrong list of config servers

As per below documentation you can delete the document.

https://docs.mongodb.com/manual/tutorial/restore-sharded-cluster/#remove-the-minoptimerecovery-document-from-the-admin-system-versions-collection


SQL tracking number of Selects of a databases table row

For SQL Server, you could set up an Audit as described in the documentation: SQL Server Audit (Database Engine).

  All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 (13.x) SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

See also Auditing SELECT statements in SQL Server 2008 by Sreekar Mankala.


Weird WHERE Clause Behavior. Why does this return a row?

What is happening is that SQL pads spaces to the end of strings so that they are of the same length. So if you try do something like:

SELECT 1 WHERE '' = ' '

You will actually get 1 back even though they are not the same. However, if you do something like:

SELECT 1 WHERE 'a' = ' a'

It will not return anything as it would be comparing 'a ' to ' a' which do not match.

However, this would return 1:

SELECT 1 WHERE 'a' = 'a '

As it is comparing 'a ' to 'a '


Inserting into mysql table with archive engine "duplicate key" error

This sounds very unusual for a table using the ARCHIVE Storage Engine. Why? A duplicate key error is not characteristic for ARCHIVE Storage Engine since

Engine does not support the creation of indexes
Engine supports INSERTs and SELECTs

Surprisingly, there can be a key internally present. How?

According to the MySQL Documentation

  The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE and ALTER TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.

Given this information, look back at the table and the query

insert into test_table (id,arch) values (123,'FILE_CONTENT')

If the id column has the AUTO_INCREMENT attribute, you should not specify id with a value. It would produce a normal 1062 error (Duplicate Key) for other Storage Engines.

SUGGESTIONS

Change the insert to a format that can handle the AUTO_INCREMENT attribute of id

insert into test_table (id,arch) values (0,'FILE_CONTENT')

or

insert into test_table (arch) values ('FILE_CONTENT')

Give it a Try !!!

UPDATE 2013-08-06 16:57 EST

If you are planning to do queries from the archive table, you need to get away from the ARCHIVE Storage Engine. Why? Again, according to the MySQL Documentation

  Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used

Note that every SELECT against an ARCHIVE table is a full table scan. If you lookup id 123 with 1,000,000 rows, you gotta read 1,000,000 rows every time.

SUGGESTION

Convert the table to MyISAM. Then you can have a proper index on id plus the ability to create other indexes on other columns as needed.

CREATE TABLE test_table_myisam ENGINE=MyISAM as SELECT * FROM test_table WHERE 1=2;
ALTER TABLE test_table_myisam ADD PRIMARY KEY (id);
INSERT IGNORE INTO test_table_myisam SELECT * FROM test_table;
DROP TABLE test_table;
ALTER TABLE test_table_myisam RENAME test_table;


It's possible optimize query with only index scan?

B:  (field3, id_a, field4)
A:  (id_a)

The B index will be optimal and "covering".  So, even if it touches 1.8K 'rows', they will be compactly sitting in the index.

Separate indexes on individual columns is not the same as a composite index.


Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

QUESTION #1

  Can I assume that the conversion went as intended?

Never assume a conversion until you test it. Thank you for backing it up in advance.

QUESTION #2

  Are there any obvious checks I should perform to ensure that integrity is not compromised?

For any given table mydb.mytable you converted to InnoDB, please check two things

TABLE COUNT

Take a count of all the rows

SELECT COUNT(1) FROM mydb.mytable;

Run the same count on the MyISAM backup.

CHECKSUM

Run a checksum of the whole table

CHECKSUM TABLE mydb.mytable;

Run the same checksum on the MyISAM backup.

SUMMARY ON QUESTION #2

You should get the same count and checksum values for both the InnoDB and MyISAM tables.

SUGGESTION FOR QUESTION #2

If you would like to manage the InnoDB conversion in stages, try doing this next time

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Use the table, test it in staging, kick the tires.

If you are satisfied the table is fine, drop the old table

DROP TABLE mydb.mytable_old;

or retain it as a backup

ALTER TABLE mydb.mytable_old RENAME mydb.mytable_myisam;

QUESTION #3

  Should I enforce that the DB is single user before conversion, or is my only worry that response time might be bad while conversion is ongoing + loss of work in case of a restore from the initial backup?

If you want convert a live MyISAM table to InnoDB, see my post Converting live MyISAM table to InnoDB

If you are very skeptical or fearful of data loss, your idea of single user access is fine.

You could just restart mysql like this

service mysql restart --skip-networking

Login to mysql as root@localhost. Do the conversion, then

service mysql restart

GIVE IT A TRY !!!


Remote connection to the instance with window account and NTLM is failing

This can be caused by one of three things:

The service account has an expired password.
The service account has been locked.
The service account does not have permission to create
an SPN.


I am running the below command:

  sudo mongod --storageEngine wiredTiger --dbpath /data --bind_ip_all
  --logpath /var/log/mongodb/mongod.log --fork

  and everything will work just fine. Except, binding all is not secure
  enough.

As per MongoDB BOL here
 there is Warning: Before you bind to other ip addresses, consider enabling access control and other security measures listed in Security Checklist to prevent unauthorized access.

  I used to received a logging error when I tried --bind_ip
  127.0.0.1,privateIP but discovered that the error would disappear when I did bind_ip [127.0.0.1,privateIP].

As here i would like to say that if you are going to bind multiple ips then as per MongoDB documentation here To bind to multiple addresses, enter a list of comma-separated values.

EXAMPLE

localhost,/tmp/mongod.sock

Or

localhost,192.168.103.100

In the above example , let's say i have bind multiple ips. One external ip (192.168.103.100) and another localhost or (in place of localhost, that is 127.0.0.1).

net.bindIp

Type : string

Default: localhost

Starting in MongoDB 3.6, mongos and mongod bind to localhost (127.0.0.1) by default.

  However, when I tried to connect to mongo locally it would fail and
  through a my site, it would sit there until it timed out. I checked
  sudo netstat -plnt | egrep mongodand there nothing showed up, which
  although it successfully fork the mongod process, it did not bind to
  any of the specified ips.

The "--fork" is a flag to tell mongod to run as a background process, rather than as an active process which blocks the shell.
The "--fork" is a flag to tell mongod to run as a background process, rather than as an active process which blocks the shell.

Most of time when we shall not configure mongd.conf properly ( i mean to say that bindIp, port, and authorization restriction) then get the timed out error.

  For reference, when I tried the same command on on the --bind_ip_all
  version it returned tcp        0      0 0.0.0.0:27017
  0.0.0.0:*               LISTEN      1645/mongod.

net.bindIpAll

Type: boolean

Default: False

New in version 3.6.

If true, the mongos and mongod instance binds to all ip addresses. When attaching mongos and mongod to a publicly accessible interface, ensure that you have implemented proper authentication and firewall restrictions to protect the integrity of your database.

set the net.bindIp setting to 0.0.0.0,:: to bind to all IP addresses.

As here in the above mongo shell ouput the mongod server returns the pid of localhost with by default port number 27017.

Note: net.bindIp and net.bindIpAll are mutually exclusive. That is, you can specify one or the other, but not both.

  My question is, why did --bind_ip ips fail to bind to the specified
  ips?

  Side Note - when binding ips, should I bind my elastic ip or my
  private one?

May be your mongod.conf configuration file is not configure properly.  That's why you are unable to start mongod server file to that specific ip.

As MongoDB configuration files use the YAML format.

The sample configuration file of mongod settings , you can find here

For example I am also write down the mongod.conf sample configuration file in "YAML" format.

storage:
  dbPath: /var/mongodb/db/node1   -- this is the dbPath location
net:
  bindIp: 192.168.103.100,localhost  -- here there is multiple ip binding
  port: 27011
security:
  authorization: enabled    -- here Authorization is enabled
systemLog:
  destination: file
  path: /var/mongodb/db/node1/mongod.log  -- this is log path
  logAppend: true
processManagement:
  fork: true    -- here fork is true

Untill unless you are not able to run mongod server through your mongod.conf file , That means there is some error in your YAML configuration file.

Note: YAML does not support tab characters for indentation: use spaces instead.

Hope this will be help out to you.


COUNTING results from self-referenced table recursively

As your data is a tree structure, consider using the Nested Set data model.

A good write-up can be found at Mike Hillyer's website, here:  http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

complete with lots of SQL to manage the tree data.


The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data.

  The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . .

Source: A Relational Model of Data for Large Shared Data Banks

Codd retained that term. (Same source.)

  Normally, one domain (or combination of domains) of a given relation has values which uniquely identify each element (n-tuple) of that relation. Such a domain (or combination) is called a primary key.

It's not unusual for a relation to have more than one "combination of domains" that uniquely identify each tuple. The relational model offers no theoretical basis for elevating the status of one of them to "primary". As far as the relational model is concerned, all candidate keys are created equal.

As far as standard SQL is concerned, any column or combination of columns that's declared UNIQUE can be the target of a foreign key constraint. In the past, dbms platforms differed slightly in how they handled NULL in a column declared UNIQUE. That's part of the reason old-school database designers lean toward NOT NULL UNIQUE declarations; the other part is that NOT NULL UNIQUE is functionally equivalent to PRIMARY KEY.

There is a semantic difference between a UNIQUE index and a UNIQUE constraint. I prefer to see constraints expressed as constraints.


Changing storage engine to InnoDb from MyIsam is dropping records from table

Are you are using SHOW TABLE STATUS to determine that there is a loss?  Don't trust it.  The "Rows" is an estimate in the case of InnoDB.  Use SELECT COUNT(*) FROM tbl to get the exact row count.

More tips on the conversion:  http://mysql.rjweb.org/doc.php/myisam2innodb


How to store bitcoin value and other currencies values together in MySql database?

As far as you use a DECIMAL(16, 8) field, values are stored with 6 decimal digits. It doesn't matter if they are bitcoins, euros, dollars, ...

If you want to use a monetary type, then you should take care and properly configure the server just to hold all values.

  8.2. Monetary Types

  The money type stores a currency amount with a fixed fractional
  precision; see Table 8.3. The fractional precision is determined by
  the database's lc_monetary setting. The range shown in the table
  assumes there are two fractional digits. Input is accepted in a
  variety of formats, including integer and floating-point literals, as
  well as typical currency formatting, such as '$1,000.00'. Output is
  generally in the latter form but depends on the locale.

CREATE TABLE Amounts(ID int, Value decimal(16,8), Value2 money);

INSERT INTO Amounts VALUES
(1, 100, 100), (2, 12.22636, 12.22636), (3, 0.1, 0.1);

SELECT * FROM Amounts;

✓

3 rows affected

id |        value |   value2
-: | -----------: | -------:
 1 | 100.00000000 | £100.00
 2 |  12.22636000 |  £12.23
 3 |   0.10000000 |   £0.10

dbfiddle here


Performance impact of Extended Events sessions looking at the exact same event type

I'm not an expert on Extended Events by any means but I do know there is potential for performance implications if not done carefully. You'll only really be able to find out by testing and monitoring throughout the day if there's any meaningful impact on your current server / database's workload. I would think by filtering it down to specific entities which aren't necessarily even being used might be ok though.
An alternative idea is to modify each stored procedure such that it inserts into a table the name of the procedure and the current datetime.
For some other alternative solutions you can see this StackOverflow post's answers.
I would be careful using sys.dm_exec_procedure_stats if you go that route though, as it isn't 100% guaranteed to give you the results you're looking for, if an event has cleared out the plan cache.


Selectively ignoring tuples from a table

You can accomplish this with a window function like the ROW_NUMBER() function inside of a subquery or CTE like so:
SELECT id, colA, colB, revFlag
FROM
(
    SELECT id, colA, colB, revFlag,
        ROW_NUMBER() OVER
        (
            PARTITION BY colA
            ORDER BY CASE WHEN revFlag = 'REVISED' THEN 1 ELSE 0 END DESC, id DESC
        ) AS SortOrder -- Generates a unique ID for each row of the same value of colA sorted by revFlag = 'REVISED' first then by id descending
    FROM myTable
) myTableSorted
WHERE SortOrder = 1

As per my comment, the SortOrder field is generated by partitioning on colA and within that partition generating a unique incremental id that is sorted with the revFlag = 'REVISED' having priority first, and then by the myTable.id column descending (to get the latest row per colA).


SQLITE: Select row ID based on only when the column all the values are same

Edit

  I should not get the result of ttid 5 because it has status as
  'preparing'. I should get ttids results only when all the statuses are
  'done'.

To get only the ones where done is found twice and no other statuses are found:

SELECT tb,  tbid, ttid,status
FROM  test t1
WHERE status = 'done'
AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid)
GROUP BY  tb,tbid,ttid,status
HAVING COUNT(*) > 1;

DB-Fiddle

Or

SELECT ttid,status
FROM test t1
WHERE status = 'done'
AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid)
GROUP BY  ttid,status
HAVING COUNT(*) > 1;

DB-Fiddle

The reasoning for adding it to the group by or applying a function:

if tb or tbid can be different for the same 'done' status and the same ttid, which one do you choose? Do you choose the smallest, the biggest, ...?

An example of what happens if this is true and it is not added to a group by or function:

DB-Fiddle that fails


Appointment scheduling is too complex for doing in SQL.  Do it in application code.

Given that, focus on having the database table(s) contain the minimal information to indicate times available and times taken.

The SELECT may end up being "select all rows having to do with a particular day", then the app munches on the data.


How do you manage service account passwords?

The security folks want all AD passwords to expire every three months.

Normally 1 year is pretty standard, 3 months might be expected in a more secure environment where virtual accounts can't be used.

  ... since I definitely won't remember, and I'll likely be on my boat, drunk, when they expire.

I thought it was in a submarine under the polar ice caps (Dilbert Reference)

  How often do you/should you change the passwords on your sql service accounts?

I'll reiterate that once a year is fairly standard, but it still isn't a great story. You have to change the account, there is a service restart required, and in general no one is happy about it except InfoSec.

This is why you should modernize by using Managed Service Accounts and/or Group Managed Service Accounts (or virtual accounts). In MSAs, the password is automatically rotated and is not known by anyone, gMSAs work a bit different but you can think of them the same as MSAs for use with multiple computer objects. The automatic password rotation does not require a service restart.


standard solution for storing units of measure

Assuming that measurement types will not be mixed (i.e. any particular row will not mix "kg" and "inches" OR "pounds" and "cm"), and also assuming that at least part of the intention of this Question relates to this now deleted related Question (please note that the following link will not work unless you have enough rep points to see deleted items:  Handling composite attributes with constant part ), then you need only to indicate the system of measurement being used by that row. In this model, you would have a single MeasurementSystem table that is Foreign Keyed to any tables containing measurements. For example (using Microsoft SQL Server syntax):

CREATE TABLE dbo.MeasurementSystem
(
  MeasurementSystemID TINYINT NOT NULL
                      CONSTRAINT [PK_MeasurementSystem] PRIMARY KEY
                                 CLUSTERED,
  MeasurementSystemName NVARCHAR(50) NOT NULL
);

dbo.Person
(
  PersonID            INT NOT NULL IDENTITY(1, 1)
                      CONSTRAINT [PK_Person] PRIMARY KEY
                                 CLUSTERED,
  MeasurementSystemID TINYINT NOT NULL
                      CONSTRAINT [FK_Person_MeasurementSystem]
                      FOREIGN KEY
                      REFERENCES dbo.MeasurementSystem (MeasurementSystemID),
  Name                NVARCHAR(50) NOT NULL
  Height              FLOAT,
  Weight              FLOAT,
  ...
);

INSERT INTO dbo.MeasurementSystem (MeasurementSystemID, MeasurementSystemName)
VALUES (1, N'Metric');

INSERT INTO dbo.MeasurementSystem (MeasurementSystemID, MeasurementSystemName)
VALUES (2, N'United States customary units');

If you will be mixing measurement systems and/or if you will be allowing for multiple units of measurement even if confined to one system (i.e. allowing for "mm", "cm", "m"), then there will need to be an additional layer to handle the increase in granularity, including the need to have one FK field per each measure column in the Person table. (I can adapt the model above to fit this but will wait for some clarification before doing so).

Or, if there will be a fairly finite / limited amount of combinations of various weight units and height units, then you can instead use each row to represent one of the accepted combinations (e.g. "cm & kg", "m & kg", "mm & kg" / "inch & lb", "foot & lb"). And then "US" vs "Metric" is just a property of each row of the lookup table. For example:

CREATE TABLE dbo.MeasurementUnit
(
  MeasurementUnitID     TINYINT NOT NULL
                                CONSTRAINT [PK_MeasurementUnit] PRIMARY KEY
                                           CLUSTERED,
  MeasurementSystem     CHAR(1) NOT NULL, -- "M" = Metric, "U" = US
  MeasurementSystemName NVARCHAR(50) NOT NULL, -- "Metric" or "US Nonsense"
  HeightUnitsName       NVARCHAR(20) NOT NULL, -- "Centimeters"
  HeightUnitsAlias      NVARCHAR(5) NOT NULL, -- "cm"
  WeightUnitsName       NVARCHAR(20) NOT NULL, -- "Kilograms"
  WeightUnitsAlias      NVARCHAR(5) NOT NULL -- "kg"
);

dbo.Person
(
  PersonID            INT NOT NULL IDENTITY(1, 1)
                      CONSTRAINT [PK_Person] PRIMARY KEY
                                 CLUSTERED,
  MeasurementUnitID   TINYINT NOT NULL
                              CONSTRAINT [FK_Person_MeasurementUnit]
                                         FOREIGN KEY
                                        REFERENCES dbo.MeasurementUnit (MeasurementUnitID),
  Name                NVARCHAR(50) NOT NULL
  Height              FLOAT,
  Weight              FLOAT,
  ...
);

Or, if there needs to be combinations of Units that cross different measurement systems, then I would use separate tables -- one for each measurement type: "WeightUnits" and "HeightUnits". I wouldn't mix units for heights & weights in the same table (i.e. "kg" and "cm" on different rows). For example:

CREATE TABLE dbo.WeightUnit
(
  WeightUnitID      TINYINT NOT NULL
                            CONSTRAINT [PK_WeightUnit] PRIMARY KEY
                                       CLUSTERED,
  MeasurementSystem CHAR(1) NOT NULL, -- "M" = Metric, "U" = US
  WeightUnitName    NVARCHAR(50) NOT NULL, -- "Kilograms"
  WeightUnitAlias   NVARCHAR(5) NOT NULL -- "kg"
);

CREATE TABLE dbo.HeightUnit
(
  HeightUnitID      TINYINT NOT NULL
                            CONSTRAINT [PK_HeightUnit] PRIMARY KEY
                                       CLUSTERED,
  MeasurementSystem CHAR(1) NOT NULL, -- "M" = Metric, "U" = US
  HeightUnitName    NVARCHAR(50) NOT NULL, -- "Centimeters"
  HeightUnitAlias   NVARCHAR(5) NOT NULL -- "cm"
);

In this model, each measurement type in any given table has its own personal FK to its measurement units lookup table.


Agregate data from multiple rows in one row

Use For XML Path..

select
Merchant_contract,
OriginalOrderid,
Stuff(
(Select ','+ status from yourtable t2 where t2.Merchant_contract=t1.Merchant_contract
and
t2.orderid=t1.orderid
For Xml Path(''))
,1,2,'') as status
---do similarly for other columns
from
yourtable t1
group by
Merchant_contract,
OriginalOrderid


Should we explicitly lock MyISAM tables or is it implicit....?

MyISAM only locks for INSERT, UPDATE, and DELETE (a.k.a. DML)

These issue full table locks each time (See MyISAM Documentation on Locking granularity).

SELECTs get blocked by those statement.

The exception is an INSERT with  concurrent_insert=2 defined.

Performing an explicit lock is unnecessary, although you are free to do so.

You may need to check you application for failed connections or DML failures.


Eliminating OIDs while upgrading PostgreSQL from 9.4 to 12

If the oids of the tables are referenced in the code, you cannot simply get rid of them. Probably the old code relies on oid as an auto-generated identifier. Are there any primary keys or other indexes on these columns?

A way to preserve the oids over an upgrade would be:

Before upgrade, on the old database:

ALTER TABLE has_oids ADD newoid bigint NOT NULL;
UPDATE has_oids SET newoid = oid;
ALTER TABLE has_oids SET WITHOUT OIDS;

After upgrade, on the new database:

ALTER TABLE has_oids RENAME newoid TO oid;
CREATE SEQUENCE has_oids_oid_seq OWNED BY has_oids.oid;
ALTER TABLE has_oids ALTER oid SET DEFAULT nextval('has_oids_oid_seq');
SELECT setval('has_oids_oid_seq', ???);

Here, ??? is a number higher than the highest oid present in the table.

You'd also have to take care of indexes or constraints on the oid column.


Conceptually, yes, SSMA is well equipped to handle large Oracle DB migrations.  However, every environment is different and depending on what idiosyncrasies your Oracle DB holds, this may or may not be a simple process.  Don't expect to see a success screen after a few clicks with any approach.

First, I suggest you give a quick read of Michelle Li's answer to a similar question here.  She outlines some key items you need to be aware of before you start.  I will also draw your attention to the Migration Engine option.  You'll want to ensure this is set to Server Side Data Migration Engine otherwise your data will go through whatever client SSMA is being executed on, and this is just a waste of bandwidth.

Most importantly, plan for failure.  It will likely take a few iterations to work out all the bugs, and you may even have to roll in some custom processes to accommodate things SSMA just can't handle, such as External C libraries/functions, SQL Loader Tasks, complex PL/SQL statements, any external scripts being called via cron, etc.  I think SSMA will take a stab at conversion of many of these things, but that doesn't mean it will work 100%.  Testing is going to be critical, so plan accordingly.  I will also suggest you try migrating a copy of your Prod environment (e.g. Dev or some other pre-prod area) first to work out the bugs.  This will ensure you're not killing your production Oracle environment while you're fine tuning things.

In closing, the complexity of your end solution will likely reflect the complexity of the Oracle database.  If it's just data, this could go swimmingly well, but if you've got a lot of logic tied into PL/SQL routines, etc. this can be time consuming.  Use SSMA if possible as it'll hopefully cover 80% or more of the work, and then manage the remaining components how best you see fit, be it SSIS or otherwise.


You have to use the following statement:

UPDATE A
SET A.CityID = B.CityID
FROM dbo.client AS A
INNER JOIN dbo.city AS B
       ON A.city = B.City;

If you don't want a [case/blank spaces] sensitive update (' New York   ' = 'NEW YORK') use this statement:

UPDATE A
SET A.CityID = B.CityID
FROM dbo.client AS A
INNER JOIN dbo.city AS B
       ON rtrim(ltrim(upper(A.city))) = rtrim(ltrim(upper(B.City)));


You can filter on databases and/or tables only.  See the manual on binlog/replicate-do/ignore-db/table

The filtering can be before leaving the Master (binlog-...) or as it comes into the Slave (replicate-...).

The main intent of Replication is for the Slave to be an identical copy of the Master.  Your request is deviating quite far from that.


Does sp_spaceused data contain more than the sum of each table data?

What I understand is that the sum of the tables will not be equals the database size. On msdn it says:

  database_size will always be larger than the sum of reserved +
  unallocated space because it includes the size of log files, but
  reserved and unallocated_space consider only data pages.

  Pages that are used by XML indexes and full-text indexes are included
  in index_size for both result sets. When objname is specified, the
  pages for the XML indexes and full-text indexes for the object are
  also counted in the total reserved and index_size results.

  If space usage is calculated for a database or an object that has a
  spatial index, the space-size columns, such as database_size,
  reserved, and index_size, include the size of the spatial index.

Reference here


SQL Server Data Tools and partition functions

I was able to find a solution to my problem — hope this helps someone else.

To avoid each database publish recreating the partition function, you can check the Ignore partition schemes option in the Advanced Publish Settings dialog (Advanced... button in the Database Publish dialog).

From the description of the option (emphasis mine):

  Specifies whether differences in partition schemes and functions should be ignored or updated when you publish to a database.

However, if you have defined a partitioned object (table or index) with page or row compression, although the Ignore partition schemes option no longer recreates the partition function, the partitioned object will get recreated anyway.

This happens because the partitioned object gets scripted with the compression defined per partition, and since the object has a different number of partitions than it was originally defined, SSDT recreates the object on publish. E.g. (formatted):

WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1)
     , DATA_COMPRESSION = PAGE ON PARTITIONS (2)
     , ...

In order for this not to happen, you can also check the Ignore table options option, in the same Advanced Publish Settings dialog — just keep in mind that you will be ignoring other options, such as ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS (reference of table options).


Missing 'Microsoft Office 12.0 Access Database Engine' in SSMS

The driver must be installed on whatever server you are on when you connect. So if you're on your laptop and using Management Studio to connect to SQL Server then the driver needs to be on your laptop.


I have not needed to overcome this problem on a SQL Server except when I make a mistake with permissions.  I do not endorse the below, because I have not spent time on the problem.

However, for what it is worth, Symantec has an article on this issue using SQL Server 2012 and Windows 2012 at:

Cannot execute as the database principal because the principal “guest” does not exist

Their answer is based on how your SQL Server is configured.

A default instance running under Local System should run under NT Service\MSSQLServer, then NT Authority\System has db_datareader role to the “master” database.
A named instance running under Local System should run under NT Service\MSSQLServer or NT Service\MSSQL$ServerXInstanceX, then ONLY NT Authority\System has db_datareader role to the “master” database.
If running under Domain or Local user account the instance should run under Mydomain\MyUser, then Mydomain\MyUser has “db_datareader” role to the “master” database


How to relate two rows with cyclic relationship in the same table in sql?

I'm not sure to understand correctly your use case, but maybe you could simply do the update comparing refer_to_id and id columns.

update my_table
set group_id =  case when refer_to_id > id then id
                    when  refer_to_id < id then refer_to_id
                    else id
                end

This is really basic and will not fix your issue if you have complex dependencies (line #1 referring to line #2 referring to line #3 etc...).


MYSQL Optimize group by query with aggregators

INDEX(date,             -- to satisfy the WHERE; must be first
      id_num, expired)  -- to complete "covering"; must be last (either order)

Because it is "covering", it will run entirely in the index's BTree.
By putting date first, it looks through the minimal number of rows.

This is a rather unusual query, so don't expect the principles to carry over to other queries.

Even when you are somewhat happy with that, I may tell you how to make it 10 times as fast.  But first, I need to see SHOW CREATE TABLE and provide some clues of the date range and distribution of dates in the table.  Also, are the rows being inserted somewhat chronologically?  And are there any UPDATEs or DELETEs?

Summary Table?

Perhaps the table could be summarized into a Summary Table that has one row per month per id_num.  Then the original query would do most of the work 30x faster from that table, plus still do some work from the huge table.  The summary table would be incrementally augmented.


Using the same primary key as a foreign key for every table?

In general it is bad schema design to have two tables in a 1:1 relationship.  (There are exceptions, but yours is not one.)

In general, birthdate should be a single column of type DATE, not 3 columns.


Traditional failover clustering vs. Availability Groups

What would you think is the best solution to look at given these factors?

AlwaysON is designed to meet business uptime and availability wherein you can afford minimal data loss and downtime. It requires proper planning and implementation.

As per your question, it seems that the databases are not that business critical (business have no problem accepting the nightly backup as restore point).

Based on the information that you provided, your business will be able to survive with Log Shipping as a DR solution.

The benefit is that you dont have to worry about windows cluster and other complexities associated with AlwaysON like DNS, Windows failover cluster, etc. Check the prereq and requirements.

Logshipping is well tested and is the most mature of all the DR technologies that you get from SQL Server. You can adjust the frequency of Transaction log backup,copy and restore as per your needs.


Look at DBMS_LOB.SUBSTR and pay close attention to the differing definition from the SUBSTR you are familiar with.


Return the uniqueidentifier generated by a default on insert

I think you are looking for output

DECLARE @MyTableVar table([testID] [uniqueidentifier]);
 INSERT [AddressBook] ([address], [zipcode])
        OUTPUT INSERTED.[testID] INTO @MyTableVar
 VALUES (N'address', N'zipcode');

--Display the result set of the table variable.
 SELECT [testID] FROM @MyTableVar;

GO

uniqueidentifier may not be the most efficient id here but this is an answer to the stated question


How do I restore an Oracle file system backup onto a new developer machine?

Here's what I did ... this required a lot of trial and error, but I've got access to the database.

Installed Oracle with it's home on an R: drive. Then opened an administrative command prompt, then:

R:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1
R:\>set ORACLE_SID=SVP
R:\>oradim -new -sid SVP -startmode manual -pfile r:\oracle\product\11.2.0\dbhome_1\dbs\init_svp.ora
R:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> create spfile='r:\oracle\product\11.2.0\dbhome_1\database\SPFILESVP.ORA' from pfile='r:\oracle\product\11.2.0\dbhome_1\dbs\init_svp.ora';
SQL> startup nomount
SQL> CREATE DATABASE SVP
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   EXTENT MANAGEMENT LOCAL
   DEFAULT TEMPORARY TABLESPACE temp
   UNDO TABLESPACE undotbs1;
SQL> shutdown immediate

At this point, I moved the new database's CONTROLFILE, DATAFILE and ONLINELOG folders away from R:\oracle\oradata\SVP and replaced them with the ones from the backup oradata\DBNAME folder (from the question).

SQL> startup nomount -- (because we'd get an error here about the control file)
SQL> alter system set control_files='R:\oracle\oradata\SVP\CONTROLFILE\O1_MF_9T04M5YO_.CTL' scope=SPFILE;
SQL> alter system set compatible='11.2.0.4.0' scope=SPFILE;
SQL> shutdown immediate
SQL> startup

The init_svp.ora file looks like this:

db_name='SVP'
memory_target=1G
processes = 150
audit_file_dest='r:\oracle\admin\SVP\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_create_file_dest='r:\oracle\oradata'
db_recovery_file_dest='r:\oracle\fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='r:\oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.2.0'

I don't know if I've got anything I don't need, and I don't particularly care. I've got access to the data, so I'm all good.

Thanks to those who posted ... mustaccio's comment about making Oracle think it already existed by supplying the control file was helpful, as were several web pages that got me through the "control_files" and "compatible" parameter issues.

EDIT: After you reboot your computer, you may get an Oracle error saying something like:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

You'll need to mount your database with these commands (substituting in your details, of course):

R:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1
R:\>set ORACLE_SID=SVP
R:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> startup


I suggest:

A single database for all groups, but with
each table partitioned by group.

This should provide you with the convenience of a single database, combined with something like the performance you would expect from separate databases for each group.

Based on the comments on the related question on StackOverflow, you are using MySQL - you can find the official MySQL 5.5 Reference Manual section on partitioning here.

There is also a related StackOverflow question here, and a related blog entry here.


Postgres Problems with Phusion Deployment (Rails)

Read the Ubuntu documentation on their PostgreSQL packages, which you'll find in /usr/share/doc . Alternately, there's a nice wiki page on it here: https://help.ubuntu.com/community/PostgreSQL (first hit for "ubuntu postgresql").

You have a pg_hba.conf file that's set to try to use ident authentication for host (TCP/IP) connections, but you don't have an identd running or you're trying to connect as a different PostgreSQL user to your unix username.

You need to set a paassword for the user and change the authentication method to md5. See the client authentication chapter of the documentation.

(A truly definitive answer would be easier to make if you'd given the command you ran to get that error).


SELECT id
FROM item
LEFT JOIN item_taxes on item.id=item_taxes.item_id
WHERE item_taxes.item_id IS NULL

EDIT

Please refer to to the comment by @ypercube as it adds more explanation and clarification.


How does Hash Join works with relational operators other than equality operator?

From the article you were reading (by Craig Freedman):

  Hash join shares many characteristics with merge join.  Like merge join, it requires at least one equijoin predicate, supports residual predicates, and supports all outer and semi-joins.

(Emphasis added)

The hash keys are built from the (required) equijoin predicates. All other predicates are evaluated during the bucket scan, hence the name "residual predicate".

In your example, Name would be used to form the hash key. The age predicate would be evaluated on all rows in the matching hash bucket, after the initial hash probe. In cases where the hash key is not unique, the actual value of the hashed columns in the bucket will also be compared, naturally.

The execution plan properties (in SQL Server anyway) for the hash join will show you the build hash keys and any residual.


Which workarounds exists to missing relative path feature of SQLCMD :r?

Microsoft isn't improving sqlcmd - all their bets are on the PowerShell horse. When you're doing automated deployments like you describe, PoSH will give you more options for all kinds of dependency stuff. I'd focus there.


Oracle XMLAGG issue with combining cells that have comma separated values in cells

Test data:

select * from t_ ;

C1   C2   C3
row1 1    apple
row2 1    apple,pear,banana
row3 1    apple,banana
row4 1    pear,banana,orange
row5 2    orange
row6 2    orange,pear
row7 2    pear,banana

Query:

select
c2 ||' | '|| rtrim (
   xmlagg( xmlelement( e, c3 || ',' ) ).extract('//text()')
, ','
) result
from
  (
    select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
    connect by regexp_substr(c3, '[^,]+', 1, level) is not null
  )
group by c2
;

RESULT
1 | apple,pear,orange,banana
2 | pear,orange,banana

LISTAGG() may also work -

select
c2 ||' | '|| rtrim (
   listagg(c2 , ',') within group ( order by c2 )
) result
from
  (
    select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
    connect by regexp_substr(c3, '[^,]+', 1, level) is not null
  )
group by c2
;

RESULT
1 | apple,banana,orange,pear
2 | banana,orange,pear

Dbfiddle here.


Parallel option performing worse on cluster

If you want to run in parallel you have to design for it. Things to consider is using partitions so they can be scanned in parallel. Don't forget that parallelism in general will try to use full scans on segments. Partition pruning can help reducing the size of your scans.

If you really are using pq for this sort of small queries, make sure you have enough PQ slaves ready for use. Otherwise, the starting of new slaves is added as overhead to your query. Not that bad for dss type queries that run for hours but deadly for short running queries.

Also think about using queuing your PQ queries to guarantee that when a query starts, it has all the slaves it needs. This can be done in Oracle Resource Manager.


Shrink the database only up to its initial size which is set after creating database

Initial size is not just 3MB, it is taken from the model database (if not specified during the creation of your user database.) So assuming you haven't specified a initial size during the creation of your user db and you haven't altered the model database file sizes after you have created your userdb you can do the following:

--Create testDB
CREATE DATABASE [TEST_100]
GO

--grow your database file size
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 100MB )
GO

--switch context
USE [TEST_100]
GO

--Find size of modelDB mdf file, this is your initial file size used for the userdb
DECLARE @TargetFileSize int

SELECT  @TargetFileSize = (size * 8 / 1024)
FROM    sys.master_files
WHERE   database_id = 3 --model database
AND     file_id= 1 --first file is mdf, assuming you have a model with just one mdf. If you have multiple files, change for the one you need to find.

--shrink the the first file of your current database to the target size that you just found.
DBCC SHRINKFILE (1,@TargetFileSize)

EDIT

Okay, some extra info is needed after your edits and comments.

First of all. I feel that the "Initial size" label that you see when you look at the file properties in SSMS is a misnomer. Basically, your intial size is just a concept. It's the first size that is used during creation of the database. You can either explicitly specify this in the CREATE DATABASE statement, or you can have SQL Server implicitly copy it from the model database by ommiting that information during creation.

However, once the database is created, from a DBA perpective there is no such thing as a "initial size" there is only one property visible for a DBA and that is: the actual size. Even the "Initial size" property in SSMS just shows actual size, not the initial size.

Well how come that DBCC SHRINKFILE or DBCC SHRINKDATABASE "know" the initial size then? Even after you have changed the size. Interesting question.

The first page of a dattabase file is the file header page. In there you have, amongst others, 2 properties: size and minsize.

At creation of the file, both file header properties get filled with the inital value:

DBCC TRACEON(3604)
--parameters for DBCC PAGE: (Dbname, fileID, pageID, outputTypeID)
DBCC PAGE('Test_100',1,0,3)with tableresults

Both sizes are in the amount of data pages. In this case. 288 data pages.

Now if I alter the file size:

ALTER DATABASE [test_100] MODIFY FILE ( NAME = N'test', SIZE = 50MB )

You can see that the "size" property is changed to reflect the new size. However, the "MinSize"  property still contains the "Initial" size. It's the minimal size to which the shrink command will go.

However, having said all this. I still don't understand why you want to complicate things by first altering the initial size and then shrink to that initial size. Instead of just shrinking directly to a targetsize.

Anyway, to answer your question. The "initial" size is not exposed as a property to the user/dba.


This is a common misconception, i.e. that yields are somehow causing the slowness. In fact they are a symptom, not a cause.  Even if there is no lock contention that requires a yield (writes basically), the queries still yield when they have to page from disk.  They then re-acquire the lock when a certain amount of paging is done and look to yield again if more paging is needed (repeat until complete). If there is no lock contention from writes, then this is all pretty much instantaneous and does not add to the overall execution time.

If a query yields a lot, then it was hitting disk a lot, and that is the cause of the slowness - the disk access.  Hence, numYields is just a way to infer that it was indeed paging to disk that caused the query to be slow.  If you want those queries to be fast, then you need to have that data set in memory, and have enough memory for it to stay there long terms and not be evicted.

Note: by default the kernel will use LRU to decide what gets evicted, so the likely candidates for slowness are queries on (large) parts of your data set that are not accessed very often.

There is no way to limit numYields, and it wouldn't really make sense to do so, but yes the remedy is to identify the data being addressed by those slow queries and make it fit into memory (note: the first query on any data will still be slow unless you pre-heat in some way, the second query will be in memory and fast).


One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.

create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)

create table employee
(
employee_id int PRIMARY KEY IDENTITY,  --(Primary Key)
employee_name varchar(100),
company_id int not null  --(Foreign Key)
)

create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10),   -- Home | Cell | Work
employee_id int not null  --(Foreign Key)
)

employee_id being your foreign key to your employees table.
If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this

select *
from phone as p
join employee as e on e.employee_id = p.employee_id
where e.company_id = 1

I hope this helps :)


help in the initial configuration of postgreSQL9.6

Basically your pg_hba.conf files need to be updated to work properly. To find out the location of the conf file, look for the main postgresql.conf file which is typically in the /Data directory.

Also check your postgresql.conf file to see what port # it is listening on, you might have setup a non default port.  Running a netstat -an would help you verify that it is currently listening.  If it is not ensure the service or app is running.

After you validate that and you edit the pg_hba.conf you should restart the service in Windows then test again locally.  If you're running IPv6 the 'open to all' notation would be ::0/0, that's a common implementation but a poor security practice.  I would recommend only testing with that, then once it works, lock it down to specific IPs since you know everything is up and running.

Try this on your conf file and try to connect locally.  If you're using IPV6 use IPV6 addresses in CIDR-ADDRESS:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust

Here is the documentation for PostgreSQL hba_conf.

The most recent common issues I've seen were when the server or client were using IPv6 or it was a default install and a client was trying to connect from a different IP than the server.  Otherwise you conf file could be setup wrong.


Batch update timestamp records to correct timezone

To adjust timestamp values for the difference between any two given time zones, use the AT TIME ZONE construct twice in a row. See:

Ignoring time zones altogether in Rails and PostgreSQL

Assuming you want to adjust all timestamps after the begin of British Summer Time (BST) at '2018-03-25 01:00':

UPDATE tbl
SET    ts = ts AT TIME ZONE 'BST' AT TIME ZONE 'UTC'
WHERE  ts >= '2018-03-25 01:00';

Of course, in your particular example you might just subtract an hour:

...
SET    ts = ts - interval '1 hour'
...


How do you clear out all old query plans from within Microsoft SQL Server?

You asked two questions here. First, you want to know if you can remove all plans stored in memory for an instance of SQL. That is done with DBCC FREEPROCCACHE as Matt M suggested.

The second question you asked is "How can I do this without impacting 30 or so users that rely on other databases on the same server?". The short answer is "you can't". If you remove all plans than the other users that are relying on plans being in memory will likely suffer a performance hit.

The workaround for this requires some manual intervention. You can use DBCC FREEPROCCACHE to remove specific plans providing you have the plan_handle.

From what you are describing above it does sound like a plan issue, but I am not certain that removing plans is the answer. I would point you in the direction of parameter sniffing before thinking about removing plans:

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/08/11/conor-vs-misbehaving-parameterized-queries-optimize-for-hints.aspx

You should be able to optimize the queries rather than fooling around with DBCC FREEPROCCACHE on a scheduled basis. I would also advise that you spend time analyzing the wait events for your instance.


If you intend to run the SSIS jobs from the SQL Agent, then they will execute as the user the agent runs as. That's usually the same as the account the instance runs as, which has rights to everything.