[T-SQL] Space taken by Null ! |
- Space taken by Null !
- Eliminate Cursor
- Avoid LEFT join
- Differences between all columns of two rows based on date
- How To Load Multiple files Using Bulk Insert & Check What Filename StartsWith
- Stored Procedure Having More than 1000 Lines
- Help with calculation and query
- Returning stored procedure results into a CTE or temp table?
| Posted: 20 Aug 2013 11:46 PM PDT Over internet there are a lot of topics regarding the size taken by NULL values. There is some mismatch in every 2 topics so i though of doing some POC myself. I observed something which was strange. Following is the list of queries i executed.[code="sql"]create table tbltemp1(i int,a nvarchar(50),b varchar(50))sp_spaceused 'tbltemp1' --Over here it showed as zeroinsert into tbltemp1 values(null,null,null)select * from tbltemp1sp_spaceused 'tbltemp1' -- over it shows the sizedelete from tbltemp1sp_spaceused 'tbltemp1' --over here it should actually show zero but it is showing some values.[/code] In the last Sp_spaceused, my question is why is it showing the values for the size of DATA. |
| Posted: 20 Aug 2013 05:59 PM PDT Hi all,I have given a scenario in which I have to eliminate cursors from all the objects of database.In my database I have around 300 stored procedure in which CURSORS are used.So, can you guys please tell me what approach should I use to remove those cursor?Also, can I remove the cursors using Tally table concept? |
| Posted: 20 Aug 2013 04:25 PM PDT I am facing issues with a LEFT JOIN in my query. It takes 45 secs to process on the production server due to huge number of records. Need help in building a query to avoid the LEFT JOIN. I am Trying to use UNION ALL and it works much faster except that I am stuck in the last bit.scripts (sample):CREATE TABLE [dbo].[tbl_PersonDetails]( [PersonID] [int] NOT NULL, [LeaveTimeId] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tbl_PersonLeaveDetails]( [PersonId] [int] NOT NULL, [LeaveFromTimeID] [int] NULL, [LeaveToTimeID] [int] NULL) ON [PRIMARY]GOINSERT [dbo].[tbl_PersonLeaveDetails] ([PersonId], [LeaveFromTimeID], [LeaveToTimeID]) VALUES (1, 5, 11)INSERT [dbo].[tbl_PersonLeaveDetails] ([PersonId], [LeaveFromTimeID], [LeaveToTimeID]) VALUES (2, 12, 15)INSERT [dbo].[tbl_PersonDetails] ([PersonID], [LeaveTimeId]) VALUES (1, 10)INSERT [dbo].[tbl_PersonDetails] ([PersonID], [LeaveTimeId]) VALUES (2, 8)INSERT [dbo].[tbl_PersonDetails] ([PersonID], [LeaveTimeId]) VALUES (3, 9)INSERT [dbo].[tbl_PersonDetails] ([PersonID], [LeaveTimeId]) VALUES (1, 4)Requirement:--------------------Need Rows from tbl_PersonDetails macthing (all 3 below) following criteria :1. tbl_PersonDetails.PersonID is present in tbl_PersonLeaveDetails2.tbl_PersonDetails.TimeID does not fall between any of the aligned (matching personid) FromTimeID and ToTimeID in tbl_PersonLeaveDetails.3. not using LEFT joinso in this case for example.. need - tbl_PersonDetails table .... record, 1,4 |
| Differences between all columns of two rows based on date Posted: 20 Aug 2013 07:28 PM PDT Hey guys,I've got a table of stats which gets updated on a daily basis with an additional row for that day, at the moment, including the date field this table has 55 fields. What I'm trying to do is ascertain if any of the values have changed by X%, so like a quality control threashold, set it to say 1%, any changes over that would require a quick look.I've established how to do for one column, which I could repeat for multiple columns, however, I'm wondering if there's anything fancy to do the whole lot in one go and allow for future columns too?This is the logic I've been following thus far: http://blog.namwarrizvi.com/?p=29I can blindly copy/paste using dispstru I guess, however, I sense there's a more compact and elegant solution I'm not spotting! - Any help much appreciated. |
| How To Load Multiple files Using Bulk Insert & Check What Filename StartsWith Posted: 20 Aug 2013 07:12 PM PDT Hi Guys, I'm required to load multiple XML Files from one folder into a database table, I also need to check that the file starts with "Filename"e.g Departments_1 I need to make sure the the file starts with "department" as there'll be other files in the folder e.g Departments_2at the moment I am able to load one file at a time using the script below that checks if a file exists before loading, is it possible to achieve this using T-Sql in Management Studio, any help woul be appreciated?Here's part of the script that I'm using to load the data into a temp table below:--Load all XML data firstDECLARE @isExists INT--Cost Centresexec master.dbo.xp_fileexist 'C:\inetpub\wwwroot\Prof1t\FTP\<CLIENT_FOLDER>\Departments_1.xml', @isExists OUTPUTIF @isExists = 1BEGIN print 'Departments.xml exists' INSERT INTO tmpCostCentreXML (CostCentreCode, CostCentreName) SELECT Y.CostCentres.query('CostCentreCode').value('.', 'VARCHAR(30)'), Y.CostCentres.query('DepartmentName').value('.', 'VARCHAR(60)') FROM ( SELECT CAST(x AS XML) FROM OPENROWSET( BULK 'C:\inetpub\wwwroot\Prof1t\FTP\<CLIENT_FOLDER>\Departments_1.xml', SINGLE_BLOB) AS T(x)) AS T(x) CROSS APPLY x.nodes('Departments/Department') as Y(CostCentres); print 'Loaded Cost Centre XML'ENDELSEBEGIN print 'Departments_1.XML doesn''t exist'ENDThanks Teee |
| Stored Procedure Having More than 1000 Lines Posted: 20 Aug 2013 03:01 PM PDT Hi All, I have One stored Procedure having 1000 lines.In That Stored Procedure I have more than 500 queries.Now i want to find out which query is taking more time with out using Sql Profiler?Is it Possible?please folks share your comments |
| Help with calculation and query Posted: 20 Aug 2013 06:03 AM PDT How to calculate the update amount for the example below? Table A has the following rows. I want to get the output only for Main_dir and Main_rei which are reduced by Withhold_dir and Withhold_rei respectively which has sub_code = 50. The common connection between the rows is the Code.Table A---------Code Sub_code Name AmountsDir 20 Main_dir 100Rei 20 Main_Rei 50Dir 50 Withhold_dir 10Rei 50 Withhold_Rei 10 Output Code Sub_code Name AmountsDir 20 Main_dir 90Rei 20 Main_Rei 40 |
| Returning stored procedure results into a CTE or temp table? Posted: 20 Aug 2013 01:50 AM PDT Hi all . . .Is it possible to return the results of a stored procedure into either a CTE or temp table?In other words, is it possible to do this:[code="sql"]with someCTE as ( exec someStoredProc)[/code]or this:[code="sql"]exec someStoredProc into #tempTable[/code]???Thanks in advance! |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment