How does the optimizer of Firebird determine join order? As Most query optimizers determine join order via a dynamic programming algorithm pioneered by IBM's System R database project, I was wondering whether the optimizer of Firebird uses the same strategy? And is there any documentation I can refer to?

The firebird-devel list on yahoo would be your best bet. Google "firebird query optimizer" and look for Dimitry Yemanov.

I don't think there are any better answer(s) to the question(s) than refer to firebird-devel. The system is developing quite quickly, and if I recall correctly, even fundamentals like how the optimizer works internally may be impossible to find out without either reading the code or checking on the devel list.

Yemanov is a major contributor to Firebird and anything he says might give some clue, but then again, a slideshare may not have enough detail. There is the Firebird Book by Helen Borrie, but again, the question may be beyond its scope.


Estimating impact of indexes SQLite database size I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to estimate the size per row with these columns, but I'm not sure how to account for the extra per-row padding due to the indexes. What's the best approach for this?

At least some of the details of index B-trees are documented.

It might be easier to just create a test database, populate it, and measure the file size before and after creating an index.


Number of times PostgreSQL database restarted How to get how many times my PostgreSQL database restarted and how to get each restart time?

I do not want to get the details from the log file. Is there any other way to get those details?

Based on the pg_log or any PG log files, using grep to match how many times of shutting down the database.

grep -rwn postgresql-2018-04-12.log -e "database system is shut down"

The output looks like as below

  postgresql-2018-04-12.log:9:2018-04-12 16:07:25 CEST LOG:  database
  system is shut down postgresql-2018-04-12.log:18:2018-04-12 16:08:18
  CEST LOG:  database system is shut down


Why is the built-in distance function so much slower than my custom one? I'd like to find the distance between 2 sets of coordinates.  First I use the built in function:
declare @lat1 real = 33.1, @lon1 real =-117.1, @lat1 real = 39.6, @lon1 real =-98.7
declare @source geography = geography::Point(@lat1, @long1, 4326);
declare @target geography = geography::Point(@lat2, @long2, 4326);

select  @source.STDistance(@target)

Now I use manual calculation to do the same thing:
 -- Kilometers to Miles: 0.621371, Earth Radius: 6378.137
 select 0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))

In my testing on SQL Server 2014, the manual function is around 4x faster than the built-in one.  Is this normal or am I missing something fundamental?

SQL Server 2016 improved the perf somewhat, but the basic reason is the STDistance is accurate, but simpler formulas can be faster, if you understand the errors they produce.
See eg Geographic distance can be simple and fast, and a description of the algorithm used in STDistance:

This paper outlines some geometric algorithms that are used in
Microsoft SQL Server for processing geospatial data in a way that is
independent of any cartographic projection.

Geometric algorithms on an ellipsoid earth model
eg
declare @lat1 real = 33.1
declare @lon1 real =-117.1
declare @lat2 real = 39.6
declare @lon2 real =-98.7

declare @source geography = geography::Point(@lat1, @lon1, 4326);
declare @target geography = geography::Point(@lat2, @lon2, 4326);

declare @distance float
declare @i int = 0
declare @iter int = 1000 * 1000
declare @start datetime2

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
  set @distance =  @source.STDistance(@target)
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
  set @distance =  @source.STDistance(@target)
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i < @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

outputs
--------------------------
STDistance: 2367ms

---------------------------
calculation: 1098ms

--------------------------
STDistance: 2347ms

---------------------------
calculation: 1105ms


Replacing IN condition in Oracle Oracle here. I have the following tables:

[orders]
===
order_id : integer constraint pk_orders primary key using index
order_name : varchar2(40 char)
order_ordered_by : integer constraint fk_shoppers references accounts
order_total : number(10,2) not null
order_status : char not null

[line_items]
===
line_item_id : integer constraint pk_line_items primary key using index
order_id : integer not null constraint fk_line_items_orders references orders on delete cascade
product_id : integer not null constraint fk_line_items_products references products
line_item_quantity : integer not null

[products]
===
product_id : integer constraint pk_products primary key using index
product_name : varchar2(40 char)
product_category : varchar2(10 char)
product_available_on : date

I am trying to write a query that updates orders and sets their statuses to "ORDERED" where:

the orders.order_status is currently "PENDING"; and
the products.product_category is currently "COFFEE"; and
the products.product_available_on is currently less than or equal to the present time (now)

My best attempt thus far does work and get the job done:

UPDATE orders
SET status = 'ORDERED'
WHERE order_id IN (
    SELECT DISTINCT orders.order_id
    FROM orders
    INNER JOIN line_items ON line_items.orderId = orders.order_id
    INNER JOIN products ON line_items.product_id = products.product_id
    WHERE
        orders.status = 'PENDING' AND
        products.product_category = 'COFFEE' AND
        products.product_available_on <= CURRENT_DATE
);

Again, this does work, however its pretty slow so I'm trying to see if there is a way I can rewrite this to be more efficient where I'm not using the IN condition (I've read in several places that IN can cause performance issues in Oracle). Is there any way to accomplish rewriting this query without the IN so I can compare performance?

Please note: changing the tables (tweaking their fields, adding constraints/indexes/anything) is out of the realm of possibility in my particular use case!

I'd recommend getting rid of that DISTINCT if possible, they tend to act as "make this subquery a black box to the DB engine" keywords, which doesn't help the query optimiser make good choices.

Try the following, if it works in Oracle (I'm a MS-SQL coder, sorry)

UPDATE orders
SET status = 'ORDERED'
WHERE orders.status = 'PENDING'
    AND EXISTS (
    SELECT 1
    FROM line_items
    INNER JOIN products ON line_items.product_id = products.product_id
    WHERE
        products.product_category = 'COFFEE' AND
        products.product_available_on <= CURRENT_DATE
        AND line_items.orderId = orders.order_id
);


Moving to Production database for first time We currently have a development database where we store lot of data (all of them are external data coming in .csv or excel files and stored via SSIS packages) and business users are directly connected to the development sql server database via excel. Since the reports derived from the development server are used in the business, we are planning to move the database to a production server.

Can Business users connect to the production database via MS Excel? Does best practices allow that?
Can I connect to the production database via SSMS and do data analysis on that? Currently I use a Windows 8 system.
Can I connect to the production database from our SQL Server Data Tools or Business Intelligence Development studio? If I can't connect to the production server, how to develop ssis packages for Production server?

Thanks
Vagan

Our IT team is saying that if we move to production... [users] can access the database only via API or Tableau or reporting services. is that so?

This very much sounds like a policy. From technical point of view, there is no reason why direct connections would not be possible. That being said, production admins are responsible for the production environment not going haywire, so this kind of restrictions are quite common in the industry. The current buzzword DevOps is pushing this limit a bit, but on more traditionally oriented houses there is little if any change.

The reason why production staff are quite strict about the access limits is simple. If something breaks in the production, it's their fault. Even if the real culprit was a developer running a cross-join-o-matic, dropping indexes and generally being a clown. Production admins are supposed to have the environments running at top performance, and letting those down for any reason is a sign for problems.

For a solution, talk with your team lead and boss. Write a memo that outlines the pros and cons the new method would introduce. Don't just list the obstacles, tell what's good too and propose solutions. The memo should give an impression that you are a problem solver, not a whiny complainer. After that, it's your boss' call what to do.

  Can I connect to the production database via SSMS?

In theory, yes. There might be firewalling or company policy that prevents such an action. Do not try to work around policies or technical barriers on your own! You might get into serious trouble.

  If I can't connect to the production server, how to develop ssis packages ?

Develop the packages on development environment. Push those to the test and let production admins move packages into the production.


How much memory are used by perticular session during run time in MySQL 5.1 community version? Is it possible to find out how much memory is used by particular session?A user was asked me how much memory is utilized by particular session running in MySQLl 5.1 community version server.
Kindly provide the solutions thanx in advance.

MySQL 5.7 (still in development as of this post, but soon to be released) is the first version to include memory profiling.

You have to enable memory profiling on the setup instruments (it is disabled by default) ans then you can check it with this sys view:

You can get results such as:

mysql> select * from sys.memory_by_thread_by_current_bytes limit 5;
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user           | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|         1 | sql/main       |              29333 | 166.02 MiB        | 5.80 KiB          | 131.13 MiB        | 196.00 MiB      |
|        55 | root@localhost |                175 | 1.04 MiB          | 6.09 KiB          | 350.86 KiB        | 67.37 MiB       |
|        58 | root@localhost |                236 | 368.13 KiB        | 1.56 KiB          | 312.05 KiB        | 130.34 MiB      |
|       904 | root@localhost |                 32 | 18.00 KiB         | 576 bytes         | 16.00 KiB         | 6.68 MiB        |
|       970 | root@localhost |                 12 | 16.80 KiB         | 1.40 KiB          | 16.00 KiB         | 1.20 MiB        |
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+

Before 5.6, you had to relay on external tools that you may not want to run in production. @rene's recomendation on profiling session status variables could be a better bet.


Updating multiple values at a time I have been given the task to update a few rows in a database. There are two columns in which one is ID and the other is CURRENCY. I have updated these rows using the update statements that follow:
update account set currency = 'INR' where id =15;
update account set currency = 'EURO' where id =12;
update account set currency = 'DOLLAR' where id =18;
update account set currency = 'Pound' where id =13;
-- and so on.

Actually, in this case I was able to do it easily using these update statements because of a relatively low number of rows, but what if there are thousands or hundreds of thousands of rows? Is this the only way to update them, or is there any alternate way or script?

You can use a table constructor with VALUES:

update account
set currency = nv.currency
from
    ( values
        (12, 'EURO'),
        (18, 'DOLLAR'),
        (13, 'Pound')
    ) as nv (id, currency)
where account.id = nv.id ;


Should I stop an awfully inefficient MySQL DELETE statement? I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode:

DELETE table
FROM (
    SELECT MAX(id) id, field
    FROM table
    GROUP BY field
) temp_table
INNER JOIN table
    ON table.field = temp_table.field
WHERE table.id != temp_table.id

It's been stuck in the Sending data state for more than 24 hours, and now I know why: it has an awful execution plan:

mysql> EXPLAIN SELECT 1 FROM (SELECT MAX(id) id, field FROM table GROUP BY field) temp_table INNER JOIN table ON table.field = temp_table.field WHERE table.id != temp_table.id
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 381888 |                                 |
|  1 | PRIMARY     | users      | ALL  | NULL          | NULL | NULL    | NULL | 984873 | Using where; Using join buffer  |
|  2 | DERIVED     | users      | ALL  | NULL          | NULL | NULL    | NULL | 984873 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
3 rows in set (46.12 sec)

(MySQL 5.5 doesn't allow EXPLAIN DELETE, so I'm doing it with EXPLAIN SELECT 1 as per this answer)

Please note that only the EXPLAIN itself is taking 46 seconds to complete.

Should I stop this statement and try to do things better, or should I wait?

I see what the query is doing. You are trying to DELETE a ton of rows and keep the last inserted id for every field.

I have a much better method.

DROP TABLE IF EXISTS keys_to_keep;
CREATE TABLE keys_to_keep
(
    id INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO keys_to_keep SELECT MAX(id) FROM mytable GROUP BY field;
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
SELECT B.* FROM keys_to_keep A INNER JOIN mytable B USING (id);
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

Check mytable. If it has only the last occurrence of field, then you can

DROP TABLE keys_to_keep;
DROP TABLE mytable_old;

GIVE IT A TRY !!!

I have recommended this technique before

Oct 20, 2014 : Deleting data from a large table with replication
Dec 20, 2013 : Massive MySQL delete from CSV file
Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?

I have also discussed doing soft deletes as an alternative (require maintaining an additional mapping table of ID marked deleted or an extra column to flag a row deleted):

Feb 15, 2012 : How to implement soft deletes?
Mar 05, 2012 : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
Nov 07, 2012 : Can I do anything about a very long running KILL command?
Aug 07, 2013 : How to delete duplicate records


How to UPDATE faster with GROUP BY? I have a typical article and tag_map tables,
CREATE TABLE `articles` (
  `ID` int(11) unsigned NOT NULL,
  `Tags` tinyint(3) unsigned DEFAULT NULL,
  `Title` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Tags` (`Tags`),
  FULLTEXT KEY `Title` (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'

CREATE TABLE `tag_map` (
  `TagID` int(11) unsigned NOT NULL,
  `ID` int(11) unsigned NOT NULL,
  `Number` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`TagID`,`ID`),
  KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'

and I need to UPDATE the article table with the number of tags COUNTed from tag_map table as
UPDATE articles a JOIN (
SELECT ID,COUNT(*) AS C FROM tag_map GROUP BY ID
) b ON a.ID=b.ID
SET a.Tags=b.C WHERE a.Tags IS NULL

The problem is that WHERE a.Tags IS NULL condition is not applied to the subsequery. When updating only a few roles in the articles table, the entire tag_map table is scanned.
I run MariaDB 10.5, and this is the output of EXPLAIN UPDATE:
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
| id   | select_type     | table       | type | possible_keys | key        | key_len | ref                   | rows     | Extra       |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
|    1 | PRIMARY         | a           | ALL  | PRIMARY,Tags  | NULL       | NULL    | NULL                  | 34543450 | Using where |
|    1 | PRIMARY         | <derived2>  | ref  | key0          | key0       | 4       | texts.a.ID            | 2        |             |
|    2 | LATERAL DERIVED | tag_map     | ref  | ID            | ID         | 4       | texts.a.ID            | 3        | Using index |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
3 rows in set (0.001 sec)

UPDATE articles a JOIN (
  SELECT a2.ID,COUNT(*) AS C
  FROM articles a2 JOIN tag_map b2 ON a2.id=b2.id
  WHERE a2.tags IS NULL
  GROUP BY a2.ID
) b ON a.ID=b.ID
SET a.Tags=b.C
WHERE a.Tags IS NULL;


Large Identifier Uniqueness Across Table We use NEWSEQUENTIALID to generate uniqueidentifier as table ID.
Issue is raised after server reset as the seed of it is modified and sequentiality breaks.
Is there another suggested solution to achieve large identifier uniqueness for a table?
Thank you

Perhaps look into Jimmy Nilsson's COMBs? Discussed here: https://stackoverflow.com/questions/1155454/performance-value-of-comb-guids
Or, if you don't need uniqueness over several SQL Server instances, just some int-type with a sequence or identity? (As suggested by Mo64.)
The fact that NEWSEQUENTIALID() is "re-seeded" at re-start isn't anything we can prevent, AFAIK.


How to add a day/night indicator to a timestamp column? There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I  only get 'DAY' as outcome. Am I not formatting the values right?

select record_id, rec_date,
       case when date_part('hour', rec_date) between 20 and 07 then 'Night'
            else 'Day' end as Indicator
from records;

The rec_date column is a timestamp where I can see values such as 2019-11-20 21:34:02.000000 - which should get a 'Night' indicator.

SELECT record_id, rec_date
     , CASE WHEN rec_date::time <  '08:00' THEN 'Night'
            WHEN rec_date::time >= '20:00' THEN 'Night'
            ELSE 'Day' END AS indicator
FROM  records;

Why?

It's a matter of date-math rather than date-format. You want to do the math correctly and efficiently. The format of 'Day' and 'Night' are not in question.

McNets already shed some light on BETWEEN vs. BETWEEN SYMMETRIC. But BETWEEN SYMMETRIC 20 AND 07 would still be dead ugly, slow, and incorrect, ultimately.

SYMMETRIC only makes sense with parameterized bounds where you don't know which will be greater ahead of time. Not the case, 20 and 07 are constants.
Applied to your case naively, you would get day and night inverted, because BETWEEN SYMMETRIC 20 AND 07 ends up being evaluated as BETWEEN 07 AND 20 (just more expensively).
OK, easily fixed by switching 'Day' and 'Night'. But now, the times 20:** and 07:** would be tagged 'Day'. BETWEEN, with or without SYMMETRIC, includes upper and lower bound. That's why it is almost always the wrong tool to use with timestamps. In this particular case, date_part() happens to counter the built-in issue with including both bounds to some extent. Either way, to match your original intent, it would have to be:

CASE WHEN date_part('hour', ts) BETWEEN '08' AND '19'  -- adjusted
     THEN 'Day' ELSE 'Night' END AS indicator

The query with the expression date_part('hour', rec_date) BETWEEN SYMMETRIC 20 AND 07 is roughly twice as expensive as my suggestion. The larger part due to the pointless SYMMETRIC, the smaller part due to the function being more expensive than the cast.
The suggested expression is much less likely to be misunderstood than the devious BETWEEN, as it makes clear which bounds are included.

Asides

I would not call a timestamp column "record_date", as date is a different basic data type than timestamp. More potential for confusion.

If your actual data type happens to be timestamptz (or, possibly, in any case), you may have to define where in the world it's supposed to be  "day" or "night". See:

Ignoring time zones altogether in Rails and PostgreSQL


can anyone help me with this awful query plan? The query:

    SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5,
Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9,
Object1.Column10, Object1.Column11, Object1.Column12, Object1.Column13,
Object1.Column14, Object1.Column15 as Column15, Object1.Column16,
Object4.Column4 AS Column17, Object4.Column2 AS Column18, Object1.Column19,
 Object1.Column20, Object1.Column21, Object1.Column22, Object1.Column23,
Object1.Column24, Object1.Column25, Object1.Column26, Object5.Column4,
Object1.Column27, Object1.Column28, Object1.Column29, Object3.Column30,
Object3.Column1 as Column31, Object3.Column32 as Column33, Object1.Column34
as Column34, ? AS Column35 , Object3.Column36 as Column37
FROM Object6 AS Object1
INNER JOIN Object7 AS Object3 ON Object1.Column38 = Object3.Column1
INNER JOIN Object8 AS Object2 ON Object3.Column30 = Object2.Column1
LEFT JOIN Object9 AS Object4 ON Object1.Column16 = Object4.Column2
LEFT JOIN Object10 AS Object5 ON Object1.Column9 = Object5.Column2
WHERE Object2.Column1 <> ? AND Object1.Column8 = ?
AND ( coalesce(Column16,?)= ? )
AND EXISTS (
         SELECT ?
         FROM Object11
         WHERE Column39 = ?
         AND Column30 = Object3.Column30)
ORDER BY Column7 desc
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

here is the query plan

I know that I should maybe add an index on this:

Database1.Schema1.Object7.Column30, Database1.Schema1.Object7.Column36, Database1.Schema1.Object7.Column6, Database1.Schema1.Object7.Column32

but one of this columns is a varchar 4000 and it can't be created cause of the big dimension of the field.

I noticed that it takes 25 second only if the rows returned are fewer than the fetch first number

The execution plan accesses Object7 first using a non covering index in Column7 order. It then does some key lookups on that table and nested loops joins against the other tables with the final joined resulting arriving at the TOP operator still ordered by Column7.

Once this has received enough rows to satisfy the OFFSET ... FETCH it can stop requesting any more rows from downstream operators. SQL Server estimates that it will only need to read 2419 rows from the initial index on Object7.Column7 before this point is arrived at.

This estimate is not at all correct. In fact it ends up reading the entirety of Object7 and likely runs out of rows before the OFFSET ... FETCH is satisfied.

The semi join on Object11 reduces the rowcount by almost half but the killer is the join on Object6 and predicate on the same table. Together these reduce the 9,753,116 rows coming out of the semijoin to 2.

You could try spending some time looking at statistics on the tables involved to try and get the cardinality estimates from these joins to be more accurate or alternatively you could add OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') ) so the plan is costed without the assumption that it can stop early due to the OFFSET ... FETCH - this will certainly give you a different plan.


SQL Server unattended / Silent installation override Configuration.ini file in command line parameter I am creating a PowerShell script to install SQL Server 2012 silent installation.  It is not hard to find a tutorial or instructions online on how to do so. I want to my script be a bit more flexible by keeping the defaults in Configuration.INI file and override the configurations in script arguments.

Does the parameter provided to C:\SQL2012\setup.exe override the configurations in Configuration.INI? For example:

The configuration file has default INSTANCEDIR="C:\Program Files\Microsoft SQL Server", if I provide alternative to setup.exe, does that override it:

C:\SQL2012\setup /INSTANCEDIR="D:\SQL Server\" /ConfigurationFile="C:\SQL2012\ConfigurationFile.ini"

Also, if I have not specified a parameter in configuration file, but I want specify now, such as Data File locations, is the installation OK with that?

# /INSTALLSQLDATADIR is not specified in Configuration.INI file
C:\SQL2012\setup /INSTALLSQLDATADIR="D:\SQL Server\data" /ConfigurationFile="C:\SQL2012\ConfigurationFile.ini"

If you provide a parameter at the command line it will override that parameter in the configuration file. Additionally, any parameters not included in the configuration file can be added to the command line instead.

More info here.


Extent Allocation Mechanism in SQL Server 2017 I was looking at the SQL Server 2017 Documentation for page and extent allocations.

What I got to know from online resources and sessions in SQL conferences: Starting with SQL Server 2016, Uniform Extent Allocation is the default behavior. However, what MS documentation describes is:

  To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data.

  Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
  Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

  A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations

So, my question is: In SQL server 2016 and later, if we create a new table or index, will it be allocated a uniform extent or a mixed extent initially?

In SQL server 2016 and later, if we create a new table or index, will it be allocated a uniform extent or a mixed extent initially?

If the database was created in SQL Server 2016+ then by default the uniform extent allocation is on by default and thus a uniform extent will be allocated even if just a single page is needed (extents are 8 contiguous pages starting from database page 0).

If, however, the database was created in an older version <= SQL Server 2014 then by default the uniform extent allocation database setting did not exist and thus a single page from a shared extent was used. In <= 2014 there was a trace flag that would override this TF1118 setting but it would be for the entire instance and not just a single database. SQL Server 2016 took away the trace flag (which is no a NOP) and made the setting per database with a default of ON.


MS SQL 2012 - Need to split records from 4 different fields then query those So I have a table called 'WORK_ORDER' and the ID is a unique text example '16-0833' next would be 0834, 0835, etc.

In that table there are 4 user fields.. USER_1, USER_2, USER_3, USER_4..

This top work order needs x amount of other work orders to be completed.. so we will in those fields like this.

USER_1 = '16-0838'
USER_2 = NULL
USER_3 = '16-0834;16-0835;16-0835'
USER_4 = NULL

I found a routing that will split them up and if I use this TSQL I get results.

DECLARE @UDF_1 nvarchar(100), @UDF_2 nvarchar(100), @UDF_3 nvarchar(100), @UDF_4 nvarchar(100), @ALL_UDF nvarchar(400);

SELECT        @UDF_1=USER_1, @UDF_2=USER_2, @UDF_3=USER_3, @UDF_4=USER_4
FROM            WORK_ORDER
WHERE        (BASE_ID = N'16-0833')

IF @UDF_1 IS NOT NULL
BEGIN
SET @ALL_UDF = @UDF_1
END

IF @UDF_2 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_2
END

IF @UDF_3 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_3
END

IF @UDF_4 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_4
END

BEGIN

SELECT
    *
FROM
    dbo.func_split(@ALL_UDF, ';')
END

With these results :

  ElementID Element

  1 16-0838

  2 16-0834

  3 16-0835

  4 16-0836

I now need to take those 16-% numbers and requery the Work Order table to get their status, etc.

Just not quite sure how to accomplish this.

Any help is greatly appreciated.   SQL 2012

Welcome to SQL, you can replace your sample code with this:

DECLARE @ParentBaseId nvarchar(100) = '16-0833'

SELECT * FROM WORK_ORDER
WHERE BASE_ID IN
(
SELECT USER_1 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
UNION
SELECT USER_2 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
UNION
SELECT USER_3 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
UNION
SELECT USER_4 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
)

It's not the fanciest or even best way, but is the easiest to start learning with.

By the way, your code won't work if @UDF_1 is null, you need to assign '' to @ALL_UDF before working with it, or add other checks.


Getting the exact edited data from a column in SQL Server I have two Tables:

Articles(artID, artContents, artPublishDate, artCategoryID, publisherID).

ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, upArtOriginalArticleID, upPublisherID)

A user logs in to the application and updates an article's contents in the artContents column. I want to:

Know which Changes the user made to the article's contents?
Store both versions of the Article, the Original version and
the Edited Version!

What should I do for the above two task:

Are there any necessary changes in the tables?
What is the query for getting exact edited data of the artContents column?

(The exact edited data means that there may be 5000 characters in the columns. The user may edit 200 characters in the middle or somewhere else in column's characters. I want the exact edited characters, before the edit and after the edit.)

Note: I am using ASP.NET with C# for Developing

There's a solution for SQL Server. You can create triggers for tracking these changes, tables where the old and new values will be stored and when a trigger is fired, insert the old and new values into the storage tables

You can also use a tool that will track the changes, I use ApexSQL Audit  - doesn't actually store both versions of the complete table, but stores old and new values for the modified column, so you can recreate the old versions. As it stores all changes in database tables, so I've created some custom reports

And to answer your questions directly

  Are there any necessary changes in the tables?

Yes, triggers are added

  What is the query for getting exact edited data of the artContents column?

It doesn't show just the modified characters, but the old and the new column values


Adding a prefix to PIVOT columns I'm PIVOTing a table such that the column names in the final table are based on the "organization name" in the source table.  The problem is, the "organization name" is data-driven and could be any set of values; I use dynamic SQL to build up the PIVOT's IN clause.  This means that the value could match another column name I have, like the RecordId I'm pivoting against.  So I have something like this:

.----------------------------------------------------------------------.
| RecordId | OrganizationFoo | OrganizationBar | OrganizationBaz | ... |
|----------|-----------------|-----------------|-----------------|-----|
| 123      | 182             | 76              | 56              | ... |
| 234      | 846             | 0               | 182             | ... |
| 345      | 46              | 2               | 951             | ... |
...

The problem is, this is all data-driven so it would be fine for an organization name to be RecordId, which would cause an invalid SQL query to be generated because of duplicate columns.  Is there a way I can automatically prefix something to every column in the PIVOT output?

If not, the only 3 ways I can think of doing it are:

Select CONCAT('Prefix_', [OrgName]) instead, but this will probably reduce efficiency because [OrgName] is indexed.
Create a duplicate table to the one holding [OrgName], and each time this query is run, clear it and insert the contents of the original table but with [OrgName] having the prefix instead.
Just always store [OrgName] with the prefix in the first place.

Any cleaner/better options?

The index use seems irrelevant to the concatenation.
The other 2 solutions are awkward.

Demo

create table t (x int,code varchar(100))

insert into t values
    (1,'X'),(1,'X'),(1,'Y'),(1,'Y'),(1,'Y'),(1,'Z'),(2,'X'),(2,'X'),(2,'Y')

select  *
from    t pivot (count(code) for code in([x],[y],[z])) p

  Msg 265, Level 16, State 1, Line 13 The column name "x" specified in
  the PIVOT operator conflicts with the existing column name in the
  PIVOT argument.
  Msg 8156, Level 16, State 1, Line 13 The column 'x'
  was specified multiple times for 'p'.

select  *
from    (select x,'my_prefix_' + code as code from t) t
            pivot (count(code) for code in([my_prefix_x],[my_prefix_y],[my_prefix_z])) p

+---+-------------+-------------+-------------+
| x | my_prefix_x | my_prefix_y | my_prefix_z |
+---+-------------+-------------+-------------+
| 1 | 2           | 3           | 1           |
+---+-------------+-------------+-------------+
| 2 | 2           | 1           | 0           |
+---+-------------+-------------+-------------+


How to compare performances of two databases There are two database servers and I want to compare their performances.

Both have Windows operating system and and Oracle (10.2.0.4 and 11.0.2) database.

Both are OLAP databases with 15-20 users each. Hardwares are different but I don't have much info about configurations.

There are similar tables on both of them and I used explain plan to compare performance firing same sql queries to exactly same tables.  (58M rows, 60 columns).

I used select * from TABLE  and select count(*) from TABLE

One explain plan shows 00:35:00 and other shows 01:45:00 for time column.

I want to learn if comparing explain plans is enough to compare performances, and if not what shall I do?

You can not compare the performance of 2 databases by just looking at their sql plans. The same queries can have very different plans. When the versions are different all kinds of computations for the cost are different. The plan that a query follows in execution could very well be different than the plan you get from explain plan from your client.

The only way to compare the performance is to measure the execution of the queries or processes and compare those timings. You could use AWR to compare the database performance. A certain workload will generate a certain thoughput and consume more or less dbtime. If the dbtime spent and the elapsed time of your jobs is lower, you can safely say that the performance in that database is better compared to the one where the times are higher.

In 11g, with the auto tuning tasks enabled and auto accept sql profiles enabled, it could very well be that the next run already shows better timings.


SQL Agent doesn't have perms to run a PowerShell script I recently upgraded SQL Server from Windows 2008 R2 & SQL Server 2008 R2 to Windows 2012 R2 & SQL Server 2014 SP1. One of the sql agent jobs fails to run. The job runs a PowerShell script to script sql agent jobs to disk. The job fails with an error:

  SSPI handshake failed with error code 0x8009030c

If I RDP to the server and run the PS script in ISE with the SQL Server Agent service creds, it works.

Any ideas?

Here's the PS script:

param([String]$ServerName)

$OutputFolder = "\\backupfolder\sqlbackups\sqlservername\SQLAgentJobs"

#Load the SQL Server SMO Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

Try
{
    $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
    $objSQLConnection.Open() | Out-Null
    $objSQLConnection.Close()
}
Catch
{
    $errText =  $Error[0].ToString()
    Write-Host $errText
}

$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName

foreach($job in $srv.JobServer.Jobs)
{
    $job_name = $job.Name
    $job.Script() | out-file "$OutputFolder\$job_name.sql"
}

I had a similar problem when running powershell scripts from a sql server job.

  If you do not have remote PowerShell enabled on both servers and
  firewall access between the servers configured properly it will have
  issues.

Run a PowerShell script on a different server - from inside a SQL Server job

the way I have solved this problem is through a proxy account.

I have created a proxy account in sql server,
that had all the relevat permissions to the remote server, folders and files that I needed and run the sql job as that account.

a proxy account in my case was a domain account, and I have granted this account the privileges on the remote server.

this might help with proxy:
How to get the AD account associated to a Proxy?

From this link below you can see:

powershell problem when running on a remote server - access is denied

As far as workarounds, there is this answer on SU. you can read the article in detail that the answer points to fully understand, but this is the main command to adjust permissions:

  If you decide you want to allow others, what you do is run the
  command:

Set-PSSessionConfiguration -Name Microsoft.PowerShell -showSecurityDescriptorUI

  Notice that this action could have a serious impact on your system so
  we ask you to confirm that you really want to do this.


Small sample database for practicing database administration? I'm looking for a portable sample database that

can be created from a script
has a simple ER model
has little data for database administration practicing purposes.

I know that there is e.g. Adventureworks, Northwind and pubs sample databases out there, but I'm looking for something smaller.

Any idea where I can find this kind of database?

Take a look at the Dell DVD Store database. It's available for multiple databases, SQL Server included.

  The Dell DVD Store is an open source simulation of an online ecommerce
  site with implementations in Microsoft SQL Server, Oracle, MySQL and
  PostgreSQL along with driver programs and web applications


How do I refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?) I have 2 seperate SQL databases on the same SQL instance. Database1 references tables on Database2 in its views and stored procedures. Everything was working fine until we had to create a fresh version of Database2 and rename the current Database2 to Database3.

The DBA did the following:

Took Database2 offline, then reattached it as Database3. Database2 was then deleted from SQL.
A new version of Database2 was created.

The problem:

The view and stored procedures in Database1 are still referencing the tables on Database3 unless we add the server name to the FROM clause in any queries.

The following view on Database1 goes to Database3 in error:

select * from Database2.dbo.Projects

For the correct behaviour we specify the server name:

select * from ProductionDB.Database2.dbo.Projects

Why are the views and stored procs of Database1 still referencing Database3 (unless we specify server name)?

Is it because we renamed the database by re-attaching instead of backup and restore?

Are there internal references in Database1 that need updating?

Are there hidden database synonyms I can't find?

sql sql-server backup database-administration cross

You might want to run sp_refreshview, it is recommended to run this if the underlying tables change at all (this case seems to be a good candidate for that case).

I found the underlying cause. The parse trees of views are generated and cached:

select * from sys.syscacheobjects
where objtype = 'View'

This is the output of the tokenizer, so an attach detach should invalidate cached tokens. An sp_refreshview rebuilds the parse tree.

From the documentation for sp_refreshview:

  Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.


How does pgAdmin III build CREATE statements? In PostgreSQL, there is no SHOW CREATE statement, known for example from MySQL. But when you use the pgAdmin III client program and go to object details, you see the corresponding CREATE statement.

So here is my question:
How does pgAdmin III build the CREATE statements it shows? Does it use psql's \d command or some completely different PostreSQL API? If so, how can I use it?

pgadmin has it's own reverse-engineering functionality - it just examines the system tables like pg_class and pg_attribute to find the details.

If what you're looking for is actually an easy way to show the CREATE statements for your objects that you can use from something else, you should look at pg_dump instead of pgadmin, it is much simpler. In particular, if you just want to look at it for one or more object, you can use pg_dump -s -t tablename and it will give you the required CREATE statements.


Table Relationships for Property Bags Configuration I am creating a database table setup for user preferences in my application based partially on this design and while I created the tables and foreign keys, I can't seem to figure out the relationships of these tables. The attached screenshot is the DB design and below is what I currently believe are the correct relationships

user to user_preference = one-to-many

user to preference = many-to-many

user_preference to user = many-to-one

user_preference to preference = many-to-one

user_preference to allowed_preference_value = many-to-one

preference to allowed_preference_value = one-to-many

You've captured the relationships in the design that you've referenced correctly.  The one nuance that isn't clear in what you've documented is that the relationship from user_preference to allowed_preference_value is optional, meaning that user_preference.allowed_preference_value_id is nullable.  It's not clear whether you've accounted for this or not, but it matters if you have user preferences which are not selected from pick lists but are instead taken from another source.

Your design also omits the preference values themselves.  I'm assuming that was a deliberate choice to focus on the relationships and gloss over the rest.


Cannot connect in SSMS with Windows Authentication I found and added the server name through <Browse for more...> -> Network Servers so it exists and is correct.

What seems weird is that the Password field is empty and I cannot modify it...?
And when I try to Connect, I get this:

I am complete noob in DB management, so guide me thoroughly, please.

In windows authentication, you don't have to type the password, it will validate by AD.

The reason for Login error could be your login does not have access to the database server.

Please ask the database administrator to grant access to your login.


PostgreSQL Get top-k minimum per range Assume I have the following data:

| f1 | f2 | f3 |
|----|----|----|
|  1 |  1 |  1 |
|  1 |  1 |  5 |
|  1 |  2 |  3 |
|  1 |  2 |  6 |
|  1 |  3 |  4 |
|  1 |  3 |  7 |
|  2 |  1 |  2 |
|  2 |  1 | 22 |
|  2 |  2 |  3 |
|  2 |  2 |  4 |

There are two f3 values maximum per each f1,f2 combination.

For this specific example, I want to get top-2 minimum values per f1 and range (f2 - max(f2) per f1).

Example output:

| f1 | RNG| f3 |
|----|----|----|
|  1 |1 -3|  1 |
|  1 |1 -3|  3 |
|  1 |2 -3|  3 |
|  1 |2 -3|  4 |
|  1 |3 -3|  4 |
|  1 |3 -3|  7 |
|  2 |1- 2|  2 |
|  2 |1- 2|  3 |
|  2 |2- 2|  3 |
|  2 |2- 2|  4 |

To create the field RNG is not required. I only added it to show that for f1=1, there are 3 ranges: 1-3, 2-3, 3-3 created by the distinct values of f2 for f1=1. For each such range I want to calculate the top-k minimum values per f1 and range.

SQL Fiddle here:

http://sqlfiddle.com/#!15/9ddbb/1

Building the Ranges may be done by:

SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2;

Is there any way to achieve that, without building intermediate DB tables?

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

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

Test at SQLfiddle.

Or without the CTE:

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


Streaming replication archive folder taking all space- Postgresql- 9.4 We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine.

We created few databases in master and it is getting replicated to slave servers.
Our Postgresql version is 9.4 and data directory is "/var/lib/pgsql/9.4/data" in both master and slave.

While setting up streaming replication, we edit postgresql.conf in master

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.4/archive/%f'

Our recovery.conf in slave is

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.10 port=5432 user=replication password=password'
trigger_file = '/var/lib/pgsql/9.4/trigger'
restore_command = 'cp /var/lib/pgsql/9.4/archive/%f "%p"'

I also added below command to clear the old archive in recovery.conf in slave

archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/9.4/archive %r'

but I started seeing below error in pg_log of slave.

< 2019-01-16 09:02:48.937 EST >WARNING:  archive_cleanup_command "pg_archivecleanup /var/lib/pgsql/9.4/archive %r": child process exited with exit code 2
pg_archivecleanup: archive location "/var/lib/pgsql/9.4/archive" does not exist
< 2019-01-16 09:07:45.927 EST >WARNING:  archive_cleanup_command "pg_archivecleanup /var/lib/pgsql/9.4/archive %r": child process exited with exit code 2

After doing some research I realized that problem is genuine as the slave is not having this directory "/var/lib/pgsql/9.4/archive" and its present in master server only.

Now, we removed "archive_cleanup_command" from recovery.conf (slave) but in our master server "/var/lib/pgsql/9.4/archive" is taking so much of space. We need to clear these old files inside "archive".

I had few doubts and wanted to take some suggestions to resolve the problem :-

Can you suggest what can be done so that we don't run into every day "No space left in /var of the master" issue ?
Can we run the pg_archivecleanup from master server and remove the old files like below

pg_archivecleanup -d archive 000000010000003700000010.00000020.backup
pg_archivecleanup:  keep WAL file "archive/000000010000003700000010" and later
pg_archivecleanup:  removing file "archive/00000001000000370000000F"
pg_archivecleanup:  removing file "archive/00000001000000370000000E"

Should I setup streaming replication again with some mount point attached to both master and slave and save the archive of master to "mount point" and then add "archive_cleanup_command" in recovery.conf?

Does the archive exist only to support the streaming replication, or do you want it for other reasons like PITR?  (Since you attempted to configure pg_archivecleanup in the first place, I guess I can assume you don't want it for other reasons...)

Because your restore_command is currently broken, then you know that the WAL archive has not been needed thus far to support streaming replication--because if it had been needed it would not have worked.  That is, your replica has never fallen far enough behind that the master has removed the WAL from pg_wal before the replica had fetched it.  If you think that this will always continue to be the case, then you just turn achive_mode = off and delete your current archive.  There is no point in maintaining an archive that is not and cannot be used.

Since the introduction of replication slots, pg_archivecleanup is more-or-less obsolete.  If you want a backstop in case the replica doesn't naturally keep up, you can make a shared mount point and use pg_archivecleanup on that mount point if you want. But configuring a replication slot is a better way to accomplish the same thing.


Table partitioning on OLTP On SQL Server 2017 Std, I have an SSIS package that runs once a day on a 24×7 mission critical operational database to archive the largest transactional tables. The data is moved to tables on a separate archive database and the process runs smoothly with minimum locking contention on the live db (deletes are spread through a long period of time). Having a separate archiving database gives us many advantages: backup databases separately; refresh testing environments with current data only; improve query performance on current data, among others. The only drawback was code change (to add logic to union on archived data when requested by the user on the UI) and a slower cross-database query when needing to fetch archived data.
We are upgrading to Enterprise edition and considering table partitioning for data archiving, but the more I read about it the more worried I get in relation to the implementation and maintenance overhead. The only real advantage I can see so far is the transparency to the application, not requiring code changes.
Did any of you use table partitioning for data archiving? Is it really worth it on OLTP systems? Thanks very much in advance.

With partitioning you have a couple of additional options.

Switch older partitions to a separate table in the same database.  Query is still not transparent, but advantage of is primarily that a partition switch is a metadata-only operation, only requiring a brief schema lock and not generating a significant amount of log writes.

Partition and leave the older rows in the partitioned table, perhaps setting an 'archived' status column.  The older partitions can be compressed differently, and if there is some column in that differentiates the archived from non-archived rows and that column is universally used as a filter by the OLTP application, then you can create filtered indexes that differentially index the archived and non-archived rows.

And on SQL 2017 you have the simple option to make the table a Temporal Table.  You would continue to DELETE older rows, but SQL Server will copy the deleted rows to the history table, manage the retention of archived rows, and provide AS OF views across the main and history table.
BTW Table and Index partitioning is available in every edition of SQL Server 2016 SP1+.


How to check with update trigger if only one column was updated postgresql I have a table with many columns and one special column that indicates if is synchronized or not. When an update occurs in the other columns I processed and update a 'log' table, this updates comes with the flag not synchronized, but when this was processed, I update the same row with a flag synchronized , in this case I do not to write in the log table.
example:

Table: user( name, last_name, age, ..., sync_status )
IF (TG_OP = 'UPDATE' ) then

     if new.sync_status != syncstatus_pending then
        if old.sync_status = syncstatus_pending and new.sync_status != old.sync_status then
           raise notice 'Sync status updated, It means we are changing from sync pending to sync yes';
           return new ;
        else
           raise Exception 'The synchronization status is not configured correctly.' ;
        end if ;
    end if;
    -- Check if we are trying to update a not synchronized row
    if old.sync_status != syncstatus_yes then
       raise Exception 'It was not possible to update rows are not synchronized.';
    end if ;
    if new.name = old.name and new.last_name = old.last_name and new.age = old.age
    and old.sync_status= syncstatus_yes and new.sync_status= syncstatus_pending then
   -- all columns are the same except the sync one, it means that we require sync this row but not to add to log_table
    return new;
   else
   -- process to insert in the other 'log' table
   -- ...
    insert into log_table ( )
   end if ;
end if ;

is there any other way to check that only one column is being updated? ( indicating that is synchronized or not )

Instead of using the value "syncstatus_yes", you could use your Syncstatus column to store a binary code that represents the columns you are updating.... and then move the syncstatus as it is to the log. This way you will know exactly which columns you are syncronizing...

whether you use your app code or either the trigger OLD and NEW values, you can know exactly which columns are being updated. Then you can use those columns to build your Syncstatus column

For example: (suppose you have 6 columns + syncstatus column)

Syncstatus = 19 = 010011 = power(2,(6-2))+power(2,(6-5))+power(2,(6-6))

this means that columns 2, 5 and 6 are being updated.


mysql - is it possible to create a partition expression based on two keys / indexes? if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like:

CREATE TABLE `t1` (
    id_1 bigint unsigned not null,
    id_2 bigint unsigned not null,
    col3, col4 ... colN ...
    KEY `index1` (`id_1`),
    KEY `index2` (`id_2`),
    ...
) ENGINE=InnoDB;

100% of the queries on the table are using WHERE clause on index1 OR index2.

Taking this into account, and assuming it is now very reasonable time to partition that table, into 100 partitions, I would like to ask your help to understand these issues:

Is it possible to create a partition expression in such a way that after the partitioning, for any query that using one of these indexes:

SELECT * FROM `t1` WHERE id_1 = 123;
-- or
SELECT * FROM `t1` WHERE id_2 = 456;

the engine will scan one partition only?
issue 1 must happen without changing the indexes. I mean, without making back references in them, because it will be too much RAM consuming. So, this must not happen:

KEY `index1` (`id_1`,`id_2`),
KEY `index1` (`id_2`,`id_1`),

Am I thinking in a wrong direction? How would you solve this issue with Amazon RDS 7.5 GB RAM instance ?

feel free to edit this question, to make it more possible to be answered, thanks

reference to stackoverflow.com

I had written an interesting alternative of implementing your own manual hash indexes for your table, and then I made maths and realised your constraints:

Having into memory 3 bigints will cost you 500*10^6*(8*8*8)/(1024*1024*1024) = 11.17GB that you do not have. RDS is simply not adequate for you anymore, as it is not flexible enough to try some alternative engines -other than InnoDB (you need an engine that works well with indexes on disk/clustering on several keys/hash indexes)- and probably too costly to handle a large table like that.

You need either a higher-end instance or migrate to EC2 to deploy an alternative engine.

Best recommendation that I could give you for your current constraints (7GB ram, InnoDB):

Use your most frequently accessed keys as your primary key, partition by RANGE on that (lets call it id_1). Do not create any other secondary keys:

CREATE TABLE `t1` (
    id_1 bigint unsigned PRIMARY KEY,
    id_2 bigint unsigned not null,
    col3, col4 ... colN ...
) ENGINE=InnoDB
PARTITION BY RANGE (id_1) (
    PARTITION p0 VALUES LESS THAN (n),
    PARTITION p1 VALUES LESS THAN (m),
    ...
);

Create a separate table with (id_2, id_1):

CREATE TABLE `t1_id2_index` (
    id_2 bigint unsigned PRIMARY KEY,
    id_1 bigint unsigned not null,
) ENGINE=InnoDB
PARTITION BY RANGE (id_2) (
    PARTITION p0 VALUES LESS THAN (n),
    PARTITION p1 VALUES LESS THAN (m),
    ...
);

Obviously you will have to insert on this second table each time you insert on the first one. You may think this is worse, but it will not be that bad as you are getting rid of huge merging processes of the secondary keys and minimising memory usage (which is your goal, afterwards).

This will only access 1 partition on access by id_1 and 2 partitions (one on each separate table) on access through id_2:

SELECT * FROM `t1` WHERE id_1 = 123;
-- or
SELECT STRAIGHT_JOIN t1.*
FROM `t1_id2_index`
JOIN `t1`
ON t1_id2_index.id_2 = 456
   and
   t1.id_1 = t2.id_1;

If your most frequent accesses are on the latest partitions, you will get the desired improvements -make sure you partition with that in mind. You can check partition pruning by using EXPLAIN PARTITIONS. Of course, if access patterns are completely random, you will not get any advantage. The goal is to maintain everything on disk except for a small set of primary keys for both id_1 and id_2 and selected rows.

You may want to minimise read ahead caching and tune innodb_old_blocks_pct and innodb_old_blocks_time for more effective caching/eviction on the buffer pool. I hope you are also using SSDs.

This is not beautiful, but please refer to my initial suggestion of migrating away from SAAS for custom requirements.


Where to put standby control file in Physical standby database? I am creating a physical standby database for which I am creating a standby control file using command = ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.ctl';

but It creates only one control file while I using 4 control files like:

O:/cntrl_ims01/CNTRLIMS.ctl, O:/cntrl_ims02/CNTRLIMS.ctl, L:/cntrl_ims03/CNTRLIMS.ctl, L:/cntrl_ims04/CNTRLIMS.ctl

, on standby host I am using same paths and structure as primary host, so where to place this single control file. ??

You can copy the same standby file controfile to all the locations and use it.
All control_files have the same content.

If you are creating new standby, I would suggest to follow below method (which uses active duplication): https://dbaclass.com/article/how-to-create-physical-standby-database/


Is this surprising deadlock reasonable or a MariaDB bug? I recently had to diagnose a deadlock in an application that seemed impossible to me. Even after I found out how to reproduce it, I don't really undestand why it happens. I'm considering filing a MariaDB bug, but since I'm not a database expert, I thought I'd ask first if someone can tell me there is a reasonable explanation.

Edit: Filed as a bug now at https://jira.mariadb.org/browse/MDEV-17512

I used a recent version of mariadb (will have to look up the exact version number, but I think that's more interesting for a bug report than for a general "why does this happen") with its default transaction isolation level of REPEATABLE_READ. Here is how I can reproduce the problem:

First, let's set up the test:

create database deadlock;
use deadlock;
create table foo(id int primary key) engine=InnoDB;
insert into foo values (10);

Now we can start. Let's open two connections A and B to the database.

-- Connection A
begin;
delete from foo;

-- Connection B
begin;
delete from foo;

At this point, the delete issued by B is waiting. This is as expected because A is holding the relevant locks.

-- Connection A
insert into foo values (9);

And at this point, mariadb kills the waiting query of B due to a deadlock. This is very surprising to me, because it would mean that B has already managed to acquire some locks.

As an additional point of information, if you use the id 11 instead of 9 in the last statement, there is no deadlock.

So can someone explain what is happening here? Is this normal behaviour, or should I file a bug?

Edit:
Here is the output of SHOW ENGINE INNODB STATUS:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-10-15 21:33:09 0x7f9f9da02700
*** (1) TRANSACTION:
TRANSACTION 481736, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3049, OS thread handle 140323520444160, query id 12527706 localhost root updating
delete from foo
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481736 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** (2) TRANSACTION:
TRANSACTION 481731, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3050, OS thread handle 140323521046272, query id 12527773 localhost root update
insert into foo values(9)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481731 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481731 lock_mode X locks gap before rec i
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** WE ROLL BACK TRANSACTION (1)

A guess...

DELETE without WHERE needs to lock the entire table.  Perhaps that is done via "start through current end", where the 'end' is at 10.  Hence messing with '9' would be problematic.

On the other hand, '11' is off the end, so less ambiguity.

Are you arguing that neither should deadlock?  Or that both should deadlock?

Here's another aspect...  To do everything "correct" in every case would possibly be very complex.  So some shortcuts have been taken.  And your test case pulls out a case where the shortcut led to an inconsistency.

Or is it "right" to have the inconsistency?  One thread is deleting "old" stuff; the other thread is adding "new" stuff.  Think of a queue -- this is exactly what you need.


Howto specify my own id value when upload a stream into a gridfs file, using the GridFSBucket API? I'm using TypeScript, MongoDb 3.6 and the mongodb 3.0 driver. Here is how I do it:

this.gfs = new GridFSBucket(this.media_db, {bucketName: 'original'});
const reader = fs.createReadStream(path);
const writer = this.gfs.openUploadStream(result.sha1);
reader.pipe(writer);
console.log("STORE ORIGINAL MongoDb/GridFs")
const waiter = new Promise((resolve, reject) => {
    reader.on('end', ()=>resolve(true));
    reader.on('error', reject);
});
await waiter;

There is a big problem with this setup. Here is my former question about creating indexes on gridfs collections:

GridFS: why can't I shard chunks with a hashed key?

I got an answer that explains how these should be indexed: the file id should have a uniform distribution (e.g. a hashed key). The default id value is not like that, so I must provide my own file ids if I want them to be distributed over multiple nodes from the beginning. The chunk ids are a different story - they are generated objectid values, and they should be left at peace. Using generated objectid values for chunks allows MongoDb to perform a range query on the chunks, resulting in a much more efficient query plan (e.g. when a data stream needs to be reconstructed for a file.)

The question is this: using the official API and the GridFSBucket class, how can I specify my own file id? It is how it should be done, but I don't see any way to do it. Here is the signature of that method:

openUploadStream(filename: string, options?: GridFSBucketOpenUploadStreamOptions): GridFSBucketWriteStream;

It only has a filename parameter and an options parameter. Options is like this:

export interface GridFSBucketOpenUploadStreamOptions {
    chunkSizeBytes?: number,
    metadata?: Object,
    contentType?: string,
    aliases?: Array<string>
}

If this can't be done, then there is no way of avoiding hot shards when mass ulpoading files to gridfs. (Then I probably need to submit an issue.)

The solution is the openUploadStreamWithId method.It has this signature:

openUploadStreamWithId(id: GridFSBucketWriteStreamId, filename: string, options?: GridFSBucketOpenUploadStreamOptions): GridFSBucketWriteStream;

The GridFSBucketWriteStreamId type is like this:

type GridFSBucketWriteStreamId = string | number | Object | ObjectID;

So you can call it like this:

openUploadStreamWithId(your_custom_file_id, your_file_name)

If the custom file id is uniformly distributed and chunks are sharded on (files_id, chunk_id) then this will prevent hot shards, and allow range queries on the chunks at the same time.


Inconsistency in Relational Databases I generally get confused when the term consistency is used. NoSQL tutorials always refer to the reads whereas Relational tutorials refer to a consistent state (ensuring referential integrity constraint)
When the data is distributed across multiple servers (ex
1 master- n slaves configuration)

Do relational databases ensure consistency in reads? I mean a committed write is immediately available to be read by other transactions? I suspect not given network is involved, making Relational DBs as eventual consistent DBs.
Do relational databases ensure referential integrity constraint well?

To directly answer your questions:

"Do relational databases ensure consistency in reads?"

A: In short, when the ACID principals, and specifically the Consistency part, were defined, this was long before distributed data was a concept for relational databases. So the Consistency from that standpoint was automatic because there's only a single server at play. Once a transaction was committed, the server was immediately Consistent with itself, regarding that transaction and all constraints associated with it, there were no other servers to synchronize and commit to. (Note this only focusses on one part to the meaning of Consistency in the ACID principals for the relevancy of OP's question regarding distributed data.)
Nowadays there's a multitude of ACID compliant relational database systems out there that are designed to handle distributed data across multiple servers. But let's take even Microsoft SQL Server and their AlwaysOn Availability Groups feature as an example for a moment. This feature is meant to be a High Availability / Disaster Recovery feature by synchronizing data from your primary server to other secondary servers. It's also ACID compliant because it can be configured to ensure Consistency across servers (when set to Synchronous mode). In a very rudimentary explanation on how it works is  it only fully commits a transaction once that transaction has been synchronized to all secondary servers from the primary server. This guarantees Consistency across all servers, at all times, and allows it to remain ACID compliant in a distributed server environment.

"Do relational databases ensure referential integrity constraint well?"

A: There's not really a way to quantifiably answer that question, but generally speaking, yes they do. Referential Integrity is one of the main points of using a relational database. When there's a well relationally defined schema, with proper constraints defined, such as foreign keys for example, a relational database is guaranteed to always enforce the rules of those constraints to ensure it always enforces the appropriate referential integrity. This ties into the definition of Consistency and how a relational database is ACID compliant.


Getting SQLSTATE in psql error messages I have the following problem.

SELECT * FROM tgvbn();
ERROR:  function tgvbn() does not exist
LINE 1: SELECT * FROM tgvbn();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Since Appendix A of the official documentation only states error_codes and conditional names but not the actual messages, one can only guess that "No function matches..." refers to 42883 / undefined_function.  I can set log_error_verbosity = verbose in postgresql.conf - but that will affect the logs, not the messages returned to the client:

  Controls the amount of detail written in the server log for each
  message that is logged.

Well, I can consult the logs for this piece of information, but is there any means of including it in the error messages?

Based on a_horse_with_no_name's comment, I started searching around psql and found the solution:

\set VERBOSITY verbose
SELECT * FROM tgvbn();

ERROR:  42883: function vfjkb() does not exist
...

Now that goes into .psqlrc.  Details and further options can be found in the psql documentation.


PostgreSql : flatten json array data From my current query, I obtain this jsonb data :

values: "a1", ["b1", "b2"]

And I want to flatten it on one level only, like this :

values: "a1", "b1", "b2"

Here is a simplfied way to get data in a query (only 2 levels are possible, never more):

SELECT *
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test');

I tried to use jsonb_array_elements but my problem is that : I don’t know if it is a json array or not ! Not expert in SQL, I did not find a way to code something like :

SELECT
    IF (is_json_array(list))
        jsonb_array_elements(list)
    ELSE
        list
    ENDIF
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test');

For a «zoom out» view of my current data, here is a table-free working test :

with recursive search_key_recursive (jsonlevel) as(
    values ('{"fr": {"WantedKey": "a1", "Sub": [{"WantedKey": ["b1", "b2"]}], "AnotherSub": [{"WantedKey": "c1"}]}}'::jsonb)
    union all
    select
        case jsonb_typeof(jsonlevel)
            when 'object' then (jsonb_each(jsonlevel)).value
            when 'array' then jsonb_array_elements(jsonlevel)
        end as jsonlevel
    from search_key_recursive where jsonb_typeof(jsonlevel) in ('object', 'array')
)
select search_key_recursive.jsonlevel->'WantedKey'
from search_key_recursive
where jsonlevel ? 'WantedKey';

For after, I will use the result in an insert statement :

INSERT INTO table1
SELECT 'someText', value
FROM jsonb_array_elements('{"test": ["a1", "b1", "c1"]}'::jsonb->'test');

Got it !

SELECT
case jsonb_typeof(json)
    when 'string' then json->>0
    when 'array' then jsonb_array_elements_text(json)
end
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test') as json;


Select count(*), where 2 rows with different values exists I have following table structure and data:

+-----------+-------+
|type       | value |
+-----------+-------+
|first_name | john  |
+-----------+-------+
|last_name  | doe   |
+-----------+-------+
|last_name  | smith |
+-----------+-------+

I need to get count (in my case it's 1), if table contain data John Doe. Query like:

SELECT COUNT(*) FROM tablename WHERE (type='first_name' AND value='john') AND (type='last_name' AND value='doe')

returns zero count. Don't understand, how to create this combined query.

I think I found the solution for this case. My solution is to use SUM() aggregation with IF() condition. The final logic is substitute COUNT(*) to boolean - have rows or empty query. This query will return results only if it match both conditions:

SELECT SUM(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS cnt FROM tablename HAVING cnt=2;

If I want to select all data where first name is John and last name is Doe OR last name is Smith, query will be:

SELECT sum(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS first_last, SUM(IF(value='smith' AND type='last_name', 1, 0)) AS last FROM tablename HAVING first_last=2 OR last=1;


How to select blocks from a result by condition? Example table:

master_id 1 created_at 22.02.1997
master_id 1 created_at 22.03.1997
master_id 1 created_at 22.04.1997
master_id 1 created_at 22.07.1997
master_id 1 created_at 22.08.1997
master_id 1 created_at 22.10.1997

Example query result:

master_id 1 date_from 22.02.1997 date_to 22.04.1997
master_id 1 date_from 22.07.1997 date_to 22.08.1997
master_id 1 date_from 22.10.1997 date_to 22.10.1997

Note: The interval between dates must not be longer than one month for a block.

A classical gaps-and-islands problem:

SELECT master_id, min(created_at) AS date_from, max(created_at) AS date_to
FROM  (
   SELECT *
        , count(*) FILTER (WHERE step) OVER (PARTITION BY master_id ORDER BY created_at) AS range
   FROM  (
      SELECT *, created_at > lag(created_at) OVER (PARTITION BY master_id ORDER BY created_at) + interval '1 month' AS step
      FROM   tbl
      ) sub1
   ) sub2
GROUP  BY master_id, range
ORDER  BY master_id, range;

Related (with explanation):

Combining separate columns into smallest possible related ranges
Select longest continuous sequence
Calculate Difference in Overlapping Time in PostgreSQL / SSRS


AlwaysOn Statistics getting updated on Readonly node immediately after updating primary node with Fullscan Version:
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
    Oct 20 2015 15:36:27
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: )

Once I update my statistics with full scan, with 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample. I ran extended event trace to capture auto update stats on secondary node but nothing was logged.
On primary node once I run this:
Update statistics Tablename with fullscan
I see this about my stats status

When I query for the stats on secondary I get this. Look at the rows_sampled and last_updated column.

I also ran an extended event trace to capture the auto update stats but nothing was logged.  Here is the defination of my XE trace.

CREATE EVENT SESSION [AutoUpdateStats] ON SERVER ADD EVENT sqlserver.auto_stats(
ACTION(sqlserver.client_app_name,sqlserver.server_principal_name)
WHERE ([package0].[equal_uint64]([database_id],(25))))WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)GO

I did open a case with Microsoft Support. I was notified similar behavior was reported by other customers. For now the solution is to rebuild the index.  Microsoft is investigating further about the root cause.


How database buffer pool work? For example we have 2 scenarios:

Scenario 1:-
             We have very small buffer pool size of 100MB. My query data is 500MB. Does the         system keep 500MB query data in RAM such that a new query will read from RAM?

Scenario 2:-
           Buffer pool size is 1GB. In this situation will the system always keep 1GB free (in RAM) for MySQL query processing. Can the 1GB be used by other processes?

The amount of memory specified in innodb_buffer_pool_size is allocated when the MySQL Server daemon starts up, and if the system doesn't have that much memory free at that moment, MySQL won't start.

The amount of memory you specify is what the buffer pool gets, and it neither grows nor shrinks.

InnoDB reads pages from disk into the pool as they are needed for queries, and the data remains in the pool for future queries until the least-recently-used data is eventually evicted by different data, when the pool is out of space... but the allocated memory is not available for any other process.

MySQL will request additional memory from the system for various other buffers needed by individual threads, but when the buffer pool itself is exhausted for space, no additional memory will be used for storing cached table pages.  Anything that doesn't fit into the buffer pool will not persist in memory for subsequent access.

Ideally MySQL runs on its own hardware.  An environment where MySQL is competing with other processes for memory is not optimum.  This is particularly true with a web server like Apache on the same machine, which commonly causes problems under load, because both the web server and the database are making demands for memory at the same time.


In a data warehouse, should a measure be based on a fact or a dimension? Let's say there is a data warehouse created from a shop data. A fact is a single purchase of a product. There is a dimension that describes a customer.

There is a need to create a measure that stores a number of distinct customers. Can this measure be created based on customer identifier in the dimension table, or it needs to be fact table? In which cases one or the other solution is better?

Below I post a visualization based on an AdventureWorks2016 database:

The problem with contrived examples like the one you provide is that whatever answer you find, it won't be helpful in a real life situation. "A number of distinct customers" is useless as a measure, because it doesn't represent anything a business would care about.

Now, there could be more useful measures, such as "a number of distinct active customers per month", or "a number of distinct customers per geo", or "monthly customer churn", and from their very definitions you immediately see what information is required to compute them.


Update a column based on another's table column value I have a table Users

+----------+----------+
|  ID_EMP  | isActive |
+----------+----------+
|   100    +    1     +
|   101    +    1     +
+----------+----------+

and a table UsersInfo

+----------+------------+
|  ID_EMP  |   FRDATE   |
+----------+------------+
|   100    +   null     +
|   101    + 2019-10-10 +
+----------+------------+

I tried to update column Users.isActive with 0 when the UsersInfo.FRDATE is not null. I read similar questions and ended up with the following query, which is not working as expected, as it is updating all the columns with 0

UPDATE [dbMyDB].[dbo].[Users]
SET [dbMyDB].[dbo].[Users].[isActive] = 0
FROM [dbMyDB].[dbo].[Users] USERS
INNER JOIN
    [dbMyDB].[dbo].[UsersInfo] INFO
ON USERS.ID_EMP = INFO.ID_EMP
WHERE EXISTS
(
    SELECT FRDATE FROM [dbMyDB].[dbo].[UsersInfo]
    WHERE FRDATE IS NOT NULL
)

what I am missing?
Thank you!

UPDATE users
SET isActive=0
FROM users
JOIN userinfo
    ON users.id_emp = info.id_emp
   AND frdate IS NOT NULL

?


What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT? What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?
As far as I can tell, they seem to do the same thing. Is there some difference in side-effects, or how they perform for certain inputs? How do I know which to choose?

They are both the same function but one was specifically designed for IBM PureData System for Analytics, which is the regexp_extract() function. The other function REGEXP_SUBSTR() is for DB2 only and doesn't exist on PureData System for Analytics.
Use the one that correlates to whichever system you're working in (seems like DB2).


How to Convert Data to Proper Case In SQL Server SQL Server contains system functions for viewing / updating string data to both uppercase and lowercase but not proper case. There are multiple reasons for desiring this operation takes place in SQL Server rather than at the application layer. In my case, we were performing some data cleanup during a consolidation of our global HR data from multiple sources.

If you search the internet you will find multiple solutions to this task but many seem to have restrictive caveats or do not allow for exceptions to be defined in the function.

NOTE: As mentioned in the comments below SQL Server is NOT the ideal place to perform this conversion. Other methods were suggested as well - such as CLR, for example. This post has served it's purpose, in my opinion - it's great to have all of these thoughts in one place as opposed to the random tidbits available here and there. Thank you to all.

The challenge you'll run into with these approaches is that you've lost information. Explain it to the business users that they've taken a blurry, out of focus picture and despite what they see on t.v. there's no way to make it crisp and in focus. There's always going to be situations where these rules won't work and as long as everyone knows going into this that's the case, then have at it.

This is HR data so I'm going to assume we're talking about getting names in a consistent title case format because the mainframe stores it as AARON BERTRAND and we want the new system to not yell at them. Aaron is easy (but not cheap). You and Max have already identified the problem with the Mc/Mac so it correctly capitalizes Mc/Mac but there are instances where it's too aggressive with Mackey/Maclin/Mackenzie. Mackenzie is an interesting case though - look how the popularity of it has boomed as a baby name

At some point, there will be a poor child named Mackenzie MacKenzie because people are awful beings.

You're also going to run into lovely things like D'Antoni where we should cap both letters around the tick mark. Except for d'Autremont where you only capitalize the letter after the apostrophe. Heaven help you though, if you send mail to d'Illoni as their family name is D'illoni.

For the sake of contributing actual code, the following is a CLR method we used in a 2005 instance for our purposes. It generally used ToTitleCase except for the list of exceptions we built out which is when we basically gave up trying to codify the aforementioned exceptions.

namespace Common.Util
{
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading;

    /// <summary>
    /// A class that attempts to proper case a word, taking into
    /// consideration some outliers.
    /// </summary>
    public class ProperCase
    {
        /// <summary>
        /// Convert a string into its propercased equivalent.  General case
        /// it will capitalize the first letter of each word.  Handled special
        /// cases include names with apostrophes (O'Shea), and Scottish/Irish
        /// surnames MacInnes, McDonalds.  Will fail for Macbeth, Macaroni, etc
        /// </summary>
        /// <param name="inputText">The data to be recased into initial caps</param>
        /// <returns>The input text resampled as proper cased</returns>
        public static string Case(string inputText)
        {
            CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
            TextInfo textInfo = cultureInfo.TextInfo;
            string output = null;
            int staticHack = 0;

            Regex expression = null;
            string matchPattern = string.Empty;

            // Should think about maybe matching the first non blank character
            matchPattern = @"
                (?<Apostrophe>'.\B)| # Match things like O'Shea so apostrophe plus one.  Think about white space between ' and next letter.  TODO:  Correct it's from becoming It'S, can't -> CaN'T
                \bMac(?<Mac>.) | # MacInnes, MacGyver, etc.  Will fail for Macbeth
                \bMc(?<Mc>.) # McDonalds
                ";
            expression = new Regex(matchPattern, RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);

            // Handle our funky rules
            // Using named matches is probably overkill as the
            // same rule applies to all but for future growth, I'm
            // defining it as such.
            // Quirky behaviour---for 2005, the compiler will
            // make this into a static method which is verboten for
            // safe assemblies.
            MatchEvaluator upperCase = delegate(Match match)
            {
                // Based on advice from Chris Hedgate's blog
                // I need to reference a local variable to prevent
                // this from being turned into static
                staticHack = matchPattern.Length;

                if (!string.IsNullOrEmpty(match.Groups["Apostrophe"].Value))
                {
                    return match.Groups["Apostrophe"].Value.ToUpper();
                }

                if (!string.IsNullOrEmpty(match.Groups["Mac"].Value))
                {
                    return string.Format("Mac{0}", match.Groups["Mac"].Value.ToUpper());
                }

                if (!string.IsNullOrEmpty(match.Groups["Mc"].Value))
                {
                    return string.Format("Mc{0}", match.Groups["Mc"].Value.ToUpper());
                }

                return match.Value;
            };

            MatchEvaluator evaluator = new MatchEvaluator(upperCase);

            if (inputText != null)
            {
                // Generally, title casing converts the first character
                // of a word to uppercase and the rest of the characters
                // to lowercase. However, a word that is entirely uppercase,
                // such as an acronym, is not converted.
                // http://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase(VS.80).aspx
                string temporary = string.Empty;
                temporary = textInfo.ToTitleCase(inputText.ToString().ToLower());
                output = expression.Replace(temporary, evaluator);
            }
            else
            {
                output = string.Empty;
            }

            return output;
        }
    }
}

Now that all of that is clear, I'm going to finish this lovely book of poetry by e e cummings


Is it safe to sync MySQL binlogs? I am working on a strategy to replicate my MySQL binlogs to Amazon s3. I have looked into mysqlbinlog --remote-server but it seems like adding a file watcher on /var/log/mysql which triggers an aws s3sync command is a little easier.

I have tested this with mysqlslap and it seems to work, But I was wondering if anyone had insight as to whether this was a bad practice. Perhaps the files can get corrupted this way?

What do you mean by "sync"?  rsync?  copy?

Do you shutdown mysqld while doing the sync?

What is the value of sync_binlog?

The problem is that mysqld will be writing to the binlog at random times, and your sync has a chance of copying only half of a 'record'.

Your testing probably did not encounter the potential problem because the writes will often be 'atomic'.

Suggest you shutdown mysqld for the duration of the sync.  Even better would be to have an LVM snapshot; this would allow for a much shorter shutdown.

Plan B

Do FLUSH LOGS;.  That will 'rotate' the binlogs -- close the current binlog and start a new binlog with the next higher number.  Then copy, by any convenient means, the flushed binlog (and any previous ones as needed).  This should be totally safe.  It will capture the replication stream up to the point of the FLUSH LOGS.

The only minor tricky part is knowing how many binlogs to copy and what their names are.


Errors restoring a differential backup I'm getting the following error as I'm trying to restore from a differential backup:

  System.Data.SqlClient.SqlError: This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.

We are using SQL Server 2008 R2 with 2 small databases (A:Production, B:Testing), in full recovery mode.

My daily backups are:

Full nightly backup that runs at 6 PM.
Hourly differential backup during the day.
Log backups every 30 minutes during business hours.

I usually restore my testing database from the latest full backup using Management Studio, but today we discovered that some of the reports are not working correctly, so I'm trying to check what happened exactly yesterday during business hours so I tried the differential backup for the first time.

I got the following error:

  the log or differential backup cannot be restored because no files are ready to roll forward

Then I manually backed up the Testing database, restored it using this same manual full backup (since the one from last night's production is not working), with Replace and NoRecovery options, then I tried restoring the differential from yesterday morning, but I get the following error about paths:

  This backup set cannot be applied because it is on a recovery path that is inconsistent with the database...

Any advice on what am I doing wrong or how can I keep restoring from all the differentials until the end of the day?

Here's what I have understood from your question :

Your Full backup taken yesterday failed to restore on your TestingDB. Hence, you took a latest full backup and that got restored successfully. After this you are trying to restore a differential backup from yesterday.

This will not work. Here's the reason it will not work. Everytime a backup happens let it be FULL\DIFF or LOG. it generates an LSN, this LSN is a chain and unique number which matches with each other and are compared at the time of restore.

If you have taken latest FULL backup then now you should take the latest diff backup and try to restore it on TestingDB and you should not get any errors you received earlier. Also, while restoring you can check the LSN of Full and Diff backup files by using RESTOREHEADERONLY  command which reads the headers of your .bak file and gives you the information about First and Previous LSN.

I hope this helps in solve your problem.


Using pg_prewarm to load X number of latest rows into cache We have a large query that is extra slow when customers "run it the first time, early in the morning..."

So, I found pg_prewarm that I would like to use load into PG's buffer cache
a certain amount or most recently accessed rows (inserted, updated, or deleted)
from several tables that are used in the said query.

Furthermore, I need to make sure that 'warm up' does not exceed PG's cache (shared_buffers setting I believe, or am I wrong?)
To prewarm last 1000 pages of a single table I can do:

SELECT pg_prewarm(
    'mytable',
    -- "pre warm" last 1000 pages
    first_block := (
        SELECT pg_relation_size('mytable') / current_setting('block_size')::int4 - 1000
    )
);

Question #1: Does this approach makes sense?

The trick is that pg_prewarm can only load a certain number of pages,
so I need to calculate "how many live rows are in page for certain table"

-- show some settings
SELECT current_setting('block_size')::int4 AS page_size_bytes; -- 8192
SHOW shared_buffers; -- 512 MB

-- https://www.postgresql.org/docs/current/static/pgstattuple.html
--CREATE EXTENSION pgstattuple;
-- find out live row size and live rows per page
SELECT 'mytable'AS table_name, pg_size_pretty(tuple_len / tuple_count) AS live_row_size, 8192.00 / (tuple_len / tuple_count) AS live_rows_per_page, * FROM pgstattuple('public.mytable')

--"table_name","live_row_size","live_rows_per_page","table_len","tuple_count","tuple_len","tuple_percent","dead_tuple_count","dead_tuple_len","dead_tuple_percent","free_space","free_percent"
--"studies","1286 bytes",6.3701399688958009,652697600,462123,594431269,91.07,0,0,0,52329672,8.02

Question #2: Is my query above correct? Is this the proper way to get "live rows per page"?
Question #3: the live_row_size I get from above query is not the same as the result mentioned in this answer (by Erwin). Am I doing something wrong?

Based on live_rows_per_page I can then modify pg_prewarm to load enough last XXXX pages that contain 10,000 rows (6.37 x 10,000)

SELECT pg_prewarm(
    'mytable',
    -- "pre warm" pages of the last 10,000 rows for 'mytable'
    first_block := (
        SELECT pg_relation_size('mytable') / current_setting('block_size')::int4 - 63700
    )
);

Update #1
Regarding the Question #3, I get following when I run the query for mytable...
The output of pgstatuple is is different, probably because it is not listing the same items, but I am not sure...

"what","bytes/ct","bytes_pretty","bytes_per_row"
"core_relation_size",652697600,"622 MB",1412
"visibility_map",16384,"16 kB",0
"free_space_map",180224,"176 kB",0
"table_size_incl_toast",1101955072,"1051 MB",2384
"indexes_size",508289024,"485 MB",1099
"total_size_incl_toast_and_indexes",1610244096,"1536 MB",3484
"live_rows_in_text_representation",1138946462,"1086 MB",2464
"------------------------------",<NULL>,"<NULL>",<NULL>
"row_count",462123,"<NULL>",<NULL>
"live_tuples",0,"<NULL>",<NULL>
"dead_tuples",3,"<NULL>",<NULL>

Addressing Question #3

  the live_row_size I get from above query is not the same
  as the result mentioned in this answer (by Erwin). Am I doing
  something wrong?

You have:

SELECT pg_size_pretty(tuple_len / tuple_count) AS live_row_size
FROM pgstattuple('public.mytable');

The manual defines:

  tuple_len ... Total length of live tuples in bytes
  tuple_count ... Number of live tuples

You compute the medium length of live tuples. It seems like you expect Postgres to extract only live tuples when fetching data pages into RAM, but that's not how it works. Postgres just reads full pages into RAM, including any dead tuples that may be contained.

Hence, your next expression does not compute "live rows per page":

8192.00 / (tuple_len / tuple_count) AS live_rows_per_page

It computes "hypothetical maximum of live rows per data page with only live tuples", which is nice to know, but otherwise useless for your task.

Addressing Update #1

Why different sizes? In the referenced answer, I am leading with this:

  This is going to demonstrate that the various methods to measure the
  "size of a row" can lead to very different results. It all depends
  what you want to measure exactly.

Your findings seem to confirm that - though I am not sure what you compare exactly.

The referenced answer is a bit outdated. I improved some details and added numbers from pgstattuple for comparison. You need to install the module once per database (well you obviously have it, but maybe other readers):

CREATE EXTENSION pgstattuple;

Then:

SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr
      / CASE part WHEN 1 THEN NULLIF(x.ct, 0)
                  WHEN 2 THEN NULLIF(st.tuple_count, 0)
                  WHEN 3 THEN NULLIF(st.dead_tuple_count, 0)
                  WHEN 4 THEN NULLIF(st.tuple_count + st.dead_tuple_count, 0) END
            END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.big t                     -- provide table name *once*
   ) x
 , LATERAL pgstattuple(tbl) st             -- also get numbers from pgstattuple
 , LATERAL (
   VALUES
      (1, false, 'row_count'                           , ct)
    , (1, false, ' ------ DB_obj_size_func -------'    , NULL)
    , (1, true , 'core_relation_size'                  , pg_relation_size(tbl))
    , (1, true , 'visibility_map'                      , pg_relation_size(tbl, 'vm'))
    , (1, true , 'free_space_map'                      , pg_relation_size(tbl, 'fsm'))
    , (1, true , 'table_size_incl_toast'               , pg_table_size(tbl))
    , (1, true , 'indexes_size'                        , pg_indexes_size(tbl))
    , (1, true , 'total_size_incl_toast_and_indexes'   , pg_total_relation_size(tbl))
    , (1, true , 'live_rows_in_text_representation'    , txt_len)
    , (2, false, ' ------ pgstattuple ------------'    , NULL)
    , (2, false, 'live_tuples'                         , st.tuple_count)
    , (2, false, 'dead_tuples'                         , st.dead_tuple_count)
    , (2, true , 'total table / live tuples'           , st.table_len)
    , (2, true , 'live table / live tuples'            , st.tuple_len)
    , (3, true , 'dead table / dead tuples'            , st.dead_tuple_len)
    , (4, true , 'total table / all tuples'            , st.table_len)
   ) l(part, is_size, what, nr);

Simple solution?

If your query does not have side effects, the most efficient method should be to just execute it to fetch relevant data pages into RAM.