Tag Info

New answers tagged

0

Solution for this : SET @Query = 'SELECT Col1, Col2, ' + @Columns + ' INTO PhysicalTable FROM (SELECT Col1, Col2, [DateRange], Col3 FROM View) A PIVOT (SUM(Col3) FOR DateRange IN (' + @Columns + ') ) S ' Execute (@Query) And now I am able to remove the OpenRowSet.


0

If you cannot use OPENROWSET, you could create a loopback linked server and use OPENQUERY or EXECUTE AT. DECLARE @srv nvarchar(4000); SET @srv = @@SERVERNAME; -- gather this server name -- Create the linked server EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK', @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer” @provider ...


0

Queries executed with EXECUTE are re-planned with the actual parameter values passed to it every time. Since you are using Postgres 9.2, you may not even need EXECUTE: PostgreSQL Stored Procedure Performance Either way, upgrading to Postgres 9.3 (or the upcoming 9.4) might help some more. So, unless we have a type mismatch (like you already suspected) ...


2

Function for a single table Returns all character-type columns of the given table with a count of empty values ('') and whether they are defined NOT NULL. CREATE OR REPLACE FUNCTION f_tbl_empty_status(_tbl regclass) RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool) AS $func$ DECLARE -- basic char types, possibly extend with citext, ...


1

The best way to do this is with a CHECK constraint, as you noted, possibly via a DOMAIN, e.g. CREATE DOMAIN nonempty_string AS text CONSTRAINT non_empty CHECK (length(VALUE) > 0); then ALTER existing columns to use the domain. If that's not possible, you will need to query INFORMATION_SCHEMA to find all columns of the target type across all tables, ...


1

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server Step1: Create function to split tokens CREATE FUNCTION [dbo].[fnSplit] ( @List VARCHAR(8000), @Delimiter CHAR(1) = ',' ) RETURNS @Temp1 TABLE ( ItemId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Item VARCHAR(8000) NULL ) AS BEGIN DECLARE @item ...


1

Your code looks mature, mostly, but see below! Once you fixed that, I don't see much that could go wrong with it. I share the doubts that it will improve performance much, though. And there may be better alternatives, depending on exact requirements. General advice For simple functions as displayed: Use simple SQL function (LANGUAGE sql) which can be ...


0

This approach is not something I would recommend. There is way too much that can go wrong and lots of operations in the database that are unnecessary. Have you benchmarked the slowness you claim? Chances are, it's not slow at all. I'll demonstrate: create table fmt (k text, f text, constraint pk_fmt primary key (k)); insert into fmt values ('myfmt', ...



Top 50 recent answers are included