Is it possible to clean up duplicate entries with just SQL? I accidentally introduced a bug in my software that caused it to create duplicate entries. Now I have a lot of duplicate entries, the total rows of that table is around 111 million.

This is my table structure for that table.

CREATE TABLE `graph_player_ranked` (
  `player_id` int(11) NOT NULL,
  `season_id` int(11) NOT NULL,
  `playlist_id` int(11) NOT NULL,
  `timestamp` datetime NOT NULL,
  `matches_played` int(11) DEFAULT NULL,
  `rank_points` int(11) DEFAULT NULL,
  `tier` int(11) DEFAULT NULL,
  `division` int(11) DEFAULT NULL,
  `no_update_at` datetime NOT NULL,
  PRIMARY KEY (`player_id`,`season_id`,`playlist_id`,`timestamp`),
  KEY `player_id` (`player_id`,`playlist_id`,`timestamp`),
  KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please don't comment about the possibly bad structure, I am switching to a new structure but I have to fix this issue first. However, should I remove timestamp from the PK?

The idea was to only add a new row when "rank_points" OR "tier" OR "division" changed OR "matches_played" increased. At some time I accidentally made it create a new row when "matches_played" was the same as the previous row or larger.

Example of duplicate data:

I want to remove all duplicate entries with just one row. So for the above image that would be:

Remove all of the rows in a red rectangle
Grab "timestamp" from the first row in the rectangle.
Grab "no_update_at" timestamp from the last row in the rectangle.
Place one row back with all previous data and the above two timestamps.

I have no idea how to achieve this, the only solution I can think of is creating an external tool to do this. But that would probably take very long to process all rows.

Edit: Sample data for two players can be found here https://dl.dropboxusercontent.com/u/184110260/sample_data/graph_player_ranked.sql

Your data sample seems to suggest that the two datetime columns are the only columns that can change in each group of duplicates. In other words, duplicates are determined by columns player_id, season_id, playlist_id, matches_played, rank_points, tier, division.

One other assumption that your data sample prompts is that a row with a greater timestamp corresponds to a row with a greater no_update_at as well. (In fact, the two columns always have the same value in your example.)

Finally, for the purpose of this answer, I am also going to assume that when you are saying "first" or "last" (row in the rectangle), you are talking about the earliest or latest datetime value.

With those out of the way, you can generate the desired result set from your table if you just group the rows by the columns listed above and take MIN(timestamp) for timestamp and MAX(no_update_at) for no_update_at – that is, like this:

SELECT
  player_id,
  season_id,
  playlist_id,
  MIN(timestamp) AS timestamp,
  matches_played,
  rank_points,
  tier,
  division,
  MAX(no_update_at) AS no_update_at
FROM
  graph_player_ranked
GROUP BY
  player_id,
  season_id,
  playlist_id,
  matches_played,
  rank_points,
  tier,
  division
;

You could store that in a temporary table:

CREATE TEMPORARY TABLE tmp_graph_player_ranked
AS
SELECT
  ...
;

then truncate the source:

TRUNCATE graph_player_ranked;

and populate it from the temporary table:

INSERT INTO
  graph_player_ranked
    (
      player_id,
      season_id,
      playlist_id,
      timestamp,
      matches_played,
      rank_points,
      tier,
      division,
      no_update_at
    )
SELECT
  player_id,
  season_id,
  playlist_id,
  timestamp,
  matches_played,
  rank_points,
  tier,
  division,
  no_update_at
FROM
  tmp_graph_player_ranked
;

Of course, if the table is referenced by other tables, you cannot simply remove all data – you will probably need to disable or remove the foreign keys before re-populating the table and enable or recreate them afterwards. (That is an entirely different problem that would need to be addressed separately, in case you need help with it.)


How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup? On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes).

Now I add a binary packup of the PG_DATA directory (excluding the pg_xlog subdirectoy). To do that I perform a pg_start_backup(), the binary copy, and a pg_stop_backup().

I think I understand quite well what the pg_start_backup and pg_stop_backup are doing, the first one make a checkpoint and the last one ensure the last WAL file is archived.

From the official documentation we can see that for the binary data copy we should:

  Perform the backup, using any convenient file-system-backup tool such as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary nor desirable to stop normal operation of the database while you do this.

So I'm quite perplex. This mean a Checkpoint could be performed while we make the copy. I saw a lot of documentation stating that the copy command should allow data changes while performing the copy, I'm Ok with that, simplya matter of finding the right tool. But My question is how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent (some from before the checkpoint, some from after)?

By replaying the transaction logs Postgresql will be able to put all these files in the right state? I saw that create tables and drop operations are dangerous while the backup is performing, Isn't there some dangerous operations like vacuum commands? Does the pg_backup suspend the vacuum operations? Should I make a copy of the global/pg_control file in the end of in the start of the binary copy process? Should I use a snapshot-enabled filesystem (like with an xfs-freeze) to get a faster restore process?

I saw that a backup script crash will not launch a pg_stop_backup automatically, so there's a chance that my backup state live for a long time (until my nagios rings someone somewhere to fix the pg_stop_backup() ). So if anythingis different in PostgreSQL between these two comands I'd like to know it, to understand what impact it may have.

Enlighten me please.

You asked:

how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent.

pg_start_backup() ensure the data file is at least as new as the checkpoint. On recovery, the logs are applied.
If the data is old, the log will update it..
If the data is new, the log will have same content. There is no hurt writing it again.
The data are never newer then the log, because the logs are write ahead (WAL).

You asked:

... xfs-freeze ...

xfs-freeze is alike to pg_start_backup(), it don't take a snapshot. You need a volume manager to do that.

You asked:

... why do create tablespace & create database statements are unsupported if the WAL can replay everything?

It is supported, just some little gotcha.  See http://www.postgresql.org/docs/8.1/static/backup-online.html :

23.3.5. Caveats
CREATE TABLESPACE commands are WAL-logged with the literal absolute
path, and will therefore be replayed as tablespace creations with the
same absolute path. This might be undesirable if the log is being
replayed on a different machine. It can be dangerous even if the log
is being replayed on the same machine, but into a new data directory:
the replay will still overwrite the contents of the original
tablespace. To avoid potential gotchas of this sort, the best practice
is to take a new base backup after creating or dropping tablespaces.


How do I cancel an app lock request in SQL Server? The sp_getapplock stored procedure has the following return values:

  0: The lock was successfully granted synchronously.
  1: The lock was granted successfully after waiting for other incompatible locks to be released.
  -1: The lock request timed out.
  -2: The lock request was canceled.
  -3: The lock request was chosen as a deadlock victim.
  -999: Indicates a parameter validation or other call error.

I am writing a wrapper for calling sp_getapplock in our data access layer and I want to know under which circumstances -2 can be returned so that I can throw a descriptive and helpful exception. It is obvious what return values of -1 and -3 mean and I can easily create test conditions that cause those values to be returned. How would I manage to get a return value of -2?

Looking at the source of the sp_getapplock wrapper proc, all the return values except for -999 originate from the underlying sys.xp_userlock  internal stored procedure. I'd bet the internal proc returns a -2 when the request is cancelled by an attention event (client query timeout or explict client query cancel). However, no further sp_getapplock code executes after the batch is cancelled, including the RETURN statement. Consequently, the -2 return code may be returned internally but there's no practical way for the client to get the value.

Assuming this theory is correct, there isn't any value in translating to -2 to more descriptive message since it's the client that cancelled the request in the first place.

I'll leave it to Paul to confirm this by stepping through the SQL database engine code with a debugger :-)


Oracle set schema in a PreparedStatement Using an Oracle Database through a Java application, I need to execute a SET CURRENT SCHEMA statement using a PreparedStatement to overcome potential SQL injection.

So, I cannot do

public void executeSetCurrentSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();
     stmt.execute("SET CURRENT SCHEMA " + schema);

  } catch(Exception){}
}

but instead I'd like to use a prepared statement, that won't accept a "SET" command, instead I can do a "SELECT setCurrentSchema(" + schema + ")" if such a function would exists.

Is there any function I can issue using a SELECT to set the schema?

Always use the DBMS_ASSERT package when dealing with variable Database Objects that are used within Dynamic SQL statements.

Example

create or replace
procedure owner_of_proc.setCurrentSchema( schema_name in varchar2 )
as
  asserted_schema_name  varchar2(30 byte); -- set to 128 BYTE for 12c+ extended names
  sql_text              varchar2(200);
begin

  asserted_schema_name := DBMS_ASSERT.SCHEMA_NAME( schema_name );

  sql_text := 'ALTER SESSION SET CURRENT_SCHEMA = "' || asserted_schema_name || '"';

  -- always have the ability to log the generated SQL text for Debugging purposes
  -- logger.debug( 'SQL =' || chr(10) || sql_text );

  execute immediate sql_text;
end;
/

Your Java code would be:

public void executeSetCurrentSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();

     // I think this is how you use BIND variables in Java
     stmt.execute("begin owner_of_proc.setCurrentSchema( ? ) end;", schema);

  } catch(Exception e){
    /* Please fill this out
       It is the Java equivalent of WHEN OTHERS THEN NULL;

       You need to catch (and handle) "ORA-44001: Invalid schema name"
     */
  }
}


Searching a text with space in mySql match against Query I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result.

My MySql Query is:

SELECT count(id) as total
    , MIN(product_price) as min_price
    , MAX(product_price) as max_price
FROM `products_details`
WHERE subcat_id = 1026
    AND MATCH(alternate_name, product_desc, keywords)
    AGAINST ('+moto +g +plus +(>mobile <mobiles)'; IN BOOLEAN MODE)

I have problem with:

  AGAINST ('+moto +g +plus +(>mobile <mobiles)'

I want to search for string moto g plus mobile,using the above query. I want to add space before and after the g. Example, moto "SPACE"g"SPACE".

I tried with the 2 following solns, its not working.

[[:space:]]

  AGAINST ('+moto +[[:space:]]g[[:space:]] +plus +(>mobile <mobiles)'

and

  AGAINST ('+moto +\\ g\\  +plus +(>mobile <mobiles)'  ==> I have a space character (Ascii 32) after the \ in this query

Both of them are not working.

Can you please let me know what should be there?

Edit: The value of ft_min_word_len is set 1.

FULLTEXT needs space to be space, not a "letter".  That is, you cannot have spaces in a "word".

I would write application code to ignore 'short' words (such as 'g') and end up with

WHERE MATCH(alternate_name, product_desc, keywords)
        AGAINST ('+moto +plus'; IN BOOLEAN MODE)
  AND ( alternate_name LIKE '%moto g plus%'
     OR product_desc   LIKE '%moto g plus%'
     OR keywords       LIKE '%moto g plus%' )

That would be efficient because the MATCH happens first, thereby limiting the number of rows significantly.  Then the costly OR and LIKE does not have to touch many rows.

(I left off the 'mobiles' for this discussion.)


Applying normalization to a relational Database (Access & SQL Server) This question is pretty specific but I hope it can get answered on here. There is a wide range of information about normalization but I feel what I have read so far isn't as effective as just stating my situation.

I'm having trouble discerning what NF my tables are and applying normalization principles to my model. Also whether some tables should be left in 2NF and not 3NF, if it even exists and for what reason. I'm having difficulty transitioning from creating a regular database and normalizing the whole thing.

I believe the tables I have attached are all that is needed.

I will be building this in both Access and SQL Server, with that said I will be creating a form for making bookings and billings from Access that should be able to be input into the database.

Side question: I have been asked to create a report for a profit and loss statement, while this can be done in Excel is it possible to make a table in the database?

Thank you and please feel free to ask any questions relating to the topic

I have been asked to setup and ODBC driver to link with Access.

EDIT: I have decided that the BIKE table be renamed to BIKE_HIRE and have a BIKE table with the actual details of the bike itself. Also to create the Income/Expense table, have it linked to the PAYROLL table. If anything else needs to be normalized please suggest it.

EDIT 2: Specifications: This may give further insight into the database, but I'll be working on it with the help, thanks !

The business is a small 5 room guest house home stay.
They have motorbike and car rentals and propose that only motorbike rentals be recorded in the database.
There is little staff which duties along with the manager are housekeeper, guest liaison, housekeeping, accounts.
Drivers are paid on the day and don't need to be included on the bill or database. (Details may be necessary)
Guests are given a form to fill for their details when they enter.
Proposed that payments have different rates depending on days.
Client wants to be able to record expenses and income

Based on this, is it possible that maybe some entities are better left as 2NF?

Edit3: The solution for my case was simpler than possible. I left Payment unnormalised, and attached an Expense table onto Payroll, this was all I needed to do as this was a sample .beginner project for me

Sorry, this may not be An Answer, but I couldn't add a comment.

This is surely a 'solved problem' in that hotel and reservation systems are available commercially 'off the shelf'? Why develop your own? Even if you do decide for good reason that you need to, it might well be worth investigating the data models underlying a few commercial products, if you can find a way of doing that.

However, if you ARE developing your own, I think you need many-to-many relationships between BIKE and GUEST (as I think you have recognised in your additional note), and between RENTAL DRIVER and either BOOKING or GUEST. These would be modelled on the same basis as you have already done between BOOKING and ROOM, with an intermediate table - which I would suggest naming ROOM BOOKING rather than BOOKABLE for ROOMs, BIKE_HIRE (as you have suggested adding) or BIKE_BOOKING for BIKEs, and CAR_BOOKING for car and driver).

A room that is NOT available for booking could be 'booked' to something like deep clean, re-decoration, renovation, re-plumbing or whatever reason makes it unavailable for guests to book it - I assume your Status field would cover things like that.

One BOOKING can include multiple GUESTS (as you have noted). And surely one GUEST can have multiple BOOKINGs over time? And might rent a car and driver or a bike more than once in one visit of several days or week(s)? Indeed, one BOOKING for several GUESTs might need you to add the concept of (and a table for) GROUPs, about which you might or might not know individual names, or just the number of people in the GROUP - for example, if you were to cater for a wedding party.

I would seriously consider generalising the concept of BOOKING, so it can cover ANY type of BOOKING - use a field Booking Type with a lookup from a BOOKING TYPEs table. Most of the fields needed for a booking will be similar, and the others can either be included but not used on some types of booking, or you could use the BOOKING TYPE table to label fields in BOOKINGs appropriately for that type of BOOKING.

If you have business guests, they might need to book meeting rooms, projectors, flipchart stands, etc, and these can just be added as different BOOKING TYPEs without needing any more tables to be defined.

You only need to store two of the three elements of the duration of a BOOKING - StartDateTime, EndDateTime, and Duration, and calculate the third.

Some bookings (like Room bookings) might have a Duration measured in whole nights (unless of course you are running another type of hotel?), others might be in hours or half days (Bike bookings for example) depending on your booking policy.

Can EMPLOYEEs sometimes be GUESTs? Or Rental Drivers be Employees? If so, you might consider introducing a PERSON table, with a PERSON_TYPE of Guest or Employee or Rental Driver for example. Or the Employee type might be extended to the role the Employee plays - from Owner, or Chief Exec, through Manager to Porter, Cleaner and Maid (to give a few possible examples).

It might also be worthwhile to separate out ADDRESS (postal address and probably land line phone) and CONTACT DETAILs (other phone, cell/mobile, email address(es), etc) from EMPLOYEE or GUEST entities. That way, one address may cover related Employees living in the same place, or a Guest GROUP or Family, whereas CONTACT DETAILs may apply to individual EMPLOYEEs or GUESTs. Again, you could have one field for contact detail type (lookup), and another which can hold (in multiple rows) phone or cell/mobile number, fax number, email address, Skype contact, IM address or any other type of contact detail that might in future be useful.

Will you be catering for meals? In which case, you might want to include Dining Room or Meeting Room (if you have more than one of either) and TableNumber as Bookable resources.

And I think you might need a lot more fields and tables to cover PAYROLL. Days or shifts or hours worked? Hourly or weekly or monthly payment type, and corresponding rates? Tax band? Tax due, Tax paid? Or maybe payroll is handled quite separately?

Anyway, there are a few thoughts to consider. I've developed several types of database in my career, but am not a professional database developer nor administrator, nor do I have any specific knowledge of hotel reservation systems. But I HAVE helped to implement Theatre booking systems, and some of the concepts I suggest come from that experience. I have always found it worthwhile to try to generalise beyond one table for one specific type of entity, if similar ones can be added merely by extension with an extra field or two, and a SOMETHING_TYPE of lookup table to tell you what kind of SOMETHING you are dealing with.

Best wishes for your development plans.

A later realisation. I think you already have much of what I suggested. BUT I am rather thrown by your use of Arrival Date and Departure Date in your BOOKINGS table. That makes it look conceptually more like a Guest Stay than the kind of Booking I was suggesting. Maybe you need a new concept for stay, and a table to match?

And I think you also need a concept of Bill and Line Item on the Bill as places to keep financial data, and the basis for charging and payment processing.

Going back to another commentator's mention of Functional Definition, consider how you might handle cases such as one person making a reservation for two rooms for three guests, two sharing, with different arrival and departure  dates for each guest. And one of them wants a bike on days 1 and 3 of their stay, a car and driver for day 2 for all three of them, and three bikes on day 4.

And by the way, they are splitting the Bill!


How to restrict access to current user record and its rights in postgres Postgres 9 database contains additional information about postgres users:

create table public.user
(
  user char(10) primary key,  -- CURRENT_USER in postgres
  name char(30)
);

and user roles;

create table public.userrole (
  user char(10) references user on delete cascade on update cascade,
  roleid char(30) not null );

User restricteduser should have limited access to its own records only.

I tried

revoke all on  user,userrole from restricteduser cascade;
grant select on user, userrole to restricteduser;
grant update (name) on user to restricteduser;

But in this case restricteduser can see other users record only and update other users names.

How to restrict access to restricteduser only to its own records in user and userrole tables and update access only to own name column in user table ?

Update

I need 2 things:

Retrieve and update current user name and some other fields from users table in
roced whose id is currnt_user
Retrieve list of roles for current_user  from roles table.

Users can use ODBC for data access so application level security is not possible.

Is it possible and reasonable to create plpgsql methods for those operations ?

It looks like what you want is row-level security. PostgreSQL will support this in version 9.5, but that's due to come out in over a year.

For now, you'll need to use views. If you don't mind the fact that a clever user can trick the system into revealing information they're not meant to be able to see using malicious functions in the WHERE clause, you can just:

CREATE VIEW my_user AS
SELECT *
FROM "user"
WHERE "user"."user" = current_user;

Then GRANT the SELECT and UPDATE rights on it to the restricted user, and REVOKE ALL ON "user" FROM restricted_user.

They'll be able to see and update their record through the view, but nobody else's.

If you want to avoid the need for a different table name, you can use schemas and the search_path, e.g.

CREATE SCHEMA restricted_user;

CREATE VIEW restricted_user."user" AS
SELECT *
FROM public."user" u
WHERE u."user" = current_user;

GRANT USAGE ON SCHEMA restricted_user TO restricted_user;

PostgreSQL will automatically put a schema with the same name as the username first in the search_path, so this will cause connections as the PostgreSQL user restricted_user to see the view instead of the underlying table.

Note that this is a bit leaky, but it's safe unless you let restricted_user run arbitrary SQL against the database. Alternately, you can wait to update to 9.4, which has updatable security_barrier views, preventing the leak.

Personally, I think it's probably going to be easiest to do this at the application level until PostgreSQL 9.5's row security is available. Not least because Rails doesn't tend to play well with database features and I have no idea how its various caching systems, etc, will cope with rows that are sometimes visible and sometimes not.


copy/sync only data between 2 database with non identical table structure I have the following issue and was wondering if someone knows if this is possible.

We have a MS SQL Server 2012 Instance with 2 databases database A and database B.

On Database A the data from 6 tables ( not the tables itself) has to be copied or synced to tables with the same name on Database B . I cannot use the sync within sql server because the layout of the tables is slightly different.

I got it to work in 3 steps now :

remove constraints from tables in database B and delete entire content from tables
Copy data with SSIS package from A to B
Re applying constrains from tables in database B

This is not a desirable situation users that have a connection to database B lose connection ( duh!! )

My question : Is it possible to copy or sync only the ( modified ) table data from A to B without users having to leave the database for small period of time.
I hope my explanation is a bit clear…

Alright so this is an intro on how to accomplish this using SSIS. This is just a base model to get you going you're going to need to tune this based on your specific database to improve performance. This tutorial also assumes you know how to set connection managers. Keep that in mind.

First off you're going to want to create and then open a dataflow task. Then you want to add two OLE DB Sources, one Merge Join operation, one conditional split, one (possibly two) OleDB destinations, and one (possibly zero) OLEDB commands.

Once you've done this we will need to build the query for this table my example is just a calendar table.

SELECT
    [BaseDate],
    [YearNumber],
    [MonthNumber],
    [DayNumber],
    [DayofWeek],
    [DayofYear],
    [WeekNumber],
    [Holiday],
    [WorkDay],
    [JulianDate],
    [BusinessDay],
    [Quarter]
FROM [Calendar]
ORDER BY [BaseDate] -- Very important to order by your primary key columns

Open your OLEDB Source.

NOTE:By use Identical column names I mean in each source.

Once you've done this right click on the source and go to the advanced editor.

Rinse and repeat for your second OLEDB Source. Once this is connect the source table to the Merge. This will prompt you for either Left or Right Input. The source table should always be the left side.

Once both are connected Open the Merge Join operator.

Open the conditional split and add this.

Connect the Merge Join Operator to the Conditional Split

Then connect that to your OLE DB Destination map the columns and boom you have your insert. Now for the tricky part. You will have to write an expression for comparing all of those columns. However if you're lazy like me you will use this stored procedure I made to do it for you. Simply create this stored procedure on the database you are pulling from and then execute with that table name in the parameter.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[MakeSSISColumnCompare]
        (@TableName VARCHAR(50))
AS

DECLARE
        @SqlCmd VARCHAR(max)
        ,@ColumnName VARCHAR(50)
        ,@DataType VARCHAR(50)
        ,@Nullable VARCHAR(3)
        ,@Count INT

DECLARE ColCsr CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName

OPEN ColCsr
SET @Count = 0

FETCH NEXT FROM ColCsr INTO @ColumnName, @DataType, @Nullable
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @ColumnName NOT IN ('CreatedDateTime','UpdateDateTime')
    BEGIN
        IF @Count = 0
                SET @SqlCmd =
                CASE
                        WHEN @Nullable = 'NO' THEN '[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        WHEN @DataType LIKE '%CHAR%' THEN '(ISNULL([' + @ColumnName + '])?"XXXX":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?"XXXX":[' + @ColumnName + ' (1)])'
                        WHEN @DataType LIKE '%INT%' THEN '(ISNULL([' + @ColumnName + '])?-99:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?-99:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'DATETIME' THEN '(ISNULL([' + @ColumnName + '])?"(DT_DBDATE)1/1/1900":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + ' (1)])'
                        WHEN @DataType IN ('MONEY', 'NUMERIC', 'DECIMAL','FLOAT') THEN '(ISNULL([' + @ColumnName + '])?0:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?0:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'BIT' THEN '[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        ELSE 'TRUE' END
        ELSE
                SET @SqlCmd = @SqlCmd +
                CASE
                        WHEN @Nullable = 'NO' THEN '||[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        WHEN @DataType LIKE '%CHAR%' THEN '||(ISNULL([' + @ColumnName + '])?"XXXX":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?"XXXX":[' + @ColumnName + ' (1)])'
                        WHEN @DataType LIKE '%INT%' THEN '||(ISNULL([' + @ColumnName + '])?-99:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?-99:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'DATETIME' THEN '||(ISNULL([' + @ColumnName + '])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + ' (1)])'
                        WHEN @DataType IN ('MONEY', 'NUMERIC', 'DECIMAL','FLOAT') THEN '||(ISNULL([' + @ColumnName + '])?0:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?0:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'BIT' THEN '||[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        ELSE '||TRUE' END
        SET @Count = @Count + 1
    END
    FETCH NEXT FROM ColCsr INTO @ColumnName, @DataType, @Nullable
END
SELECT @SqlCmd AS SqlSelect

CLOSE ColCsr
DEALLOCATE ColCsr

GO

Then execute like so.

EXEC    [dbo].[MakeSSISColumnCompare]
        @TableName = N'YourTableName'

This should give you the String you need to compare. You will want to delete the piece of this string that compares the key values. In my case turning this.

[BaseDate]!=[BaseDate (1)]||[YearNumber]!=[YearNumber (1)]||[MonthNumber]!=[MonthNumber (1)]||[DayNumber]!=[DayNumber (1)]||[DayofWeek]!=[DayofWeek (1)]||[DayofYear]!=[DayofYear (1)]||[WeekNumber]!=[WeekNumber (1)]||[Holiday]!=[Holiday (1)]||[WorkDay]!=[WorkDay (1)]||[JulianDate]!=[JulianDate (1)]||[BusinessDay]!=[BusinessDay (1)]||[Quarter]!=[Quarter (1)]

Into this.

[YearNumber]!=[YearNumber (1)]||[MonthNumber]!=[MonthNumber (1)]||[DayNumber]!=[DayNumber (1)]||[DayofWeek]!=[DayofWeek (1)]||[DayofYear]!=[DayofYear (1)]||[WeekNumber]!=[WeekNumber (1)]||[Holiday]!=[Holiday (1)]||[WorkDay]!=[WorkDay (1)]||[JulianDate]!=[JulianDate (1)]||[BusinessDay]!=[BusinessDay (1)]||[Quarter]!=[Quarter (1)]

This is why keeping the column names the same was important earlier. This allows that stored procedure to build the comparison string for us. Now simply copy and paste that into the second place of the conditional split.

Now either that to an OLEDB command to update the necessary table or send to to a staging table and then perform and traditional SQL update from there. This will depend on the size of the dataset being updated as to which performs better.

I would recommend including a timestamp system of some sort as well. This will allow you to limit the amount of rows you grab in your initial select.

Sorry this is isn't using 2012 data tools but I don't have access to that quite yet.


Partitioning a 4TB table while staying online (SQL Server Standard) We have a table that is currently ~4TB in size. We wish to introduce partitioning on this table. The table is already clustered on the key we wish to partition on.

The options appear to be

Use ALTER TABLE SWITCH to switch the data of the table to a range-partitioned table that has only one partition (zero cost). Then call ALTER PARTITION FUNCTION for each partition we wish to introduce (definitely not zero cost)
re-create the clustered index using DROP_EXISTING=ON

We're not using SQL Enterprise, so there's no ability to do this online.

Running these queries locks the entire table.

Even if we accepted the lock, we have no real way of estimating how long this would take (and whether we could complete it in time over a weekend)
Most of the data is archive data, there's only a small slice of data that's actually 'live' and would be a problem when it's locked. Trying to figure out if there's a strategy using ALTER PARTITION and ALTER TABLE SWITCH that allows us to do most of this with the data swapped out on a staging table.
Can anyone suggest a way we can do this progressively?

Thanks!

the table is append-only (new rows, no updates to existing ones)

Use ALTER TABLE SWITCH to switch the data of the table to a range-partitioned table that has only one partition for all the existing data, but for which new inserts will land in new partitions.  Eg all row up to today land in one big partition, but new rows will fall into monthly partitions in the future.

Then perhaps later you have some time to split the big partition.  If you use a RANGE RIGHT partition scheme, then you can split small partitions off of the RIGHT side of the large partition.  If you use RANGE LEFT you can split small partitions off of the LEFT side.

Or, after you have switched to the new table you can INSERT the data from the old single partition into a new properly-partitioned staging table.  Once that insert is done you can truncate the large partition, split the now-empty partition to match the staging table, and then switch the staging table in.  Since the data in the old partition is read-only, you can perform the insert over time.  Then the truncate, split, and switch-in are all metadata operations.


Inventory Database Design Issue I own a jewelry retail store alongside an online store, and am in the midst of building a completely custom system to track all of my inventory. I also have a number of stockists (other stores) that sell my items on consignment, so I want to keep track of stock levels there too.

I primarily sell rings, and have thousands of them in storage. I keep one of each size/style in stock at my physical store. They come in a variety of sizes and styles. I want to avoid the EAV approach completely and am happy to have sparse fields, there aren't many attributes.

The main product table, I included a number of attributes in here because they don't vary for each product. Size varies, so I have addressed that using other tables.

  Product Table
  - id
  - name
  - categoryID
  - description
  - finish (polished, brushed, satin etc)
  - width
  - thickness

Each ring comes in 6 sizes, so rather than creating 6 duplicate entries in the product table I created the productVariation table which references the productID. At the moment this is only used for storing the different sizes. Each different sized ring will have a unique SKU and barcode, so I have put that in here rather than the main product table.

  ProductVariation Table
  - id
  - productID
  - sizeID
  - sku

Then I have to keep quantity levels for several locations, so I made the productQuantity table to hold that information.

  ProductQuantity Table
  - id
  - productVariationID
  - locationID
  - quantity

Am I on the right track? One thing I haven't figured out yet is how to add a product that has no variations, for example a watch that only comes in one style and has no size options. It would have to be added to the productVariation table with a blank sizeID since that's where the SKU is stored. Any ideas to improve this design?

I agree with TomTom on the merits of the Datamodel resource book - it will give you more insight than StackOverflow, and if memory serves, there's a fairly detailed discussion of exactly this scenario.

The design you've come up with is similar to what I've seen for other systems - what you call ProductVariation is often called "variant".

I'm a little confused about the role of SKU in your design - the acronym stands for "Stock Keeping Unit". As it's supposed to be unique and never change, I'd make that the primary key of your ProductVariation table, rather than ID.

If you're only ever going to sell your current product range, or are happy to refactor in future, you can leave it as is; alternatively, you could consider a design with "table per subclass". This would give you a schema along the lines of:

Product (id, name, categoryID, description)

SizeVariant (SKU, productID, finish, width, thickness, Size, location, quantity)
ColourVariant (SKU, productID, Colour, finish, location, quantity)
NoVariant (SKU, productID, location, quantity)

Note how I've also put some of the attributes you've currently assigned to "Product" into the child tables.

This keeps the schema more "self describing", but adds a lot of work - only do that if you think the product catalogue will grow in the future, and you want to avoid EAV.


How to calculate total memory used by one table? I have innodb_buffer_pool_size=100GB;

show table status where name='test_tb'\G

gives

    Data_length: 4686086144
Max_data_length: 0
   Index_length: 5107564544

seems index uses 5GB memory.

ANALYZE table test_tb;
SELECT
sum(stat_value) pages,
  index_name,
  sum(stat_value) * @@innodb_page_size /1024/1024 "size(MB)"
  FROM
  mysql.innodb_index_stats
  WHERE
  table_name = 'test_tb'
  AND database_name = 'test_dbs'
  AND stat_description = 'Number of pages in the index'
  GROUP BY
  index_name;

+--------+-------------------+---------------+
| pages  | index_name        | size(MB)      |
+--------+-------------------+---------------+
| 286016 | PRIMARY           | 4469.00000000 |
| 150014 | test_key1         | 2343.96875000 |
| 161727 | test_key2         | 2526.98437500 |
+--------+-------------------+---------------+

its 8GB.

So which one is accurate?

Both are correct; both say the same values.  (Don't divide by 1024*1024; it will be a little more obvious.)

In InnoDB, the PRIMARY KEY is "clustered" with the data.  That is, they are both in the same BTree structure.  The PK provides the order; the data provides the content.

In your table, the data is ~4GB, including the PK and BTree overhead; the two indexes are in two separate BTrees, adding up to about 5GB.


When is it appropriate to use a scalar valued function? I've just read several articles that were critical of scalar functions because of performance issues. These articles usually point to using table functions instead. Under what circumstances would it be appropriate to use a scalar function?

Here is a simple example.  In SQL Server, we have 5 parts of a name (prefix, first, middle, last, suffix) that come from a person table.  In addition to having a column for each name part, we also would like to have a column that concatenates them.  In order to be consistent and to save on some typing, we are considering creating a function to create a "full name" string and putting the results in a separate column.

There are good reasons to be critical of the performance of scalar UDFs. Performance problems include forcing the entire plan to be run serially, excessive memory grants, cardinality estimation problems, and lack of inlining. However, that doesn't mean that they don't have good use cases.

Consider a series of stored procedures which needs to call the same complicated business logic that returns a scalar value. A scalar UDF used to assign the requested value to a local variable can work very well. The performance issues of scalar UDFs are really only felt when they are used inside of a query. Using a Scalar UDF inside a stored procedure will not force the entire stored procedure to run serially.

Some applications might require you to send the exact same SQL code to multiple RDBMS platforms. Scalar UDFs can be a good workaround to get functionality which is implemented in different ways on different platforms. For example, truncating a datetime in both SQL Server and Oracle is fairly difficult to do with the same code unless you use a UDF. In this case you will pay the performance penalties of UDFs, but sometimes they are your only option. For a scenario like this you may not be able to use TVFs because that syntax may not be supported by the other platform.

Other than that it's difficult to think of a good use for scalar UDFs. I suppose you might have a query for which response time is not critical. It might be easier to code the query with a scalar UDF, but you're likely to get better (or at the very least no worse) performance with a TVF instead.


Oracle enterprise manager showing wrong port after change I changed the port that Oracle was using from 1523 to the default 1521. The database is running on the correct port now. I can connect on 1521 and run queries.

However the Enterprise Manager is still showing that the port is 1523, and that the database is down.

Why doesn't the OEM know that I'm using the new port? How do I fix this?

Edit: I think I changed the port the wrong way. I changed the listener.ora file, the tnsnames.ora file and ran alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))";

Sometimes it helps to de-install the enterprise manager and then re-install it using the emca command. We'll leave that for the worst case.

Using EMCA After Changing the Database Listener Port

  If you change the listener port of the database after you have configured Database Control, the database status will appear as down. To reconfigure Database Control so it uses the new listener port, run the EMCA command using the -config dbcontrol db [-cluster] command-line arguments.

(emphasis mine)

Reference: Enterprise Manager Advanced Configuration (Oracle Docs 10g)

In your case you would run the following commands:

SET ORACLE_HOME= your_oracle_home
SET ORACLE_SID= your_oracle_sid

emca -config dbcontrol db

You might have to provide various pieces of information when re-configuring the Enterprise Manager:

port
SID
DBSNMP_PWD
SYSMAN_PWD
HOST_USER
HOST_USER_PWD
SYS_PWD

If this doesn't reconfigure the database control for your instance you might have to re-create the repository containing all the Entrprise Management Console configuration settings. You would achieve this be adding a parameter to the previous command:

SET ORACLE_HOME= your_oracle_home
SET ORACLE_SID= your_oracle_sid

emca -deconfig dbcontrol db
emca -config dbcontrol db -repos recreate

This will de-configure the EM Console settings for a database (not for a cluster installation) and then recreate the repository.

The additional information I listed above will be required for a re-configuration to work.

  DISCLAIMER
  Re-configuring the Enterprise Management Console is no easy task. I have failed many times (especially with broken timezone settings). If you are unsure or don't feel in the position to risk your installation, please consider contacting an Oracle Consultant.

  If you have a test environment, consider trying this out there first.


Fix double-encoded data I have a field defined as Question VARCHAR(1024) CHARACTER SET utf8 NOT NULL. Our application stored double-encoded data into this field. For example, é shows up as Ã© in phpMyAdmin, and C383C2A9 when passed to HEX.

I've added a second field (DoubleEncoded TINYINT NOT NULL DEFAULT 1), which identifies which records suffer from this problem.

I want to fix the remaining fields. Is there a way to do this using MySQL SQL? In other words, I need something to replace decode_utf8 in the following:

UPDATE `MyTable`
   SET `Question` = decode_utf8(`Question`),
       `DoubleEncoded` = 0
 WHERE `DoubleEncoded` = 1

CONVERT(CONVERT(CONVERT(`Question` USING latin1) USING binary) USING utf8)


Microsoft SQL Server Management Studio - default table view, default table parameters A friend has recently started teaching me how to design relational databases. Long story short, as I am not SQL-capable yet, we're using SSMS and lots of right-clicking.

And I am frustrated with the stuff - 95% of the new tables I make have ID as first column (non-nullable INT primary key) and I have to change datatype and mark it as primary each time. So it can be even more frustrating, setting table view to Custom requires roughly the same amount of wasted clicks each time as well (though it is "set as default").

What am I doing wrong? Said friend does his part mostly in the code editor (or whatever is the name of the actual feature) and doesn't know the GUI that well.

Is there any way to automate these first steps for new tables? Get them done with only "create table"-click and filling in the name field preferably?

Sorry for my bad English (but I searched through other questions first ;)).

I'm not sure you can automate this in SSMS. Why not take the opportunity to learn a little SQL? The script below will create a table with ID as the primary key (int) - you can then just add the extra columns you need (refresh your table list first):

CREATE TABLE [dbo].[your_table_name](
[ID] [int] NOT NULL,
CONSTRAINT [PK_your_table_name] PRIMARY KEY CLUSTERED
(
   [ID] ASC
))


Timestamp with time zone Please can you explain the meaning of this sentence!

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instance in UTC, regardless of the TIME ZONE offsets stored in the data. For example

  TIMESTAMP '1999-04-15 8:00:00 -8:00'

is same as the

  TIMESTAMP '1999-04-15 11:00:00 -5:00'

Many thanks!

Firstly, read the Wikipedia article that explains UTC/GMT.

Time stamps with time zones are expressed as a date, a local time (eg: 08:00:00, which is 8am), and an offset (eg: -8, which would be 8 hours behind UTC). To convert a local time into UTC you use the offset.

So, in your example of:

TIMESTAMP '1999-04-15 8:00:00 -8:00'

... the local time is 08:00:00, which is 16:00:00 UTC (08:00:00 + 8 hours, because it is 8 hours behind UTC).

TIMESTAMP '1999-04-15 11:00:00 -5:00'

... the local time is 11:00:00, which is 16:00:00 UTC (11:00:00 + 5 hours, because it is 5 hours behind UTC).

The two times and dates in your example are identical when converted to UTC - that is what the sentence in your question means.


Capture row with highest value from each percentile So far I've tried doing this:
SELECT MAX(buckets.position_id), NTILE
FROM  (
   SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id)
   FROM position
   WHERE activity_id = 8
   ) AS buckets
GROUP BY  NTILE ORDER BY 2;

Which outputs something like this:
 max | ntile
-----+-------
   3 |     1
   6 |     2
   9 |     3
  12 |     4
  15 |     5
  18 |     6
  21 |     7
  24 |     8
  27 |     9
  30 |    10
  33 |    11
  36 |    12

But this just shows the id along with the percentile of that id in the row, I need to show the complete row every time, so I tried doing this:
SELECT MAX(buckets.position_id), NTILE
FROM  (
   SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id)
   FROM position
   WHERE activity_id = 8
   ) AS buckets
FULL JOIN position ON position.position_id = buckets.position_id
GROUP BY  NTILE ORDER BY 2;

Which shows the same output.
SELECT MAX(buckets.position_id), buckets, buckets.ntile
FROM  (
   SELECT position.position_id, position.lat, position.long, position.activity_id, position.position_created_at , NTILE(100) OVER (ORDER BY position.position_id)
   FROM position
   WHERE activity_id = 8
   ) AS buckets
GROUP BY buckets, buckets.ntile
ORDER BY 3;

This outputs something like this, which is what I want, but it's repeating the NTILE instead of just getting the last one.
 max |                                       buckets                                       | ntile
-----+-------------------------------------------------------------------------------------+-------
   1 | (1,20,90,12,12,8,"2021-03-12 21:07:47.8646",1)                                      |     1
   2 | (2,20,90,2,12,8,"2021-03-12 21:08:57.59163",1)                                      |     1
   3 | (3,20,90,2,12,8,"2021-03-12 21:09:21.355993",1)                                     |     1
   4 | (4,20,90,2,12,8,"2021-03-12 21:09:36.752904",2)                                     |     2
   5 | (5,20,90,2,12,8,"2021-03-12 21:09:45.815941",2)                                     |     2
   6 | (6,20,90,2.001,12.001,8,"2021-03-15 12:11:36.320269",2)                             |     2
   7 | (7,20,90,2.001,12.001,8,"2021-03-15 12:11:38.876406",3)                             |     3
   8 | (8,20,90,2.002,12.001999999999999,8,"2021-03-15 12:11:41.911301",3)                 |     3
   9 | (9,20,90,2.002,12.001999999999999,8,"2021-03-15 12:11:44.938797",3)                 |     3
  10 | (10,20,90,2.0029999999999997,12.002999999999998,8,"2021-03-15 12:11:47.975407",4)   |     4
  11 | (11,20,90,2.0029999999999997,12.002999999999998,8,"2021-03-15 12:11:51.003433",4)   |     4
  12 | (12,20,90,2.0039999999999996,12.003999999999998,8,"2021-03-15 12:11:54.035314",4)   |     4
  13 | (13,20,90,2.0039999999999996,12.003999999999998,8,"2021-03-15 12:11:57.067232",5)   |     5
  14 | (14,20,90,2.0049999999999994,12.004999999999997,8,"2021-03-15 12:12:00.099153",5)   |     5
  15 | (15,20,90,2.0049999999999994,12.004999999999997,8,"2021-03-15 12:12:03.131785",5)   |     5
  16 | (16,20,90,2.0059999999999993,12.005999999999997,8,"2021-03-15 12:12:06.171215",6)   |     6
  17 | (17,20,90,2.0059999999999993,12.005999999999997,8,"2021-03-15 12:12:09.200098",6)   |     6

But since those values change every row, the Max function messes up and it just shows every row instead of the last percentile...
I also tried wrapping everything inside MAX() functions, but I'm afraid that when I get a lower value from either lat or long it's going to mess up. (Also I don't think that's very efficient, correct me if I'm wrong.)
DB-fiddle for testing
https://www.db-fiddle.com/f/gKoqqmRa47g5546EMz5Hs/0

If I understand correctly your objective is this:
After ordering by position_id, get the row with the highest position_id for each of 100 percentiles.
DISTINCT ON does it:
SELECT DISTINCT ON (ntile)
       NTILE(100) OVER (ORDER BY position_id) AS ntile, *
FROM   position
WHERE  activity_id = 1
ORDER  BY ntile, position_id DESC;

db<>fiddle here
Since the PK is on (position_id, activity_id), position_id is unique for each activity_id and we need no tiebreaker.
Can be a single SELECT without subquery because DISTINCT ON is applied after window functions.
See:

Select first row in each GROUP BY group?
Best way to get result count before LIMIT was applied


Identify in details the consumption of memory from a specific session We are using Oracle 11g standard, without any options (diag/tunning/perf).

I can identify one session with the SQL bellow which having a high consumption (1.4 GB) of memory on the past hours...  (pga and uga)

There is someway to get details about this consumption?
Whether it is the use of temporary tables or something else?

with x as (select s.osuser osuser , s.username
     , s.status
     , se.sid
     , s.serial# serial
     , n.name
     , round(max(se.value)/1024/1024, 2) maxmem_mb
     , max(se.value) as maxmem
  from v$sesstat se , v$statname n , v$session s
 where n.statistic# = se.statistic#
  and n.name in ('session pga memory','session pga memory max', 'session uga memory','session uga memory max')
   and s.sid        = se.sid
 group by s.osuser, s.username, s.status, se.sid, s.serial#, n.name
 order by maxmem desc
 )
 select * from x where rownum < 5
 ;

After some research I've discovered the view V$PROCESS_MEMORY_DETAIL and how use it.
By default it's always empty and need some commands to "enable" it (oradebug or alter session).

References I found:

How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)
Oracle Memory Troubleshooting, Part 4: Drilling down into PGA memory usage with V$PROCESS_MEMORY_DETAIL

This quote from Oracle Article says everything :

How to use V$PROCESS_MEMORY and V$PROCESS_MEMORY_DETAIL to identify
where the memory is growing.

In Oracle 10.2 and higher exist 2 new views that can be used to find
where the memory continue to grow. This views can be used instead of
heap dump to find where the memory is growing:

 - V$PROCESS_MEMORY:

     This view displays dynamic PGA memory usage by named component categories for each Oracle process. This view will contain up to six
rows for each Oracle process, one row for:
      - Java
      - PL/SQL
      - OLAP
      - SQL
      - Freeable
      - Other

 - V$PROCESS_MEMORY_DETAIL
    Contain break down of memory allocation for each component.
    - To activate this view can one of following commands executed:
       SQL> alter session set events'immediate trace name PGA_DETAIL_GET level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG SETMYPID;
       SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>;
    - To remove all rows in the view run following command:
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;

and a useful SQL:

SELECT
    s.sid,p.spid,pm.*
FROM
    v$session s
  , v$process p
  , v$process_memory pm
WHERE
    s.paddr = p.addr
AND p.pid = pm.pid
-- AND p.spid IN (1423)
AND s.sid IN (244)
ORDER BY
    sid
  , category

Here is the informations detailed...
Just needs digging the mysterious names .

  1* select * from v$process_memory_detail
SQL> /

       PID    SERIAL# CATEGORY        NAME                       HEAP_NAME            BYTES ALLOCATION_COUNT HEAP_DESCRIPTOR  PARENT_HEAP_DESC
---------- ---------- --------------- -------------------------- --------------- ---------- ---------------- ---------------- ----------------
        15          1 SQL             miscellaneous                                   18880               80 00               00
        15          1 Other           koi korfc                  koh-kghu sessi       24544                2 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           kxsFrame8kPage             session heap         24744                3 0000002A97935BE0 000000000B0BC680
        15          1 PL/SQL          static frame of inst       koh-kghu sessi       26696                3 0000002A97A2BD50 0000002A97935BE0
        15          1 Other           kgsc ht segs               session heap         29824              327 0000002A97935BE0 000000000B0BC680
        15          1 Other           kgh stack                  pga heap             32832                1 000000000B0B6C20 00
        15          1 Other           kopo object                koh-kghu sessi       34728                3 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           koh-kghu session heap      session heap         35352               15 0000002A97935BE0 000000000B0BC680
        15          1 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        15          1 PL/SQL          miscellaneous                                   64736               47 00               00
        15          1 Other           mark                       Alloc server h      101088               24 0000002A97A99ED0 0000002A9795E090
        15          1 Other           free memory                top call heap       107520                3 000000000B0BC460 00
        15          1 Other           mark                       Alloc environm      111576               18 0000002A9795E090 0000002A97935BE0
        15          1 Other           kxsFrame4kPage             session heap        120408               29 0000002A97935BE0 000000000B0BC680
        15          1 Other           free memory                session heap        345672              213 0000002A97935BE0 000000000B0BC680
        15          1 Other           miscellaneous                                  380264              619 00               00
        15          1 Other           free memory                pga heap            917616             4618 000000000B0B6C20 00
        15          1 Other           permanent memory           koh-kghu call      2618480            32731 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           permanent memory           koh dur heap d     2619072            32731 0000002A97947E40 0000002A97935BE0
        15          1 Other           permanent memory           session heap       7450512              180 0000002A97935BE0 000000000B0BC680
        15          1 Other           permanent memory           pga heap          23983776             2907 000000000B0B6C20 00
        15          1 Other           kol vstring                koh-kghu call     34298216            32727 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           kadaih image               koh dur heap d   132475264            65452 0000002AAE0E66C8 000000000B0B6C20
        15          1 Other           koh-kghu call heap         pga heap         164949272            65457 000000000B0B6C20 00
        37         67 Other           mark                       Alloc server h        8480                3 0000002A97870860 0000002A978715C8
        37         67 Other           KFKPGA                     KFK_IO_SUBHEAP        9936                1 0000002A97899E10 000000000B0B6C20
        37         67 Other           dbgr entry                 diag pga             12248              218 0000002A976007E0 000000000B0B6C20
        37         67 Other           permanent memory           diag pga             16056                6 0000002A976007E0 000000000B0B6C20
        37         67 PL/SQL          miscellaneous                                   16232               34 00               00
        37         67 PL/SQL          PL/SQL STACK               PLS PGA hp           16432                2 0000002A9788F150 000000000B0B6C20
        37         67 Other           kgh stack                  pga heap             17024                1 000000000B0B6C20 00
        37         67 Other           dbgeInitProcessCtx:InvCtx  diag pga             17088                2 0000002A976007E0 000000000B0B6C20
        37         67 Other           kgsc ht segs               session heap         17320              286 0000002A979467C8 000000000B0BC680
        37         67 Other           kxsc: kkspsc0 2            session heap         20056               23 0000002A979467C8 000000000B0BC680
        37         67 Other           koh-kghu session heap      session heap         22752               10 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                top uga heap         31880                1 000000000B0BC680 00
        37         67 SQL             miscellaneous                                   32728               93 00               00
        37         67 Other           kxsFrame16kPage            session heap         32880                2 0000002A979467C8 000000000B0BC680
        37         67 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        37         67 Other           free memory                pga heap             47192                5 000000000B0B6C20 00
        37         67 PL/SQL          recursive addr reg file    koh-kghu sessi       51112                3 0000002A9810AA50 0000002A979467C8
        37         67 Other           permanent memory           session heap         52672                8 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                session heap         72944               23 0000002A979467C8 000000000B0BC680
        37         67 Other           miscellaneous                                  101120              178 00               00
        37         67 Other           free memory                top call heap       127936                3 000000000B0BC460 00
        37         67 Other           permanent memory           pga heap            175960               22 000000000B0B6C20 00
        37         67 Other           kxsFrame4kPage             session heap        195144               47 0000002A979467C8 000000000B0BC680

47 rows selected.


Unexpected (not helpful) result on first run of sp_BlitzIndex I just ran sp_BlitzIndex for the first time and got 1 row:

Priority=-1,
Finding=sp_BlitzIndex(TM) v5.7 - September 01, 2017: All Databases,
Database Name=NULL,
Details: schema.table.index(indexed)=http://FirstResponderKit.org,
URL=From Your Community Volunteers

Environment information:

sp_BlitzIndex version: 5.7
SQL Server version: 2008 R2 (SP2), 10.50.4000.0 (X64)
Operating System: Windows 2008 T2, SP1

It sounds like you ran it with @GetAllDatabases = 1, and an error occurred. For example, you may have more than 50 databases on your system, or it may have encountered a dynamic SQL problem.

When you're troubleshooting why a query doesn't produce the results you expected, head on over to the Messages tab in SSMS.


IDENTITY_INSERT is set to OFF? Here is my script of my db ,

CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](25) NULL,
[Name] [nvarchar](25) NULL,
[dob] [date] NULL ,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
  (
[ID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]

When I insert , I got this error

Cannot insert explicit value for identity column in table 'MyTable' when
IDENTITY_INSERT is set to OFF.

How can I solve it ?

When a column is defined as IDENTITY you should not include it in the insert statement
SQL Server by it self will insert the value.

    INSERT INTO MYTABLE([CODE],[NAME],[DOB]) VALUES (123,'BOND','01-04-1971')

If you look at the insert statement ID column is missing but still will get a incremented value upon executing this statement.


Adhoc Queries filling up plan cache I have a bloated plan cache because of Adhoc queries.
I have SQL job that goes in and purges Adhoc plans every hour. (This process was adopted from Kimberly Tripp in regards to Plan cache bloat).
When I look at the top 50 adhoc plans I see this (with much more content in each row) for all 50:

Select '100', 0, ''; begin try dbcc inputbuffer(100) with no_infomsgs end try begin catch select 'RPC Event',0,'(unknown)' end catch; Select '102', 0, '';

How can I prevent that from filling up my plan cache? BTW..
I do have optimize for ad hoc workloads on.

Thanks,
Shelz

Following can be used to clean adhoc and prepared query plan cache . This is from Glenn Berry - Eight Different Ways to Clear the SQL Server Plan Cache

DBCC FREESYSTEMCACHE ('SQL Plans')

It also says:

  “I would argue that running DBCC FREEPROCCACHE does not cause that much distress with a modern processor, even on a very busy OLTP system. It will cause a pretty minor CPU spike for a few seconds on most systems as the query plans get recompiled as they are executed. “

Following query can give adhoc queries in the plan cache

SELECT  DISTINCT
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
        ,size_in_bytes
        ,qStat.creation_time
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Adhoc'
ORDER BY MultipleQ.PlanCount DESC

If you have Query Store you can use “Delete ad-hoc queries” as defined in
Monitoring performance by using the Query Store

Enabling optimize for adhoc workloads can be found in  Kimberly Tripp - Plan cache and optimizing for adhoc workloads


MERGE running slowly I created MERGE statements to transfer differences with a minimal set of required columns from production db into a staging database. The merged tables are planned to be used by different reporting and analysis scenarios (readonly).
The whole process of getting the differences into the staging tables should be fast (at least a lot faster then the current way of getting the whole table contents entirely every day by doing a DELETE followed by INSERT INTO for all data regardless of the amount of indeed changed data that I estimate to be about 5%).

Here is a sample:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET NOCOUNT ON

IF NOT exists(select 1 from StagingDB.sys.tables t join StagingDB.sys.schemas s on t.schema_id = s.schema_id WHERE t.name like 'A' and s.name like 'staging')
BEGIN
        SELECT Top 0 [ID],[OID]
        INTO [StagingDB].[staging].[A]
        From [LiveDB].[dbo].[A];
END;

MERGE INTO [StagingDB].[staging].[A] AS Target
USING (
    SELECT [ID], [OID] From [LiveDB].[dbo].[A] where X = 0
) AS Source ([ID],[OID])
ON (Target.[ID] = Source.[ID])
WHEN MATCHED AND (Target.[OID] <> Source.[OID]) THEN
    UPDATE SET [OID] = Source.[OID]
WHEN NOT MATCHED BY TARGET  THEN
    INSERT([ID],[OID]) VALUES(Source.[ID],Source.[OID])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

IF NOT EXISTS(
    SELECT * FROM sys.indexes ix Where ix.name = 'PK_A' AND ix.object_id = OBJECT_ID('[StagingDB].[staging].[A]')
)
BEGIN
        ALTER TABLE [StagingDB].[staging].[A] add constraint PK_A primary key CLUSTERED ([ID])
END

This runs VERY QUICKLY for most of my tables. Unfortunately there are two tables that have much more rows and additionaly many more columns of those two tables are being used lateron in the process of reporting, so I have to transfer much more data.

While I can run this kind of merge command for 71 tables in within less than a minute, the two problematic MERGE statements run for about 2 hours each.
I could not yet find out why.

I suspect the amount of date but also the way I compare the tables differences. To find out if I have to run an UPDATE statement I do a comparison for each column that is included in the Merge Statement. So in case I have 10 colums the UPDATE condition of the MERGE looks like that:

WHEN MATCHED AND (Target.[OID] <> Source.[OID] OR Target.[OID1] <> Source.[OID2]  OR Target.[Text1] <> Source.[Text1] OR Target.[varcharlong1] <> Source.[varcharlong1] )  etc...

So it turns out that this kind of update condition check includes all colums and will not have an index. The staging tables always have a clustered primary key in uniqueidentifier and (so far) no additional indexes.

My question is: While the MERGE statement is running very quickly and efficient for most of our tables, it looks like this procedure is not suitable for the two larger tables including multiple columns.
Am I using the MERGE statement correctly in this case using the OR clause to make sure the rows need to be updated or is there a better way?
Is there a better way to quickly get the delta (changes) for a subset of columns into a staging table?
all of my tables do have a ROWVERSION, maybe this could be used to figure out the rows that have been changed somehow?

Don't use merge, it has always had performance problems on large tables.  This has been an on going issue since the key word merge was put into SQL Server.  See Microsoft Connect and one other site below:

https://connect.microsoft.com/SQLServer/feedback/details/635778/not-matched-and-matched-parts-of-a-sql-merge-statement-are-not-optimized

http://www.sqlservercentral.com/Forums/Topic1465931-391-1.aspx

OK, looking at your code, looks like you're trying to do everything in a single pass (which is often a good idea).
In this case, you're using the WHEN NOT MATCHED predicate, which requires a Full Outer Join to include matched rows and unmatched rows in a single pass.
The individual DELETE and UPDATE statements do not suffer from this issue, so they actually perform better.

Hope this helps
Gianluca


How to use max function in such condition? My query is

select * from guestuser union select * from olduser ORDER BY userid DESC LIMIT 1;

Is that correct query to find last userid from different table have different userid?
can i change query using max function. or optimize it?

table1 - userid -  1, 4, 5... etc

table2 - userid -  2, 3, 6...etc

Try it this way:

select userid from (
(select userid from guestuser order by userid DESC LIMIT 1)
union all
(select userid from olduser ORDER BY userid DESC LIMIT 1)
) sq
order by userid DESC LIMIT 1;


Update Query said "ORDER_DETAILS"."QUANTITY": invalid identifier Update Query said ORDER_DETAILS.QUANTITY is an invalid identifier. I check and there is a table named ORDER_DETAILS with a field named QUANTITY.

Error at Command Line : 14 Column : 57
Error report -
SQL Error: ORA-00904: "ORDER_DETAILS"."QUANTITY": invalid identifier
00904. 00000 -  "%s: invalid identifier"

///////////////////////////////////////////////

UPDATE
(SELECT Orders.Order_ID, Order_Details.Quantity, Products.Units_In_Stock, Orders.Shipped_Date, Orders.This_Order_Was_Shipped
FROM Products
INNER JOIN Orders
INNER JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID ON Products.Product_ID = Order_Details.Product_ID
WHERE ((Orders.Shipped_Date Is Not Null) AND (Orders.This_Order_Was_Shipped='YES')))
SET Products.Units_In_Stock = Products.Units_In_Stock - Order_Details.Quantity;

For reference, this is a continuation of the following post.
How to convert an Update a query from MS Access to Oracle SQL Software

I kind of wish you would have continued our conversation there, but I guess they can be merged somehow.

I think you might have taken what I said literally and just switched ORDER DETAILS table to ORDER_DETAILS in your SELECT statement instead of changing the actual object's name.

The object's name was [ORDER DETAILS] and needs to be identified as "ORDER DETAILS" in ORACLE (with Quotes instead of brackets).  Try that and see.

If not, and you actually did change the table itself to ORDER_DETAILS, then your issue is that you're using ORDER_DETAILS.Quantity in your SET.  That is not appropriate.  You NEED to give the subquery that the UPDATE wraps an ALIAS and say:

SET Alias.OldValue = Alias.NewValue

You should set up your SELECT to retrieve the OldValue of the column, and the NewValue of the column.  Please see the link I included in my original answer:
https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle


Expanding a dataset based on a weight field I have a table that contains records in an un-expanded form. Each record has an associated integer weight that essentially informs us how many times the record should be replicated in order to get the true population.

Say, I have 3 records in my table. sampn ids a unique record and weight is the frequency weight. The un-expanded dataset looks like this:

sampn   weight  attrib1 attrib2 attrib3
    1       2       23      32      65
    2       1       32      56      75
    3       3       54      25      87

Once expanded, the dataset will be like this (note - I removed the weight field - but this is not essential):

sampn   attrib1 attrib2 attrib3
    1       23      32      65
    1       23      32      65
    2       32      56      75
    3       54      25      87
    3       54      25      87
    3       54      25      87

I have tried to do this using cursors but it is taking a really long time to execute. Is there a clever way to do this really fast? Any predefined T-SQL stored procedure that achieves this?

Update
All,
Thanks for the answers! Really great learning experience! Performed the expansion operation on my dataset. Paul's auxiliary table of numbers had the best execution time.

An efficient way to perform this task uses an auxiliary table of numbers. This is simply a table with integers from 1 to n, where 'n' is perhaps a million or so. A numbers table comes in handy for all sorts of regular tasks.

CREATE TABLE dbo.Demo
(
    SampleNumber    integer IDENTITY NOT NULL,
    SampleWeight    integer NOT NULL,
    Attribute1      integer NOT NULL,
    Attribute2      integer NOT NULL,
    Attribute3      integer NOT NULL,

    CONSTRAINT [PK dbo.Demo SampleNumber]
        PRIMARY KEY (SampleNumber),

    CONSTRAINT [CK dbo.Demo SampleWeight 1-50]
        CHECK (SampleWeight BETWEEN 1 AND 50)
);

INSERT INTO dbo.Demo
    (
    SampleWeight,
    Attribute1,
    Attribute2,
    Attribute3
    )
VALUES
    (2, 23, 32, 65),
    (1, 32, 56, 75),
    (3, 54, 25, 87);

SELECT
    D.SampleNumber,
    D.Attribute1,
    D.Attribute2,
    D.Attribute3
FROM dbo.Demo AS D
JOIN dbo.Numbers AS N
    ON N.n BETWEEN 1 AND D.SampleWeight;

DROP TABLE dbo.Demo;

Execution plan:

Output:

╔══════════════╦════════════╦════════════╦════════════╗
║ SampleNumber ║ Attribute1 ║ Attribute2 ║ Attribute3 ║
╠══════════════╬════════════╬════════════╬════════════╣
║            1 ║         23 ║         32 ║         65 ║
║            1 ║         23 ║         32 ║         65 ║
║            2 ║         32 ║         56 ║         75 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
╚══════════════╩════════════╩════════════╩════════════╝

SQLfiddle here.


Is there a service broker target for an extended event session We use Service Broker heavily for asynchronous background processing.
Additionally we want to use it for extended event processing.

Is it possible to use Service Broker listener as target for an extended event session?

There looks to be a service_broker target in SQL Server 2017 but I can't find any documentation on it. This target is visible with the latest SSMS GUI and this query:

    SELECT *
    FROM sys.dm_xe_objects
    WHERE
        object_type='target'
        AND name = N'service_broker';

Without documentation, I experimented using the script below but could not capture any events and saw no SB activity. The execution_count in sys.dm_xe_session_targets increments so it seems the plumbing is there. It could be an issue with my script or the target might not be fully implemented by SQL Server.

I'll see if I can find more info about the service broker target and report back here.

/*
DROP EVENT SESSION [service_broker_target_test] ON SERVER
DROP SERVICE InitiatorService;
DROP SERVICE TargetService;
DROP CONTRACT ExtendedEventMessageContract;
DROP MESSAGE TYPE ExtendedEventMessageType;
DROP QUEUE InitiatorQueue;
DROP QUEUE TargetQueue;
*/

CREATE QUEUE InitiatorQueue;
CREATE QUEUE TargetQueue;
CREATE MESSAGE TYPE ExtendedEventMessageType VALIDATION = NONE;
CREATE CONTRACT ExtendedEventMessageContract (
      ExtendedEventMessageType SENT BY ANY
    );
CREATE SERVICE InitiatorService
    ON QUEUE dbo.InitiatorQueue (ExtendedEventMessageContract);
CREATE SERVICE TargetService
    ON QUEUE dbo.TargetQueue (ExtendedEventMessageContract);
GO

CREATE EVENT SESSION service_broker_target_test ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1))
ADD TARGET sqlserver.service_broker(SET database_id=(14)
    ,initiator_service=N'InitiatorService'
    ,message_type=N'ExtendedEventMessageType'
    ,service_contract=N'ExtendedEventMessageContract'
    ,target_service=N'TargetService')
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

ALTER EVENT SESSION service_broker_target_test ON SERVER
    STATE = START;
GO

SELECT * FROM sys.conversation_endpoints;
SELECT * FROM dbo.InitiatorQueue;
SELECT * FROM dbo.TargetQueue;
SELECT * FROM sys.dm_xe_session_targets
WHERE target_name = 'service_broker';
GO


Unable to force drop Postgres database I'm trying to forcibly drop a PostgreSQL 9.4.4 database from a Bash script using psql:

#!/bin/bash
db="mydb"
psql -d $db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$db';"

I'm using the solution shown in this question but it doesn't work. What I see is this:

FATAL: terminating connectoin due to administrator command
server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.

What am I doing wrong? I own the database and my user role has the "Superuser" attribute as shown when I do \du from the Postgres console.

You are killing your own connection with that statement. You need to add and pid <> pg_backend_pid() to exclude the session where you run that statement:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '$db'
  and pid <> pg_backend_pid();


Centralized Backup report for SQL server databases Even though, I've searched couple of them online but still looking if there is a better way to manage the SQL server backups, almost across 250 servers.

Issue is we are not allowed sysadmin login to be created on one server to pull this report from.

Please suggest a link or way to view the health report for sql server backups.

Also SSRS works as we are not supposed to use database mail to sen emails

Use your central repository where you keep information for all servers (Most likely another SQL server which is used as a central repository for DBA stuff) in the firm. Use below tsql query as a start and modify as necessary.  You can loop thru all your servers and collect backup history. That can be another tsql script called via SQL Agent job or PowerShell. Then you will need to set up a threshold for each type of backup and get alerted if the threshold exceeds.

For example if you do a full backup once a week, differential every other days, and transaction log at xx minute interval; you can set up thresholds of 168 hours for full backup, differential 24 hours + some buffer, transaction log xx minute + some buffer.

-- Get Backup History for required database
SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1048576 AS INT) AS VARCHAR(14))  AS bkSizeMB,
CAST(CAST(s.compressed_backup_size / 1048576 AS INT) AS VARCHAR(14))  AS Compressed_bkSizeMB,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(12)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.backup_start_date>'2017-04-01'  --adjust your date
--Uncomment below lines if you want a one or more type of backup
--AND (s.type='D' OR s.type ='I')
--AND (s.type='L' )
--Uncomment below line if you want to filter by database name
--AND database_name ='security'
ORDER BY backup_start_date DESC, backup_finish_date

As an alternative you can leverage these two PowerShell function to achieve the same result.

Get-DbaBackupHistory
Get-DbaLastBackup


What is the purpose of the Save Transaction statement? I read up on SAVE TRANSACTION, but I'm not completely sure I understand it. I'm aware of the concept of transactions as far as BEGIN, COMMIT, and ROLLBACK, but this is a new one.  It sounds almost like it's there for keeping the size of your transaction log down, or not affecting the performance of the server when working on lots of data at once.

Here is the example I found:

  "For example, you could create a save point during a large operation
  every so often so that the rollback or commit trans is not having to
  handle as many records when it is performed."

Can someone explain this better?  Postgres has something similar called the SAVEPOINT command.

The use of Save Transaction provides you with a mechanism for rolling back portions of a transaction. For example SP A starts a transaction which then calls SP B. At the start of SP B, a Save Transaction Start Processing could be created.

If an error then occurs in SP B you could just rollback the change in the SP B allowing the changes in SP A to be committed.


Can Availability Groups replace transactional replication? I've been looking at the new features of SQL Server 2012, and how it can help my current situation.

Currently we're on SQL Server 2008 R2, and replicate two OLTP servers' databases to a single reporting server via transactional replication. We then use these replicated DBs for reporting.

I'm interested in whether SQL Server 2012 and the Availability Groups can replace this - so instead of transactional replication, I'd use the new availability groups and mirror the two OLTP servers' databases to the reporting server - where the reporting will be able to access the read-only replicas.

I'm unsure if this will work well, or even at all.

I would like to get away from replication, as it causes issues with my deployment strategy (Using VSDBCMD.exe). I'd also like to get away from replication reinitialisation delays on large DBs.

Does anyone have any good examples or experience with this? Is it possible to mirror with readable replicas from multiple servers to a single server as per normal mirroring in lower versions?

This was originally asked here: https://stackoverflow.com/questions/10415225/mirroring-and-availability-groups-in-sql2012 Sorry I don't think I can migrate questions yet.

Swiped from my answer on StackOverflow, only to prevent others from spending effort on the same type of answer.

Personally I think this will work a lot better than transactional replication, though I haven't done any formal comparisons of the two in a true migration scenario. I know that with the amount of troubles folks have with transactional replication, compared to even standard mirroring (and this is an upgrade of that), you are certainly bound to have fewer problems.

The biggest boon is that the secondary can be marked as read only - so you can run all the reporting off of it that you want, and it won't affect the mirroring at all. You just need beefier tempdb (since it essentially uses rcsi to do this).

Of course you do need to be aware that both sides of the AG need to be fully licensed in order to use the replica for read-only operations. And both sides need to be running on specific versions of Windows (Enterprise or better on 2008 R2 and lower; Standard or better on 2012 and above) because they require failover clustering - the SQL instances in AGs don't need to be clustered, but they need to be sitting on top of that infrastructure in the OS.


charindex thrown off by extended characters I've got a column with a filenames stored in a nvarchar(255). I'm trying to parse out the file extension using

reverse( left( reverse(filename), charindex('.', reverse(filename) ) -1 ) )

This works great for most of my records, however some are failing due to charindex returning 0 while a '.' is definitely part of the string.

I think I've isolated it to an issue with accented characters and a few others.

One of them I have is ité.pdf, when reversed, charindex is not finding the '.'

fdp.́eti

I've taken to converting it to it's hex representation to narrow down what's going on using

cast(reverse(filename) as varbinary(max))

0x6600640070002E000103650074006900

From what I can work out The '.' is 2E 00. 01 03 65 00 is the 'é' which is encoded differently than the others I have in the same column.

Since 2E 00 comes first I would still expect charindex to get a match, but it's returning me 0, which in turn make the call to LEFT fail.

Am I missing something obvious here? Converted to HEX it seems the '.' I am looking for is definitely there, but the character after is tripping charindex somehow.

EDIT: Simplified test

When outputting the string there is clearly a ., but charindex is not finding it?

select charindex('.', cast(0x6600640070002E000103650074006900 as nvarchar(max)))

Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cda6f0a77fa9e206bff9217ec58be075

Apparently it seems related to a collation or encoding issue.

I'd check database and column collations to ensure both are the same.

Server collation

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

Database collation

SELECT
    name,
    collation_name
FROM
    sys.databases
WHERE
    name = 'DBName';

Column collation

SELECT
    name,
    collation_name
FROM
    sys.columns
WHERE
    name = N'ColumnName';

Quoted from MS-Docs Collation precedende

  Functions and Collation

  THE CAST, CONVERT, and COLLATE functions are collation sensitive for
  char, varchar, and text data types. If the input and output of the
  CAST and CONVERT functions are character strings, the output string
  has the collation label of the input string. If the input is not a
  character string, the output string is Coercible-default and assigned
  the collation of the current database for the connection, or the
  database that contains the user-defined function, stored procedure, or
  trigger in which the CAST or CONVERT is referenced.

  For the built-in functions that return a string but do not take a
  string input, the result string is Coercible-default and is assigned
  either the collation of the current database, or the collation of the
  database that contains the user-defined function, stored procedure, or
  trigger in which the function is referenced.

  The following functions are collation-sensitive and their output
  strings have the collation label of the input string:

  CHARINDEX, REPLACE, DIFFERENCE, REVERSE, ISNUMERIC, RIGHT, LEFT, SOUNDEX, LEN, STUFF, LOWER, SUBSTRING, PATINDEX, UPPER

Let me suggest to have a look at this or this answers of Solomon Rutzky about UTF-8 and UTF-16 encoding values for VARCHAR and NVARCHAR.

By now you can try by CASTING it to VARCHAR(255) before use CHARINDEX.

I've tried to reproduce it in one of my servers in this way:

DECLARE @t VARBINARY(MAX) = 0x6600640070002E000103650074006900;
DECLARE @vt VARCHAR(MAX);

SELECT CAST(@t AS nvarchar(MAX));

---------
fdp.́eti

SET @vt =  CAST(CAST(@t AS nvarchar(MAX)) AS VARCHAR(MAX))

SELECT REVERSE(LEFT(REVERSE(@vt), CHARINDEX(N'.', REVERSE(@vt))-1));

-------
´eti

Note it returns the accent as a single character.


PostgreSQL pg_upgrade --link mode performance issue after upgrade Trying to do PostgreSQL upgrade from 9.0 to 9.3. I'm using pg_upgrade with the --link mode since the downtime is very minimal compared to other options like pg_dumpall and pg_upgrade without using --link.

After upgrading and executing an SQL-statement to check execution-time. It is taking nearly 30 minutes the 1st time and for the 2nd time onwards, it is taking 15 mins. The same query executed in 9.0 is not even taking a full 45 seconds.

No of Rows : 150000 records
Using same postgresql.conf file
vaccumdb all : done after upgrade

Is there any thing else I need to configure, to improve the performance?

It doesn't state anywhere in the documentation that pg_upgrade --link will degrade performance.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID for new cluster                 ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster

mydatabase

Setting minmxid counter in new cluster                      ok
Removing support functions from new cluster                 ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /opt/postgres/9.0/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

I got a message like the above, but I couldn't find such files in the postgres folder to execute.

I am using postgresql 9.3.5 from openscg community

**postgresql.conf changes**

shared_buffers = 4GB
work_mem = 128MB
maintenance_work_mem = 256MB
effective_cache_size = 12GB

After a pg_upgrade all your optimizer statistics are gone. You need to ANALYZE all of your tables.  This is true regardless of whether or not you use --link.


Killed SPID on xp_LogInfo not rolling back and growing tempdb I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task.
I checked the scheduled tasks and we had one that had been running for over a day, usually it runs for less than a minute, and it was showing a status of "Executing: 0(Unknown)" in the job activity monitor.
When I tried to stop the Job in Activity Monitor nothing happened, it just kept on running and filling up tempdb to almost the full size of the disc.  Knowing that the job itself could just be stopped and re-run without affecting the business, I, perhaps too hastily, killed the SPID, thinking this would "release" space back to tempdb and I would then restart the services out of hours to shrink the files.
Unfortunately this didn't work and the SPID has been in a Killed/rollback state for nearly a day now whilst the tempdb files grow (I arranged for the disk they sit on to be increased whilst I looked into it).
When I check the process for the SPID it appears to be running xp_LogInfo with the last batch relating to insert #nt select distinct domain+N'\'+name, sid, sidtype from OpenRowset(TABLE NETUSERGETGROUPS, @acctnam.
I believe that the owner of the SQL Job may have reset their password when the job kicked in to cause this (this is just me guessing) which has caused it to get stuck in a loop.
The CPU and MemUsage in sysprocesses hasn't changed for over a day now and the LastWaitType is showing as PREEMPTIVE_OS_AUTHORIZATIONOPS.
When running DBCC INPUTBUFFER for the SPID I previously killed I am receiving this:
EventType - Language Event
Parameters - 0
EventInfo - (@P1 nvarchar(128),@P2 uniqueidentifier)EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1, @job_id = @P2

I have now restarted the SQL Server Agent Service which stopped the job running but the tempdb log continues to grow.
What can I do to resolve this?
When running DBCC OPENTRAN(TEMPDB) I get the below, the spid it relates to is the one I killed:
Transaction information for database 'tempdb'.
Oldest active transaction:
SPID (server process ID): 205
UID (user ID) : -1
Name : INSERT EXEC
LSN : (5293:26478:20)
Start time : Oct 19 2020 3:30:02:193PM
SID : 0x0105000000000005150000005f9b10c13b99fcfc4ad890fd8e220200

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

kill {spid} with statusonly returned "SPID 205: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."

Thanks for the assistance on this one,
Although the kill/rollback wasn't showing to be progressing it looks as though it was and after 45 hours the process completed and the space returned to my TempDb
Thanks again


join 3 tables with one to many and one to one relationship I have 3 tables:
    Memory_map:
        - Id
        - Memory_map_version
        - Product_id_hex
        - Parameter --------------------> want to add this to Histogram table

    Histogram_read:
        - Id
        - Memory_map_version -------> these 2 keys are referencing to the Memory_map table
        - Product_id_hex ---------> these 2 keys are referencing to the Memory_map table

    Histogram:
        - Id
        - Histogram_read_id

The relationship is:
Histogram_read (one) --> histogram(many)

Memory_map (one) --> histogram_read(one)

So Now I want to create a NEW Histogram table with the [Parameter] field from Memory_map table added to the original Histogram table.
Because Memory_map (one) --> histogram_read(one) is a ONE-TO-ONE relationship, so I will need to join these two tables first before I join the Histogram_read table with Memory_map table to add the [Parameter] field to Histogram table.
So, should I do a inner join first between Histogram_read and Histogram based on Histogram_read_id? (since Histogram_read_id is a key referencing the Histogram_read table records), but since it's a ONE-TO-MANY relationship, is it ok to do the INNER JOIN?
And Because I only want all records and columns from Histogram table - just with [Parameter] field added, so if I join the Histogram_read table with Histogram table before I join the Histogram_read table with the Memory_map table, how do I make sure the final table of Histogram wasn't messed up by columns in the Histogram_read table?
I have this following query but it doesn't work:
create view histogram_new
as
select M.parameter, Nest.* from memory_map M
RIGHT JOIN
(select * from histogram_read R
INNER JOIN histogram H
ON H.histogram_read_id = R.id) Nest
ON M.memory_map_version = Nest.memory_map_version
AND M.product_id_hex = Nest.product_id_hex;

I'm confused about the 3 tables join with one=to-many and one-to-one relationship, I am not sure what the logic should be. anyone can helps? THANKS!

With relationship cardinalities between tables, you should think of the outcome of INNER JOINING those tables as multiplicative per row, in a sense. In a one-to-one relationship you'll always end up with, at most, 1 x 1 record per record in the smaller table. For example if your memory_map table has 10 records and your histogram_read table has 5 records, the most records you could possibly end up with is 5 records.  Those would be whichever 5 records in memory_map that had a related record to the 5 in histogram_read.
In a one-to-many relationship, the above is similarly true except the many table dictates how many rows will be returned per row in the one table. So now the multiplicative relationship is 1 x N where N is the number of related rows in the many table to 1 particular row in the one table. For example, if your histogram_read table has 5 records, and your histogram table has 20, the most records an INNER JOIN could possibly return is 20, if all 20 records related to the 5 records in histogram_read. (Then you'd be getting every row back from histogram in that case.)
So in short, with an INNER JOIN, for a one-to-one relationship, the most records you can end up with is the total number of records in the smaller table between the join. For a one-to-many relationship, the most records would be the total number of records in the larger table.
The last thing to keep in mind is INNER JOIN only returns rows that match between both tables of the join clause, and filters out the rows that don't match. OUTER JOIN like a LEFT OUTER JOIN (LEFT JOIN for short) or RIGHT OUTER JOIN (RIGHT JOIN for short) will always return all rows from the table on that side of the join clause, and will NULL out the columns of the table on the other side of the join clause where there aren't any matches. For example, if your histogram_read table has 5 records and histogram has 20 records, and only 12 of the records in histogram related to the 5 records in histogram_read then with an INNER JOIN between the two tables you would only get 12 records. But with an OUTER JOIN that points to the histogram table, such as FROM histogram_read RIGHT JOIN histogram, will ensure you get all 20 records back from the histogram table, with NULL values in the columns from histogram_read for the 8 rows that didn't match from histogram.
All of that being said, I don't see anything inherently wrong with the query you wrote, other than the INNER JOIN in your subquery possibly filtering rows out of histogram if they don't match histogram_read. You can use all OUTER JOIN clauses to prevent that from happening, and you actually don't even need a subquery. Here's an example query that should essentially produce what you're looking for:
SELECT M.parameter, R.*, H.* -- You shouldn't use *, it's bad practice, anti-pattern, and potentially bad for performance, instead explicitly list only the columns you need
FROM histogram H
LEFT JOIN histogram_read R
    ON H.histogram_read_id = R.id
LEFT JOIN memory_map M
    ON M.memory_map_version = R.memory_map_version
    AND M.product_id_hex = R.product_id_hex

With one-to-many relationships, I personally prefer to start with the many table in my joins, but it doesn't really matter, as long as you use the correct OUTER JOIN. In this case, because the many table is on the left side of the join clause, we need to use a LEFT JOIN. The above query will return all rows from histogram, with the parameter column from memory_map, with NULL values for wherever there wasn't a match on a record between histogram and the rest of the tables.