Online index rebuild LOB types I have read and heard that you cannot perform online clustered index rebuild for tables that have a varchar(max) type. This (as I have read) also should be true for nonclustered indexes that contain varchar(max) types. http://msdn.microsoft.com/en-us/library/ms190981.aspx. However when proving this out (in both versions of SQL2012 and SQL2014) I do not run into errors when rebuilding these indexes online. Is this an error in the documentation?

As a side note the online rebuilds fail on 2008R2.

Test script is included below ...

CREATE TABLE BIGMAX ( X INT IDENTITY(1,1), Y VARCHAR(MAX), FILLER BIT DEFAULT 1, FILLER2 BIT DEFAULT 1);

INSERT INTO BIGMAX ( Y ) SELECT top 1000 replicate('1',10000) from sys.all_columns c , sys.all_columns c2

CREATE CLUSTERED INDEX [CIDX_BIGMAX] ON BIGMAX(X) WITH (ONLINE = ON);

CREATE NONCLUSTERED INDEX [NIDX_BIGMAX_FILLER] ON BIGMAX(FILLER) WITH (ONLINE = ON);

CREATE NONCLUSTERED INDEX [NIDX_BIGMAX_FILLER2] ON BIGMAX(FILLER) INCLUDE (Y) WITH (ONLINE = ON);

ALTER INDEX [CIDX_BIGMAX] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

ALTER INDEX [NIDX_BIGMAX_FILLER] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

ALTER INDEX [NIDX_BIGMAX_FILLER2] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

Ok, so the documentation is not exactly stellar, but you must think this shouldn't work online because the doc says:

  Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

Which - if you invert it - states that an index that includes a MAX type, for example, can't be created online.

Now, the emphasis on the word created was put there by me - this stated restriction does not apply to rebuild (which I guess explains why rebuild isn't mentioned explicitly in that sentence). In your updated code sample, you actually have demonstrated that both rebuild and create, with the option to run online, run successfully and don't return an error. I have confirmed that this is the case (and at least in the create scenario, that conflicts with the sentence above). I have also received confirmation from Microsoft that the doc is wrong.

In fact, in the table below that (for both the 2012 doc and the 2014 one), there is a list of the types of indexes that are not supported by rebuild or create, and LOB types are no longer a constraint (whether part of the base table or included in the index being created or rebuilt). These are the only indexes that you can't rebuild online:

Disabled clustered index or disabled indexed view
XML index
Index on a local temp table

And these are the only indexes that you can't create online:

XML index
Initial unique clustered index on a view
Index on a local temp table

So this is kind of like reverse documentation - you didn't experience a problem, and this document didn't exactly lead you to believe that you should experience a problem. Well, not intentionally. I don't know that there is a document in MSDN that says, "Listen up yo, you can now create and rebuild indexes online when LOB columns are part of the equation!" - but as I pointed out above, others have blogged about it.

In the end, you are correct, and the documentation is wrong , as is the IntelliSense tooltip you get when you try to do the same - it will underline the index name as if there is an error, even though when executing the code, it works successfully. Click to enlarge:

Whether the creation or rebuild actually happens online, I did not test; only confirming that it did not generate an error, as IntelliSense (and the document above) would have you believe. But according to Microsoft and Remus' blog above, it should work as expected (forget what the doc currently says).

I've filed the following Connect item to deal with the IntelliSense problem:

SSMS : IntelliSense incorrectly warns index with MAX can't be created online

Back in June of 2012, I filed this item against the documentation, which at the time still had an entry in the table explaining that LOB types invalidated online operations.

[DOC] : ALTER INDEX topic still says no online rebuild with LOB

That Connect item was marked as fixed, because the table was corrected, but the sentence I highlight above still needs to be fixed. I'm waiting to hear if I should petition to have my existing item re-opened (we used to be able to do that ourselves), or create a new one. I'll update here when I hear back. The Connect item has been re-opened and the topic owner has received a detailed e-mail. :-)


Copy maintenance plans to a different server Is there a way to copy maintenance plans from one SQL Server 2012 to another? Both servers are not accessible via the same copy of Management Studio at the same time (on different networks).

Go to the server where the jobs exist, in Management Studio's Object Explorer go to Management > SQL Server Agent and highlight the Jobs folder. Hit F7 (or View > Object Explorer Details). You should see multiple jobs in the right pane. Highlight the ones you want to copy, right-click, Script Job As > Create To > Clipboard. Now connect to the other server, open a new query window, paste, and hit F5. Much easier than mucking with SSIS.

You may have to take additional steps, depending on what the plans do. Connect to the Integration Services instance, rather than the database instance, expand Stored Packages > MSDB > Maintenance Plans, and right-click any plan and choose Export package. On the other server, follow the same steps, but right-click Maintenance Plans and choose Import package. You will have to do this for each plan.


store sql handle from xml to table for later use SQL Server 2014

I have an application that provides the xml for blocking reports.  The xml contains a sqlhandle that I'm wanting to store in a separate database for later consumption.

The sql handle is stored in the table using the following code

INSERT INTO BlockingSqlHandle(BlockID, SQLServerID, Line, StmStart, StmEnd, sqlhandle)
SELECT f.BlockID,
       f.SQLServerID,
       f.frame.value('data(/frame/@line)[1]','INT') line,
       f.frame.value('data(/frame/@stmtstart)[1]','INT') stmtstart,
       f.frame.value('data(/frame/@stmtend)[1]','INT') stmtend,
       f.frame.value('data(/frame/@sqlhandle)[1]','VARCHAR(1000)') sqlhandle
  FROM f;

However when I try to pull the sqlhandle out for use in sys.dm_exec_sql_text() I get an error "The handle that was passed to dm_exec_sql_text was invalid."
Or no value is returned.

If I pull the handle from the xml and put it into the query manually it returns just fine.

What is the proper way to store a varbinary value that comes from a file so that I can pull the value out and use it with dm_exec_sql_text?

Objects pointed to by handles are temporary.  Do not store them, however if you do store them, use a varbinary(64) datatype and realize the object they refer to may not exist in memory later on.

Instead of storing the handle, it's likely much better to pull the actual sql text or plan text, and store that.

From Microsoft Docs:

  The lifetime of sql_handles used to retrieve the SQL text [...] outlives the cached plan used to execute the query. If the query text is not available in the cache, the data cannot be retrieved using the information provided in the function result.


Updating total using values from previous row - SQL Server 2016 I have a table that depends on the previous row’s values for its new total. We have discovered that a couple of years ago a value was incorrectly altered and has subsequently made all following values incorrect.  I have inherited a formula that uses the previous date to calculate but there appears to be dates missing in the table and at this point the formula stops. I would like to make the update use the previous row rather than the date. I have tried using LAG but had issues using it with an UPDATE.

This is an example of the type of data I am using

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20

I need to add Value1 + Value2 + Value3 * Previous Day Total. The answer to this will update the following row's total. As mentioned I need to use an UPDATE because there is existing data in the total, but it's currently incorrect.

The results I would like to achieve are in the Table below. Please be aware that dates are not sequential so I can't use date as a condition. An answer with LAG would be great.

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20
|20151114 |  2   |  3   |  1   | 140
|20151115 |  1   |  1   |  1   | 420
|20151117 |  1   |  3   |  1   | 2100
|20151119 |  2   |  1   |  3   | 12600

Example - for the 20151114 - 2 + 2 + 3 * 20 (Total from the previous row) = 140

I had to do something similar once and had a good result from using a windowing function like below:

SELECT RunningTotal = History.Total + ISNULL((SUM(History.Total) OVER
                    (PARTITION BY PartitionID
                    ORDER BY History.EffectiveDate ASC, History.HistoryID ASC
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 0)
FROM   History
ORDER BY History.EffectiveDate, History.HistoryID

This sums up all of the rows before the current row.

You can't update straight from that, but you could put it into a CTE or insert the data into a temp table and then update from one of those.

The performance of it isn't great, but for a one off script in a maintenance window it was good enough for us.


Most efficient way to identify records with any number of key columns I work with large third party data-sets.  Long experience has taught me that it is a really good idea to give incoming lines of data a surrogate ID as soon as it enters my system so I can track it easily as it's validated, warehoused, etc.  The problem is the key values can potentially be every dimensional value, which could be 200 columns.

My general process is this:

load data to a staging table.
match the data with a second IdMatch table that holds only the key values and surrogate IDS.

IF OBJECT_ID('Staging.myTest') IS NULL
    CREATE TABLE Staging.myTest (
        [ID] INT IDENTITY(1,1) NOT NULL,
        [Hash] INT NULL,
        [Dim_1] NVARCHAR(32) NOT NULL,
        [Dim_2] NVARCHAR(32) NOT NULL,
        [Dim_3] NVARCHAR(32) NULL,
        [Met_1] INT NULL,
        [Met_2] DECIMAL(5,2) NULL
    );

IF OBJECT_ID('IdMatch.myTest') IS NULL
    CREATE TABLE IdMatch.myTest (
        [ID] INT IDENTITY(1,1) NOT NULL,
        [Hash] INT NULL,
        [Dim_1] NVARCHAR(32) NOT NULL,
        [Dim_2] NVARCHAR(32) NOT NULL,
        [Dim_3] NVARCHAR(32) NULL,
    );

TRUNCATE TABLE Staging.myTest;
TRUNCATE TABLE IdMatch.myTest;

INSERT INTO Staging.myTest
    ([Dim_1], [Dim_2], [Dim_3])
VALUES ('A', 'A', 'A'),
    ('B', 'B', 'B'),
    ('C', 'C', NULL),
    ('C', 'C', 'C'),
    ('D', 'D', 'D');

INSERT INTO IdMatch.myTest
    ([Dim_1], [Dim_2], [Dim_3])
VALUES ('A', 'A', 'A');

--My Proc (as script) for setting the index.
INSERT INTO [IdMatch].myTest
    ([Dim_1], [Dim_2], [Dim_3])
SELECT src.[Dim_1], src.[Dim_2], src.[Dim_3]
FROM Staging.myTest AS src
WHERE NOT EXISTS (
        SELECT tgt.[Dim_1], tgt.[Dim_2], tgt.[Dim_3]
        FROM [IdMatch].myTest AS tgt
        WHERE tgt.[Dim_1] = src.[Dim_1]
            AND tgt.[Dim_2] = src.[Dim_2]
            AND tgt.[Dim_3] = src.[Dim_3]
    );

SELECT * FROM IdMatch.myTest

THE PROBLEM:
Matching to get a surrogate ID this way takes a long time when the real data-set contains 200+ columns of NVARCHAR data.  Is there a better way?  I've tried pre-computing hashes, but am not sure how to handle the eventual collisions I'll produce.

One common approach is to choose a hash function with a very, very small chance of collision and then assume there won't be any:

CREATE TABLE Staging.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS
        CONVERT(binary(32),
            HASHBYTES('SHA2_256',
                CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
    [Met_1] INT NULL,
    [Met_2] DECIMAL(5,2) NULL
);
GO
CREATE TABLE IdMatch.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS
        CONVERT(binary(32),
            HASHBYTES('SHA2_256',
                CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
);
GO
-- Declared unique because we have decided it will be
CREATE UNIQUE NONCLUSTERED INDEX
    IX_HASH
ON IdMatch.myTest
    ([Hash]);

Note: Most people replace column NULLs with empty string for hashing, which is the default behaviour of CONCAT. If you need to differentiate NULL from empty string, you would need to identify some other magic value to use, and wrap the nullable column with ISNULL or COALESCE.

Adding rows that don't match is then:

INSERT Staging.myTest
(
    Dim_1,
    Dim_2,
    Dim_3
)
SELECT
    SRC.Dim_1,
    SRC.Dim_2,
    SRC.Dim_3
FROM Staging.myTest AS SRC
WHERE
    NOT EXISTS
    (
        SELECT 1
        FROM IdMatch.myTest AS TGT
        WHERE
            TGT.[Hash] = SRC.[Hash]
    );

See Finding rows that have changed in T-SQL – CHECKSUM, BINARY_CHECKSUM, HASHBYTES by Greg Low. You should test this approach with your data to see if this scheme will work for you.


Which hardware you suggest? I'm completely new to buying a database hosting and I would greatly appreciate your opinion about the following situation. I just created an App and I want to go live this weekend. I want to be prepared as good as possible so I will write some statistics of the database usage I think to see every 24 hours.

- 20 000 000 inserts into a table which contains 1 bigint, 7 ints and 1 date.
-  5 000 000 updates on above table
- 10 000 000 inserts into mapping table 1 bigint 1 int
-  2 000 000 inserts into table with 3 ints
-  5 000 000 select queries with WHERE pk reference of first table described above

I will use the MySQL 5.5 database with all InnoDB tables. At the moment I am thinking about buying a dedicated server with these stats:

Intel Xeon E3-1220LV2 2.3GHz
100 Mbps Uplink
4 GB DDR3 Memory
500 GB RAID-1 Drives
10 TB Bandwidth
Up to 5 Dedicated IPs

For a price of $140 dollar a month. What do you think, will this be enough? Would it be overkill? You know a better hosting? At the moment I am on a shared webhost ($12 a month) and I get a fifth of the numbers above, but the shared database is really not optimized for innoDB.

If you are honestly expecting to have the numbers you say for an all-InnoDB database, you are going to be needing a lot more memory. Here is why:

InnoDB caches data and index pages in the InnoDB Buffer Pool. If you will be doing heavy reads, a large buffer pool is your best friend. Since most people recommend 75-80% of RAM for a dedicated MySQL DB Server, that would about about 3200M (3.2G).

I would go the following (32GB or RAM)

innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 4
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_file_per_table = 1

There are too many other aspects for me to just put in the answer. Here are some of my InnoDB posts:

Possible to make MySQL use more than one core?
How large should be mysql innodb_buffer_pool_size?
How do you tune MySQL for a heavy InnoDB workload?
Clarification on MySQL innodb_flush_method variable
Is it safe to use innodb_flush_log_at_trx_commit = 2
My Other InnoDB Posts

Don't forget to tune the per-connection settings and multiply the sum of them by max_connections : See my post Understanding mysqltuner Recomendations w/ Unused Catalogs


How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package.

I am using SQLDeveloper 3.0 with an Oracle 11g database.

I have already developed a stored procedure 'acctg_detail_proc()' that generates a detailed list of accounting transactions within a specified period. What I am trying to do is create a summary report procedure 'acctg_summary_proc()' using the detailed data returned by acctg_detail_proc().

Since I am new to PL/SQL, all I have learned so far is how to retrieve the detailed data via a ref cursor, then LOOP through it, FETCHing each individual detail row. I want to figure out how acctg_summary_proc() can substitute that call to acctg_detail_proc() for a table name in a SELECT statement with a GROUP-BY clause.  Here is the source code for an anonymous block where I tried to test it:

SET SERVEROUTPUT ON;
DECLARE
    start_date VARCHAR2(50) := '04/01/2012';
    end_date VARCHAR2(50) := '04/30/2012';
    c_acctg_refcur    SYS_REFCURSOR;
BEGIN
  acctg_rpt_pkg.acctg_detail_proc(start_date, end_date, c_acctg_refcur);
  SELECT
    date_posted,
    debit_acct,
    credit_acct,
    SUM(dollar_amt)
  FROM c_acctg_refcur
  GROUP BY
    date_posted,
    debit_acct,
    credit_acct;
  CLOSE c_acctg_refcur;
END;

When I try to execute this code, I get the following error:

PL/SQL: ORA-00942: table or view does not exist

I realize I could use a nested SELECT statement instead of a table name,
but I want to avoid duplication of source code. Is there any way to
'alias' a ref cursor so I can reference its data in a SELECT statement?

Here is some further background info:
The called sub-procedure has ~600 lines of code, and selects 40 columns of data from a de-normalized VIEW.  The corporate DBAs will not let me create any VIEWs that contain WHERE clauses, so that is not an option for me.

Thanks in advance,
Ken L.

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set.  Both your original procedure and your new procedure could then use the function.  The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY.  In this way the core query code would not be duplicated.  Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/

create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/

SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;


Can I generate a list of all combinations between two columns table 1
|id|name  |
------------
|1 |Bob   |
|2 |Frank |
|3 |Paula |

Table 2
|id|item|
---------
|1 |a   |
|2 |b   |
|3 |c   |

i need to write a query to show me all the combinations of the two tables as below:

result
|name|item|
-----------
|bob |a   |
|bob |b   |
|bob |c   |
etc

I am currently using mysql any help would be greatly appreciated

This is called a cartesian product or a CROSS JOIN:

SELECT
    a.name,  b.item
FROM
    table1 AS a
  CROSS JOIN
    table2 AS b;


Optimistic concurrency check failure when updating linked server table with timestamp column I’m trying to update a table in a database on a linked server using code like this:

declare @FirstName nvarchar(30);
declare @LastName nvarchar(50);
declare @AccountNumber nvarchar(30);

set @FirstName = N'John';
set @LastName = N'Smith';
set @AccountNumber = N'ABC001';

update RemoteServer.MyDB.dbo.Customer
set    FirstName = @FirstName,
       LastName = @LastName
where  AccountNumber = @AccountNumber;

The remote server is also a SQL Server instance and is defined thus:

EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServer'
        , @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is
         changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RemoteServer'
        ,@useself=N'False'
        ,@locallogin=NULL
        ,@rmtuser=N'sa'
        ,@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

The Customer table is defined as

CREATE TABLE [dbo].[Customer](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [AccountNumber] [nvarchar](20) NOT NULL,
    [FirstName] [nvarchar](30) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [DBTimeStamp] [timestamp] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY NONCLUSTERED
(
    [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] TEXTIMAGE_ON [PRIMARY]

and has an index on the timestamp column

CREATE UNIQUE CLUSTERED INDEX [IX_Customer_TimeStamp] ON [dbo].[Customer]
(
    [DBTimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
        , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF
        , ONLINE = OFF, ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

When I run the query I get this error message:

The OLE DB provider "SQLNCLI11" for linked server "RemoteServer" could not UPDATE
         table "[RemoteServer].[MyDB].[dbo].[Customer]".
The rowset was using optimistic concurrency and the value of a column has been
         changed after the containing row was last fetched or resynchronized.

If I remove the index on the timestamp column, the query works as I’d expect.

How can I force the linked server query to use pessimistic rather than optimistic concurrency? I’ve already tried adding the tablockx query hint, and also loading the row in a select before running the update and using tablockx and holdlock. Wrapping the whole thing in a regular or distributed transaction also makes no difference.

Do the update remotely:

exec RemoteServer.MyDB.dbo.sp_executesql N'UPDATE Customer
    set    FirstName = @FirstName,
       LastName = @LastName
     where  AccountNumber = @AccountNumber',
     N'@FirstName varchar(...), @LastName varchar(...), @AccountNumber <type>',
     @FirstName, @LastName, @AccountNumber;

Must enable RPC and RPC Out on the linked server.


what is difference between uniform extents and mixed extents in Database file storage structure? I just went to some resources saying:

uniform extents are owned by single object and mixed extents can be shared by maximum 8 objects.

But I'm unable to grasp their insight meaning, like what is the benefit of this structure? basically, I am new in this field, so if you could provide some intuitive examples and applications, it would be highly appreciated.

There is no difference between what is stored on a page, between uniform and shared extents. It is as simple as that the first 8 pages for an allocation (a table, index, ...) comes from shared extents.

Back in the mid-90s when the "new" architecture was done, MS decided that they didn't want to waste a whopping 64 kB (8 pages) for each allocation. Back then we didn't have partitioning, and if we also ignore LOB data, we can equate allocation to "each index and also the actual data". I.e., imagine a table with 4 indexes (say it is a heap, but if that doesn't mean anything to you, just ignore the nuances). With uniform extents, each of these 5 allocations (one for the data and one each for the index) would use as a minimum 64 kB each. I.e., at least 5 * 64 kB = 320 kB, even if you only have one row.

So, they decided that the first 8 pages comes from mixed extents. Now, each of these 5 allocations only use 1 page (instead of 1 extent), i.e., 40 kB instead of 320 kB for this example.

The years fly by, and as of SQL Server 2016, MS decided that saving these measly kB doesn't buy us that much, so nowadays SQL Server allocate uniform extents from the very start. I.e., no more "first 8 pages comes from shared extents". You can revert to the old behavior using a database option if you want.

Shared extents still exists, though. For instance the IAM pages still comes from shared extents.


When creating a view how do I separate one column into two depending on a foreign key value? I'm supposed to create a view that joins the Customer table with the Address table and Address type table. Call it Customer_View.  Have two sections for the Address; Physical and Mailing. Make sure that your column headings reflect the two sections. If they are both you can just put the details under the Physical.

These are my tables with columns

Customers
Columns:
Customer_Id
Last_Name
First_Name
Middle_Name
Phone
Email

Address
Columns:
Address_Id
Customer_Id
Address_Type_Id
Address_1
Address_2
City
State
Zip
Country

address_type
Columns:
Address_Type_Id
Description

I know how to join the tables but I don't know how to make it so the address column shows as two columns, one for physical and one for mailing, depending on if the address_type_id value is 1 & 3 or 2... does that make sense?

This is the code I have:

    CREATE OR REPLACE VIEW Customer_View AS
SELECT  c.customer_id, CONCAT(First_Name, ' ', Last_Name) AS 'Full_Name',
        Address_1, Address_2, Phone, Email, City, State, Zip, Country,
FROM address AS a
    JOIN customer AS c
        ON a.customer_id = c.customer_id
    JOIN address_type AS aty
        ON a.address_type_id = atp.address_type_id

you need to use CASE construction in the query SELECT list:

SELECT
   CASE
      WHEN aty.address_type_id IN (1,3) THEN a.Address1 ELSE NULL END as mail_address,
   CASE
      WHEN aty.address_type_id =2 THEN a.Address1 ELSE NULL END as physical_address
....
FROM ...


Alternative methods for client data integration (Azure SQL Database) I have an application that ingests data from clients on a daily/weekly basis (two different data sets, one daily and the other weekly) into a SQL Azure Database. The clients' data source depends on what software they use, so can vary from client to client.
I currently have two methods of integration, depending on the client:

Using Azure Data Factory and Self-Hosted Integration Runtime. In this method, the client is required to provide (within their network) a VM in which I set up the Integration Runtime, and a SQL Server database with just two tables where they dump the two datasets as required. In ADF, I create pipelines to pull the data directly from their SQL Server into my Azure SQL Database, then run necessary import procedures.
Using Azure Data Factory and BLOB Storage. In this method, I provide the client with a set of Powershell scripts to be run on a schedule (Windows task scheduler) that help them to copy their exported files (.CSV) to our BLOB storage. Then, the ADF pipelines copy from the BLOB storage to the Azure SQL Db, then run the necessary import procedures.

The first method is much simpler, but in terms of infrastructure at the client end, it seems like a bit of overkill to set up a mostly blank Windows VM and a database with just a couple of data dump tables. Obviously, this can be costly if the client themselves are cloud-hosted - firing up a new VM is not cheap, so could make them think twice about using our product.

The second method requires me to set up a storage container for each client, which I feel could make administration difficult as we scale up. Also, providing scripts to run with Windows Task Scheduler doesn't feel overly elegant.

Does anybody have any alternative solutions to this scenario? Or am I on the right track?

Any insights would be greatly appreciated. Thanks.


Connect to Oracle DB 9i using SSH TUNNEL via putty I am trying to connect to an Oracle database behind NAT. Therefore I create an ssh tunnel using putty.

This is the network  information:

global IP example: 122.54.34.12
LAN IP example: 192.168.3.103
Oracle database port : 1521

and this is a snapshot of the putty ssh tunnel settings

After I have opened an ssh session  to the global IP with putty,
I try to connect to the Oracle database with SQL console through the ssh tunnel  but get the following error message:

sql > connect username/password@127.0.0.1/service-name
ERROR:
ORA-12170:TNS Connect timeout occurred

If the global IP is the NAT running the ssh server and the LAN IP is the one with the Oracle server on it, then you need to use the LAN IP as the destination in the tunnel.


Script to query multiple instances SQL Server - What is the best method to run the same query over multiple databases on multiple instances? for example we have many application databases, with the same schema that need to have information aggregated.

I currently use a script that relies on linked servers already being setup and a table that identifies which instance and database to loop through.

        /* declare some cursor variables and the dynamic sql string variable */
    DECLARE @currentDB          nvarchar(64)
    DECLARE @connectionstring   nvarchar(256)
    DECLARE @sqlstring          nvarchar(max)

CREATE TABLE #TEMP1
                (
                     currentDB      varchar(32)
                    ,field1         varchar(128)
                    ,field2         smallint
                    ,field3         datetime
                )

/* Build and open the cursor */
DECLARE connectioncursor CURSOR FAST_FORWARD
FOR
    SELECT  currentDB, connectionstring
    FROM    [admin].dbo.DatabaseList
    WHERE   dbtype = 'PROD'
OPEN    connectioncursor
FETCH NEXT FROM connectioncursor INTO @currentDB, @connectionstring

/* Start the loop through db list */
WHILE @@FETCH_STATUS = 0
    BEGIN
        /* Build and set the sql string */
        SET @sqlstring =
            '
                select
                         ''' + @currentDB + '''
                        ,field1, field2, field3
                        from ' + @connectionstring + '.dbo.table1
            '

        /* Insert the results from the iteration and fetch the next db */
        INSERT INTO #TEMP1 exec sp_executesql @sqlstring
        FETCH NEXT FROM connectioncursor INTO @currentDB, @connectionstring
    END

/* Kill the cursor */
CLOSE connectioncursor
DEALLOCATE connectioncursor

select * from #TEMP1  -- some output

Be gentle I'm a TSQL newbie!

There's lots of options.

PowerShell
SSMS's Multi Server Query Functionality
Linked Servers
Batch File with a loop and sqlcmd variables


How to join a table with a table valued function? I have a user defined function:

create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
  -- blah blah
end

Now I want to join this on another table, like so:

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f
join ut_FooFunc(f.ID, 1) u -- doesn't work
where f.SomeCriterion = 1

In other words, for all Foo records where SomeCriterion is 1, I want to see the Foo ID and Desc, alongside the values of Field1 and Field2 that are returned from ut_FooFunc for an input of Foo.ID.

What's the syntax to do this?

You need CROSS APPLY not join.

The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table.

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f
Cross apply ut_FooFunc(f.ID, 1) u
where f.SomeCriterion = ...


Restrict access for Windows administrator As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows administrator rights?

No.

If a user is a Windows Administrator of a box, assume that they own everything on the box (including SQL Server). With Windows Administrator rights it is trivial to bypass any targeted protection you apply (such as a logon trigger that identifies their user name), by impersonating someone else (including NT AUTHORITY\SYSTEM, which gets de facto admin rights on all local SQL Server instances). Auditing won't help much either, because they can easily turn that off, but you should have it just in case.

If you don't trust someone, don't give them Windows Administrator rights, period.


MYSQL Including Missing Values Using the Previous Most Recent Record Given a table containing a date for every day over a period

CREATE TABLE `tbl_calendar` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_calendar` (`date`)
    VALUES
        ('2016-12-10'),
        ('2016-12-09'),
        ('2016-12-08'),
        ('2016-12-07'),
        ('2016-12-06'),
        ('2016-12-05'),
        ('2016-12-04'),
        ('2016-12-03'),
        ('2016-12-02'),
        ('2016-12-01')
 ;

And a table containing values, of different types, with values missing for random days where they have not been populated.

CREATE TABLE `tbl_values` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL DEFAULT '0',
  `date` date DEFAULT NULL,
  `value` double(15,2) DEFAULT '0.00',
   PRIMARY KEY (`value_id`),
   KEY `type_id_date` (`type_id`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_values` (`type_id`, `date`, `value`)
VALUES
    (100, '2016-12-02', 1),
    (100, '2016-12-04', 2),
    (100, '2016-12-06', 3),
    (100, '2016-12-08', 4),
    (100, '2016-12-10', 5)
;

How can the values for the missing days be returned in a SELECT, using the most recent previous record for that type? here is what I have so far.

SELECT
    v1.type,
    c.date,
    v1.value

FROM
   tbl_calendar c

   LEFT JOIN tbl_values v1 ON (
            v1.type_id = 100
        AND v1.date <= c.date
   )

   LEFT JOIN tbl_values v2 ON (
            v2.type_id = 100
        AND v2.date < c.date
        AND v2.date > o1.date
   )

WHERE
       v1.date = c.date
    OR v2.date IS NULL

The problem with this query is that it returns the previous most recent records value when there is a value for that date, along with a record with the correct value.

Expected Output

a_vlad answer is correct in results, however poor on performance, which is expected.

SELECT
    t1.date,
    (SELECT v1.type_id FROM tbl_values v1 where v1.date <= t1.date ORDER BY v1.date desc limit 1) as `type`,
    (SELECT v1.`value` FROM tbl_values v1 where v1.date <= t1.date     ORDER BY v1.date desc limit 1) as `value`

FROM tbl_calendar t1
HAVING `type` IS NOT NULL

The final solution that I used was to use a_vlad query to create a summary table. However, it turned out not to increase performance of the system (as previously the missing values were filled in in a PHP loop, which worked out to be as fast)

If I understand the question correctly you want the lag value where there is no direct value.

Permit me a little OCD.  Steer well clear of any table or field names that are reserved words in any of the ANSI or ODBC standards or MySQL reserved words.
I would ditch the tbl_ prefix as well.

I've renamed your date field to Perioddate

Try this

SELECT  v2.type_id,DT.PeriodDate, V2.my_value
FROM (
    SELECT C.PeriodDate,MAX(V1.PeriodDate) AS PrecedingDate
    FROM tbl_calendar AS C
        LEFT JOIN tbl_values AS V1
        ON C.PeriodDate>=V1.PeriodDate
        AND V1.type_id = 100
    GROUP BY C.PeriodDate
) AS DT
    INNER JOIN  tbl_values AS V2
    ON DT.PrecedingDate = V2.PeriodDate
    AND V2.type_id = 100

The way it works is that the derived table builds up a list of dates and the highest date in the values table that is either less than or the same as the entry in the calendar table.  If there is no value the same then the highest date will be the preceding date.

On the subject of indexes make sure that your most selective column comes first i.e. the PeriodDate column, followed by your type_id.

What does the auto_increment column give you apart from a surrogate key?
If PeriodDate and type_id should be unique then make this your primary key.

I don't know if the query optimiser in MySQL does anything special with unique keys but it certainly does in other DB platforms.


data driven subscriptions in SSRS - pulling a password value from the database If I create a data driven subscription in SSRS and use the query to return some value for the username and password, how "safe" is this password?  Let me explain.

I am looking to set up a data source on the SSRS Web Portal that uses Credentials stored securely in the report server and Use as Windows credentials when connecting to the data source.  This data source, in turn, would be used in the data driven subscription.  The user setting up the data driven subscription would not have access to the data source's credential password.

I would then like to permit the user to pull a username and password using this shared data source and then use in their subscription as the user/pass to save the file to a shared file area... without the user knowing what the user/pass is.

Make sense?  If so, is this a risky thing to do, security-wise?

(Ultimately I am thinking about using this as a way to consolidate the password for shared file area subscriptions into one place.)

Thanks!

Addendum 3/26 - I like to think the lack of feedback means this is a fantastic idea, but I'd rather get some validation that I'm not treading in dangerous territory here.  So let's try a rephrase:

Is there any way a user can view the raw results of the query associated with a data driven subscription?

I just thought of an issue.  A user could take the column that contains the password and use it as the subject of an email.  Silly me!  I am not sure why I didn't think of this before.

Oh, well - documented here for others' future reference!


MongoDB as a log storage. Choosing shard key I'm designing a log storage system based on MongoDB.
I want to shard a log collection to increase ingestion and capacity (distribute writes to several machines) while allow fast search.
I should be able to increase ingestion by adding more nodes to the cluster.

My collection has following fields:

Subsystem - string, name of the application. E.g: "SystemA", "SystemB". ~ 100 unique values.

Tenant - string, the name of the deployment. It's used to separate logs from different application deployments / environments. E.g: "South TEST", "North DEV", "South PROD", "North PROD". ~ 20 unique values.

Date - timestamp.

User - string.

SessionId - guid, logically groups several related log records.

Data - BLOB, contains zipped data. Average size = 2Kb, maximum = 8Mb.

Context - array of key/value pairs. Both key and value are strings. It's used to store additional metadata associated with event.

The search could be performed by any combination of fields Subsystem, Date, User and Context.
Tenant almost always will be specified.

The question is - what shard key and sharding strategy will be better in that case?

My suggestions:

The simplest case is to shard by Tenant, but it will cause highly uneven data distribution, because PROD environments generates much more logs than DEV.

"Tenant + Subsystem" seems to be better but still there are subsystems that generates much more logs than other subsystems.
And also subsystem is not mandatory - user can omit subsystem during search and search query will be broadcasted.

"SessionId" will cause even data distribution but search requests will be broadcasted to all nodes.

For even write distribution, the SessionId looks like a much better idea (Or a hashed index on other fileds). Proper indexing should solve queries then.

The other two options have very low cardinality (and therefore very large chunks).


Why Does Microsoft SQL Server Management Studio Keep Creating Table Aliases? When I create or edit queries using design view, SSMS keeps creating aliases (TableName_1) for some tables for no apparent reason. More annoying is it will also do this if I open existing queries in design view. In other words, it changes my SQL code! Even simple queries have this issue, for example:

SELECT
  dbo.tblCalendar.id,
  dbo.tblCalendar.title,
  dbo.luCalendarType.typeName
FROM
  dbo.luCalendarType RIGHT OUTER JOIN
  dbo.tblCalendar ON dbo.luCalendarType.id = dbo.tblCalendar.type

will be become:

SELECT
  dbo.tblCalendar.id,
  dbo.tblCalendar.title,
  luCalendarType_1.typeName
FROM
  dbo.luCalendarType AS luCalendarType_1 RIGHT OUTER JOIN
  dbo.tblCalendar ON luCalendarType_1.id = dbo.tblCalendar.type

Is there any way to stop SSMS altering my SQL code?

The best option is to stop using the SSMS View Designer.

By sticking to using a plain old query window to create or edit views, you will have full control over the queries as well as regular practice in Transact-SQL.

Besides, the SSMS View Designer is known to be buggy (see view corrupted when viewed using design feature) or produce peculiar syntax1 (see View designer strange join syntax) or encourage syntax that has become obsolete and is now misleading (see After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column).

So, in short, you will just be better off, in the long run, without the View Designer.

1Not necessarily an issue in itself, but syntax that is not very familiar to you does not lend itself well to maintainability of the query.


Best relationship database design to manage types for different entities Suppose we have to manage phone types for: Clients, Employees and  Suppliers.
Each of these can have multiple phone numbers, so for better management I would like to add phone type.
Each entity can have different phone types like:

Clients: Home, work , mobile
Employees: Home, mobile
Suppliers: Stock, Director, and so on.

Problem: I want to be able to show for clients only the possible client phone types, for suppliers only possible supplier phone types and so on...

Question:
What will be the best relationship database design to manage this kind of data?

Case 1 : To make one table PhoneType and then ClientsPossiblePhoneTypes, SupplierPossiblePhoneTypes , EmployeesPossiblePhoneTypes, or

Case2: to make table ClientPhoneType, EmployeePhonetype, SupplierPhoneType.

If your various types of individuals and organizations (employees, clients, suppliers) each have multiple phone numbers of various types, then you want to keep the phone numbers in a child table.

This child table should be an intersection between the individual/organization and a phone type table.

Since you have different allowable phone types for each type of legal entity, you can create a table for each of these lists of allowable phone types (your Case 1).  The only issue with this is that you may have duplication of data, for example having a "mobile" record in multiple allowable types tables.  There is a way to have distinct lists of allowable phone types while at the same time rationalizing these lists for management purposes.

Consider this ERD:

There is one master lists of phone types (PHONE_TYPE) which includes flags for each possible legal entity type, indicating whether a particular phone type is allowable for each type of legal entity.  The table might look like this:

create table PHONE_TYPE
( phone_type_id int primary key
, description varchar(10)
, is_client_type bit
, is_employee_type bit
, is_supplier_type bit
);

From this master list of phone types, you can create distinct views for each legal entity phone type list (i.e. CLIENT_PHONE_TYPE, EMPLOYEE_PHONE_TYPE, SUPPLIER_PHONE_TYPE).  One of these views might look like this:

create view CLIENT_PHONE_TYPE
as
select * from PHONE_TYPE
where is_client_type = 1;

This lets you maintain the phone types master list in one place while enforcing the list of allowable phone types for each of the different types of legal entity that you support.


Golden Gate realtime replication basic question I have a task to replicate unidirectional real time data from source database A to target database B. I do not need all the table from A, only few tables and few columns inside those tables. Say 10 tables and 30 columns from A go into 6 tables on database B.

Is this doable in Golden Gate (GG)? Oracle 11g source and 12c target DB version in same OS version.

In addition to the above, when GG is replicating can it insert a sequence number on a column/table in database B?

Any links you can point, as how to do the above is very much appreciated?

Is this doable in GoldenGate(GG)?

The Map or Table parameter can be used to select Table, depending on whether to apply in Extract or in Replicat.

The Cols and ColsEXCEPT attributes of the Table parameter can be used for Columns selection.

I believe that the Oracle's official documentation is good link to create the solution.

Here are some.

Selecting Table

Selecting Columns

SQLEXEC:Overview


Foreign key default value I have a master table and another table with foreign key. I can easily add data to master table. But whenever I want to add data to the connected table, I get a message saying Foreign key doesn't have a default value. When I make the foreign key auto increment in MySQL then I can enter data, but it is not the way it should work. May be it can be acceptable for one to one relationship.
Shouldn't the connected table get the value from Master table? or is there sth else I'm missing here?

I'd really appreciate any suggestions.

Have a nice day.

I'm not sure what you are saying here. Assume the following:

CREATE TABLE PARENT
( a int not null primary key );

CREATE TABLE CHILD
( b int not null primary key
, a int not null
,    constraint fk foreign key (a)
         references PARENT (a) );

insert into PARENT (a) values (1),(2);

-- What should the default value of a be?
insert into CHILD (b) values (1);

A foreign key is a rule that makes sure that you use a valid value for the column(s) involved. If you want a default value for a in CHILD you can add another constraint (I'm not sure why default values are considered to be constraints):

CREATE TABLE CHILD
( b int not null primary key
, a int DEFAULT 1 not null
,    constraint fk foreign key (a)
         references PARENT (a) );

Without that it is impossible for the DBMS to know what you mean when you don't provide a value for an attribute.

EDIT: Add info on how to retrieve last generated value

If you want to inspect what was generated in the PARENT table, and use that value for the CHILD table, you can use a function LAST_INSERT_ID():

CREATE TABLE PARENT
( a int not null AUTO_INCREMENT primary key
, c int not null );

CREATE TABLE CHILD
( b int not null primary key
, a int DEFAULT 1 not null
,    constraint fk foreign key (a)
         references PARENT (a) );

insert into PARENT (c) values (1);
insert into CHILD (b,a) select 1, LAST_INSERT_ID();

or

insert into CHILD (b,a) values (2, LAST_INSERT_ID());


SQL Server - Copying a key from one table to another In a trigger, I have created a table using SELECT * INTO. I have done that in a specific way, in order to create the table without also creating the identity column that exists in it (let's name this column ID):

SELECT * INTO #ins
FROM (SELECT * FROM inserted
      UNION ALL
      SELECT * FROM inserted WHERE 1 = 0) AS a

The problem is that the new table (#ins) does contain the identity column (which I want it to) but it has not marked is as Primary Key (which is how it was in the original table, and I would like to keep it that way).

I would like to know the easier of the following two:

How could I "read" the primary key from the original table and alter the new one to also add it there? I would like this to be generic, so assume the name of the ID column will be unknown.

or (if easier)

Is there another way to create table #ins from the original, retaining the column "ID" and its definition as primary key, but without it being an IDENTITY one?

EDIT: OK, as stated in a comment below, the task i was trying to achieve can be done in another way, using the primary key from the original table. This makes the question above rather pointless.

For permanent reused code like this, you really should be explicitly defining your temporary table definitions.  If you are looking for an easy way to do that, please see below.  I'll also include information on your actual question.

Easy way to generate table definition:

locate the object in object explorer
right click on it and select "script --> create table --> to SSMS window"

This should generate a new SSMS window with the table you selected scripted there.  Depending on your options, you will have indexes as well at the bottom.

To make it a temporary table, just change the name of the table.

Remove IDENTITY

Locate the identity column and remove the identity definition more than likely "IDENTITY(1,1)".

Example:

CREATE TABLE #INS
(
MyIdentityID INT NOT NULL PRIMARY KEY
, MyValue VARCHAR(100) NOT NULL
)

Please note, this is a simple and straightforward method that directly answers your question.  Any use of temporary tables in triggers makes me suspicious and I would like to know the full business reason behind it; it's possible that a better solution exists.


SQL Server 2014 Reporting Services issues I have set up SSRS on MS SQL 2014 Dev Edition using the ReportServer built in account as the Service Account.

My machine name is: POUYASAGER
My local login is pyous
I have given the ReportServer account full permissions to:

C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services

I have checked that SecureConnectionLevel is set to 0 in the rsreportserver.config file.

I set up ReportServer as the virtual directory so that the Report Server Web Service URLs would be: http://POUYASAGER:80/ReportServer

When I click on the link in the Reporting Services Configuration Manager which is the same as above, I get:

The permissions granted to user 'POUYASAGER\pyous' are insufficient for performing this operation. (rsAccessDenied)

When I navigate to: http://127.0.0.1:80/ReportServer, an authentication pop-up asks for a username and password.

When I navigate to the reports manager using: http://pouyasager:80/Reports, I get:
Error
Unable to connect to the remote server

How can I get SSRS working properly?

EDIT: I added "localhost" to the host headers and still got the same error.
EDIT: Using Chrome with Admin permissions, I navigate to http://localhost:80/ReportServer with the same results.

Adding the account you are logging in on your OS will allow for you to connect to SSRS. You are provided with a 403 error message directly related to an access denied. The type of access being denied here is at the database level, mainly for the Reportserver$ database. Your OS login is unable to personify NT SERVICE since it does not have a login on the server and guest account being probably disabled.

Additionally, verify the following:
'C:\WINDOWS\Microsoft.NET\Framework\V2.0.050727\Temporary ASP.NET Files'. (or where ever your .NET framework is installed on your machine)

Right click on the "Temporary ASP.NET Files" and select properties->secutity.

See if the "NETWORK SERVICE" is already part of the "User/Group" section. If not add it by typing "Network Service".
If it's already there then grant the write permissions.


Trigger creation for Sybase SQLAnywhere 16 I've been having the hardest time trying to create a trigger for a Sybase SAP SQLAnywhere 16 database following these docs.
Currently it's -131 syntax erroring on line 15/16 (the update statement), but I don't see any error in the syntax. What am I doing wrong here?
create trigger "DBA"."WKM_autoFillCL143" after insert order 2 on "DBA"."case_checklist"
when ((select top 1 "inserted"."code" from "inserted") in( '143' ) )
  begin
    declare @parentRef integer;
    declare @desc varchar(255);
    declare @desc1 varchar(255);
    declare @checkID integer;
    set @parentRef = (select top 1 "parent_ref" from "inserted");
    if (@parentRef <> '0')
      then
        set @desc = (select "description" from "case_checklist" where "checklist_id" = @parentRef);
        if (@desc is not null)
          then
            set @checkID = (select top 1 "checklist_id" from "inserted");
            update "WKM_RecordChecklistMapping" set "c143" = @checkID where "c142" = @parentRef;
            declare @tabid integer;
            set @tabid = (select top 1 "tab_id" from "WKM_recordChecklistMapping" where "c142" = @parentRef);
            set @tabid = (select top 1 "tab_id" from "user_tab2_data" where "tab_id" = @tabid);
            if (@tabid is not null)
              then
                declare @recProvider varchar(255),@recsRequested varchar(255),@dateFrom "datetime",@dateTo "datetime"
                set @recProvider = (select top 1 "Provider_Name" from "user_tab2_data" where "tab_id" = @tabid);
                set @recsRequested = (select top 1 "Records_Requested" from "user_tab2_data" where "tab_id" = @tabid);
                set @dateFrom = (select top 1 "For_Dates_From" from "user_tab2_data" where "tab_id" = @tabid);
                set @dateTo = (select top 1 "Through" from "user_tab2_data" where "tab_id" = @tabid);
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+"coalesce"(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+"coalesce"(convert(varchar(255),@dateTo,1),'00/00/00');
                set @checkID = (select top 1 "checklist_id" from "inserted");
                update "case_checklist" set "description" = @desc1,"staff_assigned" = 'ZKS',"due_date" = ("today"()+7) where "checklist_id" = @checkID
              end if;
          end if;
      end if;
  end;

I think the problem you are running into is caused by getting a mixture of T-SQL (ASE) syntax and SQLA (Watcom) syntax. You can use T-SQL triggers in SQLA - but with quite a lot of limitations. It better to do a re-write if you can.
This complies, whether it works, I have no idea! (The changes are all in the DECLARE statements. One per variable, double quotes removed from the timestamp datatype (as per markp-fuso's comment). If you really need local variables further down the code, you have to declare them at the beginning of a new BEGIN / END block)
create trigger DBA.WKM_autoFillCL143 after insert order 2 on DBA.Case_Checklist
  when ((select top 1 inserted.code from inserted) in( '143' ) )
  begin
    declare @parentRef integer;
    declare @desc varchar(255);
    declare @desc1 varchar(255);
    declare @checkID integer;
    declare @tabid integer;
    declare @recProvider varchar(255);
    declare @recsRequested varchar(255);
    declare @dateFrom datetime;
    declare @dateTo datetime;
    set @parentRef = (select top 1 parent_ref from inserted);
    if (@parentRef <> '0')
      then
        set @desc = (select description from case_checklist where checklist_id = @parentRef);
        if (@desc is not null)
          then
            set @checkID = (select top 1 checklist_id from inserted);
            update WKM_RecordChecklistMapping set c143 = @checkID where c142 = @parentRef;
            set @tabid = (select top 1 tab_id from WKM_recordChecklistMapping where c142 = @parentRef);
            set @tabid = (select top 1 tab_id from user_tab2_data where tab_id = @tabid);
            if (@tabid is not null)
              then
                set @recProvider = (select top 1 Provider_Name from user_tab2_data where tab_id = @tabid);
                set @recsRequested = (select top 1 Records_Requested from user_tab2_data where tab_id = @tabid);
                set @dateFrom = (select top 1 For_Dates_From from user_tab2_data where tab_id = @tabid);
                set @dateTo = (select top 1 Through from user_tab2_data where tab_id = @tabid);
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+coalesce(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+coalesce(convert(varchar(255),@dateTo,1),'00/00/00');
                set @checkID = (select top 1 checklist_id from inserted);
                update case_checklist set description = @desc1,staff_assigned = 'ZKS',due_date = (today()+7) where checklist_id = @checkID
              end if;
          end if;
      end if;
  end;


PL/pgSQL issues when function used twice (caching problem ?) I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem.

I have this function:

CREATE FUNCTION sp_connect(mail character varying, passwd character varying, role character varying)
  RETURNS json LANGUAGE plpgsql STABLE AS
$$
DECLARE
    user_info record;
BEGIN
  IF role = 'Role1' THEN
    SELECT u.id, r.name INTO user_info
    FROM users u
    INNER JOIN users_roles ur ON ur.user_id = u.id
    INNER JOIN roles r ON ur.role_id = r.id
    WHERE u.email = mail
      AND u.password = encode(digest(CONCAT(passwd, u.password_salt), 'sha512'), 'hex')
      AND r.name = 'Role1';
  ELSIF role = 'Role2' THEN
    SELECT h.id, 'Role1' AS name INTO user_info
    FROM history h
    WHERE h.email = mail
      AND h.password = encode(digest(CONCAT(passwd, h.password_salt), 'sha512'), 'hex');
  ELSE
    RAISE 'USER_NOT_FOUND';
  END IF;

  IF NOT FOUND THEN
      RAISE 'USER_NOT_FOUND';
  ELSE
      RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
  END IF;
END;
$$;

The problem I'm facing is when I use this function to log in with a Role1-user, then when I use it with a Role2-user, I get this error message:

type of parameter 7 (character varying) does not match that when preparing the plan (unknown)

Which is... well, I just don't understand where does it come from. If you wipe the database and change the login order (i.e. Role2 then Role1), this time, Role1 gets the error.

Strange issue, strange solutions... If I just use ALTER FUNCTION sp_connect but without modify anything inside the function, then magically, the two roles can login without any problem. I also tried this solution:

  IF NOT FOUND THEN
      RAISE 'USER_NOT_FOUND';
  ELSE
      IF role = 'Seeker'
      THEN
          RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
      ELSE
          RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
  END IF;

And by adding an IF and ELSE that is absolutely useless and use the same RETURN-clause, this does not trigger any error.

I know DBA StackExchange is not for developers but this kind of problem seems to be more like a caching problem or whatever. Can somebody can tell me if I am doing something wrong with PostgreSQL functions? Or where I may get help with this weird problem?

Explanation:

You declare user_info as record. The manual:

  Record variables are similar to row-type variables, but they have no
  predefined structure. They take on the actual row structure of the row
  they are assigned during a SELECT or FOR command. The substructure of
  a record variable can change each time it is assigned to.

Bold emphasis mine.

You assign the record user_info and then derive a row type (actually called row in your code) from it in the RETURN statement. This is all fine and dandy, until you assign columns of different data types to the record in the next call within the same session. This is incompatible with the cached query plan of the prepared statement and raises an exception.

PL/pgSQL treats all SQL statements in the function body as prepared statements. The query plan for prepared statements is cached for the duration of the session unless any involved objects are changed (including the function itself), which deallocates all depending prepared statements. This explains why the next invocation after ALTER FUNCTION always worked. More explanation:

PostgreSQL Stored Procedure Performance

Solution

There are various ways around this. You found some yourself already. Just avoid feeding parameters of different data type to the same (prepared) statement

The simple solution would be to cast to the same data type. You didn't provide table definitions, I assume users.id and history.id are integer and match just fine. Judging from the error message I assume roles.name is varchar, so I cast the string literal 'Role1' to varchar as well and everything should work. (You might actually mean 'Role2', but that's orthogonal to the problem.)

An untyped string literal is coerced to a matching data type in some types of SQL statements where a data type can be derived from the context. But that's not the case here. Without explicit cast the string literal is type unknown, which is not the same as varchar (or text). This also shows in your error message.

CREATE FUNCTION sp_connect(mail varchar, passwd varchar, role varchar)
  RETURNS json AS
$func$
DECLARE
   user_info record;
BEGIN
  IF role = 'Role1' THEN
    SELECT u.id, r.name INTO user_info
    FROM   users u
    JOIN   users_roles ur ON ur.user_id = u.id
    JOIN   roles r ON ur.role_id = r.id
    WHERE  u.email = mail
    AND    u.password = encode(digest(CONCAT(passwd, u.password_salt), 'sha512'), 'hex')
    AND    r.name = 'Role1';

    RAISE NOTICE 'Role1: user_info.big_id: %; user_info.name: %'
                , pg_typeof(user_info.big_id), pg_typeof(user_info.name);  -- see data types
  ELSIF role = 'Role2' THEN
    SELECT h.id, 'Role1'::varchar AS name INTO user_info  -- Cast! And did you mean 'Role2'?
    FROM   history h
    WHERE  h.email = mail
    AND    h.password = encode(digest(CONCAT(passwd, h.password_salt), 'sha512'), 'hex');

    RAISE NOTICE 'Role2: %: user_info.big_id: %; user_info.name: %'
                , pg_typeof(user_info.big_id), pg_typeof(user_info.name);  -- see data types
  END IF;

  IF NOT FOUND THEN
      RAISE 'USER_NOT_FOUND';
  ELSE
      RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
  END IF;
END
$func$  LANGUAGE plpgsql STABLE;

The function can be STABLE, that's the correct volatility.

I also added RAISE NOTICE statements to show data types, which should help you debug. Note that the plan for the RAISE statement itself is cached as well, so a single RAISE after END IF would be subject to the same problem.


Mysql Select using Alias I Need to filter the query that have some conditional column as below:

 SELECT
     `c`.`fullname` AS `fullname`,
     IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`,
     `bla`.`loan_no` AS `Loan_No`,
     `bla`.`total_loan_amt` AS `Total_Loan_Amt`,
     `bla`.`monthly_installment` AS `Month_Inst_Amt`,
     `bla`.`disb_date` AS `disb_date` FROM (`customers` `c`
 JOIN `basic_loan_application` `bla`
    ON ((`bla`.`customer_uid` = `c`.`customer_uid`)))
 WHERE Ic LIKE '%800%' ORDER BY `bla`.`Basic_Loan_Application_Id` DESC LIMIT 0, 10

But I cannot Use the Ic Field  and return error as decribed below:

 Unknown column 'Ic' in 'where clause'

I want try to avoid nested select if possible.

Is there another solution for this problem?

In the where clause, trade the Ic alias with IFNULL(c.new_ic,c.old_ic).

 SELECT
     `c`.`fullname` AS `fullname`,
     IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`,
     `bla`.`loan_no` AS `Loan_No`,
     `bla`.`total_loan_amt` AS `Total_Loan_Amt`,
     `bla`.`monthly_installment` AS `Month_Inst_Amt`,
     `bla`.`disb_date` AS `disb_date` FROM (`customers` `c`
 JOIN `basic_loan_application` `bla`
    ON ((`bla`.`customer_uid` = `c`.`customer_uid`)))
 WHERE IFNULL(`c`.`new_ic`,`c`.`old_ic`) LIKE '%800%' ORDER BY `bla`.`Basic_Loan_Application_Id` DESC LIMIT 0, 1

This happen because the query execution order. In MySQL queries are parsed in this order:

1- FROM clause
2- WHERE clause
3- GROUP BY clause
4- HAVING clause
5- SELECT clause
6- ORDER BY clause

Which means that queries is not executed the way we write them. So, the Ic alias is not defined in the moment of WHERE clause is executed.


Find history of rollbacks on SQL Server 2012 Is there a way to find the history of any and all rollbacks that happened on a SQL Server database? Specifically, I would like to know the time and the user.
Thanks.

Do you mean ROLLBACK TRANSACTION? Not very easily. You could use DBCC LOG or fn_dblog() to find rollbacks that have happened in the active transaction log. Getting the same information from old log backups requires fn_dump_dblog().

SELECT
    l.name,
    r.*
FROM fn_dblog(NULL, NULL) r
    LEFT OUTER JOIN fn_dblog(NULL, NULL) b
        ON r.[Transaction ID] = b.[Transaction ID]
        AND b.Operation = 'LOP_BEGIN_XACT'
    LEFT OUTER JOIN master.sys.server_principals l
        ON b.[Transaction SID] = l.sid
WHERE r.Operation = 'LOP_ABORT_XACT'

A better option would be to configure either a trace or extended event session to record rollbacks, though this would only help you going forward.


How to find total used and remaining space in Azure SQL mi I queried sp_databases and the sum of space occupied by all db's was 380Gb. The total allocated space for the managed instance is 800Gb.

But, enquiring with the infra team found that we have only 40gb of space left.

What am I getting wrong here? How to check the total used and remaining space in a MI.?

sp_databases doesn't seem to be applies to Azure SQL Database (as per MS Official Documentation) which is the same case for sys.dm_os_volume_stats and sys.master_files.

However, you may want try with following query, this basically gives break-down of utilized and available space with all mounted drives that are hosting database related files.

SELECT  SERVERPROPERTY('MachineName') as HostName,
        volume_mount_point,
        max(total_bytes / 1048576.0) / 1024 as TotalSize_Gb,
        max(available_bytes / 1048576.0) / 1024 as Avalable_Size_Gb
FROM sys.master_files AS f
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
Group by volume_mount_point
go

select DB_NAME(database_id) as DBName,
        SUM (CASE WHEN type_desc = 'ROWS' THEN ((size * 8) / 1024.0)/1024 end) as Data_Size_GB,
        SUM (CASE WHEN type_desc = 'LOG' THEN ((size * 8) / 1024.0)/1024 end) as Log_Size_GB,
        SUM (CASE WHEN type_desc = 'ROWS' THEN ((size * 8) / 1024.0)/1024 end) +
        SUM (CASE WHEN type_desc = 'LOG' THEN ((size * 8) / 1024.0)/1024 end) as Total_Size_GB
from sys.master_files
group by database_id
go


Select Multiple Values from Same Column; one sql statement and split into separate columns This post derives from following -
Select Multiple Values from Same Column; one sql statment

Based on this query -

 SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id)
 FROM t
 WHERE meta_key  IN('first_name','last_name','street_add','city','state')
 GROUP BY user_id

It will give me a comma separated column, which is great and gives me the data, but is there a way to separate the fields into columns?

The way you are describing the expected result; I am concerned that what you are looking for won't get you what you want. Taking the example from the referenced post, you may actually want:

create view yourview1 as (
  select
    user_id,
    case when Item_Type = "first_name" then meta_value end as first_name,
    case when Item_Type = "last_name" then meta_value end as last_name,
    case when Item_Type = "street_add" then meta_value end as street_add,
    case when Item_Type = "city" then meta_value end as city,
    case when Item_Type = "state" then meta_value end as state
  from User_Items
);

create view yourview1_Pivot as (
  select
    user_id,
    MAX(first_name) as first_name,
    MAX(last_name) as last_name,
    MAX(street_add) as street_add,
    MAX(city) as city,
    MAX(state) as state
  from yourview1
  group by user_id
);

source


How to use postgresql service file in pgadmin4 My service file is working well in PgAdmin3 and in QGIS.
I'm trying to use the same service file in PgAdmin4. But no luck, I still have the same message:

  Unable to connect to server: definition of service "XXXX" not found

My service is located in:

/etc/postgresql-common/pg_service.conf

I'm on Ubuntu 18.04 and I used this webpage to install PgAdmin4: https://askubuntu.com/a/831263/363564

Try downloading the latest version of pgAdmin4, Many features are implemented now,

Specifically, the one you asked for https://redmine.postgresql.org/issues/3140


Setting up MySQL Cluster on Two Systems I am attempting to setup a MySQL Cluster.

I have the folllowing setup

one management node
two data nodes
another machine on which two more nodes reside

Below is the config.ini file:

[ndbd default]

# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2    # Number of replicas

DataMemory=80M    # How much memory to allocate for data storage

IndexMemory=18M   # How much memory to allocate for index storage

                  # For DataMemory and IndexMemory, we have used the
                  # default values. Since the "world" database takes up
                  # only about 500KB, this should be more than enough for
                  # this example Cluster setup.

DataDir=/var/lib/mysql-cluster

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

LogDestination=FILE:filename=my-cluster.log

[tcp default]

[ndb_mgmd]

# Management process options:
hostname=X.X.X.26         # Hostname or IP address of MGM node

datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

# Storage Nodes
[NDBD]

hostname=X.X.X.26   # IP address of storage-node-1

DataDir= /var/lib/mysql-cluster

[NDBD]

HostName=X.X.X.73         # IP address of storage-node-2

DataDir= /var/lib/mysql-cluster

[NDBD]
hostname=X.X.X.26      # IP address of storage-node-3
DataDir= /var/lib/mysql-cluster

[NDBD]
HostName=X.X.X.73         # IP address of storage-node-4
DataDir= /var/lib/mysql-cluster

# Setup node IDs for mySQL API-servers (clients of the cluster)

[mysqld]
# SQL node options:
hostname=X.X.X.73        # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

Below is the my.cnf file for both systems:

[mysqld]

ndbcluster # run NDB storage engine

ndb-connectstring=X.X.X.26 # location of management server

[mysql_cluster]

ndb-connectstring=X.X.X.26 # location of management server

When I try to start the cluster, it gives me following error:

Could not determine which NodeID to use for this node. Specify it with --ndb-nodeid=<nodeid> on command line

If clustering setup is done, there is no replication of data as it give me again another error: ERROR 1296 (HY000): Got error 4009 'Cluster Failure' from NDBCLUSTER

Can anyone help me determine the problem?

Your management configuration seems fine to me.  It sounds like ndb_mgmd didn't quite die or there is a problem with the contents in the /var/lib/mysql-cluster directory on the management server.

You might also want to make sure that ndb_mgmd isn't starting automatically, like through some startup script in /etc/init.d/.

To try and resolve the issue, I'd start with these steps first.

Since you are just getting started and don't have any real data yet.

Kill any left over ndb_mgmd processes with:  killall ndb_mgmd or by rebooting if killall doesn't help.
Completely delete everything within /var/lib/mysql-cluster: rm -Rf /var/lib/mysql-cluster/*
Start the cluster process again: ndb_mgmd -f /etc/config.ini --initial
Verify that ndb_mgmd is listening with netstat

$> netstat -tlpn

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 0.0.0.0:1186                0.0.0.0:*                   LISTEN      3251/ndb_mgmd

Connect to management node

$> ndb_mgm
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.9.26  (mysql-5.1.61 ndb-7.1.22)

Update

Here is a Quick Start guide from Oracle with all nodes, management, mysql and data running on one server.

http://downloads.mysql.com/tutorials/cluster/GetMySQLClusterRunning-LINUX.pdf


Finding row with Max value with inner join and sub-query I need to SUM the total values of each student using INNER JOIN and then find the row with maximum value.

I have used the below query to find the SUM of each student and sort in descending order SHOWING ALL the students including the one with the maximum value.

Select      S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE"
from        Student S
inner join  Attempt A
on          S.Student_ID = A.Student_ID
group by    S.Student_ID, S.Student_Name
order by    3 desc;

I need to ONLY show the student with the maximum value but it shows an empty table.

Select     S.Student_ID,
           S.Student_Name,
           MAX(A.Score) as "TOTAL SCORE"
from       Student S
inner join Attempt A
on         S.Student_ID = A.Student_ID
where      A.Score = (select   SUM(A.Score)
                      from     Student S)
                      group by S.Student_ID, S.Student_Name;`

Does this have to be done with MAX or is there another way to do it, if latter then how.

Not sure if I can explain simpler than this.

One way to accomplish what you're looking for is by using the ROWNUM pseudocolumn. Note that you need to define your ORDER BY in an inline view or by some other method to avoid a common trap that people fall into. Here's one way to do it:

SELECT *
FROM
(
    Select      S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE"
    from        Student S
    inner join  Attempt A
    on          S.Student_ID = A.Student_ID
    group by    S.Student_ID, S.Student_Name
    order by    TOTAL_SCORE desc
) t
WHERE ROWNUM = 1;

As of Oracle 12c you can use the extended ORDER BY syntax to return just the first row. Here's an example:

Select      S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE"
from        Student S
inner join  Attempt A
on          S.Student_ID = A.Student_ID
group by    S.Student_ID, S.Student_Name
order by    TOTAL_SCORE desc
FETCH FIRST 1 ROWS ONLY;


How to group records by a two-value column ignoring small gaps? I have a subquery with a column named code that only has 1 and 0 values and an ID column because I query a lot of codes for each ID. I want to group by said column (and by ID) and by ocurrence but want to ignore groups with less than 3 records, so that bigger groups get merged.

I use ROW_NUMBER() - ROW_NUMBER() over (partition ...) to generate a group number which I use to group records by, but sometimes the records get mixed up caused by a coincidence in the sequence of the row number and the partitioned row number. This is an example

code | row_number | row_number_partition_by_code | group_number
------------------------------------------------------------------
  0  |     1      |             1                |     0
  0  |     2      |             2                |     0
  1  |     3      |             1                |     2
  1  |     4      |             2                |     2
  0  |     5      |             3                |     2
  1  |     6      |             3                |     3
  0  |     7      |             4                |     3
  0  |     8      |             5                |     3
  0  |     9      |             6                |     3
  0  |     10     |             7                |     3
  1  |     11     |             4                |     7
  0  |     12     |             1                |     0

As you can see, some groups contain both codes!

After this, I do

GROUP BY group_number
HAVING COUNT(1) > 2

to eliminate small groups, and generate another group number to merge adjacent groups.

How can I group these values homogeneously?

More Details

All records are ordered by ID and by date (the later in descending order) in every ROW_NUMBER().

The final result I expect is the time in minutes of the latest code group for each ID, and merging groups that are separated by less than 3 minutes.

I'm more interested in the groups with the value 1, so if the latest code group is a 0 ( considering it is not a couple minutes long ) then ignore that ID completely.

Usually, there is one record per minute. Sometimes it can take hours without a record for any ID


Updating 3 million rows in 100million+ table I have a really big table containing over 100kk rows and I would like to update about 3kk rows.

Following count returns 3kk and takes about 300s to execute

select count(*) from A where x=.. and y=..

But the update statement is much slower...

update A set z = .. where x=.. and y=..

V$SESSION_LONGOPS says it might take ~10-15h to finish.

What are the steps I can take to improve the performance of the above update? CTAS probably doesn't make much sense in this case (3kk rows in 100kk rows table).

Let's say your statement is:

update A set z = 'HELLO WORLD' where x = 1 and y = 2;

1) This does extra work, because it updates column z to HELLO WORLD, even if it was HELLO WORLD already. Rewrite it as:

update A set z = 'HELLO WORLD' where x = 1 and y = 2 and (z != 'HELLO WORLD' or z is null);

2) If you have 2 columns indexed seperately, it is possible, but quite rare, that the database uses both indexes for the lookup. You can try creating 1 index having both columns, but I highly doubt this would be of any help, you would need a big, wide table with exceptionally good clustering for x and y columns to benefit from this for 3/100 million rows.

3) DML does not run in parallel by default. You can enable parallel execution as:

alter session force parallel dml parallel 8;

Note that this will not force parallel execution even if the syntax suggests that. It's like adding parallel 8 hint to your statements in your session.

4) If column z is indexed, than the UPDATE statement need to maintain the index(es) as well. Sometimes it is better to make the index(es) unusable, run the DML, then rebuild the index(es).

5) If the content of column z does not fit its original place (HI becomes HELLO WORLD), and your table blocks are full, you may experience row migration, that makes performance worse not only during the UPDATE, but for later queries as well.

You would be surprised how much better CTAS can be.

6) If column z is part of an enabled foreign key constraint, you can not simply update it to any value. The database needs to check the parent values to make sure you are updating to a valid value. Again, disable, run the DML, enable validate, may improve performance.

But this is just guesswork without the facts.
An SQL Monitoring output, or execution plan with runtime statistics could help.
If that is not enough, monitoring the wait events + parameters in v$session, or a raw SQL trace could help even more.


Deleting records in relation with other tables I need suggestions on deleting records in relation with other tables

For example i have a client in the clients table and this client has bills, payments, wire transfers etc... so this client id is in relation with other tables, it seems like i will never be able to delete this client because of past records, because if i delete this client other tables wont be able to inner join the clients table and all the records which has this client id will be vanished :)

I hope i was clear enough on my explanation,
My question is; what is the right way in a situation where i have to delete a client record?

IMHO the best way is: Do not delete the records.

As far as I understand your are talking about accounting and financial information. Usually this information cannot be deleted, at least for a minimum space of time, 5 years on my country. (More time for medical & health products)

If, for some reason, a customer becomes inactive, use a status field to mark him.

        status | description
        -------|-------------
          0    |   Active
          1    |   Blocked
          2    |   Deleted
          n    |   ....

However, if you must delete all this information. I'd suggest you to define and develop a function (stored procedure, udf, etc) that takes care of all required changes in your DB. May be saving historical information, just to know when, who and why this records has been deleted.


Partitioning a table vs moving entries to another table I don't understand how partitioning helps me more or less than using a separate table to store older records.

I need to maintain a list of stock entries along with historical records about the stock.

So here I have the option that if some stock is discontinued, I can move that stock to another table that will be accessed only at the time of reports when a user wants to see old data.

Another option I have is partitioning the table, but as I don't know much about partitioning I am not sure if this will really help me.

Stock records will be accessed each time stock is removed from inventory.  Once the remaining quantity reaches 0, the item needs to remain in the table for reporting purposes only.  A new entry is created upon purchase of further stock.

I have gone through the dev.mysql documentation on Partitioning.

Questions

Can anyone shade some light on this ?
Is this the right way of doing this ?
Should we handle it differently ?

Partitioning is helpful with large tables and management purposes for your DBA.  If you have 50GB of data you should start looking into partitioning just for perfomrance alone, as Kim Tripp stated.

However, as a DBA I find partitioning great for management of large tables and reporting data that comes from a OLTP source, something which I think you are describing.  Partitioning will allow you to also create different indexes, different fill factors, and utilize indexed views on top of them to gather massive performance boosts for aggregation type functions utilizng "Indexed Views over Partitioned Tables".

In my recommendation, get familiar with partitioning.  It can save you disasters.  I once inhereted a table that was 6 billion rows and growing at 2-3GB a day with only 20GB free on the drive.  I was able to create a new partition for new data, copy out all the old data to different databases on RAID 5, and update the code to refer to views; all without client downtime in a 24/7 shop.  Partitioning is essential as data sets grow larger.


Is one-to-one relationship required? I have these three tables on my database:

There is a one-to-one relationship between Code and Aggregation table (an AggregationId is always a CodeId. And of course, a CodeId is unique). Note: A Code.CodeId doesn't need to be in Aggregation table.

There is a one-to-many relationship between Aggregation and AggregationChildren table (an aggregation will have one or more children).

I think I'm wasting a lot of space repeating a NVARCHAR(20) data in three tables Code.CodeId, Aggregation.AggregationId and AggregationChildren.AggregationId.

By the way, `AggregationChildren.

What do you think?

I think I can delete Aggregation and AggregationChildren tables.Then, add two new columns to Code table:
One to indicate to replace Aggregation.AggregationId.
Another one to replace AggregationChildren.Position.

If the multiple use of NVARCHAR(20) bothers you, change the type to INT and adapt the Code table to have two columns: ID (INT) and Name (NVARCHAR(20)).


Multiple FK poiting to single column I'm working on a project, I'm not an advanced backend/database dev, but I always like to understand the 'why's, pros and cons' of things. So I have this table and some times I use Foreign Keys to relate one table to another.
One thing my client asked me recently was to implement a way to know wich user created each record on the table. Doesn't matter if it's a product, category, address, purchase, client, whatever. He wants to know who created those records.
To do that, I implemented one more field on these tables and I called it author_id, this id is reference the id on the main user table.
The problem is, If I go to the table tb_product and create one FK pointing to tb_user.id I can't do the same on the tb_category. Same applies to all the other tables that are referencing the user ID to be the author of that record.
I could go around this issue by changing the name of the following FK declarations, ex:

fk_product_author_id
fk_category_author_id
fk_address_author_id
and so on...

But my question is.. Is it ok to do that? Is it the correct way to declare all those fk? Or should I do something completely different?

You should choose a pattern for the FK name that respects the relationships between the tables.
My preference is FK_<child entity>_<verb phrase>_<parent entity>.
So fk_product_modified_by_user, fk_category_modified_by_user, etc.


Is there performance difference between "SET ROWCOUNT" and "TOP"? I need to select batches of N rows from a large table.

I know of two ways of doing this:

SET ROWCOUNT

SET ROWCOUNT ####
SELECT something FROM table WHERE clauses
SET ROWCOUNT 0 -- rowcount is only needed for the one query above.

TOP

 SELECT TOP ### something FROM table WHERE clauses

Is there any performance difference (or any other reason to pick one over another) between the two solutions?

Notes:

If it matters, Sybase ASE is version 15.
The query is a single SELECT statement. The limit will not apply to any other statements (so usual reasons to pick rowcount over top don't apply).
The order of the rows does not matter

Research done:

This SO Q&A has accepted answer which claims no difference without any supporting references; and another answer that shows a possible un-measured difference for MS SQL server (which I'm not sure applies to Sybase as the reference is from Microsoft).

Generally speaking ... performance should be the same.

Technically speaking ... it depends on the query; for example, if the TOP is used within the confines of a derived table then you may see different performance depending on how/if the optimizer flattens the query.

To answer the question for a given query(s), run some tests with the following options enabled:

set showplan on
set statistics io,time on
go
-- set rowcount XXX
select [top] ...
-- set rowcount 0
go

You'll want to run the query a couple times to make sure data has been pulled from disk so that you can compare in-memory data queries to each other.

Obviously you'll then want to compare the 2x query plans as well as the IOs and timings (keeping in mind that times can vary greatly based on duration of blocking and/or time spent on the wait queue waiting to run on a dataserver engine). IOs should be comparable on repeat runs, but for timings you'll likely want to run each query a few times and take the average.


Not able to search on zipcode column a table in postgresql Hello I have a postgresql question about searching on zip codes.  Basicly, if I do a:

select * from my_table where zip_ = '90210';

I am returned 0 rows.  I know for a fact that there is thousands of rows with that zip code.  The table itself is over a million rows.

    Column        |       Type        |                      Modifiers
---------------------+-------------------+-----------------------------------------------------
...
 zip_             | character varying |

Here is my table and I am not able to search effectively on zip_ column.
  The table does return a search if I use the like keyword along with % char, but that is not what I need.

my questions are:

why when i search on zip_ lets say '90210' it comes back with 0 rows when I know there is thousands of records with the zip code of '90210'?
if i change the type to int would that make a difference?
do i need an index on the zip_ column?

Try to remove the whitespaces in the table:

UPDATE my_table
       SET zip_ = trim(_zip);

And then find out who enters ZIPs with whitespaces and tell them to stop, or add a trigger to the table that trims the data upon insertion or updating.

Simple version for a start:

    CREATE FUNCTION my_table_normalize_zip()
                    RETURNS TRIGGER
    AS
    $$
    BEGIN
      NEW.zip_ = trim(NEW.zip_);

      RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER normalize_zip
                   BEFORE INSERT
                           OR UPDATE
                   ON my_table
                   FOR EACH ROW
                   EXECUTE PROCEDURE my_table_normalize_zip();

Possibly add more normalization logic to it as needed.

You might also consider a CHECK CONSTRAINT or rather a CONSTRAINT TRIGGER (because this can get complicated and beyond the scope of a simple CHECK CONSTRAINT) to have some plausibility check on the ZIPs. But a warning: We tried that once in the company I worked for. For some countries it's pretty simple. For others it's just a nightmare and again for others you can search your b**t to even find any rules. In the end we gave up. It was to expensive compared to the benefit (most postal services are quite good in finding out where misaddressed mail really belongs to anyway). But a "light version", for those countries it's easy for, might be reasonable.


Database Design Question for a weird use case I am having trouble figuring out a good database design for an app I am developing. I have included a description of the related use case in the image below.

How would you design the Work Package in the database? A Project is made up of many Work Packages, which is fine, but a Work Package can also be made up of smaller Work Packages, which can be made up of even smaller Work Packages?  How do you design such a 'nested self' relation in the database?

Thanks for the help!

I would approach it this way.  Have a "ParentWorkPackageID" that links back to the parent work package.  Add as many additional properties as you need to the work packages.

You can easily (using a recursive CTE) query for a work package and any children it may have.  You don't mention your engine so I'm not going to provide an example.

You could optionally do away with the Project table completely and simply assume that any workPackage with NULL for a Parent must be the top level workPackage/Project.  But I think keeping Project as a separate table will be cleaner since there are probably attributes that apply to projects and not to sub-work packages.

EDIT

I corrected the syntax (sorry about the check constraint error).
I've also included an example for you.

DROP TABLE IF EXISTS dbo.WorkPackage
DROP TABLE IF EXISTS dbo.Project

CREATE TABLE dbo.Project
    (
    ProjectID INT NOT NULL IDENTITY(1,1)
    , ProjectName NVARCHAR(100) NOT NULL
    , CONSTRAINT PK_Project PRIMARY KEY CLUSTERED (ProjectID)
    )

CREATE TABLE dbo.WorkPackage
    (
    WorkPackageID INT NOT NULL IDENTITY(1,1)
    , ProjectID INT NOT NULL
    , ParentWorkPackageID INT NULL
    , WorkPackageName NVARCHAR(100) NOT NULL
    , CONSTRAINT PK_WorkPackage PRIMARY KEY CLUSTERED (WorkPackageID)
    , CONSTRAINT FK_WorkPackage_ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID)
    , CONSTRAINT FK_WorkPackage_ParentWorkPackageID FOREIGN KEY (ParentWorkPackageID) REFERENCES dbo.WorkPackage (WorkPackageID)
    )

GO

INSERT INTO dbo.Project
(ProjectName)
VALUES ('TestProject')

INSERT INTO dbo.WorkPackage
(ProjectID, ParentWorkPackageID, WorkPackageName)
VALUES (1, NULL, 'First Work Package')
    , (1, 1, 'First child Package')
    , (1, 2, 'First grand child Package')
    , (1, 1, 'Second Child')

/** Easy to find all packages for a project... **/
SELECT * FROM dbo.WorkPackage WHERE ProjectID = 1

/** Find all children for a specific work package using recursive CTE
    - adds a Generation column so you can see parent to child descendants, regardless of when they were created.
    **/
;WITH CTE_FindChildren AS
    (
    SELECT WP.WorkPackageID AS WorkPackageID
        , WP.ParentWorkPackageID AS ParentWorkPackageID
        , Generation = 1
    FROM dbo.WorkPackage AS WP
    WHERE WP.ParentWorkPackageID IS NULL
    UNION ALL
    SELECT WP.WorkPackageID AS WorkPackageID
        , CWP.WorkPackageID AS ParentWorkPackageID
        , Generation = Generation + 1
    FROM dbo.WorkPackage AS WP
        INNER JOIN CTE_FindChildren AS CWP ON CWP.WorkPackageID = WP.ParentWorkPackageID
    )
SELECT WP.*
    , CF.Generation
FROM CTE_FindChildren AS CF
    INNER JOIN dbo.WorkPackage AS WP ON WP.WorkPackageID = CF.WorkPackageID
WHERE WP.ProjectID = 1
ORDER BY Generation, WorkPackageID


Difference between oracle database desktop and server class I installed Oracle database 11g enterprise edition to my laptop. When I was installing Oracle, at the beginning I selected Desktop Class (not server class) option.

Now I am trying to make remote connection to my Oracle database from another PC but I cannot connect to Oracle and get this error message:

  "ORA-12545: Connect failed because target host or object does not exist"

even though all Oracle services have been already started.

May the reason be why I installed Oracle as desktop class instead of server class?

My tnsnames.ora file:

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

DERS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Ders)
    )
  )

May the reason be why I installed Oracle as desktop class instead of server class?

No, 'Desktop Class' just means you only get basic choices from the installer1, according to the docs:

  Desktop Class—This installation class is most appropriate for laptop or desktop computers. It includes a starter database and
  requires minimal configuration.
  Server Class—This installation class is for servers, such as you would find in a data center, or used to support enterprise-level
  applications. Choose this installation class if you need access to
  advanced configuration options.

  During a Desktop Class installation, you make only basic choices. For
  a Server Class installation, you choose either typical installation
  (where you make only basic choices) or advanced installation.

  During a Desktop Class or a typical installation, Oracle Database
  automatically installs the sample schemas.

You'll need to go through the kind of troubleshooting steps suggested in the comments to find out what is causing the ORA-12545 error:

  An ORA-12545 error occurs when the client is not able to contact the
  listener on the node specified. For example: when the listener has not
  been started on the node, if the node address has been specified
  incorrectly, or a firewall blocks access.

1 So 'Desktop Class' and 'Server Class' are poor names for the installation types. 'Basic' and 'Custom' might be better?